Dapper.NET and DapperExtensions to Run Stored Procedures

Introduction

This is my first article on CodeProject.

Over the years, we have seen Object-Relational-Mappers (ORM) like NHibernate, Entity Framework, Dapper and Peta POCO, etc.

We are not discussing all of them in detail in my post, I am targetting to brief about Dapper.NET (Micro ORM) and DapperExtensions together to configure our DAL.

Background

If you are new to Dapp.NET, a brief discussion on Dapper.NET article has been posted here.

Using the Code

Basically, we write common code configuration when we use any of ORM as mentioned above. In the same way, I have written SqlHelper a helper class within my DAL (Data Access Layer) which is the entry point for all your DAL calls through business layer or UI layer.

Below is the code snippet which discusses about SqlHelper class implementation:

Hide Shrink

Copy Code

// C#
public static class SqlHelper
{
        public static bool Insert<T>(T parameter, string connectionString) where T : class
        {
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                sqlConnection.Insert(parameter);
                sqlConnection.Close();
                return true;
            }
        }
        public static int InsertWithReturnId<T>(T parameter, string connectionString) where T : class
        {
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                var recordId = sqlConnection.Insert(parameter);
                sqlConnection.Close();
                return recordId;
            }
        }
        public static bool Update<T>(T parameter, string connectionString) where T : class
        {
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                sqlConnection.Update(parameter);
                sqlConnection.Close();
                return true;
            }
        }
        public static IList<T> GetAll<T>(string connectionString) where T : class
        {
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                var result = sqlConnection.GetList<T>();
                sqlConnection.Close();
                return result.ToList();
            }
        }
        public static T Find<T>(PredicateGroup predicate, string connectionString) where T : class
        {
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                var result = sqlConnection.GetList<T>(predicate).FirstOrDefault();
                sqlConnection.Close();
                return result;
            }
        }
        public static bool Delete<T>(PredicateGroup predicate, 
			string connectionString) where T : class
        {
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                sqlConnection.Delete<T>(predicate);
                sqlConnection.Close();
                return true;
            }
        }
        public static IEnumerable<T> QuerySP<T>(string storedProcedure, dynamic param = null,
            dynamic outParam = null, SqlTransaction transaction = null,
            bool buffered = true, int? commandTimeout = null, 
			string connectionString = null) where T : class
        {
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            var output = connection.Query<T>(storedProcedure, param: (object)param, 
            transaction: transaction, buffered: buffered, commandTimeout: commandTimeout, 
            commandType: CommandType.StoredProcedure);
            return output;
        }
        private static void CombineParameters(ref dynamic param, dynamic outParam = null)
        {
            if (outParam != null)
            {
                if (param != null)
                {
                    param = new DynamicParameters(param);
                    ((DynamicParameters)param).AddDynamicParams(outParam);
                }
                else
                {
                    param = outParam;
                }
            }
        }
        private static int ConnectionTimeout { get; set; }
}

Below is the key method to call or execute stored procedures using Dapp.NET and DapperExtension.

Hide Copy Code

//C#
public static IEnumerable<T> QuerySP<T>(string storedProcedure, dynamic param = null,
            dynamic outParam = null, SqlTransaction transaction = null,
            bool buffered = true, int? commandTimeout = null, 
				string connectionString = null) where T : class
        {
            SqlConnection connection = new SqlConnection(connectionString);
            connection.Open();
            var output = connection.Query<T>(storedProcedure, param: (object)param, 
		transaction: transaction, buffered: buffered, commandTimeout: commandTimeout, 
		commandType: CommandType.StoredProcedure);
            return output;
        }

Other methods in the above code snippet are being configured to map your CRUD operations like:

Hide Copy Code

//C#

public static bool Insert<T>(T parameter, string connectionString) where T : class
        {
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                sqlConnection.Insert(parameter);
                sqlConnection.Close();
                return true;
            }
        }

This above method will insert record to the type T to database with same name as of class name.

Hide Copy Code

//C#

public static IList<T> GetAll<T>(string connectionString) where T : class
        {
            using (var sqlConnection = new SqlConnection(connectionString))
            {
                sqlConnection.Open();
                var result = sqlConnection.GetList<T>();
                sqlConnection.Close();
                return result.ToList();
            }
        }

The above code snippet returns all the records from type T from database.

Example is Attached with this Tip

The attachment contains DAL, Business, Entity and Web layer. The zip file also contains a folder called “Lib” which contains Dapper.NET and DapperExtension DLLs.

The referred database backup file is also added under “DB” folder within the same zip file.

Code which is attached is simple to understand hence, I only provided example based in “User” table. If you want, you can extend it to use multiple POCOs by adding them to the same source code or replicating it in your project.

Points of Interest

Dapper.Net runs on auto mapping system that means you need to add your data entity with the same name as of your data table which is created in database and also needs to add same property names those are of table columns in database.

Summary

Dapper.NET in combination with DapperExtension configuration seems a bit complex but by referring to this example, you won’t feel it.

The tools that I have used are Visual Studio 2013 Web Express, SQL Server 2008 R2 Express. It worked really well for me.

Bhavin Patel