Bulk insert and update from gridview using Asp.net and C#

This is how to insert bulk records. More a less, its same only, But some what difference. As in bulk update we fetch records and display in grid. But in bulk insert, we have to first create an empty row for inserting bulk records.

Download source and store procedure script from here.

private void InsertEmptyRow() {   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)));   for (int i = 0; i < 5; i++)   {       dr = dt.NewRow();       dr["CustName"] = string.Empty;       dr["CustPosition"] = string.Empty;       dr["CustCity"] = string.Empty;       dr["CustState"] = string.Empty;       dt.Rows.Add(dr);    }     gvCustomer.DataSource = dt;     gvCustomer.DataBind(); }

As, you see above code I am creating five empty rows initially which is empty. This method I am calling at page load event. Note :- I am creating only five empty rows, you can create as much required for your bulk insert according to your  requirement

And one more difference between, Bulk update and bulk insert is that as we fetch all records for bulk update, so we don’t have empty rows, but in insert we have five empty rows. One more thing, Suppose we insert three rows and two  rows empty what would happen ?. For that I am checking if name field is empty or not, else don’t insert.

StringBuilder sb = new StringBuilder();  sb.Append("<root>");   for (int i = 0; i < gvCustomer.Rows.Count; i++)  {     TextBox txtName = gvCustomer.Rows[i].FindControl("txtName") as TextBox;     TextBox txtPosition = gvCustomer.Rows[i].FindControl("txtPosition") as TextBox;     TextBox txtCity = gvCustomer.Rows[i].FindControl("txtCity") as TextBox;     TextBox txtState = gvCustomer.Rows[i].FindControl("txtState") as TextBox;     if(txtName.Text.Length != 0)       sb.Append("<row Name='" + txtName.Text.Trim() + "' Position='" + txtPosition.Text.Trim() +          "' City='" + txtCity.Text.Trim() + "' State='" + txtState.Text.Trim() + "'/>");  }  sb.Append("</root>");

As you see, I am checking txtName if length is more then zero then insert record else skip it.  As other insert detail is simple.

string conStr = WebConfigurationManager. ConnectionStrings["BlogConnectionString"].ConnectionString;  SqlConnection con = new SqlConnection(conStr);  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;   }

Store procedure also simple, just direct insert from XML structure what it consist, this is also difference between bulk update  as in which we check in where condition of customer id matches or not

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go 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

Note :-  In bulk update, we are passing customer id also, here its not required as it is auto increment.

Bulk Update using Gridview and

 Sql Server XML

As we are know importance of opening and closing connection while using Database. Generally when we have  some many records into database and have to update all record at same time, opening and closing  connection for each transaction will give performance issue.

For this, we like to do an bulk update for all records, instead of single records. When we update record into  bulk, its open only one connection. Here, in this article I will explain how to do bulk update using gridview and sql server xml. I will pass an XML from code behind and using in store procedure. In SP, I will use  XML as data type. and will update into table.

Download source code and SP script here.

I will use Template column in Gridview.  Template column, use Textbox control to display all records. Records are binding using Sql Data Source.  A button has been used, for updating bulk records. Using Sql Server XML,  I had learned from here.

