SQL Server 2005 데이터베이스가 있습니다. 몇 가지 절차에는 저장된 proc에 nvarchar
(쉼표로 구분) 전달하고 내부적으로 단일 값으로 나누는 테이블 매개 변수가 있습니다. 다음과 같이 SQL 명령 매개 변수 목록에 추가합니다.
cmd.Parameters.Add("@Logins", SqlDbType.NVarchar).Value = "jim18,jenny1975,cosmo";
데이터베이스를 SQL Server 2008로 마이그레이션해야합니다. 테이블 값 매개 변수가 있으며이를 저장 프로 시저에서 사용하는 방법을 알고 있습니다. 그러나 SQL 명령에서 매개 변수 목록으로 전달하는 방법을 모르겠습니다.
누구나 Parameters.Add
절차 의 올바른 구문을 알고 있습니까? 아니면이 매개 변수를 전달하는 다른 방법이 있습니까?
답변
DataTable
, DbDataReader
또는 IEnumerable<SqlDataRecord>
개체를 사용하여 MSDN 문서 SQL Server 2008의 테이블 반환 매개 변수 (ADO.NET)에 따라 테이블 반환 매개 변수를 채울 수 있습니다 .
다음 예제는 a DataTable
또는 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;
}
}
답변
또한 라이언의 대답에 당신은 또한 설정해야합니다 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
};
}