Как передать в запрос параметр?

Question

Всем привет, есть вот такой код

private const string SELECT_PARTNERS_PARAM = "SELECT_PARTNERS_PARAM.sql";
// Метод для вызова запроса
private static string GetQuerySelect(string sql_file)
        {
            using (var stream = Assembly.GetExecutingAssembly().GetManifestResourceStream($"Rtb.Api.Dal.Query.{sql_file}"))
            using (StreamReader reader = new StreamReader(stream, Encoding.UTF8))
                return reader.ReadToEnd();
        }
public async Task<IEnumerable<RtbModel>> GetPartnerDal(int[] ids)
        {
            var listOfPartners = new List<RtbModel>();
             // Тут идет обращение к запросу
            var sqlCommand = GetQuerySelect(SELECT_PARTNERS_PARAM);
            var cmd = CreateCommand(sqlCommand);

            using (var reader = await cmd.ExecuteReaderAsync())
            {
                while (await reader.ReadAsync())
                {
                   // Остальной код работы
                }
            }
            return listOfPartners;
        }

Собстевнно говоря сам SQL-запрос

SELECT
    [AdvertXrefDsp].[advertId],
    [Dsp].[dspId],
    [Dsp].[name],
    [Dsp].[endpoint],
    [Dsp].[protocol],
    [Dsp].[protocolOptions]

FROM 
    [AdvertXrefDsp]

INNER JOIN [Dsp] on [AdvertXrefDsp].[dspId] = [Dsp].[dspId]

WHERE
    [advertId] in (@advert_ids)

Вопрос, как можно передать параметр @advert_ids в SQL запрос из кода?

Answer 1

Кусочек кода как вариант

   int advert_ids = 10;
   Console.WriteLine($"... WHERE [advertId] in ({advert_ids})");

Вариант 2

   public Form1()
    {
        InitializeComponent();
        Console.WriteLine($"....WHERE [advertId] in ({getparamarray()})");
    }

    public string getparamarray()
    {
        ..... //делаем что нужно
        return "1 and 2 and 3 and 4";
    }

Answer 2

Рассмотрим два способа.

Первый способ не нуждается в создании нового типа в БД. Но его не стоит использовать, если нужно передать очень много (тысячи) параметров.
Второй способ позволяет передавать любое количество данных в IN, но необходимо создать табличный тип.

Ваш запрос должен выглядеть так: SELECT ... WHERE [advertId] IN ({0}). На место {0} мы подставим параметры.

// ids - коллекция айдишников
var parameters = string.Join(", ", ids.Select((_, i) => "@p" + i));

string sql = GetQuerySelect ...;
sql = string.Format(sql, parameters);


using (var conection = new SqlConnection(_connectionString))
{
    await conection.OpenAsync();

    using (var cmd = conection.CreateCommand())
    {
        cmd.CommandText = sql;

        // Тут задаём значения параметрам
        for (int i = 0; i < ids.Length; i++)
        {
            // Укажите правильный тип SqlDbType
            cmd.Parameters.Add("@p" + i, SqlDbType.Int).Value = ids[i];
        }

        using (var reader = await cmd.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                ...
            }
        }
    }
}

Используем Table-Valued Parameters.

Для этого в БД нужно создать тип:

CREATE TYPE dbo.AdvertIds AS TABLE
(
    Id INT    
);

Запрос SQL при этом должен выглядеть так:

SELECT ... WHERE [advertId] IN (SELECT Id FROM @advert_ids)

Теперь используем его.

// ids - коллекция айдишников

var table = new DataTable();
table.Columns.Add("Id", typeof(int)); // укажите правильный тип
foreach (var id in ids)
    table.Rows.Add(id);

string sql = GetQuerySelect ...;

using (var conection = new SqlConnection(_connectionString))
{
    await conection.OpenAsync();

    using (var cmd = conection.CreateCommand())
    {
        cmd.CommandText = sql;
        
        var param = cmd.Parameters.Add("@advert_ids", SqlDbType.Structured);
        param.TypeName = "AdvertIds"; // указываем наш тип
        param.Value = table;

        using (var reader = await cmd.ExecuteReaderAsync())
        {
            while (await reader.ReadAsync())
            {
                ...
            }
        }
    }
}

Освобождайте ресурсы! Для этого оберните их вызовы в using или явно вызывайте метод Dispose. При этом не важно, создаёте команду прямо здесь или получаете из другого метода.

БЛОГ НА HUSL

Как передать в запрос параметр?

Ответы (2 шт):