How to add lazy loading to dapper

Dapper is a great way to take care of querying SQL Server (and other) databases. It offers a nice compromise between full blown ORM Frameworks such as entity framework and raw SqlDataReader based approaches. Although entity framework is great it ads another layer of complexity to your applications, I’ve found that sometimes it can be hard to figure out how everything is glued together. Which is why I like to use the very cool dapper library, it’s a bit more transparent than the Entity Framework, giving you more control over the SQL Queries executed and allowing you to use advanced SQL syntax.

Because dapper is purely an object mapper it doesn’t support features like dependency tracking and lazy loading. Today we’ll discuss implementing how to implement lazy loading when you are using dapper (it’s quite easy really!). We’ll be using a new class of the .NET framework 4.0 Lazy<T>.aspx)

Below this post you can find a demo application that shows the completed project. For the demo I’ve used the AdventureWorks database (it’s predecessor being the Northwind sample database) which you can download at here at codeplex.

For the purpose of this demo I’ve modeled the following entities:

Entities

Although not visible in above diagram it’s important to note that the properties Person and Store are of type Lazy<Person> (ha ha, funny I know =), and Lazy<Store>. So after setting up the entities we’ll use the Repository pattern to abstract our database queries. To keep the demo simple I’ve created the following interface and class:

Repositories

As you see only the LoadAll() method is publicly exposed, the others are private methods and are only relevant to the repository itself.

public IList<Customer> LoadAll()
{
   using (var connection = GetConnection())
   {
       string sql = "SELECT CustomerID, PersonID, StoreID, AccountNumber FROM Sales.Customer";
       return connection.Query(sql).Select<dynamic, Customer>(row => {return LoadFromData(row);}).ToList();
   }
}

As you can see we are using dappers dynamic querying abilities, we’ve done this for two reasons:

  1. Using the generic querying methods it’s not possible to set extra properties after dapper created the mapped instance of the entity
  2. To set the lazy loading properties later on the entities we’d have to re iterate the list, which wouldn’t be very efficient.

So anyway as you can see we are mapping the entity manually using the Select Linq extension method, and have implemented the mapping in an method to better abstract the functionality. The Select method takes two generic parameters firstly dynamic, which is the source of the data we are querying. Secondly it takes the destination type as a parameter which in our case is Customer.

Now let’s have a look at the method were we are actually mapping the data, which is where things get interesting.

private Customer LoadFromData(dynamic data)
{
   var customer = new Customer();
   customer.AccountNumber = data.AccountNumber;
   /*SNIP*/
   customer.Person = new Lazy<Person>(() => LoadPerson(customer.PersonID));
   customer.Store = new Lazy<Store>(() => LoadStore(customer.StoreID));
   return customer; 
}

So as you can see we are instantiating a new Customer object, and start assigning the dynamic data properties to it. When we want to create a our Lazy<Person> we instantiate a new Lazy<T> object and pass a Func<T> as the first parameters (these are basically inline delegates), see the MSDN documentation here. So we declare that when you call the property Person on the Customer entity the LoadPerson method is executed, passing in the current customers PersonId.

And that’s all! For a completed version of the project check out the repository on github.:

Basic Authentication with the ASP.NET Membership API

If you are an ASP.NET developer you are probably familiar with both membership and http basic authentication. If you are familiar with IIS’s different authentication methods you also know that the only way to use HTTP authentication on IIS to verify the credentials against the windows account store. In this post we’ll discuss how you can get IIS to use basic authentication against an ASP.NET Membership store.

The second question you should ask yourself, why would you want to do this? After all basic authentication has some serious drawbacks, most namely that credentials are send as plain text when you don’t use SSL, and you can’t force users to sign out due to HTTP’s stateless nature (most browsers cache the credentials until you close the browser window).

Next to that there are also some benefits to using basic authentication in combination with IIS. Namely: it provides an generic interface for authentication supported by all clients that implement the HTTP protocol, using membership you can share credentials over several web applications, it is platform independent (it doesn’t matter if you use classic asp, php, asp.net or anything else that runs on IIS).

So to be more concrete, how do we implement this feature? We implement an System.Web.IHttpModule and handle the AuthenticateRequest event. For more details on how to implement this download the attachment to this post.

The module can be configured with the following options:

