Skip to content

CategoryASP.NET (Web Forms)

Dapper – A C# Micro ORM Library Makes Data Access a Snap – Part 1

I’ve recently gotten a chance to try out Dapper – a micro ORM library by Sam Saffron, which originally spawned from StackOverflow’s codeset. If you’re looking for an extremely light-weight library to access your SQL Server, I highly recommend it. It focuses more on convention than configuration. It also does not have the overhead that EntityFramework or NHibernate have, and it emphasizes speed and ease-of-use.

Dapper is a collection of Extension Methods that enhance the Connection object (or rather extends the IDbConnection interface). It also embraces the use of inline SQL in your code, which is useful when retrieving sets that are less trivial. With its buddy, Dapper-Extensions by Thad Smith, it makes DB interaction even easier.

Both of these libraries can be downloaded from NuGet, and you’ll need them to run the following code.

There’s really just one thing that I found irritating about the Dapper project: the documentation. While there is documentation found at its Google Code home page, I find it very lacking.

Anywhoot, to get started first we need to create our table. Once we have that and add some data, we’ll use the Dapper to extract the data and map to objects.

-- Customer Schema
CREATE TABLE [dbo].[Customer](
[CustomerID] [uniqueidentifier] NOT NULL,
[FirstName] [nvarchar](50) NULL,
[LastName] [nvarchar](50) NULL,
[Age] [int] NULL,
[IsAllowed] [bit] NULL,
[DateRegistered] [datetime] NULL,
[AdditionalDetails] [nvarchar](max) NULL,
[CustomerID] ASC

ALTER TABLE [dbo].[Customer] ADD  CONSTRAINT [DF_Customer_CustomerID]  DEFAULT (newid()) FOR [CustomerID]

-- Data
INSERT INTO [dbo].[Customer]([CustomerID], [FirstName], [LastName], [Age], [IsAllowed], [DateRegistered], [AdditionalDetails])
  SELECT N'5458dff8-cea2-4bdb-9431-1dea56f109f8', N'Bruce', N'Wayne', 31, 1, '20080604 08:13:44.000', N'I am Batman' UNION ALL
  SELECT N'1767bc57-af03-4b17-891f-2aa9af244180', N'Peter', N'Parker', 25, 1, '20120103 12:22:28.000', N'I am Spider-Man' UNION ALL
  SELECT N'dc2bf42d-045b-4189-bd6b-8bf1bf120291', N'Eddie', N'Brock', 34, 0, '20040123 02:45:41.000', N'We are Venom'

OK, so this is what we have so far:

Because Dapper focuses more on convention over configuration, it’s important to note that when you create your class:

  • The name of your class must match the name of the table, otherwise an exception is thrown.
  • All column names must match all class property names, otherwise an exception is thrown.
  • If a column is not used (in NOT written out in the SELECT statement) and the class property exists, it will set to NULL or assign a default value – more on this later.

Now that we’re aware of these points, let’s see the code to get started:

using System;
using Dapper;
using System.Data.SqlClient;

class Customer
  public Guid CustomerID { get; set; }
  public string FirstName { get; set; }
  public string LastName { get; set; }
  public int Age { get; set; }
  public bool IsAllowed { get; set; }
  public DateTime DateRegistered { get; set; }
  public string AdditionalDetails { get; set; }

class Program
  private static void Main()

    using (SqlConnection conn = new SqlConnection("Data Source=NARUTO;Initial Catalog=TESTDATABASE;Integrated Security=True"))

      // Put dapper code here.



OK, so there’s nothing out of the ordinary here. We’re creating the class we want to map our data to, creating a Connection object, opening, closing, and then disposing (via using). Now let’s suck up some data within our Main() method:

private static void Main()
  using (SqlConnection conn = new SqlConnection("Data Source=NARUTO;Initial Catalog=TESTDATABASE;Integrated Security=True"))

    IEnumerable customers = conn.Query("SELECT * FROM Customer");

    foreach (Customer c in customers)
      Console.WriteLine("CustomerID: " + c.CustomerID);
      Console.WriteLine("FirstName: " + c.FirstName);
      Console.WriteLine("LastName: " + c.LastName);
      Console.WriteLine("Age: " + c.Age);
      Console.WriteLine("IsAllowed: " + c.IsAllowed);
      Console.WriteLine("DateRegistered: " + c.DateRegistered);
      Console.WriteLine("AdditionalDetails: " + c.AdditionalDetails);



As we see, the Query extension method pulls in the data based on the select statements. The Query method returns a strongly-typed list of Customer. You should see the following:

Now what if we want to get the number of records in the table? Well, because Query returns an IEnumerable, we cannot use the Count property. To do so, we convert to a List:

List customers = (List)conn.Query("SELECT * FROM Customer");

That should give us the count. To see how the data types were mapped, we can take a look at the Dapper source code, SqlMapper.cs inside the static method SqlMapper(). For reference, you can take a peek at these two sources to understand SQL Server/CLR/.NET framework data types:

For our example though, we saw that the data types were mapped:

Mismatches and Nulls

In the query that we ran, we were making the assumption that all columns in the db match all the properties in the class. What if we have a mismatch? What if we have this?

IEnumerable customers = conn.Query("SELECT LastName FROM Customer");

Notice that we’re only retrieving the LastName, while we have other properties in our Customer class. Well, this is what you would see:

So from the above, we notice that Dapper will handle Nulls by:

  • Setting strings as Nulls
  • Setting a Guid to 00000000-0000-0000-0000-000000000000
  • Setting an int to 0
  • Setting bool to false
  • Setting a DateTime to 1/1/0001 12:00:00 AM

Which will all happen when Dapper cannot find a class property to map to from the column value. Also, if you column name that DOES NOT match a property, it will throw a SqlException of “Invalid Column Name”.


What if we want to pass a parameter into our SQL? Let’s pass a parameter:

IEnumerable customers = conn.Query("SELECT * FROM Customer WHERE AGE > @Age", new { Age = 30 } );

Notice that I’m passing in an anonymous object. I could also have done:

Customer someCustomer = new Customer {
Age = 33,
FirstName = "Clark Kent"

IEnumerable customers = conn.Query("SELECT * FROM Customer WHERE AGE > @Age", someCustomer );

Which yields:

The property in someCustomer (FirstName = “Clark Kent”) is silently ignored because it’s not used in the SELECT statement.

When the above is run, SQL Server Profiler shows that the following is run:

exec sp_executesql N'SELECT * FROM Customer WHERE AGE > @Age',N'@Age int',@Age=33

So we know it’s parameterized.

To call a stored procedure and map it to objects, we do, assuming we have the following sproc:

@IsAllowed BIT

SELECT * FROM Customer
WHERE IsAllowed = @IsAllowed


We do:

// The second @ token does not have to be named "@IsAllowed" and can be named anything as
// long as the C# property in the anonymous object is named the same thing.
IEnumerable customers = conn.Query("GetCustomers @IsAllowed = @IsAllowed", new { IsAllowed = 33 } );

Also accepted:

IEnumerable customers = conn.Query("GetCustomers @IsAllowed = @Allow", new { Allow = 33 } );

Or you can be more explicit by specifying the command type (StoredProcedure):

IEnumerable customers = conn.Query("GetCustomers", new { IsAllowed = 33 }, commandType: CommandType.StoredProcedure );

Or writing it a more secure way:

DynamicParameters parameters = new DynamicParameters();
parameters.Add("@IsAllowed", 'f', dbType:DbType.Int32);

IEnumerable customers = conn.Query("GetCustomers", parameters, commandType: CommandType.StoredProcedure );

To insert data, we do as follows:

Customer customer = new Customer{
  FirstName = "Mazinger",
  LastName = "Z",
  Age = 40,
  IsAllowed = true,
  DateRegistered = DateTime.Now,
  AdditionalDetails = "Metallic defender"

conn.Execute(@"INSERT INTO Customer
                                  ( FirstName ,
                                  LastName ,
                                  Age ,
                                  IsAllowed ,
                                  DateRegistered ,

                   VALUES  ( @FirstName ,
                                 @LastName ,
                                 @Age ,
                                 @IsAllowed ,
                                 @DateRegistered ,
                 , customer);

This ends part 1 of the tutorial. I will post the 2nd part soon.

Model Binding and Validation ASP.NET MVC Sample Project

I wanted to share this sample project I put together that demonstrates model-binding and validation of several form components, including complex ones such as dropdowns, checkboxes, and radio buttons. There are several aspects to think about when submitting a form:

  1. HTML form value has to map to the correct C# property/object via Model Binding.
  2. Value submitted has to be properly validated based on Attributes or logic in controller.
  3. If the form has invalid data, the form has to refresh itself and be populated with all the values the user previous attempted to submit.
  4. The appropriate error messages have to be displayed next. The error messages can be part of the set of attributes above the property or handled via ViewBag/ViewData object.

The following example has all that. It also uses a ViewModel (BankAccount.cs) to piece together the main Business Domain Models (e.g. Person).

Download the Sample Project

Here’s our classes in the project:

Error Logging in ASP.NET MVC with ELMAH

Error logging in ASP.NET MVC is extremely easy with ELMAH (Error Logging Modules and Handlers). I suggest you get it from NuGet:

Once you have it installed, it can instantly start recording errors, even Yellow Screens of Deaths. Here’s a list of errors from Elmah:

So what happens, is after you set it up via NuGet, you can get started viewing issues (if there are any) via elmah.axd. So in my case:


Now, if you’re using Chrome, and you view this page, Chrome will try to request for favicon.ico, which is not delivered from this web page. So, you have to write a filter in your web.config to ignore the .ico file:

        <regex binding="Context.Request.ServerVariables['URL']" pattern="/favicon\.ico(\z|\?)" />     


Or if you want to completely ignore 404 errors:

        <equal binding="HttpStatusCode" value="404" type="Int32" />     

By default, ELMAH stores error details in memory, so every time the app pool gets restarted, it gets flushed. To dump the data to SQL Server instead, update your web.config:

    <errorLog type="Elmah.SqlErrorLog, Elmah" connectionStringName="ElmahConnectionString"/>

The Elmah connection string is pretty straightforward:

<add name="ElmahConnectionString " connectionString="Initial Catalog=my_database;data source=.\SQLEXPRESS;Integrated Security=SSPI;" providerName="System.Data.SqlClient" />

Then you go to the Elmah project page and run the SQL Server script via SSMS – MAKE SURE YOU RUN ‘USE ‘ first!

It then creates a table:

Companies Who Use ASP.NET

During a job hunt, I compiled a list of companies that use ASP.NET. Most of them, without a surprise, are big corporate ones. Also, not all of them use ASP.NET exclusively for their entire site/business. Many of these sites have subsites for their many divisions, so a mix of technologies are used.

I’m going to keep updating as I find more.

Microsoft Amazon