TSQL: Передача списка/коллекции/множества в хранимую процедуру

    • .NET
    • TSQL
    • SQL Server
    • Transact SQL
    • Bulk Insert
    • Table-Valued Parameters
    • Stored Procedures
  • modified:
  • reading: 9 minutes

Передача множества в хранимую процедуру довольно-таки частая задача. Встречается, например, при фильтрации какой-нибудь коллекции. Так же это может быть импорт данных в базу данных из внешних источников. Я рассмотрю несколько вариантов, которые можно использовать в вашем приложении: склеивание SQL запроса, передача строки списка параметров, разделенных запятой, Bulk Insert, а так же table-valued parameters (самый интересный вариант, пришедший с MS SQL Server 2008).

Предположим у нас есть список товаров и нам нужно отфильтровать его в зависимости от некоторых категорий товаров (“Телевизоры”, “Игровые приставки”, “DVD-плееры” или списка фирм “Фирма 1”, “Фирма 2”, “Фирма 3”). Изобразим как это может выглядеть в нашей Базе Данных

Ну и чтобы совсем было понятно, накидаем приблизительно интерфейс, который обычно бывает в таких случаях:

Фильтр
Категории
  • Телевизоры
  • Игровые приставки
  • DVD-плееры
  • Холодильники
  • Пылесосы
Фирмы
  • Фирма 1
  • Фирма 2
  • Фирма 3
  • Фирма 4
  • Фирма 5
Товары
Товар Категория Фирма
Телевизор 32 Телевизоры Фирма 1
Пылесос Пылесосы Фирма 3
Игровая приставка Игровые приставки Фирма 5

То есть у нас есть запрос выводящий нам список товаров, и есть возможность отфильтровать его по категориям или по фирмам, причем фильтровать конечно же будем по идентификаторам. Задача ясна. Как же теперь ее решать? Самый просто способ, который используют Junior программисты – это склейка SQL инструкции в коде C#, примерно, это может выглядеть так

List<int> categories = new List<int>() { 1, 2, 3 };
 StringBuilder sbSql = new StringBuilder();
