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

问题描述

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

I have a 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命令中将一个传递给参数列表.

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.

有人知道 Parameters.Add 过程的正确语法吗?或者有没有其他方式传递这个参数?

Does anyone know correct syntax of the Parameters.Add procedure? Or is there another way to pass this parameter?

推荐答案

DataTable、DbDataReader 或 IEnumerable 对象可以是用于根据 MSDN 文章 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).

以下示例说明如何使用 DataTable 或 IEnumerable:

The following example illustrates using either a DataTable or an 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;
    }
}

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