Efficient Batch Operation From ASP.NET 2.0 Using SQL Server 2005 XML Data Type

One of the good practice while developing high performance web application is to reduce the database roundtrip as much as possible. In the current trend of web applications, its a very common to enable users to delete multiple items in a list user interface at a time.

To perform this type of batch delete operation, one common mistake that happens by beginner developers are to calling the data access method repetitively for each item with in a loop as below:

For Each row As GridViewRow In gvEmployees.Rows
            ' Access the CheckBox
            Dim cb As CheckBox = CType(row.FindControl("chkEmployeeSelector"), CheckBox)
            If cb IsNot Nothing AndAlso cb.Checked Then
                ' First, get the EmployeeID for the selected row
                Dim employeeId As SqlInt32 = CType(gvEmployees.DataKeys(row.RowIndex).Value, SqlInt32)

                ' Deleting a employee...
                DAL.Employee.Delete.(employeeId)

            End If
Next

A good approach regarding this issue is to send all of the employee id's at a time to the database stored procedure, so that all the deletion operation can be handled at a time, and thus reducing the cost with respect to database roundtrip! BUT one basic problem with this approach is SQL Server doesn't support array, and thus we can't pass the list of ID's as an array! Then?

Oh yes! We have XML data type available is SQL Server 2005, well we can utilize that concept easily. How? Easy! Just to create the list as an xml and then pass to the corresponding stored procedure. The stored procedure will extract the xml accordingly and perform required operation individually on each item.

Application End:

The method below converts a .net generic list to a xml representation of list of Id's which is passed to stored procedure to perform required batch operation: 

public static string FormatXMLForIdArray(System.Collections.Generic.List<SqlInt32> idsToList)
        {
            //converting the list to xml first
            StringBuilder xmlString = new StringBuilder();
            for (int i = 0; i < idsToList.Count; i++)
            {
                xmlString.AppendFormat("<Id>{0}</Id>", idsToList [ i ] );
            }

            return xmlString.ToString();
        }

Stored Procedure End:

ALTER PROCEDURE spr_Employee_DeleteEmployeeList
@EmployeeSystemUserIdList xml
AS

DELETE App_Employee
WHERE EmployeeSystemUserId in
(
    SELECT ParamValues.EmployeeSystemUserId.value('.','Int') as Id
    FROM @EmployeeSystemUserIdList.nodes('/EmployeeSystemUserId') as ParamValues(EmployeeSystemUserId)
)