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: 
type Employee2 = {
    Id:int
    FirstName:string
    LastName:string
}

let updateEmployee (employee: Employee2) =
    let foundEmployee = query {
        for p in ctx.Public.Employee2 do
        where (p.Id = employee.Id)
        exactlyOneOrDefault
    }
    if not (isNull foundEmployee) then
        foundEmployee.FirstName <- employee.FirstName
        foundEmployee.LastName <- employee.LastName
    ctx.SubmitUpdates()

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: 
ctx.SubmitUpdatesAsync() |> Async.StartAsTask
Multiple items
type LiteralAttribute =
  inherit Attribute
  new : unit -> LiteralAttribute

Full name: Microsoft.FSharp.Core.LiteralAttribute

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

Full name: Crud.connectionString
val resolutionPath : string

Full name: Crud.resolutionPath
namespace System
Multiple items
namespace FSharp

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

--------------------
namespace Microsoft.FSharp.Data
namespace FSharp.Data.Sql
type sql = SqlDataProvider<...>

Full name: Crud.sql
type SqlDataProvider

Full name: FSharp.Data.Sql.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'>The owner of the schema for this provider to resolve (Oracle Only)</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='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

Full name: FSharp.Data.Sql.Common.DatabaseProviderTypes
Common.DatabaseProviderTypes.SQLITE: Common.DatabaseProviderTypes = 1
type CaseSensitivityChange =
  | ORIGINAL = 0
  | TOUPPER = 1
  | TOLOWER = 2

Full name: FSharp.Data.Sql.Common.CaseSensitivityChange
Common.CaseSensitivityChange.ORIGINAL: Common.CaseSensitivityChange = 0
val ctx : SqlDataProvider<...>.dataContext

Full name: Crud.ctx
SqlDataProvider<...>.GetDataContext() : SqlDataProvider<...>.dataContext


<summary>Returns an instance of the SQL Provider using the static parameters</summary>

SqlDataProvider<...>.GetDataContext(transactionOptions: Transactions.TransactionOptions) : SqlDataProvider<...>.dataContext


<summary>Returns an instance of the SQL Provider</summary>
                              <param name='transactionOptions'>TransactionOptions for the transaction created on SubmitChanges.</param>

SqlDataProvider<...>.GetDataContext(connectionString: string) : SqlDataProvider<...>.dataContext


<summary>Returns an instance of the SQL Provider</summary>
                              <param name='connectionString'>The database connection string</param>

SqlDataProvider<...>.GetDataContext(connectionString: string, transactionOptions: Transactions.TransactionOptions) : SqlDataProvider<...>.dataContext


<summary>Returns an instance of the SQL Provider</summary>
                              <param name='connectionString'>The database connection string</param>
                              <param name='transactionOptions'>TransactionOptions for the transaction created on SubmitChanges.</param>

SqlDataProvider<...>.GetDataContext(connectionString: string, resolutionPath: string) : SqlDataProvider<...>.dataContext


<summary>Returns an instance of the SQL Provider</summary>
                              <param name='connectionString'>The database connection string</param>
                              <param name='resolutionPath'>The location to look for dynamically loaded assemblies containing database vendor specific connections and custom types</param>

SqlDataProvider<...>.GetDataContext(connectionString: string, resolutionPath: string, transactionOptions: Transactions.TransactionOptions) : SqlDataProvider<...>.dataContext


<summary>Returns an instance of the SQL Provider</summary>
                              <param name='connectionString'>The database connection string</param>
                              <param name='resolutionPath'>The location to look for dynamically loaded assemblies containing database vendor specific connections and custom types</param>
                              <param name='transactionOptions'>TransactionOptions for the transaction created on SubmitChanges.</param>
val orders : SqlDataProvider<...>.dataContext.mainSchema.main.Orders

Full name: Crud.orders
property SqlDataProvider<...>.dataContext.Main: SqlDataProvider<...>.dataContext.mainSchema
property SqlDataProvider<...>.dataContext.mainSchema.Orders: SqlDataProvider<...>.dataContext.mainSchema.main.Orders


<summary>The table Orders belonging to schema main</summary>
val employees : SqlDataProvider<...>.dataContext.mainSchema.main.Employees

Full name: Crud.employees
property SqlDataProvider<...>.dataContext.mainSchema.Employees: SqlDataProvider<...>.dataContext.mainSchema.main.Employees


<summary>The table Employees belonging to schema main</summary>
val customer : SqlDataProvider<...>.dataContext.main.CustomersEntity

Full name: Crud.customer
property SqlDataProvider<...>.dataContext.mainSchema.Customers: SqlDataProvider<...>.dataContext.mainSchema.main.Customers


