Pass table value param to stored procedure using PetaPoco

Question

For while I am trying to call SQL Server 2008 R2 stored procedure using PetaPoco.

My stored procedure accepts a table valued parameter.

How I can call the stored procedure in petapoco with table value param?

Here 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 :

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect.
Parameter 3 ("@0"): Data type 0x62 (sql_variant) has an invalid type for type-specific metadata.

If I set parametar ty value

param.SqlDbType = SqlDbType.Structured;

Then I get exception like

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

When I define my param like

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

Then I get exception

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

How I can define SqlParam with table valued param so I can send it whit data to SQL Server?

Solution:

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

Helicograph

While I can't say the above doesn't work. I couldn't get a named parameter working in my solution. I ended up just doing a @0 instead. db.Query<Thing>("INNER JOIN @0 rowIds ON ...", param).ToList() – Montano

Answer 1

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

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

The SqlDbType.Structured is the key to this. Don't use DbType.Object.

Electromagnet

I did it and Now I have different exception – Helicograph