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