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