FSharp.Data.SqlClient


Transactions

This chapter assumes a basic understanding of the "Transactions and Concurrency" topic as it applies to ADO.NET and SQL Server. (If you need to brush up your knowledge use favorite search engine - there is plenty of information on the subject.)

The following links may be helpful:

Explicit Transactions

Command types generated by both the SqlCommandProvider and the SqlProgrammabilityProvider have a constructor which accepts connection instance and optionally transaction information. This conforms to familiar ADO.NET conventions for command constructors.

 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: 
open System
open System.Data.SqlClient

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

type InsertCurrencyRate = SqlCommandProvider<"
        INSERT INTO Sales.CurrencyRate 
        VALUES (@currencyRateDate, @fromCurrencyCode, @toCurrencyCode, 
                @averageRate, @endOfDayRate, DEFAULT) 
    ", connectionString>
do
    //Don't forget `use` binding to properly scope transactoin.
    //It guarantees rollback in case of unhandled exception. 

    use conn = new SqlConnection(connectionString)
    conn.Open()
    use tran = conn.BeginTransaction()
    
    //Implicit assumption that
    assert (tran.Connection = conn)
    //Supply connection and transaction 
    use cmd = new InsertCurrencyRate(conn, tran)

    let recordsInserted = 
        cmd.Execute(
            currencyRateDate = DateTime.Today, 
            fromCurrencyCode = "USD", 
            toCurrencyCode = "GBP", 
            averageRate = 0.63219M, 
            endOfDayRate = 0.63219M) 

    assert (recordsInserted = 1)

    // Invoke Commit otherwise transaction will be disposed (roll-backed) when out of scope
    tran.Commit()

Note, that Connection property of the transaction instance has to match the connection supplied up at the first position.

Most often transactions are used in combination with data modification commands (INSERT, UPDATE, DELETE, MERGE). Commands based on SELECT statements or calls to a stored procedure (function) can join a transaction as well, but generally do not do so if they stand alone.

 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: 
47: 
48: 
49: 
50: 
51: 
52: 
53: 
54: 
type AdventureWorks = SqlProgrammabilityProvider<connectionString>

do
    use conn = new SqlConnection(connectionString)
    conn.Open()
    //bump up isolation level to serializable
    use tran = conn.BeginTransaction(Data.IsolationLevel.Serializable)
    let jamesKramerId = 42

    let businessEntityID, jobTitle, hireDate = 
        //Include SELECT in transaction
        //Note that inline definition requires both design time connection string
        // and runtime connection object
        use cmd = new SqlCommandProvider<"
            SELECT 
	            BusinessEntityID
	            ,JobTitle
	            ,HireDate
            FROM 
                HumanResources.Employee 
            WHERE 
                BusinessEntityID = @id
            ", connectionString, ResultType.Tuples, SingleRow = true>(conn, tran)

        jamesKramerId |> cmd.Execute |> Option.get

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

    use updatedJobTitle = new AdventureWorks.HumanResources.uspUpdateEmployeeHireInfo(conn, tran)
    let recordsAffrected = 
        updatedJobTitle.Execute(
            businessEntityID, 
            newJobTitle, 
            hireDate, 
            RateChangeDate = DateTime.Now, 
            Rate = 12M, 
            PayFrequency = 1uy, 
            CurrentFlag = true 
        )
    
    let updatedJobTitle = 
        // Static Create factory method can also be used to pass connection and/or transaction
        // It provides better intellisense. See a link below
        // https://github.com/Microsoft/visualfsharp/issues/449
        use cmd = new AdventureWorks.dbo.ufnGetContactInformation(conn, tran)
        //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)

    tran.Commit()

Implicit a.k.a Ambient Transactions

It can become tedious to pass around a connection or transaction pair over and over again. Fortunately, the .NET BCL class TransactionScope was designed to address such tediousness. The basic idea is that all database connections opened within specific scope are included in that transaction. Thus, the example above can be re-written as follows:

 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: 
open System.Transactions
do
    use tran = new TransactionScope()

    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

    use updatedJobTitle = new AdventureWorks.HumanResources.uspUpdateEmployeeHireInfo(connectionString)
    let recordsAffrected = 
        updatedJobTitle.Execute(
            businessEntityID, 
            newJobTitle, 
            hireDate, 
            RateChangeDate = DateTime.Now, 
            Rate = 12M, 
            PayFrequency = 1uy, 
            CurrentFlag = true 
        )
    
    let updatedJobTitle = 
        use cmd = new AdventureWorks.dbo.ufnGetContactInformation(connectionString)
        //Use ExecuteSingle on sproc/function generated types
        //if you're sure it return 0 or 1 rows
        let result = cmd.ExecuteSingle(PersonID = jamesKramerId) 
        result.Value.JobTitle.Value

    assert(newJobTitle = updatedJobTitle)

    tran.Complete()

