/// <summary>
/// 根据ID删除记录
/// </summary>
/// <param name="id">要删除的记录ID</param>
/// <returns></returns>
public int DeleteById(object id)
{
int ret = -1;
DeleteCondition con = new DeleteCondition(EntityInfo.DBInfo);
con.Tables.Append(EntityInfo.DBInfo.CurrentDbAdapter.FormatTableName(EntityInfo.TableName));
ParamList list = new ParamList();
ScopeList lstScope = new ScopeList();
PrimaryKeyInfo pkInfo = id as PrimaryKeyInfo;
if (pkInfo == null)
{
lstScope.AddEqual(EntityInfo.PrimaryProperty[0].PropertyName, id);
}
else
{
pkInfo.FillScope(EntityInfo.PrimaryProperty, lstScope, true);
}
con.Condition.Append("1=1");
con.Condition.Append(DataAccessCommon.FillCondition(EntityInfo, list, lstScope));
Dictionary <string, bool> cacheTables = null;
cacheTables = _oper.DBInfo.QueryCache.CreateMap(EntityInfo.TableName);
ret = ExecuteCommand(con.GetSql(true), list, CommandType.Text, cacheTables);
return(ret);
}
/// <summary>
/// 解释Condition条件
/// </summary>
/// <param name="scope">条件</param>
/// <param name="list"></param>
/// <param name="entityType"></param>
/// <param name="paramName"></param>
/// <param name="type"></param>
/// <param name="lstIndex"></param>
public static string Condition(Scope scope, ParamList list, Type entityType, string paramName, DbType type, int lstIndex)
{
DBInfo db = EntityInfoManager.GetEntityHandle(entityType).DBInfo;
string paramVal = db.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(paramName, lstIndex));
string paramKey = db.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(paramName, lstIndex));
StringBuilder sql = new StringBuilder(500);
ScopeType ctype = scope.ScopeType;
string connectString = DataAccessCommon.GetConnectString(scope);
BQLCondition fhandle = scope.Value1 as BQLCondition;
if (!CommonMethods.IsNull(fhandle))
{
KeyWordInfomation info = new KeyWordInfomation();
info.Infos = new Buffalo.DB.BQLCommon.BQLInfos();
info.DBInfo = db;
info.ParamList = list;
sql.Append(" ");
sql.Append(connectString);
sql.Append(" (" + fhandle.DisplayValue(info) + ")");
}
return(sql.ToString());
}
/// <summary>
/// 解释EndWith条件
/// </summary>
/// <param name="list"></param>
/// <param name="entityType"></param>
/// <param name="paramName"></param>
/// <param name="type"></param>
/// <param name="lstIndex"></param>
public static string EndWith(Scope scope, ParamList list, Type entityType, string paramName, DbType type, int lstIndex)
{
DBInfo db = EntityInfoManager.GetEntityHandle(entityType).DBInfo;
string paramVal = db.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(paramName, lstIndex));
string paramKey = db.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(paramName, lstIndex));
StringBuilder sql = new StringBuilder(500);
ScopeType ctype = scope.ScopeType;
string connectString = DataAccessCommon.GetConnectString(scope);
if (list != null)
{
sql.Append(" ");
sql.Append(connectString);
sql.Append(" (" + db.CurrentDbAdapter.FormatParam(paramName) + " like " + db.CurrentDbAdapter.ConcatString("'%'", paramVal) + ")");
list.AddNew(paramKey, type, scope.Value1);
}
else
{
sql.Append(" ");
sql.Append(connectString);
string curValue = scope.Value1.ToString();
sql.Append(" (" + db.CurrentDbAdapter.FormatParam(paramName) + " like '%" + scope.Value1 + "')");
}
return(sql.ToString());
}
/// <summary>
/// 解释MoreThen条件
/// </summary>
/// <param name="list"></param>
/// <param name="entityType"></param>
/// <param name="paramName"></param>
/// <param name="type"></param>
/// <param name="lstIndex"></param>
public static string MoreThen(Scope scope, ParamList list, Type entityType, string paramName, DbType type, int lstIndex)
{
DBInfo db = EntityInfoManager.GetEntityHandle(entityType).DBInfo;
string paramVal = db.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(paramName, lstIndex));
string paramKey = db.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(paramName, lstIndex));
StringBuilder sql = new StringBuilder(500);
ScopeType ctype = scope.ScopeType;
string connectString = DataAccessCommon.GetConnectString(scope);
if (list != null)
{
sql.Append(" ");
sql.Append(connectString);
sql.Append(" (");
sql.Append(db.CurrentDbAdapter.FormatParam(paramName));
sql.Append(" >= ");
sql.Append(paramVal);
sql.Append(")");
list.AddNew(paramKey, type, scope.Value1);
}
else
{
sql.Append(" ");
sql.Append(connectString);
sql.Append(" (");
sql.Append(db.CurrentDbAdapter.FormatParam(paramName));
sql.Append(" >= ");
sql.Append(DataAccessCommon.FormatValue(scope.Value1, type, db));
sql.Append(")");
}
return(sql.ToString());
}
/// <summary>
/// 解释NotIN条件(如果集合为空，则返回1=1)
/// </summary>
/// <param name="list"></param>
/// <param name="entityType"></param>
/// <param name="paramName"></param>
/// <param name="type"></param>
/// <param name="lstIndex"></param>
public static string NotIN(Scope scope, ParamList list, Type entityType, string paramName, DbType type, int lstIndex)
{
DBInfo db = EntityInfoManager.GetEntityHandle(entityType).DBInfo;
string paramVal = db.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(paramName, lstIndex));
string paramKey = db.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(paramName, lstIndex));
StringBuilder sql = new StringBuilder(500);
ScopeType ctype = scope.ScopeType;
string connectString = DataAccessCommon.GetConnectString(scope);
string inValue = null;
inValue = GetInString(scope.Value1, type, db);
if (inValue != "" && inValue != null)
{
sql.Append(" ");
sql.Append(connectString);
sql.Append(" (");
sql.Append(db.CurrentDbAdapter.FormatParam(paramName));
sql.Append(" not in (");
sql.Append(inValue);
sql.Append("))");
}
else //没有数据时候设置改条件不成立
{
sql.Append(" ");
sql.Append(connectString);
sql.Append(" 1=1");
}
return(sql.ToString());
}
/// <summary>
/// 获取全部查询的条件
/// </summary>
/// <param name="list">参数列表</param>
/// <param name="scopeList">范围查找的集合</param>
/// <returns></returns>
protected string GetSelectPageContant(ParamList list, ScopeList scopeList)
{
SelectCondition condition = new SelectCondition(CurEntityInfo.DBInfo);
condition.Oper = this._oper;
condition.Tables.Append(CurEntityInfo.DBInfo.CurrentDbAdapter.FormatTableName(CurEntityInfo.TableName));
condition.SqlParams.Append(GetSelectParams(scopeList));
if (scopeList.UseCache)
{
condition.CacheTables = CurEntityInfo.DBInfo.QueryCache.CreateMap(CurEntityInfo.TableName);
}
condition.Condition.Append("1=1");
foreach (EntityPropertyInfo ep in CurEntityInfo.PrimaryProperty)
{
condition.PrimaryKey.Add(CurEntityInfo.DBInfo.CurrentDbAdapter.FormatParam(ep.ParamName));
}
string conditionWhere = "";
SortList sortList = scopeList.OrderBy;
if (scopeList != null)
{
condition.Condition.Append(DataAccessCommon.FillCondition(CurEntityInfo, list, scopeList));
}
if (conditionWhere.Length > 0)
{
condition.Condition.Append(conditionWhere);
}
//排序方式
if (sortList != null && sortList.Count > 0)
{
string orderBy = GetSortCondition(sortList);
if (orderBy != "")
{
if (condition.Orders.Length > 0)
{
condition.Orders.Append("," + orderBy);
}
else
{
condition.Orders.Append(orderBy);
}
}
}
condition.PageContent = scopeList.PageContent;
//throw new Exception("");
condition.DbParamList = list;
//if (scopeList.UseCache)
//{
// cachetables[CurEntityInfo.DBInfo.CurrentDbAdapter.FormatTableName(CurEntityInfo.TableName)]=true;
//}
return(condition.GetSql(true));
}
/// <summary>
/// 解释Contains条件
/// </summary>
/// <param name="list"></param>
/// <param name="entityType"></param>
/// <param name="paramName"></param>
/// <param name="type"></param>
/// <param name="lstIndex"></param>
public static string Contains(Scope scope, ParamList list, Type entityType, string paramName, DbType type, int lstIndex)
{
DBInfo db = EntityInfoManager.GetEntityHandle(entityType).DBInfo;
string paramVal = db.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(paramName, lstIndex));
string paramKey = db.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(paramName, lstIndex));
StringBuilder sql = new StringBuilder(500);
ScopeType ctype = scope.ScopeType;
string connectString = DataAccessCommon.GetConnectString(scope);
sql.Append(FullTextConfigManager.GetLikeSql(scope, list, paramName, type, lstIndex, entityType, connectString, true));
return(sql.ToString());
}
/// <summary>
/// 获取全部查询的条件
/// </summary>
/// <param name="list">参数列表</param>
/// <param name="scopeList">范围查找的集合</param>
/// <param name="param">输出字段</param>
/// <returns></returns>
protected SelectCondition GetSelectContant(ParamList list, ScopeList scopeList, string param)
{
string conditionWhere = "";
string orderBy = "";
SelectCondition condition = new SelectCondition(CurEntityInfo.DBInfo);
if (condition.SqlParams.Length > 0)
{
condition.SqlParams.Append(",");
}
condition.SqlParams.Append(param);
condition.Tables.Append(CurEntityInfo.DBInfo.CurrentDbAdapter.FormatTableName(CurEntityInfo.TableName));
condition.Condition.Append("1=1");
if (scopeList.UseCache)
{
condition.CacheTables = CurEntityInfo.DBInfo.QueryCache.CreateMap(CurEntityInfo.TableName);
}
if (scopeList != null)
{
condition.Condition.Append(DataAccessCommon.FillCondition(CurEntityInfo, list, scopeList));
}
if (conditionWhere.Length > 0)
{
condition.Condition.Append(conditionWhere);
}
SortList sortList = scopeList.OrderBy;
if (sortList != null && sortList.Count > 0)
{
if (orderBy != "")
{
orderBy = orderBy + "," + GetSortCondition(sortList);
}
else
{
orderBy = GetSortCondition(sortList);
}
}
if (orderBy != "")
{
condition.Orders.Append(orderBy);
}
condition.DbParamList = list;
return(condition);
}
/// <summary>
/// 根据数据类型自动拼合in字符串
/// </summary>
/// <param name="collection">值集合</param>
/// <param name="type">类型</param>
/// <returns></returns>
private static string GetInString(object collection, DbType type, DBInfo info)
{
IEnumerable enumValues = (IEnumerable)collection;
string ret = "";
foreach (object item in enumValues)
{
ret += DataAccessCommon.FormatValue(item, type, info) + ",";
}
if (ret.Length > 0)
{
ret = ret.Substring(0, ret.Length - 1);
}
return(ret);
}
/// <summary>
/// 分页查询表(返回List)
/// </summary>
/// <param name="scopeList">范围查找的集合</param>
/// <returns></returns>
public List <T> SelectList(ScopeList scopeList)
{
if (scopeList.HasPage)
{
if (!scopeList.HasSort)
{
foreach (EntityPropertyInfo pInfo in CurEntityInfo.PrimaryProperty)
{
scopeList.OrderBy.Add(pInfo.PropertyName, SortType.ASC);
}
}
}
if (scopeList.HasInner)
{
return(_cdal.SelectList <T>(scopeList));
}
ParamList list = null;
list = new ParamList();
string sql = null;
using (BatchAction ba = Oper.StarBatchAction())
{
if (!scopeList.HasPage)//判断是否分页查询
{
sql = GetSelectContant(list, scopeList, GetSelectParams(scopeList)).GetSql(scopeList.UseCache);
}
else
{
sql = GetSelectPageContant(list, scopeList);
}
List <T> retlist = null;
Dictionary <string, bool> cacheTables = null;
if (scopeList.UseCache)
{
cacheTables = _oper.DBInfo.QueryCache.CreateMap(CurEntityInfo.TableName);
}
retlist = QueryList(sql, list, CommandType.Text, cacheTables);
DataAccessCommon.FillEntityChidList(retlist, scopeList);
return(retlist);
}
}
/// <summary>
/// 填充版本控制的信息
/// </summary>
/// <param name="where"></param>
/// <param name="where"></param>
/// <param name="info"></param>
/// <param name="list"></param>
/// <param name="curValue"></param>
protected internal void FillWhereConcurrency(StringBuilder where,
EntityPropertyInfo info, ParamList list, object curValue, ref int index)
{
string paramValW = EntityInfo.DBInfo.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(info.ParamName, index));
string paramKeyW = EntityInfo.DBInfo.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(info.ParamName, index));
index++;
if (DefaultType.IsDefaultValue(curValue))
{
throw new Exception("版本控制字段:" + info.PropertyName + " 必须有当前版本值");
}
where.Append(" and ");
where.Append(EntityInfo.DBInfo.CurrentDbAdapter.FormatParam(info.ParamName));
where.Append("=");
where.Append(paramValW);
list.AddNew(paramKeyW, info.SqlType, curValue);
}
/// <summary>
/// 删除指定数据
/// </summary>
/// <param name="obj">要删除的实体</param>
/// <param name="scopeList">要删除的条件</param>
/// <param name="isConcurrency">是否版本并发删除</param>
/// <returns></returns>
public int Delete(EntityBase obj, ScopeList scopeList, bool isConcurrency)
{
DeleteCondition con = new DeleteCondition(EntityInfo.DBInfo);
con.Tables.Append(EntityInfo.DBInfo.CurrentDbAdapter.FormatTableName(EntityInfo.TableName));
ParamList list = new ParamList();
Type type = EntityInfo.EntityType;
con.Condition.Append("1=1");
if (obj != null)
{
if (scopeList == null)//通过ID删除
{
scopeList = new ScopeList();
foreach (EntityPropertyInfo info in EntityInfo.PrimaryProperty)
{
scopeList.AddEqual(info.PropertyName, info.GetValue(obj));
}
}
}
con.Condition.Append(DataAccessCommon.FillCondition(EntityInfo, list, scopeList));
if (isConcurrency)
{
int index = 0;
foreach (EntityPropertyInfo pInfo in EntityInfo.PropertyInfo)
{
if (pInfo.IsVersion)
{
FillWhereConcurrency(con.Condition, pInfo, list, pInfo.GetValue(obj), ref index);
}
}
}
Dictionary <string, bool> cacheTables = null;
cacheTables = _oper.DBInfo.QueryCache.CreateMap(EntityInfo.TableName);
int ret = -1;
ret = ExecuteCommand(con.GetSql(true), list, CommandType.Text, cacheTables);
return(ret);
}
/// <summary>
/// 填充查询条件并返回条件的SQL语句( and 开头)
/// </summary>
/// <param name="lstParam">参数列表</param>
/// <param name="lstScope">范围查询集合</param>
/// <param name="CurEntityInfo">当前实体信息</param>
/// <param name="index">索引</param>
/// <returns></returns>
internal static string FillCondition(EntityInfoHandle curEntityInfo, ParamList lstParam, ScopeList lstScope, ref int index)
{
if (lstScope == null)
{
return("");
}
StringBuilder ret = new StringBuilder();
for (int i = 0; i < lstScope.Count; i++)
{
Scope objScope = lstScope[i];
EntityPropertyInfo info = null;
if (!string.IsNullOrEmpty(objScope.PropertyName))
{
info = curEntityInfo.PropertyInfo[objScope.PropertyName];
}
if (objScope.ScopeType == ScopeType.Scope)
{
ScopeList lstInnerScope = objScope.Value1 as ScopeList;
if (lstInnerScope != null)
{
string strSql = FillCondition(curEntityInfo, lstParam, lstInnerScope, ref index);
string connectString = DataAccessCommon.GetConnectString(objScope);
StringBuilder sbstrSQL = new StringBuilder(strSql);
DataAccessCommon.TrimAnd(sbstrSQL);
ret.Append(" ");
ret.Append(connectString);
ret.Append(" (" + sbstrSQL.ToString() + ")");
}
}
else
{
string pName = (info != null ? info.ParamName : "");
DbType dbType = (info != null ? info.SqlType : DbType.Object);
ret.Append(FormatScorp(objScope, lstParam, pName, dbType, index, curEntityInfo.EntityType));
}
index++;
}
return(ret.ToString());
}
/// <summary>
/// 修改记录
/// </summary>
/// <param name="obj">修改的对象</param>
/// <param name="scopeList">条件列表</param>
/// <param name="setList">Set值列表</param>
/// <param name="optimisticConcurrency">是否进行并发控制</param>
/// <returns></returns>
public int Update(EntityBase obj, ScopeList scopeList, ValueSetList setList, bool optimisticConcurrency)
{
StringBuilder sql = new StringBuilder(500);
ParamList list = new ParamList();
StringBuilder where = new StringBuilder(500);
//where.Append("1=1");
Type type = EntityInfo.EntityType;
List <VersionInfo> lstVersionInfo = null;
int index = 0;
KeyWordInfomation keyinfo = BQLValueItem.GetKeyInfo().Clone() as KeyWordInfomation;
keyinfo.ParamList = list;
keyinfo.OutPutModle = false;
if (obj != null)
{
if (!(obj is IEntityProxy))
{
throw new System.InvalidCastException("Update的实体类型必须为代理类，请用CH.Create<T>创建实体或者使用查询出来的实体来更新");
}
///读取属性别名
foreach (EntityPropertyInfo info in EntityInfo.PropertyInfo)
{
object curValue = info.GetValue(obj);
if (!info.ReadOnly)
{
if (optimisticConcurrency == true && info.IsVersion) //并发控制
{
object newValue = FillUpdateConcurrency(sql, info, list, curValue, ref index);
FillWhereConcurrency(where, info, list, curValue, ref index);
if (lstVersionInfo == null)
{
lstVersionInfo = new List <VersionInfo>();
}
lstVersionInfo.Add(new VersionInfo(info, curValue, newValue));//添加信息
}
else
{
//string paramVal = CurEntityInfo.DBInfo.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(info.ParamName, index));
//string paramKey = CurEntityInfo.DBInfo.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(info.ParamName, index));
if (info.IsNormal)
{
//if (obj._dicUpdateProperty___ == null || obj._dicUpdateProperty___.Count == 0)
//{
// //if (DefaultType.IsDefaultValue(curValue))
// //{
// continue;
// //}
//}
if (!obj.HasPropertyChange(info.PropertyName))
{
continue;
}
if (setList != null)
{
BQLValueItem bvalue = null;
if (setList.TryGetValue(info.PropertyName, out bvalue))
{
continue;
}
}
sql.Append(",");
sql.Append(EntityInfo.DBInfo.CurrentDbAdapter.FormatParam(info.ParamName));
sql.Append("=");
if (curValue != null)
{
DBParameter dbPrm = list.NewParameter(info.SqlType, curValue, EntityInfo.DBInfo);
sql.Append(dbPrm.ValueName);
}
else
{
sql.Append("null");
}
}
}
}
if (info.IsPrimaryKey && scopeList == null)//当不强制指定条件时候，用主键做更新条件
{
//if (DefaultType.IsDefaultValue(curValue))
//{
// continue;
//}
//if (obj._dicUpdateProperty___ != null)
//{
// if (!obj._dicUpdateProperty___.ContainsKey(info.PropertyName))
// {
// continue;
// }
//}
DBParameter dbPrm = list.NewParameter(info.SqlType, curValue, EntityInfo.DBInfo);
where.Append(" and ");
where.Append(EntityInfo.DBInfo.CurrentDbAdapter.FormatParam(info.ParamName));
where.Append("=");
where.Append(dbPrm.ValueName);
//primaryKeyValue=curValue;
}
index++;
}
}
if (setList != null)
{
foreach (KeyValuePair <string, BQLValueItem> kvp in setList)
{
EntityPropertyInfo epinfo = EntityInfo.PropertyInfo[kvp.Key];
if (epinfo == null)
{
throw new Exception("实体:" + EntityInfo.EntityType.FullName + " 找不到属性:" + kvp.Key);
}
sql.Append(",");
sql.Append(EntityInfo.DBInfo.CurrentDbAdapter.FormatParam(epinfo.ParamName));
sql.Append("=");
sql.Append(kvp.Value.DisplayValue(keyinfo));
}
}
where.Append(DataAccessCommon.FillCondition(EntityInfo, list, scopeList));
if (sql.Length <= 0)
{
return(0);
}
else
{
sql.Remove(0, 1);
}
UpdateCondition con = new UpdateCondition(EntityInfo.DBInfo);
con.Tables.Append(EntityInfo.DBInfo.CurrentDbAdapter.FormatTableName(EntityInfo.TableName));
con.UpdateSetValue.Append(sql);
con.Condition.Append("1=1");
con.Condition.Append(where);
int ret = -1;
Dictionary <string, bool> cacheTables = null;
cacheTables = _oper.DBInfo.QueryCache.CreateMap(EntityInfo.TableName);
ret = ExecuteCommand(con.GetSql(true), list, CommandType.Text, cacheTables);
if (obj != null && obj._dicUpdateProperty___ != null)
{
obj._dicUpdateProperty___.Clear();
}
if (lstVersionInfo != null && lstVersionInfo.Count > 0)
{
foreach (VersionInfo info in lstVersionInfo)
{
info.Info.SetValue(obj, info.NewValue);
}
}
return(ret);
}
private object FillUpdateConcurrency(StringBuilder sql,
EntityPropertyInfo info, ParamList list, object curValue, ref int index)
{
object newValue = NewConcurrencyValue(curValue);
if (newValue != null)
{
string paramValV = EntityInfo.DBInfo.CurrentDbAdapter.FormatValueName(DataAccessCommon.FormatParam(info.ParamName, index));
string paramKeyV = EntityInfo.DBInfo.CurrentDbAdapter.FormatParamKeyName(DataAccessCommon.FormatParam(info.ParamName, index));
sql.Append(",");
sql.Append(EntityInfo.DBInfo.CurrentDbAdapter.FormatParam(info.ParamName));
sql.Append("=");
sql.Append(paramValV);
list.AddNew(paramKeyV, info.SqlType, newValue);
index++;
}
return(newValue);
}