sbSql.Append(  @"
    select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName 
    from Item i
      inner join Firm f on i.FirmId = f.FirmId
      inner join Category c on i.CategoryId = c.CategoryId    where c.CategoryId in (");
if (categories.Count > 0)
{  for (int i = 0; i < categories.Count; i ++)
  {    if (i != 0)
      sbSql.Append(",");
    sbSql.Append(categories[i]);
  }
}else
{  sbSql.Append("-1"); // It is for empty result when no one category selected
}sbSql.Append(")");
 string sqlQuery = sbSql.ToString();
 DataTable table = new DataTable();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=TableParameters;Integrated Security=SSPI;"))
{
  connection.Open();  using (SqlCommand command = new SqlCommand(sqlQuery, connection))
  {    using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
    {
      dataAdapter.Fill(table);
    }
  }
}
 //TODO: Working with table

Для того, чтобы писать поменьше кода будем фильтровать только по категориям. В приведенном коде первая строка – это список идентификаторов категорий, которые выбрал пользователь (выбранные checkbox'ы), само собой нам нет необходимости хранить имена категорий, для фильтрации хватит и идентификаторов. Проблемы этого решения очевидны – в некоторых случаях подверженность SQL-инъекциям (например, в случае строк-идентификаторов, которые мы получаем с веб-формы - пользователь с легкостью может их подменить), не очень приятное сопровождение кода, при достаточно большом количестве категорий в фильтре строка запроса будет быстро расти. И еще одна проблема – такой код невозможно поместить в хранимую процедуру (можно конечно клеить запрос и на SQL сервере). Это решение можно назвать Решение 0, так как оно применяется либо из-за лени, либо потому что так быстро.

Решение 1. Строка – список значений, разделенных запятой

Все остальные варианты будут использоваться в связке с хранимыми процедурами. Первый вариант – это передача параметра – строки, которая состоит из списка идентификаторов, разделенных запятой, например так ‘1,2,3,4,’. Первое, что нужно сделать - это создать функцию, которая будет из этой строки создавать таблицу и возвращать ее, назовем данную функцию Split:

if object_id('Split') is not null 
    drop function split
go
 create function dbo.Split
(    @String int
)returns @SplittedValues table
(    Id varchar(50) primary key
)as
begin
    declare @SplitLength int, @Delimiter varchar(5)
    set @Delimiter = ','
    while len(@String) > 0
    begin 
        select @SplitLength = (case charindex(@Delimiter,@String) when 0 then
            len(@String) else charindex(@Delimiter,@String) -1 end)
         insert into @SplittedValues
        select cast(substring(@String,1,@SplitLength) as int)
        select @String = (case (len(@String) - @SplitLength) when 0 then  ''
            else right(@String, len(@String) - @SplitLength - 1) end)
    end 
return  
end 

Теперь мы можем использовать эту функцию в нашей хранимой процедуре для поиска продуктов

if object_id('FindItems') is not null 
    drop proc FindItems
go
 set ansi_nulls on
go 
set quoted_identifier on
go
 create proc FindItems
(    @categories varchar(max)
)as
begin
  select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName 
  from Item i
    inner join Firm f on i.FirmId = f.FirmId
    inner join Category c on i.CategoryId = c.CategoryId
    inner join dbo.Split(@categories) cf on c.CategoryId = cf.Id
end 

Ну и соответственно C# код, при помощи которого мы сможем получить список продуктов:

List<int> categories = new List<int>() { 1, 2, 3 };
 DataTable table = new DataTable();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=TableParameters;Integrated Security=SSPI;"))
{  connection.Open();
  using (SqlCommand command = new SqlCommand("FindItems", connection) { CommandType = CommandType.StoredProcedure })
  {    command.Parameters.AddWithValue("@categories", string.Join(",", categories));
    using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
    {      dataAdapter.Fill(table);
    }
  }
}
 //TODO: Working with table

Недостатки данного подхода так же очевидны, если это будут строки с возможными запятыми или объекты с композитными ключами то с данным подходом будут трудности. Но в простых случаях он более чем достоин внимания и используется повсеместно.

Решение 2. BULK INSERT

Проблемы, которые были в Решение 1 можно решить при помощи Bulk Insert – эта процедура будет копировать из серверного кода C# из объекта DataTable в экземпляр SQL приложения во временную таблицу данные, с которыми мы потом сможем работать. Давайте сначала перепишем нашу процедуру FindItems

if object_id('FindItems') is not null 
    drop proc FindItems
go
 set ansi_nulls on
go 
set quoted_identifier on
go
 create proc FindItems
as
begin
    if object_id('tempdb..#FilterCategory') is null 
    begin
        raiserror('#FilterCategory(id int) should be created', 16, 1)
        return
    end
    select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName 
    from Item i
        inner join Firm f on i.FirmId = f.FirmId
        inner join Category c on i.CategoryId = c.CategoryId
        inner join #FilterCategory cf on c.CategoryId = cf.Id
end 

Теперь эта процедура будет ожидать, что перед тем как ее будут использовать создадут временную табличку #FilterCategory, которую она уже будет использовать. Кода на C# нам придется писать побольше чем в прошлый, давайте создадим отдельный класс-репозиторий ItemsRepository

public class ItemsRepository
{  public static DataTable FindItems(List<int> categories)
  {    DataTable tbCategories = new DataTable("FilterCategory");
    tbCategories.Columns.Add("Id", typeof (int));
    categories.ForEach(x => tbCategories.Rows.Add(x));
     using (
      SqlConnection connection =        new SqlConnection("Data Source=(local);Initial Catalog=TableParameters;Integrated Security=SSPI;"))
    {
      connection.Open();      using (SqlTransaction transaction = connection.BeginTransaction())
      {        try
        {          string tableName = string.Format("tempdb..#{0}", tbCategories.TableName);
 
          CreateTableOnSqlServer(connection, transaction, tbCategories, tableName);
          CopyDataToSqlServer(connection, transaction, tbCategories, tableName);
           DataTable result = new DataTable();
          using (SqlCommand command = new SqlCommand("FindItems", connection, transaction)
                                        {CommandType = CommandType.StoredProcedure})
          {            using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
            {
              dataAdapter.Fill(result);
            }
          }
          transaction.Commit();          return result;
        }        catch
        {
          transaction.Rollback();          throw;
        }
      }
    }
  }
   private static void CopyDataToSqlServer(SqlConnection connection, SqlTransaction transaction, DataTable table,
                                          string tableName)
  {    using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction)
                                    {
                                      DestinationTableName = tableName
                                    })
    {
      bulkCopy.WriteToServer(table);
    }
  }
   private static void CreateTableOnSqlServer(SqlConnection connection, SqlTransaction transaction, DataTable table,
                                             string tableName)
  {    StringBuilder sb = new StringBuilder();
     sb.AppendFormat("create table {0}(", tableName);
    foreach (DataColumn column in table.Columns)
    {      sb.AppendFormat("{0} {1} {2}",
                      table.Columns.IndexOf(column) == 0 ? string.Empty : ",",
                      column.ColumnName, GetSqlType(column.DataType));
    }    sb.Append(")");
     using (SqlCommand command = new SqlCommand(sb.ToString(), connection, transaction))
    {
      command.ExecuteNonQuery();
    }
  }
   private static string GetSqlType(Type type)
  {    if (type == typeof (string))
      return string.Format("{0}(max)", SqlDbType.VarChar);
    else if (type == typeof (int))
      return SqlDbType.Int.ToString();
    else if (type == typeof (bool))
      return SqlDbType.Bit.ToString();
    else if (type == typeof (DateTime))
      return SqlDbType.DateTime.ToString();
    else if (type == typeof (Single))
      return SqlDbType.Float.ToString();
    else throw new NotImplementedException();
  }
}

Метод FindItems создает объект DataTable, записывает в него список идентификаторов категорий, по которым хотим отфильтровать, дальше метод открывает новую транзакцию, создает на сервере временную табличку #FilterCategories, копирует содержимое DataTable в эту таблицу и вызывает хранимую процедуру FindItems. Замечу, что временные таблицы tempdb..#<TableName> живут только в определенном Scope, в нашем случае это транзакция (потому если несколько пользователей вызовут этот метод в один момент, то ничего страшного не будет и они друг другу не помешают), и потому что таблица живет только на время жизни транзакции, то и удалится она при завершении транзакции (правда, все равно рекомендуют удалять временные таблицы самим именно тогда, когда она уже вам не нужна).

Я этот подход особенно часто использовал при импорте данных из внешних источников, вроде Excel файлы или какие-нибудь другие.

Давайте найдем минусы данного подхода. Минус “больше кода” сразу выбрасываем, так как это все можно зарефакторить и вынести в специальные классы во внутренний фреймворк и забыть. Другие минусы – это лишние создания временных таблиц, ну и соответственно лишние запросы к базе данных. Так же могут быть проблемы, если внутри одной хранимой процедуры запускаете другую, которая может сама создает временную таблицу с таким же именем, либо когда происходит рекурсивный вызов. Еще недостаток данного подхода в тестировании самой процедуры, в смысле в работе с ней из Management Studio, нужно постоянно писать скрипт для создания временной таблицы (а это еще нужно вспомнить какая у нее структура, да как называется).

create table #FilterCategory(id int)
insert into #FilterCategory ( id ) values  ( 1  )
insert into #FilterCategory ( id ) values  ( 2 )
insert into #FilterCategory ( id ) values  ( 3  )
insert into #FilterCategory ( id ) values  ( 4  )
 exec FindItems
 drop table  #FilterCategory

Решение 3. Table-Valued Parameters (Database Engine)

И последнее решение – это использование table-valued parameters (о которых к сожалению я узнал не так давно, надо внимательнее смотреть What’s new в новых версиях продуктов, которые мы используем). Этот подход очень похож на BULK-INSERT, только немного упрощает его. Использовать его можно с базами данных MS SQL 2008 и выше. Опять переписываем процедуру FindItems, не забываем создать тип-таблицу Identifiers

if object_id('FindItems') is not null 
    drop proc FindItems
go
 if exists(select * from sys.types where name = 'Identifiers')
    drop type Identifiers
go
 create type Identifiers AS TABLE 
( id int primary key);
go
 set ansi_nulls on
go 
set quoted_identifier on
go
 create proc FindItems
(
    @categories Identifiers readonly
)as
begin
    select i.Name as ItemName, f.Name as FirmName, c.Name as CategoryName 
    from Item i
        inner join Firm f on i.FirmId = f.FirmId
        inner join Category c on i.CategoryId = c.CategoryId
        inner join @categories cf on c.CategoryId = cf.Id
end 
go

Ну и переписываем теперь серверный код

List<int> categories = new List<int>() { 1, 2, 3 };
 DataTable tbCategories = new DataTable("FilterCategory");
tbCategories.Columns.Add("Id", typeof(int));
categories.ForEach(x => tbCategories.Rows.Add(x));
 DataTable table = new DataTable();
using (SqlConnection connection = new SqlConnection("Data Source=(local);Initial Catalog=TableParameters;Integrated Security=SSPI;"))
{
  connection.Open();  using (SqlCommand command = new SqlCommand("FindItems", connection) { CommandType = CommandType.StoredProcedure })
  {    command.Parameters.AddWithValue("@categories", tbCategories);
    using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
    {
      dataAdapter.Fill(table);
    }
  }
}
 

Он стал намного проще, чем был с Bulk Insert и работать в Management Studio с процедурой стало чуть-чуть попроще

declare @categories Identifiers
 insert into @categories ( id ) values  ( 1  )
insert into @categories ( id ) values  ( 2 )
insert into @categories ( id ) values  ( 3  )
insert into @categories ( id ) values  ( 4  )
 exec FindItems @categories

У table-valued parameters есть некоторые ограничения, вроде того, что данные параметры всегда должны быть readonly. По поводу производительности в сравнении с Bulk Insert в этой статье Table-Valued Parameters (Database Engine) приводится таблица, в которой поясняется когда лучше использовать table-valued parameters, а когда Bulk Insert. А в целом когда какой подход выбирать – решать вам.

See Also