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.

list

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)

)

 

Print | posted on Monday, November 19, 2007 3:52 PM

Feedback

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

left by Dexter Zafra at 5/17/2009 9:54 AM Gravatar
I like your approach. In some of our projects, we passed in a CSV value to the sproc, and a SQL function will return a table of all the IDs. Pretty cool too. Sample code.
/***********************************************
* Samplae usage:
* DECLARE @CSV varchar(100)
* SET @CSV = '1,2,3,4,5,6,7,8,9,10'
* SELECT * from dbo.fnConvertCSVToINT(@CSV)
***********************************************/

ALTER FUNCTION [dbo].[fnConvertCSVToINT]
(
@CSVArray varchar(5000)
)
RETURNS @Table Table (ID int)
AS

BEGIN

DECLARE @separator char(1)
SET @separator = ','

DECLARE @pos int
DECLARE @arrvalue varchar(1000)

SET @CSVArray = @CSVArray + ','

WHILE PATINDEX('%,%' , @CSVArray) <> 0
BEGIN
SELECT @pos = PATINDEX('%,%' , @CSVArray)
SELECT @arrvalue = LEFT(@CSVArray, @pos - 1)

INSERT @Table
VALUES (CAST(@arrvalue AS int))

SELECT @CSVArray = STUFF(@CSVArray, 1, @pos, '')
END

RETURN

END
Post A Comment
Title:
Name:
Email:
Comment:
Verification: