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?

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)
• ...