/// <summary>
/// Gets the record count for the current type's mapped table.
/// </summary>
/// <param name="conditions">The search items.</param>
/// <returns></returns>
public int GetRecordCount(QueryConditions conditions = null)
{
if (string.IsNullOrWhiteSpace(TargetTable))
{
return(0);
}
var searchItems = conditions.SearchItems;
var searchLogic = conditions.SearchLogic;
using (var connection = CreateConnection())
{
using (var command = CreateCommand(connection))
{
// Get the overall record count
const string countQuery = @"SELECT COUNT(1) FROM {0}";
var queryBuilder = new StringBuilder().AppendFormat(countQuery, TargetTable);
if (searchItems != null || conditions.RequiredSearchItems.Any())
{
var first = true;
conditions.RequiredSearchItems.ForEach(delegate(SearchItem item)
{
if (first)
{
first = false;
queryBuilder.AppendFormat(" WHERE ({0}", item.DetailSql);
}
else
{
queryBuilder.AppendFormat(" AND {0}", item.DetailSql);
}
item.Parameters.ForEach(
parameter =>
command.Parameters.Add(CreateParameter(parameter.ParameterName, parameter.Value)));
});
if (conditions.RequiredSearchItems.Any())
{
queryBuilder.AppendFormat(")");
}
first = true;
searchItems.ForEach(
delegate(SearchItem item)
{
if (first)
{
first = false;
queryBuilder.AppendFormat(conditions.RequiredSearchItems.Any() ? " AND (({0})" : " WHERE ({0})",
item.DetailSql);
}
else
{
queryBuilder.AppendFormat(" {0} ({1})", searchLogic, item.DetailSql);
}
item.Parameters.ForEach(
parameter =>
command.Parameters.Add(CreateParameter(parameter.ParameterName, parameter.Value)));
});
if (conditions.RequiredSearchItems.Any() && searchItems.Any())
{
queryBuilder.AppendFormat(")");
}
}
command.CommandText = queryBuilder.ToString();
connection.Open();
int count;
int.TryParse(Convert.ToString(command.ExecuteScalar()), out count);
return(count);
}
}
}
/// <summary>
/// Gets the record list for this instance's target type.
/// </summary>
/// <param name="conditions">The conditions.</param>
/// <returns></returns>
public List <T> GetRecords(QueryConditions conditions = null)
{
var records = new List <T>();
using (var connection = CreateConnection())
{
using (var command = CreateCommand(connection))
{
// Build and execute the main query
var queryBuilder = new StringBuilder("SELECT * FROM (SELECT a.*,");
if (conditions != null)
{
if (conditions.SortOptionSql != null)
{
queryBuilder.AppendFormat(" ROW_NUMBER() OVER (ORDER BY {0})", string.Join(", ", conditions.SortOptionSql.ToArray()));
}
else
{
queryBuilder.Append(GetRowNumber());
}
queryBuilder.AppendFormat(" rn FROM {0} a", TargetTable);
if (conditions.SearchItems != null || conditions.RequiredSearchItems.Any())
{
var first = true;
conditions.RequiredSearchItems.ForEach(delegate(SearchItem item)
{
if (first)
{
first = false;
queryBuilder.AppendFormat(" WHERE ({0}", item.DetailSql);
}
else
{
queryBuilder.AppendFormat(" AND {0}", item.DetailSql);
}
item.Parameters.ForEach(
parameter =>
command.Parameters.Add(CreateParameter(parameter.ParameterName, parameter.Value)));
});
if (conditions.RequiredSearchItems.Any())
{
queryBuilder.AppendFormat(")");
}
first = true;
conditions.SearchItems.ForEach(
delegate(SearchItem item)
{
if (first)
{
first = false;
queryBuilder.AppendFormat(
conditions.RequiredSearchItems.Any() ? " AND (({0})" : " WHERE ({0})", item.DetailSql);
}
else
{
queryBuilder.AppendFormat(" {0} ({1})", conditions.SearchLogic, item.DetailSql);
}
item.Parameters.ForEach(delegate(IDataParameter parameter) { command.Parameters.Add(parameter); });
});
if (conditions.RequiredSearchItems.Any() && conditions.SearchItems.Any())
{
queryBuilder.AppendFormat(")");
}
}
queryBuilder.Append(GetSubQueryTerminator());
if (conditions.RecordLimit > 0)
{
queryBuilder.AppendFormat(" WHERE rn > {0} AND rn <= {1}", conditions.SkipOffset, conditions.SkipOffset + conditions.RecordLimit);
}
}
else
{
queryBuilder.AppendFormat(" {0} rn FROM {1} a)", GetRowNumber(), TargetTable);
}
connection.Open();
command.CommandText = queryBuilder.ToString();
using (var reader = command.ExecuteReader())
{
while (reader.Read())
{
records.Add(FillModel(reader));
}
}
}
}
return(records);
}