Dapper

Dapper

I helped work on a side project to help introduce new people to Azure here (basically how to take a web app + API app + SQL DB and get it into the cloud): https://github.com/catenn/ToDoList

I am interested in Dapper because it's a very fast, lightweight micro-ORM.  It makes life a lot easier for coding.  While it doesn't have the extensive features of Entity Framework, it's quick and easy to learn and to implement.

I wanted to show you all the difference in a file using ADO.NET, then I converted the full file to use Dapper. Both of these are working, if you want to download the project linked above you can try out each and run them through the debugger to see how they work. Also you can get Dapper here: https://github.com/StackExchange/Dapper

ADO.NET version 

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using ToDoListDataAPI.Models;
using System.Configuration;
using System.Data;

namespace ToDoListDataAPI.Repo
{
    public class ToDoListRepo
    {

        public IEnumerable<todoitem> GetTodoItems()
        {
            var data = new List<todoitem>();
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["todoItems"].ConnectionString;

                using (var command = new SqlCommand("GetItems", conn)
                {
                    CommandType = CommandType.StoredProcedure
                })
                {
                    conn.Open();

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            data.Add(new ToDoItem()
                            {
                                ID = reader.GetInt32(reader.GetOrdinal("ID")),
                                Description = reader.GetString(reader.GetOrdinal("Description")),
                            });
                        }

                    }
                }
            }

            return data;
        }

        internal void EditDescription(ToDoItem toDoItem)
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["todoItems"].ConnectionString;

                using (var command = new SqlCommand("UpdateItem", conn)
                {
                    CommandType = CommandType.StoredProcedure
                })
                {
                    command.Parameters.Add(new SqlParameter("@id", toDoItem.ID));
                    command.Parameters.Add(new SqlParameter("@description", toDoItem.Description));

                    conn.Open();

                    command.ExecuteNonQuery();
                }
            }
        }

        internal void DeleteById(int id)
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["todoItems"].ConnectionString;

                using (var command = new SqlCommand("DeleteItemById", conn)
                {
                    CommandType = CommandType.StoredProcedure
                })
                {
                    command.Parameters.Add(new SqlParameter("@id", id));

                    conn.Open();

                    command.ExecuteNonQuery();
                }
            }
        }

        public ToDoItem GetTodoItemById(int id)
        {
            ToDoItem toDoItem = new ToDoItem();

            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["todoItems"].ConnectionString;

                using (var command = new SqlCommand("GetItemById", conn)
                {
                    CommandType = CommandType.StoredProcedure
                })
                {
                    command.Parameters.Add(new SqlParameter("@id", id));
                    conn.Open();

                    using (SqlDataReader reader = command.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            toDoItem.ID = reader.GetInt32(reader.GetOrdinal("ID"));
                            toDoItem.Description = reader.GetString(reader.GetOrdinal("Description"));
                        }

                    }
                }
            }

            return toDoItem;
        }

        public void InsertTodoItem(ToDoItem toDoItem)
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["todoItems"].ConnectionString;

                using (var command = new SqlCommand("InsertItem", conn)
                {
                    CommandType = CommandType.StoredProcedure
                })
                {
                    command.Parameters.Add(new SqlParameter("@description", toDoItem.Description));

                    conn.Open();

                    var id = command.ExecuteScalar();
                }
            }

        }
    }
}

Dapper Version

using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Web;
using ToDoListDataAPI.Models;
using System.Configuration;
using System.Data;
using Dapper;

namespace ToDoListDataAPI.Repo
{
    public class ToDoListRepo
    {
        public T DbConnection<t>(Func<idbconnection t=""> getData)
        {
            using (SqlConnection conn = new SqlConnection())
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["todoItems"].ConnectionString;
                conn.Open();

                return getData(conn);
            }
        }

        public IEnumerable<todoitem> GetTodoItems()
        {
            return DbConnection(conn =&gt;
                conn.Query<todoitem>("dbo.GetItems", commandType: CommandType.StoredProcedure).ToList());
        }


        public void EditDescription(ToDoItem toDoItem)
        {
            DbConnection(conn =&gt;
                conn.Execute("dbo.UpdateItem", toDoItem, commandType: CommandType.StoredProcedure));
        }

        public void DeleteById(int id)
        {
            DbConnection(conn =&gt;
                conn.Execute("dbo.DeleteItemById", new { id }, commandType: CommandType.StoredProcedure));
        }

        public ToDoItem GetTodoItemById(int id)
        {
            return DbConnection(conn =&gt;
                conn.QuerySingle<todoitem>("dbo.GetItemById", new { id }, commandType: CommandType.StoredProcedure));
        }

        public void InsertTodoItem(ToDoItem toDoItem)
        {
            int id;
            DbConnection(conn =&gt;
                id = conn.ExecuteScalar<int>("dbo.InsertItem", new {toDoItem.Description}, commandType: CommandType.StoredProcedure));
        }
    }
}