Passing List<> to SQL Stored Procedure

Question

I've often had to load multiple items to a particular record in the database. For example: a web page displays items to include for a single report, all of which are records in the database (Report is a record in the Report table, Items are records in Item table). A user is selecting items to include in a single report via a web app, and let's say they select 3 items and submit. The process will add these 3 items to this report by adding records to a table called ReportItems (ReportId,ItemId).

Currently, I would do something like this in in the code:

public void AddItemsToReport(string connStr, int Id, List<int> itemList)
{
    Database db = DatabaseFactory.CreateDatabase(connStr);

    string sqlCommand = "AddItemsToReport"
    DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

    string items = "";
    foreach (int i in itemList)
        items += string.Format("{0}~", i);

    if (items.Length > 0)
        items = items.Substring(0, items.Length - 1);

    // Add parameters
    db.AddInParameter(dbCommand, "ReportId", DbType.Int32, Id);
    db.AddInParameter(dbCommand, "Items", DbType.String, perms);
    db.ExecuteNonQuery(dbCommand);
}

and this in the Stored procedure:

INSERT INTO ReportItem (ReportId,ItemId)
SELECT  @ReportId,
          Id
FROM     fn_GetIntTableFromList(@Items,'~')

Where the function returns a one column table of integers.

My question is this: is there a better way to handle something like this? Note, I'm not asking about database normalizing or anything like that, my question relates specifically with the code.

score 37 · Answer 1 · edited Dec 10 '11 at 00:28

37

If going to SQL Server 2008 is an option for you, there's a new feature called "Table-valued parameters" to solve this exact problem.

Check out more details on TVP here and here or just ask Google for "SQL Server 2008 table-valued parameters" - you'll find plenty of info and samples.

Highly recommended - if you can move to SQL Server 2008...

edited Dec 10 '11 at 00:28

answered Jan 21 '09 at 20:48

marc_s

732,580
175
1,330
1,459

Jason Jackson · Answer 2 · 2008-10-16T20:29:09.827

Your string join logic can probably be simplified:

string items = 
    string.Join("~", itemList.Select(item=>item.ToString()).ToArray());

That will save you some string concatenation, which is expensive in .Net.

I don't think anything is wrong with the way you are saving the items. You are limiting trips to the db, which is a good thing. If your data structure was more complex than a list of ints, I would suggest XML.

Note: I was asked in the comments if this would save us any string concatenation (it does indeeed). I think it is an excellent question and would like to follow up on that.

If you peel open string.Join with Reflector you will see that Microsoft is using a couple of unsafe (in the .Net sense of the word) techniques, including using a char pointer and a structure called UnSafeCharBuffer. What they are doing, when you really boil it down, is using pointers to walk across an empty string and build up the join. Remember that the main reason string concatenation is so expensive in .Net is that a new string object is placed on the heap for every concatenation, because string is immutable. Those memory operations are expensive. String.Join(..) is essentially allocating the memory once, then operating upon it with a pointer. Very fast.

Is that really going to save you from concatenation? Do you know if the method was implemented using StringBuilder? — Pablo Marambio, Oct 16 '08 at 18:33
Yes, it will save you from concatenation. String.Join uses an UnsafeCharBuffer to do the concat (look at it in Reflector). As far as the the LINQ goes, that is just enumerating across the list calling ToString(), which is unavoidable, and creating an array. The array creation might be expensive. — Jason Jackson, Oct 16 '08 at 18:55
I didn't know that you could do it like that, thanks for the tip! — Ryan Abbott, Oct 17 '08 at 15:01

score 8 · Answer 3 · answered Oct 16 '08 at 19:21

One potential issue with your technique is that it doesn't handle very large lists - you may exceed the maximum string length for your database. I use a helper method that concatenates the integer values into an enumeration of strings, each of which is less than a specified maximum (the following implementation also optionally checks for and removes duplicates ids):

public static IEnumerable<string> ConcatenateValues(IEnumerable<int> values, string separator, int maxLength, bool skipDuplicates)
{
    IDictionary<int, string> valueDictionary = null;
    StringBuilder sb = new StringBuilder();
    if (skipDuplicates)
    {
        valueDictionary = new Dictionary<int, string>();
    }
    foreach (int value in values)
    {
        if (skipDuplicates)
        {
            if (valueDictionary.ContainsKey(value)) continue;
            valueDictionary.Add(value, "");
        }
        string s = value.ToString(CultureInfo.InvariantCulture);
        if ((sb.Length + separator.Length + s.Length) > maxLength)
        {
            // Max length reached, yield the result and start again
            if (sb.Length > 0) yield return sb.ToString();
            sb.Length = 0;
        }
        if (sb.Length > 0) sb.Append(separator);
        sb.Append(s);
    }
    // Yield whatever's left over
    if (sb.Length > 0) yield return sb.ToString();
}

Then you use it something like:

using(SqlCommand command = ...)
{
    command.Connection = ...;
    command.Transaction = ...; // if in a transaction
    SqlParameter parameter = command.Parameters.Add("@Items", ...);
    foreach(string itemList in ConcatenateValues(values, "~", 8000, false))
    {
        parameter.Value = itemList;
        command.ExecuteNonQuery();
    }
}

Good point, I hadn't thought about exceeding the string lengths. Though I doubt I'll have cases that even come close, it's good to learn this, thanks! — Ryan Abbott, Oct 17 '08 at 15:02

Kev · Answer 4 · 2014-01-19T09:52:22.293

You either do what you've already got, pass in a delimited string and then parse out to a table value, or the other choice is passing in a wodge of XML and kinda much the same:

http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx

I haven't had a chance to look at SQL 2008 yet to see if they've added any new functionality to handle this type of thing.

score 5 · Answer 5 · edited Mar 13 '19 at 11:19

5

Why not use a table-valued parameter? https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/table-valued-parameters

edited Mar 13 '19 at 11:19

Kols

3,641
2
34
42

answered Jun 11 '10 at 20:06

GaTechThomas

5,421
5
43
69

Because it requires User Defined Table Type, which may stay in db permanently unless you guarantee that you delete this type after every invocation. – alpav Nov 08 '12 at 17:31
They're kind of difficult to manage, if you have to script changes to a live deployment for example. – philw Aug 05 '13 at 07:53

score 2 · Answer 6 · answered Oct 16 '08 at 19:26

2

See http://www.sommarskog.se/arrays-in-sql-2005.html for a detailed discussion of this issue and the different approaches that you could use.

answered Oct 16 '08 at 19:26

Phillip Wells

7,402
9
43
41

score 2 · Answer 7 · answered Sep 28 '10 at 14:24

2

Here's a very clear-cut explanation to Table Valued Parameters from sqlteam.com: Table Valued Parameters

answered Sep 28 '10 at 14:24

dotnetN00b

5,021
13
62
95

score 1 · Answer 8 · edited Jan 23 '12 at 20:30

1

Query a Single Field for Multiple Values in a Stored Procedure
http://www.norimek.com/blog/post/2008/04/Query-a-Single-Field-for-Multiple-Values-in-a-Stored-Procedure.aspx

edited Jan 23 '12 at 20:30

Robert Harvey

178,213
47
333
501

answered Oct 16 '08 at 23:04

Robert C. Barth

22,687
6
45
52

Passing List<> to SQL Stored Procedure

8 Answers8

Linked

Related