Connect to SQL database without the SQL type provider?

Question

In reference to this question, I need to share a connection string with coworkers without the connection string being pushed to our repository. Using the F# SQL type provider forces me to "hardcode" the connection string in my code. Although the workaround provided in the answer is promising, is there simply a way to connect to the dB without the type provider?

I already know the schema/structure of the dB; is there some other way I can connect?

• DaveShaw: Using the F# SQL type provider forces me to "hardcode" the connection string in my code this isn't true, you can pass a Connection String in at runtime.
• Fyodor Soikin: @DaveShaw the data connection string, you can pass in at runtime. But the shape connection string has to be a constant in the source code.
• s952163: do you simply need to change the connection string at run-time, or rather you don't want to make the connection string public on GitHub (neither the design time nor the run-time)? You could make a dll that contains the connection logic and push that your repo. If you don't to use a type provider you can always connect through ADO.
• kvb: @FyodorSoikin - that's only mostly true ;-). For instance, you could easily create an EnvironmentVariableTypeProvider which exposes all environment variables as literals, in which case you could use one as the argument to the SQL type provider.
• Steven: @CaringDev the connection string would be in a secure, synced folder located in the same relative location on all of our machines.

Answer 1

One possibility is using the EdmxFile type provider:

#I "../packages/FSharp.Data.TypeProviders/lib/net40"
#r "System.Data.Entity"
#r "FSharp.Data.TypeProviders.dll"
open FSharp.Data.TypeProviders
type DB = EdmxFile<"MyDB.edmx">
let c = new DB.Model.ModelContainer("runtimeConnectionString")
query { for e in c.MyEntitySet do select e.Id }

You can create the EDMX file in Visual Studio (from an existing DB or from scratch) by adding a new "ADO.NET Entity Data Model" item to a C# project and then moving it to the F# project. The designer functionality is then still available in the F# project.

Within the same FSharp.Data.TypeProviders project there exist DBML file, SqlData and SqlEntity type providers. The latter two require compile time connection strings or names but also support offline schema caching. So you could add the cache file to source control and then change the connection string.

Another alternative would be to use any of the "live" type providers but point it to a source controlled MDF file:

let [<Literal>] LocalMDF =
 "Server=.\SQLExpress;AttachDbFilename=.\MyDataFile.mdf;Database=dbname;Trusted_Connection=Yes;"

If you are interested in issuing plain SQL, have a look at the SQL Client type provider. It allows specifying the .config file used at design-time.

Apart from that, all "non-type-provider" (less convenient, less type-safe) standard .NET approaches can be used as well:

• ORMs (EF, NHibernate, Dapper, ...)
  Might require non-idiomatic, verbose F# to mimick C# classes
• ADO.NET
• Hand-Coding (SqlDataReader and associates)
• ...