/// <summary>
/// Provided for query the data table using condition and order clause and pagination in a instance of DataTable.
/// </summary>
/// <param name="src">The instance of DataTable.</param>
/// <param name="sqlCriteria">the condition using the <typeparamref name="SqlCriteria"/></param>
/// <param name="orderbyClause">Order By Clause.</param>
/// <param name="pageIndex">the page index.</param>
/// <param name="pageSize">the page size.</param>
/// <param name="recordCount">the count of the result.</param>
/// <returns>the data in the provided condition for the parameter of src.</returns>
public static DataTable Query(DataTable src, SqlCriteria sqlCriteria, string orderbyClause, int pageIndex, int pageSize, ref int recordCount)
{
ThrowExceptionUtil.ArgumentNotNull(src, "src");
DataTable table = src;
DataRow[] rows = src.Select(sqlCriteria != null ? sqlCriteria.FilterClause : "", orderbyClause);
recordCount = rows.Length;
if (pageIndex >= 0 && pageSize > 0)
{
pageIndex = pageIndex == 0 ? 1 : pageIndex;
rows = rows.Skip((pageIndex - 1) * pageSize).Take(pageSize).ToArray();
}
if (rows.Length != 0)
{
table = rows.CopyToDataTable();
}
else
{
table = table.Clone();
}
return(table);
}
public void ApplyFiltersForCriteria(SqlCriteria anSqlCriteria, DBCommand <T> aCommand)
{
foreach (DBFilterBase <T> _filter in aCommand.Filters)
{
anSqlCriteria.Add(_filter.Expression());
}
}
public int Count <T>(SqlCriteria aCriteria, CachePreference aPreferences) where T : class, new()
{
int count = 0;
var query = QueryFactory.CreateSelectQuery();
query.SetStatement(
s =>
{
s.AddField(Factory.GetExpressionHelper <T>().GetCountField <T>());
s.AddFrom(Loader.GetClassMap <T>().GetTableName());
});
if (aCriteria == null)
{
aCriteria = Factory.CreateCriteria();
}
if (aPreferences == null)
{
aPreferences = new CachePreferenceImpl();
}
query.SetCriteria(aCriteria).SetCache(aPreferences);
IntBinder binder = new IntBinder();
binder.OnBind = delegate(IntResult aResult)
{
count = aResult.value;
};
Connetion.execute(query, binder);
return(count);
}
public void ApplyOrderForQuery(SqlCriteria anSqlCriteria, DBCommand <T> aCommand)
{
foreach (DBSort <T> _sort in aCommand.Sorts)
{
anSqlCriteria.AddOrder(_sort.GetOrder());
}
}
public virtual DataTable GetDByCriteria(SqlCriteria aCriteria = null, CachePreference aCachePreferences = null, OnRowSelect onSelect = null)
{
if (aCriteria == null)
{
aCriteria = GetCriteria();
}
return(ExecuteSelectQuery(GetQuery(aCachePreferences).SetCriteria(aCriteria), onSelect));
}
public List <T> Get <T>(SqlCriteria aCriteria = null, OnEntitySelect <T> onExecute = null, CachePreference aPreferences = null) where T : class, new()
{
if (aCriteria == null)
{
aCriteria = Factory.CreateCriteria();
}
return(Select <T>(GetQuery <T>(aPreferences).SetCriteria(aCriteria), new TemplateBinder <T>(), onExecute));
}
internal string MapSqlCriteria(Dictionary <string, string> propertyMappings)
{
if (PropertyName.IsEmpty() || PropertyName == "N/A")
{
return(SqlCriteria);
}
return(SqlCriteria.Replace($"${{{{{PropertyName}}}}}", propertyMappings[PropertyName]));
}
public virtual List <T> Get(SqlCriteria aCriteria = null, CachePreference aCachePreferences = null, OnEntitySelect <T> onSelect = null)
{
if (aCriteria == null)
{
aCriteria = GetCriteria();
}
return(ExecuteSelectQuery(GetQuery(aCachePreferences).SetCriteria(aCriteria), onSelect));
}
private void AppendCriteria <T>(SqlCriteria aCriteria, AppendCriteria aCriteriaAppender, PropertyMap aMap, T aData, bool isBindNull) where T : class, new()
{
object value = extractor.GetValue(aMap.Member.Name, aData);
if (!isBindNull && value == null)
{
return;
}
aCriteriaAppender(aCriteria, aMap, value);
}
public T GetSingle <T>(SqlCriteria aCriteria, CachePreference aPreferences) where T : class, new()
{
if (aCriteria == null)
{
aCriteria = Factory.CreateCriteria();
}
var _results = Select <T>(GetQuery <T>(aPreferences).SetCriteria(aCriteria).SetMaximum(1), null);
return(_results.Count > 0 ? _results.First() : null);
}
public virtual T GetSingle(SqlCriteria aSqlCriteria, CachePreference aCachePreference)
{
T data = null;
DBHelper.Execute(
delegate(DbSession aSession)
{
data = aSession.GetSingle <T>(aSqlCriteria, aCachePreference);
});
return(data);
}
public virtual int Delete(SqlCriteria aCriteria)
{
int result = 0;
DBHelper.Execute(
delegate(DbSession aSession)
{
result = aSession.Delete <T>(aCriteria);
});
CheckForResult(result, "Delete");
return(result);
}
internal override void Configure(DatabaseQuery query)
{
if (SqlCriteria.HasValue())
{
query.Where(new DirectDatabaseCriterion(SqlCriteria));
}
foreach (var item in Criterion.OrEmpty())
{
query.Where(item);
}
}
public virtual int Update(T aModel, SqlCriteria aCriteria, bool aWithNullValues = false)
{
int result = 0;
DBHelper.Execute(
delegate(DbSession aSession)
{
result = aSession.Update(aModel, aCriteria, aWithNullValues);
});
CheckForResult(result, "Update");
return(result);
}
public int ExecuteUpdatePartial <T>(T aUpdatedModel, bool isBindNull, T[] aModels, int aIndex, int aCount) where T : class, new()
{
SqlCriteria _criteria = Factory.CreateCriteria();
var _exp = new List <Expression>();
for (int i = aIndex; i < aCount; i++)
{
_exp.Add(GetByIdExpression(aModels[i]));
}
_criteria.Add(ExpressionHelper.Or(_exp.ToArray()));
Update <T>(aUpdatedModel, _criteria, isBindNull);
return(aCount);
}
private int ExecuteDeletePartial <T>(T[] aModels, int aIndex, int aCount) where T : class, new()
{
SqlCriteria _criteria = Factory.CreateCriteria();
var _exp = new List <Expression>();
for (int i = aIndex; i < aCount; i++)
{
_exp.Add(GetByIdExpression(aModels[i]));
}
_criteria.Add(ExpressionHelper.Or(_exp.ToArray()));
Delete <T>(_criteria);
return(aCount);
}
public int Update <T>(T aModel, SqlCriteria aCriteria, bool isNullBind) where T : class, new()
{
UpdateQuery <T> query = QueryFactory.CreateUpdateQuery <T>(aModel, isNullBind).SetCriteria(aCriteria);
int result = 0;
IntBinder binder = new IntBinder();
binder.OnBind =
delegate(IntResult aResult)
{
result = aResult.value;
};
Connetion.execute <IntResult>(query, binder);
return(result);
}
public static DataTable Suggest(DataTable src, string columns, SqlCriteria sqlCriteria, int pageSize)
{
int recordCount = 0;
DataTable dataTable = Query(src, sqlCriteria, null, 1, pageSize, ref recordCount);
Dictionary <string, string> mapping = new Dictionary <string, string>();
foreach (string item in columns.Split(new string[] { "," }, StringSplitOptions.RemoveEmptyEntries))
{
mapping.Add(item.Trim(), item.Trim());
}
return(Clone(dataTable, mapping, true));
}
public int Delete <T>(SqlCriteria aCriteria) where T : class, new()
{
DeleteQuery <T> query = QueryFactory.CreateDeleteQuery <T>();
query.SetCriteria(aCriteria);
int result = 0;
IntBinder binder = new IntBinder();
binder.OnBind =
delegate(IntResult aResult)
{
result = aResult.value;
};
Connetion.execute <IntResult>(query, binder);
return(result);
}
private int GetCount(SqlCriteria aCriteria = null, String aSqlQuery = null, CachePreference aCachePreferences = null)
{
int count = 0;
if (aSqlQuery != null)
{
aCriteria.AddSql(aSqlQuery);
}
DBHelper.Execute(
delegate(DbSession aSession)
{
count = aSession.Count <T>(aCriteria, aCachePreferences);
});
return(count);
}
/// <summary>
/// Basic query for pagination.
/// </summary>
/// <param name="tableName">the table name.</param>
/// <param name="selectClause">the select clauses.</param>
/// <param name="dataAdapter">the data.</param>
/// <param name="sqlCriteria">the condition.</param>
/// <param name="recordCount">the record count of the result.</param>
/// <returns>the result.</returns>
protected virtual DataTable InnerQuery(string tableName, string selectClause, BeeDataAdapter dataAdapter,
SqlCriteria sqlCriteria)
{
int pageNum = dataAdapter.TryGetValue <int>("pagenum", 1);
int pageSize = dataAdapter.TryGetValue <int>("pagesize", 20);
int recordCount = dataAdapter.TryGetValue <int>("recordcount", 0);
string orderField = dataAdapter.TryGetValue <string>("orderField", "Id");
string orderDirection = dataAdapter.TryGetValue <string>("orderDirection", "desc");
DataTable result = DbSession.Current.Query(tableName, selectClause, sqlCriteria,
"{0} {1}".FormatWith(orderField, orderDirection), pageNum, pageSize, ref recordCount);
dataAdapter["recordcount"] = recordCount;
return(result);
}
protected void AddCondition(SqlCriteria sqlCriteria, ModelQueryType queryType, string name, object value)
{
switch (queryType)
{
case ModelQueryType.Contains:
sqlCriteria.Contains(name, value.ToString());
break;
case ModelQueryType.EndWith:
sqlCriteria.EndWith(name, value.ToString());
break;
case ModelQueryType.Equal:
sqlCriteria.Equal(name, value);
break;
case ModelQueryType.GreaterThan:
sqlCriteria.GreaterThan(name, value);
break;
case ModelQueryType.GreaterThanOrEqual:
sqlCriteria.GreaterThanOrEqual(name, value);
break;
case ModelQueryType.LessThan:
sqlCriteria.LessThan(name, value);
break;
case ModelQueryType.LessThanOrEqual:
sqlCriteria.LessThanOrEqual(name, value);
break;
case ModelQueryType.StartWith:
sqlCriteria.StartWith(name, value.ToString());
break;
case ModelQueryType.In:
sqlCriteria.In(name, value.ToString());
break;
default:
break;
}
}
public override PageResult Index(BeeDataAdapter dataAdapter)
{
DbSession dbSession = GetDbSession();
DataTable dataTable = null;
try
{
InitPagePara(dataAdapter);
EntityProxy <WeiXinUser> entityProxy = EntityProxyManager.Instance.GetEntityProxy <WeiXinUser>();
SqlCriteria sqlCriteria = GetQueryCondition(dataAdapter);
if (dataAdapter.ContainsKey("linkflag"))
{
bool linkFlag = dataAdapter.TryGetValue <Boolean>("linkflag", false);
if (!linkFlag)
{
sqlCriteria.Equal("fakeid", "");
}
else
{
sqlCriteria.NotEqual("fakeid", "");
}
}
string selectClause = GetQuerySelectClause(typeof(WeiXinUser));
dataTable = InnerQuery("WeiXinUser", selectClause, dataAdapter, sqlCriteria);
}
catch (Exception e)
{
Logger.Error("List object({0}) Error".FormatWith(typeof(WeiXinUser)), e);
}
finally
{
dbSession.Dispose();
}
return(View(dataTable));
}
public IEnumerable <Contacts> GetDataPager(SqlCriteria criteria)
{
List <Contacts> lst = new List <Contacts>();
try
{
using (SafeDataReader dr = new SafeDataReader(this.ExecuteReader(criteria.GenSqlScript(), criteria.GenSqlParams())))
{
while (dr.Read())
{
lst.Add(this.ReaderToModel(dr));
}
}
}
catch (Exception ex)
{
throw ex;
}
return(lst);
}
protected void QuickAddCondition(SqlCriteria sqlCriteria, ModelQueryType queryType, string name, BeeDataAdapter dataAdapter)
{
if (queryType == ModelQueryType.Between)
{
if (dataAdapter.ContainsKey(name + "begin"))
{
sqlCriteria.GreaterThanOrEqual(name, dataAdapter[name + "begin"]);
}
if (dataAdapter.ContainsKey(name + "end"))
{
sqlCriteria.GreaterThanOrEqual(name, dataAdapter[name + "end"]);
}
}
else
{
if (dataAdapter.ContainsKey(name))
{
AddCondition(sqlCriteria, queryType, name, dataAdapter[name]);
}
}
}
public string MappingAll(string mappingName, string valuePropertyFormat, SqlCriteria sqlCriteria)
{
StringBuilder builder = new StringBuilder();
object cacheValue = GetCachedMappingValue(mappingName);
if (cacheValue != null)
{
DataTable tableValue = cacheValue as DataTable;
if (tableValue != null)
{
if (sqlCriteria != null)
{
try
{
DataRow[] rows = tableValue.Select(sqlCriteria.FilterClause);
foreach (DataRow item in rows)
{
builder.AppendLine(valuePropertyFormat.RazorFormat(item));
}
}
catch (Exception)
{
// do nothing here.
}
}
else
{
foreach (DataRow item in tableValue.Rows)
{
builder.AppendLine(valuePropertyFormat.RazorFormat(item));
}
}
}
}
return(builder.ToString());
}
public string ForSelect(string name, DataTable data, string valuePropertyName, SqlCriteria sqlCriteria, bool appendAll)
{
StringBuilder builder = new StringBuilder();
string value = string.Empty;
if (owner.ViewData[name] != null)
{
value = "svalue='{0}'".FormatWith(owner.ViewData[name]);
}
builder.AppendFormat("<select name='{0}' class='combox {2}' {1}>", name, value, owner.ViewData.ContainsKey(Constants.BeeReadonly) ? "readonly" : "");
if (appendAll)
{
builder.Append("<option value=''>请选择</option>");
}
if (data != null && data.Rows.Count > 0)
{
data = DataUtil.Query(data, sqlCriteria);
foreach (DataRow item in data.Rows)
{
builder.Append(valuePropertyName.RazorFormat(item));
}
}
builder.Append("</select>");
return(builder.ToString());
}
public string ForSelect(string name, string mappingName, SqlCriteria sqlCriteria, bool appendAll)
{
return(ForSelect(name, mappingName, "<option value='@id'>@name</option>", sqlCriteria, appendAll));
}
public string ForSelect(string name, string mappingName, string valuePropertyName, SqlCriteria sqlCriteria, bool appendAll)
{
DataTable data = DataMapping.Instance.GetMapping(mappingName) as DataTable;
return(ForSelect(name, data, valuePropertyName, sqlCriteria, appendAll));
}
public string ForSelect(string name, string mappingName, string valuePropertyName, SqlCriteria sqlCriteria)
{
return(ForSelect(name, mappingName, valuePropertyName, sqlCriteria, true));
}
