FSharp.Data.SqlClient


Not your grandfather's ORM

The library is a home for three type providers:

  • SqlCommandProvider - type-safe access to full set of T-SQL language
  • SqlProgrammabilityProvider - quick access to Sql Server functions, stored procedures and tables in idiomatic F# way
  • SqlEnumProvider - generates enumeration types based on static lookup data from any ADO.NET complaint source
The FSharp.Data.SqlClient library can be installed from NuGet:
PM> Install-Package FSharp.Data.SqlClient

SqlCommandProvider

All examples based on AdventureWorks sample database.

1: 
2: 
3: 
4: 
5: 
open FSharp.Data

[<Literal>]
let connectionString = 
    @"Data Source=.;Initial Catalog=AdventureWorks2014;Integrated Security=True"

The query below retrieves top 3 sales representatives from North American region with YTD sales of more than one million.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
do
    use cmd = new SqlCommandProvider<"
        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"

//output
//seq
//    [("Pamela", "Ansman-Wolfe", 1352577.1325M);
//     ("David", "Campbell", 1573012.9383M);
//     ("Tete", "Mensa-Annan", 1576562.1966M)]

SqlProgrammabilityProvider

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
type AdventureWorks = SqlProgrammabilityProvider<connectionString>
do
    use cmd = new AdventureWorks.dbo.uspGetWhereUsedProductID()
    for x in cmd.Execute( StartProductID = 1, CheckDate = System.DateTime(2013,1,1)) do
        //check for nulls
        match x.ProductAssemblyID, x.StandardCost, x.TotalQuantity with 
        | Some prodAsmId, Some cost, Some qty -> 
            printfn "ProductAssemblyID: %i, StandardCost: %M, TotalQuantity: %M" prodAsmId cost qty
        | _ -> ()

//output
//ProductAssemblyID: 749, StandardCost: 2171.2942, TotalQuantity: 1.00
//ProductAssemblyID: 750, StandardCost: 2171.2942, TotalQuantity: 1.00
//ProductAssemblyID: 751, StandardCost: 2171.2942, TotalQuantity: 1.00
//...

SqlEnumProvider

Let's say we need to retrieve number of orders shipped by a certain shipping method since specific date.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
//by convention: first column is Name, second is Value
type ShipMethod = SqlEnumProvider<"
    SELECT Name, ShipMethodID FROM Purchasing.ShipMethod ORDER BY ShipMethodID", connectionString>

//Combine with SqlCommandProvider
do 
    use cmd = new SqlCommandProvider<"
        SELECT COUNT(*) 
        FROM Purchasing.PurchaseOrderHeader 
        WHERE ShipDate > @shippedLaterThan AND ShipMethodID = @shipMethodId
    ", connectionString, SingleRow = true>() 
    //overnight orders shipped since Jan 1, 2008 
    cmd.Execute( System.DateTime( 2008, 1, 1), ShipMethod.``OVERNIGHT J-FAST``) |> printfn "%A"
    //output
    //Some (Some 1085)

System requirements

  • .NET 4.0 and higher
  • SqlCommandProvider and SqlProgrammabilityProvider only SQL Server 2012 and up or SQL Azure Database at compile-time
  • SqlEnumProvider works with any ADO.NET complain data-source

SqlCommandProvider and SqlProgrammabilityProvider features at glance

  • Static type with 2 methods per SqlCommandProvider<...> declaration:
    • AsyncExecute - for scalability scenarios
    • Execute - convenience when needed
  • Configuration
    • Command text (sql script) can be either literal or path to *.sql file
    • Connection string is either literal or name from config file (app.config is default for config file)
    • Connection string can be overridden at run-time via constructor optional parameter
    • Constructor optionally accepts SqlTransaction and uses associated connection to execute command
    • "ResolutionFolder" parameter - a folder to be used to resolve relative file paths at compile time. Applied to command text *.sql files only.
  • Input:
    • Unbound sql variables/input parameters mapped to mandatory typed arguments for AsyncExecute/Execute
    • Set AllParametersOptional to true to make all parameters optional (nullable) (SqlCommandProvider<...> only)
    • Stored Procedures and User-Defined Functions can be easily discovered with SqlProgrammabilityProvider<...>
    • SqlProgrammabilityProvider<...> infers default values for input parameters
  • Output:
    • Inferred static type for output. Configurable choice of seq<Records>(default), seq<Tuples>,DataTable, or raw SqlReader for custom parsing. For seq<Records> and seq<Tuples> each column mapped to corresponding property/item
    • Nullable output columns translate to the F# Option type
    • Output parameters and return values for stored procedures are not supported. Open a GitHub project issue to request this support.
  • Extra configuration options:
    • SingleRow hint forces singleton output instead of sequence

  • Microsoft.SqlServer.Types (Spatial on Azure) is supported.
  • SqlCommandProvider and SqlProgrammabilityProvider are of "erased types" kind. It can be used only from F#.
  • SqlEnumProvider is of "generated types" kind and can be used from any .NET language.

Limitations

In addition to system requirements listed above SqlCommandProvider and SqlProgrammabilityProvider constrained by same limitations as two system meta-stored procedures it uses in implementation: sys.sp_describe_undeclared_parameters and sys.sp_describe_first_result_set. Look online for more details.

namespace FSharp
namespace FSharp.Data
Multiple items
type LiteralAttribute =
  inherit Attribute
  new : unit -> LiteralAttribute

Full name: Microsoft.FSharp.Core.LiteralAttribute

--------------------
new : unit -> LiteralAttribute
val connectionString : string

