/// <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> /// 将带#属性名的字符串转换为数据库字段名 /// </summary> /// <param name="colExp">将带#属性名的字符串</param> /// <param name="props">类属性数组</param> /// <param name="withTblAlias">字段是否带表别名(仅对以#作前缀的属性名称作处理)</param> /// <param name="parse2ColAlias">在withTblAlias为true的情况下,决定是否将字段变换为"表别名_字段名"的形式,如"A_ID"(仅对以#作前缀的属性名称作处理)</param> /// <returns>SQL</returns> private string Parse2Sql(string colExp, PropertyInfo[] props, bool withTblAlias = true, bool parse2ColAlias = false) { if (StringHelper.IsNullOrWhiteSpace(colExp)) { return(string.Empty); } StringBuilder sql = new StringBuilder(colExp); string realColName = string.Empty; for (int i = 0, len = props.Length; i < len; i++) { PropertyInfo prop = props[i]; Regex reg = new Regex(string.Format(@"#{0}\W", prop.Name)); if (!reg.IsMatch(colExp)) { continue; } ColumnAttr[] columnAttrs = (ColumnAttr[])props[i].GetCustomAttributes(typeof(ColumnAttr), true); if (columnAttrs == null || columnAttrs.Length == 0) { continue; } ColumnAttr columnAttr = columnAttrs[0]; string colFullName = string.Empty; if (withTblAlias && parse2ColAlias) { colFullName = string.Format("\"{0}_{1}\"", (StringHelper.IsNullOrWhiteSpace(columnAttr.TblAlias) ? string.Empty : columnAttr.TblAlias), (StringHelper.IsNullOrWhiteSpace(columnAttr.ColName) ? prop.Name : columnAttr.ColName)); } else { realColName = string.Format("\"{0}\"", (StringHelper.IsNullOrWhiteSpace(columnAttr.ColName) ? prop.Name : columnAttr.ColName)); colFullName = realColName; if (withTblAlias) { colFullName = (StringHelper.IsNullOrWhiteSpace(columnAttr.TblAlias) ? string.Empty : columnAttr.TblAlias + ".") + colFullName; } } sql.Replace("#" + prop.Name, colFullName); } return(sql.ToString()); }
/// <summary> /// 将数据库的值保存到实体对象 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="reader">数据读取器</param> /// <param name="recs">记录集</param> /// <param name="excludedProps">不包含在Select子句的属性名</param> private void ConvertRO2DO <T>(IDataReader reader, List <T> recs, List <string> excludedProps) { Type modelType = typeof(T); PropertyInfo[] propertyInfos = modelType.GetProperties(BindingFlags.Public | BindingFlags.Instance); T rec = default(T); while (reader.Read()) { rec = (T)modelType.GetConstructor(new Type[] { }).Invoke(null); for (int i = 0; i < propertyInfos.Length; i++) { PropertyInfo curPropertyInfo = propertyInfos[i]; ColumnAttr[] attrs = (ColumnAttr[])propertyInfos[i].GetCustomAttributes(typeof(ColumnAttr), true); ColumnAttr colAttr = null; if (attrs.Length == 0 || ((colAttr = (ColumnAttr)attrs[0]) == null)) { continue; } if (null != excludedProps && excludedProps.Exists((excludedName) => { return(excludedName.Equals(propertyInfos[i].Name)); })) { continue; // 排除不包含的Select子句的列 } string colName = (StringHelper.IsNullOrWhiteSpace(colAttr.ColName) ? propertyInfos[i].Name : colAttr.ColName); colName = (colAttr.ColName.IndexOf(m_QuotingStart) >= 0 ? colAttr.ColName.Replace(m_QuotingStart.ToString(), string.Empty).Replace(m_QuotingEnd.ToString(), string.Empty) : colName); int colIndex = reader.GetOrdinal((StringHelper.IsNullOrWhiteSpace(colAttr.TblAlias) ? string.Empty : colAttr.TblAlias + "_") + colName); if (reader.IsDBNull(colIndex)) { continue; } object valObj = (reader.IsDBNull(colIndex) ? Util.GetDefaultVal(curPropertyInfo.PropertyType) : reader.GetValue(colIndex)); curPropertyInfo.SetValue(rec, Converter.ChangeType(valObj, curPropertyInfo.PropertyType), null); } recs.Add(rec); } }
public ColumnSpec(ColumnType type, string name, ColumnAttr attr) { this.Type = type; this.Name = name; this.Attr = attr; }
/// <summary> /// 获取Select子句和From子句 /// </summary> /// <param name="fromClause"></param> /// <param name="cols"></param> /// <param name="propertyInfos"></param> /// <param name="tblNames"></param> /// <param name="excludedProps"></param> /// <param name="propFns"></param> /// <param name="relAttrs"></param> /// <param name="distinctAttrs"></param> /// <param name="isQueryCount"></param> private void GetFormAndSelectClause(StringBuilder fromClause , StringBuilder cols , PropertyInfo[] propertyInfos , Dictionary <string, string> tblNames , List <string> excludedProps , Dictionary <string, DBFn> propFns , RelAttr[] relAttrs = null , DistinctAttr[] distinctAttrs = null , bool isQueryCount = false) { bool existRelAttrs = relAttrs != null && relAttrs.Length != 0; string onClause = string.Empty; // 连接时的ON子句 string joinClause = string.Empty; // Join子句类型 for (int i = 0; i < propertyInfos.Length; i++) { ColumnAttr[] colAttrs = (ColumnAttr[])propertyInfos[i].GetCustomAttributes(typeof(ColumnAttr), true); if (colAttrs.Length == 0) { continue; } ColumnAttr colAttr = null; string realColName = string.Empty;// 当ColumnAttr的ColName为null、空字符串或全空格字符串时,即表示数据表字段名与属性名相同 for (int j = 0, len = colAttrs.Length; j < len; ++j) { colAttr = colAttrs[j]; if (colAttr.JoinType != JoinType.NONE) { joinClause = ParseToJoinTypeEvent(colAttr.JoinType); } // 组装FROM子句 if (!StringHelper.IsNullOrWhiteSpace(colAttr.RelatedColName) && !existRelAttrs) { realColName = (StringHelper.IsNullOrWhiteSpace(colAttr.ColName) ? propertyInfos[i].Name : colAttr.ColName); onClause = string.Format("ON ({0}.{1} = {2}.{3})", colAttr.TblAlias, realColName, colAttr.RelatedTblAlias, colAttr.RelatedColName); if (fromClause.Length == 0) { fromClause.AppendFormat("{0} {1} {2} {3}", tblNames[colAttr.TblAlias], joinClause, tblNames[colAttr.RelatedTblAlias], onClause); } else { int relatedTblIndex, tblIndex; string fromClauseStr = fromClause.ToString(); if ((relatedTblIndex = fromClauseStr.IndexOf(tblNames[colAttr.RelatedTblAlias])) == -1) { fromClause.AppendFormat(" {0} {1} {2}", joinClause, tblNames[colAttr.RelatedTblAlias], onClause); } else if ((tblIndex = fromClauseStr.IndexOf(tblNames[colAttr.TblAlias])) == -1) { fromClause.AppendFormat(" {0} {1} {2}", joinClause, tblNames[colAttr.TblAlias], onClause); } else { // 因两个表格的信息已存在,所以只需追加ON子句即可 int minIndex = AlgorithmHelper.Min(new int[] { relatedTblIndex, tblIndex }); int onClauseStartIndex = StringHelper.IndexOf(fromClauseStr, @"\bON \(", minIndex); int onClauseEndIndex = StringHelper.IndexOf(fromClauseStr, @"\)", onClauseStartIndex); fromClause.Insert(onClauseEndIndex , string.Format(" AND {0}.{1} = {2}.{3}" , colAttr.TblAlias , realColName , colAttr.RelatedTblAlias , colAttr.RelatedColName)); } } } } if (colAttr == null) { continue; } realColName = (StringHelper.IsNullOrWhiteSpace(colAttr.ColName) ? propertyInfos[i].Name : colAttr.ColName); if (isQueryCount) { if (StringHelper.IsNullOrWhiteSpace(cols) && colAttr.IsPrimary) { cols.Append((StringHelper.IsNullOrWhiteSpace(colAttr.TblAlias) ? string.Empty : colAttr.TblAlias + ".") + m_QuotingStart + realColName + m_QuotingEnd); cols.Append(","); } } else { if (null != excludedProps && excludedProps.Exists((excludedName) => { return(excludedName.Equals(propertyInfos[i].Name)); })) { continue; // 排除不包含的Select子句的列 } string colName = (realColName.IndexOf(m_QuotingStart) >= 0 ? realColName.Replace(m_QuotingStart.ToString(), string.Empty).Replace(m_QuotingEnd.ToString(), string.Empty) : realColName); if (propFns.ContainsKey(propertyInfos[i].Name)) { colName = WrapFnEvent(propFns[propertyInfos[i].Name], Parse2Sql("#" + propertyInfos[i].Name, propertyInfos)) + " " + m_AliasSymbol + " " + m_QuotingStart + colAttr.TblAlias + "_" + colName + m_QuotingEnd; } else if (!StringHelper.IsNullOrWhiteSpace(colAttr.TblAlias)) { colName = string.Format("{0}.{1} {2} {3}" , colAttr.TblAlias , m_QuotingStart + colName + m_QuotingEnd , m_AliasSymbol , m_QuotingStart + colAttr.TblAlias + "_" + colName + m_QuotingEnd); } cols.Append(colName + ","); } } if (cols.Length >= 1) { cols.Remove(cols.Length - 1, 1); } if (existRelAttrs) { // 排序 CollectionHelper.Sort <RelAttr>(relAttrs, (x, y) => { return(x.Index - y.Index); }); foreach (RelAttr relAttr in relAttrs) { joinClause = ParseToJoinTypeEvent(relAttr.JoinType); onClause = string.Format("ON ({0})", relAttr.JoinConstraint); if (fromClause.Length == 0) { fromClause.AppendFormat("{0} {1} {2} {3}", tblNames[relAttr.TblAlias], joinClause, tblNames[relAttr.RelatedTblAlias], onClause); } else { int relatedTblIndex, tblIndex; string fromClauseStr = fromClause.ToString(); if ((relatedTblIndex = fromClauseStr.IndexOf(tblNames[relAttr.RelatedTblAlias])) == -1) { fromClause.AppendFormat(" {0} {1} {2}", joinClause, tblNames[relAttr.RelatedTblAlias], onClause); } else if ((tblIndex = fromClauseStr.IndexOf(tblNames[relAttr.TblAlias])) == -1) { fromClause.AppendFormat(" {0} {1} {2}", joinClause, tblNames[relAttr.TblAlias], onClause); } else { // 因两个表格的信息已存在,所以只需追加ON子句即可 int minIndex = AlgorithmHelper.Min(new int[] { relatedTblIndex, tblIndex }); int onClauseStartIndex = StringHelper.IndexOf(fromClauseStr, @"\bON \(", minIndex); int onClauseEndIndex = StringHelper.IndexOf(fromClauseStr, @"\)", onClauseStartIndex); fromClause.Insert(onClauseEndIndex , string.Format(" AND {0}", relAttr.JoinConstraint)); } } } } if (fromClause.Length == 0) { foreach (string key in tblNames.Keys) { fromClause.Append(tblNames[key]); break; } } if (distinctAttrs != null && distinctAttrs.Length != 0) { cols.Insert(0, "DISTINCT "); } }
/// <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); }
public static bool ChangeOrder <T>(MSSQLHelper dbHelper, string idPropName, long?id, string orderPropName, int?srcOrder, int?destOrder, string whereClause) where T : class { PropertyInfo orderProp = typeof(T).GetProperty(orderPropName); string orderDbName = string.Empty; object[] colAttrs = orderProp.GetCustomAttributes(typeof(ColumnAttr), false); if (colAttrs != null && colAttrs.Length >= 1) { ColumnAttr colAttr = colAttrs[0] as ColumnAttr; orderDbName = string.Format("[{0}]", typeof(ColumnAttr).GetProperty("Name").GetValue(colAttr, null)); } else { throw new Exception("序列字段没有通过ColumnAttr对应到数据表的字段"); } List <T> updatingItems = null; int i = 0; // 删除节点时,更新其余节点的序号 if (!destOrder.HasValue) { updatingItems = dbHelper.Query <T>(string.Format(" {0} > {1} {2} ", orderDbName, srcOrder, (whereClause != null && whereClause.Length >= 1 ? " AND " + whereClause : "")), orderDbName, null); i = -1; dbHelper.Del <T>("#" + idPropName + "=@" + idPropName, new ParamInfo(idPropName, id)); } else { // 在同一父节点下改变序号 long dVal = destOrder.Value - srcOrder.Value; if (dVal > 0) { // 新序号在旧序号之后 updatingItems = dbHelper.Query <T>(string.Format(" {0} > {1} and {0} <= {2} {3} ", orderDbName, srcOrder, destOrder, (whereClause != null && whereClause.Length >= 1 ? " AND " + whereClause : "")), null, null); i = -1; } else if (dVal < 0) { // 新序号在旧序号之前 updatingItems = dbHelper.Query <T>(string.Format(" {0} >= {1} and {0} < {2} {3} ", orderDbName, destOrder, srcOrder, (whereClause != null && whereClause.Length >= 1 ? " AND " + whereClause : "")), null, null); i = 1; } } for (int j = 0, len = updatingItems.Count; j < len; ++j) { orderProp.SetValue(updatingItems[j], CalcOrder(orderProp.PropertyType, orderProp.GetValue(updatingItems[j], null), i), null); } if (destOrder.HasValue) { T targetTObj = typeof(T).GetConstructor(new List <Type>().ToArray()).Invoke(new List <object>().ToArray()) as T; targetTObj.GetType().GetProperty(idPropName).SetValue(targetTObj, id, null); orderProp.SetValue(targetTObj, destOrder, null); updatingItems.Add(targetTObj); } updatingItems.ForEach(tInfo => { dbHelper.Update <T>(tInfo); }); return(true); }