<summary>The table Customers belonging to schema main</summary>
Multiple items
module Seq

from FSharp.Data.Sql

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

from Microsoft.FSharp.Collections
val head : source:seq<'T> -> 'T

Full name: Microsoft.FSharp.Collections.Seq.head
val employee : SqlDataProvider<...>.dataContext.main.EmployeesEntity

Full name: Crud.employee
val now : DateTime

Full name: Crud.now
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)
property DateTime.Now: DateTime
val row : SqlDataProvider<...>.dataContext.main.OrdersEntity

Full name: Crud.row
SqlDataProvider<...>.dataContext.mainSchema.main.Orders.Create() : SqlDataProvider<...>.dataContext.main.OrdersEntity
SqlDataProvider<...>.dataContext.mainSchema.main.Orders.Create(data: Collections.Generic.IEnumerable<string * obj>) : SqlDataProvider<...>.dataContext.main.OrdersEntity


<summary>Item array of database columns:
</summary>
property SqlDataProvider<...>.dataContext.main.OrdersEntity.CustomerId: string
property SqlDataProvider<...>.dataContext.main.CustomersEntity.CustomerId: string
property SqlDataProvider<...>.dataContext.main.OrdersEntity.EmployeeId: int64
property SqlDataProvider<...>.dataContext.main.EmployeesEntity.EmployeeId: int64
property SqlDataProvider<...>.dataContext.main.OrdersEntity.Freight: decimal
property SqlDataProvider<...>.dataContext.main.OrdersEntity.OrderDate: DateTime
DateTime.AddDays(value: float) : DateTime
property SqlDataProvider<...>.dataContext.main.OrdersEntity.RequiredDate: DateTime
property SqlDataProvider<...>.dataContext.main.OrdersEntity.ShipAddress: string
property SqlDataProvider<...>.dataContext.main.OrdersEntity.ShipCity: string
property SqlDataProvider<...>.dataContext.main.OrdersEntity.ShipName: string
property SqlDataProvider<...>.dataContext.main.OrdersEntity.ShipPostalCode: string
property SqlDataProvider<...>.dataContext.main.OrdersEntity.ShipRegion: string
property SqlDataProvider<...>.dataContext.main.OrdersEntity.ShippedDate: DateTime
SqlDataProvider<...>.dataContext.SubmitUpdates() : Unit


<summary>Save changes to data-source. May throws errors: To deal with non-saved items use GetUpdates() and ClearUpdates().</summary>
val emp : SqlDataProvider<...>.dataContext.main.EmployeesEntity

Full name: Crud.emp
member Common.SqlEntity.Delete : unit -> unit
type Employee =
  {FirstName: string;
   LastName: string;}

Full name: Crud.Employee
Employee.FirstName: string
Multiple items
val string : value:'T -> string

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

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

Full name: Microsoft.FSharp.Core.string
Employee.LastName: string
val mvps1 : Employee list

Full name: Crud.mvps1
Multiple items
module List

from FSharp.Data.Sql

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

from Microsoft.FSharp.Collections

--------------------
type List<'T> =
  | ( [] )
  | ( :: ) of Head: 'T * Tail: 'T list
  interface IEnumerable
  interface IEnumerable<'T>
  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
  member Tail : 'T list
  static member Cons : head:'T * tail:'T list -> 'T list
  static member Empty : 'T list

Full name: Microsoft.FSharp.Collections.List<_>
val map : mapping:('T -> 'U) -> list:'T list -> 'U list

Full name: Microsoft.FSharp.Collections.List.map
val x : Employee
val row : SqlDataProvider<...>.dataContext.main.EmployeesEntity
SqlDataProvider<...>.dataContext.mainSchema.main.Employees.Create() : SqlDataProvider<...>.dataContext.main.EmployeesEntity
SqlDataProvider<...>.dataContext.mainSchema.main.Employees.Create(data: Collections.Generic.IEnumerable<string * obj>) : SqlDataProvider<...>.dataContext.main.EmployeesEntity


<summary>Item array of database columns:
FirstName,LastName</summary>

SqlDataProvider<...>.dataContext.mainSchema.main.Employees.Create(FirstName: string, LastName: string) : SqlDataProvider<...>.dataContext.main.EmployeesEntity
property SqlDataProvider<...>.dataContext.main.EmployeesEntity.FirstName: string
property SqlDataProvider<...>.dataContext.main.EmployeesEntity.LastName: string
val mvps2 : Employee list

Full name: Crud.mvps2
type Employee2 =
  {Id: int;
   FirstName: string;
   LastName: string;}

Full name: Crud.Employee2
Employee2.Id: int
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<_>
Employee2.FirstName: string
Employee2.LastName: string
val updateEmployee : employee:Employee2 -> Unit