Although very convenient, TransactionScope has some pitfalls and therefore requires a good understanding of what happens behind the scenes. Make sure to read the General Usage Guidelines to avoid common mistakes.

There are two kind of issues you might run into when using TransactionScope:

Unexpectedly Distributed Transactions

Distributed Transactions spell all kind of trouble. They are rarely required and should be avoided in most cases. Strictly speaking this problem is not specific to TransactionScope, but it can be exacerbated by automatic Transaction Management Escalation, which thus makes it annoying easy to fall into the trap.

If a local transaction was accidently promoted to distributed it should be considered a design problem. It's generally best to have a simple check in your code right before commit to reveal the issue:

1: 
2: 
3: 
4: 
5: 
6: 
7: 
do
    use tran = new TransactionScope()
    //your transaction logic here
    let isDistributed = Transaction.Current.TransactionInformation.DistributedIdentifier <> Guid.Empty
    if isDistributed 
    then invalidOp "Unexpected distributed transaction."
    else tran.Complete()

TIP SQL Server can use multiple SQLConnections in a TransactionScope without escalating, provided the connections are not open at the same time (which would result in multiple "physical" TCP connections and thus require escalation).

TIP The value of the Enlist key from SqlConnection.ConnectionString property determines the auto-enlistment behavior of connection instance.

TransactionScope + AsyncExecute

Another tricky problem involves combining TransactionScope with asynchronous execution. TransactoinScope has thread affinity. To propagate the transaction context to another thread, .NET 4.5.1 introduced TransactionScopeAsyncFlowOption.Enabled which needs to be passed into the TransactionScope constructor. Unfortunately if you are stuck with .NET Framework prior to version 4.5.1 the only way to combine TransactionScope with AsyncExecute is explicit transactions.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
do
    use tran = new TransactionScope(TransactionScopeAsyncFlowOption.Enabled)
    
    use cmd = new SqlCommandProvider<"
        INSERT INTO Sales.CurrencyRate 
        VALUES (@currencyRateDate, @fromCurrencyCode, @toCurrencyCode, 
                @averageRate, @endOfDayRate, @modifiedDate) 
    ", connectionString>(connectionString)

    let today = DateTime.Now.Date

    let recordsInserted = 
        cmd.AsyncExecute(
            currencyRateDate = today, 
            fromCurrencyCode = CurrencyCode.``US Dollar``, 
            toCurrencyCode = CurrencyCode.``United Kingdom Pound``, 
            averageRate = 0.63219M, 
            endOfDayRate = 0.63219M, 
            modifiedDate = today) 
        |> Async.RunSynchronously

    assert (recordsInserted = 1)

    tran.Complete()

DataTable Updates/Bulk Load

Statically typed data tables generated either by SqlProgrammabilityProvider or by SqlCommandProvider with ResultType.DataTable have two helper methods Update and BulkCopy to send changes back into the database. Both methods accept connection and transaction to support explicit transactions.

The example above which inserts a new USD/GBP currency rate 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: 
do
    let currencyRates = new AdventureWorks.Sales.Tables.CurrencyRate()
    let today = DateTime.Now
    currencyRates.AddRow(
            CurrencyRateDate = today, 
            FromCurrencyCode = CurrencyCode.``US Dollar``, 
            ToCurrencyCode = CurrencyCode.``United Kingdom Pound``, 
            AverageRate = 0.63219M, 
            EndOfDayRate = 0.63219M)

    use conn = new SqlConnection(connectionString)
    conn.Open()
    use tran = conn.BeginTransaction()

    let recordsAffected = currencyRates.Update(conn, tran)
    assert (recordsAffected = 1)
    
    //or Bulk Load
    //currencyRates.BulkCopy(conn, transaction = tran)

    tran.Commit()

Same as above with implicit transaction.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
do
    let currencyRates = new AdventureWorks.Sales.Tables.CurrencyRate()
    let today = DateTime.Now
    currencyRates.AddRow(
            CurrencyRateDate = today, 
            FromCurrencyCode = CurrencyCode.``US Dollar``, 
            ToCurrencyCode = CurrencyCode.``United Kingdom Pound``, 
            AverageRate = 0.63219M, 
            EndOfDayRate = 0.63219M)

    use tran = new TransactionScope()

    let recordsAffected = currencyRates.Update()
    assert (recordsAffected = 1)
    
    //or Bulk Load
    //currencyRates.BulkCopy()

    tran.Complete()