Full name: Index.connectionString
val cmd : SqlCommandProvider<...>
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>
SqlCommandProvider<...>.Execute(topN: int64, regionName: string, salesMoreThan: decimal) : System.Collections.Generic.IEnumerable<SqlCommandProvider<...>.Record>
val printfn : format:Printf.TextWriterFormat<'T> -> 'T

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.printfn
type AdventureWorks = SqlProgrammabilityProvider<...>

Full name: Index.AdventureWorks
type SqlProgrammabilityProvider

Full name: FSharp.Data.SqlProgrammabilityProvider



<summary>Typed access to SQL Server programmable objects: stored procedures, functions and user defined table types.</summary>
<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='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='DataDirectory'>The name of the data directory that replaces |DataDirectory| in connection strings. The default value is the project or script directory.</param>
val cmd : SqlProgrammabilityProvider<...>.dbo.uspGetWhereUsedProductID
type dbo =
  nested type Echo
  nested type EchoText
  nested type Get
  nested type Init
  nested type MyProc
  nested type SingleElementProc
  nested type Swap
  nested type Tables
  nested type User-Defined Table Types
  nested type ufnGetAccountingEndDate
  ...

Full name: FSharp.Data.SqlProgrammabilityProvider,ConnectionStringOrName="Data Source=.;Initial Catalog=AdventureWorks2014;Integrated Security=True".dbo
type uspGetWhereUsedProductID =
  inherit ISqlCommand Implementation
  new : ?connectionString: string * ?commandTimeout: int -> uspGetWhereUsedProductID + 1 overload
  member AsyncExecute : StartProductID: int * CheckDate: DateTime -> Async<IEnumerable<Record>>
  member AsyncExecuteSingle : StartProductID: int * CheckDate: DateTime -> Async<Option<Record>>
  member CommandTimeout : int
  member Execute : StartProductID: int * CheckDate: DateTime -> IEnumerable<Record>
  member ExecuteSingle : StartProductID: int * CheckDate: DateTime -> Option<Record>
  static member ConnectionStringOrName : string
  static member Create : ?connectionString: string * ?commandTimeout: int -> uspGetWhereUsedProductID + 1 overload
  nested type Record

Full name: FSharp.Data.SqlProgrammabilityProvider,ConnectionStringOrName="Data Source=.;Initial Catalog=AdventureWorks2014;Integrated Security=True".dbo.uspGetWhereUsedProductID


Stored Procedure
val x : SqlProgrammabilityProvider<...>.dbo.uspGetWhereUsedProductID.Record
SqlProgrammabilityProvider<...>.dbo.uspGetWhereUsedProductID.Execute(StartProductID: int, CheckDate: System.DateTime) : System.Collections.Generic.IEnumerable<SqlProgrammabilityProvider<...>.dbo.uspGetWhereUsedProductID.Record>


<param name='@StartProductID'>Input parameter for the stored procedure uspGetWhereUsedProductID. Enter a valid ProductID from the Production.Product table.</param>
<param name='@CheckDate'>Input parameter for the stored procedure uspGetWhereUsedProductID used to eliminate components not used after that date. Enter a valid date.</param>
namespace System
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

--------------------
System.DateTime()
   (+0 other overloads)
System.DateTime(ticks: int64) : unit
   (+0 other overloads)
System.DateTime(ticks: int64, kind: System.DateTimeKind) : unit
   (+0 other overloads)
System.DateTime(year: int, month: int, day: int) : unit
   (+0 other overloads)
System.DateTime(year: int, month: int, day: int, calendar: System.Globalization.Calendar) : unit
   (+0 other overloads)
System.DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int) : unit
   (+0 other overloads)
System.DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, kind: System.DateTimeKind) : unit
   (+0 other overloads)
System.DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, calendar: System.Globalization.Calendar) : unit
   (+0 other overloads)
System.DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int) : unit
   (+0 other overloads)
System.DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int, kind: System.DateTimeKind) : unit
   (+0 other overloads)
property SqlProgrammabilityProvider<...>.dbo.uspGetWhereUsedProductID.Record.ProductAssemblyID: Option<int>
property SqlProgrammabilityProvider<...>.dbo.uspGetWhereUsedProductID.Record.StandardCost: Option<decimal>
property SqlProgrammabilityProvider<...>.dbo.uspGetWhereUsedProductID.Record.TotalQuantity: Option<decimal>
union case Option.Some: Value: 'T -> Option<'T>
val prodAsmId : int
val cost : decimal
val qty : decimal
type ShipMethod =
  static val ``CARGO TRANSPORT 5`` : int
  static val Items : IEnumerable<string * int>
  static val ``OVERNIGHT J-FAST`` : int
  static val ``OVERSEAS - DELUXE`` : int
  static member Parse : value: string * ignoreCase: bool -> int + 1 overload
  static member TryFindName : value: int -> Option<string>
  static member TryParse : value: string * ignoreCase: bool -> Option<int> + 1 overload
  static val ``XRQ - TRUCK GROUND`` : int
  static val ``ZY - EXPRESS`` : int

Full name: Index.ShipMethod
type SqlEnumProvider

Full name: FSharp.Data.SqlEnumProvider



<summary>Enumeration based on SQL query.</summary>
<param name='Query'>SQL used to get the enumeration labels and values. A result set must have at least two columns. The first one is a label.</param>
<param name='ConnectionString'>String used to open a data connection.</param>
<param name='Provider'>Invariant name of a ADO.NET provider. Default is "System.Data.SqlClient".</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='CLIEnum'>Generate standard CLI Enum. Default is false.</param>
SqlCommandProvider<...>.Execute(shippedLaterThan: System.DateTime, shipMethodId: int) : Option<Option<int>>
Fork me on GitHub