FSharp.Data.SqlClient - Type providers for Microsoft SQL Server

This library exposes SQL Server Database objects in a type safe manner to F# code, by the mean of Type Providers

You can reference it in F# Interactive that ships with Visual Studio

#r "nuget: FSharp.Data.SqlClient"
open FSharp.Data
open FSharp.Data.SqlClient
let [<Literal>] connectionString = "Server=.;Database=AdventureWorks2012;Trusted_Connection=True;"
type MyCommand = SqlCommandProvider<"""
select 
	data.a 
from 
	(select 1 a union all select 2 union all select 3) data
where
	data.a > @data 
    """, connectionString>;;

(new MyCommand(connectionString)).Execute(data=1) 
|> Seq.toArray
|> printfn "%A"

dotnet fsi is not supported yet.

Quick Links

Type Providers

SqlCommandProvider

Provides statically typed access to the parameters and result set of T-SQL command in idiomatic F# way ^(*).

open FSharp.Data

[<Literal>]
let connectionString = "Data Source=.;Initial Catalog=AdventureWorks2012;Integrated Security=True;TrustServerCertificate=true"

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

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

Exposes Tables, Stored Procedures, User-Defined Types and User-Defined Functions in F# code.

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.

//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)

SqlFileProvider

type SampleCommand = SqlFile<"sampleCommand.sql">
type SampleCommandRelative = SqlFile<"sampleCommand.sql", "MySqlFolder">

use cmd1 = new SqlCommandProvider<SampleCommand.Text, ConnectionStrings.AdventureWorksNamed>()
use cmd2 = new SqlCommandProvider<SampleCommandRelative.Text, ConnectionStrings.AdventureWorksNamed>()