Using String Builder to Bulk Insert and Update Data's in Asp.net

//Design gridview

  DataTable dt = new DataTable();

  DataRow dr = null;

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

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

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

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

  gvCustomer.DataSource = dt;

  gvCustomer.DataBind();

//Add button Insert to gridview

private void Insert()

{ 

      DataTable dt = new DataTable();

      DataRow dr = null;

      dr = dt.NewRow();

      dr["CustName"] = txtName.Text.ToString();

      dr["CustPosition"] = txtPos.Text.ToString();

      dr["CustCity"] = txtCity.Text.ToString();

      dr["CustState"] = txtState.Text.ToString();

      dt.Rows.Add(dr);

      gvCustomer.DataSource = dt;

      gvCustomer.DataBind();

}

//Save button using string builder

 protected void btnSave_Click(object sender, EventArgs e)

 {

   StringBuilder sb = new StringBuilder();

   sb.Append("<root>"); 

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

   {    

    string Name= gvCustomer.Rows[i].Cells[0].Text.ToString();

    string Position = gvCustomer.Rows[i].Cells[1].Text.ToString();

    string City = gvCustomer.Rows[i].Cells[2].Text.ToString();

    string State= gvCustomer.Rows[i].Cells[3].Text.ToString();

   

    if(Name != "")

      sb.Append("<row Name='" + Name + "' Position='" + Position + "' City='" +         City + "' State='" + State + "'/>");

   }

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

  string conStr = WebConfigurationManager.

  ConnectionStrings["BlogConnectionString"].ConnectionString;

  SqlConnection con = new SqlConnection(conStr);

  //InsertCustomer as Stored Procedure

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

  cmd.CommandType = CommandType.StoredProcedure;

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

 try

 {

   using (con)

   {

     con.Open();

     cmd.ExecuteNonQuery();

   }

   lblError.Text = "Record(s) Inserted successfully";

   lblError.ForeColor = System.Drawing.Color.Green;

 }

 catch (Exception ex)

 {

  lblError.Text = "Error Occured";

  lblError.ForeColor = System.Drawing.Color.Red;

  }

 }

//Stored Procedure Insert Query in Sql server

CREATE PROCEDURE [dbo].[InsertCustomer]

(

 @XMLCustomer XML

)

AS

BEGIN

      INSERT INTO CUSTOMER

             (CustName,CustPosition,CustCity,CustState)             

      SELECT

            TempCustomer.Item.value('@Name', 'VARCHAR(50)'),

            TempCustomer.Item.value('@Position', 'VARCHAR(50)'),

            TempCustomer.Item.value('@City', 'VARCHAR(50)'),

            TempCustomer.Item.value('@State', 'VARCHAR(50)')

      FROM @XMLCustomer.nodes('/root/row') AS TempCustomer(Item)

RETURN 0

END

//Upadate button Same Process of Save button differnt stored procedure

//Stored Procedure Update Query in Sql server

CREATE PROCEDURE [dbo].[UpdateCustomer]

(

 @XMLCustomer XML

)

AS

BEGIN

      UPDATE Customer

            SET CustName=TempCustomer.Item.value('@Name', 'VARCHAR(50)'),

            CustPosition=TempCustomer.Item.value('@Position', 'VARCHAR(50)'),

            CustCity=TempCustomer.Item.value('@City', 'VARCHAR(50)'),

            CustState=TempCustomer.Item.value('@State', 'VARCHAR(50)')

      FROM @XMLCustomer.nodes('/root/row') AS TempCustomer(Item)

      WHERE CustID=TempCustomer.Item.value('@CustID', 'INT')

RETURN 0

END