namespace System
namespace System.Data
namespace System.Data.SqlClient
type CurrencyCode = obj

Full name: Transactions.CurrencyCode
val connectionString : string

Full name: Transactions.connectionString
type InsertCurrencyRate = obj

Full name: Transactions.InsertCurrencyRate
val conn : SqlConnection
Multiple items
type SqlConnection =
  inherit DbConnection
  new : unit -> SqlConnection + 1 overload
  member BeginTransaction : unit -> SqlTransaction + 3 overloads
  member ChangeDatabase : database:string -> unit
  member Close : unit -> unit
  member ConnectionString : string with get, set
  member ConnectionTimeout : int
  member CreateCommand : unit -> SqlCommand
  member DataSource : string
  member Database : string
  member EnlistDistributedTransaction : transaction:ITransaction -> unit
  ...

Full name: System.Data.SqlClient.SqlConnection

--------------------
SqlConnection() : unit
SqlConnection(connectionString: string) : unit
SqlConnection.Open() : unit
val tran : SqlTransaction
SqlConnection.BeginTransaction() : SqlTransaction
SqlConnection.BeginTransaction(transactionName: string) : SqlTransaction
SqlConnection.BeginTransaction(iso: Data.IsolationLevel) : SqlTransaction
SqlConnection.BeginTransaction(iso: Data.IsolationLevel, transactionName: string) : SqlTransaction
property SqlTransaction.Connection: SqlConnection
val cmd : InsertCurrencyRate
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
SqlTransaction.Commit() : unit
type AdventureWorks = obj

Full name: Transactions.AdventureWorks
Multiple items
namespace System.Data

--------------------
namespace Microsoft.FSharp.Data
type IsolationLevel =
  | Unspecified = -1
  | Chaos = 16
  | ReadUncommitted = 256
  | ReadCommitted = 4096
  | RepeatableRead = 65536
  | Serializable = 1048576
  | Snapshot = 16777216

Full name: System.Data.IsolationLevel
field Data.IsolationLevel.Serializable = 1048576
val jamesKramerId : int
val businessEntityID : obj
val jobTitle : string
val hireDate : obj
val cmd : IDisposable
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
val updatedJobTitle : IDisposable
val recordsAffrected : obj
property DateTime.Now: DateTime
val updatedJobTitle : string
val result : obj
namespace System.Transactions
val tran : TransactionScope
Multiple items
type TransactionScope =
  new : unit -> TransactionScope + 7 overloads
  member Complete : unit -> unit
  member Dispose : unit -> unit

Full name: System.Transactions.TransactionScope

--------------------
TransactionScope() : unit
TransactionScope(scopeOption: TransactionScopeOption) : unit
TransactionScope(transactionToUse: Transaction) : unit
TransactionScope(scopeOption: TransactionScopeOption, scopeTimeout: TimeSpan) : unit
TransactionScope(scopeOption: TransactionScopeOption, transactionOptions: TransactionOptions) : unit
TransactionScope(transactionToUse: Transaction, scopeTimeout: TimeSpan) : unit
TransactionScope(scopeOption: TransactionScopeOption, transactionOptions: TransactionOptions, interopOption: EnterpriseServicesInteropOption) : unit
TransactionScope(transactionToUse: Transaction, scopeTimeout: TimeSpan, interopOption: EnterpriseServicesInteropOption) : unit
TransactionScope.Complete() : unit
val isDistributed : bool
type Transaction =
  member Clone : unit -> Transaction
  member DependentClone : cloneOption:DependentCloneOption -> DependentTransaction
  member Dispose : unit -> unit
  member EnlistDurable : resourceManagerIdentifier:Guid * enlistmentNotification:IEnlistmentNotification * enlistmentOptions:EnlistmentOptions -> Enlistment + 1 overload
  member EnlistPromotableSinglePhase : promotableSinglePhaseNotification:IPromotableSinglePhaseNotification -> bool
  member EnlistVolatile : enlistmentNotification:IEnlistmentNotification * enlistmentOptions:EnlistmentOptions -> Enlistment + 1 overload
  member Equals : obj:obj -> bool
  member GetHashCode : unit -> int
  member IsolationLevel : IsolationLevel
  member Rollback : unit -> unit + 1 overload
  ...

