public static SqlQuery Where(this SqlQuery query, ExpandoObject where, char prefix) { if (null != query && null != where) { IDictionary<string, object> dic = where; StringBuilder sql = query.Text; FilterCriteriaList criterias = new FilterCriteriaList(prefix); foreach (KeyValuePair<string, object> kvp in dic) { criterias.Add(kvp.Key, null, ConditionOperator.Equals, kvp.Value); } query.Combine(criterias.ToQuery()); } return query; }
public static SqlQuery CreateWhereSqlByKeys(IEntityMetaData metaData, char prefix, object entity) { if (null == entity) throw new ArgumentNullException("entity"); IList<PropertyMetaData> keySchemas = metaData.OfKeys(true); object[] keyValues = new object[keySchemas.Count]; for (int j = 0; j < keySchemas.Count; ++j) { keyValues[j] = keySchemas[j].Property.GetValue(entity, null); } FilterCriteriaList list = new FilterCriteriaList(prefix); for (int j = 0; j < keySchemas.Count; ++j) { PropertyMetaData keySchema = keySchemas[j]; list.Add(keySchema, ConditionOperator.Equals, keyValues[j]); } return list.ToQuery(); }
internal bool DataBind() { this.Keys.EnsureDbKeysValue(); SqlQuery query = this.CreateSelectQuery(); RecordTableMetaDataProvider metaDataProvider = new RecordTableMetaDataProvider(this.Template); IEntityMetaData metaData = metaDataProvider.CreateEntityMetaData(); FilterCriteriaList filters = new FilterCriteriaList(this.card.Factory.ParameterPrefix); IEnumerable<PropertyMetaData> keySchemas = metaData.OfKeys(true);//Order a göre geldiği için böyle. IEnumerator<DbKey> keys = this.Keys.GetEnumerator(); foreach (PropertyMetaData keyProperty in keySchemas) { keys.MoveNext(); filters.Add(keyProperty, ConditionOperator.Equals, keys.Current.Value); } char tableAlias = this.TableName[0]; string tableNameOp = tableAlias + "."; query.Combine(filters.ToQuery(tableNameOp)); bool readed = false; Record record = this.Template.NewRecord(); using (IDataReader dr = this.card.Factory.DataAccess.CreateDataReader(query, CommandBehavior.SingleRow)) { if (readed = dr.Read()) { int length = dr.FieldCount; for (int j = 0; j < length; ++j) { string columnName = dr.GetName(j); object dbValue = dr[j]; if (dbValue == DBNull.Value) dbValue = null; record[columnName] = dbValue; } } } if (readed) { using (Binding binding = new Binding(record, metaDataProvider, this.Controls)) { binding.DataBind(); } } else { foreach (IEntityControl ctrl in this.Controls) ctrl.Clear(); } return readed; }
public SqlQuery GenerateWhereQuery() { SqlQuery query = new SqlQuery(); StringBuilder text = query.Text; text.AppendLine(); text.Append("where "); FilterCriteriaList criteriaList = new FilterCriteriaList('@'); foreach (KeyValuePair<PropertyInfo, WebControl> kvp in this.SearchControls) { string value = GetControlValue(kvp.Value); if (value.Length != 0) { PropertyInfo pi = kvp.Key; if (!pi.NotMapped()) { ConditionOperator op = (ConditionOperator)Int32.Parse(this.FilterComboBoxes[pi].SelectedValue); FilterCriteria criteria = new FilterCriteria(pi.GetColumnName(), op, '@', value); criteriaList.Add(criteria); } } } if (criteriaList.Count != 0) { return criteriaList.ToQuery(DynamicSearchPage.TableAlias + '.'); } else return new SqlQuery(); }
[HttpPost]//Örneğin İki Tarih Arası İStenirse bunu bir attribute ile meta dataya göm ve sayı tarih vb tüm alanlar için Kullan. public virtual MvcResult<object> Search(SearchRequest request, int? take, int? page, SearchSortRequest[] sort)//Button Edit Search. Ek paramatreler kendo dan. { return this.ResultList(() => { Datas<object> ret = new Datas<object>(); Type entityType = null; // CacheMetaDataConfig.MetaData.TryGetType(request.TypeFullName, out entityType); if (null == entityType) throw new NullReferenceException(request.TypeFullName); object entity = JsonConvert.DeserializeObject(request.EntityJson, entityType); IEntityMetaData metaData = new MetaDataProvider().CreateEntityMetaData(entityType); ISqlQueryProvider builder = entity as ISqlQueryProvider; ; if (null == builder) { builder = new SqlQueryBuilderSelect(metaData); } const char prefix = ':'; FilterCriteriaList criteriaList = new FilterCriteriaList(prefix); HashSet<string> betweenColumns = new HashSet<string>(); IEnumerable<BetweenAttribute> bas = entityType.GetCustomAttributes<BetweenAttribute>(); if (!bas.IsEmptyList()) { foreach (BetweenAttribute ba in bas) { betweenColumns.Add(ba.FirstProperty); betweenColumns.Add(ba.SecondProperty); betweenColumns.Add(ba.ColumnName); //? object firstVal = entityType.GetProperty(ba.FirstProperty).GetValue(entity); if (!firstVal.IsNull()) { object secVal = entityType.GetProperty(ba.SecondProperty).GetValue(entity); if (secVal.IsNull()) secVal = firstVal; FilterCriteria criteria = new FilterCriteria(ba.ColumnName, ConditionOperator.Between, prefix, firstVal, secVal); criteriaList.Add(criteria); } } } SqlQuery query = builder.ToQuery().ToInnerQuery(); foreach (PropertyMetaData pmd in metaData.Properties) { if (betweenColumns.Contains(pmd.Schema.ColumnName)) continue; PropertyInfo pi = pmd.Property; object entityValue = pi.GetValue(entity); if (null != entityValue && entityValue.ToString().Length != 0) { object defaultValue = MvcExtensions.GetSearchDefaultValue(pi.PropertyType); if (!Object.Equals(defaultValue, entityValue))//Kullanıcı Değer Girdi İse. { FilterCriteria criteria = null; Type propertyType = pi.PropertyType; if (propertyType == CachedTypes.String) { criteria = new FilterCriteria(pi.Name, ConditionOperator.Contains, prefix, entityValue); } else { criteria = new FilterCriteria(pi.Name, ConditionOperator.Equals, prefix, entityValue); } criteriaList.Add(criteria); } } } if (!request.CustomFilters.IsEmptyList()) { foreach(CustomFilter filter in request.CustomFilters) { FilterCriteria criteria = filter.ToCriteria(prefix); if (null != criteria) { criteriaList.Add(filter.ToCriteria(prefix)); } } } if (criteriaList.Count != 0) { query.Combine(criteriaList.ToQuery("T.")); } SqlQuery nonPagingQuery = query; bool pagingEnabled = take.HasValue && page.HasValue; if (pagingEnabled)//Yani Sayfalama var ise ???.... { if (sort.IsEmptyList()) { PropertyMetaData pm = metaData.Properties.FirstOrDefault((p) => p.Schema.IsKey); if (null == pm)//Hiç pk yok ise. { pm = metaData.Properties.First(); } sort = new [] { new SearchSortRequest() { Field = pm.Schema.ColumnName, Dir = "asc"} }; } var fp = this.CreateFluentPaging(); fp.Select("*") .From("(" + query + ") TP") .OrderBy(sort.First().Field + " " + sort.First().Dir) .Page(page.Value) .Take(take.Value); SqlQuery temp = fp.ToQuery(); query.Parameters.ForEach((p) => { temp.Parameters.Add(p); }); query = temp; } ret.EntityList = (IEnumerable<object>)base.Cmd.Query(entityType, query); if (pagingEnabled) { SqlQuery countQuery = "SELECT COUNT(*) FROM ( ".ToQuery(); countQuery.Combine(nonPagingQuery); countQuery.Sql(" ) T"); ret.Total = base.Cmd.Factory.DataAccess.ExecuteScalarSafely<int>(countQuery); } return ret; }); }