FSharp.Data.SqlClient


FSharp.Data.SqlEnumProvider

Motivation

Often there is a certain amount of reference/lookup data in a database. This information changes relatively rare. At same time it's never represented in an application types.

Here is the specific example. We'll use AdventureWorks2012 as sample database.

Let's say we need to retrieve number of orders shipped in certain way since specific date.

Order shippment types defined in Purchasing.ShipMethod table.

SELECT Name, ShipMethodID FROM Purchasing.ShipMethod

The query returns:

NameShipMethodID
CARGO TRANSPORT 55
OVERNIGHT J-FAST4
OVERSEAS - DELUXE3
XRQ - TRUCK GROUND1
ZY - EXPRESS2

A typical implementation for overnight orders shipped since Jan 1, 2008 is following:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
[<Literal>]
let connStr = @"Data Source=.;Initial Catalog=AdventureWorks2012;Integrated Security=True"

open System 
open System.Data.SqlClient

let conn = new SqlConnection (connStr)
conn.Open()

let cmd = new SqlCommand ("
    SELECT COUNT(*) 
    FROM Purchasing.PurchaseOrderHeader 
    WHERE ShipDate > @shippedLaterThan AND ShipMethodID = @shipMethodId", conn)

cmd.Parameters.AddWithValue("@shippedLaterThan", DateTime(2008, 1, 1)) |> ignore
cmd.Parameters.AddWithValue("@shipMethodId", 4) |> ignore
cmd.ExecuteScalar() |> unbox<int>

The query returns valid answer 748 but suffers from a serious issue - it uses magic number (4).

The problem can alleviated by ad-hoc enum definition:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
type ShippingMethod = 
    | ``XRQ - TRUCK GROUND`` = 1
    | ``ZY - EXPRESS`` = 2
    | ``OVERSEAS - DELUXE`` = 3
    | ``OVERNIGHT J-FAST`` = 4
    | ``CARGO TRANSPORT 5`` = 5
    
cmd.Parameters.AddWithValue("@shippedLaterThan", DateTime(2008, 1, 1)) |> ignore
cmd.Parameters.AddWithValue("@shipMethodId", ShippingMethod.``OVERNIGHT J-FAST``) |> ignore
cmd.ExecuteScalar() |> unbox<int>

But improvement is questionable because we traded one problem for another - keeping this enum type definition in sync with database changes.

Solution - SqlEnumProvider

F# idiomatic enum-like type

Idea is to generate enumeration like type based on a query to database leveraging F# type providers feature.

The code above can be rewritten as follows:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
open FSharp.Data

//by convention: first column is Name, second is Value
type ShipMethod = 
    SqlEnumProvider<"SELECT Name, ShipMethodID FROM Purchasing.ShipMethod ORDER BY ShipMethodID", connStr>

//Now combining 2 F# type providers: SqlEnumProvider and SqlCommandProvider
type OrdersByShipTypeSince = SqlCommandProvider<"
    SELECT COUNT(*) 
    FROM Purchasing.PurchaseOrderHeader 
    WHERE ShipDate > @shippedLaterThan AND ShipMethodID = @shipMethodId", connStr, SingleRow = true>

let cmd2 = new OrdersByShipTypeSince(connStr) 
cmd2.Execute( DateTime( 2008, 1, 1), ShipMethod.``OVERNIGHT J-FAST``) 

This type has semantics similar to standard BCL Enum type and stays in sync with database SqlEnumProvider pointed to. We get readability, up-to date lookup data verified by compiler and intellisense.

Important presumption that source reference data is synchronized with production environment because SqlEnumProvider usually points to development environment database.

The SQL statement used to query database has to return resultset of certain shape:

1: 
2: 
3: 
4: 
5: 
- It has to have 2 or more columns
- The first columns is unique name (or tag/label).
- The second column is value. In contrast to BCL Enum it can not only numeric type but also 
decimal, DateTime, DateTimeOffet or string
- If value consists of more than one column it represented as Tuple. It makes it similar to Java enums 

Again the idea is straightforward: anywhere in the code tag/label will be replace by value: XRQ - TRUCK GROUND with 1, ZY - EXPRESS with 1, etc.

The ShipMethod type provides more idiomatic interface that standard BCL Enum: - Items is read-only field of list<string * 'T> where 'T is type of value - TryParse return option<'T>

Below are sample invocations and output from FSI:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
//Utility methods - provide more idiomatic F# interface
ShipMethod.Items
//val it : List<string * int> =
//  [("XRQ - TRUCK GROUND", 1); ("ZY - EXPRESS", 2); ("OVERSEAS - DELUXE", 3);
//   ("OVERNIGHT J-FAST", 4); ("CARGO TRANSPORT 5", 5)]

ShipMethod.``CARGO TRANSPORT 5``
//val it : int = 5
ShipMethod.``OVERNIGHT J-FAST``
//val it : int = 4
ShipMethod.TryParse("CARGO TRANSPORT 5") 
//val it : Option<int> = Some 5
ShipMethod.TryParse("cargo transport 5") 
//val it : Option<int> = None
ShipMethod.TryParse("cargo transport 5", ignoreCase = true) 
//val it : Option<int> = Some 5
ShipMethod.TryParse("Unknown") 
//val it : Option<int> = None
ShipMethod.Parse("CARGO TRANSPORT 5") 
//val it : int = 5

F# tuple-valued enums

As mentioned above result set with more than 2 columns is mapped to enum with tuple as value. This makes it similar to [Java enums] (http://javarevisited.blogspot.com/2011/08/enum-in-java-example-tutorial.html).

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
//ShipRate is included into the resultset in addition to ShipMethodID``` 
type ShipInfo = 
    SqlEnumProvider<"SELECT Name, ShipMethodID, ShipRate FROM Purchasing.ShipMethod ORDER BY ShipMethodID", connStr>

type TheLatestOrder = SqlCommandProvider<"
    SELECT TOP 1 * 
    FROM Purchasing.PurchaseOrderHeader 
    ORDER BY ShipDate DESC
    ", connStr, SingleRow = true>

let cmd3 = new TheLatestOrder(connStr) 
let theLatestOrder = cmd3.Execute().Value

//exploring multi-item value for application logic

//using the first item for conditional logic
if theLatestOrder.ShipMethodID = fst ShipInfo.``OVERSEAS - DELUXE``
then 
    //using the second item for computation
    printfn "Some calculation: %M" <| 50M * snd ShipInfo.``OVERSEAS - DELUXE``

I have mixed feelings about applicability of multi-item value type. Please provide feedback/examples that prove it useful or otherwise._

CLI native enums

SqlEnumProvider is unique because it supports two type generation strategies: F# idiomatic enum-behaving type and standard CLI enumerated types. Second can be useful where compiler allows only const declaration - attribute constructors for example. Set "CLIEnum" parameter to generate standard enum.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
25: 
//CLI Enum

#r "System.Web.Http.dll" 
#r "System.Net.Http.dll" 
open System.Web.Http

type Roles = 
    SqlEnumProvider<"
        SELECT * 
        FROM (VALUES(('Read'), 1), ('Write', 2), ('Admin', 4)) AS T(Name, Value)
    ", @"Data Source=(LocalDb)\v11.0;Integrated Security=True", Kind = SqlEnumKind.CLI>

type CustomAuthorizeAttribute(roles: Roles) = 
    inherit AuthorizeAttribute()

    override __.OnAuthorization actionContext = 
        //auth logic here
        ()

[<CustomAuthorizeAttribute(Roles.Admin)>]
type MyController() = 
    inherit ApiController()

    member __.Get() = 
        Seq.empty<string>

It also makes this types accessible from C# or any other .NET language.

Miscellaneous

Multi-platform.

Any ADO.NET supported database

SqlEnumProvider has a static parameter "Provider" which allows to pass ADO.NET provider invariant name. This makes it usable with any ADO.NET supported database. "System.Data.SqlClient" is default value for ADO.NET provider.

Invariant names of available ADO.NET providers can be retrieved as follows:

1: 
2: 
open System.Data.Common
[ for r in DbProviderFactories.GetFactoryClasses().Rows -> r.["InvariantName"] ]

Generated types accesable from C#, Visual Basic and other .NET languages

Show your fellow C#/VB developers magic of F# type provider accessible from their favorite language!!! Sample project is here. Demo solution includes example.

Xamarin ???

The type provider should work in XS when a project targets Mono runtime. Nothing technically stops to make it available for Xamarin supported mobile platforms (iOS, Android & Windows Phone) to access SQLite.

Future extensions: FlagsAttribute Enums ?

Educational

F# developers often ask about simple examples of "generated types" type providers. Here you go. I hope it will be useful.

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

Full name: Microsoft.FSharp.Core.LiteralAttribute

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

Full name: Sqlenumprovider.quickstart.connStr
namespace System
namespace System.Data
namespace System.Data.SqlClient
val conn : SqlConnection

Full name: Sqlenumprovider.quickstart.conn
Multiple items
type SqlConnection =
  inherit DbConnection
  new : unit -> SqlConnection + 1 overload
  member BeginTransaction : unit -> SqlTransaction + 3 overloads
  member ChangeDatabase : database:string -> unit
  member Close : unit -> unit
  member ConnectionString : string with get, set
  member ConnectionTimeout : int
  member CreateCommand : unit -> SqlCommand
  member DataSource : string
  member Database : string
  member EnlistDistributedTransaction : transaction:ITransaction -> unit
  ...

Full name: System.Data.SqlClient.SqlConnection

--------------------
SqlConnection() : unit
SqlConnection(connectionString: string) : unit
val cmd : SqlCommand

Full name: Sqlenumprovider.quickstart.cmd
Multiple items
type SqlCommand =
  inherit DbCommand
  new : unit -> SqlCommand + 3 overloads
  member BeginExecuteNonQuery : unit -> IAsyncResult + 1 overload
  member BeginExecuteReader : unit -> IAsyncResult + 3 overloads
  member BeginExecuteXmlReader : unit -> IAsyncResult + 1 overload
  member Cancel : unit -> unit
  member Clone : unit -> SqlCommand
  member CommandText : string with get, set
  member CommandTimeout : int with get, set
  member CommandType : CommandType with get, set
  member Connection : SqlConnection with get, set
  ...

Full name: System.Data.SqlClient.SqlCommand

--------------------
SqlCommand() : unit
SqlCommand(cmdText: string) : unit
SqlCommand(cmdText: string, connection: SqlConnection) : unit
SqlCommand(cmdText: string, connection: SqlConnection, transaction: SqlTransaction) : unit
property SqlCommand.Parameters: SqlParameterCollection
SqlParameterCollection.AddWithValue(parameterName: string, value: obj) : SqlParameter
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

--------------------
DateTime()
   (+0 other overloads)
DateTime(ticks: int64) : unit
   (+0 other overloads)
DateTime(ticks: int64, kind: DateTimeKind) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, calendar: Globalization.Calendar) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, kind: DateTimeKind) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, calendar: Globalization.Calendar) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int) : unit
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int, kind: DateTimeKind) : unit
   (+0 other overloads)
