c# - 정의 - mssql 프로시저 테이블 파라미터




테이블 값 매개 변수를.net 코드에서 저장 프로 시저로 전달하는 방법 (3)

가장 깨끗한 방법으로 작업하십시오. 테이블이 "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);

MS SQL Server 2005 데이터베이스가 있습니다. 몇 가지 절차에서 나는 저장된 프로 시저를 nvarchar (쉼표로 구분)로 전달하고 내부적으로 단일 값으로 나누는 테이블 매개 변수를 사용합니다. 다음과 같이 SQL 명령 매개 변수 목록에 추가합니다.

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

데이터베이스를 SQL Server 2008로 마이그레이션해야합니다. 테이블 값 매개 변수가 있으며 해당 매개 변수를 저장 프로 시저에서 사용하는 방법을 알고 있습니다. 하지만 SQL 명령에서 매개 변수 목록으로 전달하는 방법을 모르겠습니다. 누구든지 Parameters.Add 프로 시저의 올바른 구문을 알고 있습니까? 아니면이 매개 변수를 전달하는 다른 방법이 있습니까?


또한 Ryan의 답변에 따라 서 Ordinal 알파벳 순서가 아닌 여러 열이있는 table-valued parameter 를 처리하는 경우 DataColumnOrdinal 속성을 설정해야합니다.

예를 들어 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로 변환하지 못했습니다.


DataTable , DbDataReader 또는 IEnumerable<SqlDataRecord> 개체는 SQL Server 2008 (ADO.NET) 의 MSDN 문서 테이블 반환 매개 변수에 따라 테이블 반환 매개 변수를 채우는 데 사용할 수 있습니다.

다음 예제에서는 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;
    }
}




table-valued-parameters