Open DataReader issues while using Dapper.Net in high traffic scenario - now to resolve?

Question

Here is an example of one of our data calls in our DAL using Dapper.Net:

    /// <summary>
    /// Handles db connectivity as Dapper assumes an existing connection for all functions
    /// Since the app uses three databases, pass in the connection string for the required db.
    /// </summary>
    /// <returns></returns>
    protected static IDbConnection OpenConnection(string connectionStringName)
    {
        try
        {
            connection = new SqlConnection(WebConfigurationManager.ConnectionStrings[connectionStringName].ConnectionString);
            //connection = SqlMapperUtil.GetOpenConnection(connectionStringName);       // if we want to use the Dapper utility methods
            //connection = new SqlConnection(connectionString);
            connection.Open();
            return connection;
        }
        catch (Exception ex)
        {
            ErrorLogging.Instance.Fatal(ex);        // uses singleton for logging
            return null;
        }
    }


    public string GetNickname(int profileID)
    {
        string nickname = string.Empty;

        using (IDbConnection connection = OpenConnection("PrimaryDBConnectionString"))
        {
            try
            {
                var sp_nickname = connection.Query<string>("sq_mobile_nickname_get_by_profileid", new { profileID = profileID }, commandType: CommandType.StoredProcedure);
                nickname = sp_nickname.First<string>();
            }
            catch (Exception ex)
            {
                ErrorLogging.Instance.Fatal(ex);
                return null;
            }
        }

        return nickname;
    }

The consistent errors we are seeing are as follows:

