By Marek Kwiendacz


2011-04-08 12:56:20 8 Comments

I have an MS SQL Server 2005 database. In a few procedures I have table parameters that I pass to a stored proc as an nvarchar (separated by commas) and internally divide into single values. I add it to the SQL command parameters list like this:

cmd.Parameters.Add("@Logins", SqlDbType.NVarchar).Value = "jim18,jenny1975,cosmo";

I have to migrate the database to SQL Server 2008. I know that there are table value parameters, and I know how to use them in stored procedures. But I don't know how to pass one to the parameters list in an SQL command. Does anyone know correct syntax of the Parameters.Add procedure? Or is there another way to pass this parameter?

4 comments

@Ryan Prechel 2012-05-28 05:03:35

DataTable, DbDataReader, or IEnumerable<SqlDataRecord> objects can be used to populate a table-valued parameter per the MSDN article Table-Valued Parameters in SQL Server 2008 (ADO.NET).

The following example illustrates using either a DataTable or an IEnumerable<SqlDataRecord>:

SQL Code:

CREATE TABLE dbo.PageView
(
    PageViewID BIGINT NOT NULL CONSTRAINT pkPageView PRIMARY KEY CLUSTERED,
    PageViewCount BIGINT NOT NULL
);
CREATE TYPE dbo.PageViewTableType AS TABLE
(
    PageViewID BIGINT NOT NULL
);
CREATE PROCEDURE dbo.procMergePageView
    @Display dbo.PageViewTableType READONLY
AS
BEGIN
    MERGE INTO dbo.PageView AS T
    USING @Display AS S
    ON T.PageViewID = S.PageViewID
    WHEN MATCHED THEN UPDATE SET T.PageViewCount = T.PageViewCount + 1
    WHEN NOT MATCHED THEN INSERT VALUES(S.PageViewID, 1);
END

C# Code:

private static void ExecuteProcedure(bool useDataTable, 
                                     string connectionString, 
                                     IEnumerable<long> ids) 
{
    using (SqlConnection connection = new SqlConnection(connectionString)) 
    {
        connection.Open();
        using (SqlCommand command = connection.CreateCommand()) 
        {
            command.CommandText = "dbo.procMergePageView";
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter parameter;
            if (useDataTable) {
                parameter = command.Parameters
                              .AddWithValue("@Display", CreateDataTable(ids));
            }
            else 
            {
                parameter = command.Parameters
                              .AddWithValue("@Display", CreateSqlDataRecords(ids));
            }
            parameter.SqlDbType = SqlDbType.Structured;
            parameter.TypeName = "dbo.PageViewTableType";

            command.ExecuteNonQuery();
        }
    }
}

private static DataTable CreateDataTable(IEnumerable<long> ids) 
{
    DataTable table = new DataTable();
    table.Columns.Add("ID", typeof(long));
    foreach (long id in ids) 
    {
        table.Rows.Add(id);
    }
    return table;
}

private static IEnumerable<SqlDataRecord> CreateSqlDataRecords(IEnumerable<long> ids) 
{
    SqlMetaData[] metaData = new SqlMetaData[1];
    metaData[0] = new SqlMetaData("ID", SqlDbType.BigInt);
    SqlDataRecord record = new SqlDataRecord(metaData);
    foreach (long id in ids) 
    {
        record.SetInt64(0, id);
        yield return record;
    }
}

@lc. 2013-01-25 09:52:29

+1 Excellent example. Takeaways are: send a DataTable as the parameter value, set SqlDbType to Structured and TypeName to the database UDT name.

@Søren Boisen 2016-01-21 11:42:23

If you are going to reuse an instance of a reference type in a loop (SqlDataRecord in your example), please please add a comment on why it is safe to do so in this particular instance.

@ta.speot.is 2016-03-13 23:09:02

This code is wrong: empty table valued parameters should have their value set to null. CreateSqlDataRecords will never return null if given an empty ids parameter.

@Crono 2016-04-18 19:21:53

Also, DataTable is a IDisposable and hence its Dispose method should be called before it goes out of scope.

@Rango 2016-07-19 08:56:34

@Crono: DataTable(or DataSet) only implement it because they have to suppiort drag&drop capabilities in Visual-Studio, so they implement IComponent which implements IDisposable. If you don't use the designer but create it manually there's no reason to dispose it (or to use the using-statement). So this is one of the exceptions of the golden rule "dispose everything that implements IDisposable".

@Crono 2016-08-30 16:58:16

@TimSchmelter As a rule of thumb I always call Dispose methods, even if it's only so that Code Analysis won't warn me if I don't. But I agree that in this specific scenario where base DataSet and DataTable instances are used, calling Dispose wouldn't do anything.

