Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

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?

like image 756
Marek Kwiendacz Avatar asked Apr 08 '11 12:04

Marek Kwiendacz


People also ask

Can we pass table variable as parameter in stored procedure?

You cannot pass table-valued parameters to CLR user-defined functions. Table-valued parameters can only be indexed to support UNIQUE or PRIMARY KEY constraints.


1 Answers

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;     } } 
like image 153
Ryan Prechel Avatar answered Oct 20 '22 05:10

Ryan Prechel