/// <summary> /// 条件函数 /// </summary> /// <param name="sourceHandle">发送源(字段)</param> /// <param name="query">查询</param> /// <param name="handle">关联处理函数</param> public BQLConditionItem(BQLParamHandle sourceHandle, BQLQuery query, DelConditionHandle handle) { this._sourceHandle = sourceHandle; this._handle = handle; this._query = query; this._valueDbType = DbType.Boolean; }
/// <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)); }
/// <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> /// <typeparam name="E"></typeparam> /// <param name="lstScope">条件</param> /// <returns></returns> public List <E> SelectList <E>(ScopeList lstScope) where E : EntityBase, new() { Type eType = typeof(E); List <E> retlist = null; BQLEntityTableHandle table = _oper.DBInfo.FindTable(eType); if (CommonMethods.IsNull(table)) { _oper.DBInfo.ThrowNotFondTable(eType); } BQLQuery BQL = GetSelectSql(lstScope, table); if (!lstScope.HasPage) { retlist = QueryList <E>(BQL, lstScope.ShowEntity, lstScope.UseCache); DataAccessCommon.FillEntityChidList(retlist, lstScope); return(retlist); } using (BatchAction ba = _oper.StarBatchAction()) { retlist = QueryPageList <E>(BQL, lstScope.PageContent, lstScope.ShowEntity, lstScope.UseCache); DataAccessCommon.FillEntityChidList(retlist, lstScope); return(retlist); } }
/// <summary> /// 执行sql语句,分页返回Reader /// </summary> /// <param name="BQL">sql语句</param> /// <param name="objPage">分页对象</param> /// <param name="tableType">表对应的实体类型</param> public IDataReader QueryReader(BQLQuery BQL, PageContent objPage, Type tableType, bool useCache) { AbsCondition con = null; if (tableType == null) { con = BQLKeyWordManager.ToCondition(BQL, _oper.DBInfo, null, true); } else { con = ToCondition(BQL, new BQLEntityTableHandle[] { }, true, tableType); } Dictionary <string, bool> cacheTables = null; if (useCache) { cacheTables = con.CacheTables; } con.PageContent = objPage; IDataReader reader = null; con.PageContent = objPage; con.Oper = _oper; string sql = con.GetSql(useCache); reader = _oper.Query(sql, con.DbParamList, cacheTables); return(reader); }
/// <summary> /// 执行sql语句,分页返回DataSet /// </summary> /// <param name="BQL">sql语句</param> /// <param name="objPage">分页对象</param> public DataSet QueryDataSet(BQLQuery bql, Type tableType, PageContent objPage, bool useCache) { AbsCondition con = ToCondition(bql, null, true, tableType); Dictionary <string, bool> cacheTables = null; if (useCache) { cacheTables = con.CacheTables; } DataSet ds = null; using (BatchAction ba = _oper.StarBatchAction()) { if (con.DbParamList != null) { con.PageContent = objPage; con.Oper = _oper; string sql = con.GetSql(useCache); ds = _oper.QueryDataSet(sql, con.DbParamList, cacheTables); } else { SelectCondition sCon = con as SelectCondition; DataTable dt = con.DBinfo.CurrentDbAdapter.QueryDataTable(sCon.GetSelect(), objPage, _oper, null); dt.TableName = "newTable"; ds = new DataSet(); ds.Tables.Add(dt); } } return(ds); }
/// <summary> /// 查询是否存在符合条件的记录 /// </summary> /// <param name="BQL">sql语句</param> /// <returns></returns> public bool ExistsRecord <E>(BQLQuery BQL, bool useCache) where E : EntityBase, new() { Type tableType = typeof(E); AbsCondition con = ToCondition(BQL, null, true, tableType); string sql = con.DBinfo.CurrentDbAdapter.GetTopSelectSql(con as SelectCondition, 1); bool exists = false; IDataReader reader = null; Dictionary <string, bool> cacheTables = null; if (useCache) { cacheTables = con.CacheTables; } try { con.Oper = _oper; reader = _oper.Query(sql, con.DbParamList, cacheTables); exists = reader.Read(); } finally { if (reader != null) { reader.Close(); } } return(exists); }
/// <summary> /// 执行sql语句,分页返回DataSet /// </summary> /// <param name="BQL">sql语句</param> public DataSet QueryDataSet <E>(BQLQuery BQL, bool useCache) { AbsCondition con = ToCondition(BQL, null, true, typeof(E)); DataSet ds = null; Dictionary <string, bool> cacheTables = null; if (useCache) { cacheTables = con.CacheTables; } con.Oper = _oper; if (con.DbParamList != null) { ds = _oper.QueryDataSet(con.GetSql(useCache), con.DbParamList, cacheTables); } else { SelectCondition sCon = con as SelectCondition; DataTable dt = con.DBinfo.CurrentDbAdapter.QueryDataTable(sCon.GetSelect(), sCon.PageContent, _oper, null); dt.TableName = "newTable"; ds = new DataSet(); ds.Tables.Add(dt); } return(ds); }
/// <summary> /// 填充GroupBy /// </summary> /// <param name="BQL"></param> /// <param name="groupBy"></param> /// <returns></returns> protected static BQLQuery FillGroupBy(BQLQuery BQL, ScopePropertyCollection groupBy) { if (groupBy == null || groupBy.Count > 0) { return(BQL); } return(new KeyWordGroupByItem(groupBy.ToArray(), BQL)); }
/// <summary> /// 遍历关键字链,把它放进关键字栈(把倒置的关键字项转回来) /// </summary> /// <param name="item"></param> internal void CollectItem(BQLQuery item, KeyWordInfomation info) { BQLQuery curItem = item; while (curItem != null) { curItem.LoadInfo(info); stkKeyWord.Push(curItem); curItem = curItem.Previous; } }
///// <summary> ///// 加载From的别名表的信息 ///// </summary> ///// <param name="item"></param> ///// <param name="info"></param> //private void LoadParamInfo(BQLKeyWordItem item, KeyWordInfomation info) //{ // KeyWordSelectItem sitem = item as KeyWordSelectItem; // if (sitem!=null) // { // sitem.LoadParamInfo(info); // } //} /// <summary> /// 开始分析 /// </summary> /// <returns></returns> private AbsCondition DoConver(KeyWordInfomation info) { StringBuilder ret = new StringBuilder(2000); while (stkKeyWord.Count > 0) { BQLQuery item = stkKeyWord.Pop(); //LoadParamInfo(item, info); //ret.Append(item.Tran(info)) ; item.Tran(info); } return(info.Condition); }
/// <summary> /// 执行sql语句,返回Reader /// </summary> /// <param name="sql">sql语句</param> /// <param name="objPage">分页对象</param> public IDataReader QueryReader(ScopeList lstScope, Type tableType) { BQLEntityTableHandle table = _oper.DBInfo.FindTable(tableType); if (CommonMethods.IsNull(table)) { _oper.DBInfo.ThrowNotFondTable(tableType); } BQLQuery BQL = GetSelectSql(lstScope, table); return(QueryReader(BQL, null, lstScope.UseCache)); }
/// <summary> /// 执行Sql命令 /// </summary> /// <param name="BQL">sql语句</param> public int ExecuteCommand(BQLQuery BQL) { AbsCondition con = BQLKeyWordManager.ToCondition(BQL, _oper.DBInfo, null, true); Dictionary <string, bool> cacheTables = null; cacheTables = con.CacheTables; int ret = -1; con.Oper = _oper; ret = _oper.Execute(con.GetSql(true), con.DbParamList, cacheTables); return(ret); }
/// <summary> /// 执行sql语句,返回Reader /// </summary> /// <param name="sql">sql语句</param> /// <param name="objPage">分页对象</param> public IDataReader QueryReader(BQLQuery BQL, Type tableType, bool useCache) { AbsCondition con = ToCondition(BQL, null, true, tableType); Dictionary <string, bool> cacheTables = null; if (useCache) { cacheTables = con.CacheTables; } IDataReader reader = null; con.Oper = _oper; reader = _oper.Query(con.GetSql(useCache), con.DbParamList, cacheTables); return(reader); }
/// <summary> /// 转成条件信息 /// </summary> /// <param name="BQL"></param> /// <param name="db"></param> /// <param name="aliasManager"></param> /// <returns></returns> private AbsCondition ToCondition(BQLQuery BQL, IEnumerable <BQLEntityTableHandle> outPutTables, bool isPutPropertyName, Type entityType) { TableAliasNameManager aliasManager = null; if (entityType != null) { aliasManager = new TableAliasNameManager(new BQLEntityTableHandle(EntityInfoManager.GetEntityHandle(entityType))); } if (outPutTables != null) { FillOutPutTables(outPutTables, aliasManager); } AbsCondition con = BQLKeyWordManager.ToCondition(BQL, _oper.DBInfo, aliasManager, isPutPropertyName); return(con); }
/// <summary> /// 把传进来的值转换成BQL能识别的值项 /// </summary> /// <param name="value"></param> /// <returns></returns> public static BQLValueItem ToValueItem(object value) { BQLQuery query = value as BQLQuery; if (!CommonMethods.IsNull(query)) { return(query.AS(null)); } BQLValueItem item = value as BQLValueItem; if (CommonMethods.IsNull(item)) { item = new BQLValueTypeItem(value); } return(item); }
/// <summary> /// 执行sql语句,分页返回List /// </summary> /// <typeparam name="E">实体类型</typeparam> /// <param name="BQL">BQL</param> /// <param name="objPage">分页数据</param> /// <param name="outPutTables">输出表</param> /// <returns></returns> public List <E> QueryPageList <E>(BQLQuery BQL, PageContent objPage, IEnumerable <BQLEntityTableHandle> outPutTables, bool useCache) where E : EntityBase, new() { AbsCondition con = ToCondition(BQL, outPutTables, false, typeof(E)); con.PageContent = objPage; List <E> retlist = null; IDataReader reader = null; try { Dictionary <string, bool> cacheTables = null; if (useCache) { cacheTables = con.CacheTables; } if (con.DbParamList != null) { con.PageContent = objPage; con.Oper = _oper; string sql = con.GetSql(useCache); reader = _oper.Query(sql, con.DbParamList, cacheTables); } else { SelectCondition sCon = con as SelectCondition; reader = con.DBinfo.CurrentDbAdapter.Query(sCon.GetSelect(), objPage, _oper); } retlist = LoadFromReader <E>(con.AliasManager, reader); } finally { if (reader != null) { reader.Close(); } } return(retlist); }
/// <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> /// <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> /// 检测表结构 /// </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> /// <typeparam name="E"></typeparam> /// <param name="BQL"></param> /// <returns></returns> public E GetUnique <E>(BQLQuery BQL, bool useCache) where E : EntityBase, new() { Type tableType = typeof(E); TableAliasNameManager aliasManager = new TableAliasNameManager(new BQLEntityTableHandle(EntityInfoManager.GetEntityHandle(tableType))); AbsCondition con = BQLKeyWordManager.ToCondition(BQL, _oper.DBInfo, aliasManager, true); Dictionary <string, bool> cacheTables = null; if (useCache) { cacheTables = con.CacheTables; } string sql = con.DBinfo.CurrentDbAdapter.GetTopSelectSql(con as SelectCondition, 1); E ret = default(E); IDataReader reader = _oper.Query(sql, con.DbParamList, cacheTables); try { con.Oper = _oper; bool hasValue = true; aliasManager.InitMapping(reader); if (reader.Read()) { ret = aliasManager.LoadFromReader(reader) as E; } } finally { if (reader != null) { reader.Close(); } } return(ret); }
/// <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> /// 查询并返回DataSet /// </summary> /// <typeparam name="E"></typeparam> /// <param name="lstScope">条件集合</param> /// <returns></returns> public DataSet SelectDataSet <E>(ScopeList lstScope) { 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); // 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); // } BQLQuery BQL = GetSelectSql(lstScope, table); //.OrderBy(GetSort(lstScope.OrderBy, table)); if (!lstScope.HasPage) { return(QueryDataSet <E>(BQL, lstScope.UseCache)); } using (BatchAction ba = _oper.StarBatchAction()) { return(QueryDataSet <E>(BQL, lstScope.PageContent, lstScope.UseCache)); } }
/// <summary> /// 执行sql语句,返回List /// </summary> /// <typeparam name="E">实体类型</typeparam> /// <param name="BQL">BQL</param> /// <returns></returns> public virtual List <T> QueryList(BQLQuery BQL) { return(QueryList <T>(BQL, null, false)); }
/// <summary> /// In条件(如果集合为空,则返回1=2) /// </summary> /// <param name="lstParam"></param> /// <returns></returns> public BQLConditionItem In(BQLQuery item) { return(new BQLConditionItem(this, item, BQLConditionManager.DoIn)); }
/// <summary> /// 别名查询 /// </summary> /// <param name="query">查询</param> /// <param name="asName">别名</param> /// <returns></returns> public static BQLAliasHandle TableAs(BQLQuery query, string asName) { BQLAliasHandle item = new BQLAliasHandle(query, asName); return(item); }
public BQLAliasHandle(BQLQuery query, string aliasName) { this._query = query; this._aliasName = aliasName; }
/// <summary> /// 开始转换 /// </summary> /// <param name="item"></param> /// <returns></returns> internal AbsCondition ToConver(BQLQuery item, KeyWordInfomation info) { CollectItem(item, info); return(DoConver(info)); }