如何从 .net 代码向存储过程传递表值参数

Question

我有一个 SQL Server 2005 数据库。在一些存储过程中，我有表参数，我将它们作为 nvarchar （由逗号分隔）传递给存储过程，然后在内部将其拆分为单个值。我通过以下方式将其添加到 SQL 命令参数列表中：

cmd.Parameters.Add("@Logins", SqlDbType.NVarchar).Value = "jim18,jenny1975,cosmo";

我需要将数据库迁移到SQL Server 2008。我知道有表值参数，并且知道如何在存储过程中使用它们。但是我不知道如何将其传递到SQL命令的参数列表中。

有人知道Parameters.Add过程的正确语法吗？或者是否有另一种方法传递此参数？

- Marek Kwiendacz

---

看看这个解决方案：使用EF的表值参数存储过程。 https://code.msdn.microsoft.com/Stored-Procedure-with-6c194514 - Carl Prothman

在这种情况下，我通常会在服务器端连接字符串并将其拆分，或者如果有多个列，甚至传递一个xml。 Sql在处理xml时非常快速。您可以尝试所有方法并检查处理时间，然后选择最佳方法。 XML看起来像<Items><Item value="sdadas"/><Item value="sadsad"/>...</Items>。 在Sql Server上的过程也很简单。使用此方法，如果需要更多信息，则始终可以向<item>添加新属性。 - Nițu Alexandru

"Sql在处理xml时非常快"，绝对不是这样。 - nothrow

Answer 1

DataTable、DbDataReader或IEnumerable<SqlDataRecord>对象可以用于根据MSDN文章Table-Valued Parameters in SQL Server 2008 (ADO.NET)来填充表值参数。

下面的示例演示如何使用DataTable或IEnumerable<SqlDataRecord>：

SQL代码：

CREATE TABLE dbo.PageView
(
    PageViewID BIGINT NOT NULL CONSTRAINT pkPageView PRIMARY KEY CLUSTERED,
    PageViewCount BIGINT NOT NULL
);
CREATE TYPE dbo.PageViewTableType AS TABLE
(
    PageViewID BIGINT NOT NULL
);
CREATE PROCEDURE dbo.procMergePageView
    @Display dbo.PageViewTableType READONLY
AS
BEGIN
    MERGE INTO dbo.PageView AS T
    USING @Display AS S
    ON T.PageViewID = S.PageViewID
    WHEN MATCHED THEN UPDATE SET T.PageViewCount = T.PageViewCount + 1
    WHEN NOT MATCHED THEN INSERT VALUES(S.PageViewID, 1);
END

C# 代码:

private static void ExecuteProcedure(bool useDataTable, 
                                     string connectionString, 
                                     IEnumerable<long> ids) 
{
    using (SqlConnection connection = new SqlConnection(connectionString)) 
    {
        connection.Open();
        using (SqlCommand command = connection.CreateCommand()) 
        {
            command.CommandText = "dbo.procMergePageView";
            command.CommandType = CommandType.StoredProcedure;

            SqlParameter parameter;
            if (useDataTable) {
                parameter = command.Parameters
                              .AddWithValue("@Display", CreateDataTable(ids));
            }
            else 
            {
                parameter = command.Parameters
                              .AddWithValue("@Display", CreateSqlDataRecords(ids));
            }
            parameter.SqlDbType = SqlDbType.Structured;
            parameter.TypeName = "dbo.PageViewTableType";

            command.ExecuteNonQuery();
        }
    }
}

private static DataTable CreateDataTable(IEnumerable<long> ids) 
{
    DataTable table = new DataTable();
    table.Columns.Add("ID", typeof(long));
    foreach (long id in ids) 
    {
        table.Rows.Add(id);
    }
    return table;
}

private static IEnumerable<SqlDataRecord> CreateSqlDataRecords(IEnumerable<long> ids) 
{
    SqlMetaData[] metaData = new SqlMetaData[1];
    metaData[0] = new SqlMetaData("ID", SqlDbType.BigInt);
    SqlDataRecord record = new SqlDataRecord(metaData);
    foreach (long id in ids) 
    {
        record.SetInt64(0, id);
        yield return record;
    }
}

- Ryan Prechel

---

+1 优秀的示例。重点是：将 DataTable 作为参数值发送，将 SqlDbType 设置为 Structured，并将 TypeName 设置为数据库 UDT 名称。 - lc.

如果您要在循环中重复使用引用类型的实例（例如 SqlDataRecord），请务必添加注释，说明在此特定情况下这样做是安全的。 - Søren Boisen

这段代码是错误的：空表值参数应该将它们的值设置为 null。如果给定一个空的 ids 参数，CreateSqlDataRecords 永远不会返回 null。 - ta.speot.is

@Crono说：DataTable（或DataSet）只是为了支持Visual-Studio中的拖放功能而实现了它，因此它们实现了IComponent，该接口实现了IDisposable。如果您不使用设计器而是手动创建它，则没有理由处理它（或使用using语句）。因此，这是“处理所有实现IDisposable的对象”的黄金法则的例外之一。 - Tim Schmelter

请注意，未来的用户：DataTable方法会消耗大量内存，而IEnumerable方法则不会。 - AgentFire

Answer 2

除了Ryan的答案之外，如果你处理的是一个包含多个列且其序号不是按字母顺序排列的table-valued parameter，你还需要设置DataColumn的Ordinal属性。

