Передача списка строк в хранимую процедуру SQL в C#

Быстрый ответ

Существует несколько способов передать список в хранимую процедуру SQL Server. Однако оптимальным вариантом будет использовать параметры с типом таблицы (TVPs). Начало работы предполагает определение пользовательского типа таблицы в SQL:

CREATE TYPE dbo.ListType AS TABLE ( Item INT );

Затем следует создать хранимую процедуру, которая будет принимать TVP:

CREATE PROCEDURE InsertItems @List dbo.ListType READONLY AS BEGIN
    -- Вставка элементов из списка 
    INSERT INTO TargetTable (ItemColumn)
    SELECT Item FROM @List
END;

В C# заполните DataTable списком и передайте его в качестве параметра в хранимую процедуру:

// Создание DataTable и добавление столбца
DataTable table = new DataTable();
table.Columns.Add("Item", typeof(int));

// Заполнение DataTable элементами списка
myList.ForEach(i => table.Rows.Add(i));

using(SqlCommand cmd = new SqlCommand("InsertItems", conn) {
    CommandType = CommandType.StoredProcedure
}) {
    // Передача параметра с таблицей 
    cmd.Parameters.Add(new SqlParameter("@List", table) {
        SqlDbType = SqlDbType.Structured,
        TypeName = "dbo.ListType"
    });
    conn.Open(); // Открывание подключения 
    cmd.ExecuteNonQuery(); // Выполнение запроса 
    conn.Close(); // Закрытие соединения
}

С использованием TVP осуществляется прямая передача данных из C# в SQL Server.

Повышение уровня в SQL

Работа с большими объемами данных

При работе с большими списками полезно разбивать данные на меньшие порции. Это помогает лучше контролировать использование памяти и предотвратить таймауты:

int batchSize = 1000; // Размер порции данных 
for(int i = 0; i < myList.Count; i += batchSize) {
    // Создание отдельной таблицы и ее заполнение
    DataTable batchTable = new DataTable();
    batchTable.Columns.Add("Item", typeof(int));
    myList.GetRange(i, Math.Min(batchSize, myList.Count – i)).ForEach(item => batchTable.Rows.Add(item));
    
    // После этого, как ранее описано, используется 'batchTable'
}

Вопросы безопасности

Безопасность является приоритетом. Использование параметризированных запросов закрывает "дорогу" для SQL-инъекций.

Выбор инструментов

В зависимости от задачи выберите подходящий инструмент: TVP, XML, JSON или строки с разделителями.

Парсинг XML на SQL Server:

CREATE PROCEDURE InsertXMLItems @ListXML XML AS BEGIN
    -- Парсер XML и вставка в таблицу
    INSERT INTO TargetTable (ItemColumn)
    SELECT Tbl.Col.value('.', 'INT')
    FROM @ListXML.nodes('//item') Tbl(Col)
END;

В общем, TVP является наиболее эффективной техникой с точки зрения скорости выполнения и масштабируемости.

Визуализация

Вся процедура представляет из себя следующие шаги:

1. Упаковываем: преобразуем список в структурированный параметр.
2. Передаем: отправляем данный параметр в хранимую процедуру.
3. Обрабатываем: хранимая процедура выполняет работу с данными.

Технически это несложно!

Усиление процедуры

Контроль ресурсов в C#

Для корректного контроля над ресурсами, включая подключения к базе данных, используйте блоки try-catch-finally и инструкции using.

Альтернативы TVP

Вместо TVP могут быть использованы функции с типом table (TVFs) или функции разделения строки, если TVP не подходят лучше.

Учет типов данных

Если вы работаете с особыми типами данных, убедитесь, что типы в пользовательском типе таблицы точно соответствуют вашим типам данных, либо используйте nvarchar(MAX) для большей гибкости.

Полезные материалы

1. Параметры с типом table – ADO.NET — рекомендации от Microsoft.
2. C# Вставка в таблицу SQL с использованием параметра списком – Stack Overflow — обсуждение этой проблемы и возможные решения.
3. Изучите момент... — опыт сообщества C# Corner.
4. Использование функций с типом table в SQL Server — подробное руководство о функциях с типом table.