C# | Dapper

To understand Dapper, first, we need to know what is ORM or Object Relational Mapper. 

Object Relational Mapper(ORM) :
Object Relational Mapping is the concept of writing queries using the object-oriented paradigm of the preferred programming language. ORM is used to interact with the SQL databases using our preferred language instead of SQL. Example – Entity Framework for ASP.NET. So, basically, an ORM creates objects based on database queries.

Now, there is another thing called Micro-ORM which is kind of a lightweight version of a full ORM. It does not come with all the heavy features of a full ORM and saves a lot of work but still does the mapping between the database and our preferred programming language. Dapper is an example of Micro ORM, in fact, it is called the King of Micro ORM because of its speed and ease of work. 

Dapper works in the following way – 

• First, it creates an IDbConnection object and allows us to write queries to perform CRUD operations on the database. 
• Then, it passes the queries as one of its various parameters through one of its various methods. 

Parameters of Dapper :

1. sql – The command to execute.
2. param – The command parameters (default = null).
3. commandTimeout – The command timeout (default = null).
4. commandType -The command type (default = null).

Methods of  Dapper :

1. Execute : 
Execute is a method called from IDbConnection type object which can execute command one or multiple times and return the number of affected rows in the database tables. It can execute Stored Procedures, SELECT/INSERT/DELETE statements, etc.

Example :

C#

string sql = "INSERT INTO Companies (CompanyName)
                Values (@CompanyName);";

using (var connection = new SqlConnection(
    FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    var affectedRows = connection.Execute(sql,
              new {CompanyName = "GeeksforGeeks"});
}

2. Query :
The query is a method called from IDbConnection type object which can execute a query and map the result.

C#

string sql = "SELECT * FROM Companies";

using (var connection = new SqlConnection(
          FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    var companies = connection.Execute(sql);
}

3. QueryFirst :
QueryFirst is a method called from IDbConnection type object which can execute a query and map the first result.

C#

string sql = "SELECT * FROM Companies WHERE CompanyId = @CompanyId";

using (var connection = new SqlConnection(
     FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    var company = connection.QueryFirst(sql,
                           new {CompanyId = 1});
}

Note: QueryFirstOrDefault method is a method that can execute a query and map the first result, or a default value if the sequence contains no elements.

4. QuerySingle :
QuerySingle is a method called from IDbConnection type object which can execute a query and map the first result and throws an exception if there is not exactly one element in the sequence.

C#

string sql = "SELECT * FROM Companies WHERE CompanyId = @CompanyId";

using (var connection = new SqlConnection(
          FiddleHelper.GetConnectionStringSqlServerW3Schools()))
{
    var company = connection.QuerySingle(sql,
                             new {CompanyId = 1});
}

Note: QuerySingleOrDefault method is a method that can execute a query and map the first result, or a default value if the sequence contains no elements and throws an exception if there is more than one element in the sequence.