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

Question

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

My app.config is as follows

 <?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 this: it should be correct according to http://fsprojects.imtqy.com/FSharp.Data.SqlClient/configuration%20and%20input.html

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

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

I tried "name=DefaultConnection" , but it gives me a runtime error when the name is not supported.

I can't seem to find documentation explaining what is going on there.

Update

Created to fix the problem, I found this workaround. https://fsprojects.imtqy.com/FSharp.Configuration/

I do not understand the purpose of ConnectionStringOrName if you still need to put a connection string. Also why you should specify it twice. It makes very little sense to me :(

Answer 1

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

• Compilation time , which is used when editing code and compiling code. The type provider uses this connection or data source to output the data schema — in the case of the SQL provider, it is a database connection that is used to check for all column names, etc.

• Runtime is used when you actually run the program after deploying it somewhere. Here you will read the actual data.

The reason you need two is because the runtime source at runtime can be determined at runtime, and it may not be available at compile time (usually you have access to the dev database, but not to the production database data). The compile-time connection must be permanent so that the provider can use it (when compiling your code) without using any part of your code.

In the 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 the app.config key at compile time
• ????? is where you need to specify the runtime connection string

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

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

Something like this will not work when passing the connection string to SqlCommandProvider , because for this you need to run some code to read the string and this is only possible at runtime. This is why the SQL command provider has a convenient option to specify name=DefaultConnection as a special string.