A set of F# Type Providers for statically typed access to MS SQL database
Clone or download
Latest commit dabf2cb Nov 18, 2018
Permalink
Type Name Latest commit message Commit time
Failed to load latest commit information.
.paket fix mono May 19, 2018
.vs/config samples upgrade Jul 15, 2016
docs Migrate to SDK-style projects Oct 25, 2018
nuget Update packed assemblies in nuspec to reflect new framework targets Nov 10, 2018
src Update release notes Nov 13, 2018
tests Update tests and test projects to target netstandard and net40 TP Nov 10, 2018
tools add build target to run IISExpress to host the documentation and open… May 20, 2018
.gitattributes production version of TP Sep 12, 2013
.gitignore Merge branch 'master' into feature/temp-tvp-types May 20, 2018
.travis.yml Use hosted db for test projects Nov 10, 2018
ISSUE_TEMPLATE.md Create ISSUE_TEMPLATE.md Apr 4, 2016
LICENSE.md license Oct 14, 2013
README.md Update README.md May 19, 2018
RELEASE_NOTES.md Update release notes Nov 13, 2018
Samples.sln introduce paket usage: May 19, 2018
SqlClient.sln Update tests and test projects to target netstandard and net40 TP Nov 10, 2018
SqlClient.userprefs Bug fix for string param with allParametersOptional Apr 19, 2014
TestProjects.sln Update tests and test projects to target netstandard and net40 TP Nov 10, 2018
Tests.sln Update tests and test projects to target netstandard and net40 TP Nov 10, 2018
Types Cache.md Types cache doc May 6, 2015
appveyor.yml Update CI config for dotnet SDK builds Nov 10, 2018
build.cmd migrate to FAKE 5 May 20, 2018
build.fsx update build script Nov 12, 2018
build.sh migrate to FAKE 5 May 20, 2018
fsc.props Update tests and test projects to target netstandard and net40 TP Nov 10, 2018
netfx.props Update tests and test projects to target netstandard and net40 TP Nov 10, 2018
paket.dependencies Cleanup net40 test project, references Nov 12, 2018
paket.lock Cleanup net40 test project, references Nov 12, 2018
provision.sh ctor for DynamicRecord, JSON.NET in tests, vagrant provision Apr 9, 2014

README.md

SqlClient providers

SqlCommandProvider

Provides statically typed access to input 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"

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

More information can be found in the documentation.

Build Status

Windows Linux NuGet
Build status (Windows Server 2012, AppVeyor) Build Status NuGet Status

Maintainers

The default maintainer account for projects under "fsprojects" is @fsprojectsgit - F# Community Project Incubation Space (repo management)