使用PetaPoco将表值参数传递到存储过程 [英] Pass table value param to stored procedure using PetaPoco

问题描述

当我尝试使用PetaPoco调用SQL Server 2008 R2存储过程时。

我的存储过程接受一个表值参数。

如何使用表值参数在petapoco中调用存储过程？

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）; 
  

这段代码给我一个例外：

传入表格数据流（TDS）远程过程调用（RPC）协议流不正确。

参数3（@ 0）：数据类型0x62（sql_variant）

如果我设置参数值

  param.SqlDbType = SqlDbType.Structured; 
  

然后我得到例外

 表类型参数@ 0必须具有有效的类型名称。 
 

当我定义我的参数，如

  param.SqlDbType = SqlDbType.Structured; 
 param.SqlValue = table; 
 param.ParameterName =@art_id; 
 param.TypeName = SqlDbType.Structured.ToString（）; 
  

然后我得到异常

列，参数或变量@ 0。 ：找不到数据类型结构化。

如何用表格定义 SqlParam

解决方案：

pre> var param = new SqlParameter（）;
param.SqlDbType = SqlDbType.Structured; //根据marc_s
param.SqlValue = table;
param.ParameterName =@art_id;
param.TypeName =dbo.typ_art_id; //这是来自SQL Server数据库的TYP，它需要等于SQL Server中定义的类型不是参数类型


解决方案

根据相关的MSDN文档对于表值参数，应该使用：

  var param = new SqlParameter（）; 
 param.SqlDbType = SqlDbType.Structured; 
  

SqlDbType.Structured 这个。不要使用 DbType.Object 。

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

解决方案

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.

这篇关于使用PetaPoco将表值参数传递到存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！