在高流量場景中使用Dapper.Net時打開DataReader問題 - 現在要解決?

asp.net c#-4.0 dapper datareader dbdatareader

以下是使用Dapper.Net在DAL中進行數據調用的示例:

    /// <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;
    }

我們看到的一致錯誤如下:

2012-06-20 11:42:44.8903 |致命|已經有一個與此命令關聯的打開DataReader必須先關閉。在System.Data.SqlClient.SqlInternalConnectionTds.ValidateConnectionForExecute(SqlCommand命令)處於System.Data.SqlClient.SqlConnection.ValidateConnectionForExecute(String method,SqlCommand命令)的System.Data.SqlClient.SqlCommand.ValidateCommand(String method,Boolean async)at System系統中System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,RunBehavior runBehavior,Boolean returnStream,String方法)的.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior,RunBehavior runBehavior,Boolean returnStream,String方法,DbAsyncResult結果)。 System.Data.Com.DbCommand.System.Data.Data.IDbCommand.ExecuteReader()中System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior行為)的Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior,String方法)
at MyApp.DAL.DapperORM.SqlMapper.d_ 13 1.MoveNext() in C:\Projects\Git\MyApp\MyApp.DAL\MyApp.DAL.MyAppPrimary.Repositories\Dapper\SqlMapper.cs:line 581 at System.Collections.Generic.List位於MyApp.DAL.DapperORM.SqlMapper.Query [T]的1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable 1 source)的1.MoveNext() in C:\Projects\Git\MyApp\MyApp.DAL\MyApp.DAL.MyAppPrimary.Repositories\Dapper\SqlMapper.cs:line 581 at System.Collections.Generic.List 1..ctor(IEnumerable 1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable (IDbConnection cnn,String sql,Object param,IDbTransaction事務,布爾緩衝,Nullable 1 commandTimeout, Nullable 1 commandType)在C:\ Projects \ Git \ MyApp \ MyApp.DAL \ MyApp.DAL.MyAppPrimary.Repositories \ Dapper \ SqlMapper.cs:MyApp.DAL第538行C:\ Projects \ Git \ MyApp \ MyApp.DAL \ MyApp.DAL.MyAppPrimary.Repositories \ MemberRepositories \ MemberRepository.cs中的.Repositories.MemberRepository.AddNotificationEntry(NewsfeedNotification notificationEntry):line 465 2012-06-20 11:42:45.2491 |致命|讀取器關閉時無效嘗試調用讀取在System.Data.SqlClient.SqlDataReader.ReadInternal(Boolean setTimeout)
在MyApp.DAL.DapperORM.SqlMapper.d
_13中的1.MoveNext() in C:\Projects\Git\MyApp\MyApp.DAL\MyApp.DAL.MyAppPrimary.Repositories\Dapper\SqlMapper.cs:line 597 at System.Collections.Generic.List 1..ctor(IEnumerable 1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable 1.MoveNext() in C:\Projects\Git\MyApp\MyApp.DAL\MyApp.DAL.MyAppPrimary.Repositories\Dapper\SqlMapper.cs:line 597 at System.Collections.Generic.List MyApp.DAL.DapperORM.SqlMapper的1 collection) at System.Linq.Enumerable.ToList[TSource](IEnumerable 1 source)。在C:\ Projects \ Git \ MyApp \ MyApp.DAL \ MyApp.DAL.MyAppPrimary.Repositories中查詢[T](IDbConnection cnn,String sql,Object param,IDbTransaction事務,Boolean buffered,Nullable 1 commandTimeout, Nullable 1 commandType) Dapper \ SqlMapper.cs:位於C:\ Projects \ Git \的MyApp.DAL.DapperORM.SqlMapper.Query(IDbConnection cnn,String sql,Object param,IDbTransaction事務,Boolean buffered,Nullable 1 commandTimeout, Nullable 1 commandType)中的第538行MyApp \ MyApp.DAL \ MyApp.DAL.MyAppPrimary.Repositories \ Dapper \ SqlMapper.cs:位於C:\ Projects \ Git \ MyApp \ MyApp.DA的MyApp.DAL.Repositories.MemberRepository.GetBuddies(Int32 profileID)的第518行L \ MyApp.DAL.MyAppPrimary.Repositories \ MemberRepositories \ MemberRepository.cs:第271行2012-06-20 11:43:01.2392 |致命|序列不包含任何元素at System.Linq.Enumerable.First [TSource](IEnumerable`1 source)位於C:\ Projects \ Git \ MyApp \ MyApp.DAL \ MyApp.DAL.MyAppPrimary中的MyApp.DAL.Repositories.MemberRepository.GetNickname(Int32 profileID) .Repositories \ MemberRepositories \ MemberRepository.cs:第337行

最初我在using {...}內部返回並將它們移到using塊之外,但仍然遇到相同的問題。

這是一個高流量的應用程序,所以在測試這個問題並沒有真正出現,直到我們上線。

使用Dapper進行DataReader管理是否還需要做其他事情?

-----更新-----

我應該早點發布這個,但現在就添加它。

Dapper.Net的第581行包含ExecuteReader()代碼:

   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;
                }

            }
        }

...在嵌套using代碼中看到它?我想知道是否由於yield return (T)next; while內部的代碼,嵌套using內部,如果這是導致問題。

事情是,在適度的交通量下,Dapper似乎運作得很好。然而,在每秒大約1000個請求的系統中,它似乎絆倒了。

我想這對於Dapper dev來說更像是一個FYI,並且想知道他們是否可以解決這個問題。

(我意識到我在代碼中錯過了名為DapperORM的東西 - 它不是ORM)

一般承認的答案

我使用Entity Framework來生成我的類,因此為DAL訪問創建了一個不同的存儲庫 - 而不是使用Dapper,我只是重寫了訪問代碼以使用Entity Framework。沒有什麼不同於EF連接字符串和在我的using語句中使用EF數據庫上下文。

一切正常。

從我讀到的,Dapper相當快,這就是為什麼我最初選擇這個為我的DAL。但是,它似乎在高頻交易環境中有其局限性。也許Dapper團隊可以澄清這一點,以防我錯過了某些內容或者錯誤地實現了某些內容。


熱門答案

您只讀取了datareader的第一行,因此如果有多行,它永遠不會被關閉。



許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因
許可下: CC-BY-SA with attribution
不隸屬於 Stack Overflow
這個KB合法嗎? 是的,了解原因