Full name: Crud.updateEmployee
val employee : Employee2
val foundEmployee : Employee2
val query : Linq.QueryBuilder

Full name: Microsoft.FSharp.Core.ExtraTopLevelOperators.query
val p : Employee2
custom operation: where (bool)

Calls Linq.QueryBuilder.Where
custom operation: exactlyOneOrDefault

Calls Linq.QueryBuilder.ExactlyOneOrDefault
val not : value:bool -> bool

Full name: Microsoft.FSharp.Core.Operators.not
val isNull : value:'T -> bool (requires 'T : null)

Full name: Microsoft.FSharp.Core.Operators.isNull
val updateEmployee' : employee:Employee2 -> Unit

Full name: Crud.updateEmployee'
val iter : action:('T -> unit) -> source:seq<'T> -> unit

Full name: Microsoft.FSharp.Collections.Seq.iter
val e : Employee2
val john : Employee2

Full name: Crud.john
val map : mapping:('T -> 'U) -> source:seq<'T> -> seq<'U>

Full name: Microsoft.FSharp.Collections.Seq.map
val x : SqlDataProvider<...>.dataContext.main.EmployeesEntity
property Common.SqlEntity.ColumnValues: seq<string * obj>
val toList : source:seq<'T> -> 'T list

Full name: Microsoft.FSharp.Collections.Seq.toList
val twins : List<Common.SqlEntity>

Full name: Crud.twins
SqlDataProvider<...>.dataContext.GetUpdates() : List<Common.SqlEntity>
SqlDataProvider<...>.dataContext.ClearUpdates() : List<Common.SqlEntity>
SqlDataProvider<...>.dataContext.SubmitUpdatesAsync() : Async<Unit>


<summary>Save changes to data-source. May throws errors: Use Async.Catch and to deal with non-saved items use GetUpdates() and ClearUpdates().</summary>
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 FromBeginEnd : beginAction:(AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg:'Arg1 * beginAction:('Arg1 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg1:'Arg1 * arg2:'Arg2 * beginAction:('Arg1 * 'Arg2 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromBeginEnd : arg1:'Arg1 * arg2:'Arg2 * arg3:'Arg3 * beginAction:('Arg1 * 'Arg2 * 'Arg3 * AsyncCallback * obj -> IAsyncResult) * endAction:(IAsyncResult -> 'T) * ?cancelAction:(unit -> unit) -> Async<'T>
static member FromContinuations : callback:(('T -> unit) * (exn -> unit) * (OperationCanceledException -> unit) -> unit) -> Async<'T>
static member Ignore : computation:Async<'T> -> Async<unit>
static member OnCancel : interruption:(unit -> unit) -> Async<IDisposable>
static member Parallel : computations:seq<Async<'T>> -> Async<'T []>
static member RunSynchronously : computation:Async<'T> * ?timeout:int * ?cancellationToken:CancellationToken -> 'T
static member Sleep : millisecondsDueTime:int -> Async<unit>
static member Start : computation:Async<unit> * ?cancellationToken:CancellationToken -> unit
static member StartAsTask : computation:Async<'T> * ?taskCreationOptions:TaskCreationOptions * ?cancellationToken:CancellationToken -> Task<'T>
static member StartChild : computation:Async<'T> * ?millisecondsTimeout:int -> Async<Async<'T>>
static member StartChildAsTask : computation:Async<'T> * ?taskCreationOptions:TaskCreationOptions -> Async<Task<'T>>
static member StartImmediate : computation:Async<unit> * ?cancellationToken:CancellationToken -> unit
static member StartWithContinuations : computation:Async<'T> * continuation:('T -> unit) * exceptionContinuation:(exn -> unit) * cancellationContinuation:(OperationCanceledException -> unit) * ?cancellationToken:CancellationToken -> unit
static member SwitchToContext : syncContext:SynchronizationContext -> Async<unit>
static member SwitchToNewThread : unit -> Async<unit>
static member SwitchToThreadPool : unit -> Async<unit>
static member TryCancelled : computation:Async<'T> * compensation:(OperationCanceledException -> unit) -> Async<'T>
static member CancellationToken : Async<CancellationToken>
static member DefaultCancellationToken : CancellationToken

Full name: Microsoft.FSharp.Control.Async

--------------------
type Async<'T>

Full name: Microsoft.FSharp.Control.Async<_>
static member Async.StartAsTask : computation:Async<'T> * ?taskCreationOptions:Threading.Tasks.TaskCreationOptions * ?cancellationToken:Threading.CancellationToken -> Threading.Tasks.Task<'T>
Fork me on GitHub