SQLProvider


MySQL Provider

Parameters

ConnectionString

Basic connection string used to connect to MySQL instance; typical connection string parameters apply here. See MySQL Connector/NET Connection Strings Documentation for a complete list of connection string options.

1: 
2: 
[<Literal>]
let connString  = "Server=localhost;Database=HR;User=root;Password=password"

To deal with some MySQL data connection problems you might want to add some more parameters to connectionstring: Auto Enlist=false; Convert Zero Datetime=true;

ConnectionStringName

Instead of storing the connection string in the source code, you can store it in the App.config file. This is the name of the connectionString key/value pair stored in App.config (TODO: confirm filename).

1: 
2: 
// found in App.config (TODO: confirm)
let connexStringName = "DefaultConnectionString"

Database Vendor

Use MYSQL from the FSharp.Data.Sql.Common.DatabaseProviderTypes enumeration.

1: 
2: 
[<Literal>]
let dbVendor    = Common.DatabaseProviderTypes.MYSQL

Resolution Path

Path to search for assemblies containing database vendor specific connections and custom types. Type the path where Mysql.Data.dll is stored. Both absolute and relative paths are supported.

1: 
2: 
[<Literal>]
let resPath = __SOURCE_DIRECTORY__ + @"/../../../packages/scripts/MySql.Data/lib/net45"

Individuals Amount

Sets the count of records to load for each table. See individuals for further info.

1: 
2: 
[<Literal>]
let indivAmount = 1000

Use Option Types

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.

1: 
2: 
[<Literal>]
let useOptTypes = true

Example

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
14: 
type sql = SqlDataProvider<
                dbVendor,
                connString,
                ResolutionPath = resPath,
                IndividualsAmount = indivAmount,
                UseOptionTypes = useOptTypes,
                Owner = "HR"
            >
let ctx = sql.GetDataContext()

let employees = 
    ctx.Hr.Employees 
    |> Seq.map (fun e -> e.ColumnValues |> Seq.toList)
    |> Seq.toList

Working with Type-mappings

Basic types

MySql.Data types are not always the ones you have used to in .NET, so here is a little help:

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
let myEmp = 
    query {
        for jh in ctx.Hr.JobHistory do
        where (jh.Years > 10u)
        select (jh)
    } |> Seq.head

let myUint32 = 10u
let myInt64 = 10L
let myUInt64 = 10UL

System.Guid Serialization

If you use string column to save a Guid to database, you may want to skip the hyphens ("-") when serializing them:

1: 
2: 
3: 
let myGuid = System.Guid.NewGuid() //e.g. b8fa7880-ce44-4315-8d60-a160e5734c4b

let myGuidAsString = myGuid.ToString("N") // e.g. "b8fa7880ce4443158d60a160e5734c4b"

The problem with this is that you should never forgot to use "N" in anywhere.

System.DateTime Serialization

Another problem with MySql.Data is that DateTime conversions may fail if your culture is not the expected one.

So you may have to convert datetimes as strings instead of using just myEmp.BirthDate <- DateTime.UtcNow:

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

Notice that if you use .ToString("s") there will be "T" between date and time: "yyyy-MM-ddTHH\:mm\:ss". And comparing two datetimes as strings with "T" and without "T" will generate a problem with the time-part.

If your DateTime columns are strings in the database, you can use DateTime.Parse in your where-queries:

1: 
2: 
3: 
4: 
5: 
6: 
7: 
let longAgo = DateTime.UtcNow.AddYears(-5)
let myEmp = 
    query {
        for emp in ctx.Hr.Employees do
        where (DateTime.Parse(emp.HireDate) > longAgo)
        select (emp)
    } |> Seq.head

You should be fine even with canonical functions like DateTime.Parse(a.MeetStartTime).AddMinutes(10.).

Caveats / Additional Info

Check General, Static Parameters and Querying documentation.

Suppport for MySqlConnector

MySqlConnector is alternative driver to use instead of MySql.Data.dll. It has less features but a lot better performance than the official driver.

