F# Type Provider for SQL in a class

Question

I am writing a F# to be used with Azure Worker role. I want the class to have the connection string a as a parameter. I create a db connection with

type dbSchema = SqlDataConnection<"...">
let db = dbSchema.GetDataContext()

but dbSchema is a type so it cannot be embeded in my class (another type). I can create two separate modules, one with the db connection and another one with my class

module DataSource =

    [<Literal>]
    let connectionString = "Data Source=.\SQLEXPRESS;Initial Catalog=Service;Integrated Security=True"

    type dbSchema = SqlDataConnection<connectionString>
    let db = dbSchema.GetDataContext()

module DealerFactory =

    type Factory(connectionString) =

        member this.GetList(latitudeLeftTop, longitudeLeftTop, latitudeRightBottom, longitudeRightBottom) =
        ".."

But how do I use the connectionString in my class' constructor to create the connection?

Answer 1

The type provider for SQL database uses connection string for two different purposes. First, it needs one (at compile time) to generate the database schema. Second, you may (optionally) give it another one to use at runtime when actually running the program.

The compile-time connection string needs to be specified as parameter in SqlDataConnection<...> and the run-time connection string can be passed to GetDataContext(...) operation.

So, you can define your type using statically known compile-time connection string:

[<Literal>]
let connectionString = "Data Source=.\SQLEXPRESS;Initial Catalog=Service; ..."
type dbSchema = SqlDataConnection<connectionString>

And when you want to create an instance of the DB connection, you can pass it another connection string:

type Factory(connectionString) =
  // Create connection to the DB using (a different)
  // connection string specified at runtime
  let db = dbSchema.GetDataContext(connectionString)

  member this.GetList( latitudeLeftTop, longitudeLeftTop, 
                       latitudeRightBottom, longitudeRightBottom) =
    // Use local 'db' to access the database
    query { for v db.Table do select v }

Compared with your original code (with the db value in a module), there is a difference that this creates a new db instance for every Factory, but I guess this is expected if Factory takes the connection string as an argument.