/// <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语句,分页返回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> /// 检测关系 /// </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> /// 执行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> /// 进行转换 /// </summary> /// <param name="info"></param> /// <param name="item"></param> /// <returns></returns> private static AbsCondition DoConver(KeyWordInfomation info, BQLQuery item) { KeyWordConver conver = new KeyWordConver(); AbsCondition con = conver.ToConver(item, info); con.AliasManager = info.AliasManager; con.DbParamList = info.ParamList; con.CacheTables = info.ContainTables; return(con); }
/// <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> /// 执行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> /// 检测表结构 /// </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> /// <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); } }