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 compliant source

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=AdventureWorks2012;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>(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(connectionString)
    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>(connectionString) 
    //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. Use it only if result set has zero or one row.
• 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.

• 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