First of all, lets undersand what dapper is?According to wikipedia, Dapper is an object-relational mapping(ORM) product for the microsoft .NET platform: it provides a framework for mapping an object-oriented domain model to a traditional relational database. Its purpose is to relieve the developer from a significant portion of relational data persistence-related programming tasks. Dapper is free as open source software that is distributed under dual license, either the Apache Licence 2.0 or the MIT Licence.
Now, lets look at simple implementation of Dapper in asp.net mvc. To do so first of all create a ConnectionFactory class to connect to database.
public class ConnectionFactory
{
//private readonly string connectionString = ConfigurationManager.ConnectionStrings["myCon"].ConnectionString;
public IDbConnection GetConnection
{
get
{
var factory = DbProviderFactories.GetFactory("System.Data.SqlClient");
var conn = factory.CreateConnection();
string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString as string;
conn.ConnectionString = connectionString;
conn.Open();
return conn;
}
}
#region IDisposable Support
private bool disposedValue = false; // To detect redundant calls
protected virtual void Dispose(bool disposing)
{
if (!disposedValue)
{
if (disposing)
{
// TODO: dispose managed state (managed objects).
} disposedValue = true;
}
}
public void Dispose()
{
Dispose(true);
}
#endregion
}
Now lets create a simple Repository named AuthorRepository and use dapper to connect to database.
public class Author
{
public int Id { get; set; }
public string Name { get; set; }
public int OrderId { get; set; }
public string AdminName { get; set; }
}
public class AuthorRepository
{
ConnectionFactory _connectionFactory;
public AuthorRepository()
{
_connectionFactory = new ConnectionFactory();
}
public void InsertAuthor(Author author)
{
using (var conn=_connectionFactory.GetConnection)
{
var query = "usp_Author_Insert";
var param = new DynamicParameters();
param.Add("@Name", author.Name);
param.Add("@OrderId", author.OrderId);
param.Add("@AdminName", author.AdminName);
conn.Query(query, param, commandType: CommandType.StoredProcedure);
}
}
public void UpdateAuthor(Author author)
{
using (var conn = _connectionFactory.GetConnection)
{
var query = "usp_Author_Update";
var param = new DynamicParameters();
param.Add("@Id", author.Id);
param.Add("@Name", author.Name);
param.Add("@OrderId", author.OrderId);
param.Add("@AdminName", author.AdminName);
conn.Query(query, param, commandType: CommandType.StoredProcedure);
}
}
public IEnumerable<Author> GetAllAuthors()
{
using(var conn = _connectionFactory.GetConnection)
{
var query = "usp_Author_Select";
var param = new DynamicParameters();
return conn.Query<Author>(query, param, commandType: CommandType.StoredProcedure);
}
}
public Author GetAuthorById(int id)
{
using (var conn = _connectionFactory.GetConnection)
{
var query = "";
var param = new DynamicParameters();
param.Add("@Id", id);
return conn.Query<Author>(query, param, commandType: CommandType.StoredProcedure).FirstOrDefault();
}
}
public void DeleteAuthor(int id)
{
using (var conn = _connectionFactory.GetConnection)
{
var query = "usp_Author_Delete";
var param = new DynamicParameters();
param.Add("@Id", id);
conn.Query<Author>(query, param, commandType: CommandType.StoredProcedure);
}
}
}
Now, just compare dapper with ado.net. In ado.net we had to map all the data to he model whereas ado.net does all of this for us. Here are some of the advantages that dapper brings up with us.
1) make it trivially easy to correctly parameterize queries
2) make it trivially easy to execute queries (scalar, multi-rows, multi-grids, and no-results)
3) make it trivially easy to turn results into objects
4) very efficiently and quickly
Now, in some cases we may need to use multi mapping. i.e we may need to split the result into different models.
var list = conn.Query<Model1, Model2, Model1>(query, (model1, model2) =>
{
model1.model2= model2;
return model1;
}, param, commandType: CommandType.StoredProcedure);
Similarly, in some cases we may need to map multi-result data into a model. e.g:
Bill bill = new Bill();
using (var multi = conn.QueryMultiple(query, param, commandType: CommandType.StoredProcedure))
{
bill = multi.Read<Bill, Student, Bill>((billy, student) =>
{
billy.Student = student;
return billy;
}).FirstOrDefault();
bill.Particulars = multi.Read<BillParticulars>();
bill.DiscountParticulars = multi.Read<BillParticulars>();
}
