string BuildCountSql(ISession session, SqlQueryBody sql, int limit, int offset)
{
var dialect = session.SessionFactory.DbSettings.SqlDialect;
var countFunction = dialect.GetFunction(FunctionNames.Count);
if (countFunction == null)
{
throw new GoliathDataException(string.Format("Count function is either not registerd for provider {0} or is not supported",
dialect.DatabaseProviderName));
}
StringBuilder countSql = new StringBuilder(string.Format("SELECT {0} ctn_TOTAL ", countFunction.ToSqlStatement()));
countSql.AppendFormat("FROM {0} ", sql.From);
if (!string.IsNullOrWhiteSpace(sql.JoinEnumeration))
{
countSql.Append(sql.JoinEnumeration);
}
if (!string.IsNullOrWhiteSpace(sql.WhereExpression))
{
countSql.AppendFormat(" WHERE {0}", sql.WhereExpression);
}
return(countSql.ToString());
}
/// <summary>
/// Queries the with paging.
/// </summary>
/// <param name="queryBody">The query body.</param>
/// <param name="pagingInfo">The paging info.</param>
/// <returns></returns>
public override string QueryWithPaging(SqlQueryBody queryBody, PagingInfo pagingInfo)
{
StringBuilder sb = new StringBuilder("SELECT * \nFROM \n\t\t( SELECT ROW_NUMBER() OVER");
sb.AppendFormat(" (ORDER BY {0}) AS __RowNum, {1}", queryBody.SortExpression, queryBody.ColumnEnumeration);
sb.AppendFormat("\n\t\tFROM {0} ", queryBody.From);
if (!string.IsNullOrWhiteSpace(queryBody.JoinEnumeration))
{
sb.Append(queryBody.JoinEnumeration);
}
if (!string.IsNullOrWhiteSpace(queryBody.WhereExpression))
{
sb.AppendFormat("\n\t\tWHERE {0}\n", queryBody.WhereExpression);
}
sb.Append(") AS RowConstrainedResult");
sb.AppendFormat("\nWHERE __RowNum > {0}", pagingInfo.Offset);
sb.AppendFormat("\nAND __RowNum <= {0}", pagingInfo.Offset + pagingInfo.Limit);
sb.Append("\nORDER BY __RowNum");
return(sb.ToString());
}
public SqlQueryBody Build(bool selectCount = false)
{
SqlQueryBody queryBody = new SqlQueryBody()
{
QueryMap = QueryMap
};
if (string.IsNullOrEmpty(alias))
{
alias = tableName;
queryBody.From = tableName;
}
else
{
queryBody.From = string.Format("{0} {1}", tableName, alias);
}
if (columnNames.Count < 1)
{
queryBody.ColumnEnumeration = "*";
}
else
{
queryBody.ColumnEnumeration = ColumnFormatter.Format(columnNames, alias);
}
if (selectCount)
{
var countFunction = dialect.GetFunction(FunctionNames.Count);
var sql = countFunction.ToSqlStatement(new QueryParam("*", null));
queryBody.ColumnEnumeration = sql;
}
StringBuilder joinBuilder = new StringBuilder();
if (joins.Count > 0)
{
string jtype = "JOIN";
foreach (var join in joins.Values)
{
switch (join.Type)
{
case JoinType.Inner:
jtype = "INNER JOIN";
break;
case JoinType.Left:
jtype = "LEFT JOIN";
break;
case JoinType.Right:
jtype = "RIGHT JOIN";
break;
case JoinType.Full:
jtype = "FULL JOIN";
break;
}
joinBuilder.AppendFormat("{0} {1} {2} ON ", jtype, join.JoinTableName, join.JoinTableAlias);
string leftSide = string.Format("{0}.{1}", join.JoinLeftTableAlias, join.JoinRightColumn);
if (join.JoinRightColumn.Contains("."))
{
leftSide = join.JoinRightColumn;
}
string rightSide = string.Format("{0}.{1}", join.JoinTableAlias, join.JoinLeftColumn);
if (join.JoinLeftColumn.Contains("."))
{
rightSide = join.JoinLeftColumn;
}
joinBuilder.AppendFormat("{0} = {1} ", leftSide, rightSide);
}
queryBody.JoinEnumeration = joinBuilder.ToString().Trim();
}
if (whereClauses.Count > 0)
{
var firstWhere = whereClauses[0];
var sql = firstWhere.BuildSqlString(dialect, 0);
AddToParameterList(sql.Item2);
StringBuilder wherebuilder = new StringBuilder();
wherebuilder.AppendFormat("{0} ", sql.Item1);
if (whereClauses.Count > 1)
{
for (int i = 1; i < whereClauses.Count; i++)
{
var where = whereClauses[i].BuildSqlString(dialect, i);
AddToParameterList(where.Item2);
string prep = "AND";
if (whereClauses[i].PreOperator != SqlOperator.AND)
{
prep = "OR";
}
wherebuilder.AppendFormat("{0} {1} ", prep, where.Item1);
}
}
queryBody.WhereExpression = wherebuilder.ToString().Trim();
}
if (sortClauses.Count > 0)
{
queryBody.SortExpression = string.Join(", ", sortClauses);
}
return(queryBody);
}
public T Run <T>(SqlQueryBody sql, params QueryParam[] paramArray)
{
return(commandRunner.Run <T>(this, sql, paramArray));
}
public IList <T> RunList <T>(SqlQueryBody sql, int limit, int offset, out long total, params QueryParam[] paramArray)
{
return(commandRunner.RunList <T>(this, sql, limit, offset, out total, paramArray));
}
/// <summary>
/// Queries the with paging.
/// </summary>
/// <param name="queryBody">The query body.</param>
/// <param name="pagingInfo">The paging info.</param>
/// <returns></returns>
public virtual string QueryWithPaging(SqlQueryBody queryBody, PagingInfo pagingInfo)
{
return($"{queryBody} LIMIT {pagingInfo.Limit} OFFSET {pagingInfo.Offset}");
}
internal IList <T> ExecuteReader <T>(IEntityMap entMap, DbConnection dbConn, ISession session, SqlQueryBody sql, int limit, int offset, params QueryParam[] paramArray)
{
using (var dataReader = session.DataAccess.ExecuteReader(dbConn, session.CurrentTransaction, sql.ToString(session.SessionFactory.DbSettings.SqlDialect, new PagingInfo()
{
Limit = limit, Offset = offset
}), paramArray))
{
var entities = session.SessionFactory.DataSerializer.SerializeAll <T>(dataReader, entMap, sql.QueryMap);
return(entities);
}
}
/// <summary>
/// Runs the list.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="session">The session.</param>
/// <param name="sql">The SQL.</param>
/// <param name="paramArray">The param array.</param>
/// <returns></returns>
public IList <T> RunList <T>(ISession session, SqlQueryBody sql, params QueryParam[] paramArray)
{
return(RunList <T>(session, sql.ToString(), sql.QueryMap, paramArray));
}
/// <summary>
/// Runs the statement.
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="session">The session.</param>
/// <param name="sql">The SQL.</param>
/// <param name="limit">The limit.</param>
/// <param name="offset">The offset.</param>
/// <param name="total">The total.</param>
/// <param name="paramArray">The param array.</param>
/// <returns></returns>
public IList <T> RunList <T>(ISession session, SqlQueryBody sql, int limit, int offset, out long total, params QueryParam[] paramArray)
{
total = 0;
Type instanceType = typeof(T);
//bool ownTransaction = false;
var dbConn = session.ConnectionManager.OpenConnection();
IList <T> list = new List <T>();
var dialect = session.SessionFactory.DbSettings.SqlDialect;
var serializer = session.SessionFactory.DataSerializer;
try
{
//if ((session.CurrentTransaction == null) || !session.CurrentTransaction.IsStarted)
//{
// ownTransaction = true;
// session.BeginTransaction();
//}
if (instanceType.IsPrimitive || typeof(string) == instanceType || typeof(Guid) == instanceType || typeof(DateTime) == instanceType || typeof(DateTimeOffset) == instanceType)
{
list = ExecuteReaderPrimitive <T>(dbConn, session, sql, limit, offset, out total, paramArray);
}
else
{
MapConfig map = session.SessionFactory.DbSettings.Map;
EntityMap entMap;
ComplexType complexType;
if (map.EntityConfigs.TryGetValue(instanceType.FullName, out entMap))
{
list = ExecuteReader <T>(entMap, dbConn, session, sql, limit, offset, out total, paramArray);
}
else if (map.ComplexTypes.TryGetValue(instanceType.FullName, out complexType))
{
list = ExecuteReader <T>(complexType, dbConn, session, sql, limit, offset, out total, paramArray);
}
else
{
//Build a dynamic entity
DynamicEntityMap dynEntMap = new DynamicEntityMap(instanceType);
list = ExecuteReader <T>(dynEntMap, dbConn, session, sql, limit, offset, out total, paramArray);
}
}
//if (ownTransaction)
// session.CommitTransaction();
return(list);
}
catch (GoliathDataException ex)
{
//if (ownTransaction)
// session.RollbackTransaction();
logger.Log(LogLevel.Debug, string.Format("Goliath Exception found {0} ", ex.Message));
throw;
}
catch (Exception ex)
{
//if (ownTransaction)
// session.RollbackTransaction();
throw new GoliathDataException(string.Format("Exception while running sql command: {0}", sql), ex);
}
}
internal IList <T> ExecuteReader <T>(IEntityMap entMap, DbConnection dbConn, ISession session, SqlQueryBody sql, int limit, int offset, out long total, params QueryParam[] paramArray)
{
total = 0;
var dialect = session.SessionFactory.DbSettings.SqlDialect;
var serializer = session.SessionFactory.DataSerializer;
string countSql = BuildCountSql(session, sql, limit, offset);
string sqlWithPaging = sql.ToString(dialect, new PagingInfo()
{
Limit = limit, Offset = offset
});
string sqlToRun = string.Format("{0};\n\r{1};", countSql, sqlWithPaging);
using (var dataReader = session.DataAccess.ExecuteReader(dbConn, session.CurrentTransaction, sqlToRun, paramArray))
{
//First resultset contains the count
while (dataReader.Read())
{
total = serializer.ReadFieldData <long>(0, dataReader);
//we only expect 1 row of data to be returned, so let's break out of the loop.
break;
}
//move to the next result set which contains the entities
dataReader.NextResult();
var entities = serializer.SerializeAll <T>(dataReader, entMap, sql.QueryMap);
return(entities);
}
}
