F# - FSharp.Data.SqlClient - update row after getting identity value

Question

Let’s say that I have the following:

SQL

Create database called Clm, then run this script:

CREATE TABLE dbo.ModelData(
    modelId bigint IDENTITY(1,1) NOT NULL,
    numberOfAminoAcids int NOT NULL,
    maxPeptideLength int NOT NULL,
    seed int NOT NULL,
    fileStructureVersion nvarchar(50) NOT NULL,
    modelData nvarchar(max) NOT NULL,
    createdOn datetime NOT NULL,
 CONSTRAINT PK_ModelData PRIMARY KEY CLUSTERED 
(
    modelId ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY
) ON PRIMARY TEXTIMAGE_ON PRIMARY
GO
ALTER TABLE dbo.ModelData ADD  CONSTRAINT DF_ModelData_createdOn  DEFAULT (getdate()) FOR createdOn
GO

F#

[<Literal>]
let ClmDbName = "Clm"

[<Literal>]
let AppConfigFile = __SOURCE_DIRECTORY__ + "\.\App.config"

[<Literal>]
let ClmConnectionString = "Server=localhost;Database=" + ClmDbName + ";Integrated Security=SSPI"

[<Literal>]
let ClmSqlProviderName = "name=" + ClmDbName

type ClmDB = SqlProgrammabilityProvider<ClmSqlProviderName, ConfigFile = AppConfigFile>
type ModelDataTable = ClmDB.dbo.Tables.ModelData
type ModelDataTableRow = ModelDataTable.Row

type ModelDataTableData = 
    SqlCommandProvider<"select * from dbo.ModelData where modelId = @modelId", ClmConnectionString, ResultType.DataReader>

App.config

<?xml version="1.0" encoding="utf-8"?>
<configuration>
  <startup>
    <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.7.2" />
  </startup>
  <connectionStrings configSource="db.config" />
  <runtime>
    <gcAllowVeryLargeObjects enabled="true" />
  </runtime>
</configuration>

db.config

<connectionStrings>
    <add name="Clm" connectionString="Data Source=localhost;Initial Catalog=Clm;Integrated Security=SSPI" />
</connectionStrings>

I need to get SQL identity value from table ModelData. It is used somewhere in the code. So, I have the following function to add a new row with some default values and then get identity value back.

let getNewModelDataId (conn : SqlConnection) =
    let t = new ModelDataTable()
    let r = 
        t.NewRow(
                numberOfAminoAcids = 0,
                maxPeptideLength = 0,
                seed = 0,
                fileStructureVersion = "",
                modelData = "",
                createdOn = DateTime.Now
                )

    t.Rows.Add r
    t.Update(conn) |> ignore
    r.modelId

let openConnIfClosed (conn : SqlConnection) =
    match conn.State with
    | ConnectionState.Closed -> do conn.Open()
    | _ -> ignore ()

And the I use it to get new identity value of modelId from the database.

let modelId = getNewModelDataId conn

The after about 0.5 – 1.5 hours of execution time I need to update some data, e.g.

use d = new ModelDataTableData(conn)
let t1 = new ModelDataTable()
d.Execute(modelId = modelId) |> t1.Load
let r1 = t1.Rows |> Seq.find (fun e -> e.modelId = modelId)
r1.modelData <- "Some new data..."
t1.Update(conn) |> ignore

where the string "Some new data..." represents some fairly large string. This only happens once per modelId. The code above does work. But it looks soooo ugly, epsecially the part t1.Rows |> Seq.find ... ☹ I guess that I am missing something about FSharp.Data.SqlClient type providers. I’d appreciate any advice.

Answer 1

To begin with the most obvious blunder: FSharp.Data.SqlClient type provider via its SqlCommandProvider supports any DML data modification statements, including UPDATE. So, instead of all that jazz with pulling the model to the client side, modifying, and pushing back to the server, the same can be achieved by

...
use cmd = SqlCommandProvider<
              "UPDATE [dbo].[ModelData] SET [modelData]=@ModelData WHERE [modelId]=@ModelId",
               conn>(conn)
cmd.Execute(ModelData="Some new data...", ModelId=modelId) |> ignore
...

The less obvious problem relates to the use of identity field. It sounds like it does not have any special role beyond uniquely identifying every new model. So, instead of the tinkering with creating a fake record, then pulling its id from the server, then updating the record having this id with real values, why not just:

• introduce an additional column modelUid of type uniqueidentifier
• add a nonclustered index on it, if this matters
• begin creating of every new model by generating a fresh GUID with System.Guid.NewGuid()
• use this GUID value whatever you want, then when you ready to persist your model do it with SQL DML INSERT using the same GUID for modelUid field

Notice, that with such approach you also use just SqlCommandProvider type of FSharp.Data.SqlClient type provider, so the things stay simple.