Category Archives: WebMatrix

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.

WebMatrix doesn’t Publish All my Files

I’m trying to publish my project to a local website running under IIS 7 on my dev machine and I noticed that some of my .cshtml files were not being published. How does that happen??

It turns out that WebMatrix caches the list of files and whether or not they’ve been changed since the last publish action. But if for example you’ve gone and manually deleted some files from your test website (as I’d done), and then tried to publish them again – when there had been no changes to them – it will think they aren’t changed, so therefore don’t need to be sync’d again.

This is the default behaviour of WebMatrix thanks to a nice little checkbox that is tucked away in the options settings. Go to the Application menu and click on the Options menu item. The Application menu is the menu to the left of the Home tab:

When the Options dialog appears, click Publish on the left hand side and you will see that the first checkbox is ticked by default:

Uncheck the first option and all of your project files will be sync’d each time you go to publish your site. You will still be able to manually de-select any files you want when the Publish dialog is opened.

Lastly, if you are using a a version control system (you are aren’t you?? :o) ) such as Subversion (SVN) you don’t want all the SVN admin folders and files published to your production website. So it’s a good idea to also check the last checkbox on the same screen so that they can be filtered out:

You may need to close and restart WebMatrix for these changes to take effect. The first time I tried it, it didn’t seem to work properly, so I shutdown WebMatrix, restarted and tried again, this time successfully.

You can also clear WebMatrix’ cached list of published files. See here for details.

Informative Error Messages

I just came across this tonight… I have a column called Quantity(int) defined in one table, and another called exactly the same name in another table. Only this time I set the type to be bigint instead – without realising it.

It just so happens when I render the Quantity value to a WebGrid I use a @helper to do some calculations and format it nicely. The WebGrid that renders the data from the table with the int value Quantity worked perfectly. So I copied and pasted the helper code into the WebGrid for the second (bigint) Quantity expecting it to work just the same.

Instead I got a bizarre error message from the Runtime Binder telling me that the best overloaded method match for my method “has some invalid arguments“:

The best overloaded method match for ‘ASP.MyHelper.GetPositionValue(decimal, decimal, decimal, decimal, int)’ has some invalid arguments

This is the offending line in my grid declaration:

I must admit that I sat and stared at the screen for a long, long, time trying to figure this one out. I thought it must have been a null coming from the database as there were some columns that allowed nulls. I checked my default values to make sure they were right.

I finally picked the error and got it sorted – changing the Quantity column from bigint to int. I just wonder how much faster I would have worked it out if the error message had given me type narrowing/potential data lost error, or an invalid cast exception? To me it didn’t seem like the error message I got was the most informative nor pointed me to the real cause of the problem.

ASP.NET/Razor Custom Helpers, DLL’s & Unit Tests

All of the tutorials and sample code I seem to come across for writing a custom @helper using Razor syntax seem to only show how to pass string parameters. I’m building a website that uses bigint, int, nvarchar, datetime and money types in the database.

Obviously bigint maps to long, int to int and nvarchar to string, but for some reason I couldn’t seem to figure out what to map the database money type to, or check for null against a datetime (DB) value. Maybe there is a mapping table specific to razor out there, but I couldn’t find it.

In the end I figured out that the database Money type maps to the .net Decimal type. So the @helper call in my .cshtml file to get the profit from a sale, looks like this:

@MyHelper.GetProfit( @item.Buy, @item.Sell, @item.Qty )

The @helper implementation is:

@helper GetProfit( decimal buy, decimal sell, int qty) 
{
    var zero = new Decimal(0.0);
    if ( qty > 0 && sell > zero )
    {
        var profit = (sell – buy) * qty;
        @string.Format(“{0,0:C2}”, profit);
    }
    else
    {
        var str = “n/a”;
        @str;
    }
}

This seems to work well and prints out a nicely formatted $10,170.00, –$1,340.00 or n/a thanks to the {0,0:C2} formatter string.

If you are editing some values and need to process them on the postback for storing in the database, you need to figure out if you are working with the string value from the form data, or need a strongly typed object ready to pass to your db.Execute( ) statement.

In the top of your .cshtml file you are going to need something like this:

    var buyPrice = “$”;
    var sellPrice = “$”;
    var quantity = “”;
    var sellDate = “”;
    SqlDateTime sellDateDT = DateTime.Now;

To perform validation and any data manipulation you will need to work with the ‘var‘ values (ie. string’s). eg: testing if the date entered by the user is a valid date etc… Once you’ve done that, you have to decide whether to pass a DateTime object, set to the entered value, or a null value (if allowed in the database). For that, you are going to need an SqlDateTime object – set to the correct value, or else set to SqlDateTime.Null.

As I started to create more and more .cshtml pages with these sorts of values to stored in the database, I realised that I needed some utility methods to perform the repetitive, mundane tasks of sanitizing the data received from the posted form.

This led me to write my own little custom .dll with a series of utility classes. I put all of the sanitise methods in there. Some include:


public static string SanitiseCurrency(string input) {}
public static SqlDateTime SanitiseDate(string input) {}
public static string SanitiseWholeNum(string input) {}
public static string SanitiseStr(string input) {}

A sample implementation of one of these santitise methods could look something like this:


public static SqlDateTime SanitiseDate(string input)

{
    SqlDateTime result = SqlDateTime.Null;
    try 
    {
        if (!string.IsNullOrEmpty(input))
            result = SqlDateTime.Parse(input.Trim());
    }
    catch (Exception)
    {
        result = SqlDateTime.Null; // just to make sure
    }
    return result;
}

Notice that most methods return a string object, which works nicely for using parameterized SQL Queries, but the DateTime method must return a strongly typed instance to satisfy the DB driver type mappings etc…

I edit and compile this .dll in Visual Studio 2010, and have a post build success event that copies the output assembly into the /bin folder of my WebMatrix project. It’s so simple to do, no playing with paths, or imports or assembly references etc… Just drop it in the /bin directly and it’s immediately available to your Razor code.

Now, some might think this is a little overkill, but there is a nice side effect – I can create a Unit Test Project under the same solution that holds my utilities project. And suddenly I can edit, build, and test my code! If it passes all my test, I can deploy it to the WebMatrix /bin directory with confidence that my financial/money utility methods are correct with their processing.

Another benefit of having an external project like this, is that you can also call out of your @helper code into your C# .dll and gain the benefit of the feature rich environment in Visual Studio if your @helper performs some complex operations or calculations. This allows you to unit test your @helper too!!

So using the example from the very start of this post again, once I have moved the @helper code out into my utility project, it now looks like this:

@using MyApp.Core;
@helper GetProfit( decimal buy, decimal sell, int qty)
{
    @Utils.GetProfit( buy, sell, qty );
}

Don’t forget to import any namespaces you have used in your .dll, otherwise ASP.NET won’t find it when it attempts to compile the page.

So I now have nice clean HTML code in my .cshtml file; I have nice clean code in my @Helper code; and I have a set of fully unit tested utility functions I can access from my custom .dll.

Very Nice!