val ignore : value:'T -> unit

Full name: Microsoft.FSharp.Core.Operators.ignore
SqlCommand.ExecuteScalar() : obj
val unbox : value:obj -> 'T

Full name: Microsoft.FSharp.Core.Operators.unbox
Multiple items
val int : value:'T -> int (requires member op_Explicit)

Full name: Microsoft.FSharp.Core.Operators.int

--------------------
type int = int32

Full name: Microsoft.FSharp.Core.int

--------------------
type int<'Measure> = int

Full name: Microsoft.FSharp.Core.int<_>
type ShippingMethod =
  | XRQ - TRUCK GROUND = 1
  | ZY - EXPRESS = 2
  | OVERSEAS - DELUXE = 3
  | OVERNIGHT J-FAST = 4
  | CARGO TRANSPORT 5 = 5

Full name: Sqlenumprovider.quickstart.ShippingMethod
namespace FSharp
namespace FSharp.Data
type ShipMethod

Full name: Sqlenumprovider.quickstart.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='Kind'></param>
type OrdersByShipTypeSince = obj

Full name: Sqlenumprovider.quickstart.OrdersByShipTypeSince
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>
<param name='DataDirectory'>The name of the data directory that replaces |DataDirectory| in connection strings. The default value is the project or script directory.</param>
val cmd2 : OrdersByShipTypeSince

