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

Open
#481 4 comments 0 reactions 0 assignees View on GitHub

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
25/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Stale
Tech stack
fsharp, sql
Domain
database

Research direction

Start with the SQL Server path using localCtx.Dbo.WriteStorages.Create(x.ColumnValues), then review how null values and timestamp columns are handled during inserts. Done means the sync can insert these records without replacing nulls with DBNull.Value or filtering timestamp columns manually.

Written by the indexing model from the issue text.

Description

sql server
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
Dominant language
F#
Stars
627
Forks
147
Avg merge
2h 2m
Merged PRs (30d)
1

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from fsprojects/SQLProvider

All issues in fsprojects/SQLProvider

Similar issues

More Databases issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.