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 (<list of
IDs>)
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 = '<UserTaskList>
<UserTaskData>
<UserID>1</UserID>
<TaskID>100</TaskID>
</UserTaskData>
<UserTaskData>
<UserID>2</UserID>
<TaskID>200</TaskID>
</UserTaskData>
</UserTaskList>'
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("<UserTaskList>");
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("<UserTaskData>");
XMLString.AppendFormat("<UserID>{0}</UserID>",
UserCheckBoxList.Items[iUserCount].value);
XMLString.AppendFormat("<TaskID>{0}</TaskID>",
UserCheckBoxList.Items[iUserCount].value);
XMLString.AppendFormat("</UserTaskData>");
}
}
}
}
XMLString.AppendFormat("</UserTaskList>");
}
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.