Pass the value of a table parameter to a stored procedure using PetaPoco

Question

While I'm trying to call a SQL Server 2008 R2 stored procedure using PetaPoco.

My stored procedure accepts a table dependent parameter.

How can I call a stored procedure in petapoco with the value parameter of a table?

Here is what I am trying to do:

var db = new PetaPoco.Database("repikaciskaBaza"); DataTable table = new DataTable(); DataColumn id = table.Columns.Add("id", type: typeof(Int32)); for (int i = 0; i < 10;i++ ) { DataRow row = table.NewRow(); row["id"] = i; table.Rows.Add(row); } var param = new SqlParameter(); param.DbType = DbType.Object; param.ParameterName = "@art_id"; param.SqlValue = table; var lista = db.Query<pocoArts>(";exec dbo.test_sporc_param @0", param); 

This code gives me an exception:

Invalid tabular data stream (TDS) (RPC) protocol flow is invalid. Parameter 3 ("@ 0"): Data type 0x62 (sql_variant) has an invalid type for type metadata.

If I set the parameter value

 param.SqlDbType = SqlDbType.Structured; 

Then I get an exception like

 The table type parameter '@0' must have a valid type name. 

When I define my parameter as

  param.SqlDbType = SqlDbType.Structured; param.SqlValue = table; param.ParameterName = "@art_id"; param.TypeName = SqlDbType.Structured.ToString(); 

Then i get an exception

Column, parameter or variable @ 0 .: Cannot find the structured data type.

How can I define a SqlParam with a table value parameter so that I can send whit data to SQL Server?

Decision

 var param = new SqlParameter(); param.SqlDbType = SqlDbType.Structured; // According to marc_s param.SqlValue = table; param.ParameterName = "@art_id"; param.TypeName = "dbo.typ_art_id"; // this is TYP from SQL Server database it needs to be equal to type defined in SQL Server not type of param 

Answer 1

According to the relevant MSDN documentation for the table-valued parameter , you should use:

 var param = new SqlParameter(); param.SqlDbType = SqlDbType.Structured; 

The key to this is SqlDbType.Structured . Do not use DbType.Object .