Passing multiple IDs to the database from ASP.NET Application

For example, consider below form where user need to input Comma Separated ProductIds and based on it, he/she should get the results.

The problem

Normally what we will think is its an easy task, if we write Sql query like below in the stored procedure, it would give us the correct result.

select * from Product where ProductId in (10, 8, 7, 15)

Actually this is correct but this will give correct result only if you are directly writing this in SQL Server, if you will  pass 10,8,7,15 as input parameter to the stored procedure, it will not give you the correct result.

Another way we could have tried is following

select * from Product where ProductId in ('10', '8', '7', '15')

Get solutions of your .NET problems with video explanations, .pdf and source code in .NET How to's.

However, when you will pass '10', '8', '7', '15' as input parameter it will again not give correct output.

There are few solutions available if you google it and they suggest to create a User Defined Function (UDF) that will split the input parameter separated by comma and give the output that you can use with IN keyword that will work for you, however creating a UDF is again a concern for many developers and they do not want to do it considering a separate dependent object in order to run the stored procedure and plus a lot of other maintainence headache and deployment issues.

The easiest solution

The solution I am going to provide here is not very very new but I found it very useful that will avoid the use of UDFs and its very straight forward too.

Here, I have split the comma separated values given by user and build a xml string in the front end, the code to build a simple xml is below. I could have used XmlTextWrite as well but generating my xml was very easy so I have used StringBuilder (use System.Text namespace) and manually write the node I wanted to have in the xml.

// format the productids
string[] ids = txtProductIds.Text.Trim().Split(',');
StringBuilder strb = new StringBuilder("<ProductIds>", 1000);
foreach (string s in ids)
{
strb.Append("<ProductId>" + s + "</ProductId>");
}
strb.Append("</ProductIds>");

The above code will output the xml string something like below

<ProductIds>
<ProductId>10</ProductId>
<ProductId>8</ProductId>
<ProductId>7</ProductId>
<ProductId>15</ProductId>
<ProductId></ProductId>
</ProductIds>

Now, what I need to do is pass this xml string as input parameter to my Data Access Layer, notice that I have used the data type as SqlDbType.Xml as my input parameter is a xml string.

/// <summary>
/// get products 
/// </summary>
/// <param name="productIds">Comma separated string values of product ids</param>
/// <returns></returns>
public DataTable GetProductsByPageIds(string productIds)
{
SqlParameter[] prms = null;
prms = new SqlParameter[1];
prms[0] = new SqlParameter("@ProductIds", SqlDbType.Xml);
prms[0].Value = productIds;
 
return SqlServerHelper.ExecuteDataTable(ConnStr, CommandType.StoredProcedure, "GetProducts", prms);
}

My stored procedure looks like below, here I have used IN keyword too but underneath instead of creating a UDF, I have parsed my xml string containing ProductIds (input parameter) to its values.

ALTER PROCEDURE [dbo].[GetProducts]
@ProductIds xml
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
select * from product where ProductId in (SELECT
ParamValues.ID.value('.','VARCHAR(20)')
FROM @ProductIds.nodes('/ProductIds/ProductId') as ParamValues(ID) )
END

Above stored procedure gives me the exact same result as I would have got using UDFs but I avoided it by passing the xml string as input parameter to the stored procedure and exploiting the parsing xml ability of SQL Server 2005 onwards versions.

Conclusion

By passing the xml string as input parameter, I avoided the need of creating a UDF, someone may ask what about the performance, I would say do not worry as SQL Server 2005 and greater versions are well capable of parsing the xml strings so the performance will not be noticeable.