SQLProvider


ODBC

DSN

Configuring DSN on Windows ODBC Data Source Administrator server: Control Panel -> Administrative Tools -> Data Sources (ODBC) (or launch: c:\windows\syswow64\odbcad32.exe) and add your driver to DSN.

1: 
2: 
3: 
4: 
5: 
open FSharp.Data.Sql 
[<Literal>] 
let dnsConn = @"DSN=foo" 
type db = SqlDataProvider<Common.DatabaseProviderTypes.ODBC, dnsConn>
let ctx = db.GetDataContext()

You don't need DSN for all data source, e.g. MS Access can be used directly through the database file with a connection string like: @"Driver={Microsoft Access Driver (*.mdb, *.accdb)};Dbq=C:\Northwind.mdb"

Quote Character

Because there are a lot of different kind of ODBC connection types, you can set quote characters for SQL-clauses with optional parameter OdbcQuote.

  • OdbcQuoteCharacter.SQUARE_BRACKETS
  • OdbcQuoteCharacter.GRAVE_ACCENT
  • OdbcQuoteCharacter.NO_QUOTES
  • OdbcQuoteCharacter.DOUBLE_QUOTES
  • OdbcQuoteCharacter.APHOSTROPHE

and the difference in executed SQL is:

1: 
2: 
3: 
4: 
5: 
SELECT [CourseID], [CourseName] FROM [Course] as [q] 
SELECT `CourseID`, `CourseName` FROM `Course` as `q` 
SELECT  CourseID ,  CourseName  FROM  Course  as  q 
SELECT "CourseID", "CourseName" FROM "Course" as "q"
SELECT 'CourseID', 'CourseName' FROM 'Course' as 'q'
1: 
2: 
3: 
4: 
[<Literal>] 
let quotechar = FSharp.Data.Sql.Common.OdbcQuoteCharacter.DEFAULT_QUOTE
type db2 = SqlDataProvider<Common.DatabaseProviderTypes.ODBC, dnsConn, OdbcQuote = quotechar>
let ctx2 = db2.GetDataContext()

DTC Transactions

SQLProvider will do DTC-transactions over CRUD-operations. That will ensure that all the database operations will either success of fail at once, when you do ctx.SubmitUpdates().

However, some ODBC-drivers (like MS-Access) don't support DTC-transactions and will fail on constructor call. The transaction creation can be disabled in context creation:

1: 
2: 
3: 
4: 
5: 
let ctx3 = 
    db.GetDataContext(
        { Timeout = TimeSpan.MaxValue; 
          IsolationLevel = Transactions.IsolationLevel.DontCreateTransaction
        }:FSharp.Data.Sql.Transactions.TransactionOptions)

Don't disable transactions if you don't need to.

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

--------------------
new : unit -> LiteralAttribute
val connectionString : string
val connectionString2 : string
val connectionString3 : string
val connectionString4 : string
val resolutionPath : string
Multiple items
namespace FSharp

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

--------------------
namespace Microsoft.FSharp.Data
namespace FSharp.Data.Sql
val dnsConn : string
type db = 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.ODBC: Common.DatabaseProviderTypes = 6
val ctx : obj
val quotechar : Common.OdbcQuoteCharacter
type OdbcQuoteCharacter =
  | DEFAULT_QUOTE = 0
  | GRAVE_ACCENT = 1
  | SQUARE_BRACKETS = 2
  | NO_QUOTES = 3
  | DOUBLE_QUOTES = 4
  | APHOSTROPHE = 5
Common.OdbcQuoteCharacter.DEFAULT_QUOTE: Common.OdbcQuoteCharacter = 0
type db2 = obj
val ctx2 : obj
val ctx3 : obj
namespace FSharp.Data.Sql.Transactions
type IsolationLevel =
  | Serializable = 0
  | RepeatableRead = 1
  | ReadCommitted = 2
  | ReadUncommitted = 3
  | Snapshot = 4
  | Chaos = 5
  | Unspecified = 6
  | DontCreateTransaction = 99
Transactions.IsolationLevel.DontCreateTransaction: Transactions.IsolationLevel = 99
type TransactionOptions =
  { Timeout: TimeSpan
    IsolationLevel: IsolationLevel }
    static member Default : TransactionOptions
Fork me on GitHub