Учим DataContext правильно воспринимать Null и DBNull.Value в параметрах

Категория: Код

– Автор: Игорь (Администратор)

С каждым витком развития технологий взаимодействия с базой данных, изменялись представление об использовании параметров. Если вы использовали RerordSet в скриптовом ASP, то, наверное, ни один раз составляли запрос ручной вставкой в текст. Согласитесь, что намного проще добавить единственный параметр оператором "&", нежели каждый раз составлять корректный запрос и определять отдельный параметр. Ведь, последнее не только захламляло код, но и несколько удивляло. Зачем громоздить конструкции для простых и давно известных задач. Понятно, идеология, валидность и прочее. Но вопрос "зачем?" все равно оставался. 

Приход DataSet-ов в Asp.Net несколько упростил задачу. Удобство обработки больших массивов данных. Никакой лишней возни с каждой строкой. Вам достаточно было один раз составить шаблон запроса сохранения. Если же вы использовали инструменты Visual Studio, то вам вообще ничего не приходилось делать. Просто загрузите схему БД и радуйтесь. Однако, желание составить запрос вручную все равно оставалось. Ну, никак не хочется ради одного двух параметров вызывать специальные функции для создания оных. Понятно, концептуальность и все дела. Тем не менее, вопрос "зачем?" все равно оставался.

Примечание: Конечно, у DataSet-ов были свои проблемы. Тем не мене, в данном случае это не важно, так как речь идет об использовании параметров и не более.

И вот, казалось бы, появился удобный инструмент для составления запросов, позволяющий решать давно известную задачу преобразования стандартных типов данных в типы базы данных. Linq2Sql. Вызовите ExecuteQuery. Укажите в теле запроса параметры в виде '{номер}' и передайте параметры в нужном порядке. Никаких лишних телодвижений. Нет эти страшных "концепций". Достаточно написать что-то вроде:

dataContext.ExecuteQuery<ResultType>(
    "exec dbo.someProc {0}, {1}",
    intParam, stringParam
);

Но, на практике оказалось не все так замечательно. Дело в том, что функции Microsoft не умеют корректно обрабатывать Null / DBNull.Value. Несмотря на все заявления в MSDN. Т.е. если вы передадите null или DBNull.Value в качестве параметра, то вы увидите красивое сообщение с ошибкой:

• В случае Null - A query parameter cannot be of type 'System.Object'
• В случае DBNull.Value - Unexpected type code: DBNull (which give this as a stack trace)

Обычно, после такого рода ошибок в голове проносится что-то вроде "эх, опять все ручками составлять". Ведь, какое бы решение не было концептуальное и удобное, если оно не позволяет получать корректные результаты, то такое решение бессмысленно. Конечно, можно использовать пограничные значения и другие изощренные способы. Но, это все превратит ваш код в "нечто", если не сказать больше.

Тем не менее, есть способ намного проще. Дело в том, что если один из параметров оказался Null или DBNull.Value, то нужно просто это указать в теле запроса. Т.е. если бы stringParam из первого примера был бы null, то запрос должен выглядеть следующим образом:

dataContext.ExecuteQuery<ResultType>(
    "exec dbo.someProc {0}, NULL",
    intParam
);

При таком вызове, код выполнится без каких-либо проблем. Однако, от таких вызовов веет чем-то вроде "switch" и "if-else if-else". Так что если вы не любитель составлять большие и громоздкие конструкции вызовов, то стоит использовать небольшую обертку. Например, можно расширить стандартный набор функций DataContext. Получится примерно следующий код.

// Исправляем баг мелкомягких
public static class DataContextExtend
{
    // Формируем "правильный" запрос
    public static string FixNullableQuery(this DataContext dataContext, string commandText, object[] parameters)
    {
        // Исправляем только копию запроса. 
        // Сам запрос не изменяем, так как он может использоваться в будущем
        var result = string.Copy(commandText);
 
        // Проходимся по всем параметрам
        // И заменяем все null параметры на NULL в запросе 
        for (int i = 0; i < parameters.Length; i++)
        {
            if (parameters[i] == null || Convert.IsDBNull(parameters[i]))
            {
                result = result.Replace("{" + i.ToString() + "}", "NULL");
            }
        }
        return result;
    }
 
    // Формируем "правильный" набор параметров
    public static object[] FixNullableParameters(this DataContext dataContext, object[] parameters)
    {
        // Исправляем копию параметров,
        // так как набор параметров может использоваться в будущем
        object[] nullableParams = new object[parameters.Length];
        parameters.CopyTo(nullableParams, 0);
 
        // Проходимся по всем параметрам.
        // Заменяем все null параметры на 0. 
        // Можно использовать и другое значение. Это не важно, все равно параметр не будет использоваться
        for (int i = 0; i < parameters.Length; i++)
        {
            if (parameters[i] == null || Convert.IsDBNull(parameters[i]))
            {
                nullableParams[i] = 0;
            }
        }
 
        return nullableParams;
    }
 
    // Вызов запроса, с учетом null параметров
    public static IEnumerable<OutType> ExecuteQueryNull<OutType>(this DataContext dataContext, string query, params object[] parameters)
    {
        return dataContext.ExecuteQuery<OutType>(
                    dataContext.FixNullableQuery(query, parameters), 
                    dataContext.FixNullableParameters(parameters)
                );
    }
 
    // Вызов запроса, с учетом null параметров
    public static IEnumerable ExecuteQueryNull(this DataContext dataContext, Type OutType, string query, params object[] parameters)
    {
        return dataContext.ExecuteQuery(OutType, 
                dataContext.FixNullableQuery(query, parameters), 
                dataContext.FixNullableParameters(parameters)
            );
    }
}

Теперь, единственное, что вам останется, это подключать расширение класса и вызывать вместо привычного ExecuteQuery функцию ExecuteQueryNull, которая будет преобразовывать запрос и формировать нужные параметры для исполнения запроса. При этом сам запрос и его параметры будут оставаться без изменения. Например, для первого запроса не будет уже важно нулевые ли значения у типов:

dataContext.ExecuteQueryNull<ResultType>(
    "exec dbo.someProc {0}, {1}",
    intParam, stringParam
);