F# TypeProviders, how to Change Database?

Question

Am using F# TypeProviders for processing SQL data from two different servers but many databases. It is going fine except for the speed now which is becoming very slow as I add more files. As at now each file has its own connection string. I was thinking of extracting the connection string and Type provider to an external project with the ability to pass database as an argument. Trying to achieve this, I attempt the following, following the 3rd answer from the following link F# Type Provider, create with string variable:

open System.Configuration
open System.Data.SqlClient
open Microsoft.FSharp.Data.TypeProviders
open System.Data.SqlClient

let con = ConfigurationManager.AppSettings.Item("conDebug")
let setDB dbName =
    let cn = new SqlConnectionStringBuilder(con)
    cn.InitialCatalog <- dbName
    cn.ConnectionString

[<Literal>]
let conStr = setDB "AnotherDB"

type selectedDb = SqlDataConnection<conStr>

but kept on getting the error: This is not a valid constant expression Is it also possible to use the ChangeDatabase member in the SqlDataConnection?

Update: The answer suggested won't solve my problem in this case. Below is what is similar to what am doing for different tables from two different servers:

[<Literal>]
let sourceConStr = @"sourceCon"

type sourceDB = SqlDataConnection<sourceConStr>

type person =
    { Id : string
      birthDate : Nullable<DateTime>
      firstName : string
      lastName : string
      dateCreated : DateTime }

type processPeople()
    member this.makePerson(oldPerson: source.ServiceTypes.Person) =
        { Id = oldPerson.oldId
        ......some other processing of old data
        }

     member this.transferPeople conStr =
        use source = sourceDB.GetDataContext()
        use sw = File.AppendText("./ImportLog.txt")
        use targetCon = new SqlConnection(conStr)
        targetCon.Open()
        let query = "insert query"            

        let insert person =
            try
                use cmd = new SqlCommand(query, targetCon)
                ....setting parameters stuff......                
                cmd.ExecuteNonQuery() |> ignore
            with sx -> sw.WriteLine(sx.Message)
        source.source.ServiceTypes.Person
        |> Seq.map this.makePerson
        |> Seq.filter (fun p -> p.Id <> null && p.birthDate.HasValue )
        |> Seq.iter insert
        printfn "Done"

Now I need to do this for every table i.e class per table and as the files grow compilation and for intellisense to start takes so long.

Answer 1

There is a fundamental flaw in the approach you're trying to take. You want getting a connection string from your application configuration at run time and offer SqlDataConnection type provider to make its magic with underlying database.

But this type provider simply cannot do anything at run time stage of workflow as its job has to be already completed at compile time on some known at compile time database.

Then, you may ask, what is the point of using type provider if we want to make our code, after being compiled once, being able to work with database(s) configurable in run-time?

Right, but we do expect results of type provider work being applicable to structurally the same databases, do we?

So, the way out is offering type provider to do its job upon a database compileTimeDB with literally known at compile time connection string compileTimeCC, and consider all goodies we getting (type checks, Intellisense,...) parameterized upon a connection string. This connection string parameter value runTimeCC can be set at run time in any desirable way as long as it points to a database runTimeDB with the same schema as compileTimeDB .

Illustrating this fundamental principle with a bit of code below:

[<Literal>]
let compileTimeCC = @"Data Source=(localdb)\ProjectsV12;Initial Catalog=compileTimeDB;Integrated Security=True;"
.....
type MySqlConnection = SqlDataConnection<ConnectionString = compileTimeCC>
// Type provider is happy, let it help us writing our DB-related code
.....
let db = MySqlConnection.GetDataContext(runTimeCC)
// at run time db will be a runTimeDB set by connection string runTimeCC that can be any
// as long as runTimeDB and compileTimeDB has same schema
.....

UPDATE as question author made his problem context clearer I may suggest more specific recommendation upon approaching this with the given TP. As SO answers should be reasonably concise let's limit consideration by two legacy Person types OldPersonT1 and OldPersonT2 as data sources and one contemporary ModernPerson type as destination. I'm talking types here, it can be as many as you want instances of such around your DB farm.

Now, let's create a single DB at your localdb named CompileTypeDB and run Sql scripts for creating tables corresponding to OldPersonT1,OldPersonT2, and ModernPerson(it's one time exercise and no real data moving will be involved). This will be a single source of type info for SqlDataConnection TP.

Having this ready, let get back to the code:

type CTSqlConn = SqlDataConnection<ConnectionString = @"Data Source=(LocalDB)\Projectsv12;Initial Catalog=myCompileTimeDB;Integrated Security=True">

type OldPersonT1 = CTSqlConn.ServiceTypes.OldPersonT1 // just for brevity
type OldPersonT2 = CTSqlConn.ServiceTypes.OldPersonT2
type ModernPerson = CTSqlConn.ServiceTypes.ModernPerson

Then, augment each of legacy types with the following static member (given below for OldPersonT1 only for brevity):

type CTSqlConn.ServiceTypes.OldPersonT1 with
    static member MakeModernPersons(rtConn: string) =
        let projection (old: OldPersonT1) =
            // Just a direct copy, but you may be very flexible in spreading verification
            // logic between query, projection, and even makeModernPersons function
            // that will be processing IQueryable<ModernPerson>
            let mp = ModernPerson()
                mp.Id <- old.Id
                mp.birthDate <- old.birthDate
                mp.firstName <- old.firstName 
                mp.lastName <- old.lastName 
                mp.dateCreated <- old.dateCreated
                mp 
        query {
            for oldPerson in (CTSqlConn.GetDataContext(rtConn)).OldPersonT1 do
            select (projection oldPerson)
        }

Now you can get hold on IQueryable<ModernPerson> from any data source of type OldPersonT1 by merely evaluating

OldPersonT1.MakeModernPersons("real time connection string to any DB having OldPersonT1 table")

For this to work real time DB may be not identical to compile time DB, it just should contain everything that OldPersonT1 has and depends upon. Similarly would be true for OldPersonT2 or any other variation type: by implementing MakeModernPersons once per variation type you get all data source instances covered.

Doing with data destination requires a single function with signature

let makeModernPersons destinationConnStr (source: IQueryable<ModernPerson>) =
...

that now covers all possible combinations of Person data sources and destinations just by manipulating values of two real-time connection strings.

Very rough cut, but the idea seems quite clear.