Geeks With Blogs

News Ashraful Alam Joy

Create Your Badge

Ashraful Alam is a Software Architect, who has 8 years of professional experience in Software Development industry. This Bangladeshi national is involved with project management and development of several US based software projects from his country. Already he has managed and developed several software projects, which are being used by several users of different countries, such as USA, Canada, Australia, and Bangladesh. While developing and managing a team, he contains and maintains a set of well defined engineering practices developed by him and other online developer communities.

Due to his willingness to give effort to improve and share better software development practices, Ashraf has been awarded as “Most Valuable Professional” (MVP) in ASP.NET category by Microsoft since year 2007 multiple times, which is a rare honor and prestigious reorganization among the developers around the world.

Check his portfolio to know more about him and his works.

.NETTER Characters... Every part of your life is best, if you can know yourself and thus create your life like an artist!

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...



            End If


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




DELETE App_Employee

WHERE EmployeeSystemUserId in


    SELECT ParamValues.EmployeeSystemUserId.value('.','Int') as Id

    FROM @EmployeeSystemUserIdList.nodes('/EmployeeSystemUserId') as ParamValues(EmployeeSystemUserId)



Posted on Monday, November 19, 2007 3:52 PM Implementation , Database , .NET , Software Development | Back to top

Comments on this post: Efficient Batch Operation From ASP.NET 2.0 Using SQL Server 2005 XML Data Type

# re: Efficient Batch Operation From ASP.NET 2.0 Using SQL Server 2005 XML Data Type
Requesting 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)

@CSVArray varchar(5000)
RETURNS @Table Table (ID int)


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

DECLARE @pos int
DECLARE @arrvalue varchar(1000)

SET @CSVArray = @CSVArray + ','

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

VALUES (CAST(@arrvalue AS int))

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


Left by Dexter Zafra on May 17, 2009 9:54 AM

Your comment:
 (will show your gravatar)

Copyright © Ashraf Alam | Powered by: