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