Full name: Sqlenumprovider.quickstart.cmd2
val not : value:bool -> bool

Full name: Microsoft.FSharp.Core.Operators.not
Multiple items
val decimal : value:'T -> decimal (requires member op_Explicit)

Full name: Microsoft.FSharp.Core.Operators.decimal

--------------------
type decimal = System.Decimal

Full name: Microsoft.FSharp.Core.decimal

--------------------
type decimal<'Measure> = decimal

Full name: Microsoft.FSharp.Core.decimal<_>
Multiple items
val string : value:'T -> string

Full name: Microsoft.FSharp.Core.Operators.string

--------------------
type string = System.String

Full name: Microsoft.FSharp.Core.string
type ShipInfo

Full name: Sqlenumprovider.quickstart.ShipInfo
type TheLatestOrder = obj

Full name: Sqlenumprovider.quickstart.TheLatestOrder
val cmd3 : TheLatestOrder

Full name: Sqlenumprovider.quickstart.cmd3
val theLatestOrder : obj

Full name: Sqlenumprovider.quickstart.theLatestOrder
val fst : tuple:('T1 * 'T2) -> 'T1

Full name: Microsoft.FSharp.Core.Operators.fst
val printfn : format:Printf.TextWriterFormat<'T> -> 'T

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.printfn
val snd : tuple:('T1 * 'T2) -> 'T2

