Tuesday, October 16, 2012

ADO.NET & Dapper.NET using C# & ASP.NET

I posted my first question on StackOverflow the othe dayr. A question regarding best practise for high speed raw data access (Follow the link for the full blown version). In short however it centred around whether there was a faster way to access the data than executing a stored procedure and loading a DataTable with the ExecuteReader results.

At first most respondents (some with >5k scores) said that this was generally the fastest way and that everything I was doing was correct. However Marc Gravell  (A Microsoft MVP and Moderator of StackOverflow) kindly posted an awesome response! In order to increase the performance of StackOverflow he and Sam Saffron had developed a very small .NET ORM called Dapper.NET .

Essentially what this allows you to do is fire inline SQL or stored procedures from a IDBConnection type and return statically typed results! This is awesome if you just want to get data really fast and don't require the overhead of a DataTable that has functionality to manage relationships and track changes and such forth.

In the jQuery AJAX, Web 2+, ASP.NET MVC world this is more often than not what you're trying to acheive, very fast smallish selects/inserts and updates and whilst Entity Framework, LLBLGEN, NHibernate etc have their advantages sometimes they are waaaaaaay more than you need and can add unecessary complexity.  

Because Dapper.NET removes all this complex relationship management overhead it can outperform most data related operations compared to its heavier siblings. In short, its about as fast as you can get within the framework! Heres a small sample of code I wrote to add and return users from a database - this is ALL the code I had to write and notice the statically typed objects Im using - no nasty DataTable["SomeColumnName"] references - just POCO ;o)

Steps for setup:
Create a Blank Web Application (I chose WebForms for ease but MVC is equally applicable)
Create a Db
Add Db Connection String to Web.Config
Create UserInsert Stored Procedure
Create UserSelectAll Stored Procedure

Default.aspx.cs code:

using Dapper;
using System;

using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace DapperSandbox
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (IsPostBack) return;
            BindUserResults();
        }

        public class DapperUser
        {
            public Guid Id { get; set; }
            public string Firstname { get; set; }
            public string Surname { get; set; }
            public string Email { get; set; }
        }

        private void BindUserResults()
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
            {
                connection.Open();
                var rows = connection.Query("UserSelectAll", commandType: CommandType.StoredProcedure).ToList(); 
                ResultsView.DataSource = rows;
                ResultsView.DataBind();
            }
        }

        public void Add_Click(object sender, EventArgs e)
        {
            using (SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["default"].ToString()))
            {
                connection.Open();
                connection.Execute("UserInsert", new { Id = Guid.NewGuid(), Firstname = Firstname.Text, Surname = Surname.Text, Email = Email.Text },
                    commandType: CommandType.StoredProcedure);
            }
            BindUserResults();
        }
    }
}

No comments: