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

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

SQL Server 2005 데이터베이스가 있습니다.몇 가지 절차에서 테이블 파라미터는 저장 프로시저에 전달되며nvarchar(쉼표로 구분) 및 내부적으로는 단일 값으로 나뉩니다.다음과 같이 SQL 명령어파라미터 목록에 추가합니다.

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

데이터베이스를 SQL Server 2008으로 이행해야 합니다.테이블 값 파라미터가 있으며 저장 프로시저에서 사용하는 방법도 알고 있습니다.하지만 SQL 명령의 매개 변수 목록에 어떻게 전달해야 할지 모르겠습니다.

의 올바른 구문을 아시는 분?Parameters.Add절차요?아니면 이 파라미터를 전달할 다른 방법이 있나요?

DataTable,DbDataReader, 또는IEnumerable<SqlDataRecord>오브젝트를 사용하여 MSDN 문서 "Table-Value 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;
    }
}

Ryan의 답변에 더하여 다음 항목을 설정해야 합니다.DataColumn의Ordinal를 취급하고 있는 경우table-valued parameter서수가 알파벳 순서가 아닌 여러 개의 열이 있습니다.

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

포괄적인

   public static DataTable ToTableValuedParameter<T, TProperty>(this IEnumerable<T> list, Func<T, TProperty> selector)
    {
        var tbl = new DataTable();
        tbl.Columns.Add("Id", typeof(T));

        foreach (var item in list)
        {
            tbl.Rows.Add(selector.Invoke(item));

        }

        return tbl;

    }

가장 깔끔한 작업 방식입니다.테이블이 "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);

이 코드를 사용하여 유형에서 적절한 매개 변수를 만듭니다.

private SqlParameter GenerateTypedParameter(string name, object typedParameter)
{
    DataTable dt = new DataTable();

    var properties = typedParameter.GetType().GetProperties().ToList();
    properties.ForEach(p =>
    {
        dt.Columns.Add(p.Name, Nullable.GetUnderlyingType(p.PropertyType) ?? p.PropertyType);
    });
    var row = dt.NewRow();
    properties.ForEach(p => { row[p.Name] = (p.GetValue(typedParameter) ?? DBNull.Value); });
    dt.Rows.Add(row);

    return new SqlParameter
    {
        Direction = ParameterDirection.Input,
        ParameterName = name,
        Value = dt,
        SqlDbType = SqlDbType.Structured
    };
}

파라미터가 있는 테이블 값 함수가 있는 경우, 예를 들어 다음과 같습니다.

CREATE FUNCTION [dbo].[MyFunc](@PRM1 int, @PRM2 int)
RETURNS TABLE
AS
RETURN 
(
    SELECT * FROM MyTable t
    where t.column1 = @PRM1 
    and t.column2 = @PRM2
)

이걸 이렇게 부르죠

select * from MyFunc(1,1).

다음으로 C#에서 다음과 같이 호출할 수 있습니다.

public async Task<ActionResult> MethodAsync(string connectionString, int? prm1, int? prm2)
{
  List<MyModel> lst = new List<MyModel>();

  using (SqlConnection connection = new SqlConnection(connectionString))
  {
     connection.OpenAsync();

     using (var command = connection.CreateCommand())
     {
        command.CommandText = $"select * from MyFunc({prm1},{prm2})";
        using (var reader = await command.ExecuteReaderAsync())
        {
           if (reader.HasRows)
           {
              while (await reader.ReadAsync())
              {
                 MyModel myModel = new MyModel();
                 myModel.Column1 = int.Parse(reader["column1"].ToString());
                 myModel.Column2 = int.Parse(reader["column2"].ToString());
                 lst.Add(myModel);
              }
            }
         }
     }
  }
  View(lst);
}

언급URL : https://stackoverflow.com/questions/5595353/how-to-pass-table-value-parameters-to-stored-procedure-from-net-code