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.
[<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;
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).
// found in App.config (TODO: confirm)
let connexStringName = "DefaultConnectionString"
Use MYSQL
from the FSharp.Data.Sql.Common.DatabaseProviderTypes
enumeration.
[<Literal>]
let dbVendor = Common.DatabaseProviderTypes.MYSQL
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.
[<Literal>]
let resPath = __SOURCE_DIRECTORY__ + @"/../../../packages/tests/MySql.Data/lib/net45"
Sets the count of records to load for each table. See individuals for further info.
[<Literal>]
let indivAmount = 1000
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.
[<Literal>]
let useOptTypes = FSharp.Data.Sql.Common.NullableColumnType.OPTION
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
MySql.Data types are not always the ones you have used to in .NET, so here is a little help:
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
If you use string column to save a Guid to database, you may want to skip the hyphens ("-")
when serializing them:
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.
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
:
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:
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.)
.
Check General, Static Parameters and Querying documentation.
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:
type HRFast = SqlDataProvider<Common.DatabaseProviderTypes.MYSQL, connString, ResolutionPath = @"c:\mysqlConnectorPath", Owner = "HR">
type HRProcs = SqlDataProvider<Common.DatabaseProviderTypes.MYSQL, connString, ResolutionPath = @"c:\MysqlDataPath", Owner = "HR">
One complex query:
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:
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
<summary>Adding this attribute to a value causes it to be compiled as a CLI constant literal.</summary>
<category>Attributes</category>
--------------------
new: unit -> LiteralAttribute
[<Literal>]
val connString: string = "Server=localhost;Database=HR;User=root;Password=password"
val connexStringName: string
[<Literal>]
val dbVendor: Common.DatabaseProviderTypes = 3
namespace FSharp.Data.Sql.Common
[<Struct>]
type DatabaseProviderTypes =
| MSSQLSERVER = 0
| SQLITE = 1
| POSTGRESQL = 2
| MYSQL = 3
| ORACLE = 4
| MSACCESS = 5
| ODBC = 6
| FIREBIRD = 7
| MSSQLSERVER_DYNAMIC = 8
| MSSQLSERVER_SSDT = 9
Common.DatabaseProviderTypes.MYSQL: Common.DatabaseProviderTypes = 3
[<Literal>]
val resPath: string = "C:\git\SQLProvider\docs\content\core/../../../packages/tests/MySql.Data/lib/net45"
[<Literal>]
val indivAmount: int = 1000
[<Literal>]
val useOptTypes: Common.NullableColumnType = 1
[<Struct>]
type NullableColumnType =
| NO_OPTION = 0
| OPTION = 1
| VALUE_OPTION = 2
Common.NullableColumnType.OPTION: Common.NullableColumnType = 1
<summary>
Option types are Option<_>. (Old true.)
</summary>
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 set, F# option types will be used in place of nullable database columns. If not, 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>
<param name='SsdtPath'>A path to an SSDT .dacpac file.'</param>
val ctx: obj
val employees: obj list list
Multiple items
module Seq
from FSharp.Data.Sql
--------------------
module Seq
from Microsoft.FSharp.Collections
<summary>Contains operations for working with values of type <see cref="T:Microsoft.FSharp.Collections.seq`1" />.</summary>
val map: mapping: ('T -> 'U) -> source: seq<'T> -> seq<'U>
<summary>Builds a new collection whose elements are the results of applying the given function
to each of the elements of the collection. The given function will be applied
as elements are demanded using the <c>MoveNext</c> method on enumerators retrieved from the
object.</summary>
<remarks>The returned sequence may be passed between threads safely. However,
individual IEnumerator values generated from the returned sequence should not be accessed concurrently.</remarks>
<param name="mapping">A function to transform items from the input sequence.</param>
<param name="source">The input sequence.</param>
<returns>The result sequence.</returns>
<exception cref="T:System.ArgumentNullException">Thrown when the input sequence is null.</exception>
<example id="item-1"><code lang="fsharp">
let inputs = ["a"; "bbb"; "cc"]
inputs |> Seq.map (fun x -> x.Length)
</code>
Evaluates to a sequence yielding the same results as <c>seq { 1; 3; 2 }</c></example>
val e: obj
val toList: source: seq<'T> -> 'T list
<summary>Builds a list from the given collection.</summary>
<param name="source">The input sequence.</param>
<returns>The result list.</returns>
<exception cref="T:System.ArgumentNullException">Thrown when the input sequence is null.</exception>
<example id="tolist-1"><code lang="fsharp">
let inputs = seq { 1; 2; 5 }
inputs |> Seq.toList
</code>
Evaluates to <c>[ 1; 2; 5 ]</c>.
</example>
val myEmp: obj
val query: Linq.QueryBuilder
<summary>Builds a query using query syntax and operators.</summary>
<example id="query-1"><code lang="fsharp">
let findEvensAndSortAndDouble(xs: System.Linq.IQueryable<int>) =
query {
for x in xs do
where (x % 2 = 0)
sortBy x
select (x+x)
}
let data = [1; 2; 6; 7; 3; 6; 2; 1]
findEvensAndSortAndDouble (data.AsQueryable()) |> Seq.toList
</code>
Evaluates to <c>[4; 4; 12; 12]</c>.
</example>
val jh: obj
custom operation: where (bool)
Calls Linq.QueryBuilder.Where
<summary>A query operator that selects those elements based on a specified predicate.
</summary>
<example-tbd></example-tbd>
custom operation: select ('Result)
Calls Linq.QueryBuilder.Select
<summary>A query operator that projects each of the elements selected so far.
</summary>
<example-tbd></example-tbd>
val head: source: seq<'T> -> 'T
<summary>Returns the first element of the sequence.</summary>
<param name="source">The input sequence.</param>
<returns>The first element of the sequence.</returns>
<exception cref="T:System.ArgumentNullException">Thrown when the input sequence is null.</exception>
<exception cref="T:System.ArgumentException">Thrown when the input does not have any elements.</exception>
<example id="head-1"><code lang="fsharp">
let inputs = ["banana"; "pear"]
inputs |> Seq.head
</code>
Evaluates to <c>banana</c></example>
<example id="head-2"><code lang="fsharp">
[] |> Seq.head
</code>
Throws <c>ArgumentException</c></example>
val myUint32: uint32
val myInt64: int64
val myUInt64: uint64
val myGuid: Guid
Multiple items
[<Struct>]
type Guid =
new: b: byte[] -> unit + 5 overloads
member CompareTo: value: Guid -> int + 1 overload
member Equals: g: Guid -> bool + 1 overload
member GetHashCode: unit -> int
member ToByteArray: unit -> byte[]
member ToString: unit -> string + 2 overloads
member TryFormat: destination: Span<char> * charsWritten: byref<int> * ?format: ReadOnlySpan<char> -> bool
member TryWriteBytes: destination: Span<byte> -> bool
static member (<>) : a: Guid * b: Guid -> bool
static member (=) : a: Guid * b: Guid -> bool
...
<summary>Represents a globally unique identifier (GUID).</summary>
--------------------
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: int, b: int16, c: int16, d: byte, e: byte, f: byte, g: byte, h: byte, i: byte, j: byte, k: 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.NewGuid() : Guid
val myGuidAsString: string
Guid.ToString() : string
Guid.ToString(format: string) : string
Guid.ToString(format: string, provider: IFormatProvider) : string
Multiple items
[<Struct>]
type DateTime =
new: year: int * month: int * day: int -> unit + 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
...
<summary>Represents an instant in time, typically expressed as a date and time of day.</summary>
--------------------
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
<summary>Gets a <see cref="T:System.DateTime" /> object that is set to the current date and time on this computer, expressed as the Coordinated Universal Time (UTC).</summary>
<returns>An object whose value is the current UTC date and time.</returns>
DateTime.ToString() : string
DateTime.ToString(format: string) : string
DateTime.ToString(provider: IFormatProvider) : string
DateTime.ToString(format: string, provider: IFormatProvider) : string
val box: value: 'T -> obj
<summary>Boxes a strongly typed value.</summary>
<param name="value">The value to box.</param>
<returns>The boxed object.</returns>
<example id="box-example"><code lang="fsharp">
let x: int = 123
let obj1 = box x // obj1 is a generic object type
unbox<int> obj1 // Evaluates to 123 (int)
unbox<double> obj1 // Throws System.InvalidCastException
</code></example>
val longAgo: obj
val emp: obj
module Seq
from Microsoft.FSharp.Collections
<summary>Contains operations for working with values of type <see cref="T:Microsoft.FSharp.Collections.seq`1" />.</summary>
type HRFast = obj
type HRProcs = obj