Update Database Row using SqlDataProvider for SQL Server in F#

Question

I'm new to F# and I'm experimenting with the SqlDataProvider.

The thing I'm trying to accomplish is updating Db row which I'm prefetching it with the Individuals property. Then I'm changing on of the fields and submitting the updates, but it seems that SqlDataProvider is not keeping track of the changes.

Here is the code:

let context = SqlFunctions.Sql.GetDataContext()         
    let engeneering = context.HumanResources.Department.Individuals.``1``   
    engeneering.Name <- "Eng."
    context.SubmitUpdates()

SqlFunction.Sql implementation is as follows:

module SqlFunctions
open FSharp.Data.Sql
type Sql = SqlDataProvider<Common.DatabaseProviderTypes.MSSQLSERVER, 
                            "Server=************;Database=AdventureWorks2014;Trusted_Connection=True;">

pim pim · Accepted Answer · 2020-02-28T12:58:35

Welcome to the community!

While SqlDataProvider is an excellent tool, if you're open to it, I'd recommend using some simpler constructs. I think often times, when new people join, they're so drawn to all the fancy toys that it can be easy to lose sight of the fact that it's still just plain old .NET.

With that said, I say we start small and finish big. We'll accomplish this by looking directly at your problem scope, and in the true nature of F# write ourselves so nice little functions to make the end result clean & simple.

Our end goals is supporting syntax like this:

// Define a reusable "update department" function
let updateDepartment departmentId name connStr = 
    use conn = newConnection connStr
    exec 
        "UPDATE HumanResources.Department SET Name = @name WHERE DepartmentId = @departmentId"  
        [("departmentId", departmentId);("name", name)] 
        conn      

let connStr = "..."
updateDepartment 1 "eng" connStr |> ignore

To accomplish this, you actually need very little code, which is one of the major benefits of F#. We know we need:

A way to create connections (i.e. connection factory)
A way to execute statements (i.e. INSERT, UPDATE)
A way to query records (i.e. SELECT)

module SQL =
    open System.Data
    open System.Data.SqlClient

    let newConnection connectionString =
        let conn = new SqlConnection(connectionString)
        conn.Open()
        conn

    let createParameter (cmd: SqlCommand) (name, value) =
        let p = cmd.CreateParameter()
        p.ParameterName <- name
        p.Value <- value
        p

    let addParameter (cmd: SqlCommand) (p : SqlParameter) = 
        cmd.Parameters.Add(p) |> ignore

    let newCommand sql parameters conn =        
        let cmd = new SqlCommand(connection = conn, cmdText = sql)
        cmd.CommandType <- CommandType.Text        

        let createParam = createParameter cmd
        let addParam = addParameter cmd
        parameters 
        |> Seq.iter (fun p -> p |> createParam |> addParam)
        cmd 

    let exec sql param conn =
        let cmd = newCommand sql param conn
        cmd.ExecuteNonQuery()

    let query sql param map conn =
        let cmd = newCommand sql param conn
        use rd = cmd.ExecuteReader()
        [ while rd.Read() do
            yield map rd ]

So in about 30 lines of code we've created ourselves a small, well-understood and (in my opinion) self documenting solution.

Update Database Row using SqlDataProvider for SQL Server in F#

1 Answers