How to Bulk Update Using SQL Server XML

[AmitIper]

This Post is used to show how to bulk update records from grid view rather to updating one by one.

The concept : create a xml string(string will contain all the records to be updated with some separator ) from code behind and pass this string to stored procedure from where these records will be updated to appropriate table by reading data from XML data type variable and update will be done using temporary table concept.
I have used C# sample code for Example:


Table Structure:

CustId Int

CustName Varchar(50)

CustPosition Varchar(50)

CustCity Varchar(50)

CustState Varchar(50)

Grid:

First

The Grid View is designed as below.

<div>
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=”data source=NSL-SEG089D\SQLEXPRESS;database=bankdb;Integrated Security=SSPI”

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” Font-Bold=”True” ForeColor=”Black” Width=”544px”></asp:Label>

</div>

</div>

Below is the OnClick event code of update button.

createXml();
string conStr = “data source=YOURDATASOURCE;database=DBNAME;Integrated Security=SSPI”;
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;
}
}

Function below is called on clicking of Update button to create a xml string of all the records to be updated.

public void createXml()
{
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>”);
}

Ultimately the Stored Procedure (updat Customer) used is as below

The parameter used in proc is an XML data type. Which will update customer table from temporary table created from XML.

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 you can see in screen shot below, I had update customer Name field with all surname prefix with first name. hope it will help you to bulk update the records.