SQLProvider


SQLite Provider

Parameters

ConnectionString

Basic connection string used to connect to the SQLite database.

1: 
2: 
3: 
4: 
5: 
[<Literal>]
let connectionString = 
    "Data Source=" + 
    __SOURCE_DIRECTORY__ + @"/../../../tests/SqlProvider.Tests/scripts/northwindEF.db;" + 
    "Version=3;foreign keys=true"

ResolutionPath

Path to search for database vendor speficic assemblies. Specify the path where System.Data.SQLite.dll is stored. If you use the System.Data.SQLite NuGet package and target .NET 4.6, the path would be something like __SOURCE_DIRECTORY__ + @"\..\packages\System.Data.SQLite.Core.<version>\lib\net46". Both absolute and relative paths are supported.

Note that System.Data.SQLite.dll will look for the native interop library:

  • on Windows: SQLite.Interop.dll in the x64 and x86 subdirectories of the resolution path.
  • on Linux: libSQLite.Interop.so in the resolution path directory.

The interop libraries are not properly placed afer the System.Data.SQLite NuGet package is added, so you might have to manually copy the interop libraries:

  • on Windows: copy x64 and x86 subdirectories from SQLite build directory, which typically is <project root>\packages\System.Data.SQLite.Core.<version>\build\net46.
  • on Linux: first build the libSQLite.Interop.so using <srcDir>/Setup/compile-interop-assembly-release.sh script from System.Data.SQLite source distribution sqlite-netFx-source-1.x.xxx.x.zip. And then copy it from <srcDir>/bin/2013/Release/bin/.

If System.Data.SQLite.dll is in the location where NuGet places it by default, you don't have to submit the ResolutionPath parameter at all, but you still need to copy the interop libraries as described above.

1: 
2: 
[<Literal>]
let resolutionPath = __SOURCE_DIRECTORY__ + @"/../../../tests/SqlProvider.Tests/libs"

SQLiteLibrary

Specifies what SQLite library to use. This is an SQLiteLibrary enumeration, defined in the FSharp.Data.Sql.Common namespace, which has the following members:

  • AutoSelect - Uses System.Data.SQLite under .NET and Mono.Data.SQLite under Mono. This is the default.
  • MonoDataSQLite - Always uses Mono.Data.SQLite.
  • SystemDataSQLite - Always uses System.Data.SQLite.

Example

 1: 
 2: 
 3: 
 4: 
 5: 
 6: 
 7: 
 8: 
 9: 
10: 
11: 
12: 
13: 
type sql = SqlDataProvider<
                Common.DatabaseProviderTypes.SQLITE, 
                SQLiteLibrary = Common.SQLiteLibrary.SystemDataSQLite,
                ConnectionString = connectionString, 
                ResolutionPath = resolutionPath, 
                CaseSensitivityChange = Common.CaseSensitivityChange.ORIGINAL>

let ctx = sql.GetDataContext()

let customers = 
    ctx.Main.Customers
    |> Seq.map(fun c -> c.ContactName)
    |> Seq.toList

CRUD

When you do insert operation, after .SubmitUpdates call you can get inserted rowid like this:

1: 
2: 
3: 
let myCustomer = ctx.Main.Customers.``Create(CompanyName)``("MyCompany")
ctx.SubmitUpdates()
let rowid = myCustomer.GetColumn("rowid") : int
namespace System
Multiple items
namespace FSharp

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

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

Full name: Microsoft.FSharp.Core.LiteralAttribute

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

Full name: Sqlite.connectionString
val resolutionPath : string

Full name: Sqlite.resolutionPath
type sql = SqlDataProvider<...>

Full name: Sqlite.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 SQLiteLibrary =
  | SystemDataSQLite = 0
  | MonoDataSQLite = 1
  | AutoSelect = 2

Full name: FSharp.Data.Sql.Common.SQLiteLibrary
Common.SQLiteLibrary.SystemDataSQLite: Common.SQLiteLibrary = 0
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: Sqlite.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 customers : string list

Full name: Sqlite.customers
property SqlDataProvider<...>.dataContext.Main: SqlDataProvider<...>.dataContext.mainSchema
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 map : mapping:('T -> 'U) -> source:seq<'T> -> seq<'U>

Full name: Microsoft.FSharp.Collections.Seq.map
val c : SqlDataProvider<...>.dataContext.main.CustomersEntity
property SqlDataProvider<...>.dataContext.main.CustomersEntity.ContactName: string
val toList : source:seq<'T> -> 'T list

Full name: Microsoft.FSharp.Collections.Seq.toList
val myCustomer : SqlDataProvider<...>.dataContext.main.CustomersEntity

Full name: Sqlite.myCustomer
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 rowid : int

Full name: Sqlite.rowid
member Common.SqlEntity.GetColumn : key:string -> 'T
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<_>
Fork me on GitHub