private static DataBaseVersionInfo DataBaseVersionFromDataReader(NullableDataReader rdr) { DataBaseVersionInfo info = new DataBaseVersionInfo(); info.VersionId = rdr.GetInt32("VersionID"); info.Major = rdr.GetInt32("Major"); info.Minor = rdr.GetInt32("Minor"); info.Build = rdr.GetInt32("Build"); info.Revision = rdr.GetInt32("Revision"); info.CreatedDate = rdr.GetDateTime("CreatedDate"); return(info); }
public static string CurrentVersion() { string str = "0.0.0.0"; string key = "CK_System_DataBaseCurrentVersion"; if (SiteCache.Get(key) == null) { DataBaseVersionInfo info = LastVersion(); str = info.Major.ToString(CultureInfo.CurrentCulture) + "." + info.Minor.ToString(CultureInfo.CurrentCulture) + "." + info.Build.ToString(CultureInfo.CurrentCulture) + "." + info.Revision.ToString(CultureInfo.CurrentCulture); SiteCache.Insert(key, str, 0x4380); return(str); } return(SiteCache.Get(key).ToString()); }
public DataBaseVersionInfo LastVersion() { DataBaseVersionInfo info = new DataBaseVersionInfo(true); try { using (NullableDataReader reader = DBHelper.ExecuteReaderSql("SELECT TOP 1 * FROM PE_Version ORDER BY VersionID DESC")) { if (reader.Read()) { info = DataBaseVersionFromDataReader(reader); } return(info); } } catch { info.Major = 0x63; info.Minor = 0x63; info.Build = 0x63; info.Revision = 0x63; } return(info); }
/// <summary> /// 转换原始查询SQL语句为分页查询SQL语句 /// </summary> /// <param name="sqlStr">原始查询SQL语句</param> /// <param name="orderStr">排序字符串</param> /// <param name="pageIndex">目标页索引</param> /// <param name="pageSize">页大小</param> /// <param name="dataBaseVersion">数据库版本号信息</param> /// <param name="pagingAssistFieldName">分页字段名称</param> /// <returns>分页查询SQL语句</returns> protected override string ConvertSqlToPagingQuerySql(string sqlStr, string orderStr, long pageIndex, long pageSize, DataBaseVersionInfo dataBaseVersion, out string pagingAssistFieldName) { //dataBaseVersion.Version:2008 var startIndex = (pageIndex - 1) * pageSize; string pagingSql; if (string.IsNullOrWhiteSpace(orderStr)) { if (dataBaseVersion != null && dataBaseVersion.Version >= 2012) { //SQLServer2012+新特性,在高并发时性能强于ROW_NUMBER方式 pagingSql = $"{sqlStr} OFFSET {startIndex} ROW FETCH NEXT {pageSize} rows only"; pagingAssistFieldName = null; } else { pagingAssistFieldName = base.GetPagingAssistColName(sqlStr); string lowerSqlStr = sqlStr.ToLower(); string selectStr = "SELECT"; string ronumSqlStr = sqlStr.Insert(lowerSqlStr.IndexOf(selectStr) + selectStr.Length + 1, $" ROW_NUMBER() OVER (ORDER BY (SELECT 0)) AS {pagingAssistFieldName},"); StringBuilder sb = new StringBuilder(); sb.Append("SELECT * FROM ("); sb.Append(ronumSqlStr); sb.Append(string.Format(") AS WQS_T WHERE {0} BETWEEN ", pagingAssistFieldName)); sb.Append(startIndex); sb.Append(" AND "); sb.Append(startIndex + pageSize); pagingSql = sb.ToString(); } } else { if (dataBaseVersion != null && dataBaseVersion.Version >= 2012) { /* * declare @pageIndex int * declare @pageSize int * set @pageIndex = 1 * set @pageSize = 10 * select * from person order by ID asc OFFSET (@pageSize * (@pageIndex-1)) ROW FETCH NEXT @pageSize rows only; */ //SQLServer2012+新特性,在高并发时性能强于ROW_NUMBER方式 pagingSql = $"{sqlStr} ORDER BY {orderStr} OFFSET {startIndex} ROW FETCH NEXT {pageSize} rows only"; pagingAssistFieldName = null; } else { pagingAssistFieldName = base.GetPagingAssistColName(sqlStr); string lowerSqlStr = sqlStr.ToLower(); string selectStr = "SELECT"; string ronumSqlStr = sqlStr.Insert(lowerSqlStr.IndexOf(selectStr) + selectStr.Length + 1, $" ROW_NUMBER() OVER (ORDER BY {orderStr}) AS {pagingAssistFieldName},"); StringBuilder sb = new StringBuilder(); sb.Append("SELECT * FROM ("); sb.Append(ronumSqlStr); sb.Append(string.Format(") AS WQS_T WHERE {0} BETWEEN ", pagingAssistFieldName)); sb.Append(startIndex); sb.Append(" AND "); sb.Append(startIndex + pageSize); pagingSql = sb.ToString(); } } return(pagingSql); }
/// <summary> /// 转换原始查询SQL语句为分页查询SQL语句 /// </summary> /// <param name="sqlStr">原始查询SQL语句</param> /// <param name="orderStr">排序字符串</param> /// <param name="pageIndex">目标页索引</param> /// <param name="pageSize">页大小</param> /// <param name="dataBaseVersion">数据库版本号信息</param> /// <param name="pagingAssistFieldName">分页字段名称</param> /// <returns>分页查询SQL语句</returns> protected override string ConvertSqlToPagingQuerySql(string sqlStr, string orderStr, long pageIndex, long pageSize, DataBaseVersionInfo dataBaseVersion, out string pagingAssistFieldName) { //eg:SELECT * from person WHERE ID < 100 ORDER by ID DESC limit 10 offset 0 pagingAssistFieldName = null; var startIndex = (pageIndex - 1) * pageSize; string dstSqlStr = null; if (string.IsNullOrWhiteSpace(orderStr)) { dstSqlStr = $@"{sqlStr} limit {pageSize} offset {startIndex}"; } else { dstSqlStr = $@"{sqlStr} ORDER BY {orderStr} limit {pageSize} offset {startIndex}"; } return(dstSqlStr); }
/// <summary> /// 转换原始查询SQL语句为分页查询SQL语句 /// </summary> /// <param name="sqlStr">原始查询SQL语句</param> /// <param name="orderStr">排序字符串</param> /// <param name="pageIndex">目标页索引</param> /// <param name="pageSize">页大小</param> /// <param name="dataBaseVersion">数据库版本号信息</param> /// <param name="pagingAssistFieldName">分页字段名称</param> /// <returns>分页查询SQL语句</returns> protected override string ConvertSqlToPagingQuerySql(string sqlStr, string orderStr, long pageIndex, long pageSize, DataBaseVersionInfo dataBaseVersion, out string pagingAssistFieldName) { //dataBaseVersion:3.8.2 //eg:SELECT * from person WHERE ID < 100 ORDER by ID DESC limit 0,10 pagingAssistFieldName = null; var startIndex = (pageIndex - 1) * pageSize; string dstSqlStr = null; if (string.IsNullOrWhiteSpace(orderStr)) { dstSqlStr = string.Format("{0} limit {1},{2}", sqlStr, startIndex, pageSize); } else { dstSqlStr = string.Format("{0} ORDER BY {1} limit {2},{3}", sqlStr, orderStr, startIndex, pageSize); } return(dstSqlStr); }
/// <summary> /// 转换原始查询SQL语句为分页查询SQL语句 /// </summary> /// <param name="sqlStr">原始查询SQL语句</param> /// <param name="orderStr">排序字符串</param> /// <param name="pageIndex">目标页索引</param> /// <param name="pageSize">页大小</param> /// <param name="dataBaseVersion">数据库版本号信息</param> /// <param name="pagingAssistFieldName">分页字段名称</param> /// <returns>分页查询SQL语句</returns> protected abstract string ConvertSqlToPagingQuerySql(string sqlStr, string orderStr, long pageIndex, long pageSize, DataBaseVersionInfo dataBaseVersion, out string pagingAssistFieldName);
/// <summary> /// 转换原始查询SQL语句为分页查询SQL语句 /// </summary> /// <param name="sqlStr">原始查询SQL语句</param> /// <param name="orderInfos">排序列名集合[null为或空不排序]</param> /// <param name="pageIndex">目标页索引</param> /// <param name="pageSize">页大小</param> /// <param name="dataBaseVersion">数据库版本号信息</param> /// <param name="pagingAssistFieldName">分页字段名称</param> /// <returns>分页查询SQL语句</returns> public string ConvertSqlToPagingQuerySql(string sqlStr, IEnumerable <DBOrderInfo> orderInfos, long pageIndex, long pageSize, DataBaseVersionInfo dataBaseVersion, out string pagingAssistFieldName) { string orderStr = this.CreateOrderStr(orderInfos); return(this.ConvertSqlToPagingQuerySql(sqlStr, orderStr, pageIndex, pageSize, dataBaseVersion, out pagingAssistFieldName)); }
/// <summary> /// 转换原始查询SQL语句为分页查询SQL语句 /// </summary> /// <param name="sqlStr">原始查询SQL语句</param> /// <param name="orderInfos">排序列名集合[null为或空不排序]</param> /// <param name="pageIndex">目标页索引</param> /// <param name="pageSize">页大小</param> /// <param name="dataBaseVersion">数据库版本信息</param> /// <param name="pagingAssistFieldName">分页字段名称</param> /// <returns>分页查询SQL语句</returns> protected string PrimitiveConvertSqlToPagingQuerySql(string sqlStr, IEnumerable <DBOrderInfo> orderInfos, long pageIndex, long pageSize, DataBaseVersionInfo dataBaseVersion, out string pagingAssistFieldName) { return(this._dbInteraction.ConvertSqlToPagingQuerySql(sqlStr, orderInfos, pageIndex, pageSize, dataBaseVersion, out pagingAssistFieldName)); }
/// <summary> /// 转换原始查询SQL语句为分页查询SQL语句 /// </summary> /// <param name="sqlStr">原始查询SQL语句</param> /// <param name="orderStr">排序字符串</param> /// <param name="pageIndex">目标页索引</param> /// <param name="pageSize">页大小</param> /// <param name="dataBaseVersion">数据库版本号信息</param> /// <param name="pagingAssistFieldName">分页字段名称</param> /// <returns>分页查询SQL语句</returns> protected override string ConvertSqlToPagingQuerySql(string sqlStr, string orderStr, long pageIndex, long pageSize, DataBaseVersionInfo dataBaseVersion, out string pagingAssistFieldName) { //dataBaseVersion.Version:11.2.0.1.0 //eg1效率最高,但是不好拼接,所以采用低一点的eg2 //eg1:SELECT* FROM(SELECT ROWNUM AS RN, t.* FROM TASK_TARGET_POS_RESULT t WHERE TAR_CAP_TIME> 1554604680 AND TAR_CAP_TIME<1555209480 AND ROWNUM<110) WHERE RN>= 100 //eg2:SELECT* FROM(SELECT UDTWQS_T.*,ROWNUM AS WQS_RNCol FROM(SELECT* FROM TASK_TARGET_POS_RESULT) UDTWQS_T WHERE ROWNUM < 110) WHERE WQS_RNCol>= 100 pagingAssistFieldName = base.GetPagingAssistColName(sqlStr); var startIndex = (pageIndex - 1) * pageSize; var endIndex = startIndex + pageSize; string dstSqlStr = null; if (string.IsNullOrWhiteSpace(orderStr)) { dstSqlStr = $"SELECT* FROM(SELECT UDTWQS_T.*,ROWNUM AS {pagingAssistFieldName} FROM({sqlStr}) UDTWQS_T WHERE ROWNUM < {endIndex}) WHERE {pagingAssistFieldName}>= {startIndex}"; } else { dstSqlStr = $"SELECT* FROM(SELECT UDTWQS_T.*,ROWNUM AS {pagingAssistFieldName} FROM({sqlStr}) UDTWQS_T WHERE ROWNUM < {endIndex} ORDER BY {orderStr}) WHERE {pagingAssistFieldName}>= {startIndex}"; } return(dstSqlStr); }
/// <summary> /// 查询分页数据 /// </summary> /// <param name="sqlStr">查询SQL语句</param> /// <param name="orderByColName">排序列名</param> /// <param name="pageSize">页大小</param> /// <param name="pageIndex">查询页索引</param> /// <param name="orderFlag">排序类型[true:升序;false:降序]</param> /// <param name="parameterNameValueDic">参数名名称及对应的值字典集合[key:参数名称,含参数符号;value:参数值]</param> /// <param name="dataBaseVersion">数据库版本信息</param> /// <returns>数据表</returns> public DataTable QueryPagingData(string sqlStr, string orderByColName, long pageSize, long pageIndex, bool orderFlag, Dictionary <string, object> parameterNameValueDic = null, DataBaseVersionInfo dataBaseVersion = null) { List <DBOrderInfo> orderInfos = null; if (!string.IsNullOrWhiteSpace(orderByColName)) { orderInfos = new List <DBOrderInfo>(); orderInfos.Add(new DBOrderInfo(orderByColName, orderFlag)); } using (var conInfo = new DbConnectionInfo(this._dbid, DBVisitType.R)) { return(this.PrimitiveQueryPagingData(conInfo.DbConnection, sqlStr, orderInfos, pageSize, pageIndex, parameterNameValueDic, dataBaseVersion)); } }
/// <summary> /// 转换原始查询SQL语句为分页查询SQL语句 /// </summary> /// <param name="sqlStr">原始查询SQL语句</param> /// <param name="orderInfos">排序列名集合[null为或空不排序]</param> /// <param name="pageIndex">目标页索引</param> /// <param name="pageSize">页大小</param> /// <param name="pagingAssistFieldName">分页字段名称</param> /// <param name="dataBaseVersion">数据库版本信息</param> /// <returns>分页查询SQL语句</returns> public string ConvertSqlToPagingQuerySql(string sqlStr, IEnumerable <DBOrderInfo> orderInfos, long pageIndex, long pageSize, out string pagingAssistFieldName, DataBaseVersionInfo dataBaseVersion = null) { return(this.PrimitiveConvertSqlToPagingQuerySql(sqlStr, orderInfos, pageIndex, pageSize, dataBaseVersion, out pagingAssistFieldName)); }
/// <summary> /// 查询分页数据 /// </summary> /// <param name="con">娄所谓中连接对象</param> /// <param name="sqlStr">查询SQL语句</param> /// <param name="orderInfos">排序列名集合[null为或空不排序]</param> /// <param name="pageSize">页大小</param> /// <param name="pageIndex">查询页索引</param> /// <param name="parameterNameValueDic">参数名名称及对应的值字典集合[key:参数名称,含参数符号;value:参数值]</param> /// <param name="dataBaseVersion">数据库版本信息</param> /// <returns>数据表</returns> private DataTable PrimitiveQueryPagingData(IDbConnection con, string sqlStr, IEnumerable <DBOrderInfo> orderInfos, long pageSize, long pageIndex, Dictionary <string, object> parameterNameValueDic = null, DataBaseVersionInfo dataBaseVersion = null) { if (string.IsNullOrWhiteSpace(sqlStr)) { throw new ArgumentNullException("查询语句不能为空或null", "sqlStr"); } sqlStr = sqlStr.Trim(); if (pageIndex < 1) { throw new ArgumentOutOfRangeException(string.Format("查询页索引值不能小于1,值{0}无效", pageIndex), "pageIndex"); } if (pageSize < 1) { throw new ArgumentOutOfRangeException(string.Format("查询页大小不能小于1,值{0}无效", pageSize), "pageSize"); } string pagingAssistFieldName; string pagingQuerySql = this.PrimitiveConvertSqlToPagingQuerySql(sqlStr, orderInfos, pageIndex, pageSize, dataBaseVersion, out pagingAssistFieldName); DataTable dt = this.PrimitiveQueryDataToDataTable(con, pagingQuerySql, parameterNameValueDic); if (!string.IsNullOrWhiteSpace(pagingAssistFieldName)) { dt.Columns.Remove(pagingAssistFieldName); } return(dt); }
/// <summary> /// 查询分页数据 /// </summary> /// <param name="con">娄所谓中连接对象</param> /// <param name="sqlStr">查询SQL语句</param> /// <param name="orderInfos">排序列名集合[null为或空不排序]</param> /// <param name="pageSize">页大小</param> /// <param name="pageIndex">查询页索引</param> /// <param name="parameterNameValueDic">参数名名称及对应的值字典集合[key:参数名称,含参数符号;value:参数值]</param> /// <param name="dataBaseVersion">数据库版本信息</param> /// <returns>数据表</returns> public DataTable QueryPagingData(IDbConnection con, string sqlStr, IEnumerable <DBOrderInfo> orderInfos, long pageSize, long pageIndex, Dictionary <string, object> parameterNameValueDic = null, DataBaseVersionInfo dataBaseVersion = null) { return(this.PrimitiveQueryPagingData(con, sqlStr, orderInfos, pageSize, pageIndex, parameterNameValueDic, dataBaseVersion)); }
/// <summary> /// 查询分页数据 /// </summary> /// <param name="sqlStr">查询SQL语句</param> /// <param name="orderInfos">排序列名集合[null为或空不排序]</param> /// <param name="pageSize">页大小</param> /// <param name="pageIndex">查询页索引</param> /// <param name="parameterNameValueDic">参数名名称及对应的值字典集合[key:参数名称,含参数符号;value:参数值]</param> /// <param name="dataBaseVersion">数据库版本信息</param> /// <returns>数据表</returns> public DataTable QueryPagingData(string sqlStr, IEnumerable <DBOrderInfo> orderInfos, long pageSize, long pageIndex, Dictionary <string, object> parameterNameValueDic = null, DataBaseVersionInfo dataBaseVersion = null) { using (var conInfo = new DbConnectionInfo(this._dbid, DBVisitType.R)) { return(this.PrimitiveQueryPagingData(conInfo.DbConnection, sqlStr, orderInfos, pageSize, pageIndex, parameterNameValueDic, dataBaseVersion)); } }