Data modification

FSharp.Data.SqlClient supports multiple approaches to send data modifications to Sql Server.

Hand-written DML statements

Write DML statements using SqlCommandProvider:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19:

type CurrencyCode = 
    SqlEnumProvider<"SELECT Name, CurrencyCode FROM Sales.Currency", connectionString>

do
    use cmd = new SqlCommandProvider<"
        INSERT INTO Sales.CurrencyRate 
        VALUES (@currencyRateDate, @fromCurrencyCode, @toCurrencyCode, 
                @averageRate, @endOfDayRate, DEFAULT) 
    ", connectionString>(connectionString)

    let recordsInserted = 
        cmd.Execute(
            currencyRateDate = DateTime.Today, 
            fromCurrencyCode = CurrencyCode.``US Dollar``, 
            toCurrencyCode = CurrencyCode.``United Kingdom Pound``, 
            averageRate = 0.63219M, 
            endOfDayRate = 0.63219M) 

    assert (recordsInserted = 1)

This works for any kind of data modification statement: INSERT, UPDATE, DELETE, MERGE etc.

Stored Procedures

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
38: 
39: 
40: 
41: 
42: 
43: 
44: 
45: 
46:

type AdventureWorks = SqlProgrammabilityProvider<connectionString>

let jamesKramerId = 42

let businessEntityID, jobTitle, hireDate = 
    use cmd = new SqlCommandProvider<"
        SELECT 
	        BusinessEntityID
	        ,JobTitle
	        ,HireDate
        FROM 
            HumanResources.Employee 
        WHERE 
            BusinessEntityID = @id
        ", connectionString, ResultType.Tuples, SingleRow = true>(connectionString)

    jamesKramerId |> cmd.Execute |> Option.get

assert("Production Technician - WC60" = jobTitle)
    
let newJobTitle = "Uber " + jobTitle

let recordsAffrected = 
    use updatedJobTitle = new AdventureWorks.HumanResources.uspUpdateEmployeeHireInfo(connectionString)
    updatedJobTitle.Execute(
        businessEntityID, 
        newJobTitle, 
        hireDate, 
        RateChangeDate = DateTime.Now, 
        Rate = 12M, 
        PayFrequency = 1uy, 
        CurrentFlag = true 
    )

assert(recordsAffrected = 1)

let updatedJobTitle = 
    // Static Create factory method provides better IntelliSense than ctor.
    // See https://github.com/Microsoft/visualfsharp/issues/449
    use cmd = new AdventureWorks.dbo.ufnGetContactInformation(connectionString)

    //Use ExecuteSingle if you're sure it return 0 or 1 rows.
    let result = cmd.ExecuteSingle(PersonID = jamesKramerId) 
    result.Value.JobTitle.Value

assert(newJobTitle = updatedJobTitle)

Statically-typed DataTable

Both hand-written T-SQL and stored procedures have a significant downside: it requires tedious coding. It gets worse when different kinds of modifications -- inserts, updates, deletes, merges -- need to be issued for the same entity. In most cases you are forced to have one command/stored procedure per modification type. SqlProgrammabilityProvider offers an elegant solution based on the ADO.NET DataTable class with static types on top. To a certain extent, this is similar to the ancient, almost forgotten Generating Strongly Typed DataSets technique except that the epic F# Type Providers feature streamlines the whole development experience.

Using Sales.CurrencyRate table as an example, let's see how a generated table type is different from its base DataTable type.

Generated table type names follow a consistent pattern: TypeAliasForRoot.SchemaName.Tables.TableName

1: 
2:

let currencyRates = new AdventureWorks.Sales.Tables.CurrencyRate()
assert (currencyRates.TableName = "[Sales].[CurrencyRate]")

The type provider generates an expected value for the TableName property.