<authenticationSection xmlns="urn:BasicAuthenticationModule" enabled="true" realm="test"
requireAuthentication="true" requireSsl="false">
<cache enableCache="false" cacheDurationMinutes="1" />
<roles enableRoles="true" applicationRoleName="sample"/>
</authenticationSection>
  • /authenticationSection/enabled defines to use the module or not
  • /authenticationSection/realm the text to be displayed in the login dialog
  • /authenticationSection/requireAuthentication allow both anonymous and authenticated users to login (your application logic can then handle these situations accordingly.
  • /authenticationSection/requireSsl if set to true users can’t authenticated if the request wasn’t made over an https request
  • /authenticationSection/cache/enableCache if enabled the authentication key of the user is cached for the defined period. You might want to enable this because http is stateless every request to the server is authenticated, thus making a round trip to the database. This include css files, images and javascript files so it can be easily 15 database requests per page without caching.
  • /authenticationSection/cache/cacheDuration the duration of the cache in minutes
  • /authenticationSection/roles/enableRoles Enable your application to roles this feature is useful when you share your membership database over several applications, you can then assign every application as a role, allowing you to configure to which applications a user has access.
  • /authenticationSection/roles/applicationRoleName The name of the role/application that will be used to assign rights to users.

To start using the application you have to either copy the .dll in the download to the bin folder or add the dll to the Global Assembly Cache so that all your applications can use it in IIS. Next you have to make sure you have the following sections inclulded and configured in your web.config file:

<?xml version="1.0" encoding="utf-8"?>
<configuration>
<configSections>
<section name=”authenticationSection” type=”BasicAuthenticationModule.AuthenticationSection, BasicAuthenticationModule”/>
</configSections>

<authenticationSection xmlns=”urn:BasicAuthenticationModule” enabled=”true”
realm=”dex test” requireAuthentication=”true” requireSsl=”false”>
<cache enableCache=”false” cacheDurationMinutes=”1?/>
<roles enableRoles=”true” applicationRoleName=”sample”/>
</authenticationSection>

<connectionStrings>
<add name=”ApplicationServices” connectionString=”data source=.;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|aspnetdb.mdf;User Instance=true” providerName=”System.Data.SqlClient” />
</connectionStrings>

<system.web>

<membership>
<providers>
<clear />
<add name=”AspNetSqlMembershipProvider” type=”System.Web.Security.SqlMembershipProvider” connectionStringName=”ApplicationServices” enablePasswordRetrieval=”false” enablePasswordReset=”true” requiresQuestionAndAnswer=”false” requiresUniqueEmail=”false” maxInvalidPasswordAttempts=”5? minRequiredPasswordLength=”6? minRequiredNonalphanumericCharacters=”0? passwordAttemptWindow=”10? applicationName=”/” />
</providers>
</membership>
<roleManager enabled=”true”>
<providers>
<clear />
<add name=”AspNetSqlRoleProvider” type=”System.Web.Security.SqlRoleProvider” connectionStringName=”ApplicationServices” applicationName=”/” />
</providers>
</roleManager>
</system.web>

<system.webServer>
<validation validateIntegratedModeConfiguration=”false” />
<modules runAllManagedModulesForAllRequests=”true”>
<add name=”CustomBasicAuthentication” type=”BasicAuthenticationModule.AuthenticationModule, BasicAuthenticationModule” />
</modules>
</system.webServer>
</configuration>

I’m assuming you already now how to setup the membership database. If not you can find more information at the msdn page on configuring membership.

Resources:

Download The Basic Authentication Module for IIS

Use this framework to configure Membership or integrate Membership into your ASP.NET MVC Application.

Seed your database with test data using the entity framework

Recently I’ve started to use Entity Framework Code First in favor of linq2sql. The main reason to choose Entity framework is it’s more advanced feature set, and especially cool upcoming features like data migrations. In this post we fill focus on Data Initializers for EF Code First, this feature allows you to seed your database in development scenario’s with some standard data for testing purposes. In this post I’m assuming you’ll already have some experience using EF Code First

Especially useful in case you work in teams, I mean who hasn’t been in the situation where you spend several hours tracking down this extremely annoying bug just to find out that there was an error in the test data. Making the seeding your database part of your development strategy unfortunately doesn’t eliminate the problem, but reduces the risk of such problems greatly.

To be more concrete how do we start using this cool feature?

First of all you’ll need to get the latest version of the entity framework (this post was written for entity framework version 4.2) either by using nuget or by downloading it manually.

For the next step I assume that you are using asp.net mvc, but this could as well apply to asp.net webforms, or windows forms applications. Adding the below line to your Application_Start() event in Global.asax will execute the database operations you’ll define in the MyDataContextDbInitializer class which we’ll create in the next step.

protected void Application_Start()
{
    System.Data.Entity.Database.SetInitializer(new MyDataContextDbInitializer());
}

Creating a database initializer can be done by inheriting from either of those generic classes:

DropCreateDatabaseIfModelChanges<MyDataContext>
DropCreateDatabaseAlways<MyDataContext>

The first as you can read from the class name recreates the database only in case your entities (model classes) have changed, the later always executes the database initialization code. Always recreating the database is useful in case you just started to define your entities, when you are done doing that, you can switch to DropCreateDatabaseIfModelChanges for faster run times during development.

To actually start to implement the class we have to override the Seed method as following:

public class MyDataContextDbInitializer : DropCreateDatabaseIfModelChanges<MyDataContext>
{
   protected override void Seed(MagazineContext context)
   {
     context.Customers.Add(new Customer() { CustomerName = "Test Customer" });
   }
}

As you can see you’ll be provided with an instance of your data context class, and can start to add data for your entities. You don’t have to call context.SaveChanges(); as this is done by the base class.

That’s all for now later i’ll post some more tips and tricks on how to get most out of database initializers.

A single file login page for asp.net

Sometimes you might feel the need to simply protect a public directory on your webserver. Using IIS gives you several options to achieve this, but today we will discuss how to enable a simple login page that requires only one file and a few entries in web.config.

First of all I used the login page created by Alen Grakalic he created a great CSS3 Login page with validation, and everything in a single file. Perfect that’s just what we needed to create a simple drop in login page for iis using asp.net.

The final result will look something like this:

 

To install it just copy the contents of the zip attached to this post to your iis webserver. Make sure that your application pool is configured to run under asp.net 4.0 (will later at a web.config file for asp.net 2.0 support).

To edit the user accounts open the web.config file and find the credentials section, where you can edit the user accounts that are allowed to log in. Pay special atention to this section in web.config:

<system.webServer>
    <validation validateIntegratedModeConfiguration="false" />
    <modules runAllManagedModulesForAllRequests="true"></modules>
  </system.webServer>

It makes sure that if you have files in the public directory that are not handled by asp.net (e.g. html/text/image files) are protected as well.

This section is also quite important as it denies anonymous users access to your content:

<authorization>
   <deny users="?" />
</authorization>

With the proper configuration this solution is secure, and it’s not easy to circumvent the login mechanism. The login logic is provided by asp.net, and therefore has been thoroughly tested by Microsoft. Of course this is just an example and I’m in no way responsible for the security of your content.

For a demo go to:

http://demos.michaelwullink.com/login/login.aspx

You can login with the accounts:

username: user1

password: secret

username: admin

password: secret#23

SinglePageLogin

Looking for a more complete user management solution?

Check out the Simple User Account Management Module

i18n globalization with gettext and asp.net mvc

The most common approach to use when internationalizing asp.net mvc applications is to use the .Net frameworks built in resource system. Another viable option is to use the i18n standard format that’s provided by GNU gettext, in this post we’ll try to set up an ASP.Net MVC application that uses gettext.

The benefit of using gettext is that it’s much easier to manage the translation files then it is with the asp.net globalization framework. Next to that the i18n standard is much more common in the opensource world then the .Net resource files, and many good editors exist to simplify the translation of applications.

Daniel Crenna has created an asp.net mvc implementation of i18n at github. By the end of this blog post we’ll have a sample application that successfully utilizes the i18n framework Daniel wrote.

First we’ll have to create a new asp.net mvc application by using:

File > New Project > Web > Asp.Net MVC 3 Web Application (note: make sure to choose asp.net mvc 3).

Choose the project name you like and in the following dialog choose the Internet application project template with the Razor view engine. (see screenshot)

At the time of writing a working nuget package was unavailable, so we’ll do the installation manually (which comes in handy in case you decided not to like nuget). Next to the unavailability of a nuget package, we’ll use a slightly patched version of Daniel’s library that contains a few bug fixes and optimizations. Our version excludes the obj folder from the gettext parser and fixes a bug with obsolete translations in the .po files (see bugreport at github and bugreport here).

Download the patched version here from github, and extract it in the solution folder under Librariesi18n.

Next we need to build the i18n libraries before we can use them, the easiest way to do that is to add the libraries to to our current solution. In Visual Studio choose File > Add > Existing project, and add both the i18n project, and the i18n.Postbuild project to the solution. Next add references to both these projects in our asp.net mvc project (right click project > add reference > projects tab > select both projects). The final result should look something like this:

Now that we referenced the i18n libraries there are three more things we need to do:

  1. Copy the gettext directory to the bin folder which is needed by the postbuild script.
  2. Modify the build properties of the mvc project to parse the .cshtml files for internationalized strings using i18n.PostBuild
  3. Create a basepage that makes it easy to call the translation method in the mvc views, and register that basepage in web.config.

Firstly copy the gettext folder from Internationalization.SampleWebsiteLibrariesi18ntoolsgettext-0.14.4 to Internationalization.SampleWebsiteInternationalization.SampleWebsitebingettext

After that right click the mvc project and choose properties, navigate to the Build events tab, and in the “Post-build event command line:” add

"$(TargetDir)i18n.PostBuild.exe" "$(ProjectDir)"

So that it looks like this:

Verify that your project still builds and that the output window doesn’t display any errors and continue the setup.

If you build a folder named locale will automatically be created in your mvc projects root folder, this folder contains all translation files which will automatically be generated by the i18n.PostBuild executable after building the project.

The third and last step is to add a new base page to your mvc project which will facilitate the easy access of your translation method, which will be accessible as @(“string to be translated”)_.

Under the models folder create a new file TranslatedWebViewPage.cs which looks like this:

namespace Internationalization.SampleWebsite.Models
{
    using System;
    using System.Collections.Generic;
    using System.Globalization;
    using System.Linq;
    using System.Web;
    using System.Web.Mvc;
    using i18n;

    // For non-strongly typed view pages
    public abstract class TranslatedWebViewPage : WebViewPage, ILocalizing
    {
        private ILocalizingService service = new LocalizingService();

        public IHtmlString _(string text)
        {
            // If the session doesn't contain the users selected language,
            // default to the processes current culture (can be defined
            //in web.config to a specific language,
            // or to be the users browser language see below)
            // <system.web>
            // <globalization culture="auto" uiCulture="auto" />
            // <globalization culture="en-US" uiCulture="en-US" />
            string[] languages;
            if (Session["CurrentLanguage"] == null)
            {
                languages = new string[] { CultureInfo.CurrentCulture.Name };
            }
            else
            {
                // this can ofcourse be switched to for example cookies for
                // persistence that lasts longer then the lifetime of the session
                languages = new string[] { Session["CurrentLanguage"].ToString() };
            }

            return new HtmlString(this.service.GetText(text, languages));
        }
    }

    // For strongly typed view pages
    public abstract class TranslatedWebViewPage<T> : WebViewPage<T>, ILocalizing
    {
        private ILocalizingService service = new LocalizingService();

        public IHtmlString _(string text)
        {
            // If the session doesn't contain the users selected language,
            // default to the processes current culture (can be defined
            //in web.config to a specific language,
            // or to be the users browser language see below)
            // <system.web>
            // <globalization culture="auto" uiCulture="auto" />
            // <globalization culture="en-US" uiCulture="en-US" />
            string[] languages;
            if (Session["CurrentLanguage"] == null)
            {
                languages = new string[] { CultureInfo.CurrentCulture.Name };
            }
            else
            {
                // this can ofcourse be switched to for example cookies for
                // persistence that lasts longer then the lifetime of the session
                languages = new string[] { Session["CurrentLanguage"].ToString() };
            }

            return new HtmlString(this.service.GetText(text, languages));
        }
    }
}

Now in web.config under the Views folder we need to change the basepage that is used edit the following section:

<system.web.webPages.razor>
  <pages pageBaseType="Internationalization.SampleWebsite.Models.TranslatedWebViewPage">

Note:This is the web.config under the views folder not the one in the root

Now that this is done we are almost there, under the locale folder add new folders for the languages that you want to translate for example:

The i18n library will first try to get a translation for the most specific locale e.g. en-US if that translation doesn’t exist it will try to use en and if that translation is unavailable as well it will use the text in the translation function call: @(“text to be translated”)_

Now that we have created the locale folders it’s time to add the most important element, the translation strings in the Views.

Open the index view under the home controller and replace

“To learn more about ASP.NET MVC visit <a href=”http://asp.net/mvc&#8221; title=”ASP.NET MVC Website”>http://asp.net/mvc</a>.&#8221;

With

@_("To learn more about ASP.NET MVC")

This translatable text will now be available in the .po files under the locale folder, and can be translated to any of the languages specified in the locale folder.

The easiest way to edit the .po files is using poeditor, I hope that this post was useful and if you have any questions or suggestions please add them in the comments.

Attached to this post you can find the sample website, as well as here on github. You can download the patched version of the i18n library from github as well.

Internationalization.SampleWebsite.zip