/// <summary> /// 检测关系 /// </summary> /// <param name="dbInfo">数据库</param> /// <param name="table">要检测的表</param> /// <returns></returns> private static void CheckRelation(List <string> lstSql, DBInfo dbInfo, KeyWordTableParamItem table) { List <TableRelationAttribute> lstRelation = dbInfo.DBStructure.GetRelation(dbInfo.DefaultOperate, dbInfo, new string[] { table.TableName }); if (lstRelation == null) { return; } foreach (TableRelationAttribute item in table.RelationItems) { bool exists = false; foreach (TableRelationAttribute existsItem in lstRelation) { if (item.SourceName.Equals(existsItem.SourceName, StringComparison.CurrentCultureIgnoreCase)) { exists = true; break; } } if (!exists) { dbInfo.DBStructure.OnCheckEvent(table, dbInfo, CheckEvent.RelationBeginCheck, lstSql); item.CreateName(); BQLQuery bql = BQL.AlterTable(table.TableName).AddForeignkey(item); AbsCondition con = BQLKeyWordManager.ToCondition(bql, dbInfo, null, true); lstSql.Add(con.GetSql(false)); dbInfo.DBStructure.OnCheckEvent(table, dbInfo, CheckEvent.RelationChecked, lstSql); } } }
/// <summary> /// 直接查询数据库视图 /// </summary> /// <param name="table">表</param> /// <param name="lstScope">条件</param> /// <param name="vParams">字段列表</param> /// <param name="lstSort">排序类型</param> /// <param name="objPage">分页对象</param> /// <returns></returns> public DataSet SelectTable(BQLOtherTableHandle table, ScopeList lstScope) { List <BQLParamHandle> lstParams = GetParam(table, lstScope); List <BQLParamHandle> lstOrders = new List <BQLParamHandle>(); BQLParamHandle order = null; foreach (Sort objSort in lstScope.OrderBy) { order = table[objSort.PropertyName]; if (objSort.SortType == SortType.ASC) { order = order.ASC; } else { order = order.DESC; } lstOrders.Add(order); } BQLCondition where = BQLCondition.TrueValue; where = FillCondition(where, table, lstScope, null); BQLQuery bql = BQL.Select(lstParams.ToArray()).From(table).Where(where).OrderBy(lstOrders.ToArray()); if (lstScope.HasPage) { using (BatchAction ba = _oper.StarBatchAction()) { return(QueryDataSet(bql, null, lstScope.PageContent, lstScope.UseCache)); } } return(QueryDataSet(bql, null, lstScope.UseCache)); }
public static DataSet Search(string[] searchs, PageContent objPage) { ScopeList lstScope = new ScopeList(); lstScope.PageContent = objPage; double per = Math.Pow(2, searchs.Length); lstScope.ShowProperty.Add(Management.SampleInfo.SampleName.As("SampleName")); lstScope.ShowProperty.Add(Management.SampleInfo.SamplingTerrace.As("SamplingTerrace")); lstScope.ShowProperty.Add(Management.SampleInfo.SampleNumber.As("SampleNumber")); BQLValueItem perValue = BQLValueItem.ToValueItem(0); BQLCondition nameCon = BQLCondition.FalseValue; foreach (string search in searchs) { nameCon = nameCon | Management.SampleInfo.SampleName.Like(search); perValue = perValue + BQL.Case().When(BQL.ToParam("SampleName").IndexOf(search, 0) <= 0).Then(0).Else(1).End *per; per /= 2; } lstScope.ShowProperty.Add(perValue.As("per1")); lstScope.Add(nameCon); lstScope.OrderBy.Add(perValue.As("").DESC); per = Math.Pow(2, searchs.Length); perValue = BQLValueItem.ToValueItem(0); nameCon = BQLCondition.FalseValue; foreach (string search in searchs) { nameCon = nameCon | Management.SampleInfo.SamplingTerrace.Like(search); perValue = perValue + BQL.Case().When(BQL.ToParam("SamplingTerrace").IndexOf(search, 0) <= 0).Then(0).Else(1).End *per; per /= 2; } lstScope.ShowProperty.Add(perValue.As("per2")); lstScope.Add(nameCon, ConnectType.OR); lstScope.OrderBy.Add(perValue.As("").DESC); per = Math.Pow(2, searchs.Length); perValue = BQLValueItem.ToValueItem(0); nameCon = BQLCondition.FalseValue; foreach (string search in searchs) { nameCon = nameCon | Management.SampleInfo.SampleNumber.Like(search); perValue = perValue + BQL.Case().When(BQL.ToParam("SampleNumber").IndexOf(search, 0) <= 0).Then(0).Else(1).End *per; per /= 2; } lstScope.ShowProperty.Add(perValue.As("per3")); lstScope.Add(nameCon, ConnectType.OR); lstScope.OrderBy.Add(perValue.As("").DESC); //lstScope.OrderBy.Add(BQL.ToParam("per1").DESC); //lstScope.OrderBy.Add(BQL.ToParam("per2").DESC); //lstScope.OrderBy.Add(BQL.ToParam("per3").DESC); return(GetContext().Select(lstScope)); }
/// <summary> /// 获取别名字段 /// </summary> /// <param name="propertyName"></param> /// <returns></returns> public BQLParamHandle GetAliasParam(string propertyName) { BQLAliasParamHandle prm = null; BQLParamHandle ret = null; if (_dicParams.TryGetValue(propertyName, out prm)) { ret = BQL.ToParam(prm.AliasName); ret.ValueDbType = prm.ValueDbType; } return(ret); }
/// <summary> /// 查询表 /// </summary> /// <typeparam name="E"></typeparam> /// <param name="lstScope">条件</param> /// <returns></returns> public E GetUnique <E>(ScopeList lstScope) where E : EntityBase, new() { Type eType = typeof(E); BQLEntityTableHandle table = _oper.DBInfo.FindTable(eType); if (CommonMethods.IsNull(table)) { _oper.DBInfo.ThrowNotFondTable(eType); } List <BQLParamHandle> lstParams = GetParam(table, lstScope); BQLCondition where = BQLCondition.TrueValue; where = FillCondition(where, table, lstScope); BQLQuery bql = BQL.Select(lstParams.ToArray()) .From(table) .Where(where) .OrderBy(GetSort(lstScope.OrderBy, table)); return(GetUnique <E>(bql, lstScope.UseCache)); }
/// <summary> /// 获取范围表对应的BQL /// </summary> /// <param name="lstScope"></param> /// <param name="table"></param> /// <returns></returns> private BQLQuery GetSelectSql(ScopeList lstScope, BQLEntityTableHandle table) { List <BQLParamHandle> lstParams = GetParam(table, lstScope); BQLCondition where = BQLCondition.TrueValue; where = FillCondition(where, table, lstScope); BQLQuery bql = BQL.Select(lstParams.ToArray()) .From(table) .Where(where); if (lstScope.GroupBy.Count > 0) { bql = new KeyWordGroupByItem(lstScope.GroupBy, bql); } if (lstScope.OrderBy != null && lstScope.OrderBy.Count > 0) { bql = new KeyWordOrderByItem(GetSort(lstScope.OrderBy, table), bql); } return(bql); }
/// <summary> /// 检测表结构 /// </summary> /// <param name="lstSql">需要更新的SQL</param> /// <param name="dbInfo">数据库信息</param> /// <param name="table">要检测的表</param> private static void CheckTableStruct(List <string> lstSql, DBInfo dbInfo, KeyWordTableParamItem table) { string tableName = table.TableName; BQLQuery bql = BQL.Select(BQL.ToTable(tableName)._).From(BQL.ToTable(tableName)); SelectCondition con = BQLKeyWordManager.ToCondition(bql, dbInfo, null, true) as SelectCondition; string sql = dbInfo.CurrentDbAdapter.GetTopSelectSql(con, 1); Dictionary <string, bool> dic = new Dictionary <string, bool>(); using (IDataReader reader = dbInfo.DefaultOperate.Query(sql, new Buffalo.DB.DbCommon.ParamList(), null)) { for (int i = 0; i < reader.FieldCount; i++) { dic[reader.GetName(i).ToLower()] = true; } } StringBuilder sbSql = new StringBuilder(); string desSQL = null; IDBAdapter idb = dbInfo.CurrentDbAdapter; foreach (EntityParam pInfo in table.Params) { if (!dic.ContainsKey(pInfo.ParamName.ToLower())) { dbInfo.DBStructure.OnCheckEvent(pInfo, dbInfo, CheckEvent.TablenBeginCheck, lstSql); bql = BQL.AlterTable(tableName).AddParam(pInfo); AbsCondition acon = BQLKeyWordManager.ToCondition(bql, dbInfo, null, true); lstSql.Add(acon.GetSql(false)); if (!string.IsNullOrEmpty(pInfo.Description))//添加注释 { desSQL = idb.GetAddDescriptionSQL(table, pInfo, dbInfo); if (!string.IsNullOrEmpty(desSQL)) { lstSql.Add(desSQL); } } dbInfo.DBStructure.OnCheckEvent(pInfo, dbInfo, CheckEvent.TableChecked, lstSql); } } }
/// <summary> /// 查询是否存在符合条件的记录 /// </summary> /// <param name="BQL">sql语句</param> /// <returns></returns> public bool ExistsRecord <E>(ScopeList lstScope) where E : EntityBase, new() { Type eType = typeof(E); BQLEntityTableHandle table = _oper.DBInfo.FindTable(eType); if (CommonMethods.IsNull(table)) { _oper.DBInfo.ThrowNotFondTable(eType); } List <BQLParamHandle> lstParams = new List <BQLParamHandle>(); lstParams.Add(table[table.GetEntityInfo().PrimaryProperty[0].PropertyName]); BQLCondition where = BQLCondition.TrueValue; where = FillCondition(where, table, lstScope); BQLQuery bql = BQL.Select(lstParams.ToArray()) .From(table) .Where(where) .OrderBy(GetSort(lstScope.OrderBy, table)); return(ExistsRecord <E>(bql, lstScope.UseCache)); }
/// <summary> /// 查询总条数 /// </summary> /// <param name="lstScope"></param> /// <returns></returns> public virtual long SelectCount <E>(ScopeList lstScope) { long ret = 0; Type eType = typeof(E); TableAliasNameManager aliasManager = new TableAliasNameManager(new BQLEntityTableHandle(EntityInfoManager.GetEntityHandle(typeof(E)))); BQLEntityTableHandle table = _oper.DBInfo.FindTable(eType); if (CommonMethods.IsNull(table)) { _oper.DBInfo.ThrowNotFondTable(eType); } BQLCondition where = BQLCondition.TrueValue; where = FillCondition(where, table, lstScope); BQLQuery bql = BQL.Select(BQL.Count()) .From(table) .Where(where); //if(lstScope.GroupBy AbsCondition con = BQLKeyWordManager.ToCondition(bql, _oper.DBInfo, aliasManager, true); Dictionary <string, bool> cacheTables = null; if (lstScope.UseCache) { cacheTables = con.CacheTables; } using (IDataReader reader = _oper.Query(con.GetSql(lstScope.UseCache), con.DbParamList, cacheTables)) { if (reader.Read()) { ret = Convert.ToInt64(reader[0]); } } return(ret); }
/// <summary> /// 创建表的SQL /// </summary> /// <param name="sql">sql语句集合</param> /// <param name="dbInfo">数据信息</param> /// <param name="notExists">不存在的表</param> private static void CreateTableSQL(List <string> sql, DBInfo dbInfo, List <KeyWordTableParamItem> notExists) { IDBAdapter ida = dbInfo.CurrentDbAdapter; foreach (KeyWordTableParamItem table in notExists) { dbInfo.DBStructure.OnCheckEvent(table, dbInfo, CheckEvent.TableBeginCreate, sql); BQLQuery bql = BQL.CreateTable(table.TableName).Param(table.Params); AbsCondition con = BQLKeyWordManager.ToCondition(bql, dbInfo, null, true); sql.Add(con.GetSql(false)); if (!string.IsNullOrEmpty(table.Description))//设置表注释 { string desSQL = ida.GetAddDescriptionSQL(table, null, dbInfo); if (!string.IsNullOrEmpty(desSQL)) { sql.Add(desSQL); } } foreach (EntityParam prm in table.Params) //设置字段注释 { if (!string.IsNullOrEmpty(prm.Description)) { string desSQL = ida.GetAddDescriptionSQL(table, prm, dbInfo); if (!string.IsNullOrEmpty(desSQL)) { sql.Add(desSQL); } } } dbInfo.DBStructure.OnCheckEvent(table, dbInfo, CheckEvent.TableCreated, sql); } }
/// <summary> /// 直接查询数据库视图 /// </summary> /// <param name="tableName">表名称</param> /// <param name="lstScope">条件</param> /// <param name="vParams">字段列表</param> /// <returns></returns> public virtual DataSet SelectTable(string tableName, ScopeList lstScope) { return(SelectTable(BQL.ToTable(tableName), lstScope)); }
/// <summary> /// 添加一个Update设置 /// </summary> /// <param name="propertyName">属性</param> /// <param name="valueItem">值</param> public void Add(string propertyName, BQLValueItem valueItem) { Add(BQL.ToParam(propertyName), valueItem); }