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:
- 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:
- Actual environment testing show it can
speed up query efficiency
. But it isnot
the default behavior of DataReader, the systemdefault is CommandBehavior.Default
CommandBehavior.Default has the below behaviors:
- Can return
multiple
result sets - 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.
Top comments (0)