例如，如果你有以下用作SQL参数的表值：

CREATE TYPE NodeFilter AS TABLE (
  ID int not null
  Code nvarchar(10) not null,
);

你需要在C#中按照以下顺序排序列：

table.Columns["ID"].SetOrdinal(0);
// this also bumps Code to ordinal of 1
// if you have more than 2 cols then you would need to set more ordinals

如果您未能执行此操作，将会出现解析错误，无法将 nvarchar 转换为 int。

- Scotty.NET

Answer 3

通用的

   public static DataTable ToTableValuedParameter<T, TProperty>(this IEnumerable<T> list, Func<T, TProperty> selector)
    {
        var tbl = new DataTable();
        tbl.Columns.Add("Id", typeof(T));

        foreach (var item in list)
        {
            tbl.Rows.Add(selector.Invoke(item));

        }

        return tbl;

    }

- Martea

---

请问我应该传递什么参数？Func<T，TProperty>选择器？难道不可以简单地使用tbl.Rows.Add(item)而无需该参数吗？ - GDroid

selector.Invoke(item) 会选择项目上的属性，大多数情况下是 int 类型，但它也允许您选择字符串属性。 - Martea

请问您能否提供一个示例，说明如何将选择器放在那里？我有一个要传递给存储过程的Guid列表。 - GDroid

guidList.ToTabledValuedParameter(x=>x)。由于在您的情况下x是guid，因此返回值将是一个DataTable，其中包含一个列（id），该列包含一组guids。 - Martea

Answer 4

最干净的处理方式。假设您的表是一个名为“dbo.tvp_Int”的整数列表（根据您自己的表类型进行自定义）

创建此扩展方法...

public static void AddWithValue_Tvp_Int(this SqlParameterCollection paramCollection, string parameterName, List<int> data)
{
   if(paramCollection != null)
   {
       var p = paramCollection.Add(parameterName, SqlDbType.Structured);
       p.TypeName = "dbo.tvp_Int";
       DataTable _dt = new DataTable() {Columns = {"Value"}};
       data.ForEach(value => _dt.Rows.Add(value));
       p.Value = _dt;
   }
}

现在你可以通过简单地执行此操作，在任何地方一行代码中添加一个表值参数：

cmd.Parameters.AddWithValueFor_Tvp_Int("@IDValues", listOfIds);

- Shahzad Qureshi

---

如果paramCollection为空怎么办？如何传递空类型？ - Muflix

@Muflix，扩展方法实际上可以针对空实例进行操作。因此，在方法顶部添加一个简单的if(paramCollection != null)检查即可。 - Rhumborl

更新答案，加入了初始的-if-检查。 - Shahzad Qureshi

或许有点拘泥于细节，但我在签名中会使用 IEnumerable 而不是 List，这样你可以传递任何实现了 IEnumerable 接口的对象，而不仅限于列表。既然你没有使用到 List 特有的任何函数，我不认为没有使用 IEnumerable 的理由。 - Francis Lord

使用 List 可以让你使用简写的 data.ForEach()，否则你必须手动编写 foreach 循环。虽然这也可以工作，但我喜欢尽可能地缩短代码。 - Shahzad Qureshi

Answer 5

使用此代码从您的类型创建适当的参数：

private SqlParameter GenerateTypedParameter(string name, object typedParameter)
{
    DataTable dt = new DataTable();

    var properties = typedParameter.GetType().GetProperties().ToList();
    properties.ForEach(p =>
    {
        dt.Columns.Add(p.Name, Nullable.GetUnderlyingType(p.PropertyType) ?? p.PropertyType);
    });
    var row = dt.NewRow();
    properties.ForEach(p => { row[p.Name] = (p.GetValue(typedParameter) ?? DBNull.Value); });
    dt.Rows.Add(row);

    return new SqlParameter
    {
        Direction = ParameterDirection.Input,
        ParameterName = name,
        Value = dt,
        SqlDbType = SqlDbType.Structured
    };
}

- Andrii Viazovskyi

Answer 6

如果您有一个带参数的表值函数，例如以下类型：

CREATE FUNCTION [dbo].[MyFunc](@PRM1 int, @PRM2 int)
RETURNS TABLE
AS
RETURN 
(
    SELECT * FROM MyTable t
    where t.column1 = @PRM1 
    and t.column2 = @PRM2
)

你可以这样调用它：

select * from MyFunc(1,1).

然后你可以像这样从C#中调用它：

public async Task<ActionResult> MethodAsync(string connectionString, int? prm1, int? prm2)
{
  List<MyModel> lst = new List<MyModel>();

  using (SqlConnection connection = new SqlConnection(connectionString))
  {
     connection.OpenAsync();

     using (var command = connection.CreateCommand())
     {
        command.CommandText = $"select * from MyFunc({prm1},{prm2})";
        using (var reader = await command.ExecuteReaderAsync())
        {
           if (reader.HasRows)
           {
              while (await reader.ReadAsync())
              {
                 MyModel myModel = new MyModel();
                 myModel.Column1 = int.Parse(reader["column1"].ToString());
                 myModel.Column2 = int.Parse(reader["column2"].ToString());
                 lst.Add(myModel);
              }
            }
         }
     }
  }
  View(lst);
}

- Nur.B