@Brian 2017-12-04 18:59:26

@SørenBoisen: According to the remarks section of msdn.microsoft.com/en-us/library/… , "When writing common language runtime (CLR) applications, you should re-use existing SqlDataRecord objects instead of creating new ones every time. ". This doesn't always apply, but it probably applies here.

@AgentFire 2018-03-03 09:58:23

Future Users, please note this: DataTable approach consumes the hell of a memory, whereas the IEnumerable one simply does not.

@Rui Taborda 2018-07-10 09:14:24

@AgentFire Is this only for situations where you need to send a lot of data in that DataTable or for all? In my use case I'll just need to send a table with one column and no more than 10 rows

@AgentFire 2018-07-10 15:21:59

@RuiTaborda when you need to send lots of data, use IEnumerable approch. Otherwise, use whatever.

@Shahzad Qureshi 2015-04-24 21:45:06

The cleanest way to work with it. Assuming your table is a list of integers called "dbo.tvp_Int" (Customize for your own table type)

Create this extension method...

public static void AddWithValue_Tvp_Int(this SqlParameterCollection paramCollection, string parameterName, List<int> data)
{
   if(paramCollection != null)
   {
       var p = paramCollection.Add(parameterName, SqlDbType.Structured);
       p.TypeName = "dbo.tvp_Int";
       DataTable _dt = new DataTable() {Columns = {"Value"}};
       data.ForEach(value => _dt.Rows.Add(value));
       p.Value = _dt;
   }
}

Now you can add a table valued parameter in one line anywhere simply by doing this:

cmd.Parameters.AddWithValueFor_Tvp_Int("@IDValues", listOfIds);

@Muflix 2016-08-31 12:53:52

what if the paramCollection is NULL ? How to pass empty type ?

@Rhumborl 2017-03-16 21:41:09

@Muflix Obscurely, extension methods actually work against null instances. So adding a simple if(paramCollection != null) check at the top of the method will be fine

@Shahzad Qureshi 2017-03-25 02:53:35

Updated answer with initial -if- check

@Francis Lord 2017-05-25 14:47:11

Maybe a bit pedantic, but I'd use IEnumerable instead of List in the signature, that way you can pass anything that is IEnumerable, not just lists, Since you're not using any function specific to List, I don't really see a reason not to us IEnumerable

@Shahzad Qureshi 2017-05-30 06:02:19

Using List allows you to use the shorthand data.ForEach(), otherwise you'd have to actually write a foreach loop. Which could work also, but I like writing things as short as possible.

@Scotty.NET 2013-11-26 15:45:23

Further to Ryan's answer you will also need to set the DataColumn's Ordinal property if you are dealing with a table-valued parameter with multiple columns whose ordinals are not in alphabetical order.

As an example, if you have the following table value that is used as a parameter in SQL:

CREATE TYPE NodeFilter AS TABLE (
  ID int not null
  Code nvarchar(10) not null,
);

You would need to order your columns as such in C#:

table.Columns["ID"].SetOrdinal(0);
// this also bumps Code to ordinal of 1
// if you have more than 2 cols then you would need to set more ordinals

If you fail to do this you will get a parse error, failed to convert nvarchar to int.

@Martea 2014-02-07 08:47:48

Generic

   public static DataTable ToTableValuedParameter<T, TProperty>(this IEnumerable<T> list, Func<T, TProperty> selector)
    {
        var tbl = new DataTable();
        tbl.Columns.Add("Id", typeof(T));

        foreach (var item in list)
        {
            tbl.Rows.Add(selector.Invoke(item));

        }

        return tbl;

    }

@GDroid 2015-03-18 20:59:31

Would you please let me know that what do I pass as parameter? Func<T, TProperty> selector? Can't it be simply tbl.Rows.Add(item) and no need of that parameter.

@Martea 2015-03-20 16:44:11

the selector.Invoke(item) selects the property on the item most cases its a int, but it also allows you to select a string property

@GDroid 2015-03-23 03:31:28

can you please provide an example of how do I put selector over there?? I have a List<Guid> to pass to stored proc...

@Martea 2015-04-24 11:55:23

guidList.ToTabledValuedParameter(x=>x), since x is the guid in your case, the return will be a DataTable with one column(id) with a list of guids,

Related Questions

Sponsored Content

20 Answered Questions

26 Answered Questions

20 Answered Questions

7 Answered Questions

[SOLVED] C# SQL Server - Passing a list to a stored procedure

9 Answered Questions

[SOLVED] How to pass an array into a SQL Server stored procedure

6 Answered Questions

6 Answered Questions

[SOLVED] T-SQL stored procedure that accepts multiple Id values

2 Answered Questions

Sponsored Content