Using connectionstring from app.config with FSharp.Data.SqlClient

Question

I'm using FSharp.Data.SqlClient and trying to move my connectionString from a [<Literal>] to the app.config.

My app.config looks like this

<?xml version="1.0" encoding="utf-8" ?>
<configuration>
  <connectionStrings>
    <add name="DefaultConnection" connectionString="Data Source=(localdb)\MSSQLLocalDB;Initial Catalog=Test;Integrated Security=True" providerName="System.Data.SqlClient"/>
  </connectionStrings>
    <startup>
        <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.2" />
    </startup>
</configuration>

And my SqlCommandProvider looks like the below, which should be correct according to http://fsprojects.github.io/FSharp.Data.SqlClient/configuration%20and%20input.html

new SqlCommandProvider<"SELECT ...", 
       ConnectionStringOrName = "name=DefaultConnection", 
       SingleRow = true, 
       AllParametersOptional = true>(??????)

Now the question is. What goes in the last part, the ?????? part.

I tried "name=DefaultConnection" but it gives me a runtime error with name being unsupported.

I can't seem to find any documentation explaining what goes there.

Update

Instaed of fixnig the issue I found this workaround. https://fsprojects.github.io/FSharp.Configuration/

I don't get the purpose of ConnectionStringOrName if you have to supply the connection string anyway. Also why do you have to specify it twice. Makes very little sense to me :(

Answer 1

When using type providers, you often need two separate data sources:

• Compile-time one that is used when you are editing the code and compiling the code. The type provider uses this connection or data source to infer the schema of the data - in case of SQL provider, this is connection to the database that is used to check that all your column names exist etc.

• Run-time one is used when you actually run the program after it is deployed somewhere. This is where you'll read the actual data from.

The reason why you need two is that the run-time data source may be determined at runtime and it may not be accessible at compile-time (you typically have access to a dev database, but not to production database). The compile-time connection needs to be a constant, so that the provider can use it (when compiling your code) without running any part of your code.

In case of the SQL command provider:

type SelectCmd = SqlCommandProvider<"SELECT ...", 
   ConnectionStringOrName = "name=DefaultConnection", 
   SingleRow = true, 
   AllParametersOptional = true>

let cmd = new SelectCmd(??????)

• "name=DefaultConnection" tells the provider to use an app.config key at compile-time
• ????? is where you need to specify the run-time connection string

To read the connection string from app.config, you can use the standard .NET methods like using ConfigurationManager:

open System.Configuration

let conn = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;
let cmd = new SelectCmd(conn)

Something like this would not work when passing the connection string to the SqlCommandProvider, because this needs to run some code to read the string and that's only possible at runtime. That's why the SQL command provider has a handy option to specify name=DefaultConnection as a special string.