/// <summary>
/// 生成更新SQL语句
/// </summary>
/// <param name="entityType"></param>
/// <returns>返回</returns>
public static string ColumnSqlForUpdate(object entity, PropertyInfo propertyInfo, List <SqlParameter> parameter)
{
var config = EntityParser.GetColumnConfig(propertyInfo); //获取对属性/列的配置信息
var propertyValue = propertyInfo.GetValue(entity, null);
if (config.UsePropertyForUpdate || propertyValue == null) //如果使用属性的值更新数据库中的值
{
var columnName = EntityParser.GetColumnName(propertyInfo);
var valueExpression = "@" + columnName;
if (config.DBType == DBType.PropertyType) //如果数据库中的类型 和 属性的类型相同
{
parameter.Add(new SqlParameter(valueExpression, propertyValue));
}
else //如果要转换类型
{
propertyValue = EntityParser.ConvertPropertyValue(propertyInfo, propertyValue, config.DBType);
parameter.Add(new SqlParameter(valueExpression, propertyValue));
}
return(valueExpression);
}
else
{
if (!string.IsNullOrEmpty(config.UpdateSQL)) //如果不用属性，就使用特定的SQL语句指定列的值
{
return(config.UpdateSQL);
}
}
return(string.Empty);
}
public static string SelectSQL(Type entityType, string where = "")
{
string selectSQL;
if (!selectSQLCache.TryGetValue(entityType, out selectSQL)) //如果从缓存里面没有找到
{
var columns = entityType.GetProperties().Where(item =>
{
return(!EntityParser.IsIgnore(item));
}).Select(item => string.Format(" [{0}]", EntityParser.GetColumnName(item)));
if (!columns.Any())
{
throw new Exception(string.Format("类型{0}没有任何可以SELECT的列", entityType.FullName));
}
selectSQL = string.Format("SELECT{0} FROM {1}",
string.Join(",", columns),
EntityParser.GetTableName(entityType)
);
selectSQLCache.Add(entityType, selectSQL);
}
if (!string.IsNullOrEmpty(where))
{
where = " WHERE " + where;
}
return(selectSQL + where);
}
/// <summary>
/// 返回null或空字符串表示不生成SQL语句
/// </summary>
/// <returns></returns>
public static string ColumnSqlForInsert(object entity, PropertyInfo propertyInfo, List <SqlParameter> parameter)
{
var config = EntityParser.GetColumnConfig(propertyInfo);
if (config.UsePropertyForInsert)
{
var columnName = EntityParser.GetColumnName(propertyInfo);
var valueExpression = "@" + columnName;
var propertyValue = propertyInfo.GetValue(entity, null);
if (config.DBType == DBType.PropertyType) //如果直接用属性
{
parameter.Add(new SqlParameter(valueExpression, propertyValue));
}
else //如果要转换类型
{
propertyValue = EntityParser.ConvertPropertyValue(propertyInfo, propertyValue, config.DBType);
parameter.Add(new SqlParameter(valueExpression, propertyValue));
}
return(valueExpression);
}
else
{
if (!string.IsNullOrEmpty(config.InsertSQL))
{
return(config.InsertSQL);
}
}
return(string.Empty);
}
/// <summary>
/// 根据主键删除一个实体
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="key">主键的值</param>
public static void DeleteByKey <TEntity>(object key) where TEntity : SQLEntity
{
var keyInfo = EntityParser.GetKey(typeof(TEntity));
var keyName = keyInfo.KeyName;
var sql = SqlBuilder.DeleteSQL(EntityParser.GetTableName(typeof(TEntity)), string.Format("{0} = @{0}", keyName));
SQLServerHelper.ExecuteNonQuery(sql, new SqlParameter("@" + keyName, key));
}
/// <summary>
/// 获取一个实体，只有主键的值，并且状态为NotChanged
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="keyValue">主键的值</param>
/// <param name="isFill">是否从数据库中填充所有属性的值</param>
/// <returns></returns>
public static TEntity GetEntityForUpdate <TEntity>(object keyValue, bool isFill = false) where TEntity : SQLEntity
{
var key = EntityParser.GetKey(typeof(TEntity));
var se = Activator.CreateInstance <TEntity>(); //创建实体对象
se.EntityState = EntityState.NotInitialized; //未初始化
key.Key.SetValue(se, keyValue, null); //设置主键的值
se.EntityState = EntityState.NotChanged; //未更改
return(se);
}
/// <summary>
/// 刷新一个已存在的实体(如果一个实体类的属性的值是旧的，可以使用此方法从数据库中刷新值)
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="entity"></param>
/// <returns></returns>
public static TEntity RefreshEntity <TEntity>(TEntity entity) where TEntity : SQLEntity
{
var keyContext = EntityParser.GetKey(typeof(TEntity));
var keyName = keyContext.KeyName;
var sql = SqlBuilder.SelectSQL(typeof(TEntity), string.Format("{0} = @{0}", keyName));
return(SQLServerHelper.FirstOrDefault((reader) =>
{
return FilldEntity <TEntity>(reader, entity);
}, sql, new SqlParameter("@" + keyName, keyContext.Key.GetValue(entity, null))));
}
/// <summary>
/// 填充一个实体类的属性
/// </summary>
/// <param name="entity">实体类的实例</param>
/// <param name="propertyInfo">要填充的属性的信息</param>
/// <param name="sourceValue">从数据源获取到的属性的只</param>
private static void FillProperty(object entity, PropertyInfo propertyInfo, object sourceValue)
{
var result = EntityParser.GetTypeConverter(propertyInfo);
if (result == null)
{
sourceValue = sourceValue.To(propertyInfo.PropertyType);
propertyInfo.SetValue(entity, sourceValue, null);
}
else
{
var propertyValue = result.ConvertFrom(sourceValue);
propertyInfo.SetValue(entity, propertyValue, null);
}
}
public static string InsertSQL(Type entityType)
{
string sql;
if (!insertSQLCache.TryGetValue(entityType, out sql))
{
StringBuilder insert = new StringBuilder();
var key = EntityParser.GetKey(entityType);
if (!key.KeyConfig.UsePropertyForInsert)
{
insert.AppendLine(key.KeyConfig.KeyBeforeInsertSQL);
}
string tableName = EntityParser.GetTableName(entityType);
insert.AppendFormat("INSERT INTO [{0}]", tableName);
var properties = entityType.GetProperties().Where(item => !EntityParser.IsIgnore(item)).Select(item =>
{
string columnSQL = EntityParser.GetColumnName(item);
string valueSQL = "";
var config = EntityParser.GetColumnConfig(item);
if (config.UsePropertyForInsert)
{
valueSQL = " @" + item.Name;
}
else
{
valueSQL = config.InsertSQL;
}
return(new KeyValuePair <string, string>(columnSQL, valueSQL));
}).Where(item => !string.IsNullOrEmpty(item.Value));
insert.AppendFormat("({0})", string.Join(",", properties.Select(item => item.Key))).AppendLine();
insert.AppendFormat("VALUES({0})", string.Join(",", properties.Select(item => item.Value))).AppendLine();
if (!key.KeyConfig.UsePropertyForInsert)
{
insert.Append("SELECT ").AppendLine(key.KeyConfig.KeyAfterInsertSQL);
}
insertSQLCache.Add(entityType, insert.ToString());
return(insert.ToString());
}
return(sql);
}
/// <summary>
/// 动态的填充一个实体
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="reader">只接收DataRow和SqlDataReader作为参数，使用其他类型的对象会发生错误</param>
/// <param name="entity">实体类的实例，如果不使用此参数，就回创建一个新实例</param>
/// <returns></returns>
private static TEntity DynamicFillEntity <TEntity>(dynamic reader, TEntity entity = default(TEntity)) where TEntity : SQLEntity
{
List <ColumnContext> contexts;
var entityType = typeof(TEntity);
if (!fillEntityCache.TryGetValue(entityType, out contexts))
{
contexts = new List <ColumnContext>();
foreach (var propertyInfo in entityType.GetProperties())
{
if (EntityParser.IsIgnore(propertyInfo))
{
continue;
}
var config = EntityParser.GetColumnConfig(propertyInfo);
var context = new ColumnContext()
{
Config = config,
PropertyInfo = propertyInfo,
PropertyName = propertyInfo.Name,
ColumnName = EntityParser.GetColumnName(propertyInfo)
};
contexts.Add(context);
}
fillEntityCache.Add(entityType, contexts);
}
if (entity == null)
{
entity = Activator.CreateInstance <TEntity>();
}
foreach (var context in contexts)
{
var value = reader[context.ColumnName];
FillProperty(entity, context.PropertyInfo, value);
}
entity.EntityState = EntityState.NotChanged;
return(entity);
}
/// <summary>
/// 转换属性的值
/// </summary>
/// <param name="propertyInfo">属性信息</param>
/// <param name="source">从数据源获取到的值</param>
/// <param name="destination">目标类型</param>
/// <returns></returns>
public static object ConvertPropertyValue(PropertyInfo propertyInfo, object source, DBType destination)
{
Type destinationType = null;
switch (destination)
{
case DBType.String:
destinationType = typeof(string);
break;
case DBType.Int:
destinationType = typeof(int);
break;
case DBType.Decimal:
destinationType = typeof(decimal);
break;
case DBType.DateTime:
destinationType = typeof(DateTime);
break;
case DBType.BigInt:
destinationType = typeof(long);
break;
}
if (destinationType == null)
{
return(null);
}
var converter = EntityParser.GetTypeConverter(propertyInfo); //获取类型转换器
if (converter != null)
{
return(converter.ConvertTo(source, destinationType)); //使用自定义类型转换器将值转换为另一种类型
}
return(source.To(destinationType));
}
/// <summary>
/// 将实体实例的属性的值更新到数据库中，必须指定主键
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="entity"></param>
/// <param name="updateToEntity"></param>
/// <returns></returns>
public static TEntity Update <TEntity>(TEntity entity, bool updateToEntity = true) where TEntity : SQLEntity
{
if (entity.EntityState == EntityState.NotInitialized)
{
throw new Exception("要更新的实体尚未初始化");
}
else if (entity.EntityState == EntityState.NotChanged)
{
return(entity);
}
else
{
var entityType = typeof(TEntity);
var key = EntityParser.GetKey(entityType); //找到主键属性
if (key == null)
{
throw new Exception("在实体类中没有找主键属性");
}
List <SqlParameter> parameters = new List <SqlParameter>();
var tableName = EntityParser.GetTableName(entityType);
StringBuilder update = new StringBuilder();
update.AppendFormat("UPDATE [{0}] SET ", tableName);
var flags = false;
foreach (var propertyName in entity) //遍历更改的属性
{
if (propertyName == key.PropertyName)
{
continue;
}
var propertyInfo = entityType.GetProperty(propertyName);
if (propertyInfo == null || EntityParser.IsIgnore(propertyInfo)) //如果找不到属性或不映射到数据库
{
continue;
}
var sqlPair = SqlBuilder.ColumnSqlForUpdate(entity, propertyInfo, parameters); //获取更新列值的sql语句
if (string.IsNullOrEmpty(sqlPair))
{
continue;
}
else
{
string columnName = EntityParser.GetColumnName(propertyInfo);
update.AppendFormat("{0} = {1}, ", columnName, sqlPair);
flags = true;
}
}
if (flags)
{
update.Remove(update.Length - 2, 2).AppendFormat(" ");
}
else
{
return(entity);
}
update.AppendFormat(" WHERE {0} = @{0}", key.KeyName);
var keyValue = key.Key.GetValue(entity, null); //获取主键的值
parameters.Add(new SqlParameter("@" + key.PropertyName, keyValue));
SQLServerHelper.ExecuteNonQuery(update.ToString(), parameters.ToArray()); //更新到数据库
entity.EntityState = EntityState.NotChanged; //未更改
if (updateToEntity)
{
//SingleByKey(keyValue, entity); //从数据库刷新属性
RefreshEntity(entity);
}
}
return(entity);
}
/// <summary>
/// 向数据库中插入一个实体并返回实体类的主键的值，并从数据库中刷新实体的所有值
/// </summary>
/// <typeparam name="TKey"></typeparam>
/// <typeparam name="TEntity"></typeparam>
/// <param name="entity">要插入到数据库的实体类的实例</param>
/// <returns></returns>
public static TEntity Insert <TEntity>(TEntity entity) where TEntity : SQLEntity
{
var entityType = typeof(TEntity);
List <ColumnContext> contexts;
if (!insertCache.TryGetValue(entityType, out contexts))
{
contexts = new List <ColumnContext>();
foreach (var propertyInfo in entityType.GetProperties())
{
if (EntityParser.IsIgnore(propertyInfo))
{
continue;
}
var config = EntityParser.GetColumnConfig(propertyInfo);
var context = new ColumnContext()
{
Config = config,
PropertyInfo = propertyInfo,
PropertyName = propertyInfo.Name,
ColumnName = EntityParser.GetColumnName(propertyInfo)
};
contexts.Add(context);
}
insertCache.Add(entityType, contexts);
}
List <SqlParameter> parameters = new List <SqlParameter>();
foreach (var context in contexts)
{
if (context.Config.UsePropertyForInsert)
{
var valueExpression = "@" + context.PropertyName;
var propertyValue = context.PropertyInfo.GetValue(entity, null);
if (context.Config.DBType == DBType.PropertyType) //如果直接用属性
{
parameters.Add(new SqlParameter(valueExpression, propertyValue));
}
else //如果要转换类型
{
propertyValue = EntityParser.ConvertPropertyValue(context.PropertyInfo, propertyValue, context.Config.DBType);
parameters.Add(new SqlParameter(valueExpression, propertyValue));
}
}
}
var keyContext = EntityParser.GetKey(entityType); //寻找主键特性
object key;
string sql = SqlBuilder.InsertSQL(entityType);
if (keyContext.KeyConfig.UsePropertyForInsert) //如果使用属性的值插入到数据库
{
SQLServerHelper.ExecuteNonQuery(sql, parameters.ToArray());
key = keyContext.Key.GetValue(entity, null);
}
else
{
key = SQLServerHelper.GetSingle(sql, parameters.ToArray()); //插入并获取主键的值
}
var se = entity as SQLEntity;
if (se != null)
{
se.EntityState = EntityState.NotInitialized; //未初始化
}
string selectSQL = SqlBuilder.SelectSQL(typeof(TEntity), string.Format("{0} = @{0}", keyContext.PropertyName));
SQLServerHelper.FirstOrDefault((reader) =>
{
return(FilldEntity <TEntity>(reader, entity));
}, selectSQL, new SqlParameter("@" + keyContext.PropertyName, key)); //从数据库中刷新实体
if (se != null)
{
se.EntityState = EntityState.NotChanged; //未修改
}
return(entity);
}
/// <summary>
/// 根据指定的条件删除多个实体
/// </summary>
/// <typeparam name="TEntity"></typeparam>
/// <param name="where">条件</param>
/// <param name="pms">参数</param>
public static void Delete <TEntity>(string where, params SqlParameter[] pms) where TEntity : SQLEntity
{
var sql = SqlBuilder.DeleteSQL(EntityParser.GetTableName(typeof(TEntity)), where);
SQLServerHelper.ExecuteNonQuery(sql, pms);
}
