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