Блог

У меня есть фрагмент кода, который вставляет образец данных ([id datetime2] …) в базу данных SQLAzure.

Позже для каждого вызова я делаю select, чтобы узнать, есть ли PrimaryKey уже в DB [id datetime2], поэтому я обновляю его, в противном случае я делаю insert. Проблема в том, что select ничего не возвращает, но при вставке будет получена ошибка с дублированным ключом. (!?)

Я создал образец кода, воспроизводящий мою проблему, не каждый раз, но большую ее часть.

Если я заменю

 command.Parameters.AddWithValue("@Date", date);
 

Автор:

 command.Parameters.Add("@Date", SqlDbType.DateTime2).Value = date;
 

Это будет работать, но я хотел бы понять, почему команды select и insert не совпадают.

 //CODE - BEGIN
//.NetCore 2.2
//SqlDatabase Azure

var date = DateTime.Now;

command.Parameters.AddWithValue("@Id", 1); 
command.Parameters.AddWithValue("@Date", Date); //{19:33:22.7727095}
command.CommandText = "INSERT INTO Answer (Id , Date) VALUES (@Id, @Date)";
command.ExecuteNonQuery();

/*DB
**IdDevice  Date
**1 2019-04-18 19:33:22.7733333
*/

//Retry
command.CommandText = "SELECT TOP 1 Id FROM Answer where Id = @Id AND Date = @Date;";
var exist = command.ExecuteScalar();
if (exist == null)
{
    throw;
}
//CODE - END
 

Это всего лишь краткий пример для воспроизведения поведения, я использую точно такой же параметр для insert select, но select ничего не вернет.
Возможно, .net Datetime преобразуется в SqlDateTime для вставки и в SqlDateTime2 для выбора…

AddWithValue выводит SqlDbType из предоставленного типа .NET как DateTime , а не DateTime2 . Затем доли секунды усекаются с точностью до 3 и округляются до 1/300 секунды, чтобы соответствовать менее точному типу данных параметра. Вы увидите это менее точное значение, если вы запрашиваете базу данных без WHERE предложения.

Усечение / округление не происходит с explict SqlDbType.DateTime2 , поскольку .NET DateTime и SqlDbType.DateTime2 оба поддерживают дробные секунды с точностью до 7.

Это еще одна причина, которой следует избегать AddWithValue .

Смешивание типов datetime / datetime2 также может привести к неожиданному поведению, как показано в вашем SELECT запросе. datetime2 имеет более высокий приоритет типа данных, чем datetime, поэтому значение доли секунды сравнивается с использованием datetime фактического значения 1/300 секунды, увеличенного до большей точности, а не округленного / усеченного. Рассмотрим эти примеры T-SQL:

 --these values compare not equal because the datetime value of 1/300 is actually .003333333333...
DECLARE @datetime datetime =   '2019-04-19T00:00:00.003';
DECLARE @datetime2 datetime2 = '2019-04-19T00:00:00.003';
IF @datetime = @datetime2 PRINT 'EQUAL' ELSE PRINT 'NOT EQUAL';
GO

--these values compare not equal because the datetime value is actually .006666666666...
DECLARE @datetime datetime =   '2019-04-19T00:00:00.007';
DECLARE @datetime2 datetime2 = '2019-04-19T00:00:00.007';
IF @datetime = @datetime2 PRINT 'EQUAL' ELSE PRINT 'NOT EQUAL';
GO

--these values comare equal because the datetime value is .010000000000...
DECLARE @datetime datetime =   '2019-04-19T00:00:00.010';
DECLARE @datetime2 datetime2 = '2019-04-19T00:00:00.010';
IF @datetime = @datetime2 PRINT 'EQUAL' ELSE PRINT 'NOT EQUAL';
GO
 

Хотя это изменение, нарушающее поведение сравнения, можно контролировать, запустив на уровне совместимости с базой данных 120 или ниже, было бы лучше просто сопоставить типы SQL. Это обеспечит наилучшую производительность и надежность вашего кода в будущем.

Редактировать:

То же самое поведение можно продемонстрировать с параметрами .NET несовпадающих типов. Ниже приведен пример PowerShell.

 $connectionString = "Data Source=.;Initial Catalog=tempdb;Integrated Security=SSPI"
$connection = New-Object System.Data.SqlClient.SqlConnection($connectionString)
$connection.Open()
$command = New-Object System.Data.SqlClient.SqlCommand("CREATE TABLE dbo.Answer (Id int NOT NULL, Date datetime2 NOT NULL);", $connection)
[void]$command.ExecuteNonQuery()

$command.CommandText = "INSERT INTO dbo.Answer (Id, Date) VALUES (@Id, @Date);"
[void]$command.Parameters.AddWithValue("@Id", 1)
[void]$command.Parameters.AddWithValue("@Date", [DateTime]::Parse("2019-04-19T00:00:00.003"))
[void]$command.ExecuteNonQuery()
$command.CommandText = "SELECT TOP 1 Id FROM Answer where Id = @Id AND Date = @Date;"
$exists = $command.ExecuteScalar()
# not exists
if($exists -ne $null) { Write-Host "exists" } else { Write-Host "not exists" }

$command.CommandText = "INSERT INTO dbo.Answer (Id, Date) VALUES (@Id, @Date);"
$command.Parameters["@Id"].Value = 2
$command.Parameters["@Date"].Value = [DateTime]::Parse("2019-04-19T00:00:00.007")
[void]$command.ExecuteNonQuery()
$command.CommandText = "SELECT TOP 1 Id FROM Answer where Id = @Id AND Date = @Date;"
$exists = $command.ExecuteScalar()
# not exists
if($exists -ne $null) { Write-Host "exists" } else { Write-Host "not exists" }

$command.CommandText = "INSERT INTO dbo.Answer (Id, Date) VALUES (@Id, @Date);"
$command.Parameters["@Id"].Value = 3
$command.Parameters["@Date"].Value = [DateTime]::Parse("2019-04-19T00:00:00.010")
[void]$command.ExecuteNonQuery()
$command.CommandText = "SELECT TOP 1 Id FROM Answer where Id = @Id AND Date = @Date;"
$exists = $command.ExecuteScalar()
# exists
if($exists -ne $null) { Write-Host "exists" } else { Write-Host "not exists" }    

$connection.Close()