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 and stored procedures 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=(LocalDb)\v11.0;Initial Catalog=AdventureWorks2012;Integrated Security=True"

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

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
[<Literal>]
let query = "
    SELECT TOP(@TopN) FirstName, LastName, SalesYTD 
    FROM Sales.vSalesPerson
    WHERE CountryRegionName = @regionName AND SalesYTD > @salesMoreThan 
    ORDER BY SalesYTD
" 

type SalesPersonQuery = SqlCommandProvider<query, connectionString>
let cmd = new SalesPersonQuery()

cmd.AsyncExecute(TopN = 3L, regionName = "United States", salesMoreThan = 1000000M) 
|> Async.RunSynchronously

//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: 
type AdventureWorks2012 = SqlProgrammabilityProvider<connectionString>
type GetWhereUsedProductID = AdventureWorks2012.dbo.uspGetWhereUsedProductID
let getWhereUsedProductID = new GetWhereUsedProductID()

getWhereUsedProductID.Execute( StartProductID = 1, CheckDate = System.DateTime(2013,1,1))

//output
//seq
//  [{ ProductAssemblyID = Some 749; ComponentID = Some 807; ... };
//   { ProductAssemblyID = Some 750; ComponentID = Some 807; ... };
//   { ProductAssemblyID = Some 751; ComponentID = Some 807; ... };
//   { ProductAssemblyID = Some 752; ComponentID = Some 807; ... };
//   ...]

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: 
16: 
//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
type OrdersByShipTypeSince = SqlCommandProvider<"
    SELECT COUNT(*) 
    FROM Purchasing.PurchaseOrderHeader 
    WHERE ShipDate > @shippedLaterThan AND ShipMethodID = @shipMethodId", connectionString, SingleRow = true>

let ordersByShipTypeSince = new OrdersByShipTypeSince() 

//overnight orders shipped since Jan 1, 2008 
ordersByShipTypeSince.Execute( System.DateTime( 2008, 1, 1), ShipMethod.``OVERNIGHT J-FAST``) 
//output
//Some (Some 748)

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
union case Connection.Literal: string -> Connection

--------------------
type LiteralAttribute =
  inherit Attribute
  new : unit -> LiteralAttribute

Full name: Microsoft.FSharp.Core.LiteralAttribute

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

Full name: Index.connectionString
val query : string

Full name: Index.query
type SalesPersonQuery = SqlCommandProvider<...>

Full name: Index.SalesPersonQuery
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>
val cmd : SalesPersonQuery

