public static TableMetaData GetTableMetaData(Type entityType) { TableMetaData result; if (_Cache.TryGetValue(entityType, out result)) { return(result); } lock (_Lock) { if (!_Cache.ContainsKey(entityType)) { TableMetaData tableMetaData = TableMetaData.ParseTableMetaData(entityType); _Cache.Add(entityType, tableMetaData); } return(_Cache[entityType]); } }
/// <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 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 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); }
/// <summary> /// 解析实体类型的元数据(允许不定义TableAttribute) /// </summary> public static TableMetaData ParseTableMetaData(Type entityType) { TableMetaData table = new TableMetaData(); table.EntityType = entityType; //表名 TableAttribute[] tableAttributes = (TableAttribute[])entityType.GetCustomAttributes(typeof(TableAttribute), false); if (tableAttributes.Length > 0) { table.TableName = string.IsNullOrEmpty(tableAttributes[0].TableName) ? entityType.Name : tableAttributes[0].TableName; } //列集合 table.NormalColumns = new List <ColumnMetaData>(); table.PropertiesDic = new Dictionary <string, PropertyMetaData>(); foreach (PropertyInfo pi in entityType.GetProperties()) { string piNameUpper = pi.Name.ToUpper(); if (table.PropertiesDic.ContainsKey(piNameUpper)) { throw new MappingException(string.Format("实体类型{0}中的属性名或列名{1}重复", entityType.Name, pi.Name)); } PropertyMetaData propertyData = new PropertyMetaData(); propertyData.PropertyType = pi.PropertyType; propertyData.PropertyAccessor = new DynamicPropertyAccessor(pi); table.PropertiesDic.Add(piNameUpper, propertyData); ColumnAttribute[] columnAttributes = (ColumnAttribute[])pi.GetCustomAttributes(typeof(ColumnAttribute), false); if (columnAttributes.Length > 0) { #region 解析Column ColumnMetaData column = new ColumnMetaData(); column.PropertyType = pi.PropertyType; column.PropertyAccessor = new DynamicPropertyAccessor(pi); column.ColumnName = string.IsNullOrEmpty(columnAttributes[0].ColumnName) ? pi.Name : columnAttributes[0].ColumnName; column.ColumnCategory = columnAttributes[0].ColumnCategory; switch (column.ColumnCategory) { case Category.Normal: case Category.ReadOnly: table.NormalColumns.Add(column); break; case Category.IdentityKey: case Category.Key: if (table.PKColumn != null) { throw new MappingException(string.Format("实体类型{0}中定义了超过一个主键列:{1}和{2}", entityType.Name, table.PKColumn.ColumnName, column.ColumnName)); } table.PKColumn = column; break; case Category.Version: if (column.PropertyType != typeof(DateTime) && column.PropertyType != typeof(Int32) && column.PropertyType != typeof(Int64)) { throw new MappingException(string.Format("实体类型{0}中的Version列{1}的数据类型无效", entityType.Name, column.ColumnName)); } if (table.VersionColumn != null) { throw new MappingException(string.Format("实体类型{0}中定义了超过一个Version列:{1}和{2}", entityType.Name, table.VersionColumn.ColumnName, column.ColumnName)); } table.VersionColumn = column; break; default: throw new NotSupportedException("无效的ColumnCategory:" + columnAttributes[0].ColumnCategory); } //如果列名与属性名不同,则添加两个属性到dic中 if (!string.IsNullOrEmpty(columnAttributes[0].ColumnName)) { string columnNameUpper = columnAttributes[0].ColumnName.ToUpper(); if (columnNameUpper != piNameUpper) { if (table.PropertiesDic.ContainsKey(columnNameUpper)) { throw new MappingException(string.Format("实体类型{0}中的属性名或列名{1}重复", entityType.Name, columnAttributes[0].ColumnName)); } table.PropertiesDic.Add(columnNameUpper, propertyData); } } #endregion } } if (!string.IsNullOrEmpty(table.TableName) && table.PKColumn == null && table.NormalColumns.Count == 0) { throw new MappingException(string.Format("实体类型:{0}中定义了TableAttribute,但未找到定义ColumnAttribute的列", entityType.Name)); } return(table); }
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); }