Passing multiple IDs to the database from ASP.NET Application

SheoNarayan
Posted by in ASP.NET category on for Beginner level | Points: 150 | Views : 6849 red flag
Rating: 4.33 out of 5  
 3 vote(s)

Many a time we come into the situation where we need to pass ProductIds or ItemIds separated by comma from UI to the database. The first thing that comes in our mind is its easy by using IN keyword in the SQL Server but this is not as easy as it looks like. In this article, I have shown how to handle this situation easily in the client side itself.


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.

Page copy protected against web site content infringement by Copyscape

About the Author

SheoNarayan
Full Name: Sheo Narayan
Member Level: HonoraryPlatinum
Member Status: Administrator
Member Since: 7/8/2008 6:32:14 PM
Country: India
Regards, Sheo Narayan http://www.dotnetfunda.com
http://www.snarayan.com
Ex-Microsoft MVP, Author, Writer, Mentor & architecting applications since year 2001. Connect me on http://www.facebook.com/sheo.narayan | https://twitter.com/sheonarayan | http://www.linkedin.com/in/sheonarayan

Login to vote for this post.

Comments or Responses

Login to post response

Comment using Facebook(Author doesn't get notification)