c# - 如何将表值参数从 .net 代码传递给存储过程

Question

我有一个 SQL Server 2005 数据库。在一些过程中，我将表参数作为 nvarchar(以逗号分隔)传递给存储过程，并在内部划分为单个值。我将它添加到 SQL 命令参数列表中，如下所示:

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

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

有谁知道 Parameters.Add 过程的正确语法？或者有其他方法可以传递这个参数吗？

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