Bulk Insert Update and Delete in SQL Server

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();
}

2 Comments

Join the conversation! Your thoughts help the community grow.

Abhilash Ashok15y ago
The above mentioned method has its performance drop as well. I would suggest you to take a look at http://blogs.cametoofar.com/post/Bulk-data-InsertUpdateDelete-in-Sql-Server.aspx. Thanks.
- Harshit Vyas15y ago
  Thanks dude really nice link, sorry actually i need to mention that this type of operation (i had written here) will be good if we don't want to make our self dataentry as i had use it many times when we had lot of work of dataentry all the entries come into the excel format with the some change in code and excel file we can save our lot of time.