[SQL SERVER] Dealing with nulls values and timestamp columns during insert

从使用 localCtx.Dbo.WriteStorages.Create(x.ColumnValues) 的 SQL Server 路径开始，然后检查插入期间如何处理 null 值和 timestamp 列。完成标准是同步可以插入这些记录，而无需将 null 替换为 DBNull.Value，也无需手动筛选 timestamp 列。

Description

As an exercise I've ported a small console application that sync tables in SQL SERVER DB from C# EF to F# SQLProvider.

In C# I was able to do the following:

     foreach (Type t in types)
                {
                    List<object> remoteEntities = await remoteContext.Set(t).ToListAsync();
                    var localEntities = await localContext.Set(t).ToListAsync();

                    localContext.Set(t).RemoveRange(localEntities);
                    localContext.Set(t).AddRange(remoteEntities);
                }
               await localContext.SaveChangesAsync();

In F# It became something like this:

                            ...deleting old entities...
remoteCtx.Dbo.WriteStorages |> Seq.map (fun x-> localCtx.Dbo.WriteStorages.Create(x.ColumnValues)) |>  Seq.toList

Actual behavior

This approach was failing due to 2 errors:

• null had to be replaced with System.DBNull.Value and it was producing errors like:
  An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll: 'The parameterized query '(@param0 uniqueidentifier,@param1 datetime,@param2 nvarchar(14),' expects the parameter '@param3', which was not supplied.'

• should filter Timestamp column from ColumnValues because of errors like:
  An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll: 'Cannot insert an explicit value into a timestamp column. Use INSERT with a column list to exclude the timestamp column, or insert a DEFAULT into the timestamp column.'

For EF It's working out-of-box, so maybe SQLProvider could also take care of these cases?

Known workarounds

Do the replacements mentioned above.

Related information

• SQL SERVER
• Windows
• .NET Runtime