SQL Server用户定义表类型和.NET

Question

我需要将一个整数数组从.NET传递给存储过程，因此我在Google上搜索了这个主题，并最终找到了Erland Sommarskog撰写的《SQL Server 2008中的数组和列表》，据说这是人们进行此过程的标准方法。

我尝试了两种不同的方法来将用户定义的表类型传递给存储过程，但每种方法都出现了异常。这两种方法都类似于上面链接中Erland Sommarskog使用的方式。

方法1 - 使用DataTable作为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 });

我收到的异常信息如下： 传入的表格数据流(TDS)远程过程调用(RPC)协议流是不正确的。参数1("@ids")：数据类型0x62(sql_variant)在特定类型元数据中具有无效的类型。 方法2 - 使用List作为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 });

我得到的异常信息如下： 没有从对象类型System.Collections.Generic.List`1[[Microsoft.SqlServer.Server.SqlDataRecord, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089]]到已知托管提供程序本地类型的映射。 其他信息 lstInt_TblType是我在SQL Server 2008中定义的用户定义表类型。它确实存在（我三重检查了这一点！）。它有一个名为“n”的列，类型为int，是主键，并且不允许为空。我完全按照Erland的设置方式进行复制。

我还验证了存储过程usp_Test可以从SQL Server Management Studio正常工作，因此我几乎确定异常不是来自该方向。以下是我用于验证存储过程工作的T-SQL：

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

任何有关此事的建议将不胜感激。谢谢！
*编辑：
存储过程usp_Test：

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

- Jagd

---

您连接的数据库中是否定义了该表类型？ - Oded

@Oded - 感谢您的问题。我刚刚双重检查了它是否命中了正确的数据库，结果是肯定的。 - Jagd

值得注意的是，还应检查模式名称，可能将模式名称附加到类型上（即dbo.tvp_name）。 - Oded

@Oded - 刚试了一下 "dbo.lstInt_TblType"，但对于任何情况都没有起作用。 DataTable 尝试的错误消息让我觉得它能够很好地找到用户定义的表类型，但出于某种原因它无法协调 DataRow 的 "n" 列的 'int' 类型。 嗯...我想我会从数据库中删除用户定义的表类型的主键属性，看看是否有进展。 - Jagd

兼容性级别不是90或更低吗？ - Oded

Answer 1

找到了一种不同的方法来完成它。这种方法使用System.Data.SqlClient库创建与数据库的连接，指定存储过程名称，然后将作为SQL Server用户定义表类型的DataTable作为参数传递。

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);
    }
}

- Jagd

Answer 2

您还可以在微软自己的表值参数参考中找到如何将表值参数数据传递给存储过程的良好示例。

- jvopalensky