public void DeleteByCondition(Type entityType, Filter filter)
{
if (filter == null)
{
throw new ArgumentNullException("filter");
}
string condition = filter.GetConditionString();
if (string.IsNullOrEmpty(condition))
{
throw new SqlException("删除时未指定条件");
}
TableMetaData table = MetaDataCacheManager.GetTableMetaData(entityType);
DbCommand dbCommand = _DbInstance.GetSqlStringCommand(string.Format("Delete From {0} Where {1};", table.TableName, condition));
//执行sql命令
try
{
_DbInstance.ExecuteNonQuery(dbCommand);
}
catch (Exception ex)
{
throw new SqlException(ex.Message, LogHelper.GetSqlString(dbCommand), ex);
}
}
public void Delete <T>(object id)
{
TableMetaData table = MetaDataCacheManager.GetTableMetaData(typeof(T));
if (table.PKColumn == null)
{
throw new MappingException(string.Format("实体类型{0}未定义主键列", table.EntityType.Name));
}
if (table.PKColumn.PropertyType == typeof(Int32) || table.PKColumn.PropertyType == typeof(Int64))
{
long idValue = Convert.ToInt64(id);
if (idValue == 0)
{
throw new SqlException("id为空");
}
}
else if (table.PKColumn.PropertyType == typeof(string))
{
string idValue = (string)id;
if (string.IsNullOrEmpty(idValue))
{
throw new SqlException("id为空");
}
}
DeleteByCondition(typeof(T), Filter.CreateEq(table.PKColumn.ColumnName, id));
}
/// <summary>
/// 获取用来查询的完整的Select字符串
/// </summary>
public static string GetSelectString(this Filter filter, Type type)
{
string condition = filter.GetConditionString();
string orderKey = filter.OrderKey;
TableMetaData table = MetaDataCacheManager.GetTableMetaData(type);
StringBuilder sb = new StringBuilder("Select * From " + table.TableName);
if (!string.IsNullOrEmpty(filter.WithOption))
{
sb.AppendFormat(" WITH({0})", filter.WithOption);
}
if (!string.IsNullOrEmpty(condition))
{
sb.Append(" Where ").Append(condition);
}
if (!string.IsNullOrEmpty(orderKey))
{
sb.Append(" Order By ").Append(orderKey);
}
return(sb.ToString());
}
public bool Exist <T>(Filter <T> filter)
{
string condition = (filter == null) ? string.Empty : filter.GetConditionString();
TableMetaData table = MetaDataCacheManager.GetTableMetaData(typeof(T));
StringBuilder sb = new StringBuilder("Select Count(1) From " + table.TableName);
if (!string.IsNullOrEmpty(condition))
{
sb.Append(" Where ").Append(condition);
}
object obj;
try
{
obj = ExecuteScalar(sb.ToString());
}
catch (Exception ex)
{
throw new SqlException(ex.Message, sb.ToString(), ex);
}
return(Convert.ToInt32(obj) > 0);
}
public T Get <T>(object id)
{
TableMetaData table = MetaDataCacheManager.GetTableMetaData(typeof(T));
if (table.PKColumn == null)
{
throw new MappingException(string.Format("实体类型{0}未定义主键列", table.EntityType.Name));
}
//if (table.PKColumn.PropertyType == typeof(Int32) || table.PKColumn.PropertyType == typeof(Int64))
//{
// //long idValue = Convert.ToInt64(id);
// //if (idValue == 0)
// // throw new SqlException("id为空");
//}
//else if (table.PKColumn.PropertyType == typeof(string))
//{
// string idValue = (string)id;
// if(string.IsNullOrEmpty(idValue))
// throw new SqlException("id为空");
//}
List <T> list = GetList <T>(Filter.CreateEq(table.PKColumn.ColumnName, id));
if (list.Count == 0)
{
return(default(T));
}
else if (list.Count == 1)
{
return(list[0]);
}
else
{
throw new SqlException(string.Format("id为{0}的记录多于一条", id));
}
}
public PageList <T> GetPageList <T>(string sql, string orderKey, int pageSize, int curPage)
{
PageList <T> pageList = new PageList <T>();
pageList.SetPage(pageSize, curPage);
string pageSql = _Dialect.GetPageSql(sql, orderKey, pageList.StartRecord, pageList.EndRecord);
string executeSql;
if (pageSize > 0)
{
string countSql = _Dialect.GetCountSql(sql);
executeSql = countSql + "; " + pageSql;
}
else
{
executeSql = pageSql;
}
DbCommand dbCommand = _DbInstance.GetSqlStringCommand(executeSql);
//执行sql命令
IDataReader dr;
try
{
dr = this._DbInstance.ExecuteReader(dbCommand);
}
catch (Exception ex)
{
throw new SqlException(ex.Message, LogHelper.GetSqlString(dbCommand), ex);
}
try
{
if (pageSize > 0)
{
//读取记录条数
dr.Read();
pageList.TotalCount = (int)dr[0];
dr.NextResult();
}
string errorColumn = string.Empty;
string errorDbValue = string.Empty;
TableMetaData table = MetaDataCacheManager.GetTableMetaData(typeof(T));
try
{
while (dr.Read())
{
pageList.Add((T)CreateObjectByDataReader(table, dr, out errorColumn, out errorDbValue));
}
}
catch (Exception ex)
{
throw new SqlException(string.Format("CreateObjectByDataReader error, table:{0}, column:{1}, dbValue:{2}. message:{3}", table.TableName, errorColumn, errorDbValue, ex.Message));
}
// 如果未分页，则设置总数为当前页记录数
if (pageSize <= 0)
{
pageList.TotalCount = pageList.CurPageCount;
}
}
finally
{
dr.Close();
}
return(pageList);
}
public void Insert(object obj, bool includeIdetityColumnInSql)
{
BaseEntity baseEntity = obj as BaseEntity;
if (baseEntity != null)
{
if (string.IsNullOrEmpty(baseEntity.EnabledFlag))
{
//插入时如果EnabledFlag为空，则默认为Y
baseEntity.EnabledFlag = "Y";
}
}
#region 创建列集合
TableMetaData table = MetaDataCacheManager.GetTableMetaData(obj.GetType());
List <string> columnNames = new List <string>();
List <object> columnValues = new List <object>();
List <DbType> columnDbTypes = new List <DbType>();
if (table.PKColumn != null)
{
if (table.PKColumn.ColumnCategory != Category.IdentityKey ||
includeIdetityColumnInSql)
{
//当存在非自动增长的主键，或者存在自动增长主键，但需要在insert语句中包含指定的列时
columnNames.Add(table.PKColumn.ColumnName);
columnValues.Add(table.PKColumn.PropertyAccessor.GetValue(obj));
columnDbTypes.Add(GetDbType(table.PKColumn.PropertyType));
}
}
if (table.VersionColumn != null)
{
#region 处理版本控制列
columnNames.Add(table.VersionColumn.ColumnName);
//Version列赋初值
object versionValue;
if (table.VersionColumn.PropertyType == typeof(DateTime))
{
DateTime dt = DateTime.Now;
versionValue = new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second);
}
else if (table.VersionColumn.PropertyType == typeof(Int32))
{
versionValue = 1;
}
else if (table.VersionColumn.PropertyType == typeof(Int64))
{
versionValue = (long)1;
}
else
{
throw new MappingException(string.Format("实体类型{0}中的Version列{1}的数据类型无效", obj.GetType().Name, table.VersionColumn.ColumnName));
}
table.VersionColumn.PropertyAccessor.SetValue(obj, versionValue);
columnValues.Add(versionValue);
columnDbTypes.Add(GetDbType(table.VersionColumn.PropertyType));
#endregion
}
foreach (ColumnMetaData column in table.NormalColumns)
{
columnNames.Add(column.ColumnName);
columnValues.Add(column.PropertyAccessor.GetValue(obj));
columnDbTypes.Add(GetDbType(column.PropertyType));
}
#endregion
#region 生成Insert Command
string format = "Insert Into {0}({1}) Values({2});";
StringBuilder tempNames = new StringBuilder();
StringBuilder tempParamNames = new StringBuilder();
foreach (string columnName in columnNames)
{
tempNames.Append(columnName).Append(",");
tempParamNames.Append("@").Append(columnName).Append(",");
}
tempNames.Remove(tempNames.Length - 1, 1);
tempParamNames.Remove(tempParamNames.Length - 1, 1);
DbCommand dbCommand = _DbInstance.GetSqlStringCommand(string.Format(format, table.TableName, tempNames.ToString(), tempParamNames.ToString()));
for (int i = 0; i < columnNames.Count; i++)
{
_DbInstance.AddInParameter(dbCommand, "@" + columnNames[i], columnDbTypes[i], TransformToSqlParamValue(columnValues[i]));
}
#endregion
//执行sql命令
try
{
_DbInstance.ExecuteNonQuery(dbCommand);
}
catch (Exception ex)
{
throw new SqlException(ex.Message, LogHelper.GetSqlString(dbCommand), ex);
}
//取回标识值
if (table.PKColumn != null && table.PKColumn.ColumnCategory == Category.IdentityKey && !includeIdetityColumnInSql)
{
object newIdValue = _DbInstance.ExecuteScalar(CommandType.Text, "SELECT CAST(@@IDENTITY as bigint) as value");
table.PKColumn.PropertyAccessor.SetValue(obj, TransformToObjValueFromDb(newIdValue, table.PKColumn.PropertyType));
}
}
public IList[] GetMultiList(string[] selectStrings, Type[] returnTypes)
{
if (selectStrings == null || returnTypes == null)
{
throw new ArgumentException("selectStrings或returnTypes为空");
}
if (selectStrings.Length != returnTypes.Length)
{
throw new ArgumentException("selectStrings与returnTypes的长度不同");
}
if (selectStrings.Length == 0)
{
return(new IList[0]);
}
#region 得到实际执行的字符串
string executeSql;
if (selectStrings.Length == 1)
{
executeSql = selectStrings[0];
}
else
{
StringBuilder sb = new StringBuilder();
foreach (string item in selectStrings)
{
sb.Append(item);
if (!item.EndsWith(";"))
{
sb.Append(";");
}
}
executeSql = sb.ToString();
}
#endregion
//读取结果集
IList[] results = new IList[selectStrings.Length];
DbCommand dbCommand = _DbInstance.GetSqlStringCommand(executeSql);
dbCommand.CommandTimeout = int.MaxValue;
//执行sql命令
IDataReader dr;
try
{
dr = _DbInstance.ExecuteReader(dbCommand);
}
catch (Exception ex)
{
throw new SqlException(ex.Message, LogHelper.GetSqlString(dbCommand), ex);
}
try
{
for (int i = 0; i < selectStrings.Length; i++)
{
string errorColumn = string.Empty;
string errorDbValue = string.Empty;
Type listType = typeof(List <>).MakeGenericType(returnTypes[i]);
IList list = (IList)Activator.CreateInstance(listType);
TableMetaData table = MetaDataCacheManager.GetTableMetaData(returnTypes[i]);
try
{
while (dr.Read())
{
list.Add(CreateObjectByDataReader(table, dr, out errorColumn, out errorDbValue));
}
results[i] = list;
}
catch (Exception ex)
{
throw new SqlException(string.Format("CreateObjectByDataReader error, table:{0}, column:{1}, dbValue:{2}. message:{3}", table.TableName, errorColumn, errorDbValue, ex.Message), "", ex);
}
dr.NextResult();
}
}
finally
{
dr.Close();
}
return(results);
}
public void Update(object obj, bool ignoreVersionCheck, List <string> updateColumnList)
{
TableMetaData table = MetaDataCacheManager.GetTableMetaData(obj.GetType());
if (table.PKColumn == null)
{
throw new InvalidOperationException(string.Format("类型{0}未定义主键列，无法执行更新操作", obj.GetType().Name));
}
List <string> columnNames = new List <string>();
List <object> columnValues = new List <object>();
List <DbType> columnDbTypes = new List <DbType>();
object newVersion = null;
object oldVersion = null;
if (table.VersionColumn != null)
{
#region 处理标识列
columnNames.Add(table.VersionColumn.ColumnName);
if (table.VersionColumn.PropertyType == typeof(DateTime))
{
DateTime oldDt = (DateTime)table.VersionColumn.PropertyAccessor.GetValue(obj);
if (!ignoreVersionCheck && oldDt == DateTime.MinValue)
{
throw new SqlException("更新对象时版本列的值为空");
}
oldVersion = oldDt;
DateTime dt = DateTime.Now;
newVersion = new DateTime(dt.Year, dt.Month, dt.Day, dt.Hour, dt.Minute, dt.Second);
}
else if (table.VersionColumn.PropertyType == typeof(Int32))
{
oldVersion = table.VersionColumn.PropertyAccessor.GetValue(obj);
newVersion = (int)oldVersion + 1;
}
else if (table.VersionColumn.PropertyType == typeof(Int64))
{
oldVersion = table.VersionColumn.PropertyAccessor.GetValue(obj);
newVersion = (long)oldVersion + 1;
}
else
{
throw new MappingException(string.Format("实体类型{0}中的Version列{1}的数据类型无效", obj.GetType().Name, table.VersionColumn.ColumnName));
}
columnValues.Add(newVersion);
columnDbTypes.Add(GetDbType(table.VersionColumn.PropertyType));
#endregion
}
if (updateColumnList.Count == 0)
{
foreach (ColumnMetaData column in table.NormalColumns)
{
if (column.ColumnCategory != Category.ReadOnly)
{
columnNames.Add(column.ColumnName);
columnValues.Add(column.PropertyAccessor.GetValue(obj));
columnDbTypes.Add(GetDbType(column.PropertyType));
}
}
}
else
{
//更新指定的列
foreach (string columnName in updateColumnList)
{
ColumnMetaData column = FindColumn(columnName, table.NormalColumns);
if (column == null)
{
throw new SqlException("列名未找到或无效:" + columnName);
}
if (column.ColumnCategory != Category.ReadOnly)
{
columnNames.Add(column.ColumnName);
columnValues.Add(column.PropertyAccessor.GetValue(obj));
columnDbTypes.Add(GetDbType(column.PropertyType));
}
}
}
string format = "Update {0} Set {1} Where {2};";
StringBuilder tempUpdateString = new StringBuilder();
StringBuilder tempConditions = new StringBuilder();
for (int i = 0; i < columnNames.Count; i++)
{
tempUpdateString.AppendFormat("{0}=@{0},", columnNames[i]);
}
tempUpdateString.Remove(tempUpdateString.Length - 1, 1);
//根据主键及版本列生成条件
tempConditions.AppendFormat("{0}=@{0}", table.PKColumn.ColumnName);
if (!ignoreVersionCheck && table.VersionColumn != null)
{
tempConditions.AppendFormat(" And {0}=@Old_{0}", table.VersionColumn.ColumnName);
}
DbCommand dbCommand = _DbInstance.GetSqlStringCommand(string.Format(format, table.TableName, tempUpdateString, tempConditions));
for (int i = 0; i < columnNames.Count; i++)
{
_DbInstance.AddInParameter(dbCommand, "@" + columnNames[i], columnDbTypes[i], TransformToSqlParamValue(columnValues[i]));
}
_DbInstance.AddInParameter(dbCommand, "@" + table.PKColumn.ColumnName, GetDbType(table.PKColumn.PropertyType), table.PKColumn.PropertyAccessor.GetValue(obj));
if (!ignoreVersionCheck && table.VersionColumn != null)
{
_DbInstance.AddInParameter(dbCommand, "@Old_" + table.VersionColumn.ColumnName, GetDbType(table.VersionColumn.PropertyType), oldVersion);
}
//执行sql命令
int count;
try
{
count = _DbInstance.ExecuteNonQuery(dbCommand);
}
catch (Exception ex)
{
throw new SqlException(ex.Message, LogHelper.GetSqlString(dbCommand), ex);
}
if (count == 0)
{
throw new StaleObjectStateException("在更新到数据库时，受影响的记录数为0，请检查更新的条件是否正确，或者对象的状态已过期。");
}
if (count > 1)
{
throw new SqlException("更新的记录超过一条");
}
if (table.VersionColumn != null)
{
table.VersionColumn.PropertyAccessor.SetValue(obj, newVersion);
}
}
/// <summary>
/// 获取类型对应的表名
/// </summary>
public static string GetTableName(Type type)
{
TableMetaData table = MetaDataCacheManager.GetTableMetaData(type);
return(table.TableName);
}