You can use it with SQLProvider: Just remove MySql.Data.dll from your resolutionPath and insert there MySqlConnector.dll instead. (Get the latest from NuGet.) It uses references to System.Buffers.dll, System.Runtime.InteropServices.RuntimeInformation.dll and System.Threading.Tasks.Extensions.dll so copy those files also to your referencePath. You can get them from corresponding NuGet packages.

If you want to use the drivers in parallel, you need two resolution paths:

1: 
2: 
type HRFast = SqlDataProvider<Common.DatabaseProviderTypes.MYSQL, connString, ResolutionPath = @"c:\mysqlConnectorPath", Owner = "HR">
type HRProcs = SqlDataProvider<Common.DatabaseProviderTypes.MYSQL, connString, ResolutionPath = @"c:\MysqlDataPath", Owner = "HR">

Example performance difference from our unit tests

One complex query:

1: 
2: 
MySql.Data.dll:     Real: 00:00:00.583, CPU: 00:00:00.484, GC gen0: 1, gen1: 0, gen2: 0
MySqlConnector.dll: Real: 00:00:00.173, CPU: 00:00:00.093, GC gen0: 1, gen1: 0, gen2: 0

Lot of async queries:

1: 
2: 
MySQL.Data.dll      Real: 00:00:01.425, CPU: 00:00:02.078, GC gen0: 16, gen1: 1, gen2: 0
MySqlConnector.dll: Real: 00:00:01.091, CPU: 00:00:02.000, GC gen0: 14, gen1: 1, gen2: 0
Multiple items
namespace FSharp

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

--------------------
namespace Microsoft.FSharp.Data
namespace FSharp.Data.Sql
namespace System
Multiple items
type LiteralAttribute =
  inherit Attribute
  new : unit -> LiteralAttribute

--------------------
new : unit -> LiteralAttribute
val connString : string
val connexStringName : string
val dbVendor : Common.DatabaseProviderTypes
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.MYSQL: Common.DatabaseProviderTypes = 3
val resPath : string
val indivAmount : int
val useOptTypes : bool
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>
                    
val ctx : obj
val employees : obj list list
Multiple items
module Seq

from FSharp.Data.Sql

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

from Microsoft.FSharp.Collections
val map : mapping:('T -> 'U) -> source:seq<'T> -> seq<'U>
val e : obj
val toList : source:seq<'T> -> 'T list
val myEmp : obj
val query : Linq.QueryBuilder
val jh : obj
custom operation: where (bool)

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

Calls Linq.QueryBuilder.Select
val head : source:seq<'T> -> 'T
val myUint32 : uint32
val myInt64 : int64
val myUInt64 : uint64
val myGuid : Guid
Multiple items
type Guid =
  struct
    new : b:byte[] -> Guid + 5 overloads
    member CompareTo : value:obj -> int + 1 overload
    member Equals : o:obj -> bool + 1 overload
    member GetHashCode : unit -> int
    member ToByteArray : unit -> byte[]
    member ToString : unit -> string + 2 overloads
    member TryFormat : destination:Span<char> * charsWritten:int * ?format:ReadOnlySpan<char> -> bool
    member TryWriteBytes : destination:Span<byte> -> bool
    static val Empty : Guid
    static member NewGuid : unit -> Guid
    ...
  end

--------------------
Guid ()
Guid(b: byte []) : Guid
Guid(b: ReadOnlySpan<byte>) : Guid
Guid(g: string) : Guid
Guid(a: int, b: int16, c: int16, d: byte []) : Guid
Guid(a: uint32, b: uint16, c: uint16, d: byte, e: byte, f: byte, g: byte, h: byte, i: byte, j: byte, k: byte) : Guid
Guid(a: int, b: int16, c: int16, d: byte, e: byte, f: byte, g: byte, h: byte, i: byte, j: byte, k: byte) : Guid
Guid.NewGuid() : Guid
val myGuidAsString : string
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.UtcNow: DateTime with get
val box : value:'T -> obj
val longAgo : obj
val emp : obj
module Seq

from Microsoft.FSharp.Collections
type HRFast = obj
type HRProcs = obj
Fork me on GitHub