[C#] SqlCommand의 배열 매개 변수 전달

C# / 글쓴이

아래와 같이 C #에서 SQL commnd에 배열 매개 변수를 전달하려고하지만 작동하지 않습니다. 누구든지 전에 만난 적이 있습니까?

string sqlCommand = "SELECT * from TableA WHERE Age IN (@Age)";
SqlConnection sqlCon = new SqlConnection(connectString);
SqlCommand sqlComm = new SqlCommand();
sqlComm.Connection = sqlCon;
sqlComm.CommandType = System.Data.CommandType.Text;
sqlComm.CommandText = sqlCommand;
sqlComm.CommandTimeout = 300;
sqlComm.Parameters.Add("@Age", SqlDbType.NVarChar);
StringBuilder sb = new StringBuilder();
foreach (ListItem item in ddlAge.Items)
{
     if (item.Selected)
     {
         sb.Append(item.Text + ",");
     }
}

sqlComm.Parameters["@Age"].Value = sb.ToString().TrimEnd(',');

답변

한 번에 하나씩 배열에 값을 추가해야합니다.

var parameters = new string[items.Length];
var cmd = new SqlCommand();
for (int i = 0; i < items.Length; i++)
{
    parameters[i] = string.Format("@Age{0}", i);
    cmd.Parameters.AddWithValue(parameters[i], items[i]);
}

cmd.CommandText = string.Format("SELECT * from TableA WHERE Age IN ({0})", string.Join(", ", parameters));
cmd.Connection = new SqlConnection(connStr);

업데이트 : 여기에 Adam의 제안과 함께 제안 된 편집을 사용하는 확장 가능하고 재사용 가능한 솔루션이 있습니다. 나는 그것을 조금 개선하고 쉽게 호출 할 수 있도록 확장 방법으로 만들었습니다.

public static class SqlCommandExt
{

    /// <summary>
    /// This will add an array of parameters to a SqlCommand. This is used for an IN statement.
    /// Use the returned value for the IN part of your SQL call. (i.e. SELECT * FROM table WHERE field IN ({paramNameRoot}))
    /// </summary>
    /// <param name="cmd">The SqlCommand object to add parameters to.</param>
    /// <param name="paramNameRoot">What the parameter should be named followed by a unique value for each value. This value surrounded by {} in the CommandText will be replaced.</param>
    /// <param name="values">The array of strings that need to be added as parameters.</param>
    /// <param name="dbType">One of the System.Data.SqlDbType values. If null, determines type based on T.</param>
    /// <param name="size">The maximum size, in bytes, of the data within the column. The default value is inferred from the parameter value.</param>
    public static SqlParameter[] AddArrayParameters<T>(this SqlCommand cmd, string paramNameRoot, IEnumerable<T> values, SqlDbType? dbType = null, int? size = null)
    {
        /* An array cannot be simply added as a parameter to a SqlCommand so we need to loop through things and add it manually.
         * Each item in the array will end up being it's own SqlParameter so the return value for this must be used as part of the
         * IN statement in the CommandText.
         */
        var parameters = new List<SqlParameter>();
        var parameterNames = new List<string>();
        var paramNbr = 1;
        foreach (var value in values)
        {
            var paramName = string.Format("@{0}{1}", paramNameRoot, paramNbr++);
            parameterNames.Add(paramName);
            SqlParameter p = new SqlParameter(paramName, value);
            if (dbType.HasValue)
                p.SqlDbType = dbType.Value;
            if (size.HasValue)
                p.Size = size.Value;
            cmd.Parameters.Add(p);
            parameters.Add(p);
        }

        cmd.CommandText = cmd.CommandText.Replace("{" + paramNameRoot + "}", string.Join(",", parameterNames));

        return parameters.ToArray();
    }

}

이렇게 불립니다 …

var cmd = new SqlCommand("SELECT * FROM TableA WHERE Age IN ({Age})");
cmd.AddArrayParameters("Age", new int[] { 1, 2, 3 });

sql 문의 “{Age}”는 AddArrayParameters로 보내는 매개 변수 이름과 같습니다. AddArrayParameters는 값을 올바른 매개 변수로 대체합니다.

답변

Brian이 다른 곳에서 쉽게 사용할 수 있도록 기여한 답변을 확장하고 싶었습니다.

/// <summary>
/// This will add an array of parameters to a SqlCommand. This is used for an IN statement.
/// Use the returned value for the IN part of your SQL call. (i.e. SELECT * FROM table WHERE field IN (returnValue))
/// </summary>
/// <param name="sqlCommand">The SqlCommand object to add parameters to.</param>
/// <param name="array">The array of strings that need to be added as parameters.</param>
/// <param name="paramName">What the parameter should be named.</param>
protected string AddArrayParameters(SqlCommand sqlCommand, string[] array, string paramName)
{
    /* An array cannot be simply added as a parameter to a SqlCommand so we need to loop through things and add it manually.
     * Each item in the array will end up being it's own SqlParameter so the return value for this must be used as part of the
     * IN statement in the CommandText.
     */
    var parameters = new string[array.Length];
    for (int i = 0; i < array.Length; i++)
    {
        parameters[i] = string.Format("@{0}{1}", paramName, i);
        sqlCommand.Parameters.AddWithValue(parameters[i], array[i]);
    }

    return string.Join(", ", parameters);
}

이 새로운 기능을 다음과 같이 사용할 수 있습니다.

SqlCommand cmd = new SqlCommand();

string ageParameters = AddArrayParameters(cmd, agesArray, "Age");
sql = string.Format("SELECT * FROM TableA WHERE Age IN ({0})", ageParameters);

cmd.CommandText = sql;

편집 : 다음은 모든 유형의 값 배열과 함께 작동하며 확장 방법으로 사용할 수있는 일반적인 변형입니다. 

public static class Extensions
{
    public static void AddArrayParameters<T>(this SqlCommand cmd, string name, IEnumerable<T> values)
    {
        name = name.StartsWith("@") ? name : "@" + name;
        var names = string.Join(", ", values.Select((value, i) => {
            var paramName = name + i;
            cmd.Parameters.AddWithValue(paramName, value);
            return paramName;
        }));
        cmd.CommandText = cmd.CommandText.Replace(name, names);
    }
}

그런 다음이 확장 방법을 다음과 같이 사용할 수 있습니다. 

var ageList = new List<int> { 1, 3, 5, 7, 9, 11 };
var cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM MyTable WHERE Age IN (@Age)";
cmd.AddArrayParameters("Age", ageList);

AddArrayParameters를 호출하기 전에 CommandText를 설정해야합니다.

또한 매개 변수 이름이 명령문의 다른 이름과 부분적으로 일치하지 않는지 확인하십시오 (예 : @AgeOfChild)

답변

“dapper”와 같은 도구를 사용할 수 있다면 다음과 같습니다.

int[] ages = { 20, 21, 22 }; // could be any common list-like type
var rows = connection.Query<YourType>("SELECT * from TableA WHERE Age IN @ages",
          new { ages }).ToList();

Dapper는이를 개별 매개 변수 에 래핑 해제 처리 합니다 .

답변

MS SQL Server 2008 이상을 사용하는 경우 http://www.sommarskog.se/arrays-in-sql-2008.html에 설명 된대로 테이블 값 매개 변수를 사용할 수 있습니다
. .

1. 사용할 각 매개 변수 유형에 대한 테이블 유형을 작성하십시오.

다음 명령은 정수에 대한 테이블 유형을 작성합니다.

create type int32_id_list as table (id int not null primary key)

2. 헬퍼 메소드 구현

public static SqlCommand AddParameter<T>(this SqlCommand command, string name, IEnumerable<T> ids)
{
  var parameter = command.CreateParameter();

  parameter.ParameterName = name;
  parameter.TypeName = typeof(T).Name.ToLowerInvariant() + "_id_list";
  parameter.SqlDbType = SqlDbType.Structured;
  parameter.Direction = ParameterDirection.Input;

  parameter.Value = CreateIdList(ids);

  command.Parameters.Add(parameter);
  return command;
}

private static DataTable CreateIdList<T>(IEnumerable<T> ids)
{
  var table = new DataTable();
  table.Columns.Add("id", typeof (T));

  foreach (var id in ids)
  {
    table.Rows.Add(id);
  }

  return table;
}

3. 이렇게 사용하십시오

cmd.CommandText = "select * from TableA where Age in (select id from @age)";
cmd.AddParameter("@age", new [] {1,2,3,4,5});

답변

에 방법이 있기 때문에

SqlCommand.Parameters.AddWithValue(parameterName, value)

대체 할 매개 변수 (이름)와 값 목록을 승인하는 메소드를 작성하는 것이 더 편리 할 수 ​​있습니다. 그것은에없는 매개 변수 (같은 수준 AddWithValue )하지만 명령 자체에 그래서는 그것을 호출하는 것이 좋습니다 AddParametersWithValues 뿐 아니라 AddWithValues을 :

질문:

SELECT * from TableA WHERE Age IN (@age)

용법:

sqlCommand.AddParametersWithValues("@age", 1, 2, 3);

확장 방법 :

public static class SqlCommandExtensions
{
    public static void AddParametersWithValues<T>(this SqlCommand cmd,  string parameterName, params T[] values)
    {
        var parameterNames = new List<string>();
        for(int i = 0; i < values.Count(); i++)
        {
            var paramName = @"@param" + i;
            cmd.Parameters.AddWithValue(paramName, values.ElementAt(i));
            parameterNames.Add(paramName);
        }

        cmd.CommandText = cmd.CommandText.Replace(parameterName, string.Join(",", parameterNames));
    }
}

답변

IN 연산자로 제한을 해결하는 다른 방법을 제안하고 싶습니다.

예를 들어 다음과 같은 쿼리가 있습니다.

select *
from Users U
WHERE U.ID in (@ids)

사용자를 필터링하기 위해 여러 ID를 전달하려고합니다. 불행히도 C #으로 쉽게 할 수는 없습니다. 그러나 “string_split”함수를 사용하여 이에 대한 해결 방법이 없습니다. 쿼리를 다음과 같이 약간 다시 작성해야합니다.

declare @ids nvarchar(max) = '1,2,3'

SELECT *
FROM Users as U
CROSS APPLY string_split(@ids, ',') as UIDS
WHERE U.ID = UIDS.value

이제 쉼표로 구분 된 값의 매개 변수 열거를 쉽게 전달할 수 있습니다.

답변

쿼리가 형식을 취하기 때문에 항목 배열을 축소 된 매개 변수로 WHERE..IN 절에 전달하면 실패합니다. WHERE Age IN ("11, 13, 14, 16") 합니다.

그러나 매개 변수를 XML 또는 JSON으로 직렬화 된 배열로 전달할 수 있습니다.

nodes()방법을 사용하여 :

StringBuilder sb = new StringBuilder();

foreach (ListItem item in ddlAge.Items)
  if (item.Selected)
    sb.Append("<age>" + item.Text + "</age>"); // actually it's xml-ish

sqlComm.CommandText = @"SELECT * from TableA WHERE Age IN (
    SELECT Tab.col.value('.', 'int') as Age from @Ages.nodes('/age') as Tab(col))";
