Dapper is not a
new concept. I implemented this in one of my recent project and I found
it interesting so thought I should share with others also who are not using this.
In this article you will get basic explanation of how to use
dapper in our projects.
The followings are some benefits of Dapper:
- Good performance.
- Directly bind database properties with c# objects.
- Less number of lines of code for same implementation.
- Support for store procedures and inline SQL queries
- Well managed connection object.
Example
First, we have to
define connection.
using (IDbConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["DBConnString"].ConnectionString)
{
}
And in web config you define the connection string
<add name="DBConnString" providerName="System.Data.SqlClient" connectionString="Data Source=anujpc/SQL2014;Initial Catalog=DapperDB;Integrated Security=True;MultipleActiveResultSets=True" />
Get List of objects
Write a select query for getting selected records from the
database table
Here is the example object class:
public class User
{
public int Id { get; set; }
public string Name { get; set; }
public string Address { get; set; }
public int RoleId { get;set }
}
And below is the code for binding the database values with
the above written object.
IEnumerable<User> userList =
conn.Query<User>(@"
SELECT *
FROM User
WHERE RoleId =
@RoleId",
new { RoleId = roleId });
For getting one record only we can use
User user = conn.Query<User>(@"
SELECT *
FROM User
WHERE Id = @Id",
new { Id = id }).FirstOrDefault();
Query is used for
getting data from database. For insert and update we can use Execute method
User user = new User();
user.Name = "Anuj";
user.Address = "Chandigarh";
user.RoleId = 1;
const string query =
"INSERT INTO
Users([Name],[Address],[RoleId]) " +
"VALUES (@Name,
@Address,@RoleId)";
int result =
conn.Execute(query, user);
Here we passed user as parameters as the property and database
columns name are same, it will map it itself.
For delete also we can use Execute method
const string query = "DELETE FROM User
" +
"WHERE Id =
@Id";
return
conn.Execute(query, new {Id = id });
So here I have covered basic CRUD operations using Dapper.
The main purpose of this article is to introduce you all with Dapper classes and
give you introduction for methods.
