public IList <ExceptionLog> GetPlistExceptionLogs(List <FilterData> filters, PagingInput paging) { paging.Valid(); var filterStringBuilder = _acDomain.RetrieveRequiredService <ISqlFilterStringBuilder>(); RdbDescriptor db = GetExceptionLogDb(); List <DbParameter> prams; var filterString = filterStringBuilder.FilterString(db, filters, "t", out prams); if (!string.IsNullOrEmpty(filterString)) { filterString = " where " + filterString; } var sql = @"select top({0}) * from (SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber,* FROM {3} as t" + filterString + ") a WHERE a.RowNumber > {4}"; var countSql = "select count(*) from ExceptionLog as t" + filterString; var exceptionLogs = new List <ExceptionLog>(); var reader = db.ExecuteReader( string.Format(sql, paging.PageSize, paging.SortField, paging.SortOrder, "ExceptionLog", paging.PageSize * paging.PageIndex), prams.ToArray()); while (reader.Read()) { exceptionLogs.Add(ExceptionLog.Create(reader)); } paging.Total = (int)db.ExecuteScalar(countSql, prams.Select(p => ((ICloneable)p).Clone()).Cast <DbParameter>().ToArray()); reader.Close(); return(exceptionLogs); }
/// <summary> /// /// </summary> /// <param name="dbId"></param> /// <param name="database"></param> /// <returns></returns> public bool TryDb(Guid dbId, out RdbDescriptor database) { if (!_initialized) { Init(); } return(_dicById.TryGetValue(dbId, out database)); }
private static DbParameter CreateParameter(RdbDescriptor db, string parameterName, object value) { var p = db.CreateParameter(); p.ParameterName = parameterName; p.Value = value; return(p); }
private DbParameter CreateParameter(RdbDescriptor rdb, string parameterName, object value, DbType dbType) { var p = rdb.CreateParameter(); p.ParameterName = parameterName; p.Value = value; p.DbType = dbType; return(p); }
public bool TryGetDbView(RdbDescriptor db, string dbViewId, out DbView dbView) { if (!_initialized) { Init(); } if (!_dicById.ContainsKey(db)) { dbView = null; return false; } return _dicById[db].TryGetValue(dbViewId, out dbView); }
public bool TryGetDbTable(RdbDescriptor db, string dbTableId, out DbTable dbTable) { if (!_initialized) { Init(); } if (!_dicById.ContainsKey(db)) { dbTable = null; return(false); } return(_dicById[db].TryGetValue(dbTableId, out dbTable)); }
public bool TryGetDbTableColumn(RdbDescriptor database, string tableColumnId, out DbTableColumn dbTableColumn) { if (!_initialized) { Init(); } if (!_dicById.ContainsKey(database)) { dbTableColumn = null; return(false); } return(_dicById[database].TryGetValue(tableColumnId, out dbTableColumn)); }
public bool TryGetDbViewColumn(RdbDescriptor database, string viewColumnId, out DbViewColumn dbViewColumn) { if (!_initialized) { Init(); } if (!_dicById.ContainsKey(database)) { dbViewColumn = null; return false; } return _dicById[database].TryGetValue(viewColumnId, out dbViewColumn); }
public IAnyLog Get(Guid id) { RdbDescriptor db = GetAnyLogDb(); const string sql = "select * from AnyLog where Id=@Id"; var reader = db.ExecuteReader(sql, CreateParameter(db, "Id", id, DbType.Guid)); AnyLog anyLog = null; if (reader.Read()) { anyLog = AnyLog.Create(reader); } reader.Close(); return(anyLog); }
/// <summary> /// 根据数据库索引该库的所有视图 /// </summary> /// <param name="db"></param> /// <returns></returns> public IReadOnlyDictionary<string, DbView> this[RdbDescriptor db] { get { if (!_initialized) { Init(); } if (!_dicById.ContainsKey(db)) { return new Dictionary<string, DbView>(StringComparer.OrdinalIgnoreCase); } return _dicById[db]; } }
/// <summary> /// 根据数据库索引该库的全部表 /// </summary> /// <param name="db">数据库模型实例</param> /// <returns></returns> public IReadOnlyDictionary <string, DbTable> this[RdbDescriptor db] { get { if (!_initialized) { Init(); } if (!_dicById.ContainsKey(db)) { return(new Dictionary <string, DbTable>(StringComparer.OrdinalIgnoreCase)); } return(_dicById[db]); } }
public bool TryGetDbViewColumns(RdbDescriptor database, DbView view, out IReadOnlyDictionary<string, DbViewColumn> dbViewColumns) { if (!_initialized) { Init(); } if (!_dic.ContainsKey(database)) { dbViewColumns = new Dictionary<string, DbViewColumn>(StringComparer.OrdinalIgnoreCase); return false; } Dictionary<string, DbViewColumn> outDic; var r = _dic[database].TryGetValue(view, out outDic); dbViewColumns = outDic; return r; }
public bool TryGetDbTableColumns(RdbDescriptor database, DbTable table, out IReadOnlyDictionary <string, DbTableColumn> dbTableColumns) { if (!_initialized) { Init(); } if (!_dic.ContainsKey(database)) { dbTableColumns = new Dictionary <string, DbTableColumn>(StringComparer.OrdinalIgnoreCase); return(false); } Dictionary <string, DbTableColumn> outDic; var r = _dic[database].TryGetValue(table, out outDic); dbTableColumns = outDic; return(r); }
/// <summary> /// /// </summary> /// <param name="anyLogs"></param> public void Log(IAnyLog[] anyLogs) { // 本组命令类型所对应的数据库表 const string tableId = "[dbo][AnyLog]"; RdbDescriptor db = GetAnyLogDb(); DbTable dbTable; if (!db.TryGetDbTable(tableId, out dbTable)) { throw new AnycmdException("意外的数据库表标识" + tableId); } // 当前命令表模式克隆得到的新表 var dt = db.NewTable(dbTable); foreach (var log in anyLogs) { // 将当前命令转化DataRow,一个命令对应一行 var row = log.ToDataRow(dt); dt.Rows.Add(row); } db.WriteToServer(dt); }
public RdbContext(RdbDescriptor rdb, string nameOrConnectionString, DbCompiledModel model) : base(nameOrConnectionString, model) { _rdb = rdb; }
/// <summary> /// 根据给定的本体码和信息标识获取本节点两条数据 /// </summary> /// <param name="ontology">本体</param> /// <param name="db"></param> /// <param name="infoIds">多列联合信息标识字典,键必须不区分大小写</param> /// <param name="selectElements">选择元素</param> /// <returns></returns> private TowInfoTuple GetTop2InfoItemSet(OntologyDescriptor ontology, RdbDescriptor db, IEnumerable<InfoItem> infoIds, OrderedElementSet selectElements) { if (infoIds == null || !infoIds.Any()) { return new TowInfoTuple(null, null); } var sb = new StringBuilder(); var sqlParameters = new List<DbParameter>(); var elementList = selectElements.ToList(); sb.Append("select top 2 "); int l = sb.Length; foreach (var element in elementList) { if (sb.Length != l) { sb.Append(","); } sb.Append("t.[").Append(element.Element.FieldCode).Append("]"); } sb.Append(" from [").Append(ontology.Ontology.EntityTableName).Append("] as t where"); if (ontology.Ontology.IsLogicalDeletionEntity) { sb.Append(" t.DeletionStateCode=0 "); } else { sb.Append(" 1=1 "); } foreach (var element in infoIds) { sb.Append(" and t.[").Append(element.Element.Element.FieldCode) .Append("]=").Append("@").Append(element.Element.Element.FieldCode); object obj = element.Value; if (obj == null) { obj = DBNull.Value; } sqlParameters.Add(CreateParameter(db, element.Element.Element.FieldCode, obj, DbType.String)); } var infoValue1 = new List<InfoItem>(); var infoValue2 = new List<InfoItem>(); using (var reader = db.ExecuteReader(sb.ToString(), sqlParameters.ToArray())) { if (reader.Read()) { for (int i = 0; i < elementList.Count; i++) { infoValue1.Add(InfoItem.Create(elementList[i], reader.GetValue(i).ToString())); } } if (reader.Read()) { for (int i = 0; i < elementList.Count; i++) { infoValue2.Add(InfoItem.Create(elementList[i], reader.GetValue(i).ToString())); } } reader.Close(); } return new TowInfoTuple(infoValue1.ToArray(), infoValue2.ToArray()); }
public RdbContext(RdbDescriptor rdb, DbConnection existingConnection, DbCompiledModel model, bool contextOwnsConnection) : base(existingConnection, model, contextOwnsConnection) { _rdb = rdb; }
/// <summary> /// /// </summary> /// <param name="dbId"></param> /// <param name="database"></param> /// <returns></returns> public bool TryDb(Guid dbId, out RdbDescriptor database) { if (!_initialized) { Init(); } return _dicById.TryGetValue(dbId, out database); }
public RdbContext(RdbDescriptor rdb, ObjectContext objectContext, bool dbContextOwnsObjectContext) : base(objectContext, dbContextOwnsObjectContext) { _rdb = rdb; }
/// <summary> /// 将给定的数据查询筛选器列表转化为原生sql查询条件字符串返回 /// </summary> /// <param name="rdb"></param> /// <param name="filters">数据查询筛选器</param> /// <param name="alias">别名,空字符串或null表示无别名</param> /// <param name="prams"></param> /// <returns></returns> public string FilterString(RdbDescriptor rdb, List <FilterData> filters, string alias, out List <DbParameter> prams) { bool useAlias = !string.IsNullOrEmpty(alias); prams = new List <DbParameter>(); if (filters == null || filters.Count == 0) { return(string.Empty); } var result = new StringBuilder(); //type=string #region string var stringList = from f in filters where f.type == "string" select f; int i = 0; foreach (var filter in stringList) { var comparision = GetComparison(filter.comparison); result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field); if (comparision == "like") { result.Append(" ").Append("like").Append(" @").Append(filter.field).Append(i).Append(""); string value = string.Empty; if (filter.value != null) { value = filter.value.ToString(); } if (!value.StartsWith("%") && !value.EndsWith("%")) { value = "%" + value + "%"; } prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), value)); } else { result.Append(comparision).Append("@").Append(filter.field).Append(i); prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), filter.value)); } result.Append(" and "); i++; } #endregion //type=guid #region guid var guidList = from f in filters where f.type == "guid" select f; i = 0; foreach (var filter in guidList) { var comparision = GetComparison(filter.comparison); result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field); result.Append(comparision).Append("@").Append(filter.field).Append(i); if (filter.value.GetType() != typeof(Guid)) { throw new ValidationException("'" + filter.value + "'不是Guid类型的"); } prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), filter.value)); result.Append(" and "); i++; } #endregion //type=boolean #region boolean i = 0; var booleanList = from f in filters where f.type == "boolean" select f; foreach (var filter in booleanList) { var v = filter.value.ToString(); bool b; if (!bool.TryParse(v, out b)) { throw new ValidationException("'" + filter.value + "'不是boolean类型的"); } result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field).Append("=@").Append(filter.field).Append(i.ToString(CultureInfo.InvariantCulture)).Append(" and "); var p = CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), v); prams.Add(p); i++; } #endregion //type=null #region null i = 0; var nullList = from f in filters where f.type == "null" select f; foreach (var filter in nullList) { result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field).Append(GetComparison(filter.comparison)).Append(" and "); i++; } #endregion //type=numeric #region numeric i = 0; var numericList = from f in filters where f.type == "numeric" group f by f.field into g select g; foreach (var g in numericList) { result.Append("( "); string iiStr = string.Empty; int j = 0; foreach (var filter in g) { iiStr += alias + "." + filter.field + GetComparison(filter.comparison) + "@" + filter.field + i.ToString(CultureInfo.InvariantCulture) + j.ToString(CultureInfo.InvariantCulture) + " and "; if (filter.value.GetType() != typeof(int)) { throw new ValidationException("'" + filter.value + "'不是int类型的"); } prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture) + j.ToString(CultureInfo.InvariantCulture), filter.value)); j++; } result.Append(iiStr.Substring(0, iiStr.Length - " and".Length)); result.Append(" )"); result.Append(" and "); i++; } #endregion //type=date #region date i = 0; var dateList = from f in filters where f.type == "date" group f by f.field into g select g; foreach (var g in dateList) { int j = 0; result.Append("( "); string iiStr = string.Empty; foreach (var filter in g) { iiStr += alias + "." + filter.field + GetComparison(filter.comparison) + " @" + filter.field + i.ToString(CultureInfo.InvariantCulture) + j.ToString(CultureInfo.InvariantCulture) + ")" + " and "; prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture) + j.ToString(CultureInfo.InvariantCulture), filter.value)); j++; } result.Append(iiStr.Substring(0, iiStr.Length - " and".Length)); result.Append(" )"); result.Append(" and "); i++; } #endregion //type=list :["1","2"] #region list i = 0; var listList = from f in filters where f.type == "list" select f; foreach (var filter in listList) { result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field).Append(" in @").Append(filter.field).Append(i.ToString(CultureInfo.InvariantCulture)).Append(" and "); prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), filter.value.ToString().Replace("[", "( ").Replace("]", " )").Replace("\"", "'"))); i++; } #endregion return(result.ToString().Substring(0, result.Length - " and".Length)); }
public IList <DbView> GetDbViews(RdbDescriptor db) { return(_acDomain.RetrieveRequiredService <IRdbMetaDataService>().GetDbViews(db)); }
/// <summary> /// 将给定的数据查询筛选器列表转化为原生sql查询条件字符串返回 /// </summary> /// <param name="rdb"></param> /// <param name="filters">数据查询筛选器</param> /// <param name="alias">别名,空字符串或null表示无别名</param> /// <param name="prams"></param> /// <returns></returns> public string FilterString(RdbDescriptor rdb, List<FilterData> filters, string alias, out List<DbParameter> prams) { bool useAlias = !string.IsNullOrEmpty(alias); prams = new List<DbParameter>(); if (filters == null || filters.Count == 0) return string.Empty; var result = new StringBuilder(); //type=string #region string var stringList = from f in filters where f.type == "string" select f; int i = 0; foreach (var filter in stringList) { var comparision = GetComparison(filter.comparison); result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field); if (comparision == "like") { result.Append(" ").Append("like").Append(" @").Append(filter.field).Append(i).Append(""); string value = string.Empty; if (filter.value != null) { value = filter.value.ToString(); } if (!value.StartsWith("%") && !value.EndsWith("%")) { value = "%" + value + "%"; } prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), value)); } else { result.Append(comparision).Append("@").Append(filter.field).Append(i); prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), filter.value)); } result.Append(" and "); i++; } #endregion //type=guid #region guid var guidList = from f in filters where f.type == "guid" select f; i = 0; foreach (var filter in guidList) { var comparision = GetComparison(filter.comparison); result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field); result.Append(comparision).Append("@").Append(filter.field).Append(i); if (filter.value.GetType() != typeof(Guid)) { throw new ValidationException("'" + filter.value + "'不是Guid类型的"); } prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), filter.value)); result.Append(" and "); i++; } #endregion //type=boolean #region boolean i = 0; var booleanList = from f in filters where f.type == "boolean" select f; foreach (var filter in booleanList) { var v = filter.value.ToString(); bool b; if (!bool.TryParse(v, out b)) { throw new ValidationException("'" + filter.value + "'不是boolean类型的"); } result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field).Append("=@").Append(filter.field).Append(i.ToString(CultureInfo.InvariantCulture)).Append(" and "); var p = CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), v); prams.Add(p); i++; } #endregion //type=null #region null i = 0; var nullList = from f in filters where f.type == "null" select f; foreach (var filter in nullList) { result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field).Append(GetComparison(filter.comparison)).Append(" and "); i++; } #endregion //type=numeric #region numeric i = 0; var numericList = from f in filters where f.type == "numeric" group f by f.field into g select g; foreach (var g in numericList) { result.Append("( "); string iiStr = string.Empty; int j = 0; foreach (var filter in g) { iiStr += alias + "." + filter.field + GetComparison(filter.comparison) + "@" + filter.field + i.ToString(CultureInfo.InvariantCulture) + j.ToString(CultureInfo.InvariantCulture) + " and "; if (filter.value.GetType() != typeof(int)) { throw new ValidationException("'" + filter.value + "'不是int类型的"); } prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture) + j.ToString(CultureInfo.InvariantCulture), filter.value)); j++; } result.Append(iiStr.Substring(0, iiStr.Length - " and".Length)); result.Append(" )"); result.Append(" and "); i++; } #endregion //type=date #region date i = 0; var dateList = from f in filters where f.type == "date" group f by f.field into g select g; foreach (var g in dateList) { int j = 0; result.Append("( "); string iiStr = string.Empty; foreach (var filter in g) { iiStr += alias + "." + filter.field + GetComparison(filter.comparison) + " @" + filter.field + i.ToString(CultureInfo.InvariantCulture) + j.ToString(CultureInfo.InvariantCulture) + ")" + " and "; prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture) + j.ToString(CultureInfo.InvariantCulture), filter.value)); j++; } result.Append(iiStr.Substring(0, iiStr.Length - " and".Length)); result.Append(" )"); result.Append(" and "); i++; } #endregion //type=list :["1","2"] #region list i = 0; var listList = from f in filters where f.type == "list" select f; foreach (var filter in listList) { result.Append(alias, useAlias).Append(".", useAlias).Append(filter.field).Append(" in @").Append(filter.field).Append(i.ToString(CultureInfo.InvariantCulture)).Append(" and "); prams.Add(CreateParameter(rdb, filter.field + i.ToString(CultureInfo.InvariantCulture), filter.value.ToString().Replace("[", "( ").Replace("]", " )").Replace("\"", "'"))); i++; } #endregion return result.ToString().Substring(0, result.Length - " and".Length); }
public IList<DbViewColumn> GetViewColumns(RdbDescriptor db) { return _acDomain.RetrieveRequiredService<IRdbMetaDataService>().GetViewColumns(db); }
public IList<DbTable> GetDbTables(RdbDescriptor db) { return _acDomain.RetrieveRequiredService<IRdbMetaDataService>().GetDbTables(db); }
/// <summary> /// 根据目录获取给定节点和本体的数据,如果传入的目录为空表示获取本节点的数据 /// <remarks>本节点通常是中心节点</remarks> /// </summary> /// <param name="ontology"></param> /// <param name="db">模型</param> /// <param name="filters"></param> /// <param name="selectElements">sql select语句的选择列集合</param> /// <param name="pagingData"></param> /// <returns> /// 数据记录列表,数据记录表现为字典形式,键是数据元素编码值是相应数据元素对应的数据项值 /// </returns> private DataTuple GetPlistInfoItems( OntologyDescriptor ontology, RdbDescriptor db, OrderedElementSet selectElements, List<FilterData> filters, PagingInput pagingData) { if (string.IsNullOrEmpty(pagingData.SortField)) { pagingData.SortField = "IncrementId"; } if (string.IsNullOrEmpty(pagingData.SortOrder)) { pagingData.SortOrder = "asc"; } var elements = ontology.Elements; if (filters != null) { for (int i = 0; i < filters.Count; i++) { var filter = filters[i]; if (elements.ContainsKey(filter.field)) { // TODO:根据数据属性优化查询,比如对于身份证件号来说如果输入的值长度 // 为20或18的话可以将like替换为等于 filter.type = "string"; var element = elements[filter.field]; if (element.Element.IsEnabled != 1) { continue; } if (element.Element.InfoDicId.HasValue) { filter.comparison = "eq"; } else { filter.comparison = "like"; } } else { filters.RemoveAt(i); } } } var tableName = ontology.Ontology.EntityTableName; var sbSqlPredicate = new StringBuilder(); var l = sbSqlPredicate.Length; var pQueryList = new List<DbParameter>(); List<DbParameter> pFilters; var filterString = _filterStringBuilder.FilterString(db, filters, null, out pFilters); if (!string.IsNullOrEmpty(filterString)) { foreach (var pFilter in pFilters) { object obj = pFilter.Value; if (obj == null) { obj = DBNull.Value; } var p = db.CreateParameter(); p.ParameterName = pFilter.ParameterName; p.Value = obj; pQueryList.Add(p); } if (sbSqlPredicate.Length != l) { sbSqlPredicate.Append(" and "); } sbSqlPredicate.Append(filterString); } string sqlPredicateString = string.Empty; if (sbSqlPredicate.Length > 0) { sqlPredicateString = sbSqlPredicate.ToString(); } var sqlText = new StringBuilder(); OrderedElementSet elementList; if (selectElements == null || selectElements.Count == 0) { elementList = new OrderedElementSet { ontology.Elements["id"] }; } else { elementList = selectElements; } sqlText.Append("SELECT TOP {0} "); int len = sqlText.Length; foreach (var element in elementList) { if (sqlText.Length != len) { sqlText.Append(","); } sqlText.Append("[").Append(element.Element.FieldCode).Append("]"); } sqlText.Append(" FROM (SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber,"); len = sqlText.Length; foreach (var element in elementList) { if (sqlText.Length != len) { sqlText.Append(","); } sqlText.Append("[").Append(element.Element.FieldCode).Append("]"); } sqlText.Append(" FROM {3} where "); if (ontology.Ontology.IsLogicalDeletionEntity) { sqlText.Append("DeletionStateCode = 0"); } else { sqlText.Append("1 = 1"); } if (!string.IsNullOrEmpty(sqlPredicateString)) { sqlText.Append(" and ").Append(sqlPredicateString); } sqlText.Append(") a WHERE a.RowNumber > {4}"); string sqlQuery = string.Format( sqlText.ToString(), pagingData.PageSize.ToString(CultureInfo.InvariantCulture), pagingData.SortField, pagingData.SortOrder, tableName, (pagingData.SkipCount).ToString(CultureInfo.InvariantCulture)); pagingData.Count(() => { string where = ontology.Ontology.IsLogicalDeletionEntity ? "where DeletionStateCode = 0" : ""; string sqlCount = string.Format("select count(1) from {0} {1}", tableName, where); if (!string.IsNullOrEmpty(sqlPredicateString)) { sqlCount = sqlCount + " and " + sqlPredicateString; } return (int)db.ExecuteScalar( sqlCount, pQueryList.Select(p => ((ICloneable)p).Clone()).Cast<DbParameter>().ToArray()); }); var list = new List<object[]>(); var reader = db.ExecuteReader(sqlQuery, pQueryList.ToArray()); while (reader.Read()) { var values = new object[elementList.Count]; for (int i = 0; i < elementList.Count; i++) { values[i] = reader.GetValue(i); } list.Add(values); } reader.Close(); return new DataTuple(elementList, list.ToArray()); }
protected RdbContext(RdbDescriptor rdb, DbCompiledModel model) : base(model) { _rdb = rdb; }
public IList <OperationLog> GetPlistOperationLogs(Guid?targetId, DateTime?leftCreateOn, DateTime?rightCreateOn , List <FilterData> filters, PagingInput paging) { paging.Valid(); var filterStringBuilder = _acDomain.RetrieveRequiredService <ISqlFilterStringBuilder>(); RdbDescriptor db = GetOperationLogDb(); List <DbParameter> prams; var filterString = filterStringBuilder.FilterString(db, filters, "t", out prams); if (!string.IsNullOrEmpty(filterString)) { filterString = " where " + filterString + "{0}"; } else { filterString = " where 1=1 {0}"; } if (targetId.HasValue) { filterString = string.Format(filterString, " and t.TargetID=@TargetId {0}"); } if (leftCreateOn.HasValue) { filterString = string.Format(filterString, " and t.CreateOn >= @leftCreateOn"); } if (rightCreateOn.HasValue) { filterString = string.Format(filterString, " and t.CreateOn < @rightCreateOn"); } else { filterString = string.Format(filterString, string.Empty); } var sql = @"select top({0}) * from (SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber,* FROM {3} as t" + filterString + ") a WHERE a.RowNumber > {4}"; var countSql = "select count(*) from OperationLog as t" + filterString; var operationLogs = new List <OperationLog>(); if (prams == null) { prams = new List <DbParameter>(); } if (targetId.HasValue) { prams.Add(CreateParameter(db, "TargetId", targetId.Value, DbType.Guid)); } if (leftCreateOn.HasValue) { prams.Add(CreateParameter(db, "leftCreateOn", leftCreateOn.Value, DbType.DateTime)); } if (rightCreateOn.HasValue) { prams.Add(CreateParameter(db, "rightCreateOn", rightCreateOn.Value, DbType.DateTime)); } var reader = db.ExecuteReader( string.Format(sql, paging.PageSize, paging.SortField, paging.SortOrder, "OperationLog", paging.PageSize * paging.PageIndex), prams.ToArray()); while (reader.Read()) { operationLogs.Add(OperationLog.Create(reader)); } paging.Total = (int)db.ExecuteScalar(countSql, prams.Select(p => ((ICloneable)p).Clone()).Cast <DbParameter>().ToArray()); reader.Close(); return(operationLogs); }
public RdbContext(RdbDescriptor rdb, DbConnection existingConnection, bool contextOwnsConnection) : base(existingConnection, contextOwnsConnection) { _rdb = rdb; }
/// <summary> /// 根据目录获取给定节点和本体的数据,如果传入的目录为空表示获取本节点的数据 /// <remarks>本节点通常是中心节点</remarks> /// </summary> /// <param name="ontology"></param> /// <param name="db">模型</param> /// <param name="filters"></param> /// <param name="selectElements">sql select语句的选择列集合</param> /// <param name="pagingData"></param> /// <returns> /// 数据记录列表,数据记录表现为字典形式,键是数据元素编码值是相应数据元素对应的数据项值 /// </returns> private DataTuple GetPlistInfoItems( OntologyDescriptor ontology, RdbDescriptor db, OrderedElementSet selectElements, List <FilterData> filters, PagingInput pagingData) { if (string.IsNullOrEmpty(pagingData.SortField)) { pagingData.SortField = "IncrementId"; } if (string.IsNullOrEmpty(pagingData.SortOrder)) { pagingData.SortOrder = "asc"; } var elements = ontology.Elements; if (filters != null) { for (int i = 0; i < filters.Count; i++) { var filter = filters[i]; if (elements.ContainsKey(filter.field)) { // TODO:根据数据属性优化查询,比如对于身份证件号来说如果输入的值长度 // 为20或18的话可以将like替换为等于 filter.type = "string"; var element = elements[filter.field]; if (element.Element.IsEnabled != 1) { continue; } if (element.Element.InfoDicId.HasValue) { filter.comparison = "eq"; } else { filter.comparison = "like"; } } else { filters.RemoveAt(i); } } } var tableName = ontology.Ontology.EntityTableName; var sbSqlPredicate = new StringBuilder(); var l = sbSqlPredicate.Length; var pQueryList = new List <DbParameter>(); List <DbParameter> pFilters; var filterString = _filterStringBuilder.FilterString(db, filters, null, out pFilters); if (!string.IsNullOrEmpty(filterString)) { foreach (var pFilter in pFilters) { object obj = pFilter.Value; if (obj == null) { obj = DBNull.Value; } var p = db.CreateParameter(); p.ParameterName = pFilter.ParameterName; p.Value = obj; pQueryList.Add(p); } if (sbSqlPredicate.Length != l) { sbSqlPredicate.Append(" and "); } sbSqlPredicate.Append(filterString); } string sqlPredicateString = string.Empty; if (sbSqlPredicate.Length > 0) { sqlPredicateString = sbSqlPredicate.ToString(); } var sqlText = new StringBuilder(); OrderedElementSet elementList; if (selectElements == null || selectElements.Count == 0) { elementList = new OrderedElementSet { ontology.Elements["id"] }; } else { elementList = selectElements; } sqlText.Append("SELECT TOP {0} "); int len = sqlText.Length; foreach (var element in elementList) { if (sqlText.Length != len) { sqlText.Append(","); } sqlText.Append("[").Append(element.Element.FieldCode).Append("]"); } sqlText.Append(" FROM (SELECT ROW_NUMBER() OVER(ORDER BY {1} {2}) AS RowNumber,"); len = sqlText.Length; foreach (var element in elementList) { if (sqlText.Length != len) { sqlText.Append(","); } sqlText.Append("[").Append(element.Element.FieldCode).Append("]"); } sqlText.Append(" FROM {3} where "); if (ontology.Ontology.IsLogicalDeletionEntity) { sqlText.Append("DeletionStateCode = 0"); } else { sqlText.Append("1 = 1"); } if (!string.IsNullOrEmpty(sqlPredicateString)) { sqlText.Append(" and ").Append(sqlPredicateString); } sqlText.Append(") a WHERE a.RowNumber > {4}"); string sqlQuery = string.Format( sqlText.ToString(), pagingData.PageSize.ToString(CultureInfo.InvariantCulture), pagingData.SortField, pagingData.SortOrder, tableName, (pagingData.SkipCount).ToString(CultureInfo.InvariantCulture)); pagingData.Count(() => { string where = ontology.Ontology.IsLogicalDeletionEntity ? "where DeletionStateCode = 0" : ""; string sqlCount = string.Format("select count(1) from {0} {1}", tableName, where); if (!string.IsNullOrEmpty(sqlPredicateString)) { sqlCount = sqlCount + " and " + sqlPredicateString; } return((int)db.ExecuteScalar( sqlCount, pQueryList.Select(p => ((ICloneable)p).Clone()).Cast <DbParameter>().ToArray())); }); var list = new List <object[]>(); var reader = db.ExecuteReader(sqlQuery, pQueryList.ToArray()); while (reader.Read()) { var values = new object[elementList.Count]; for (int i = 0; i < elementList.Count; i++) { values[i] = reader.GetValue(i); } list.Add(values); } reader.Close(); return(new DataTuple(elementList, list.ToArray())); }
private static DbParameter CreateParameter(RdbDescriptor db, string parameterName, object value, DbType dbType) { var p = db.CreateParameter(); p.ParameterName = parameterName; p.Value = value; p.DbType = dbType; return p; }
public IList <DbTableColumn> GetTableColumns(RdbDescriptor db) { return(_acDomain.RetrieveRequiredService <IRdbMetaDataService>().GetTableColumns(db)); }
public RdbContext(RdbDescriptor rdb, string nameOrConnectionString) : base(nameOrConnectionString) { _rdb = rdb; }
/// <summary> /// 根据给定的本体码和信息标识获取本节点两条数据 /// </summary> /// <param name="ontology">本体</param> /// <param name="db"></param> /// <param name="infoIds">多列联合信息标识字典,键必须不区分大小写</param> /// <param name="selectElements">选择元素</param> /// <returns></returns> private TowInfoTuple GetTop2InfoItemSet(OntologyDescriptor ontology, RdbDescriptor db, IEnumerable <InfoItem> infoIds, OrderedElementSet selectElements) { if (infoIds == null || !infoIds.Any()) { return(new TowInfoTuple(null, null)); } var sb = new StringBuilder(); var sqlParameters = new List <DbParameter>(); var elementList = selectElements.ToList(); sb.Append("select top 2 "); int l = sb.Length; foreach (var element in elementList) { if (sb.Length != l) { sb.Append(","); } sb.Append("t.[").Append(element.Element.FieldCode).Append("]"); } sb.Append(" from [").Append(ontology.Ontology.EntityTableName).Append("] as t where"); if (ontology.Ontology.IsLogicalDeletionEntity) { sb.Append(" t.DeletionStateCode=0 "); } else { sb.Append(" 1=1 "); } foreach (var element in infoIds) { sb.Append(" and t.[").Append(element.Element.Element.FieldCode) .Append("]=").Append("@").Append(element.Element.Element.FieldCode); object obj = element.Value; if (obj == null) { obj = DBNull.Value; } sqlParameters.Add(CreateParameter(db, element.Element.Element.FieldCode, obj, DbType.String)); } var infoValue1 = new List <InfoItem>(); var infoValue2 = new List <InfoItem>(); using (var reader = db.ExecuteReader(sb.ToString(), sqlParameters.ToArray())) { if (reader.Read()) { for (int i = 0; i < elementList.Count; i++) { infoValue1.Add(InfoItem.Create(elementList[i], reader.GetValue(i).ToString())); } } if (reader.Read()) { for (int i = 0; i < elementList.Count; i++) { infoValue2.Add(InfoItem.Create(elementList[i], reader.GetValue(i).ToString())); } } reader.Close(); } return(new TowInfoTuple(infoValue1.ToArray(), infoValue2.ToArray())); }
protected RdbContext(RdbDescriptor rdb) : base() { _rdb = rdb; }