Perform CRUD on a database using Dapper

Dapper is a lightweight and agile ORM package for the .NET development platform. It allows executing SQL scripts and stored procedures, and Dapper maps the results directly to strongly typed objects, enabling developers to focus more on other aspects of their projects.

Basic Queries

Prepare the connection string _connectionString and the Product object.

string _connectionString = "Server=localhost;Initial Catalog=Northwind;Integrated Security=true;TrustServerCertificate=True";

/// <summary>
/// Product Object
/// </summary>
class Product
{
    public int ProductID { get; set; }
    public int CategoryID { get; set; }
    public string ProductName { get; set; }
}

A short piece of code

using (var conn = new SqlConnection(_connectionString))
{
    var sql = "SELECT * FROM Products";
    var products = conn.Query<Product>(sql).ToList();
}

Query with parameters

using (var conn = new SqlConnection(_connectionString))
{
    var sql = "SELECT * FROM Products WHERE CategoryID = @categoryID";

    var products = conn.Query<Product>(sql, new { categroyID = 1 }).ToList();
}

Single Record Query

The following methods can be used to query a single record, depending on how the developer wants to handle the retrieved data

• QuerySingle
  Expects to return exactly one record, throws an error if the result is zero or multiple records.

• QuerySingleOrDefault
  Expects to return a single record or null, throws an error if multiple result are returned.

• QueryFirst
  Returns the first record of the query result, throws an error if there are no records.

• QueryFirstOrDefault
  Returns the first record of the query result or null, this method doesn't throw an error.

• QuerySingle<T>、QuerySingleOrDefault<T>、QueryFirst<T>、QueryFirstOrDefault<T>
  These methods function the same as the first four but return a strongly typed object T instead of a dynamic object.
  

using (var conn = new SqlConnection(_connectionString))
{
    var sql = "SELECT * FROM Products WHERE CategoryID = @categoryID";
    var obj = new { categoryID = 1 };
    var products = conn.QuerySingle<Product>(sql, obj).ToList();
}

Multiple Records Query

Querying multiple records is simple, with only two methods: Query and Query<T>.

using (var conn = new SqlConnection(_connectionString))
{
    var sql = "SELECT * FROM Products";

    // return T objects
    var products = conn.Query<Product>(sql).ToList();

    // return dynamic objects
    var products = conn.Query(sql).ToList();
}

Insert, Update and Delete

Dapper also provides a method for executing non-query operations, which is used for INSERT, UPDATE, and DELETE statements. This method returns the number of rows affected.

INSERT

var sql = @"
            INSERT INTO Products (CategoryID, ProductName) 
            VALUES (@categoryID, @productName)";

var obj = new { categoryID = 1, productName = "plus" };

// return an integer
int result = conn.Execute(sql, obj);

UPDATE

var sql = @"
            UPDATE Products SET ProductName = @productName 
            WHERE CategoryID = @categoryID";

var obj = new { categoryID = 1, productName = "Update plus" };

int result = conn.Execute(sql, obj);

DELETE

var sql = @"
            DELETE FROM Products 
            WHERE CategoryID = @categoryID AND ProductName = @productName";

var obj = new { categoryID = 1, productName = "Update plus" };

int result = conn.Execute(sql, obj);

With just a few lines of code, you can retrieve data from database and directly map it to objects for further operations in application. it's convenient, lightweight, and once you try it, you'll love it.

Job done☑️

---

Thanks for reading the article

If you like it, please don't hesitate to click heart button ❤️
or follow my GitHub I'd appreciate it.