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