Passing lists to SQL server stored procedures

Passing lists to SQL server stored procedures

Article is about:

The ability to pass "a list of values" from .Net as a parameter to a T-SQL based stored procedure.

Scenarios:

There are lots of scenarios where we need to pass a list of values to save in database. Here's a couple of obvious ones:

• INSERT a list of values into the database in one "chunky" call (e.g. some IDs from a CheckBoxList)
• SELECT rows where IDs are IN ()

Some general Approaches:

Taking the INSERT statements as an example, there are various general approaches that we adopt to achieve the desired result:

• Use dynamic / Inline SQL! But ideally say, dynamic / Inline SQL is rarely the ideal solution for obvious reasons.
• Make a stored proc call for each ID to insert. This is the most common approach we see in various projects, mainly because it is the easiest to implement. The drawback of course is if we were to insert 60 values, it would result in 60 "chatty" calls to the database.
• Pass comma separated values via a VARCHAR (or similar) parameter. This works fine but has messy "string splitting" in the stored procedure to extract the IDs and then build the SQL statement in the procedure itself. Prone to SQL injection and not the best performance.
• Pass the list as an XML parameter. This is nicer and is the point of this article.

Coming to the main Point, Using XML:

Using XML for "list passing" has a number of benefits, in particular the ability to pass lists of more "complex types" rather than just single values.

Lets take an example. Suppose we are having 2 CheckedListBox; one is list of Users and another is the list of tasks / roles that can be assigned to Users. We want to store the values in Table which is having Fields UserID and TaskID. The Stored Procedure will accept Parameter with XML Datatype as,

CREATE PROCEDURE [dbo].[usp_InsertUserTask]
@UserTaskXML XML
AS
BEGIN
INSERT INTO UserTasks (UserID,TaskID)
SELECT
UserTaskTab.UserTaskCol.value('UserID[1]','int') AS UserID,
UserTaskTab.UserTaskCol.value('TaskID[1]','int') AS TaskID
FROM @UserTaskXML.nodes('//UserTaskList/UserTaskData') AS UserTaskTab(UserTaskCol)
END

To call this in Stored Procedure, you would have something like this:

EXEC [dbo].[usp_InsertUserTask]
@UserTaskXML = '

1
100


2
200

'

In your application, your C# calling code could be:

SqlConnection sqlCN = new SqlConnection();
sqlCN.ConnectionString = ConfigurationManager.AppSettings["DBConn"].ToString();
string strQuery = "usp_InsertUserTask";
SqlParameter[] sqlParams = new SqlParameter[1];
sqlParams[0] = new SqlParameter("@UserTaskXML", GetStudyDataXMLString());
SqlHelper.ExecuteNonQuery(sqlCN, CommandType.StoredProcedure, strQuery, sqlParams);
if (sqlCN.State == ConnectionState.Open)
sqlCN.Close();
sqlCN.Dispose();

which calls the method below to translate the UserID and TaskID from CheckBoxLists into an XML String:

private string GetUserTaskListXML()
{
try
{
StringBuilder XMLString = new StringBuilder();
XMLString.AppendFormat("");
for (int iUserCount = 0; iUserCount < UserCheckBoxList.Items.Count; iUserCount++)
{
if(UserCheckBoxList.Items[iUserCount].Selected)
{
for (int iTaskCount = 0; iTaskCount < TaskCheckBoxList.Items.Count; iTaskCount++)
{
if(TaskCheckBoxList.Items[iTaskCount].Selected)
{
XMLString.AppendFormat("");
XMLString.AppendFormat("{0}", UserCheckBoxList.Items[iUserCount].value);
XMLString.AppendFormat("{0}", UserCheckBoxList.Items[iUserCount].value);
XMLString.AppendFormat("");
}
}
}
}
XMLString.AppendFormat("");
}
catch (Exception Ex)
{
throw Ex;
}
return XMLString.ToString();
}

Here, StringBuilder is used for the xml concatenation as in this case I think it fits the bill but purists might prefer an XmlTextWriter approach. In summary, it performs very well and is adaptable for various lists of objects and more complex structures.