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