How to pass table value parameters to stored procedure from net code

Question

I have a 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

User · Answer

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 lt int gt  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   gt   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

User · Answer

Use this code to create suitable parameter from your type   private SqlParameter GenerateTypedParameter string name  object typedParameter        DataTable dt   new DataTable         var properties   typedParameter GetType   GetProperties   ToList        properties ForEach p   gt                dt Columns Add p Name  Nullable GetUnderlyingType p PropertyType     p PropertyType               var row   dt NewRow        properties ForEach p   gt    row p Name     p GetValue typedParameter     DBNull Value           dt Rows Add row        return new SqlParameter               Direction   ParameterDirection Input          ParameterName   name          Value   dt          SqlDbType   SqlDbType Structured

User · Answer

DataTable  DbDataReader  or IEnumerable lt SqlDataRecord gt  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 lt SqlDataRecord gt    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 lt long gt  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 lt long gt  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 lt SqlDataRecord gt  CreateSqlDataRecords IEnumerable lt long gt  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

User · Answer

Generic     public static DataTable ToTableValuedParameter lt T  TProperty gt  this IEnumerable lt T gt  list  Func lt T  TProperty gt  selector                var tbl   new DataTable            tbl Columns Add  Id   typeof T             foreach  var item in list                        tbl Rows Add selector Invoke item                        return tbl

User · Answer

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

[c#] How to pass table value parameters to stored procedure from .net code

Examples related to c#

Examples related to sql-server

Examples related to stored-procedures

Examples related to sqlcommand

Examples related to table-valued-parameters