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 :(

@FyodorSoikin Then I get this error instead: `Format of the initialization string does not conform to specification starting at index 0.` — Snæbjørn, Nov 01 '16 at 17:21

score 8 · Accepted Answer · edited May 23 '17 at 12:10

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.

When I try the above code to get the connection string I get the error "This value is not a function and cannot be applied". I have a reference to System.Configuration. What am I missing? — user1206480, May 13 '17 at 20:54
@user1206480 in f# the line should be System.Configuration.ConfigurationManager.ConnectionStrings.["DefaultConnection"] Note the dot before the before the opening square brackets. — Gunner, Jul 19 '18 at 09:46
Just curious, when the app runs in production is there any performance penalty? Does the provider read again the database structure or is it stored somewhere? — boggy, Sep 24 '19 at 00:08

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

Update

1 Answers1