Hello All, We are going to start new batch from next week. message/call or mail us for more details.

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 = newSqlConnection(ConfigurationManager.ConnectionStrings["myconn"].ConnectionString);
        SqlDataAdapter adp = new SqlDataAdapter("select * from CsvTable", cn);
        DataTable dt = new DataTable();
        adp.Fill(dt);
        System.IO.StreamWriter sw = newSystem.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 = newSqlConnection(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 = newSqlConnection(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();
    }

8 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. Bhavesh Siddhpura23 June 2014 at 00:39

    Hello Mr. Pankaj ,
    Can you post the simplest demo to perform insert, update, delete operations in ASP.NET , C# without using any source controls. I need to learn this demo through coding.

    ReplyDelete
  3. It is very good and useful for students and developer . Learned a lot of new things from your post!Good creation ,thanks for good info .Net Online Training Hyderabad

    ReplyDelete
  4. I am glad to read this. Thank you for this beautiful content, Keep it up. Techavera is the best Loadrunner training course in Noida. Visit us For Quality Learning.Thank you

    ReplyDelete
  5. I am glad to read this. Thank you for this beautiful content, Keep it up. Techavera is the best CCIE training course in Noida. Visit us For Quality Learning.Thank you

    ReplyDelete
  6. Nice article . Thank you for this beautiful content, Keep it up. Techavera is the best
    java training institute in noida
    Visit us For Quality Learning.Thank you

    ReplyDelete
  7. Good Post. wonderful article. Nice thoughts.

    If you want to logo designing services than visit - Logo Designing Services Jaipur | Logo Designer in Jaipur | Logo maker in Jaipur

    ReplyDelete