Category Archives: Razor

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!

ASP.NET Sessions

Did you know that if you don’t put anything into an ASP.NET session, it will generate a new one with EVERY page request!!???

Try it out for yourself…

  • Create a new MVC(3/4) Web App in Visual Studio.
  • Choose the Razor rendering engine
  • Add the following code to index.cshtml

Run the website and you will see that the Session ID is different every time you click back to, or refresh the index page!

At first I though this was because I wasn’t logged in. If I go to the Register.cshtml page and create a new account, and login with it, this behaviour still persists.

It’s only when you add something to the session that you see the same Session ID being reused.

Go to the About.cshtml page and add the following code:

Run the app again, and make a note of the Session ID on the index.cshtml page. Next click the About menu tab, and then go back to the Home tab. You will see that your Session has been persisted because you added today’s name into the collection.

Why does it work this way? I’m not really sure. If you have any ideas, I’d be happy to hear them.

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.

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!

IIS 7.5 Unable to serve .cshtml files

I’m playing with WebMatrix and deploying my web site, but when I try to access it, I get the following error:

HTTP Error 404.17 – Not Found. The requested content appears to be script and will not be served by the static file handler.” 

After a bit of hunting around I found that the default .net framework associated with an Application Pool under IIS 7.x seems to be v2.0! To run a Razor site (and display .cshtml files) you must have .Net Framework v4.0 selected for the assigned application pool.

  • To do this, open up the IIS Manager Console (inetmgr.exe). 
  • Expand the hostname node in the “Connections” panel on the left hand side. 
  • Select the “Application Pools” node. 
  • All the App Pools will display in the centre pane, with their details, including which .Net Framework they are running.
  • If your site’s application pool is showing v2.0, double click it and change it to “v4.0 Integrated/Classic” (which ever you need) and then click the OK button to save it. 
  • With the app pool still selected in the main pane, click the “Recycle…” link in the “Actions” panel on the right hand side. This will ensure that the app pool is recycled and restarted with the correct .Net Framework loaded. 
  • Now go back to your website and reload your .cshtml file. It should appear as expected!