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

• F# SqlClient
• Home page
• Get Library via NuGet
• Source Code on GitHub
• License
• Release Notes
• Documentation
• What's New
• Support for .NET Core
• Configuration and Input
• Output
• FAQ
• Data Modification
• Transactions
• Debugging
• Unit Testing
• Comparison
• Dynamic local db
• SqlEnumProvider
• Tools
• F# Formatting
• FAKE
• F# open-source project scaffolding
• Building great open-source libraries