Friday, January 16, 2015

Entity Framework - Configurable Database Connection String in ClickOnce Applications

On a recent project, I used Web API 2 and OWIN self hosting along with Entity Framework and LocalDb to allow our web-service to run on a local machine.  When the application was conceived, it was designed to run with LocalDb.  As the application evolved, it was asked if it could be connected to a shared database.

The architecture looks something like this: 

 WPF Shell
  >> OWIN SelfHost WebAPI2
        >>WebAPI2 Controller Project
             >>Entity Framework 6 DAL

Entity Framework code first has several methods of getting a connection string for the DbContext to use. 
  • Connection by convention
    • Uses the DbContext name to create the database
  • Connection by convention with specified database name
    • Creates the database with the name given in the DbContext constructor
  • Connection with full connection string
    • The whole connection string can be passed into the DbContext constructor
  • Connection string in app.config/web.config 
    • If the connection string matches name matches the DbContext name, or a string passed to the DbContext constructor, it will be used.  
The technical challenge is to get the connection string from the WPF application where it's entered by the user, to the Web API controller.  The app.config of the compiled WPF shell contains the connection string that will be used by EF.  If this connection is modified, when the application is restarted, EF will use the new connection string as it initializes.

While researching different ways of approaching this on the web, I noticed that several articles referenced the fact that an Entity Framework connection string contained metadata and used the EntityConnectionStringBuilder class. With Code First this is unnecessary.  Also, many examples online use the SqlConnectionStringBuilder to piece together a connection string from several user inputs.  I opted to expose the entire connection as a string. If someone wants to change this they better know what they are doing :)  I do make use of the SqlConnectoinStringBuilder using the ConnectionString property to throw errors if the connection string contains unknown sections etc. This provides a basic level of validation for the connection string.

The user interface is mildly unattractive and simple :)



Here is the code.
If an empty database is created beforehand, the test connection button will provide validation that the connection worked.  If a valid connection string is supplied with a database that does not yet exist, the test connection functionality isn't so useful.  However, EF will in either case create the correct database schema or database and schema upon restarting the application.

This method has a few drawbacks. Every time the application is updated via the ClickOnce installer, the user will have to reset the connection string.  This is because the connection string is an application setting not a user setting.  Another small annoyance is that the application must be restarted for the new connection string to be read and piped over to the OWIN Self Host project. I would love to see a better way of doing this. Suggestions are welcome.

No comments:

Post a Comment