TSQL: Передача списка/коллекции/множества в хранимую процедуру
- modified:
- reading: 9 minutes
Передача множества в хранимую процедуру довольно-таки частая задача. Встречается, например, при фильтрации какой-нибудь коллекции. Так же это может быть импорт данных в базу данных из внешних источников. Я рассмотрю несколько вариантов, которые можно использовать в вашем приложении: склеивание SQL запроса, передача строки списка параметров, разделенных запятой, Bulk Insert, а так же table-valued parameters (самый интересный вариант, пришедший с MS SQL Server 2008).
Предположим у нас есть список товаров и нам нужно отфильтровать его в зависимости от некоторых категорий товаров (“Телевизоры”, “Игровые приставки”, “DVD-плееры” или списка фирм “Фирма 1”, “Фирма 2”, “Фирма 3”). Изобразим как это может выглядеть в нашей Базе Данных
Ну и чтобы совсем было понятно, накидаем приблизительно интерфейс, который обычно бывает в таких случаях:
То есть у нас есть запрос выводящий нам список товаров, и есть возможность отфильтровать его по категориям или по фирмам, причем фильтровать конечно же будем по идентификаторам. Задача ясна. Как же теперь ее решать? Самый просто способ, который используют 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. А в целом когда какой подход выбирать – решать вам.