public static void AddParameter(this DbCommand command, PageSort item)
{
if (command != null)
{
command.Parameters.AddPageSort(item);
}
}
/// <summary>
///
/// </summary>
/// <param name="conditions"></param>
/// <returns></returns>
private static String getOrderby(Expression expression)
{
#region
string orderby = "";
string format = "";
if (expression.SqlConditions != null)
{
DBConditions conditions = expression.SqlConditions;
if (conditions.PageSorts != null &&
expression.SqlBusiness.IndexOf("count(*)") < 0)
{
for (int i = 0; i < conditions.PageSorts.Count; i++)
{
PageSort pagesort = conditions.PageSorts[i];
format = (i == 0) ? "{0} {1}" : ",{0} {1}";
orderby += string.Format(format,
pagesort.Fieldname, getAscOrDesc(pagesort.OrderByType));
}
if (conditions.PageSorts.Count > 0)
{
orderby = string.Format("order by {0}", orderby);
}
}
}
return(orderby);
#endregion
}
public IQueryable <KarmaItem> GetKarma(PageSort sort)
{
var query = Context.Karma.AsQueryable();
if (sort == PageSort.Asc)
{
return(query.OrderBy(o => o.Karma));
}
return(query.OrderByDescending(o => o.Karma));
}
public static void AddPageSort(this DbParameterCollection parameters, PageSort item)
{
if (item != null)
{
parameters.Add(new SqlParameter("PageNumber", item.PageNumber));
parameters.Add(new SqlParameter("PageSize", item.PageSize));
if (item.OrderBy != null && item.OrderBy.Count > 0)
{
var orderBy = item.OrderBy.First();
parameters.Add(new SqlParameter("Column", orderBy.Column));
parameters.Add(new SqlParameter("SortDirection", orderBy.Direction));
}
}
}
public SearchResults <T> ExecuteWithPaging(string commandText, Dictionary <string, object> parameters, CommandType commandType, PageSort pageSort)
{
Asserter.AssertIsNotNullOrEmptyString("commandText", commandText);
Asserter.AssertIsNotNull("parameters", parameters);
SearchResults <T> searchResults = new SearchResults <T>();
DbContext dbContext = (DbContext)_Context;
try
{
if (dbContext.Database.Connection.State != System.Data.ConnectionState.Open)
{
dbContext.Database.Connection.Open();
}
using (DbCommand dbCommand = dbContext.Database.Connection.CreateCommand())
{
dbCommand.CommandTimeout = CommonSettingsConfigSection.GetSection().DbContextCommandTimeout;
dbCommand.CommandText = commandText;
dbCommand.CommandType = commandType;
if (pageSort != null)
{
dbCommand.Parameters.AddPageSort(pageSort);
}
foreach (string key in parameters.Keys)
{
DbParameter dbParameter = dbCommand.CreateParameter();
dbParameter.ParameterName = key;
dbParameter.Value = parameters[key];
dbCommand.Parameters.Add(dbParameter);
}
using (DbDataReader dataReader = dbCommand.ExecuteReader())
{
if (dataReader.HasRows)
{
List <T> results = new List <T>();
Mapper.CreateMap <IDataReader, T>().IgnoreAllNonExisting();
results = Mapper.Map <IDataReader, List <T> >(dataReader);
if (results != null)
{
searchResults.Results = results;
}
else
{
searchResults.Results = new List <T>();
}
if (dataReader.NextResult())
{
if (dataReader.Read())
{
searchResults.RowsAffected = Convert.ToInt32(dataReader[0]);
}
else
{
throw new DataException("The query or stored procedure for a paged query must return TWO record sets, the record set of the actual records in the first record set and the number of total rows as a scalar value in the second record set.");
}
}
}
}
}
}
finally
{
if (dbContext.Database.Connection != null && dbContext.Database.Connection.State != ConnectionState.Closed)
{
dbContext.Database.Connection.Close();
}
}
return(searchResults);
}