Full name: System.Transactions.Transaction
property Transaction.Current: Transaction
property Transaction.TransactionInformation: TransactionInformation
property TransactionInformation.DistributedIdentifier: Guid
Multiple items
type Guid =
  struct
    new : b:byte[] -> Guid + 4 overloads
    member CompareTo : value:obj -> int + 1 overload
    member Equals : o:obj -> bool + 1 overload
    member GetHashCode : unit -> int
    member ToByteArray : unit -> byte[]
    member ToString : unit -> string + 2 overloads
    static val Empty : Guid
    static member NewGuid : unit -> Guid
    static member Parse : input:string -> Guid
    static member ParseExact : input:string * format:string -> Guid
    ...
  end

Full name: System.Guid

--------------------
Guid()
Guid(b: byte []) : unit
Guid(g: string) : unit
Guid(a: int, b: int16, c: int16, d: byte []) : unit
Guid(a: uint32, b: uint16, c: uint16, d: byte, e: byte, f: byte, g: byte, h: byte, i: byte, j: byte, k: byte) : unit
Guid(a: int, b: int16, c: int16, d: byte, e: byte, f: byte, g: byte, h: byte, i: byte, j: byte, k: byte) : unit
field Guid.Empty
val invalidOp : message:string -> 'T

Full name: Microsoft.FSharp.Core.Operators.invalidOp
val today : DateTime
property DateTime.Date: DateTime
Multiple items
type Async
static member AsBeginEnd : computation:('Arg -> Async<'T>) -> ('Arg * AsyncCallback * obj -> IAsyncResult) * (IAsyncResult -> 'T) * (IAsyncResult -> unit)
static member AwaitEvent : event:IEvent<'Del,'T> * ?cancelAction:(unit -> unit) -> Async<'T> (requires delegate and 'Del :> Delegate)
static member AwaitIAsyncResult : iar:IAsyncResult * ?millisecondsTimeout:int -> Async<bool>
static member AwaitTask : task:Task<'T> -> Async<'T>
static member AwaitWaitHandle : waitHandle:WaitHandle * ?millisecondsTimeout:int -> Async<bool>
static member CancelDefaultToken : unit -> unit
static member Catch : computation:Async<'T> -> Async<Choice<'T,exn>>
static member FromBeginEnd : beginAction:(AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg:'Arg1 * beginAction:('Arg1 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg1:'Arg1 * arg2:'Arg2 * beginAction:('Arg1 * 'Arg2 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg1:'Arg1 * arg2:'Arg2 * arg3:'Arg3 * beginAction:('Arg1 * 'Arg2 * 'Arg3 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromContinuations : callback:(('T -> unit) * (exn -> unit) * (OperationCanceledException -> unit) -> unit) -> Async<'T>
static member Ignore : computation:Async<'T> -> Async<unit>
static member OnCancel : interruption:(unit -> unit) -> Async<IDisposable>
static member Parallel : computations:seq<Async<'T>> -> Async<'T []>
static member RunSynchronously : computation:Async<'T> * ?timeout:int * ?cancellationToken:CancellationToken -> 'T
static member Sleep : millisecondsDueTime:int -> Async<unit>
static member Start : computation:Async<unit> * ?cancellationToken:CancellationToken -> unit
static member StartAsTask : computation:Async<'T> * ?taskCreationOptions:TaskCreationOptions * ?cancellationToken:CancellationToken -> Task<'T>
static member StartChild : computation:Async<'T> * ?millisecondsTimeout:int -> Async<Async<'T>>
static member StartChildAsTask : computation:Async<'T> * ?taskCreationOptions:TaskCreationOptions -> Async<Task<'T>>
static member StartImmediate : computation:Async<unit> * ?cancellationToken:CancellationToken -> unit
static member StartWithContinuations : computation:Async<'T> * continuation:('T -> unit) * exceptionContinuation:(exn -> unit) * cancellationContinuation:(OperationCanceledException -> unit) * ?cancellationToken:CancellationToken -> unit
static member SwitchToContext : syncContext:SynchronizationContext -> Async<unit>
static member SwitchToNewThread : unit -> Async<unit>
static member SwitchToThreadPool : unit -> Async<unit>
static member TryCancelled : computation:Async<'T> * compensation:(OperationCanceledException -> unit) -> Async<'T>
static member CancellationToken : Async<CancellationToken>
static member DefaultCancellationToken : CancellationToken

Full name: Microsoft.FSharp.Control.Async

--------------------
type Async<'T>

Full name: Microsoft.FSharp.Control.Async<_>
static member Async.RunSynchronously : computation:Async<'T> * ?timeout:int * ?cancellationToken:Threading.CancellationToken -> 'T
val currencyRates : obj
val recordsAffected : int
Fork me on GitHub