public void ConstructStatement <T1>(T1 id, Type type, ConstructedStatementType statementType) { if (statementType == ConstructedStatementType.GetList) { ConstructGetListStatement(id, type); return; } DbTableAttribute dbTable = type.GetCustomAttributes(false)?.OfType <DbTableAttribute>()?.FirstOrDefault(); Debug.Assert(dbTable != null, $"Statement Construction failed for type {type}"); string query = ""; string tableName = dbTable.DbTableName; List <MySqlDbType> types = new List <MySqlDbType>(); List <MySqlDbType> criteriaTypes = new List <MySqlDbType>(); string updateList = null; string insertList = null; string insertValues = null; string selectList = null; string whereList = null; var properties = GetPropertyCache(type); foreach (var p in properties) { if (p.Item2.Get) { if (selectList != null) { selectList += ", "; } selectList += p.Item2.DbFieldName; } if (p.Item2.Update && statementType == ConstructedStatementType.Update) { if (updateList != null) { updateList += ", "; } updateList += "`" + p.Item2.DbFieldName + "` = ?"; types.Add((MySqlDbType)p.Item2.DbFieldType); } if (p.Item2.Insert && statementType == ConstructedStatementType.Insert) { if (insertList != null) { insertList += ", "; } insertList += p.Item2.DbFieldName; if (insertValues != null) { insertValues += ", "; } insertValues += "?"; types.Add((MySqlDbType)p.Item2.DbFieldType); } if (p.Item2.IsCriteria) { if (whereList != null) { whereList += " AND "; } whereList += "`" + p.Item2.DbFieldName + "` = ?"; if (statementType == ConstructedStatementType.Get || statementType == ConstructedStatementType.Update) { criteriaTypes.Add((MySqlDbType)p.Item2.DbFieldType); } } } types.AddRange(criteriaTypes); switch (statementType) { case ConstructedStatementType.Get: query = $"SELECT {selectList} FROM `{tableName}` WHERE {whereList}"; break; case ConstructedStatementType.Insert: query = $"INSERT INTO `{tableName}` ({selectList}) VALUES ({insertValues})"; break; case ConstructedStatementType.Update: query = $"UPDATE `{tableName}` SET {updateList} WHERE {whereList}"; break; } PrepareStatement(Convert.ToUInt32(id), query, types); }
/// <summary> /// 使用提供的条件查询数据库(表由entityType指定),返回满足条件的记录(分页) /// </summary> /// <typeparam name="T">要查询的实体类型,标记了DbTable和DbField特性的类,并且具有空白构造函数</typeparam> /// <param name="connector"></param> /// <param name="criteria">查询条件字典,键是PropertyName,条件仅支持=比较,条件间仅支持AND运算</param> /// <param name="pageIndex">从0开始的返回页索引</param> /// <param name="pageSize">每页几行记录</param> /// <param name="sortProperty">用于排序的PropertyName(如为空则使用关键字)</param> /// <param name="descending">是否倒序排列</param> /// <param name="total">符合条件的记录总数</param> /// <returns></returns> public static async Task <PagedQuery <T> > SearchEntities <T>(DbConnector connector, Dictionary <string, object> criteria, int pageIndex, int pageSize, string sortProperty, bool descending) where T : new() { Type entityType = typeof(T); if (!(DbTableAttribute.GetCustomAttribute(entityType, typeof(DbTableAttribute)) is DbTableAttribute tableAttr)) { throw new Exception("类型" + entityType.FullName + "没有标记DbTableAttribute特性"); } string selectClause = "SELECT * "; string fromClause = "FROM " + tableAttr.TableName; string orderBy; #region 正确处理orderBy DbFieldAttribute sortFieldAttr = null; if (string.IsNullOrWhiteSpace(sortProperty)) { //如果没有传递sortProperty参数, 则使用PrimaryKey foreach (PropertyInfo property in entityType.GetProperties()) { DbFieldAttribute fldAttr = DbFieldAttribute.GetCustomAttribute(property, typeof(DbFieldAttribute)) as DbFieldAttribute; if (fldAttr != null && fldAttr.IsPrimaryKey) { sortFieldAttr = fldAttr; break; } } } else { PropertyInfo property = entityType.GetProperty(sortProperty); if (property == null) { throw new Exception(string.Format("类型{0}中没有找到{1}属性用于排序", entityType.FullName, sortProperty)); } sortFieldAttr = DbFieldAttribute.GetCustomAttribute(property, typeof(DbFieldAttribute)) as DbFieldAttribute; } if (sortFieldAttr == null) { throw new Exception("类型" + entityType.FullName + "没有标记IsPrimaryKey=true的DbField特性"); } if (descending) { orderBy = sortFieldAttr.FieldName + " DESC"; } else { orderBy = sortFieldAttr.FieldName; } #endregion StringBuilder whereBuilder = new StringBuilder(); List <SqlParameter> sqlParameters = new List <SqlParameter>(); if (criteria != null) { foreach (string conditionName in criteria.Keys) { if (criteria[conditionName] == null || criteria[conditionName].ToString().Length == 0) { continue; } PropertyInfo conditionProperty = entityType.GetProperty(conditionName); if (conditionProperty == null) { throw new Exception(string.Format("类型{0}中没有找到{1}属性用于查询条件", entityType.FullName, conditionName)); } DbFieldAttribute conditionAttr = DbFieldAttribute.GetCustomAttribute(conditionProperty, typeof(DbFieldAttribute)) as DbFieldAttribute; if (conditionAttr == null) { throw new Exception(string.Format("类型{0}的{1}属性没有标记DbFieldAttribute特性, 无法用于数据库查询", entityType.FullName, conditionName)); } SqlParameter parameter = GenerateSqlParameter(conditionAttr, conditionProperty.PropertyType, criteria[conditionName]); sqlParameters.Add(parameter); whereBuilder.AppendFormat("[{0}]=@{0} ", conditionAttr.FieldName); whereBuilder.Append("AND "); } } if (whereBuilder.Length > 0) { whereBuilder.Remove(whereBuilder.Length - 4, 4); } string sql = BuildPagedSelectSql(selectClause, fromClause, whereBuilder.ToString(), orderBy, pageIndex, pageSize); DataSet ds = await connector.ExecuteSqlQuerySet(sql, sqlParameters.ToArray()); PagedQuery <T> result = new PagedQuery <T>(); if (ds != null && ds.Tables.Count == 2) { result.Total = (int)ds.Tables[0].Rows[0][0]; ds.Tables[1].TableName = "result"; result.Records = await Task.Run(() => ValueHelper.WrapEntities <T>(ds.Tables[1])); } else { result.Total = 0; result.Records = new T[0]; } ds.Dispose(); return(result); }
private static void BuildDomainObjects(System.Type t, DomainModel.Spi.DomainModel domainModel, DomainObjectAttribute rootDomainObjectAttr, DbTableAttribute rootDbTableAttr) { var rootDomainObject = new DomainObject { ID = Guid.NewGuid().ToString(), Name = rootDomainObjectAttr.Name, ClazzReflectType = t.FullName, DomainModel = domainModel, IsLazyLoad = rootDomainObjectAttr.IsLazyload, }; var dataObject = new Sharding.Database.DataObject() { Name = rootDbTableAttr.Name, IsView = rootDbTableAttr.IsView, ID = Guid.NewGuid().ToString(), TableShardingStrategyID = rootDbTableAttr.TableShardingStrategyID, DatabaseShardingStrategyID = rootDbTableAttr.DBShardingStrategyID, LogicTableName = rootDbTableAttr.Name, DataSourceName = rootDbTableAttr.DataSourceName }; if (string.IsNullOrEmpty(rootDbTableAttr.ID) != false) { dataObject = new Sharding.Database.DataObject() { ID = rootDbTableAttr.ID }; } rootDomainObject.DataObject = dataObject; domainModel.RootDomainObject = rootDomainObject; }