SQLProvider


CRUD sample

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

type sql = SqlDataProvider<Common.DatabaseProviderTypes.SQLITE,
                           connectionString,
                           ResolutionPath = resolutionPath,
                           CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL>

let ctx = sql.GetDataContext()

let orders = ctx.Main.Orders
let employees = ctx.Main.Employees

let customer = ctx.Main.Customers |> Seq.head 
let employee = ctx.Main.Employees |> Seq.head
let now = DateTime.Now

Create() has various overloads to make inserting records simple.

Create a new row

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
let row = orders.Create()
row.CustomerId <- customer.CustomerId
row.EmployeeId <- employee.EmployeeId
row.Freight <- 10M
row.OrderDate <- now.AddDays(-1.0)
row.RequiredDate <- now.AddDays(1.0)
row.ShipAddress <- "10 Downing St"
row.ShipCity <- "London"
row.ShipName <- "Dragons den"
row.ShipPostalCode <- "SW1A 2AA"
row.ShipRegion <- "UK"
row.ShippedDate <- now

Submit updates to the database

1: 
ctx.SubmitUpdates()

After updating your item (row) will have the Id property.

You can also create with the longer ``Create(...)``(parameters)-method like this:

1: 
let emp = ctx.Main.Employees.``Create(FirstName, LastName)``("Don", "Syme")

Delete the row

1: 
row.Delete()

Submit updates to the database

1: 
ctx.SubmitUpdates()

Insert a list of records:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
type Employee = {
    FirstName:string
    LastName:string
}

let mvps1 = [
    {FirstName="Andrew"; LastName="Kennedy"};
    {FirstName="Mads"; LastName="Torgersen"};
    {FirstName="Martin";LastName="Odersky"};
]

mvps1 
    |> List.map (fun x ->
                    let row = employees.Create()
                    row.FirstName <- x.FirstName
                    row.LastName <- x.LastName)

ctx.SubmitUpdates()

Or directly specify the fields:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
let mvps2 = [
    {FirstName="Byron"; LastName="Cook"};
    {FirstName="James"; LastName="Huddleston"};
    {FirstName="Xavier";LastName="Leroy"};
]

mvps2
    |> List.map (fun x ->                                 
                   employees.Create(x.FirstName, x.LastName)                  
                    )

ctx.SubmitUpdates()

update a single row assuming Id is unique

 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: 
26: 
27: 
28: 
29: 
30: 
31: 
32: 
33: 
34: 
35: 
36: 
37: 
38: 
type Employee2 = {
    Id:int
    FirstName:string
    LastName:string
}

let updateEmployee (employee: Employee2) =
    let foundEmployeeMaybe = query {
        for p in ctx.Public.Employee2 do
        where (p.Id = employee.Id)
        select (Some p)
        exactlyOneOrDefault
    }
    match foundEmployeeMaybe with
    | Some foundEmployee ->
        foundEmployee.FirstName <- employee.FirstName
        foundEmployee.LastName <- employee.LastName
        ctx.SubmitUpdates()
    | None -> ()

let updateEmployee' (employee: Employee2) =
    query {
        for p in ctx.Public.Employee2 do
        where (p.Id = employee.Id)
    }
    |> Seq.iter( fun e ->
        e.FirstName <- employee.FirstName
        e.LastName <- employee.LastName
    )
    ctx.SubmitUpdates()

let john = {
  Id = 1
  FirstName = "John"
  LastName = "Doe" }

updateEmployee john
updateEmployee' john

Finally it is also possible to specify a seq of string * obj which is exactly the output of .ColumnValues:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
employees 
    |> Seq.map (fun x ->
                employee.Create(x.ColumnValues)) // create twins
    |>  Seq.toList

let twins = ctx.GetUpdates() // Retrieve the FSharp.Data.Sql.Common.SqlEntity objects

ctx.ClearUpdates() // delete the updates
ctx.GetUpdates() // Get the updates
ctx.SubmitUpdates() // no record is added

