Проверьте, находится ли значение SqlParameter в диапазоне его целевого столбца SQL

Question

tldnr;

Как я должен проверить границы на моем SqlParameter значения, прежде чем пытаться поместить их в базу данных?

Более длинная версия:

Итак, у меня есть эти динамически генерируемые операторы SQL, где я передаю кучу SqlParameters.

То, как мы заявляем SqlParameterс просто

new SqlParameter("fieldName", value)

и мы позволяем среде исполнения выяснить, что такое dbtype.

Тем не менее, иногда оператор update / insert терпит неудачу, и мы хотели бы определить, какое поле слишком велико (поскольку наш сервер сообщает нам, что не удалось обновить поле, а не WHICH), выполнив проверку границ. То есть мы не можем поместить двухзначное число в столбец, который допускает только 1 цифру ( например, десятичную (1,0).)

У нас есть схема столбцов в памяти (information_schema.columns ftw), поэтому мы можем просто попытаться выполнить проверку границ для значения SqlParameter, но, поскольку значение является объектом и даже не обязательно числовым типом, как мне проверить, что значение находится в диапазоне?

Или я слишком усложняю задачу и вместо этого должен был обеспечить точность и масштаб при создании SqlParameters для начала? Или, что еще лучше, должны ли мы использовать типы, отражающие столбцы в базе данных?

Обновить:

Установка точности / масштаба, кажется, не имеет никакого значения, как видно из этого кода:

decimal d= 10.0M*(decimal)Math.Pow(10, 6);
SqlParameter p = new SqlParameter("someparam", SqlDbType.Decimal);
                    p.Precision = (byte)1;
                    p.Scale = (byte)0;
                    p.Value = d;

Console.WriteLine(p.SqlValue); // doesn't throw an error, i would think the sql value would be at most 10, not 10 million.

Answer 1

Кажется, что SqlParameter не проверяет на Value свойство устанавливается. А также DataColumn не позволяет указать либо Precision или же Scale так что не очень полезно. Однако есть способ:

1. Используя коллекцию информации схемы, которая у вас уже есть, динамически создайте массив SqlMetaData в зависимости от размера коллекции схем и заполните ее данными об имени и размере столбца:

   SqlMetaData[] _TempColumns = new SqlMetaData[_SchemaCollection.Count];
   
   loop-of-some-sort
   {
      switch (_SchemaCollection.DataType)
      {
         case "decimal":
            _TempColumns[_Index] = new SqlMetaData(
                        _SchemaCollection.Name,
                        SqlDbType.Decimal,
                        (byte)_SchemaCollection.Precision, 
                        (byte)_SchemaCollection.Scale
                     );
            break;
          case "others...."
      }
   }
   

2. Создать новый SqlDataRecord с использованием SqlMetaData[] из шага 1:

   SqlDataRecord _TempRow = new SqlDataRecord(_TempColumns);
   

3. Переберите _TempRow вызывая соответствующий Set метод для каждой позиции в try / catch:

   string _DataAintRight;
   
   try
   {
      _TempRow.SetDecimal(_Index, _SchemaCollection.Value);
   }
   catch
   {
     _DataAintRight = _SchemaCollection.Name;
     break;
   }
   

ЗАМЕТКИ:

• Это будет делать только ту же проверку, что и передача параметров в процесс. Это означает, что он будет молча обрезать слишком длинные значения, такие как слишком много цифр справа от десятичной точки и строку, которая превышает максимальный размер.

• Числовые типы фиксированной длины уже должны быть в их эквивалентных типах.Net (т.е. SMALLINT значение в Int16 переменная или свойство) и, следовательно, уже предварительно проверены. Если это действительно так, то нет никакой дополнительной выгоды от их тестирования. Но если они в настоящее время находятся в более общем контейнере (больший тип Int или даже строка), то здесь уместно тестирование.

• Если вам нужно знать, что строка будет обрезана, то это нужно проверить отдельно. По крайней мере, не так SqlMetaData, но в цикле и переключателе, просто проверьте длину строки в этом случае.

• Независимо от какого-либо из этих средств тестирования, лучше не создавать параметры, если.Net угадывает тип через: new SqlParameter("fieldName", value) или даже _Command.Parameters.AddWithValue(), Что касается вопроса о том, должны ли вы "указывать точность и масштаб при построении SqlParameters для начала", то, безусловно, да.

---

Другой вариант (который я могу уточнить завтра, когда у меня будет время обновить его), заключается в проверке всего, как если бы не было встроенных контейнеров, которые, как предполагается, являются отражением реальных типов данных базы данных / провайдера. Итак, есть два основных соображения, которые будут стимулировать реализацию:

• Являются ли исходные данные в настоящее время строго типизированными или все они сериализуются в виде строк?

  а также

• Нужно ли знать, будет ли значение усечено (особенно в тех случаях, когда в противном случае значение будет усечено без предупреждения, не вызывая ошибки, которая может привести к неожиданному поведению). Проблема здесь заключается в том, что вставка данных в поле в таблице, которое превышает указанную максимальную длину, приведет к string or binary data will be truncated ошибка. Но при передаче данных параметру (то есть не напрямую в таблицу) эти данные будут усечены без возникновения ошибки. Иногда это нормально, но иногда это может привести к ситуации, когда входной параметр был указан неверно (или был корректным, но затем поле было расширено, а параметр никогда не обновлялся, чтобы соответствовать новой длине) и могло обрезать концы некоторых значений, которые остаются незамеченными, пока клиент не сообщит, что "в отчете что-то не так, и, кстати, это происходило время от времени, может быть, четыре или пять месяцев, но я действительно занят и постоянно забывал упоминать об этом, может быть, прошло девять месяцев, я не помню, но да, что-то не так ". Я имею в виду, как часто мы тестируем наш код, передавая максимальное значение для каждого параметра, чтобы убедиться, что система может его обработать?

