С помощью XML.
Например, нам необходимо получить список разрешений для различных
ролей, можно обратиться к базе по каждой роле, но затраты ресурсов на
десять обращений к хранимой процедуре значительно превышают затраты на
одно обращение с передачей десяти параметров.
Сначала формируем строку из XML тегов с нашими данными:
//Формирование xml для пердачи коллекции ролей в хранимую процедуру
string xmlRoles = "<root>";
foreach (UserRole role in roles)
{
xmlRoles += "<role id=\"" + role.Id + "\" />";
}
xmlRoles += "</root>";
после чего получим строку вида:
<root>
<role id = "1"/>
<role id = "2"/>
</root>
// Инициализация комманды выборки данных
SqlCommand sqlCmd = _sqlConn.CreateCommand();
sqlCmd.CommandType = CommandType.StoredProcedure;
sqlCmd.CommandText = "[spGetMenuItemsByTemplate]";
// Добавление параметра для ХП
sqlCmd.Parameters.Add(new SqlParameter("@Roles", SqlDbType.Text));
// Инициализация параметра ХП
sqlCmd.Parameters["@Roles"].Value = xmlRoles;
В хранимой процедуре тоже не прийдется делать что то особо сложное,
переданная строка при помощи системной функции MS SQL -
sp_xml_preparedocument превращается в таблицу с одним полем - id,
список значений этого поля и есть наш массив значений. Теперь можно
делать JOIN и получать на выходе набор необходимых данных за одно
обращение БД.
CREATE PROCEDURE dbo.spGetMenuItemsByTemplate ( @Roles AS TEXT) AS
DECLARE @RolesTable TABLE (role_id INT)
-- Разбор xml документа
DECLARE @hDoe INT
EXEC sp_xml_preparedocument @hDoe OUTPUT, @Roles
INSERT INTO @RolesTable
SELECT [id]
FROM OPENXML(@hDoe,'root/role',1)
WITH ( [id] INT)
-- Выборка доступных действий по шаблону для роли
SELECT DISTINCT
pt.menu_id,
pt.action_id
FROM
t_permission_template AS pt
INNER JOIN @RolesTable AS rt ON rt.role_id = pt.role_id
ORDER BY
pt.menu_id
P.S. Тем кому нехватило приведенного примера рекомендую ознакомиться с "Массивы и списки в SQL Server"