Передача списка <> в хранимую процедуру SQL

Мне часто приходилось загружать несколько элементов в определенную запись в базе данных. Например: веб-страница отображает элементы для включения для одного отчета, все из которых являются записями в базе данных (отчет - это запись в таблице отчета, элементы - это записи в таблице элементов). Пользователь выбирает элементы для включения в один отчет через веб-приложение, и пусть они выбирают 3 элемента и отправляют. Этот процесс добавит эти 3 элемента в этот отчет, добавив записи в таблицу под названием ReportItems (ReportId, ItemId).

В настоящее время я буду делать что-то подобное в коде:

public void AddItemsToReport(string connStr, int Id, List<int> itemList)
{
    Database db = DatabaseFactory.CreateDatabase(connStr);

    string sqlCommand = "AddItemsToReport"
    DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

    string items = "";
    foreach (int i in itemList)
        items += string.Format("{0}~", i);

    if (items.Length > 0)
        items = items.Substring(0, items.Length - 1);

    // Add parameters
    db.AddInParameter(dbCommand, "ReportId", DbType.Int32, Id);
    db.AddInParameter(dbCommand, "Items", DbType.String, perms);
    db.ExecuteNonQuery(dbCommand);
}

и это в хранимой процедуре:

INSERT INTO ReportItem (ReportId,ItemId)
SELECT  @ReportId,
          Id
FROM     fn_GetIntTableFromList(@Items,'~')

Если функция возвращает одну таблицу столбцов целых чисел.

Мой вопрос таков: есть ли лучший способ справиться с чем-то подобным? Заметьте, я не спрашиваю о нормализации базы данных или что-то в этом роде, мой вопрос относится конкретно к коду.

Ответы

Ответ 1

Если вы перейдете на SQL Server 2008, вам будет предложена новая функция, называемая "Таблично-оцененные параметры".

Подробнее о TVP здесь и здесь или просто попросите Google "Табличные параметры SQL Server 2008" - вы найдете много информации и образцов.

Очень рекомендуется - если вы перейдете на SQL Server 2008...

Ответ 2

Ваша логика объединения строк может быть упрощена:

string items = 
    string.Join("~", itemList.Select(item=>item.ToString()).ToArray());

Это сэкономит вам некоторую конкатенацию строк, что дорого стоит в .Net.

Я не думаю, что что-то не так с тем, как вы сохраняете предметы. Вы ограничиваете поездки в db, что хорошо. Если ваша структура данных была более сложной, чем список int, я бы предложил XML.

Примечание: Меня спрашивали в комментариях, если это спасет нас от какой-либо конкатенации строк (это не указано). Я думаю, что это отличный вопрос и хотел бы следить за этим.

Если вы очистите открытую строку. С помощью Reflector вы увидите, что Microsoft использует пару небезопасных (в смысле .Net слова), включая использование указателя char и структуры под названием UnSafeCharBuffer. То, что они делают, когда вы действительно вскипятите его, использует указатели, чтобы пройти через пустую строку и создать соединение. Помните, что основная причина конкатенации строк настолько дорога в .Net, что новый строковый объект помещается в кучу для каждой конкатенации, потому что строка неизменна. Эти операции памяти дороги. String.Join(..) по существу выделяет память один раз, а затем работает с ней указателем. Очень быстро.

Ответ 3

Одна из возможных проблем с вашей техникой заключается в том, что она не обрабатывает очень большие списки - вы можете превысить максимальную длину строки для своей базы данных. Я использую вспомогательный метод, который объединяет целочисленные значения в перечисление строк, каждый из которых меньше указанного максимума (следующая реализация также дополнительно проверяет и удаляет идентификаторы дубликатов):

public static IEnumerable<string> ConcatenateValues(IEnumerable<int> values, string separator, int maxLength, bool skipDuplicates)
{
    IDictionary<int, string> valueDictionary = null;
    StringBuilder sb = new StringBuilder();
    if (skipDuplicates)
    {
        valueDictionary = new Dictionary<int, string>();
    }
    foreach (int value in values)
    {
        if (skipDuplicates)
        {
            if (valueDictionary.ContainsKey(value)) continue;
            valueDictionary.Add(value, "");
        }
        string s = value.ToString(CultureInfo.InvariantCulture);
        if ((sb.Length + separator.Length + s.Length) > maxLength)
        {
            // Max length reached, yield the result and start again
            if (sb.Length > 0) yield return sb.ToString();
            sb.Length = 0;
        }
        if (sb.Length > 0) sb.Append(separator);
        sb.Append(s);
    }
    // Yield whatever left over
    if (sb.Length > 0) yield return sb.ToString();
}

Затем вы используете его что-то вроде:

using(SqlCommand command = ...)
{
    command.Connection = ...;
    command.Transaction = ...; // if in a transaction
    SqlParameter parameter = command.Parameters.Add("@Items", ...);
    foreach(string itemList in ConcatenateValues(values, "~", 8000, false))
    {
        parameter.Value = itemList;
        command.ExecuteNonQuery();
    }
}

Ответ 4

Вы либо делаете то, что у вас уже есть, переходите в строку с разделителями, а затем разбираете значение таблицы, или другой вариант передается в стиле XML, и примерно то же самое:

http://weblogs.asp.net/jgalloway/archive/2007/02/16/passing-lists-to-sql-server-2005-with-xml-parameters.aspx

У меня еще не было возможности взглянуть на SQL 2008, чтобы узнать, добавили ли они какие-либо новые функции для работы с этим типом.

Ответ 6

См. http://www.sommarskog.se/arrays-in-sql-2005.html для подробного обсуждения этой проблемы и различных подходов, которые вы могли бы использовать.