whatsnew

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<"
SELECTTOP(@topN)FirstName,LastName,SalesYTD
FROMSales.vSalesPerson
WHERECountryRegionName=@regionNameANDSalesYTD>@salesMoreThan
ORDERBYSalesYTD
">(connectionString)
 cmd.Execute(topN = 3L, regionName = "UnitedStates", 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<"INSERTINTOSales.CurrencyVALUES(@Code,@Name,GETDATE())">(
 connection = conn, 
 transaction = tran, 
 commandTimeout = 120
 )
 cmd.Execute( Code = "BTC", Name = "Bitcoin") |> printfn "Recordsaffected%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<"SELECTname,create_dateFROMsys.databases">(connectionString)
// vs
type Get42 = SqlCommandProvider<"SELECTname,create_dateFROMsys.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<"SELECTname,create_dateFROMsys.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

FSharp.Data.SqlClient

Inline T-SQL with SqlProgrammabilityProvider

Access to command and record types