The Rows property, of type IList<#DataRow>, provides access to the rows within the table. Familiar list operations are available for typed DataTable: Add, Remove, Insert etc. Typed column accessors are added to the existing set of DataRow type members. The IntelliSense experience is left a little clunky to retain legacy DataRow type members.

1: 
2:

let firstRow = currencyRates.Rows.[0]
firstRow.AverageRate

It is possible to get a reference to the DataColumn object

1:	let averageRateColumn = currencyRates.Columns.AverageRate

The AddRow method adds a new row to a table.

There is 1-1 correspondence between column names/types and the method parameters
IDENTITY column is excluded from parameters list for obvious reasons
Nullable columns are mappend to parameters of type option<_>
Columns with DEFAULT constraint are also represented as parameters of type option<_>. This is more convenient that specifying DEFAULT as a value in INSERT statement
Both kinds of parameters -- nullable columns or columns with defaults -- can be omitted from invocation
Minor but nice feature is the ability to retrieve MS_Description, which works only for Sql Server because Sql Azure doesn't support extended properties.

1: 
2: 
3: 
4: 
5: 
6: 
7:

do
    currencyRates.AddRow(
        CurrencyRateDate = DateTime.Today, 
        FromCurrencyCode = CurrencyCode.``US Dollar``, 
        ToCurrencyCode = CurrencyCode.``United Kingdom Pound``, 
        AverageRate = 0.63219M, 
        EndOfDayRate = 0.63219M)

Side-effecting AddRow makes it easier to add rows in type-safe manner. A pair of invocations to NewRow and Rows.Add can be used as an alternative. This approach also makes sense if for some reason you need to keep a reference to a newly added row for further manipulations.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12:

do 
    let newRow = 
        currencyRates.NewRow(
            CurrencyRateDate = DateTime.Today, 
            FromCurrencyCode = CurrencyCode.``US Dollar``, 
            ToCurrencyCode = CurrencyCode.``United Kingdom Pound``, 
            AverageRate = 0.63219M, 
            EndOfDayRate = 0.63219M,
            //Column with DEFAULT constraint can be passed in explicitly
            ModifiedDate = Some DateTime.Today
        )
    currencyRates.Rows.Add newRow

With this knowledge in mind, the example at top the page can be re-written as follows:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15:

do
    let currencyRates = new AdventureWorks.Sales.Tables.CurrencyRate()
    let newRow = 
        currencyRates.NewRow(
            CurrencyRateDate = DateTime.Today, 
            FromCurrencyCode = "USD", 
            ToCurrencyCode = "GBP", 
            AverageRate = 0.63219M, 
            EndOfDayRate = 0.63219M
        )
    currencyRates.Rows.Add newRow
    //Call Update to push changes to a database
    let recordsAffected = currencyRates.Update()
    assert(recordsAffected = 1)
    printfn "ID: %i, ModifiedDate: %O" newRow.CurrencyRateID newRow.ModifiedDate

Call to Update is required to push changes into a database
CurrencyRateID IDENTITY column and all fields with DEFAULT constraints that didn't have value specified are refreshed after an update from the database. This is a very cool feature. It works only for BatchSize = 1, which is the default. Of course it's applicable only to new data rows (that issue an INSERT statement). Follow this link to find out more about batch updates.

The snippet below demonstrates update and delete logic. Note how combining SqlCommandProvider to load existing data with typed data tables produces simple and safe code.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35:

