如何传递表值参数

本文关键字：参数 值参 何传递 | 更新日期: 2023-09-27 18:04:56

我试图将一个表值参数传递给存储过程，但是我一直得到一个异常(见下文)。

SqlCommand c = new SqlCommand("getPermittedUsers", myConn) { CommandType = CommandType.StoredProcedure };
c.Parameters.AddWithValue("@intNotifyingUserId", notifyingUserId);
c.Parameters.AddWithValue("@tSelectedPdfIds", sharedPdfs).SqlDbType = SqlDbType.Structured;
SqlDataReader dr = c.ExecuteReader();

该类型在服务器上定义如下:

CREATE TYPE [dbo].[IdList] AS TABLE(
    [Id] [int] NOT NULL
)

我已经尝试将sharedpdf作为List<int>和IQueryable<int>传递，但不断得到以下例外:

Object must implement IConvertible.

谁知道我做错了什么?文档暗示我应该能够将列表作为TVP传递，但没有给出任何示例。

谢谢。

如何传递表值参数

下面的示例说明了使用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;
    }
}

可以传递DataTable、IEnumerable<SqlDataRecord>或DbDataReader参数

添加带有更新链接的新答案

根据文档(learn.microsoft.com)，可以使用以下参数类型之一:

SqlClient支持从DataTable、DbDataReader或IEnumerable ' SqlDataRecord对象中填充表值参数。必须使用SqlParameter的TypeName属性为表值参数指定类型名称。TypeName必须匹配先前在服务器上创建的兼容类型的名称。

没有包含在文档中，但对于高性能应用程序很重要，使用IEnumerable (/strong>)的示例。. NET Core 3.1:

  using var timeout = new CancellationTokenSource(TimeSpan.FromSeconds(30));
  using SqlConnection connection = this.GetConnection();
  await connection.OpenAsync(timeout.Token);
  using SqlCommand command = connection.CreateCommand();
  command.CommandType = CommandType.StoredProcedure;
  command.CommandText = "Mycommand";
  IEnumerable<SqlDataRecord> records = // ...
  SqlParameter parameter = command.Parameters.Add("@MyObjects", SqlDbType.Structured);
  parameter.TypeName = "MyCustomTableType";
  parameter.Value = records;
  await command.ExecuteNonQueryAsync(timeout.Token);
使用数据表的例子: 
  // Create a DataTable with the modified rows.  
  DataTable addedCategories = CategoriesDataTable.GetChanges(DataRowState.Added);  
  // Configure the SqlCommand and SqlParameter.  
  SqlCommand insertCommand = new SqlCommand("usp_InsertCategories", connection);  
  insertCommand.CommandType = CommandType.StoredProcedure;  
  SqlParameter tvpParam = insertCommand.Parameters.AddWithValue("@tvpNewCategories", addedCategories);  
  tvpParam.SqlDbType = SqlDbType.Structured;  
  // Execute the command.  
  insertCommand.ExecuteNonQuery();

示例使用DbDataReader:

 // Assumes connection is an open SqlConnection.  
 // Retrieve data from Oracle.  
 OracleCommand selectCommand = new OracleCommand(  
     "Select CategoryID, CategoryName FROM Categories;",  
     oracleConnection);  
 OracleDataReader oracleReader = selectCommand.ExecuteReader(  
     CommandBehavior.CloseConnection);  
  // Configure the SqlCommand and table-valued parameter.  
  SqlCommand insertCommand = new SqlCommand(  
      "usp_InsertCategories", connection);  
  insertCommand.CommandType = CommandType.StoredProcedure;  
  SqlParameter tvpParam =  
      insertCommand.Parameters.AddWithValue(  
      "@tvpNewCategories", oracleReader);  
  tvpParam.SqlDbType = SqlDbType.Structured;  
  // Execute the command.  
  insertCommand.ExecuteNonQuery();
相关文章：