Inside SubmitUpdate the transaction is created by default TransactionOption, which is Required: Shares a transaction, if one exists, and creates a new transaction if necessary. So e.g. if you have query-operation before SubmitUpdates, you may want to create your own transaction to wrap these to the same transaction.

SQLProvider also supports async database operations:

1: 
2: 
ctx.SubmitUpdatesAsync() |> Async.StartAsTask
        

OnConflict

The SQLite and PostgreSQL 9.5+ providers support conflict resolution for INSERT statements.

They allow the user to specify if a unique constraint violation should be solved by ignoring the statement (DO NOTHING) or updating existing rows (DO UPDATE).

You can leverage this feature by setting the OnConflict property on a row object: Setting it to DoNothing will add the DO NOTHING clause (PostgreSQL) or the OR IGNORE clause (SQLite). Setting it to Update will add a DO UPDATE clause on the primary key constraint for all columns (PostgreSQL) or a OR REPLACE clause (SQLite).

Sql Server has a similar feature in the form of the MERGE statement. This is not yet supported.

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
let ctx = sql.GetDataContext()

let emp = ctx.Main.Employees.Create()
emp.Id <- 1
emp.FirstName <- "Jane"
emp.LastName <- "Doe"

emp.OnConflict <- FSharp.Data.Sql.Common.OnConflict.Update

ctx.SubmitUpdates()

Delete-query for multiple items

If you want to delete many items from a database table, DELETE FROM [dbo].[EMPLOYEES] WHERE (...), there is a way, although we don't recommend deleting items from a database. Instead you should consider a deletion-flag column. And you should backup your database before even trying this. Note that changes are immediately saved to the database even if you don't call ctx.SubmitUpdates().

1: 
2: 
3: 
4: 
query {
    for c in ctx.Dbo.Employees do
    where (...)
} |> Seq.``delete all items from single table``  |> Async.RunSynchronously

Selecting which Create() to use

There are 3 overrides of create.

The ideal one to use is the long one ``Create(...)``(...):

1: 
let emp = ctx.Main.Employees.``Create(FirstName, LastName)``("Don", "Syme")

This is because it will fail if your database structure changes. So, when your table gets new columns, the code will fail at compile time. Then you decide what to do with the new columns, and not let a bug to customers.

But you may want to use the plain .Create() if your setup is not optimal. Try to avoid these conditions:

  • If your editor intellisense is not working for backtick-variables.
  • You have lot of nullable columns in your database.
  • You want to use F# like a dynamic language.

In the last case you'll be maintaining code like this:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
15: 
16: 
17: 
18: 
19: 
20: 
21: 
22: 
23: 
24: 
let employeeId = 123
// Got some untyped array of data from the client
let createSomeItem (data: seq<string*obj>)  = 
    data
    |> Seq.map( // Some parsing and validation:
        function 
        // Skip some fields
        | "EmployeeId", x
        | "PermissionLevel", x -> "", x
        // Convert and validate some fields
        | "PostalCode", x -> 
            "PostalCode", x.ToString().ToUpper().Replace(" ", "") |> box
        | "BirthDate", x -> 
            let bdate = x.ToString() |> DateTime.Parse
            if bdate.AddYears(18) > DateTime.UtcNow then
                failwith "Too young!"
            else
                "BirthDate", bdate.ToString("yyyy-MM-dd") |> box
        | others -> others)
    |> Seq.filter (fun (key,_) -> key <> "")
                  // Add some fields:
    |> Seq.append [|"EmployeeId", employeeId |> box; 
                    "Country", "UK" |> box |]
    |> ctx.Main.Employees.Create

What to do if your creation fails systematically every time

Some underlying database connection libraries have problems with serializing underlying data types. So, if this fails:

1: 
2: 
emp.BirthDate <- DateTime.UtcNow
ctx.SubmitUpdates()

Try using .SetColumn("ColumnName", value |> box) for example:

