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