/// <summary> /// 使用SQL语句查询,获取结果集合 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="sql"></param> /// <param name="parameters"></param> /// <returns></returns> public override IEnumerable <T> GetListBySql <T>(string sql, dynamic parameters = null) { using (IDbConnection connection = OpenConnection(SqlExecuteType.Read)) { Type entityType = typeof(T); if (CheckListEnableCache(entityType)) { //找到主键 var pk = GetPrimaryKey(entityType); //找到第一个FROM关键字,替换前面的SELECT var tableInfo = TableInfoAttribute.GetAttribute(entityType); var tableName = tableInfo == null ? entityType.Name : tableInfo.TableName; var index = sql.IndexOf(" FROM ", StringComparison.OrdinalIgnoreCase); var pkSql = "SELECT " + tableName + "." + pk.Name + " FROM " + sql.Substring(index + " FROM ".Length); var entityIds = FetchListBySql <int>(pkSql); return(GetListByEntityIds <T>(entityIds)); } OnExecutingCommand(sql); var result = connection.Query <T>(sql, parameters as object); OnExecutedCommand(sql); return(result); } }
/// <summary> /// 根据Id获取实体 /// </summary> /// <typeparam name="T">类型</typeparam> /// <param name="id">主键Id</param> /// <param name="primaryKeyField"></param> /// <returns></returns> public T Get <T>(string id, string primaryKeyField = "") where T : BaseEntity { //find key Type type = typeof(T); T entity; PropertyInfo pk; PropertyInfo[] ps = type.GetProperties(); if (string.IsNullOrEmpty(primaryKeyField)) { pk = GetPrimaryKey(type); } else { pk = ps.FirstOrDefault(p => p.Name == primaryKeyField); } if (pk == null) { throw new Exception(string.Format("实体{0}没有设置主键", type.FullName)); } TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type); string where = string.Format("{0}.{1}='{2}'", tableInfo == null ? type.Name : tableInfo.TableName, pk.Name, id); entity = GetList <T>(where).FirstOrDefault(); //if (entity != null) // foreach (var item in ps) // { // entity.Dbvalue.Add(new KeyValuePair<string, object>(item.Name, item.GetValue(entity, null))); // } return(entity); }
private static TableInformation GetInformation(Type t) { TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(t); var information = new TableInformation(); if (tableInfo == null) { information.TableName = t.Name; } else { information.TableName = tableInfo.TableName; } //主键 var ps = t.GetProperties(); foreach (var info in ps) { IdentityAttribute id = IdentityAttribute.GetAttribute(info); if (id != null) { information.PrimaryKey = info.Name; information.AutoIncrement = true; break; } } return(information); }
private string GenerateUpdateSql(Type type) { //判断缓存中存在已经生成的Sql语句,则直接返回 if (_updateSqlCaches.ContainsKey(type)) { return(_updateSqlCaches[type]); } PropertyInfo[] propertyInfos = type.GetProperties(); TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type); string tableName = tableInfo == null ? type.Name : tableInfo.TableName; var updateSql = new StringBuilder(); updateSql.AppendFormat("UPDATE {0} SET ", tableName); int columnCount = 0; foreach (PropertyInfo info in propertyInfos) { ExtendedAttribute extended = ExtendedAttribute.GetAttribute(info); if (extended != null) { continue; } ExcludeFieldAttribute exclude = ExcludeFieldAttribute.GetAttribute(info); if (exclude != null) { continue; } IdentityAttribute identity = IdentityAttribute.GetAttribute(info); if (identity != null) { continue; } RefFieldAttribute refField = RefFieldAttribute.GetAttribute(info); if (refField != null) { continue; } GuidIdentityAttribute guidIdentity = GuidIdentityAttribute.GetAttribute(info); if (guidIdentity != null) { continue; } if (columnCount != 0) { updateSql.Append(","); } updateSql.AppendFormat("{0}=@{0}", info.Name); columnCount++; } string updateString = updateSql.ToString(); _updateSqlCaches[type] = updateString; return(updateString); }
private string GenerateUpdateSql(Type type, BaseEntity entity) { PropertyInfo[] propertyInfos = type.GetProperties(); TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type); string tableName = tableInfo == null ? type.Name : tableInfo.TableName; var updateString = new StringBuilder(); updateString.AppendFormat("UPDATE {0} SET ", tableName); int columnCount = 0; foreach (PropertyInfo info in propertyInfos) { ExtendedAttribute extended = ExtendedAttribute.GetAttribute(info); if (extended != null) { continue; } ExcludeFieldAttribute exclude = ExcludeFieldAttribute.GetAttribute(info); if (exclude != null) { continue; } IdentityAttribute identity = IdentityAttribute.GetAttribute(info); if (identity != null) { continue; } RefFieldAttribute refField = RefFieldAttribute.GetAttribute(info); if (refField != null) { continue; } GuidIdentityAttribute guidIdentity = GuidIdentityAttribute.GetAttribute(info); if (guidIdentity != null) { continue; } //var dbv = entity.Dbvalue.FirstOrDefault(p => p.Key == info.Name); //var newvalue = info.GetValue(entity, null); //if (dbv.Value != null && newvalue != null) // if (dbv.ToString() == newvalue.ToString()) continue; if (columnCount != 0) { updateString.Append(","); } updateString.AppendFormat("{0}=@{0}", info.Name); columnCount++; } if (columnCount == 0) { return(""); } return(updateString.ToString()); }
private string GenerateUpdateSql(BaseEntity entity) { var type = entity.GetType(); PropertyInfo[] propertyInfos = type.GetProperties(); TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type); string tableName = tableInfo == null ? type.Name : tableInfo.TableName; var updateString = new StringBuilder(); updateString.AppendFormat("UPDATE {0} SET ", tableName); int columnCount = 0; foreach (PropertyInfo info in propertyInfos) { ExtendedAttribute extended = ExtendedAttribute.GetAttribute(info); if (extended != null) { continue; } ExcludeFieldAttribute exclude = ExcludeFieldAttribute.GetAttribute(info); if (exclude != null) { continue; } IdentityAttribute identity = IdentityAttribute.GetAttribute(info); if (identity != null) { continue; } RefFieldAttribute refField = RefFieldAttribute.GetAttribute(info); if (refField != null) { continue; } if (!object.Equals(entity.Dbvalue[info.Name], info.GetValue(entity, null))) { if (columnCount != 0) { updateString.Append(","); } updateString.AppendFormat("{0}=@{0}", info.Name); columnCount++; } } if (columnCount == 0) { return(""); } return(updateString.ToString()); }
/// <summary> /// 根据主键Id获取实体集合 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entityIds"></param> /// <returns></returns> public List <T> GetListByEntityIds <T>(IEnumerable <string> entityIds) where T : BaseEntity { T[] tArray = new T[entityIds.Count()]; IDictionary <string, int> objs = new Dictionary <string, int>(); for (int i = 0; i < entityIds.Count(); i++) { tArray[i] = default(T); objs[entityIds.ElementAt(i)] = i; } if (objs.Any()) { var entityType = typeof(T); var tableInfoAttr = TableInfoAttribute.GetAttribute(entityType); var pk = entityType.GetProperties().FirstOrDefault(p => IdentityAttribute.GetAttribute(p) != null); var tableName = tableInfoAttr == null ? entityType.Name : tableInfoAttr.TableName; if (pk == null) { pk = entityType.GetProperties().FirstOrDefault(p => GuidIdentityAttribute.GetAttribute(p) != null); } if (pk != null) { var idsString = ""; for (int i = 0; i < objs.Keys.ToArray().Length; i++) { idsString = idsString + (i == 0 ? ("'" + objs.Keys.ToArray()[i] + "'") : (",'" + objs.Keys.ToArray()[i] + "'")); } var datalist = FetchEntities <T>(tableName + "." + pk.Name + " IN(" + idsString + ")"); foreach (var entity1 in datalist) { tArray[objs[entity1.EntityId]] = entity1; } } } List <T> tEntities = new List <T>(); T[] tArray1 = tArray; for (int i = 0; i < tArray1.Length; i++) { T entity2 = tArray1[i]; if (entity2 != null) { tEntities.Add(entity2); } } return(tEntities); }
/// <summary> /// 根据主键Id获取实体集合 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="entityIds"></param> /// <returns></returns> public override List <T> GetListByEntityIds <T>(IEnumerable <int> entityIds) { T[] tArray = new T[entityIds.Count()]; IDictionary <int, int> objs = new Dictionary <int, int>(); for (int i = 0; i < entityIds.Count(); i++) { T entity = CacheHelper.CacheService.Get <T>(EntityUpdateTrackHelper.GetEntityKey(typeof(T), entityIds.ElementAt(i))); if (entity == null) { tArray[i] = default(T); objs[entityIds.ElementAt(i)] = i; } else { tArray[i] = entity; } } if (objs.Any()) { var entityType = typeof(T); var tableInfoAttr = TableInfoAttribute.GetAttribute(entityType); var pk = entityType.GetProperties().FirstOrDefault(p => IdentityAttribute.GetAttribute(p) != null); var tableName = tableInfoAttr == null ? entityType.Name : tableInfoAttr.TableName; if (pk != null) { var datalist = FetchEntities <T>(tableName + "." + pk.Name + " IN(" + objs.Keys.ToArray().GetString() + ")"); foreach (var entity1 in datalist) { tArray[objs[entity1.EntityId]] = entity1; CacheHelper.CacheService.Add(EntityUpdateTrackHelper.GetEntityKey(entity1), entity1, CachingExpirationType.SingleObject); } } } List <T> tEntities = new List <T>(); T[] tArray1 = tArray; for (int i = 0; i < tArray1.Length; i++) { T entity2 = tArray1[i]; if (entity2 != null) { tEntities.Add(entity2); } } return(tEntities); }
private static Type GetTypeByTableName(string virtualTableName) { string sqlAssembly = typeof(Creative).Assembly.FullName; string classNamespace = typeof(Creative).Namespace; Type type = Type.GetType(string.Format("{0}.{1},{2}", classNamespace, virtualTableName, sqlAssembly)); //Get type from Attribute table name #region Try Get Type from Attribute /************************************************/ if (type == null) // Unrecognized type name ( ex. TargetKeyword ) { var assemblyNestedTypes = from t in Assembly.GetAssembly(typeof(Creative)).GetTypes() where t.IsClass && t.Namespace == classNamespace select t; foreach (Type t in assemblyNestedTypes) { TableInfoAttribute tableInfo = (TableInfoAttribute)Attribute.GetCustomAttribute(t, typeof(TableInfoAttribute)); if (tableInfo != null && tableInfo.Name == virtualTableName) { type = t; } } } /************************************************/ #endregion //Get type from Meta Property Name #region Try Get Type from Meta Property Table /************************************************/ if (type == null) // still Unrecognized type name ( ex. color ) { Int32 metaPropertyID = 0; string baseValueType = GetMetaPropertyBaseValueType(virtualTableName, SqlInt32.Null, out metaPropertyID); if (!string.IsNullOrEmpty(baseValueType)) { type = Type.GetType(string.Format("{0}.{1},{2}", classNamespace, baseValueType, sqlAssembly)); } /************************************************/ #endregion } return(type); }
/// <summary> /// 通过限制条件,批量数据库记录删除 /// </summary> /// <typeparam name="T"></typeparam> /// <param name="where"></param> /// <returns></returns> public override int DeleteEntities <T>(string where) { if (string.IsNullOrEmpty(where)) { throw new Exception("删除条件不能为空"); } if (!where.Trim().StartsWith("where", StringComparison.InvariantCultureIgnoreCase)) { where = " WHERE " + where; } Type type = typeof(T); TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type); string tableName = tableInfo == null ? type.Name : tableInfo.TableName; string deleteSql = "DELETE FROM " + tableName + " " + where; return(ExecuteSql(deleteSql)); }
/// <summary> /// 根据Id获取实体 /// </summary> /// <typeparam name="T">类型</typeparam> /// <param name="id">主键Id</param> /// <param name="primaryKeyField"></param> /// <returns></returns> public override T Get <T>(int id, string primaryKeyField = "") { //find key Type type = typeof(T); T entity; if (CheckEnableCache(type)) { entity = CacheHelper.CacheService.Get <T>(EntityUpdateTrackHelper.GetEntityKey(type, id)); if (entity != null) { return(entity); } } PropertyInfo pk; PropertyInfo[] ps = type.GetProperties(); if (string.IsNullOrEmpty(primaryKeyField)) { pk = GetPrimaryKey(type); } else { pk = ps.FirstOrDefault(p => p.Name == primaryKeyField); } if (pk == null) { throw new Exception(string.Format("实体{0}没有设置主键", type.FullName)); } TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type); string where = string.Format("{0}.{1}={2}", tableInfo == null ? type.Name : tableInfo.TableName, pk.Name, id); entity = GetList <T>(where).FirstOrDefault(); if (entity != null) { CacheHelper.CacheService.Add(EntityUpdateTrackHelper.GetEntityKey(type, id), entity, CachingExpirationType.SingleObject); } //if (entity != null) // foreach (var item in ps) // { // entity.Dbvalue.Add(new KeyValuePair<string, object>(item.Name, item.GetValue(entity, null))); // } return(entity); }
/// <summary> /// 生成更新的Sql /// </summary> /// <param name="type"></param> /// <returns></returns> protected string GenerateUpdateSql(Type type) { PropertyInfo[] propertyInfos = type.GetProperties(); TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type); string tableName = tableInfo == null ? type.Name : tableInfo.TableName; var updateSql = new StringBuilder(); updateSql.AppendFormat("UPDATE {0} SET ", tableName); int columnCount = 0; foreach (PropertyInfo info in propertyInfos) { ExtendedAttribute extended = ExtendedAttribute.GetAttribute(info); if (extended != null) { continue; } ExcludeFieldAttribute exclude = ExcludeFieldAttribute.GetAttribute(info); if (exclude != null) { continue; } IdentityAttribute identity = IdentityAttribute.GetAttribute(info); if (identity != null) { continue; } RefFieldAttribute refField = RefFieldAttribute.GetAttribute(info); if (refField != null) { continue; } if (columnCount != 0) { updateSql.Append(","); } updateSql.AppendFormat("{0}=@{0}", info.Name); columnCount++; } string updateString = updateSql.ToString(); return(updateString); }
public static bool IsModelValidateUserID(object model) { if (model == null) { throw new Exception("验证model不能为空"); } object[] tableAttributes = model.GetType().GetCustomAttributes(typeof(TableInfoAttribute), true); if (tableAttributes.Length == 0) { throw new Exception("model无TableInfoAttribute特性,不能验证"); } TableInfoAttribute att = tableAttributes[0] as TableInfoAttribute; PropertyInfo primary = model.GetType().GetProperty(att.PrimaryName); if (primary == null) { throw new Exception("model无" + att.PrimaryName + "属性,不能验证"); } object value = primary.GetValue(model, null); if (value == null) //如果主键值为空 说明是新增 直接通过 { return(true); } string sql = "select " + att.UserIDName + " from " + att.TableName + " where " + att.PrimaryName + " =@" + att.PrimaryName; DataTable dt = DbInstance.GetDataTable(sql, DbInstance.CreateParameter("@" + att.PrimaryName, value)); if (dt.Rows.Count == 0) { throw new CustomException("id不存在"); } if (dt.Rows[0][0].ToString() == IocFactory <IUserInfo> .Instance.GetUserID()) { return(true); } else { return(false); } }
public static bool IsIdsValidateUserID(Type modeltype, string ids) { if (String.IsNullOrEmpty(ids)) { return(true); } object[] tableAttributes = modeltype.GetCustomAttributes(typeof(TableInfoAttribute), true); if (tableAttributes.Length == 0) { throw new Exception("modeltype无TableInfoAttribute特性,不能验证"); } TableInfoAttribute att = tableAttributes[0] as TableInfoAttribute; string tmp = ""; foreach (string s in ids.Split(new char[] { ',' }, StringSplitOptions.RemoveEmptyEntries)) { string t = s.Trim('\''); tmp += "'" + t + "',"; } tmp = tmp.TrimEnd(','); string sql = "select " + att.UserIDName + " from " + att.TableName + " where " + att.PrimaryName + " in (" + tmp + ")"; DataTable dt = DbInstance.GetDataTable(sql); if (dt.Rows.Count == 0) { throw new CustomException("id不存在"); } foreach (DataRow dr in dt.Rows) { //只要有一项的userID不等于参数userID 则认证失败 if (dr[0].ToString() != IocFactory <IUserInfo> .Instance.GetUserID()) { return(false); } } return(true); }
internal static List <FieldProperty> GetFieldPropertys(Type t) { var fplist = new List <FieldProperty>(); TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(t); PropertyInfo[] ps = t.GetProperties(); #region 取出映射关系 foreach (PropertyInfo item in ps) { var fp = new FieldProperty(); var excludeFieldAttribute = (ExcludeFieldAttribute)Attribute.GetCustomAttribute(item, typeof(ExcludeFieldAttribute)); if (excludeFieldAttribute != null) { continue; } ExtendedAttribute extendedAttribute = ExtendedAttribute.GetAttribute(item); if (extendedAttribute != null) { continue; } RefFieldAttribute fieldattr = RefFieldAttribute.GetAttribute(item); if (fieldattr != null) { fp.TableName = fieldattr.RefTableName; fp.FieldName = fieldattr.RefFieldName ?? item.Name; fp.FieldAlias = item.Name; fp.PropertyName = item.Name; fp.MasterTableField = fieldattr.MasterTableField; fp.RelateField = fieldattr.RefTableKey; fplist.Add(fp); } else { fp.TableName = tableInfo == null ? t.Name : tableInfo.TableName; fp.FieldName = item.Name; fp.PropertyName = item.Name; fplist.Add(fp); } } #endregion List <FieldProperty> tables = Distinct(fplist); for (int i = 0; i < tables.Count; i++) { string alias = "t" + i; foreach (FieldProperty item in fplist) { if (item.TableName == tables[i].TableName && item.MasterTableField == tables[i].MasterTableField) { item.TableAlias = alias; //if (string.IsNullOrEmpty(item.FieldAlias)) // item.FieldAlias = alias + "_" + item.FieldName; } } } return(fplist); }
/// <summary> /// 生成新增的Sql语句 /// </summary> /// <param name="obj"></param> /// <returns></returns> protected string GenerateInsertSql(object obj) { Type type = obj.GetType(); PropertyInfo[] propertyInfos = type.GetProperties(); var insertSql = new StringBuilder(); TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type); string tableName; if (tableInfo == null) { tableName = type.Name; } else { tableName = tableInfo.TableName; } insertSql.AppendFormat("INSERT INTO {0} (", tableName); var values = new StringBuilder(" VALUES ("); int columnCount = 0; foreach (PropertyInfo info in propertyInfos) { ExtendedAttribute extended = ExtendedAttribute.GetAttribute(info); if (extended != null) { continue; } ExcludeFieldAttribute exclude = ExcludeFieldAttribute.GetAttribute(info); if (exclude != null) { continue; } IdentityAttribute identity = IdentityAttribute.GetAttribute(info); if (identity != null) { continue; } RefFieldAttribute refField = RefFieldAttribute.GetAttribute(info); if (refField != null) { continue; } if (columnCount != 0) { insertSql.Append(","); values.Append(","); } object value = info.GetValue(obj, null); if (value == null || value == DBNull.Value) { value = "NULL"; } else { value = string.Format("'{0}'", value.ToString().ReplaceInsertSql()); } insertSql.AppendFormat("{0}", info.Name); values.Append(value); columnCount++; } insertSql.AppendFormat(") {0} ) ", values); string insertSqlstr = insertSql.ToString() + ";"; //_insertSqlCaches.Add(type, insertSqlstr);//加入缓存 return(insertSqlstr); }
/// <summary> /// 生成新增的Sql语句 /// </summary> /// <param name="type"></param> /// <returns></returns> internal string GenerateInsertSql(Type type) { //判断缓存中存在已经生成的Sql语句,则直接返回 if (_insertSqlCaches.ContainsKey(type)) { return(_insertSqlCaches[type]); } PropertyInfo[] propertyInfos = type.GetProperties(); var insertSql = new StringBuilder(); bool hasIdentityField = false; TableInfoAttribute tableInfo = TableInfoAttribute.GetAttribute(type); string tableName = tableInfo == null ? type.Name : tableInfo.TableName; insertSql.AppendFormat("INSERT INTO {0} (", tableName); var values = new StringBuilder(" VALUES ("); int columnCount = 0; foreach (PropertyInfo info in propertyInfos) { ExtendedAttribute extended = ExtendedAttribute.GetAttribute(info); if (extended != null) { continue; } ExcludeFieldAttribute exclude = ExcludeFieldAttribute.GetAttribute(info); if (exclude != null) { continue; } IdentityAttribute identity = IdentityAttribute.GetAttribute(info); if (identity != null) { hasIdentityField = true; continue; } GuidIdentityAttribute guidIdentity = GuidIdentityAttribute.GetAttribute(info); if (guidIdentity != null) { hasIdentityField = true; } RefFieldAttribute refField = RefFieldAttribute.GetAttribute(info); if (refField != null) { continue; } if (columnCount != 0) { insertSql.Append(","); values.Append(","); } insertSql.AppendFormat("{0}", info.Name); values.AppendLine("@" + info.Name); columnCount++; } insertSql.AppendFormat(") {0} ) ", values); if (hasIdentityField) { insertSql.AppendFormat(_identity); } string insertSqlstr = insertSql.ToString(); _insertSqlCaches.Add(type, insertSqlstr); //加入缓存 return(insertSqlstr); }