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
        ">()
    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

`CreateMethodcombines command type definition and constructor invocation. Compare it with usage ofSqlCommandProvider` where generated command type aliased explicitly.

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

//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">()
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 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
val connectionString : string

Full name: Whatsnew.connectionString
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
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 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