SqlDataConnection type provider - setting the database connection string with the script parameter

Question

The usual way to use a provider of type SqlDataConnection is as follows:

type dbSchema = SqlDataConnection<"Data Source=MYSERVER\INSTANCE;InitialCatalog=MyDatabase;Integrated Security=SSPI;"> let db = dbSchema.GetDataContext() 

However, we have a problem, which is that we want to use a provider of this type in f # script, where the connection string for the database is passed as a parameter. So, what I would like to do is something like this:

 let connectionString= Array.get args 1 type dbSchema = SqlDataConnection<connectionString> 

However, it gives the error "This is not a constant expression or a valid value for a user attribute"

Is there any way to do this?

Answer 1

Unfortunately, there is no way to do this; a type provider requires a compile-time string. This means that when compiling the application, the type provider can connect and receive metadata about the database and generate types for the compiler. You can extract the connection string into a string literal by writing it in the form

 [<Literal>] let connString = "Data Source=..." type dbSchema = SqlDataConnection<connString> 

Assuming your 2 databases have the same schema, then you can specify the connection string at runtime as a parameter of the GetDataContext method, for example

 let connectionString = args.[1] let dbContext = dbSchema.GetDataContext(connectionString) 

Answer 2

The way I did this is a hard-coded string (using the Literal attribute) to use development time and use the local string from the configuration when getting the data context. I am using a local db scheme (also hardcoded) to speed up intelli-sense during development.

 type private settings = AppSettings<"app.config"> let connString = settings.ConnectionStrings.MyConnectionString type dbSchema = Microsoft.FSharp.Data.TypeProviders.SqlDataConnection<initialConnectionString, Pluralize = true, LocalSchemaFile = localDbSchema , ForceUpdate = false, Timeout=timeout> let indexDb = dbSchema.GetDataContext(connString);