Full name: Index.cmd
SqlCommandProvider<...>.AsyncExecute(TopN: int64, regionName: string, salesMoreThan: decimal) : Async<System.Collections.Generic.IEnumerable<SqlCommandProvider<...>.Record>>
Multiple items
type Async
static member AsBeginEnd : computation:('Arg -> Async<'T>) -> ('Arg * AsyncCallback * obj -> IAsyncResult) * (IAsyncResult -> 'T) * (IAsyncResult -> unit)
static member AwaitEvent : event:IEvent<'Del,'T> * ?cancelAction:(unit -> unit) -> Async<'T> (requires delegate and 'Del :> Delegate)
static member AwaitIAsyncResult : iar:IAsyncResult * ?millisecondsTimeout:int -> Async<bool>
static member AwaitTask : task:Task<'T> -> Async<'T>
static member AwaitWaitHandle : waitHandle:WaitHandle * ?millisecondsTimeout:int -> Async<bool>
static member CancelDefaultToken : unit -> unit
static member Catch : computation:Async<'T> -> Async<Choice<'T,exn>>
static member FromBeginEnd : beginAction:(AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg:'Arg1 * beginAction:('Arg1 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg1:'Arg1 * arg2:'Arg2 * beginAction:('Arg1 * 'Arg2 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg1:'Arg1 * arg2:'Arg2 * arg3:'Arg3 * beginAction:('Arg1 * 'Arg2 * 'Arg3 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromContinuations : callback:(('T -> unit) * (exn -> unit) * (OperationCanceledException -> unit) -> unit) -> Async<'T>
static member Ignore : computation:Async<'T> -> Async<unit>
static member OnCancel : interruption:(unit -> unit) -> Async<IDisposable>
static member Parallel : computations:seq<Async<'T>> -> Async<'T []>
static member RunSynchronously : computation:Async<'T> * ?timeout:int * ?cancellationToken:CancellationToken -> 'T
static member Sleep : millisecondsDueTime:int -> Async<unit>
static member Start : computation:Async<unit> * ?cancellationToken:CancellationToken -> unit
static member StartAsTask : computation:Async<'T> * ?taskCreationOptions:TaskCreationOptions * ?cancellationToken:CancellationToken -> Task<'T>
static member StartChild : computation:Async<'T> * ?millisecondsTimeout:int -> Async<Async<'T>>
static member StartChildAsTask : computation:Async<'T> * ?taskCreationOptions:TaskCreationOptions -> Async<Task<'T>>
static member StartImmediate : computation:Async<unit> * ?cancellationToken:CancellationToken -> unit
static member StartWithContinuations : computation:Async<'T> * continuation:('T -> unit) * exceptionContinuation:(exn -> unit) * cancellationContinuation:(OperationCanceledException -> unit) * ?cancellationToken:CancellationToken -> unit
static member SwitchToContext : syncContext:SynchronizationContext -> Async<unit>
static member SwitchToNewThread : unit -> Async<unit>
static member SwitchToThreadPool : unit -> Async<unit>
static member TryCancelled : computation:Async<'T> * compensation:(OperationCanceledException -> unit) -> Async<'T>
static member CancellationToken : Async<CancellationToken>
static member DefaultCancellationToken : CancellationToken

Full name: Microsoft.FSharp.Control.Async

--------------------
type Async<'T>

Full name: Microsoft.FSharp.Control.Async<_>
static member Async.RunSynchronously : computation:Async<'T> * ?timeout:int * ?cancellationToken:System.Threading.CancellationToken -> 'T
type AdventureWorks2012 = SqlProgrammabilityProvider<...>

Full name: Index.AdventureWorks2012
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>
type GetWhereUsedProductID = SqlProgrammabilityProvider<...>.dbo.uspGetWhereUsedProductID

Full name: Index.GetWhereUsedProductID
type dbo =
  nested type Get
  nested type Init
  nested type MyProc
  nested type MyTableType
  nested type SingleElementProc
  nested type SingleElementType
  nested type Swap
  nested type Tables
  nested type ufnGetAccountingEndDate
  nested type ufnGetAccountingStartDate
  ...

Full name: FSharp.Data.SqlProgrammabilityProvider,ConnectionStringOrName="Data Source=(LocalDb)\\v11.0;Initial Catalog=AdventureWorks2012;Integrated Security=True".dbo
type uspGetWhereUsedProductID =
  inherit RuntimeSqlCommand
  new : ?connectionString: string -> uspGetWhereUsedProductID + 1 overload
  member AsyncExecute : StartProductID: int * CheckDate: DateTime -> Async<IEnumerable<Record>>
  member Execute : StartProductID: int * CheckDate: DateTime -> IEnumerable<Record>
  nested type Record

Full name: FSharp.Data.SqlProgrammabilityProvider,ConnectionStringOrName="Data Source=(LocalDb)\\v11.0;Initial Catalog=AdventureWorks2012;Integrated Security=True".dbo.uspGetWhereUsedProductID


Stored Procedure
val getWhereUsedProductID : GetWhereUsedProductID

Full name: Index.getWhereUsedProductID
SqlProgrammabilityProvider<...>.dbo.uspGetWhereUsedProductID.Execute(StartProductID: int, CheckDate: System.DateTime) : System.Collections.Generic.IEnumerable<SqlProgrammabilityProvider<...>.dbo.uspGetWhereUsedProductID.Record>
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)
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>
type OrdersByShipTypeSince = SqlCommandProvider<...>

Full name: Index.OrdersByShipTypeSince
val ordersByShipTypeSince : OrdersByShipTypeSince

Full name: Index.ordersByShipTypeSince
SqlCommandProvider<...>.Execute(shippedLaterThan: System.DateTime, shipMethodId: int) : Option<Option<int>>
Fork me on GitHub