如何从.NET code传递表值参数的存储过程 [英] How to pass table value parameters to stored procedure from .net code

问题描述

我有一个MS SQL Server 2005数据库。在一些程序，我有我传递给存储过程作为一个nvarchar（用逗号分隔），并在内部分割成单个值表参数。我把它添加到SQL命令的参数列表如下：

I have an MS SQL Server 2005 database. In a few procedures I have table parameters that I pass to a stored proc as an nvarchar (separated by commas) and internally divide into single values. I add it to the SQL command parameters list like this:

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

我要迁移数据库到SQL Server 2008。我知道有表值参数，我知道如何在存储过程中使用它们。但我不知道如何通过一个参数列表中的SQL命令。
有谁知道 Parameters.Add 程序的语法是否正确？还是有另一种方式来传递这个参数？

I have to migrate the database to SQL Server 2008. I know that there are table value parameters, and I know how to use them in stored procedures. But I don't know how to pass one to the parameters list in an SQL command. Does anyone know correct syntax of the Parameters.Add procedure? Or is there another way to pass this parameter?

推荐答案

数据表， DbDataReader 或的IEnumerable＆LT;＆的SqlDataRecord GT; 对象可以用来填充每MSDN文章的Table-Valued参数在SQL Server 2008（ADO.NET）。

DataTable, DbDataReader, or IEnumerable<SqlDataRecord> objects can be used to populate a table-valued parameter per the MSDN article Table-Valued Parameters in SQL Server 2008 (ADO.NET).

以下示例说明使用一个数据表或的IEnumerable＆LT;的SqlDataRecord＆GT; ：

The following example illustrates using either a DataTable or an IEnumerable<SqlDataRecord>:

SQL code

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＃code

C# Code

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

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