Trace Dapper.NET Source Code - Detailed processing of CommandBehavior

Github Link : Trace-Dapper.NET-Source-Code

---

19. Detailed processing of CommandBehavior

This article will take readers to understand how Dapper uses CommandBehavior to optimize query efficiency, and how to choose the correct Behavior at a specific time.

I have compiled the Behavior table corresponding to each method here:

method	Behavior
Query	CommandBehavior.SequentialAccess & CommandBehavior.SingleResult
QueryFirst	CommandBehavior.SequentialAccess & CommandBehavior.SingleResult & CommandBehavior.SingleRow
QueryFirstOrDefault	CommandBehavior.SequentialAccess & CommandBehavior.SingleResult & CommandBehavior.SingleRow
QuerySingle	CommandBehavior.SingleResult & CommandBehavior.SequentialAccess
QuerySingleOrDefault	CommandBehavior.SingleResult & CommandBehavior.SequentialAccess
QueryMultiple	CommandBehavior.SequentialAccess

---

SequentialAccess, SingleResult optimization logic

First, you can see that each method uses CommandBehavior.SequentialAccess. The main function of this tag is to make the DataReader read rows and columns sequentially without buffering. After reading a column, it will be deleted from memory. It has the following advantages:

1. Resources can be read in order to avoid binary large resources from being read into memory at one time, especially Blob or Clob will cooperate with GetBytes or GetChars methods to limit the buffer size, Microsoft officials also have special attention:

1. Actual environment testing show it can speed up query efficiency. But it is not the default behavior of DataReader, the system default is CommandBehavior.Default

CommandBehavior.Default has the below behaviors:

1. Can return multiple result sets
2. Read row data to memory at once

These two features are much different from the production environment. After all, most of the time,only a set of result sets are needed with limited memory, so in addition to SequentialAccess, Dapper also uses CommandBehavior.SingleResult in most methods, so that only one set of results is required. To avoid wasting resources.

There is also a detailed processing of this paragraph. Looking at the source code, you can find that in addition to marking SingleResult, Dapper also specially adds code at the end while(reader.NextResult()){} instead of directly Return (such as the picture)

Earlier, I specifically posted an Issue (Link #1210) to ask author, here is the answer: mainly to avoid ignoring errors, such as when the DataReader is closed early.

QueryFirst with SingleRow

Sometimes we will encounter a situation where select top 1 knows that only one row of data will be read. At this time, QueryFirst can be used. It uses CommandBehavior.SingleRow to avoid wasting resources and only read one row of data.

In addition, it can be found that in addition to while (reader.NextResult()){}, Dapper also has while (reader.Read()) {}, which is also to avoid ignoring errors. This is something that some companies’ self-make ORMs will ignore.

Differences with QuerySingle

The difference between the two is that QuerySingle does not use CommandBehavior.SingleRow. As for why it is not used, it is because multiple rows of data are needed to determine whether the conditions are not met and an Exception is thrown to inform the user.

There is a particularly fun trick to learn in Dapper. The error handling directly uses the Exception corresponding to LINQ. For example: more than one line of data is wrong, use new int[2].Single(), so you don't need to maintain the Exception class separately, and you can also have more i18N language message.