1: 
2: 
emp.SetColumn("BirthDate", DateTime.UtcNow.ToString("yyyy-MM-dd HH\:mm\:ss") |> box)
ctx.SubmitUpdates()

SetColumn takes object, so you have more control over the type serialization.

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

--------------------
new : unit -> LiteralAttribute
val connectionString : string
val resolutionPath : string
namespace System
Multiple items
namespace FSharp

--------------------
namespace Microsoft.FSharp
Multiple items
namespace FSharp.Data

--------------------
namespace Microsoft.FSharp.Data
namespace FSharp.Data.Sql
type sql = obj
type SqlDataProvider


<summary>Typed representation of a database</summary>
                    <param name='ConnectionString'>The connection string for the SQL database</param>
                    <param name='ConnectionStringName'>The connection string name to select from a configuration file</param>
                    <param name='DatabaseVendor'> The target database vendor</param>
                    <param name='IndividualsAmount'>The amount of sample entities to project into the type system for each SQL entity type. Default 1000.</param>
                    <param name='UseOptionTypes'>If true, F# option types will be used in place of nullable database columns. If false, you will always receive the default value of the column's type even if it is null in the database.</param>
                    <param name='ResolutionPath'>The location to look for dynamically loaded assemblies containing database vendor specific connections and custom types.</param>
                    <param name='Owner'>Oracle: The owner of the schema for this provider to resolve. PostgreSQL: A list of schemas to resolve, separated by spaces, newlines, commas, or semicolons.</param>
                    <param name='CaseSensitivityChange'>Should we do ToUpper or ToLower when generating table names?</param>
                    <param name='TableNames'>Comma separated table names list to limit a number of tables in big instances. The names can have '%' sign to handle it as in the 'LIKE' query (Oracle and MSSQL Only)</param>
                    <param name='ContextSchemaPath'>The location of the context schema previously saved with SaveContextSchema. When not empty, will be used to populate the database schema instead of retrieving it from then database.</param>
                    <param name='OdbcQuote'>Odbc quote characters: Quote characters for the table and column names: `alias`, [alias]</param>
                    <param name='SQLiteLibrary'>Use System.Data.SQLite or Mono.Data.SQLite or select automatically (SQLite only)</param>
                    
namespace FSharp.Data.Sql.Common
type DatabaseProviderTypes =
  | MSSQLSERVER = 0
  | SQLITE = 1
  | POSTGRESQL = 2
  | MYSQL = 3
  | ORACLE = 4
  | MSACCESS = 5
  | ODBC = 6
  | FIREBIRD = 7
  | MSSQLSERVER_DYNAMIC = 8
Common.DatabaseProviderTypes.SQLITE: Common.DatabaseProviderTypes = 1
type CaseSensitivityChange =
  | ORIGINAL = 0
  | TOUPPER = 1
  | TOLOWER = 2
Common.CaseSensitivityChange.ORIGINAL: Common.CaseSensitivityChange = 0
val ctx : obj
val orders : obj
val employees : seq<obj>
val customer : obj
Multiple items
module Seq

from FSharp.Data.Sql

--------------------
module Seq

from Microsoft.FSharp.Collections
val head : source:seq<'T> -> 'T
val employee : obj
val now : DateTime
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

--------------------
DateTime ()
   (+0 other overloads)
DateTime(ticks: int64) : DateTime
   (+0 other overloads)
DateTime(ticks: int64, kind: DateTimeKind) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, calendar: Globalization.Calendar) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, kind: DateTimeKind) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, calendar: Globalization.Calendar) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int) : DateTime
   (+0 other overloads)
DateTime(year: int, month: int, day: int, hour: int, minute: int, second: int, millisecond: int, kind: DateTimeKind) : DateTime
   (+0 other overloads)
property DateTime.Now: DateTime with get
val row : obj
val emp : obj
type Employee =
  { FirstName: string
    LastName: string }
Employee.FirstName: string
Multiple items
val string : value:'T -> string

--------------------
type string = String
Employee.LastName: string
val mvps1 : Employee list
Multiple items
module List

