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