Category Archives: EntitySpaces

EntitySpaces – Access Multiple Databases Dynamically

I’ve been using EntitySpaces ORM (http://entityspaces.net) for a number of years now in my consulting work. They recently (late 2012) shut up shop and open sourced the code. It was a bit of a shame, not to mention quite surprising, as I thought they were a great company with a great product. But I’m very grateful they open sourced the project and it’s available for download from cnet.com… the full product, with all the tools, bells and whistles! Awesome stuff.

I recently came across the problem of how to dynamically access multiple databases for an Admin app I was building. A customer has 5 different databases that the Admin user can access to create, update or delete certain records. There can be multiple users logged in at once and therefore we need each user’s current database choice to be independent of each other. But I had a problem.

Normally, you would simply store the database choice (eg: country) in the users session ready for access whenever you need to hit the database. EntitySpaces has a nice mechanism for doing this. They provide an IConnectionNameService interface:

You add the database name to your session, and then when you call the EntitySpaces code, it will invoke the GetName() method and retrieve it from the session again and dynamically set which connection string to use from your web.config file.

This will work just fine on one condition – your database access code is running in the ASP.NET session context! What if your code is running in a separate .dll that is included in the project and knows nothing about the web, or ASP.NET or HTTP etc…??

You could try some of the following options:

  1. Pass the database name around from the web context to the DAL code. This means muddying the code with extra parameters and somehow keeping track of user context and thread safety.
  2. Create some sort of alternate collection to track which user is needing to connect to each database. maybe a hashtable/dictionary for quick look up. Here you would need to link back to the lookup class from anywhere where the database was being called.
  3. Find another mechanism for storing the user context and hooking into the EntitySpaces provided IConnectionNameService interface.

Options 1 and 2 simply aren’t practical and would make the code simply too messy. Option 3 though, has potential and with a bit of lateral thinking will provide the best solution.

Given that we are running in a web context on the ASP.NET stack, we have access to the Web Security framework and if we have an authenticated user who has logged in with their email address as their username, for example, we can use the current security context on the current thread as a unique way of storing the name of the database we want to connect to.

If I’m logged into my website then security identity context is going to hold the following information (I’m using forms authentication):

You can see that the Identity holds my login username – in this case my email address. So we can use this as a system wide unique ‘key’ into some collection where we can store which database name we want.

  1. We need a data store that is going to be accessible when the web application first fires up.
  2. We need to be able to store a reference to it that will persist and still be around independent of the ASP.NET Session.
  3. It would be nice to have some thread safety mechanism to allow for synchronized access.

A very nice and handy solution that ticks these boxes is the AppStart Dictionary which is automatically available to all pages in the website. It has the added benefits of providing synchronized access with it’s Lock() and UnLock() methods. We could use the global cache, and this would work quite nicely, but it doesn’t have synchronized access, although it does provide fine grain control over the life of the objects stored in it.

In a WebPages Razor website you would setup your EntitySpaces connection in the _AppStart.cshtml page:

We pass in a reference to the AppState ready to be accessed when the DAL code comes looking for which connection string to use. Here is our implementation of the IConnectionNameService interface:

Now, all we have to do is store the database name in the AppState global dictionary when the user is about to request something from the database:

Our Identity.Name (ie.email address) should be the same here as when the IConnectionNameService interface implementation is called later in the DAL custom .dll. Using a drop down select box in the web interface, you can dynamically change the database to connect to with each request.

As long as you store the country in the AppState dictionary, the EntitySpaces framework will pick it up and dynamically set it correctly each time. Very nice!

Entity Spaces 2011, SQL Server Compact Edition 4.0 and WebMatrix

Okay… I want to do some SQL queries that are wrapped in transactions and not just simple CRUD statements. So I pulled out my trusty DAL code generation library (EntitySpaces) and began integrating it into my WebMatrix project.

After installing EntitySpaces 2011 I referenced all the DLL’s that I needed in my project (I’m using a separate “Core” project in Visual Studio 2010 that is linked to my WebMatrix project), including the EntitySpaces.SqlServerCe4Provider:

WebMatrix uses SQL Server Compact Edition v4.0, and due to the way the EntitySpaces references the different CE versions, you need to supply this in the connection string for code generation:

Code generation went fine and I have my Generated and Custom classes all set to go. Next I needed to reference the EntitySpaces libraries in my Web.config file. Be sure to reference the correct provider for the Compact Edition:

Note, that we specify  “Data Source=|DataDirectory|yot.sdf;” where the Data Directory is going to default to the App_Data folder under your website root directory. This is handy too if you deploy the compact database to your intranet or production site. Because it’s all relative to your App_Data folder, it will still work without having to remember to change the path etc…

Lastly, we need to configure the EntitySpaces library on startup. This is done by adding the necessary code to the _AppStart.cshtml file:

Now, all that’s left to do is to load up a collection and populate a web grid to display it:

The rendered WebGrid looks something like this:

I am now ready to start putting together some more complex multi-step commits using EntitySpaces.