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. |
Screen Shot 1 (Before Update)
Screen Shot 2 ( After Update)
| As you can see in second screen shot, I had update customer Name field with all surname prefix with first name. I hope you like this bulk update with gridview and sql server xml. Download source code available on top. |