In this article we will see how to make bulk
operation in sql server.
First we will see the code to read the database table and take data into the .csv
file
I have just explain this code with one table in my database named csvtable I
have also uploaded simple demo example for that and one csv file to know how the
table is in database.
If you have to perform the same operation on more than one table then simply
make the procedure for that it will make the things easier to perform task on
multiple table.
Here is the code:
protected
void Button1_Click(object
sender, EventArgs e)
{
SqlConnection cn =
new SqlConnection(ConfigurationManager.ConnectionStrings["myconn"].ConnectionString);
SqlDataAdapter adp =
new SqlDataAdapter("select
* from CsvTable", cn);
DataTable dt =
new DataTable();
adp.Fill(dt);
System.IO.StreamWriter sw =
new System.IO.StreamWriter(Server.MapPath("myfile.csv"),false);
int iColCount = dt.Columns.Count;
for (int
i = 0; i < iColCount; i++)
{
sw.Write(dt.Columns[i]);
if (i < iColCount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
foreach (DataRow
dr in dt.Rows)
{
for (int
i = 0; i < iColCount; i++)
{
if (!Convert.IsDBNull(dr[i]))
{
sw.Write(dr[i].ToString());
}
if (i < iColCount - 1)
{
sw.Write(",");
}
}
sw.Write(sw.NewLine);
}
sw.Close();
Response.Redirect("myfile.csv");
}
Now how to read the data from that csv file.
protected void
Button2_Click(object sender,
EventArgs e)
{
string strLine;
string[] strArray;
char[] charArray =
new char[] {
',' };
DataSet ds =
new DataSet();
DataTable dt = ds.Tables.Add("TheData");
string path = Server.MapPath("myfile.csv");
FileStream aFile =
new FileStream(path,
FileMode.Open);
StreamReader sr =
new StreamReader(aFile);
strLine =
sr.ReadLine();
strArray =
strLine.Split(charArray);
for (int x = 0;
x <= strArray.GetUpperBound(0); x++)
{
dt.Columns.Add(strArray[x].Trim());
}
strLine =
sr.ReadLine();
while (strLine !=
null)
{
strArray = strLine.Split(charArray);
DataRow dr = dt.NewRow();
for (int
i = 0; i <= strArray.GetUpperBound(0); i++)
{
dr[i] = strArray[i].Trim();
}
dt.Rows.Add(dr);
strLine = sr.ReadLine();
}
sr.Close();
GridView1.DataSource = dt;
GridView1.DataBind();
}
Now how to updating or inserting data from csv file to database.
protected
void Button4_Click(object
sender, EventArgs e)
{
string path = Server.MapPath("myfile.csv");
FileStream aFile =
new FileStream(path,
FileMode.Open);
StreamReader isr = new
StreamReader(aFile);
try
{
SqlConnection cn =
new SqlConnection(ConfigurationManager.ConnectionStrings["myconn"].ConnectionString);
SqlDataAdapter adp =
new SqlDataAdapter("select
* from CsvTable", cn);
DataTable dt =
new DataTable();
adp.Fill(dt);
GridView2.DataSource = dt;
GridView2.DataBind();
int totaldatabaserows =
dt.Rows.Count;
string strLine;
string[] strArray;
char[] charArray =
new char[] {
',' };
DataSet ds =
new DataSet();
// DataTable dt = new DataTable();
string readlines;
for (int
qqq = 0; qqq <= totaldatabaserows; qqq++)
{
readlines = isr.ReadLine();
}
readlines = isr.ReadLine();
while (readlines !=
null)
{
// readlines = isr.ReadLine();
string[] myarray =
readlines.Split(',');
int id =
Convert.ToInt32( myarray[0].Trim());
string name = myarray[1].Trim();
int sal =
Convert.ToInt32(myarray[2].Trim());
string img = myarray[3].Trim();
bool sta =
Convert.ToBoolean(myarray[4].Trim());
SqlCommand cmd =
new SqlCommand("insert
into CsvTable (id,name,sal,img,sta) values('" + id +
"','" + name +
"','" + sal + "','" + img +
"','" + sta + "')",
cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
readlines = isr.ReadLine();
}
isr.Close();
adp.Fill(dt);
FileStream aFile1 =
new FileStream(path,
FileMode.Open);
StreamReader sr =
new StreamReader(aFile1);
strLine = sr.ReadLine();
strArray =
strLine.Split(charArray);
//for (int x = 0; x <= strArray.GetUpperBound(0); x++)
//{
//
dt.Columns.Add(strArray[x].Trim());
//}
int j = 0;
strLine =
sr.ReadLine();
while (strLine != null)
{
strArray = strLine.Split(charArray);
DataRow dr = dt.NewRow();
//for (int i = 0; i <=
strArray.GetUpperBound(0); i++)
//{
// dr[i] = strArray[i].Trim();
// dt.Rows[j].BeginEdit();
// dt.Rows[j][i] = dr[i];
// dt.Rows[j].EndEdit();
// dt.AcceptChanges();
//}
if (dt.Rows[j][0] !=
strArray[0].Trim() || dt.Rows[j][1] != strArray[1].Trim() || dt.Rows[j][2] !=
strArray[2].Trim() || dt.Rows[j][3] != strArray[3].Trim() || dt.Rows[j][4] !=
strArray[4].Trim())
{
SqlCommand cmd =
new SqlCommand("update
CsvTable set id='" + strArray[0].Trim() + "' ,
name = '" + strArray[1].Trim() + "',sal='"
+ strArray[2].Trim() + "',img='" +
strArray[3].Trim() + "',sta='" +
strArray[4].Trim() + "' where id='" +
strArray[0].Trim() + "'", cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
}
strLine = sr.ReadLine();
j++;
}
sr.Close();
SqlDataAdapter adp1 =
new SqlDataAdapter("select
* from CsvTable", cn);
DataTable dt1 =
new DataTable();
adp1.Fill(dt1);
GridView2.DataSource = dt1;
GridView2.DataBind();
}
catch(Exception
ex)
{
isr.Close();
//sr.Close();
Response.Write(ex.Message.ToString());
}
}
Now how to delete data from csv file.
protected void
Button5_Click(object sender,
EventArgs e)
{
SqlConnection cn =
new SqlConnection(ConfigurationManager.ConnectionStrings["myconn"].ConnectionString);
SqlDataAdapter adp =
new SqlDataAdapter("select
* from CsvTable", cn);
DataTable dt =
new DataTable();
adp.Fill(dt);
string path = Server.MapPath("myfile.csv");
string strLine;
string[] strArray;
char[] charArray =
new char[] {
',' };
FileStream aFile11 =
new FileStream(path,
FileMode.Open);
StreamReader deletedata =
new StreamReader(aFile11);
strLine = deletedata.ReadLine();
strArray = strLine.Split(charArray);
//for (int x = 0; x <=
strArray.GetUpperBound(0); x++)
//{
// dt.Columns.Add(strArray[x].Trim());
//}
int j = 0;
strLine = deletedata.ReadLine();
int rowscount = dt.Rows.Count;
for (int
inc = 0; inc < rowscount; inc++)
{
string val1 = dt.Rows[inc][0].ToString();
strArray =
strLine.Split(charArray);
string val2 = strArray[0].Trim();
if (val1 == val2)
{
strLine = deletedata.ReadLine();
}
else
{
SqlCommand cmd =
new SqlCommand("delete
from CsvTable where id='" + Convert.ToInt32(val1)
+ "'", cn);
cn.Open();
cmd.ExecuteNonQuery();
cn.Close();
rowscount--;
inc++;
strLine = deletedata.ReadLine();
}
}
deletedata.Close();
// SqlConnection cn = new
SqlConnection(ConfigurationManager.ConnectionStrings["myconn"].ConnectionString);
SqlDataAdapter adp1 =
new SqlDataAdapter("select
* from CsvTable", cn);
DataTable dt1 =
new DataTable();
adp1.Fill(dt1);
GridView2.DataSource = dt1;
GridView2.DataBind();
}