Dapper Micro ORM (Connection Management)

Dapper is a micro-ORM or a Data Mapper. It internally uses ADO.NET. Additionally, Dapper maps the ADO.NET data structures to your custom POCO classes. As this is additional work Dapper does, in theory, it cannot be faster than ADO.NET. It prevents SQL Injection from external user input by avoiding raw-SQL query string building. Dapper provides methods to build parameterized queries as well as passing sanitized parameters to stored procedures.

Dapper has provided multiple methods to Query or execute the store procedures and SQL queries.

At the high level, we can split into two categories as Query and Execute.

Query

• It's mainly used to fetch the data from the database.
• To query the data in sync or async way 
• To fetch single or multiple records.
• To query single or multiple result sets also.

Execute

• It's mainly used to manipulate data in the database.
• We can pass store procedures or SQL statements as input to execute them into the database.
• It has transaction support also.
• At a high level, we can cover CRUD operation with the below snippets.

Query

It will fetch the results and map to the corresponding Generic class type.

Execute

 It will execute the store procedures and return the no of affected rows as a response.

Execute With Transaction

It will execute the series of queries in sequence and we can commit or revert the entire Transaction.

Connection Management

From the above code snippets, people have a concern about performance in terms of connection management.

Here we are creating the connection by 'using'. So every time it will create a new connection instead of reusing the existing connection. We are not adhering to the connection pooling.

But the reality is, as we discussed earlier, Dapper is a mapping library. So it will not maintain the connection management. It's extended the ADO.NET feature for connection management. So ADO.NET will manage the connection polling based on the SQL connection string. Dapper will get the connection from the polling for every new request. Default pool size will be 0 (Min) to 100 (Max). Dapper has methods to clear the particular or all the connection polls. So using Dapper we don't need to worry about connection management.

We have used 'using' to make a connection. Once we have done our jobs the objects will return to ADO.NET pools. So we don't require to close the connection also. 

Database connection management will be simple in Dapper. So we don't need to worry about the memory leak in terms of connection management in Dapper.