sqlComm.Parameters.Add("@Ages", SqlDbType.NVarChar);
sqlComm.Parameters["@Ages"].Value = sb.ToString();

OPENXML방법을 사용하여 :

using System.Xml.Linq;
...
XElement xml = new XElement("Ages");

foreach (ListItem item in ddlAge.Items)
  if (item.Selected)
    xml.Add(new XElement("age", item.Text);

sqlComm.CommandText = @"DECLARE @idoc int;
    EXEC sp_xml_preparedocument @idoc OUTPUT, @Ages;
    SELECT * from TableA WHERE Age IN (
    SELECT Age from OPENXML(@idoc, '/Ages/age') with (Age int 'text()')
    EXEC sp_xml_removedocument @idoc";
sqlComm.Parameters.Add("@Ages", SqlDbType.Xml);
sqlComm.Parameters["@Ages"].Value = xml.ToString();

그것은 SQL 측면에서 조금 더 많으며 적절한 XML (루트 포함)이 필요합니다.

OPENJSON방법 사용 (SQL Server 2016+) :

using Newtonsoft.Json;
...
List<string> ages = new List<string>();

foreach (ListItem item in ddlAge.Items)
  if (item.Selected)
    ages.Add(item.Text);

sqlComm.CommandText = @"SELECT * from TableA WHERE Age IN (
    select value from OPENJSON(@Ages))";
sqlComm.Parameters.Add("@Ages", SqlDbType.NVarChar);
sqlComm.Parameters["@Ages"].Value = JsonConvert.SerializeObject(ages);

마지막 방법의 경우 호환성 수준도 130 이상이어야합니다.