Full name: Microsoft.FSharp.Core.Operators.snd
namespace System.Web
namespace System.Web.Http
type Roles =
  | Read = 1
  | Write = 2
  | Admin = 4

Full name: Sqlenumprovider.quickstart.Roles
type SqlEnumKind =
  | Default = 0
  | CLI = 1
  | UnitsOfMeasure = 2

Full name: FSharp.Data.SqlEnumKind
SqlEnumKind.CLI: SqlEnumKind = 1
Multiple items
type CustomAuthorizeAttribute =
  inherit AuthorizeAttribute
  new : roles:Roles -> CustomAuthorizeAttribute
  override OnAuthorization : actionContext:HttpActionContext -> unit

Full name: Sqlenumprovider.quickstart.CustomAuthorizeAttribute

--------------------
new : roles:Roles -> CustomAuthorizeAttribute
val roles : Roles
Multiple items
type AuthorizeAttribute =
  inherit AuthorizationFilterAttribute
  new : unit -> AuthorizeAttribute
  member OnAuthorization : actionContext:HttpActionContext -> unit
  member Roles : string with get, set
  member TypeId : obj
  member Users : string with get, set

Full name: System.Web.Http.AuthorizeAttribute

--------------------
AuthorizeAttribute() : unit
override CustomAuthorizeAttribute.OnAuthorization : actionContext:Controllers.HttpActionContext -> unit

Full name: Sqlenumprovider.quickstart.CustomAuthorizeAttribute.OnAuthorization
val actionContext : Controllers.HttpActionContext
field Roles.Admin = 4
Multiple items
type MyController =
  inherit ApiController
  new : unit -> MyController
  member Get : unit -> seq<string>

Full name: Sqlenumprovider.quickstart.MyController

--------------------
new : unit -> MyController
Multiple items
type ApiController =
  member Configuration : HttpConfiguration with get, set
  member ControllerContext : HttpControllerContext with get, set
  member Dispose : unit -> unit
  member ExecuteAsync : controllerContext:HttpControllerContext * cancellationToken:CancellationToken -> Task<HttpResponseMessage>
  member ModelState : ModelStateDictionary
  member Request : HttpRequestMessage with get, set
  member Url : UrlHelper with get, set
  member User : IPrincipal

Full name: System.Web.Http.ApiController

--------------------
ApiController() : unit
member MyController.Get : unit -> seq<string>

Full name: Sqlenumprovider.quickstart.MyController.Get
module Seq

from Microsoft.FSharp.Collections
val empty<'T> : seq<'T>

Full name: Microsoft.FSharp.Collections.Seq.empty
Multiple items
val string : value:'T -> string

Full name: Microsoft.FSharp.Core.Operators.string

--------------------
type string = String

Full name: Microsoft.FSharp.Core.string
namespace System.Data.Common
val r : Data.DataRow
type DbProviderFactories =
  static member GetFactory : providerInvariantName:string -> DbProviderFactory + 1 overload
  static member GetFactoryClasses : unit -> DataTable

Full name: System.Data.Common.DbProviderFactories
DbProviderFactories.GetFactoryClasses() : Data.DataTable
Fork me on GitHub