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"; /// /// Product Object /// 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(sql).ToList(); } Query with parameters using (var conn = new SqlConnection(_connectionString)) { var sql = "SELECT * FROM Products WHERE CategoryID = @categoryID"; var products = conn.Query(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、QuerySingleOrDefault、QueryFirst、QueryFirstOrDefault 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(sql, obj).ToList(); } Multiple Records Query Querying multiple records is simple, with only two methods: Query and Query. using (var conn = new SqlConnection(_connectionString)) { var sql = "SELECT * FROM Products"; // return T objects var products = conn.Query(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.

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

/// 

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

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

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

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

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();
}

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();
}

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);

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

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

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

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);

Related Posts

Warehouse Woes

Mar 8, 2025  0

Meet the JetBrains Team at GDC 2025

Mar 14, 2025  0

Why doesn’t the Windows blue screen of death prominentl...

Feb 2, 2025  0