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)
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.