FSharp.Data.SqlClient


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

Full name: Data modification.CurrencyCode
type SqlEnumProvider

Full name: FSharp.Data.SqlEnumProvider



<summary>Enumeration based on SQL query.</summary>
<param name='Query'>SQL used to get the enumeration labels and values. A result set must have at least two columns. The first one is a label.</param>
<param name='ConnectionString'>String used to open a data connection.</param>
<param name='Provider'>Invariant name of a ADO.NET provider. Default is "System.Data.SqlClient".</param>
<param name='ConfigFile'>The name of the configuration file that’s used for connection strings at DESIGN-TIME. The default value is app.config or web.config.</param>
<param name='Kind'></param>
val connectionString : string

Full name: Data modification.connectionString
val cmd : IDisposable
type SqlCommandProvider

Full name: FSharp.Data.SqlCommandProvider



<summary>Typed representation of a T-SQL statement to execute against a SQL Server database.</summary>
<param name='CommandText'>Transact-SQL statement to execute at the data source.</param>
<param name='ConnectionStringOrName'>String used to open a SQL Server database or the name of the connection string in the configuration file in the form of “name=&lt;connection string name&gt;”.</param>
<param name='ResultType'>A value that defines structure of result: Records, Tuples, DataTable, or SqlDataReader.</param>
<param name='SingleRow'>If set the query is expected to return a single row of the result set. See MSDN documentation for details on CommandBehavior.SingleRow.</param>
<param name='ConfigFile'>The name of the configuration file that’s used for connection strings at DESIGN-TIME. The default value is app.config or web.config.</param>
<param name='AllParametersOptional'>If set all parameters become optional. NULL input values must be handled inside T-SQL.</param>
<param name='ResolutionFolder'>A folder to be used to resolve relative file paths to *.sql script files at compile time. The default value is the folder that contains the project or script.</param>
<param name='DataDirectory'>The name of the data directory that replaces |DataDirectory| in connection strings. The default value is the project or script directory.</param>
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
type SqlProgrammabilityProvider

Full name: FSharp.Data.SqlProgrammabilityProvider



<summary>Typed access to SQL Server programmable objects: stored procedures, functions and user defined table types.</summary>
<param name='ConnectionStringOrName'>String used to open a SQL Server database or the name of the connection string in the configuration file in the form of “name=&lt;connection string name&gt;”.</param>
<param name='ResultType'>A value that defines structure of result: Records, Tuples, DataTable, or SqlDataReader.</param>
<param name='ConfigFile'>The name of the configuration file that’s used for connection strings at DESIGN-TIME. The default value is app.config or web.config.</param>
<param name='DataDirectory'>The name of the data directory that replaces |DataDirectory| in connection strings. The default value is the project or script directory.</param>
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
type DataRowState =
  | Detached = 1
  | Unchanged = 2
  | Added = 4
  | Deleted = 8
  | Modified = 16

Full name: System.Data.DataRowState
field Data.DataRowState.Deleted = 8
val totalRecords : obj
val recordsAffected : obj
namespace System.Data.SqlClient
type SqlBulkCopyOptions =
  | Default = 0
  | KeepIdentity = 1
  | CheckConstraints = 2
  | TableLock = 4
  | KeepNulls = 8
  | FireTriggers = 16
  | UseInternalTransaction = 32

Full name: System.Data.SqlClient.SqlBulkCopyOptions
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
Fork me on GitHub