Если исходные данные находятся в соответствующих типах.Net:

Есть несколько, которые не нужно проверять, поскольку числовые типы фиксированной длины одинаковы для.Net и SQL Server. Те, которые предварительно проверены просто существующими в их соответствующих.Net типах:

• bool -> BIT
• байт -> TINYINT
• Int16 -> SMALLINT
• Int32 -> INT
• Int64 -> BIGINT
• Double -> FLOAT
• Single -> REAL
• Guid -> УНИКАЛЬНЫЙ ОПРЕДЕЛИТЕЛЬ

Некоторые из них необходимо проверять только на усечение (если это важно), поскольку их значения всегда должны находиться в том же диапазоне, что и их аналоги в SQL Server. Имейте в виду, что здесь мы говорим о строгом усечении, когда значения передаются в параметры меньшего масштаба, но фактически округляются (ну, в 5), когда вставляются непосредственно в столбец, имеющий меньший масштаб. Например, отправка значения DateTime с точностью до 5 десятичных знаков усекает 3 крайних правых числа при передаче в параметр, определенный как DATETIME2 (2).

• DateTimeOffset -> DATETIMEOFFSET (0 - 7)
• DateTime -> DATETIME2 (0 - 7)
• DateTime -> DATE: с 0001-01-01 по 9999-12-31 (без времени)
• TimeSpan -> TIME (0 - 7): 00:00: 00.0000000 до 23:59:59.9999999

Некоторые из них необходимо проверить, чтобы убедиться, что они не выходят за допустимый диапазон для типа данных SQL Server, поскольку выход за пределы диапазона может вызвать исключение. Они также, возможно, должны быть проверены на усечение (если это вызывает озабоченность). Имейте в виду, что здесь мы говорим о строгом усечении, когда значения передаются в параметры меньшего масштаба, но фактически округляются (ну, в 5), когда вставляются непосредственно в столбец, имеющий меньший масштаб. Например, отправка значения DateTime потеряет все секунды и доли секунды при передаче параметру, определенному как SMALLDATETIME.

• DateTime -> DATETIME: 1753-01-01 - 9999-12-31, 00:00:00.000 - 23: 59: 59.997
• DateTime -> SMALLDATETIME: с 1900-01-01 по 2079-06-06, с 00:00 до 23:59 (без секунд)
• Десятичное число -> ДЕНЬГИ: от -922 337 203 685 477,5808 до 922 337 203 685 477,5807
• Десятичное число -> МАЛЕНЬКИЕ ДАННЫЕ: от -214 748,3648 до 214 748,3647
• Десятичное число -> DECIMAL: диапазон = от -9[цифр = (точность - масштаб)] до 9 [цифр = (точность - масштаб)], усечение зависит от определенного масштаба

Следующие строковые типы будут молча обрезаться при передаче параметру с максимальной длиной, которая меньше длины их значения, но будет ошибка с String or binary data would be truncated если непосредственно вставлено в столбец с максимальной длиной, которая меньше длины их значения:

• byte [] -> BINARY
• byte [] -> VARBINARY
• строка -> CHAR
• строка -> VARCHAR
• строка -> NCHAR
• строка -> NVARCHAR

Следующее хитро, поскольку истинная проверка требует знания дополнительных параметров, с которыми она была создана в базе данных.

• string -> XML - по умолчанию поле XML не типизировано и поэтому очень мягко относится к "правильному" синтаксису XML. Однако это поведение можно изменить, связав коллекцию схем XML (1 или более XSD) с полем для проверки (см. Также: Сравнение типизированного XML с нетипизированным XML). Таким образом, истинная проверка поля XML будет включать получение этой информации, если она существует, и, если это так, проверку на соответствие этим XSD. По крайней мере, это должен быть правильно сформированный XML (т.е. '<b>' потерпит неудачу, но '<b />' преуспеет).

Для вышеуказанных типов предварительно проверенные типы можно игнорировать. Остальные могут быть проверены в switch(DestinationDataType) состав:

• Типы, которые должны быть проверены для диапазонов, могут быть выполнены следующим образом

  case "smalldatetime":
    if ((_Value < range_min) || (_Value > range_max))
    {
      _ThisValueSucks = true;
    }
    break;
  

• Усечение Numeric/DateTime, если оно проверяется, может быть лучше сделать ToString() и использовать IndexOf(".") для большинства или IndexOf(":00.0") для DATE и SMALLDATETIME, чтобы найти количество цифр справа от десятичной дроби (или начиная с "секунд" для SMALLDATETIME)

• Усечение строки, если проверяется, является простым вопросом проверки длины.

• Десятичный диапазон можно проверить численно:

  if ((Math.Floor(_Value) < -999) || (Math.Floor(_Value) > 999))
  

  или же:

  if (Math.Abs(Math.Floor(_Value)).ToString().Length <= DataTypeMaxSize)
  

• Xml

  • поскольку XmlDocument предварительно проверен вне потенциальной проверки XSD, связанной с полем XML
  • поскольку String можно сначала использовать для создания XmlDocument, который оставляет только потенциальную проверку XSD, связанную с полем XML

Если исходными данными являются все строки:

Тогда все они должны быть проверены. Для этого вы бы сначала использовать TryParse методы, связанные с каждым типом. Затем вы можете применять правила, как указано выше для каждого типа.