2012-06-20 11:42:44.8903|Fatal|There is already an open DataReader associated with this Command which must be closed first.| at System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand command) at System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method, SqlCommand command) at System.Data.SqlClient.SqlCommand.ValidateCommand(String method, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader()
at MyApp.DAL.DapperORM.SqlMapper.d_131.MoveNext() in C:\Projects\Git\MyApp\MyApp.DAL\MyApp.DAL.MyAppPrimary.Repositories\Dapper\SqlMapper.cs:line 581 at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at MyApp.DAL.DapperORM.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable1 commandTimeout, Nullable1 commandType) in C:\Projects\Git\MyApp\MyApp.DAL\MyApp.DAL.MyAppPrimary.Repositories\Dapper\SqlMapper.cs:line 538 at MyApp.DAL.Repositories.MemberRepository.AddNotificationEntry(NewsfeedNotification notificationEntry) in C:\Projects\Git\MyApp\MyApp.DAL\MyApp.DAL.MyAppPrimary.Repositories\MemberRepositories\MemberRepository.cs:line 465 2012-06-20 11:42:45.2491|Fatal|Invalid attempt to call Read when reader is closed.| at System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
at System.Data.SqlClient.SqlDataReader.Read() at MyApp.DAL.DapperORM.SqlMapper.d_131.MoveNext() in C:\Projects\Git\MyApp\MyApp.DAL\MyApp.DAL.MyAppPrimary.Repositories\Dapper\SqlMapper.cs:line 597 at System.Collections.Generic.List1..ctor(IEnumerable1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable1 source) at MyApp.DAL.DapperORM.SqlMapper.Query[T](IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable1 commandTimeout, Nullable1 commandType) in C:\Projects\Git\MyApp\MyApp.DAL\MyApp.DAL.MyAppPrimary.Repositories\Dapper\SqlMapper.cs:line 538 at MyApp.DAL.DapperORM.SqlMapper.Query(IDbConnection cnn, String sql, Object param, IDbTransaction transaction, Boolean buffered, Nullable1 commandTimeout, Nullable1 commandType) in C:\Projects\Git\MyApp\MyApp.DAL\MyApp.DAL.MyAppPrimary.Repositories\Dapper\SqlMapper.cs:line 518 at MyApp.DAL.Repositories.MemberRepository.GetBuddies(Int32 profileID) in C:\Projects\Git\MyApp\MyApp.DAL\MyApp.DAL.MyAppPrimary.Repositories\MemberRepositories\MemberRepository.cs:line 271 2012-06-20 11:43:01.2392|Fatal|Sequence contains no elements| at System.Linq.Enumerable.First[TSource](IEnumerable`1 source) at MyApp.DAL.Repositories.MemberRepository.GetNickname(Int32 profileID) in C:\Projects\Git\MyApp\MyApp.DAL\MyApp.DAL.MyAppPrimary.Repositories\MemberRepositories\MemberRepository.cs:line 337

Initially I had the returns inside the using {...} and moved them outside the using block, but still experiencing the same issues.

This is a high-traffic application, so in testing this issue didn't really come up until we went live.

Is there something else that has to be done here for DataReader management with Dapper?

----- UPDATE -----

I should have posted this earlier, but just adding this now.

Line 581 of the Dapper.Net contains the ExecuteReader() code:

   private static IEnumerable<T> QueryInternal<T>(this IDbConnection cnn, string sql, object param, IDbTransaction transaction, int? commandTimeout, CommandType? commandType)
    {
        var identity = new Identity(sql, commandType, cnn, typeof(T), param == null ? null : param.GetType(), null);
        var info = GetCacheInfo(identity);

        using (var cmd = SetupCommand(cnn, transaction, sql, info.ParamReader, param, commandTimeout, commandType))
        {
            using (var reader = cmd.ExecuteReader())
            {
                Func<Func<IDataReader, object>> cacheDeserializer =  () =>
                {
                    info.Deserializer = GetDeserializer(typeof(T), reader, 0, -1, false);
                    SetQueryCache(identity, info);
                    return info.Deserializer;
                };

                if (info.Deserializer == null)
                {
                    cacheDeserializer();
                }

                var deserializer = info.Deserializer;

                while (reader.Read())
                {
                    object next;
                    try
                    {
                        next = deserializer(reader);
                    }
                    catch (DataException)
                    {
                        // give it another shot, in case the underlying schema changed
                        deserializer = cacheDeserializer();
                        next = deserializer(reader);
                    }
                    yield return (T)next;
                }

            }
        }

... see it there in the nested using code? I wonder if due to the yield return (T)next; code inside the while, inside the nested using, if that is causing an issue.

The thing is that with a moderate amount of traffic, Dapper seems to operate just fine. However in a system with about 1000 requests per second, it seems to trip up.

I guess this is more of a FYI for the Dapper dev's, and wondering if they could resolve this.

(and I realize I miss-named DapperORM in the code - it's not an ORM)

Another way to get this error with dapper is using `QueryMultiple` without embedding it in `using`. Even if you don't assign the result to a variable, a wild opened DataReader will stay in the memory (probably until garbage collector gets a hold of it). I'm writing this as a comment, because it doesn't answer your question, but is related to the subject. — jahu, Nov 03 '15 at 14:30
"The thing is that with a moderate amount of traffic, Dapper seems to operate just fine. However in a system with about 1000 requests per second, it seems to trip up." You gotta be kidding. StackOverflow uses Dapper and guess how much traffic they have! — Delphi.Boy, Dec 23 '21 at 18:54

score 1 · Answer 1 · answered Aug 08 '12 at 13:41

1

You only read the first row of the datareader, so it never gets closed if there is more than one row.

answered Aug 08 '12 at 13:41

dheijl

11
1

1

Can you clarify? Since it's all wrapped in [using] wouldn't that take care of it? – ElHaix Nov 07 '12 at 20:38

score 1 · Answer 2 · answered Jul 30 '19 at 19:55

A bit late to the party but it might help someone that will get stuck as me.

The issue is that Dapper "Query" method returned IEnumerable that is actually using a "yield return" statement to generate an iterator to the result set:

// From Dapper SqlMapper.cs QueryImpl function:
            while (reader.Read())
            {
                object val = func(reader);
                if (val == null || val is T)
                {
                    yield return (T)val;
                }
                else
                {
                    yield return (T)Convert.ChangeType(val, convertToType, CultureInfo.InvariantCulture);
                }
            }

The part that closes the DataReader happens later. So in case you won't iterate over the entire result set and try to query another request you will get the "There is already an open DataReader associated with this Command which must be closed first" error.

score 0 · Answer 3 · edited May 23 '17 at 12:30

The answer from Ladislav Mrnka on a similar question makes more sense:

This can happen if you execute a query while iterating over the results from another query... One thing that can cause this is lazy loading triggered when iterating over the results of some query. This can be easily solved by allowing MARS in your connection string. Add MultipleActiveResultSets=true to the provider part of your connection string (where Data Source, Initial Catalog, etc. are specified).

https://stackoverflow.com/a/6064422/1681490

More info on MARS here: http://msdn.microsoft.com/en-us/library/h32h3abf(v=vs.100).aspx

score -1 · Accepted Answer · answered Jun 22 '12 at 15:14

I used Entity Framework to generate my classes, so and created a different repository for DAL access - rather than using Dapper, I simply rewrote the access code to use Entity Framework. Nothing different than the EF connection strings and using the EF database context in my using statements.

It all works just fine.

From what I read, Dapper is pretty fast, which is why I initially chose this for my DAL. However, it seems it has its limitations in a high frequency transaction environment. Maybe the Dapper team can clarify this in case I missed something or implemented something incorrectly.

Open DataReader issues while using Dapper.Net in high traffic scenario - now to resolve?

4 Answers4