do
    use cmd = new SqlCommandProvider<"
        SELECT * 
        FROM Sales.CurrencyRate 
        WHERE FromCurrencyCode = @from
            AND ToCurrencyCode = @to
            AND CurrencyRateDate > @date
        ", connectionString, ResultType.DataReader>(connectionString)
    //ResultType.DataReader !!!
    let currencyRates = new AdventureWorks.Sales.Tables.CurrencyRate()
    //load data into data table
    cmd.Execute("USD", "GBP", DateTime(2014, 1, 1)) |> currencyRates.Load

    let latestModification =
        //manipulate Rows as a sequence
        currencyRates.Rows
        |> Seq.sortBy (fun x -> x.ModifiedDate)
        |> Seq.last

    latestModification.Delete()
    //or use list operation
    //currencyRates.Rows.Remove latestModification

    //adjust rates slightly
    for row in currencyRates.Rows do
        if row.RowState <> System.Data.DataRowState.Deleted
        then 
            row.EndOfDayRate <- row.EndOfDayRate + 0.01M
            row.ModifiedDate <- DateTime.Today

    let totalRecords = currencyRates.Rows.Count
    // custom batch size - send them all at once
    let recordsAffected = currencyRates.Update(batchSize = totalRecords) 
    
    assert (recordsAffected = totalRecords)

WARNING Unfortunately, the Update method on the typed data table doesn't have an asynchronous version. Command types provided by SqlCommandProvider have distinct advantage when you need asynchronous invocation.

Bulk Load

Bulk loading is another useful scenario for typed data tables. It looks exactly like adding new rows except at the end you make a call to BulkCopy instead of Update.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15:

do
    let currencyRates = new AdventureWorks.Sales.Tables.CurrencyRate()
    let newRow = 
        currencyRates.NewRow(
            CurrencyRateDate = DateTime.Today, 
            FromCurrencyCode = "USD", 
            ToCurrencyCode = "GBP", 
            AverageRate = 0.63219M, 
            EndOfDayRate = 0.63219M,
            ModifiedDate = DateTime.Today
        )

    currencyRates.Rows.Add newRow
    //Insert many more rows here
    currencyRates.BulkCopy(copyOptions = System.Data.SqlClient.SqlBulkCopyOptions.TableLock)

Custom update/bulk copy logic

Both Update and BulkCopy operations can be configured via parameters, i.e. connection, transaction, batchSize, etc. That said, default update logic provided by typed DataTable can be insufficient for some advanced scenarios. You don't need to give up on convenience of static typing, however. You can also customize update behavior by creating your own instance of SqlDataAdapter (or SqlBulkCopy) and configuring it to your needs.

Pseudocode for custom data adapter:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21:

open System.Data.SqlClient

do
    let currencyRates = new AdventureWorks.Sales.Tables.CurrencyRate()
    //load, update, delete, insert rows 
    // ...
    use adapter = new SqlDataAdapter()
    //configure adapter: setup select, insert, update, delete commands, transaction etc.
    // ...
    adapter.Update( currencyRates) |> ignore

//Similarly for custom bulk copy:
    
do
    let currencyRates = new AdventureWorks.Sales.Tables.CurrencyRate()
    //load, update, delete, insert rows 
    // ...
    //configure bulkCopy: copyOptions, connectoin, transaction, timeout, batch size etc.
    use bulkCopy = new SqlBulkCopy(AdventureWorks.Sales.Tables.CurrencyRate.ConnectionStringOrName)
    // ...
    bulkCopy.WriteToServer( currencyRates) |> ignore

Transaction and connection management

Please read Transactions chapter of the documentation. Pay particular attention to DataTable Updates/Bulk Load section.

Query-derived tables

You can get your hands on a typed data table by specifying ResultType.DataTable as the output type for SqlCommandProvider generated command types. This approach gives flexibility at a cost of leaving more room for error. An output projection should be suitable for sending changes back to a database. It rules out transformations, extensive joins etc. Only raw columns for a single table make good candidates for persistable changes. The typed DataTable class you get back by executing a command with ResultType.DataTable is largely similar to the one describe above. One noticeable difference is the absence of the parametrized AddRow/NewRow method. This is intentional. Updating, deleting or merging rows are the most likely scenarios where this can be useful. For update/delete/merge logic to work properly, primary key (or unique index) columns must be included in column selection. To insert new records, use static data table types generated by SqlProgrammbilityProvider. That said, it's still possible to add rows with some static typing support.

