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 compliant 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
    • Stored Procedures returning a result set and Table Valued Functions have ExecuteSingle and AsyncExecuteSingle methods that return Option<'T> and Async<Option<'T>> respectively.
    • 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 Microsoft.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 : System.IDisposable
val printfn : format:Printf.TextWriterFormat<'T> -> 'T

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.printfn
type AdventureWorks = obj

Full name: Index.AdventureWorks
val x : obj
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)
union case Option.Some: Value: 'T -> Option<'T>
val prodAsmId : int
val cost : decimal
val qty : decimal
type ShipMethod = obj

Full name: Index.ShipMethod
Fork me on GitHub