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
        })

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

Fork me on GitHub