Bulk insert and update in Asp.net

In asp.net using C# there is a big question how to update multiple records. without hitting database for each record. The answer is update in bulk. Take multiple records into "Datatable" and use string builder to do the bulk updation into your Database. 

public void bindGrd()

{

      DataTable dt = new DataTable();

      dt.Columns.Add(new DataColumn("lblUserID", typeof(int)));

      dt.Columns.Add(new DataColumn("Name", typeof(string)));

      dt.Columns.Add(new DataColumn("Country", typeof(string)));

      for (int i = 0; i < 10; i++)

      {

           dr = dt.NewRow();

           dr["UserID"] = 1;                 // Find Control 

           dr["Name"] = "XYZ";               // Find Control 

           dr["Country"] = "PQR";            // Find Control

           dt.Rows.Add(dr);

     }

     grdUpdate.DataSource = dt;

     grdUpdate.DataBind();

}

On Update Button Click ->

protected void btn_Click(sender obj, Eventargs e)

{

      StringBuilder sb = new StringBuilder();

      sb.Append("<root>"); 

      for (int i = 0; i < gvCustomer.Rows.Count; i++)

      {

          Label lblID = grdUpdate.Rows[i].FindControl("UserID") as Label;

          TextBox txtName= grdUpdate.Rows[i].FindControl("txtName") as TextBox;

          TextBox txtCountry = grdUpdate.Rows[i].FindControl("txtCountry") as TextBox;

           if(lblID.Text.Length > 0)

           {

                sb.Append("<row ID='" + lblID.Text.Trim() + "' Name='" + txtName.Text.Trim() +  "' Country='" + txtCountry.Text.Trim() + "'/>");

           }

      }

      sb.Append("</root>"); 

      bulkUpdate(sb);

}

Method which call your stored procedure :-

public static bool bulkUpdate(StringBuilder sb)

{

       string conStr = WebConfigurationManager.     ConnectionStrings["DB"].ConnectionString;

      using(SqlConnection con = new SqlConnection(conStr))

      {

            SqlCommand cmd = new SqlCommand("sp_UpdateRecords", con);

            cmd.CommandType = CommandType.StoredProcedure;

            cmd.Parameters.AddWithValue("@XMLBulkUpdate", sb.ToString());

            con.Open();

            cmd.ExecuteNonQuery();

            con.close();

      }

}

Stored Procedure for Update in Bulk - 

CREATE PROCEDURE [dbo].[sp_UpdateRecords]

(

 @XMLBulkUpdate XML

)

AS

BEGIN

      Update tbl   set          

            Name = TempData.Item.value('@Name', 'VARCHAR(50)'),

            Country = TempData.Item.value('@Country', 'VARCHAR(50)')

      FROM @XMLBulkUpdate.nodes('/root/row') AS TempData(Item)

   where 

       ID =  TempData.Item.value('@ID', 'INT'),

RETURN 0

END

And if you want to bulk update then change your procedure from Update to insert :

Insert into tbl(ID,Name,Country)

       Select        

            ID =  TempData.Item.value('@ID', 'INT'),   

            Name = TempData.Item.value('@Name', 'VARCHAR(50)'),

            Country = TempData.Item.value('@Country', 'VARCHAR(50)')

      FROM @XMLBulkUpdate.nodes('/root/row') AS TempData(Item)

       

Basic Insert Operation to SQL Table Using LINQ

Hii ,

Here i am going to tell you something about Language Integrated Query in short LINQ.
For permofing DML operation (Insert, Update , Delete) to the data in your database, for that you need to write SQL query which modify the information of your database table . Here i am giving a basic example to how to use LINQ with your application using C#.

For inserting a value :

Step 1 :  Add an EDMX to your application.

Step 2 :  Select database from your SQL SERVER 

Step 3:  Add a .aspx page to your application

Step 4 : Add namespace of your entity model in your aspx.cs page 

Step 5 : Supose your table stucture is : 

ID - Int Idenntity Primary Key
Name - Nvarchar(20)
Age - int

and page have two textboxes with button.
Now you want to insert records uisng LINQ to your SQL table.

on button click event write the below code :

protected void btn_click(object sender, Eventargs e)
{
            EntityModel obj = new EntityModel ();   // obj is an object of EntityModel
            YourTable tbl = new  YourTable ();       // tbl is an object of your Table
             tbl.Name = textbox1.Text;
             tbl.Age = Convert.Toint32(textbox1.Text);
             obj.AddTotbl();                                  // Add Data to your table 
             obj.saveChanges();                             // Save the changes to your SQL Table
}