One of the examples above can be re-written as

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
38: 
39: 
40: 
41: 
42: 
43: 
44: 
45: 
46:

do
    //CurrencyRateID is included
    use cmd = new SqlCommandProvider<"
        SELECT 
            CurrencyRateID,
            CurrencyRateDate,
            FromCurrencyCode,
            ToCurrencyCode,
            AverageRate,
            EndOfDayRate
        FROM Sales.CurrencyRate 
        WHERE FromCurrencyCode = @from
            AND ToCurrencyCode = @to
            AND CurrencyRateDate > @date
        ", connectionString, ResultType.DataTable>(connectionString)
    //ResultType.DataTable !!!
    let currencyRates = cmd.Execute("USD", "GBP", DateTime(2014, 1, 1)) 

    let latestModification =
        currencyRates.Rows
        |> Seq.sortBy (fun x -> x.CurrencyRateDate)
        |> Seq.last
    
    //Delete
    latestModification.Delete()

    //Update
    for row in currencyRates.Rows do
        if row.RowState <> System.Data.DataRowState.Deleted
        then 
            row.EndOfDayRate <- row.EndOfDayRate + 0.01M

    //Insert
    let newRecord = currencyRates.NewRow()
    newRecord.CurrencyRateDate <- DateTime.Today
    newRecord.FromCurrencyCode <- "USD"
    newRecord.ToCurrencyCode <- "GBP"
    newRecord.AverageRate <- 0.63219M
    newRecord.EndOfDayRate <- 0.63219M

    currencyRates.Rows.Add newRecord

    let totalRecords = currencyRates.Rows.Count

    let recordsAffected = currencyRates.Update(batchSize = totalRecords) 
    assert (recordsAffected = totalRecords)

type CurrencyCode = obj

Full name: Data modification.CurrencyCode

val connectionString : string

Full name: Data modification.connectionString

val cmd : IDisposable

val recordsInserted : int

Multiple items
type DateTime =
  struct
    new : ticks:int64 -> DateTime + 10 overloads
    member Add : value:TimeSpan -> DateTime
    member AddDays : value:float -> DateTime
    member AddHours : value:float -> DateTime
    member AddMilliseconds : value:float -> DateTime
    member AddMinutes : value:float -> DateTime
    member AddMonths : months:int -> DateTime
    member AddSeconds : value:float -> DateTime
    member AddTicks : value:int64 -> DateTime
    member AddYears : value:int -> DateTime
    ...
  end

Full name: System.DateTime

--------------------
DateTime()
   (+0 other overloads)
DateTime(ticks: int64) : unit
   (+0 other overloads)
DateTime(ticks: int64, kind: DateTimeKind) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, calendar: Globalization.Calendar) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, kind: DateTimeKind) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, calendar: Globalization.Calendar) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int, kind: DateTimeKind) : unit
   (+0 other overloads)

property DateTime.Today: DateTime

type AdventureWorks = obj

Full name: Data modification.AdventureWorks

val jamesKramerId : int

Full name: Data modification.jamesKramerId

val businessEntityID : obj

Full name: Data modification.businessEntityID

val jobTitle : string

Full name: Data modification.jobTitle

val hireDate : obj

Full name: Data modification.hireDate

type ResultType =
  | Records = 0
  | Tuples = 1
  | DataTable = 2
  | DataReader = 3

Full name: FSharp.Data.ResultType

ResultType.Tuples: ResultType = 1

module Option

from Microsoft.FSharp.Core

val get : option:'T option -> 'T

Full name: Microsoft.FSharp.Core.Option.get

val newJobTitle : string

Full name: Data modification.newJobTitle

val recordsAffrected : int

Full name: Data modification.recordsAffrected

val updatedJobTitle : IDisposable

property DateTime.Now: DateTime

val updatedJobTitle : string

Full name: Data modification.updatedJobTitle

val result : obj

val currencyRates : obj

