Dapper: Essential Functions and Examples

Introduction

Hello Readers! Welcome back. In the last blog, we learnt what is dapper and how to use that in our .NET applications to access data from out database. Today, we will be going through some more important dapper functions which will help you in data querying.

In case, you haven’t gone through the previous blog about dapper introduction, I will recommend you to go through it here.

If you have already gone through it or are aware of Dapper, then we are good to get started.

Dapper Functions

Execute

This function is used to execute a SQL command and in return it returns the number of rows affected in the database. Keep in mind that it will not return any data from the database. The SQL commands which can be executed under this are – DELETE, INSERT, UPDATE etc. This method is available in the IDbConnection interface.

string sql = "INSERT INTO Users(UserId, Name, Age) VALUES(@UserId, @Name,  @Age)";
using(var connection = new SqlConnection(connectionString)){
  int numberOfRowsAffected = connection.Execute(sql, new {UserId= 1, Name= "User1", Age = 18});
}

Query

The query method quickly accesses data from the database. Dapper is capable of automatically mapping the data from the database into your list of objects. This method handles your SELECT queries.

string sql = "SELECT * FROM Users";
using(var connection = new SqlConnection(connectionString)){
  IEnumerable<User> users = connection.Query<User>(sql).ToList();
}

QuerySingle/ QuerySingleOrDefault

This method fetches a single record from the database. The QuerySingle method will throw an exception if more than one record is found matching the SQL command where as the QuerySingleOrDefault will return null if no such record is found. So in such a case use you can use the method suiting your needs.

string sql = "SELECT * FROM Users WHERE UserId = @Id";
using(var connection = new SqlConnection(connectionString)){
  User user = connection.QuerySingle<User>(sql, new {Id = 1});
}

QueryFirst

This method is similar to Query method, the difference is that it after executing the query, the first result will be mapped to the object. Unlike QuerySingle, it will not throw any exception if there are more than one records present in the database. It will simply retrieve the first result and ignore the others. If in case no record is found, then it will throw an exception.

string sql = "SELECT TOP 5 * FROM Users ORDER BY UserId DESC";
using(var connection = new SqlConnection(connectionString)){
  User user = connection.QueryFirst<User>(sql);
}

QueryMultiple

The QueryMultiple is used in cases where you want to execute multiple queries in a single statement. The method will fetch multiple result sets from the database.

using(var connection = new SqlConnection(connectionString)){
  string sql = @"
    SELECT * FROM Users ;
    SELECT * FROM UserJobInfo;";
      using(var multi = connection.QueryMultiple(sql)){
        List<User> users = multi.Read<User>().ToList();
        List<UserInfo> userInfos = multi.Read<UserInfo>().ToList();
      }
}

ExecuteScalar

ExecuteScalar is used to execute a query and it returns a single value. A single value means a single row and a single column. Not a single row. For example you want to count the number of rows in a table, then is such a case you can use this. It is useful for aggregate functions like COUNT, SUM etc.

string sql = "SELECT COUNT(*) FROM Users";
using(var connection = new SqlConnection(connectionString)){
  int count = connection.ExecuteScalar<int>(sql);
}

Conclusion

Overall, Dapper is a powerful and lightweight micro-ORM that simplifies data access in .NET applications. Its intuitive methods, such as Query, QueryFirst, Execute, and QueryMultiple, allow developers to write clean, efficient, and performant database interactions with minimal overhead. Additionally, it provides support for Stored Procedures and Transactions. By combining the speed of raw ADO.NET with the convenience of object mapping, Dapper strikes the perfect balance between performance and usability.

So if you are looking for a powerful tool to handle database interactions, Dapper is an excellent choice. I hope you find this blog useful.

Finally, for more such posts like this, please follow our LinkedIn page- FrontEnd Competency for more such blogs.