SQL Server and .NET Custom Table Type

Question

I needed to pass an integer array to a stored procedure from .NET, and so I searched for a topic and ended up with Arrays and Lists in SQL Server 2008 written by Erland Sommarskog and supposedly considering the standard way this process happens.

I tried two different ways to pass a custom table type to a stored procedure, but I get exceptions with each of them. Both of these manners are similar to what Erland Sommarskog uses in the link above.

Manner # 1 - Use DataTable as SqlParameter

DataTable dt = new DataTable(); dt.Columns.Add("n", typeof(int)); // Just adding 3 test rows to the DataTable DataRow dr = dt.NewRow(); dr["n"] = 1; dt.Rows.Add(dr); dr = dt.NewRow(); dr["n"] = 2; dt.Rows.Add(dr); dr = dt.NewRow(); dr["n"] = 3; dt.Rows.Add(dr); // Creation of the SqlParameter SqlParameter p = new SqlParameter(); p.ParameterName = "@ids"; p.Direction = ParameterDirection.Input; p.SqlDbType = SqlDbType.Structured; p.TypeName = "lstInt_TblType"; p.Value = dt; // Blows up here DataSet ds = DAWrapper.GetDataSet( Common.GetDB(), "usp_Test", new SqlParameter[] { p }); 

Exception I receive: Invalid Remote Procedure Inbound Data Protocol (TDS) protocol flow (RPC). Parameter 1 ("@ids"): Data type 0x62 (sql_variant) has an invalid type for type metadata.

Manner 2 - Use List as SqlParameter

 List<SqlDataRecord> lstSDR = new List<SqlDataRecord>(); SqlMetaData[] tvp_definition = { new SqlMetaData("n", SqlDbType.Int) }; // Just adding 3 test rows SqlDataRecord rec = new SqlDataRecord(tvp_definition); rec.SetInt32(0, 50); lstSDR.Add(rec); rec = new SqlDataRecord(tvp_definition); rec.SetInt32(0, 51); lstSDR.Add(rec); rec = new SqlDataRecord(tvp_definition); rec.SetInt32(0, 52); lstSDR.Add(rec); // Creation of the SqlParameter SqlParameter p = new SqlParameter(); p.ParameterName = "@ids"; p.Direction = ParameterDirection.Input; p.SqlDbType = SqlDbType.Structured; p.TypeName = "lstInt_TblType"; p.Value = lstSDR; // Blows up here DataSet ds = DAWrapper.GetDataSet( Common.GetDB(), "usp_Test", new SqlParameter[] { p }); 

And the exception that I get for this: There is no object type matching System.Collections.Generic.List`1 [[Microsoft.SqlServer.Server.SqlDataRecord, System.Data, Version = 4.0.0.0, Culture = neutral, PublicKeyToken = b77a5c561934e089] ] a well-known managed provider.

Other information lstInt_TblType is a user-defined table type on my SQL Server 2008. It exists (I checked this three times!). It has one column named "n", of type int, a primary key and does not allow zeros. I copied exactly how Erland installed it.

I also confirmed that the usp_Test stored procedure works from SQL Server Manager Studio, so I'm pretty sure that no exceptions are thrown from this direction. This is the t-sql that I used to test the stored procedure:

 DECLARE @ids lstInt_TblType INSERT @ids(n) VALUES(1),(2),(3) EXEC usp_Test ids 

Any suggestions on where to go with this would be greatly appreciated. Thanks!

* EDIT: * usp_Test stored procedure:

 ALTER PROCEDURE [dbo].[usp_Test] ( @ids lstInt_TblType READONLY ) AS BEGIN SET NOCOUNT ON; select * from dbo.dat_MetaData where MetaDataTypeID in (select n from @ids) END GO 

Answer 1

Found another way to do this. This method uses the System.Data.SqlClient libraries to create a database connection, specify the name of the stored procedure, and then pass the parameter as a DataTable, which serves as the type of SQL Server table defined by the user.

 using (SqlConnection conn = new SqlConnection(connStr)) { SqlCommand cmd = conn.CreateCommand(); cmd.CommandType = System.Data.CommandType.StoredProcedure; cmd.CommandText = "dbo.usp_Test"; cmd.Parameters.AddWithValue("@ids", dt); conn.Open(); using (SqlDataReader sqlReader = cmd.ExecuteReader()) { DataTable retTbl = new DataTable(); retTbl.Load(sqlReader); } } 

Answer 2

You can also find good examples of how to transfer table parameter data to a stored procedure in your own Microsoft link - at http://msdn.microsoft.com/en-us/library/bb675163.aspx