Dapper is a micro-ORM. It helps you to create a strongly typed result by providing you an easy way to work with your database. It is simple to use, it does simple stuff, essentially extends your IDBConnection interface. So if you are looking for a way to query your data real fast (yes, this is the fastest it gets barring pure ADO.net), you should checkout Dapper.net. It is developed by some smart folks over at Stackoverflow.com ! (Another good reason to try it out!)

Installing Dapper

It is simple to install Dapper, you could simply use Nuget Package Manager to add Dapper to your project. From Visual Studio, Click on Tools > Library Package Manager > Package Manager Console
Type in Install-Pacakage Dapper and hit Enter

In order to demonstrate Dapper, we will use the standard Microsoft sample database NorthWind.

In NorthWind database Customers table represent customers and Orders table represent Orders placed by all the customers.

Here are some POCOs we will use:

    internal class Customer
    {
        public string CustomerId { get; set; }
        public string CompanyName { get; set; }
        public string ContactName { get; set; }
        public IEnumerable<Order> Orders { get; set; }   
    }

    internal class Order
    {
        public int OrderId { get; set; }
        public DateTime OrderDate { get; set; }
    }

A useful helper class that would get us a valid, opened SqlConnection object

    internal static class SqlHelper
    {
        public static SqlConnection GetOpenConnection()
        {
            var connectionString = ConfigurationManager.ConnectionStrings["NorthWind"];
            var connection = new SqlConnection(connectionString.ToString());
            connection.Open();
            return connection;
        }
    }

And a Data Access class that actually does some work !!

    internal class CustomerDao
    {
        public IEnumerable<Customer> GetAll()
        {

            const string sql = "Select * from Customers";
            using (var connection = SqlHelper.GetOpenConnection())
            {
                return connection.Query<Customer>(sql);
            }
        }

        public IEnumerable<Customer> GetById(string customerId)
        {

            const string sql = "Select * from Customers where CustomerId = @id";
            using (var connection = SqlHelper.GetOpenConnection())
            {
                return connection.Query<Customer>(sql, new { Id = customerId });
            }
        } 

        public IEnumerable<Order> GetOrders(string customerId)
        {
            const string sql = @"select o.Orderid, o.OrderDate from Customers c inner join Orders o 
                                 on c.CustomerId = o.CustomerId and c.CustomerId = @Id";

            using (var connection = SqlHelper.GetOpenConnection())
            {
                return connection.Query<Order>(sql, new { Id = customerId });
            }
        }


    }

Above class illustrates some very a straight select and some simple joins. You can use the same strategy to run your insert and update queries. Dapper also supports multi-mappings where you can auto map your aggregates and multiple result sets also. For more visit Dapper page on Google code: http://code.google.com/p/dapper-dot-net/

Here is some tests that actually tests the code above:

    [TestFixture]
    public class DapperTest
    {
        private CustomerDao _customerDao;
        [TestFixtureSetUp]
        public void TestFixtureSetUp()
        {
            _customerDao = new CustomerDao();
        }
        [Test]
        public void CreateConnection_ReturnsNotNull()
        {
            var connection = SqlHelper.GetOpenConnection();
            Assert.That(connection, Is.Not.Null);
        }
        [Test]
        public void GetAllCustomers_Returns_Records()
        {
            var allCustomers = _customerDao.GetAll();
            Assert.That(allCustomers.Count(), Is.Not.EqualTo(0));
        }

        [Test] 
        public void GetCustomers_With_Where_Clause()
        {
            var testId = "BERGS";
            var allCustomers = _customerDao.GetById(testId);
            Assert.That(allCustomers.Count(), Is.Not.EqualTo(0));
        }

        [Test]
        public void GetOrders_For_Customer()
        {
            const string testId = "BERGS";
            var orders  = _customerDao.GetOrders(testId);
            Assert.That(orders.Count(), Is.Not.EqualTo(0));
        }



    }

Massive and Peta Poco are two other micro ORMs that competes with Dapper. You can find Massive here => https://github.com/robconery/massive and Peta Poco here => http://www.toptensoftware.com/petapoco/

(I haven’t tried Massive, but have played with Peta Poco. I find auto mapping features are better in Peta Poco compared to Dapper)

I hope you enjoyed this post! Happy Programming!
Cheers!