ISqlDataContext.SubmitPendingChanges suppressing exceptions

[raquila]

Describe the bug When updating from an old version (1.1.5.0), we are finding that SQL exceptions are being ignored when calling SubmitPendingChanges. I have tracked it down to 1.1.38, the versions prior raise SQL exceptions.

To Reproduce I created a simple table and code to test this out in MSSQL CREATE TABLE [dbo].[Test]( [Id] [int] NULL )

` let [] connection_string = "data source=localhost;Integrated Security=SSPI;Initial Catalog=TestDB" type sql = SqlDataProvider<Common.DatabaseProviderTypes.MSSQLSERVER,ConnectionString=connection_string,UseOptionTypes=true>

let test_save (create_item:unit->FSharp.Data.Sql.Common.SqlEntity) =       
    let item = create_item()
    item.SetColumn("id","1.0")       
    let ctx = item.DataContext   
    ctx.SubmitPendingChanges()

test_save (fun ()-> sql.GetDataContext(connection_string).Dbo.Test.Create():>FSharp.Data.Sql.Common.SqlEntity)`

Expected behavior Above code should raise a SqlException: System.Data.SqlClient.SqlException: 'Conversion failed when converting the nvarchar value '1.00' to data type int.'

In versions 1.1.38 and up the exception is never raised and the row is never saved without any indication.

Additional context .Net 4.8 Fsharp.Core 4.7.2

[Thorium]

I bet this is a design decision that you don't have to catch everything, instead on failure you can get the pending updates from .GetUpdates() if you want, or you can clear them.

Also, I'd now prefer to do strongly typed item.id <- 1

[raquila]

I don't see a GetUpdates method off of ISqlDataContext. I'm using string instead of strongly typed to show how the exception is not raised.

[Thorium]

I just tried and it fails for me, the latest version:

  let ctx = sql.GetDataContext(connectionString = runtimeConnStr)
  let newRegion = ctx.Dbo.Regions.Create() 
  newRegion.RegionName <- "Antarctica"
  newRegion.SetColumn("Region_Id", "5.0")
  ctx.SubmitUpdates()

Failed:

System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the nvarchar value '5.0' to data type int.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at System.Data.SqlClient.SqlDataReader.Read()
   at System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at <StartupCode$FSharp-Data-SqlProvider>.$Providers.MsSqlServer.FSharp-Data-Sql-Common-ISqlProvider-ProcessUpdates@1086.Invoke(SqlEntity e) in C:\git\SQLProvider\src\SQLProvider.Runtime\Providers.MsSqlServer.fs:line 1093
   at Microsoft.FSharp.Collections.SeqModule.Iterate[T](FSharpFunc`2 action, IEnumerable`1 source) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\seq.fs:line 497
   at FSharp.Data.Sql.Providers.MSSqlServerProvider.FSharp-Data-Sql-Common-ISqlProvider-ProcessUpdates(IDbConnection con, ConcurrentDictionary`2 entities, TransactionOptions transactionOptions, FSharpOption`1 timeout) in C:\git\SQLProvider\src\SQLProvider.Runtime\Providers.MsSqlServer.fs:line 1085
   at <StartupCode$FSharp-Data-SqlProvider>.$SqlRuntime.DataContext.FSharp-Data-Sql-Common-ISqlDataContext-SubmitPendingChanges@110.Invoke(Unit unitVar0) in C:\git\SQLProvider\src\SQLProvider.Runtime\SqlRuntime.DataContext.fs:line 111
   at FSharp.Data.Sql.Runtime.SqlDataContext.FSharp-Data-Sql-Common-ISqlDataContext-SubmitPendingChanges() in C:\git\SQLProvider\src\SQLProvider.Runtime\SqlRuntime.DataContext.fs:line 110
   at <StartupCode$FSI_0021>.$FSI_0021.main@()

More interestingly, this error comes from System.Data.SqlClient directly, which is the underlying library used for the connection.

Edit: Included the full stacktrace.

I'm running on Microsoft Windows [Version 10.0.19042.867] Microsoft (R) F# Interactive version 11.3.2.0 for F# 5.0 System.Data.SqlClient, Version=4.6.1.1 .NET Framework 4.8.4341.0 SQL Server 15.0.2000.5 SQLProvider 1.2.1 (but I've not encountered this on other versions either)

[raquila]

Yes that is the case when calling GetDataContext and SubmitUpdates. But if you pull the datacontext from a SqlEntity and call SubmitPendingChanges it will not throw the error.

[Thorium]

No difference to me:

  let ctx = HR.GetDataContext(connectionString = runtimeConnStr)
  let newRegion = ctx.Dbo.Regions.Create() 
  newRegion.RegionName <- "Antarctica"
  newRegion.SetColumn("Region_Id", "5.0")
  let dt = newRegion.DataContext
  dt.SubmitPendingChanges()

Result:

System.Data.SqlClient.SqlException (0x80131904): Conversion failed when converting the nvarchar value '5.0' to data type int.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
   at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
   at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
   at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
   at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
   at System.Data.SqlClient.SqlDataReader.Read()
   at System.Data.SqlClient.SqlCommand.CompleteExecuteScalar(SqlDataReader ds, Boolean returnSqlValue)
   at System.Data.SqlClient.SqlCommand.ExecuteScalar()
   at <StartupCode$FSharp-Data-SqlProvider>.$Providers.MsSqlServer.FSharp-Data-Sql-Common-ISqlProvider-ProcessUpdates@1086.Invoke(SqlEntity e) in C:\git\SQLProvider\src\SQLProvider.Runtime\Providers.MsSqlServer.fs:line 1093
   at Microsoft.FSharp.Collections.SeqModule.Iterate[T](FSharpFunc`2 action, IEnumerable`1 source) in F:\workspace\_work\1\s\src\fsharp\FSharp.Core\seq.fs:line 497
   at FSharp.Data.Sql.Providers.MSSqlServerProvider.FSharp-Data-Sql-Common-ISqlProvider-ProcessUpdates(IDbConnection con, ConcurrentDictionary`2 entities, TransactionOptions transactionOptions, FSharpOption`1 timeout) in C:\git\SQLProvider\src\SQLProvider.Runtime\Providers.MsSqlServer.fs:line 1085
   at <StartupCode$FSharp-Data-SqlProvider>.$SqlRuntime.DataContext.FSharp-Data-Sql-Common-ISqlDataContext-SubmitPendingChanges@110.Invoke(Unit unitVar0) in C:\git\SQLProvider\src\SQLProvider.Runtime\SqlRuntime.DataContext.fs:line 111
   at FSharp.Data.Sql.Runtime.SqlDataContext.FSharp-Data-Sql-Common-ISqlDataContext-SubmitPendingChanges() in C:\git\SQLProvider\src\SQLProvider.Runtime\SqlRuntime.DataContext.fs:line 110
   at <StartupCode$FSI_0035>.$FSI_0035.main@()

[Thorium]

If nothing happens, it's a sign that your SQL-clause is never executed, i.e. the code has not been run at all. Could the problem be somewhere else?

[raquila]

Not sure, all I do is flip back to the old version and then I see the error. Are you able to run the sample I put together?

[Thorium]

Yes. Weird. It's not failing silently: It's not doing the SQL at all. If you capture the value like this, then it works:

test_save (fun ()-> let c = sql.GetDataContext(connection_string)
                    c.Dbo.Test.Create():>FSharp.Data.Sql.Common.SqlEntity)

This has to be related to some kind of use statement disposing resources too early or something like that.

[Thorium]

Between 1.1.37 and 1.1.38 there are only 2 change sets https://github.com/fsprojects/SQLProvider/commit/56aeb845d16b514cfd8bde472b1fa1db6283d0fb and https://github.com/fsprojects/SQLProvider/commit/c4138ddc720ef4297c81711ce750770c76f85009 which are not suspicious looking, they are doing LINQ parsing on some join-operation scenarios.