FSharp.Data.SqlClient


Inline T-SQL with SqlProgrammabilityProvider

Starting version 1.8.1 SqlProgrammabilityProvider leverages a new F# 4.0 feature to support inline T-SQL.

1: 
2: 
3: 
4: 
5: 
6: 
7: 
8: 
9: 
do
    use cmd = 
        DB.CreateCommand<"
            SELECT TOP(@topN) FirstName, LastName, SalesYTD 
            FROM Sales.vSalesPerson
            WHERE CountryRegionName = @regionName AND SalesYTD > @salesMoreThan 
            ORDER BY SalesYTD
        ">(connectionString)
    cmd.Execute(topN = 3L, regionName = "United States", salesMoreThan = 1000000M) |> printfn "%A"

This makes SqlProgrammabilityProvider as one stop shop for both executing inline T-SQL statements and accessing to built-in objects like stored procedures, functoins and tables. Connectivity information (connection string and/or config file name) is defined in one place and doesn't have be carried around like in SqlCommandProvider case.

CreateCommand optionally accepts connection, transaction and command timeout parameters. Any of these parameters can be ommited.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
#r "System.Transactions"

do
    use conn = new System.Data.SqlClient.SqlConnection( connectionString)
    conn.Open()
    use tran = conn.BeginTransaction()
    use cmd = 
        DB.CreateCommand<"INSERT INTO Sales.Currency VALUES(@Code, @Name, GETDATE())">(
            connection = conn, 
            transaction = tran, 
            commandTimeout = 120
        )

    cmd.Execute( Code = "BTC", Name = "Bitcoin") |> printfn "Records affected %i"
    //Rollback by default. Uncomment a line below to commit the change.
    //tran.Commit()

Access to command and record types

CreateMethod combines command type definition and constructor invocation. Compare it with usage of SqlCommandProvider where generated command type aliased explicitly.

1: 
2: 
3: 
4: 
5: 
6: 
7: 
let cmd1 = DB.CreateCommand<"SELECT name, create_date FROM sys.databases">(connectionString)
// vs
type Get42 = SqlCommandProvider<"SELECT name, create_date FROM sys.databases", connectionString>
let cmd2 = new Get42(connectionString)

//access to Record type
type Cmd2Record = Get42.Record

Although #3 is most verbose it has distinct advantage of providing straightforward access to type of generated command and record. This becomes important for [unit testing] or explicit type annotations scenarios. By default CreateCommand usage triggers type generation as well. A type located under Commands nested type.

1: 
2: 
3: 
4: 
type Cmd1Command = 
    DB.Commands.``CreateCommand,CommandText"SELECT name, create_date FROM sys.databases"``

type Cmd1Record = Cmd1Command.Record

Type names are generated by compiler and look ugly. The type provider knows to remove illegal '=' and '@' characters but auto-competition still chokes on multi-line definitions.

A workaround is to provide explicit name for generated command type

1: 
2: 
let cmd3 = DB.CreateCommand<"SELECT name, create_date FROM sys.databases", TypeName = "Get42">(connectionString)
type Cmd3Record = DB.Commands.Get42.Record

Note Unfortunate downside of this amazing feature is absent of intellisense for both static method parameters and actual method parameters. This is compiler/tooling issue and tracked here:

https://github.com/Microsoft/visualfsharp/issues/642
https://github.com/Microsoft/visualfsharp/pull/705
https://github.com/Microsoft/visualfsharp/issues/640

Please help to improve quality of F# compiler and tooling by providing feedback to F# team or Don Syme.

val cmd : System.IDisposable
type DB = obj

Full name: Whatsnew.DB
val connectionString : string

Full name: Whatsnew.connectionString
val printfn : format:Printf.TextWriterFormat<'T> -> 'T

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.printfn
val conn : System.Data.SqlClient.SqlConnection
namespace System
namespace System.Data
namespace System.Data.SqlClient
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

--------------------
System.Data.SqlClient.SqlConnection() : unit
System.Data.SqlClient.SqlConnection(connectionString: string) : unit
System.Data.SqlClient.SqlConnection.Open() : unit
val tran : System.Data.SqlClient.SqlTransaction
System.Data.SqlClient.SqlConnection.BeginTransaction() : System.Data.SqlClient.SqlTransaction
System.Data.SqlClient.SqlConnection.BeginTransaction(transactionName: string) : System.Data.SqlClient.SqlTransaction
System.Data.SqlClient.SqlConnection.BeginTransaction(iso: System.Data.IsolationLevel) : System.Data.SqlClient.SqlTransaction
System.Data.SqlClient.SqlConnection.BeginTransaction(iso: System.Data.IsolationLevel, transactionName: string) : System.Data.SqlClient.SqlTransaction
val cmd1 : obj

Full name: Whatsnew.cmd1
type Get42 = obj

Full name: Whatsnew.Get42
val cmd2 : Get42

Full name: Whatsnew.cmd2
type Cmd2Record = obj

Full name: Whatsnew.Cmd2Record
type Cmd1Command = obj

Full name: Whatsnew.Cmd1Command
type Cmd1Record = obj

Full name: Whatsnew.Cmd1Record
val cmd3 : obj

Full name: Whatsnew.cmd3
type Cmd3Record = obj

Full name: Whatsnew.Cmd3Record
Fork me on GitHub