Click here to Skip to main content
14,642,113 members
Articles » Web Development » ASP.NET » General
Tip/Trick
Posted 14 Sep 2015

Stats

37.8K views
1.3K downloads
16 bookmarked

Dapper.NET and DapperExtensions to Run Stored Procedures

Rate this:
4.81 (12 votes)
Please Sign up or sign in to vote.
4.81 (12 votes)
14 Sep 2015CPOL
We will discuss about how we can combine Dapper.NET and DapperExtensions to configure DAL to execute normal queries as well as 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:

// 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.

//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:

//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.

//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.

Your suggestions, questions or queries are most welcome.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Vijaykumar Vadnal
Software Developer (Senior)
India India
No Biography provided

Comments and Discussions

 
QuestionVery Nice Pin
kleberapsilva25-Nov-15 9:36
Memberkleberapsilva25-Nov-15 9:36 
GeneralMy vote of 4 Pin
Abhishek Kumar Goswami17-Sep-15 1:29
professionalAbhishek Kumar Goswami17-Sep-15 1:29 
Good Start V.J.
GeneralRe: My vote of 4 Pin
Vijaykumar Vadnal17-Sep-15 19:51
MemberVijaykumar Vadnal17-Sep-15 19:51 
QuestionNice article Pin
wiredeye14-Sep-15 5:43
Memberwiredeye14-Sep-15 5:43 
AnswerRe: Nice article Pin
Vijaykumar Vadnal14-Sep-15 21:25
MemberVijaykumar Vadnal14-Sep-15 21:25 
AnswerRe: Nice article Pin
Vijaykumar Vadnal14-Sep-15 23:30
MemberVijaykumar Vadnal14-Sep-15 23:30 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.