from FSharp.Data.Sql

--------------------
module List

from Microsoft.FSharp.Collections

--------------------
type List<'T> =
  | ( [] )
  | ( :: ) of Head: 'T * Tail: 'T list
    interface IReadOnlyList<'T>
    interface IReadOnlyCollection<'T>
    interface IEnumerable
    interface IEnumerable<'T>
    member GetReverseIndex : rank:int * offset:int -> int
    member GetSlice : startIndex:int option * endIndex:int option -> 'T list
    member Head : 'T
    member IsEmpty : bool
    member Item : index:int -> 'T with get
    member Length : int
    ...
val map : mapping:('T -> 'U) -> list:'T list -> 'U list
val x : Employee
val row : Employee
val mvps2 : Employee list
type Employee2 =
  { Id: int
    FirstName: string
    LastName: string }
Employee2.Id: int
Multiple items
val int : value:'T -> int (requires member op_Explicit)

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

--------------------
type int<'Measure> = int
Employee2.FirstName: string
Employee2.LastName: string
val updateEmployee : employee:Employee2 -> unit
val employee : Employee2
val foundEmployeeMaybe : Employee2 option
val query : Linq.QueryBuilder
val p : Employee2
custom operation: where (bool)

Calls Linq.QueryBuilder.Where
custom operation: select ('Result)

Calls Linq.QueryBuilder.Select
union case Option.Some: Value: 'T -> Option<'T>
custom operation: exactlyOneOrDefault

Calls Linq.QueryBuilder.ExactlyOneOrDefault
val foundEmployee : Employee2
union case Option.None: Option<'T>
val updateEmployee' : employee:Employee2 -> 'a
val iter : action:('T -> unit) -> source:seq<'T> -> unit
val e : Employee2
val john : Employee2
val map : mapping:('T -> 'U) -> source:seq<'T> -> seq<'U>
val x : obj
val toList : source:seq<'T> -> 'T list
val twins : obj
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 -> Async<unit>
  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 Choice : computations:seq<Async<'T option>> -> Async<'T option>
  static member FromBeginEnd : beginAction:(AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
  ...

--------------------
type Async<'T> =
static member Async.StartAsTask : computation:Async<'T> * ?taskCreationOptions:Threading.Tasks.TaskCreationOptions * ?cancellationToken:Threading.CancellationToken -> Threading.Tasks.Task<'T>
val emp : Employee2
type OnConflict =
  | Throw
  | Update
  | DoNothing
union case Common.OnConflict.Update: Common.OnConflict
val c : obj
val ( delete all items from single table ) : _arg1:Linq.IQueryable<'T> -> Async<int>
static member Async.RunSynchronously : computation:Async<'T> * ?timeout:int * ?cancellationToken:Threading.CancellationToken -> 'T
val employeeId : int
val createSomeItem : data:seq<string * obj> -> 'a
val data : seq<string * obj>
Multiple items
val seq : sequence:seq<'T> -> seq<'T>

--------------------
type seq<'T> = Collections.Generic.IEnumerable<'T>
type obj = Object
union case CanonicalOp.ToUpper: CanonicalOp
union case CanonicalOp.Replace: SqlItemOrColumn * SqlItemOrColumn -> CanonicalOp
val box : value:'T -> obj
val bdate : DateTime
DateTime.Parse(s: string) : DateTime
DateTime.Parse(s: string, provider: IFormatProvider) : DateTime
DateTime.Parse(s: ReadOnlySpan<char>,?provider: IFormatProvider,?styles: Globalization.DateTimeStyles) : DateTime
DateTime.Parse(s: string, provider: IFormatProvider, styles: Globalization.DateTimeStyles) : DateTime
property DateTime.UtcNow: DateTime with get
val failwith : message:string -> 'T
val others : string * obj
val filter : predicate:('T -> bool) -> source:seq<'T> -> seq<'T>
val key : string
val append : source1:seq<'T> -> source2:seq<'T> -> seq<'T>
Fork me on GitHub