HTML mark up look like below :- <asp:GridView ID="gvCustomer" runat="server" AutoGenerateColumns="False" BackColor="White"  BorderColor="#999999" BorderWidth="1px" CellPadding="3" DataKeyNames="CustID"DataSourceID="SqlDataSource1" GridLines="Vertical" BorderStyle="None" ShowFooter="True">    <RowStyle BackColor="#EEEEEE" ForeColor="Black" />      <Columns>         <asp:BoundField DataField="CustID" HeaderText="CustID" InsertVisible="False" ReadOnly="True"SortExpression="CustID" />         <asp:TemplateField HeaderText="Name" SortExpression="CustName">             <ItemTemplate>                 <asp:TextBox ID="txtName" runat="server" Text='<%# Bind("CustName") %>' BorderStyle="Solid"BorderWidth="1px"/>             </ItemTemplate>         </asp:TemplateField>         <asp:TemplateField HeaderText="Position" SortExpression="CustPosition">             <ItemTemplate>                <asp:TextBox ID="txtPosition" runat="server" Text='<%# Bind("CustPosition") %>'BorderStyle="Solid" BorderWidth="1px"/>             </ItemTemplate>         </asp:TemplateField>         <asp:TemplateField HeaderText="City" SortExpression="CustCity">             <ItemTemplate>                <asp:TextBox ID="txtCity" runat="server" Text='<%# Bind("CustCity") %>' BorderStyle="Solid"BorderWidth="1px"/>             </ItemTemplate>         </asp:TemplateField>         <asp:TemplateField HeaderText="State" SortExpression="CustState">             <ItemTemplate>               <asp:TextBox ID="txtState" runat="server" Text='<%# Bind("CustState") %>' BorderStyle="Solid"BorderWidth="1px"/>             </ItemTemplate>         </asp:TemplateField>       </Columns>       <FooterStyle BackColor="#CCCCCC" ForeColor="Black" />       <PagerStyle BackColor="#999999" ForeColor="Black" HorizontalAlign="Center" />       <SelectedRowStyle BackColor="#008A8C" ForeColor="White" Font-Bold="True" />       <HeaderStyle BackColor="#000084" Font-Bold="True" ForeColor="White" />       <AlternatingRowStyle BackColor="#DCDCDC" />       </asp:GridView>       <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:BlogConnectionString %>"            SelectCommand="SELECT * FROM [Customer]"></asp:SqlDataSource>        <div align="center" style="width: 500px">           <asp:Button ID="btnUpdate" runat="server" Text="Update" OnClick="btnUpdate_Click" />           <br />           <br />        <asp:Label ID="lblError" runat="server"></asp:Label>         </div>

I had created an function named UpdateCustomer, what it will do is first iterate all gridview rows and append in stringbuilder object ,  which it will create an XML structure

StringBuilder sb = new StringBuilder();         sb.Append("<root>"); for (int i = 0; i < gvCustomer.Rows.Count; i++) {    string CustID = gvCustomer.Rows[i].Cells[0].Text;    TextBox txtName = gvCustomer.Rows[i].FindControl("txtName") as TextBox;    TextBox txtPosition = gvCustomer.Rows[i].FindControl("txtPosition") as TextBox;    TextBox txtCity = gvCustomer.Rows[i].FindControl("txtCity") as TextBox;    TextBox txtState = gvCustomer.Rows[i].FindControl("txtState") as TextBox;    sb.Append("<row CustID='" + CustID + "' Name='" + txtName.Text.Trim() + "' Position='" + txtPosition.Text.Trim() +              "' City='" + txtCity.Text.Trim() + "' State='" + txtState.Text.Trim() + "'/>"); } sb.Append("</root>");

Then, I will call usual Sql Connection, Sql Command, pass parameter to database.

string conStr = WebConfigurationManager. ConnectionStrings["BlogConnectionString"].ConnectionString; SqlConnection con = new SqlConnection(conStr); SqlCommand cmd = new SqlCommand("UpdateCustomer", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@XMLCustomer", sb.ToString()); try {   using (con)   {     con.Open();     cmd.ExecuteNonQuery();   }   lblError.Text = "Record(s) updated successfully";   lblError.ForeColor = System.Drawing.Color.Green; } catch (Exception ex) {    lblError.Text = "Error Occured";    lblError.ForeColor = System.Drawing.Color.Red ; }

As mentioned above, I am passing string builder object in XML structure to store procedure. I had created UpdateCustomer store procedure, which mark up looks like below one :-

set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER 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

As mentioned above code, parameter has an XML data type. Which will update customer table from temporary table created from XML.