Full name: Data modification.currencyRates

val firstRow : obj

Full name: Data modification.firstRow

val averageRateColumn : obj

Full name: Data modification.averageRateColumn

val newRow : obj

union case Option.Some: Value: 'T -> Option<'T>

val currencyRates : obj

val recordsAffected : int

val printfn : format:Printf.TextWriterFormat<'T> -> 'T

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.printfn

ResultType.DataReader: ResultType = 3

val latestModification : obj

module Seq

from Microsoft.FSharp.Collections

val sortBy : projection:('T -> 'Key) -> source:seq<'T> -> seq<'T> (requires comparison)

Full name: Microsoft.FSharp.Collections.Seq.sortBy

val x : obj

val last : source:seq<'T> -> 'T

Full name: Microsoft.FSharp.Collections.Seq.last

val row : obj

namespace System

namespace System.Data

field Data.DataRowState.Deleted = 8

val totalRecords : obj

val recordsAffected : obj

namespace System.Data.SqlClient

field Data.SqlClient.SqlBulkCopyOptions.TableLock = 4

val adapter : SqlDataAdapter

Multiple items
type SqlDataAdapter =
  inherit DbDataAdapter
  new : unit -> SqlDataAdapter + 3 overloads
  member DeleteCommand : SqlCommand with get, set
  member InsertCommand : SqlCommand with get, set
  member SelectCommand : SqlCommand with get, set
  member UpdateBatchSize : int with get, set
  member UpdateCommand : SqlCommand with get, set
  event RowUpdated : SqlRowUpdatedEventHandler
  event RowUpdating : SqlRowUpdatingEventHandler

Full name: System.Data.SqlClient.SqlDataAdapter

--------------------
SqlDataAdapter() : unit
SqlDataAdapter(selectCommand: SqlCommand) : unit
SqlDataAdapter(selectCommandText: string, selectConnectionString: string) : unit
SqlDataAdapter(selectCommandText: string, selectConnection: SqlConnection) : unit

Data.Common.DbDataAdapter.Update(dataTable: Data.DataTable) : int
Data.Common.DbDataAdapter.Update(dataRows: Data.DataRow []) : int
Data.Common.DbDataAdapter.Update(dataSet: Data.DataSet) : int
Data.Common.DbDataAdapter.Update(dataSet: Data.DataSet, srcTable: string) : int

val ignore : value:'T -> unit

Full name: Microsoft.FSharp.Core.Operators.ignore

val bulkCopy : SqlBulkCopy

Multiple items
type SqlBulkCopy =
  new : connection:SqlConnection -> SqlBulkCopy + 3 overloads
  member BatchSize : int with get, set
  member BulkCopyTimeout : int with get, set
  member Close : unit -> unit
  member ColumnMappings : SqlBulkCopyColumnMappingCollection
  member DestinationTableName : string with get, set
  member NotifyAfter : int with get, set
  member WriteToServer : reader:IDataReader -> unit + 3 overloads
  event SqlRowsCopied : SqlRowsCopiedEventHandler

Full name: System.Data.SqlClient.SqlBulkCopy

--------------------
SqlBulkCopy(connection: SqlConnection) : unit
SqlBulkCopy(connectionString: string) : unit
SqlBulkCopy(connectionString: string, copyOptions: SqlBulkCopyOptions) : unit
SqlBulkCopy(connection: SqlConnection, copyOptions: SqlBulkCopyOptions, externalTransaction: SqlTransaction) : unit

SqlBulkCopy.WriteToServer(rows: Data.DataRow []) : unit
SqlBulkCopy.WriteToServer(table: Data.DataTable) : unit
SqlBulkCopy.WriteToServer(reader: Data.IDataReader) : unit
SqlBulkCopy.WriteToServer(table: Data.DataTable, rowState: Data.DataRowState) : unit

ResultType.DataTable: ResultType = 2

val newRecord : obj