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