public override string GetSql(SqlParameterCollection parameters, int joinCount)
{
StringBuilder stringBuilder = new StringBuilder();
if (typeof(T) == typeof(int))
{
string paramName = "@" + parameters.Count;
QueryHelpers.AddSqlParameter(parameters, paramName, SqlParameterUtils.Create(paramName, values.OfType <int>()));
stringBuilder.Append(paramName);
}
else if (typeof(T) == typeof(int?))
{
string paramName = "@" + parameters.Count;
QueryHelpers.AddSqlParameter(parameters, paramName, SqlParameterUtils.Create(paramName, values.OfType <int?>()));
stringBuilder.Append(paramName);
}
else if (typeof(T) == typeof(long))
{
string paramName = "@" + parameters.Count;
QueryHelpers.AddSqlParameter(parameters, paramName, SqlParameterUtils.Create(paramName, values.OfType <long>()));
stringBuilder.Append(paramName);
}
else if (typeof(T) == typeof(long?))
{
string paramName = "@" + parameters.Count;
QueryHelpers.AddSqlParameter(parameters, paramName, SqlParameterUtils.Create(paramName, values.OfType <long?>()));
stringBuilder.Append(paramName);
}
else if (typeof(T) == typeof(string))
{
string paramName = "@" + parameters.Count;
QueryHelpers.AddSqlParameter(parameters, paramName, SqlParameterUtils.Create(paramName, values.OfType <string>()));
stringBuilder.Append(paramName);
}
else
{
int i = 0;
foreach (T value in values)
{
if (!value.Equals(default(T)))
{
if (i > 0)
{
stringBuilder.Append(", ");
}
string paramName = "@" + parameters.Count;
stringBuilder.Append(paramName);
QueryHelpers.AddSqlParameter(parameters, paramName, value);
i++;
}
}
}
string str1 = queryEls[0].GetSql(parameters, joinCount);
return(string.Format(sql, str1, stringBuilder));
}
private void EnsureElementsAreLoaded()
{
if (cachedElements != null)
{
return;
}
lock (cachedElementsLock)
{
if (cachedElements != null)
{
return;
}
var cachedTypedElements = new List <TElement>();
List <(TElement, List <object>)> loadedData = SqlCommands.ExecuteSqlCommand(() =>
{
using (var sqlConnection = Db.GetConnection())
{
using (var sqlCommand = GetSqlCommand())
{
QueryHelpers.AddSqlParameters(sqlCommand, parameters);
// AppContext.StartRequestTiming(sql, "OpenConnection");
sqlConnection.Open();
// AppContext.StopRequestTiming(sql, "OpenConnection");
sqlCommand.Connection = sqlConnection.SqlConnection;
// sqlCommand.Transaction = sqlConnection.SqlConnection.BeginTransaction(Db.Settings().IsolationLevel);
if (sqlCommand.CommandType == CommandType.StoredProcedure)
{
SqlParameter returnValue = SqlParameterUtils.Create("@ReturnValue", TypeToDbType <TElement>());
returnValue.Direction = ParameterDirection.ReturnValue;
sqlCommand.Parameters.Add(returnValue);
sqlCommand.ExecuteNonQuery();
TElement entity = (TElement)sqlCommand.Parameters["@ReturnValue"].Value;
cachedTypedElements.Add(entity);
sqlCommand.Parameters.Clear();
return(new List <(TElement, List <object>)>());
}
else
{
// AppContext.StartRequestTiming(sql, "ExecuteReader");
var loadedDataFromReader = new List <(TElement, List <object>)>();
using (SqlDataReader reader = sqlCommand.ExecuteReader(CommandBehavior.SequentialAccess))
{
// AppContext.StopRequestTiming(sql, "ExecuteReader");
// AppContext.StartRequestTiming(sql, "FillVisitor");
var fillVisitor = new FillVisitor(
reader: reader,
db: Db,
objectFillerFactory: new ObjectFillerFactory());
// fillVisitor.SQL = sql;
// AppContext.StopRequestTiming(sql, "FillVisitor");
// AppContext.StartRequestTiming(sql, "Read");
int k = 0;
while (fillVisitor.Read())
{
// AppContext.StopRequestTiming(sql, "Read");
// AppContext.StartRequestTiming(sql, "Fill", k > 0);
TElement entity = QueryHelpers.Fill(default(TElement), fillVisitor);
var dbEntity = entity as DbEntity;
if (dbEntity != null)
{
((IDbEntityInternal)dbEntity).SetAllowSettingColumns(false);
}
if (entity is IId)
{
entity = (TElement)Db.LoadedEntityCache.GetOrAdd(typeof(TElement), "Id", ((IId)entity).Id, entity);
}
// AppContext.StopRequestTiming(sql, "Fill");
// First fetch all entities from the db and then return them
// Otherwise we have problems, if someone calls AnyDb() first (AnyDb() just requests the first entity)
// Anyway we improve performance if someone calls the query two times: The second time, we don't query the
// database but return the cached elements
List <object> subEntities = new List <object>();
if (fillVisitor.HasNext && !fillVisitor.IsDBNull())
{
subEntities.OfType <IDbEntityInternal>().ForEach(e => e.SetAllowSettingColumns(true));
// AppContext.StopRequestTiming(sql, "subEntities");
}
k++;
loadedDataFromReader.Add((entity, subEntities));
// CachedElements.Add(entity);
}
// AppContext.StopRequestTiming(sql, "Read");
}
sqlCommand.Parameters.Clear();
return(loadedDataFromReader);
}
}
}
});
// Connect the items to one another (i.e. if Person.Customer was called, set Person.Customer to the recently loaded entity)
// First emit EmitValueLoadedBeforeRightsCheck(dbEntity); for all entities before checking the rights, because if only do it within the while loop below just before Db.ReturnEntity for each dbEntity,
// in GetOwnerMandatorId() of i.e. User (which is called from Db.ReturnEntity()), if we call Person.Customer.MandatorId, Customer is loaded from the DB, because Person.Customer was not set yet
loadedData.Select(e => e.Item1).OfType <DbEntity>().ForEach(e => EmitValueLoadedBeforeRightsCheck(e));
// Do this after the sql connection above has been closed
// Otherwise we get this exception:
// System.Reflection.TargetInvocationException: Exception has been thrown by the target of an invocation. ---> System.Transactions.TransactionAbortedException: The transaction has aborted. ---> System.Transactions.TransactionPromotionException: Failure while attempting to promote transaction. ---> System.Data.SqlClient.SqlException: There is already an open DataReader associated with this Command which must
var entityFilter = GlobalDbConfiguration.GetConfigurationOrEmpty(typeof(TElement)).EntityFilter;
for (int i = 0; i < loadedData.Count; i++)
{
var entity = loadedData[i].Item1;
var joinedEntities = loadedData[i].Item2;
GlobalDbConfiguration.QueryLogger.IncrementLoadedElementCount(increment: 1);
// Count the joined entities too
GlobalDbConfiguration.QueryLogger.IncrementLoadedElementCount(increment: joinedEntities?.Count ?? 0);
var dbEntity = entity as DbEntity;
if (dbEntity != null)
{
((IDbEntityInternal)dbEntity).SetAllowSettingColumns(allowSettingColumns: true);
}
if (entityFilter.DoReturnEntity(Db.Settings, entity, joinedEntities))
{
EmitValueLoaded(dbEntity);
cachedTypedElements.Add(entity);
}
else
{
EmitValueRemoved(dbEntity);
}
}
cachedElements = cachedTypedElements.AsReadOnly();
}
}
public static void AddSqlParameter(SqlParameterCollection sqlParameterCollection, string paramName, object parameter)
{
var sqlParameter = SqlParameterUtils.Create(paramName, parameter);
sqlParameterCollection.Add(sqlParameter);
}
