/// <summary> /// 组装Insert语句的Sql /// </summary> /// <typeparam name="T"></typeparam> /// <param name="parse2InsertSql"></param> /// <param name="entity"></param> /// <param name="paramInfos"></param> /// <returns>Insert语句的Sql</returns> private string AssembleInsertSql <T>(Parse2InsertSQL parse2InsertSql, T entity, List <ParamInfo> paramInfos) { // 组装Sql string tblName = string.Empty; // 表名称 Type modelType = typeof(T); TblAttr tblAttr = (TblAttr)modelType.GetCustomAttributes(typeof(TblAttr), true)[0]; tblName = tblAttr.Name; StringBuilder cols = new StringBuilder(); // 字段名称 StringBuilder colParams = new StringBuilder(); // 字段参数 PropertyInfo[] propertyInfos = modelType.GetProperties(BindingFlags.Public | BindingFlags.Instance); string realColName = string.Empty; // 实际的字段名称 for (int i = 0; i < propertyInfos.Length; i++) { ColumnAttr[] colAttrs = (ColumnAttr[])propertyInfos[i].GetCustomAttributes(typeof(ColumnAttr), true); if (colAttrs == null || colAttrs.Length == 0) { continue; } ColumnAttr colAttr = colAttrs[0]; if (colAttr.IsPrimary && colAttr.IsAutoGenerate) { continue; } if (propertyInfos[i].GetValue(entity, null) == colAttr.IgnoreValue || object.Equals(propertyInfos[i].GetValue(entity, null), colAttr.IgnoreValue)) { continue; } realColName = (StringHelper.IsNullOrWhiteSpace(colAttr.ColName) ? propertyInfos[i].Name : colAttr.ColName); cols.AppendFormat("{0},", (realColName.IndexOf(m_QuotingStart) >= 0 ? realColName : m_QuotingStart + realColName + m_QuotingEnd)); colParams.AppendFormat("{0}{1}," , m_Db.ParamPreffix , realColName.Replace(m_QuotingStart.ToString(), "").Replace(m_QuotingEnd.ToString(), "")); paramInfos.Add(new ParamInfo(realColName.Replace(m_QuotingStart.ToString(), "").Replace(m_QuotingEnd.ToString(), ""), propertyInfos[i].GetValue(entity, null))); } if (cols.Length > 0) { cols.Remove(cols.Length - 1, 1); } if (colParams.Length > 0) { colParams.Remove(colParams.Length - 1, 1); } SQLInsertClause sqlInsertClause = new SQLInsertClause(); sqlInsertClause.Tbl = tblName; sqlInsertClause.Cols = cols.ToString(); sqlInsertClause.Values = colParams.ToString(); string sql = parse2InsertSql(sqlInsertClause); return(sql); }
/// <summary> /// 过滤父类中与子类使用同一个表别名的表格特性,并获取别名对应命名表别名的SQL语句 /// </summary> /// <param name="filteredTbls">别名对应命名表别名的SQL语句</param> /// <param name="modelType">对象类型</param> private void FilterTbls(IDictionary <string, string> filteredTbls, Type modelType) { object[] allTblAttrs = modelType.GetCustomAttributes(typeof(TblAttr), true); object[] selfTblAttrs = modelType.GetCustomAttributes(typeof(TblAttr), false); TblAttr tblAttr = null; for (int i = selfTblAttrs.Length - 1; i >= 0; i--) { tblAttr = selfTblAttrs[i] as TblAttr; filteredTbls.Add(tblAttr.Alias, string.Format("{0} {1} {2}", tblAttr.Name, m_AliasSymbol, tblAttr.Alias)); } for (int i = allTblAttrs.Length - 1; i >= 0; i--) { tblAttr = allTblAttrs[i] as TblAttr; if (filteredTbls.ContainsKey(tblAttr.Alias)) { continue; } filteredTbls.Add(tblAttr.Alias, string.Format("{0} {1} {2}", tblAttr.Name, m_AliasSymbol, tblAttr.Alias)); } }
/// <summary> /// 删除记录 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="parse2DelSQL">生成完整SQL语句的函数</param> /// <param name="where">Where子句</param> /// <param name="paramInfos">Where子句参数值</param> /// <returns></returns> protected bool Del <T>(Parse2DelSQL parse2DelSQL, string where = "", List <ParamInfo> paramInfos = null) { bool isSuccess = false; // 组装Sql string tblName = string.Empty; // 表名称 Type modelType = typeof(T); TblAttr tblAttr = (TblAttr)modelType.GetCustomAttributes(typeof(TblAttr), true)[0]; tblName = tblAttr.Name; // 获取where sql子句 PropertyInfo[] props = modelType.GetProperties(BindingFlags.Public | BindingFlags.Instance); string whereSql = Parse2Sql(where, props, false); SQLDelClause sqlDelClause = new SQLDelClause(); sqlDelClause.From = tblName; sqlDelClause.Where = string.Format(" {0} ", whereSql); string sql = parse2DelSQL(sqlDelClause); if (m_IsDebug) { Logger.WriteMsg2LogFile(sql); } object countObj = m_Db.ExecNonQuery(sql, paramInfos); if (null != countObj) { isSuccess = Convert.ToInt32(countObj) != 0; } return(isSuccess); }
/// <summary> /// 更新记录 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="parse2UpdateSQL">生成完整SQL语句的函数</param> /// <param name="entity">实体对象</param> /// <param name="where">Where子句</param> /// <returns></returns> protected bool Update <T>(Parse2UpdateSQL parse2UpdateSQL, T entity, string where = "", List <ParamInfo> _paramInfos = null) { bool result = false; // 组装Sql string tblName = string.Empty; // 表名称 Type modelType = typeof(T); TblAttr tblAttr = (TblAttr)modelType.GetCustomAttributes(typeof(TblAttr), true)[0]; tblName = tblAttr.Name; PropertyInfo[] propertyInfos = modelType.GetProperties(BindingFlags.Public | BindingFlags.Instance); StringBuilder cols = new StringBuilder(); // set子句 StringBuilder wheres = new StringBuilder(); // where子句 if (!StringHelper.IsNullOrWhiteSpace(where)) { wheres = new StringBuilder(" " + Parse2Sql(where, propertyInfos, false) + " "); } List <ParamInfo> paramInfos = new List <ParamInfo>(); // 入参实体集合 if (_paramInfos != null) { paramInfos = _paramInfos; } string realColName = string.Empty; for (int i = 0; i < propertyInfos.Length; i++) { ColumnAttr[] colAttrs = (ColumnAttr[])propertyInfos[i].GetCustomAttributes(typeof(ColumnAttr), true); if (colAttrs == null || colAttrs.Length == 0) { continue; } ColumnAttr colAttr = colAttrs[0]; realColName = (StringHelper.IsNullOrWhiteSpace(colAttr.ColName) ? propertyInfos[i].Name : colAttr.ColName); if (colAttr.IsPrimary) { if (propertyInfos[i].GetValue(entity, null) != null && StringHelper.IsNullOrWhiteSpace(where)) { wheres.AppendFormat(" {3} {0}={1}{2} " , realColName , m_Db.ParamPreffix , propertyInfos[i].Name , (wheres.Length == 0 ? string.Empty : "AND")); paramInfos.Add(new ParamInfo(propertyInfos[i].Name, propertyInfos[i].GetValue(entity, null))); } continue; } if (propertyInfos[i].GetValue(entity, null) == colAttr.IgnoreValue || object.Equals(propertyInfos[i].GetValue(entity, null), colAttr.IgnoreValue)) { continue; } cols.AppendFormat(" {0}={1}{2}," , (realColName.IndexOf(m_QuotingStart) >= 0 ? realColName : m_QuotingStart + realColName + m_QuotingEnd) , m_Db.ParamPreffix , propertyInfos[i].Name); paramInfos.Add(new ParamInfo(propertyInfos[i].Name, propertyInfos[i].GetValue(entity, null))); } if (cols.Length > 0) { cols.Remove(cols.Length - 1, 1); } SQLUpdateClause sqlUpdateClause = new SQLUpdateClause(); sqlUpdateClause.Tbl = tblName; sqlUpdateClause.Set = cols.ToString(); sqlUpdateClause.Where = wheres.ToString(); string sql = parse2UpdateSQL(sqlUpdateClause); if (m_IsDebug) { Logger.WriteMsg2LogFile(sql); } object countOjb = m_Db.ExecNonQuery(sql, paramInfos); if (null != countOjb) { result = Convert.ToInt32(countOjb) != 0; } return(result); }
/// <summary> /// 改变树节点序号 /// </summary> /// <typeparam name="T">树节点类</typeparam> /// <param name="dbInstance">数据库操作对象</param> /// <param name="dbHelper">数据库操作帮助对象</param> /// <param name="idProperty">主键属性名</param> /// <param name="pIdProperty">父节点主键属性名</param> /// <param name="orderProperty">序号属性名</param> /// <param name="id">主键</param> /// <param name="sourcePID">源父节点主键</param> /// <param name="sourceIndex">源序号</param> /// <param name="newPID">新父节点主键</param> /// <param name="newIndex">新序号</param> /// <returns></returns> public static bool ChangeOrder<T>(IDBInstance dbInstance, MSSQLHelper dbHelper, string idProperty, string pIdProperty, string orderProperty, long? id, long? sourcePID, long? sourceIndex, long? newPID, long? newIndex) where T : class { PropertyInfo orderProp = typeof(T).GetProperty(orderProperty); if (newPID == null || newPID == sourcePID) { // 在同一父节点下改变序号 long dVal = newIndex.Value - sourceIndex.Value; List<T> tObjs = null; if (dVal > 0) { // 新序号在旧序号之后 tObjs = dbHelper.Query<T>(string.Format(" [{0}] > {1} and [{0}] <= {2} and [{3}] " + (sourcePID == 0 ? "IS NULL" : "=" + sourcePID) + " ", orderProperty, sourceIndex, newIndex, pIdProperty), null, null); for (int i = 0, len = tObjs.Count; i < len; ++i) { orderProp.SetValue(tObjs[i], CalcOrder(orderProp.PropertyType, orderProp.GetValue(tObjs[i], null), -1), null); } } else if (dVal < 0) { // 新序号在旧序号之前 tObjs = dbHelper.Query<T>(string.Format(" [{0}] >= {1} and [{0}] < {2} and [{3}] " + (sourcePID == 0 ? "IS NULL" : "=" + sourcePID) + " ", orderProperty, newIndex, sourceIndex, pIdProperty), null, null); for (int i = 0, len = tObjs.Count; i < len; ++i) { orderProp.SetValue(tObjs[i], CalcOrder(orderProp.PropertyType, orderProp.GetValue(tObjs[i], null), 1), null); } } T targetTObj = typeof(T).GetConstructor(new List<Type>().ToArray()).Invoke(new List<object>().ToArray()) as T; targetTObj.GetType().GetProperty(idProperty).SetValue(targetTObj, id, null); orderProp.SetValue(targetTObj, newIndex, null); tObjs.Add(targetTObj); tObjs.ForEach(tInfo => { dbHelper.Update<T>(tInfo); }); return true; } else { // 在不同父节点间下改变序号 List<T> sourceParentChildInfos = dbHelper.Query<T>(string.Format(" [{0}] > {1} and [{2}] " + (sourcePID == 0 ? "IS NULL" : "=" + sourcePID) + " ", orderProperty, sourceIndex, pIdProperty), null, null); List<T> targetParentChildInfos = dbHelper.Query<T>(string.Format(" [{0}] >= {1} and [{2}] " + (newPID == 0 ? "IS NULL" : "=" + newPID) + " ", orderProperty, newIndex, pIdProperty), null, null); for (int i = 0, len = sourceParentChildInfos.Count; i < len; ++i) { orderProp.SetValue(sourceParentChildInfos[i], CalcOrder(orderProp.PropertyType, orderProp.GetValue(sourceParentChildInfos[i], null), -1), null); } for (int i = 0, len = targetParentChildInfos.Count; i < len; ++i) { orderProp.SetValue(targetParentChildInfos[i], CalcOrder(orderProp.PropertyType, orderProp.GetValue(targetParentChildInfos[i], null), 1), null); } List<T> totalTInfos = new List<T>(); totalTInfos.AddRange(sourceParentChildInfos); totalTInfos.AddRange(targetParentChildInfos); totalTInfos.ForEach(tInfo => { dbHelper.Update<T>(tInfo); }); TblAttr tblAttr = typeof(T).GetCustomAttributes(typeof(TblAttr), false)[0] as TblAttr; int count = dbInstance.ExecNonQuery(string.Format("UPDATE {0} SET [{1}]=" + (newPID == 0 ? "NULL" : newPID.Value.ToString()) + ",[{2}]=" + newIndex + " WHERE [{3}]=" + id.Value, tblAttr.Name, pIdProperty, orderProperty, idProperty), null); return count > 0; } }