/// <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)); }