/// <summary>
/// 创造一个数据库工具实例。
/// </summary>
/// <param name="databaseVersion">指定数据库版本。</param>
/// <returns>数据库工具实例。</returns>
public static DatabaseUtil GetInstance(Enum_DatabaseVersion databaseVersion)
{
if (!EntitySet.ContainsKey(databaseVersion))
{
EntitySet[databaseVersion] = new DatabaseUtil()
{
DatabaseVersion = databaseVersion
};
}
return(EntitySet[databaseVersion]);
}
/// <summary>
/// 对Sql语句进行分页处理。
/// </summary>
/// <param name="viewSql">待处理的Sql语句。</param>
/// <param name="pageIndex">分页页码。</param>
/// <param name="dataCount">每页显示数据数量。</param>
/// <param name="sortBy">排序规则。</param>
/// <param name="databaseVersion">数据库版本。</param>
/// <returns>处理后的分页语句。</returns>
public static string PagingView(string viewSql, int pageIndex, int dataCount, string sortBy, Enum_DatabaseVersion databaseVersion)
{
return(PagingView(viewSql, pageIndex, dataCount, sortBy, string.Empty, string.Empty, databaseVersion));
}
/// <summary>
/// 对Sql语句进行分页处理。
/// </summary>
/// <param name="viewSql">待处理的Sql语句。</param>
/// <param name="pageIndex">分页页码。</param>
/// <param name="dataCount">每页显示数据数量。</param>
/// <param name="sortBy">排序规则。</param>
/// <param name="viewName">生成子视图名称。</param>
/// <param name="rowIndexName">生成行标记序列名称。</param>
/// <param name="databaseVersion">数据库版本。</param>
/// <returns>处理后的分页语句。</returns>
public static string PagingView(string viewSql, int pageIndex, int dataCount, string sortBy, string viewName, string rowIndexName, Enum_DatabaseVersion databaseVersion)
{
if (string.IsNullOrEmpty(viewSql))
{
return(viewSql);
}
if (pageIndex < 1)
{
pageIndex = 1;
}
if (dataCount < 1)
{
dataCount = 1;
}
if (string.IsNullOrEmpty(viewName))
{
viewName = string.Format("View_{0}", Guid.NewGuid().ToString().Replace("-", string.Empty));
}
if (string.IsNullOrEmpty(rowIndexName))
{
rowIndexName = string.Format("RowIndex_{0}", Guid.NewGuid().ToString().Replace("-", string.Empty));
}
switch (databaseVersion)
{
case Enum_DatabaseVersion.Default:
case Enum_DatabaseVersion.OleDB:
{
return(viewSql);
}
case Enum_DatabaseVersion.SqlServer2000:
{
return(string.Format("select top {3} * from ({0}) as {1}_A where id not in (select top {4} id from ({0}) as {1}_B order by {2}) order by {2};select Count(*) as Amount from ({0}) as {1}_C"
, viewSql
, viewName
, sortBy
, dataCount
, ((pageIndex - 1) * dataCount)
));
}
case Enum_DatabaseVersion.SqlServer2005:
case Enum_DatabaseVersion.SqlServer2008:
{
return(string.Format("select * from (select Row_Number() over(order by {3}) as {2},* from ({0}) as {1}_A) as {1}_B where {2} between {4} and {5};select Count(*) as Amount from ({0}) as {1}_C"
, viewSql
, viewName
, rowIndexName
, sortBy
, ((pageIndex - 1) * dataCount + 1)
, (pageIndex * dataCount)
));
}
case Enum_DatabaseVersion.Oracle:
{
return(string.Format("select * from (select RowNum {2},* from ({0} order by {3}) {1}_A where RowNum >= {4} ) {1}_B where {1}_B.{2} <= {5};select Count(*) as Amount from ({0}) as {1}_C"
, viewSql
, viewName
, rowIndexName
, sortBy
, ((pageIndex - 1) * dataCount + 1)
, (pageIndex * dataCount)
));
}
default: goto case Enum_DatabaseVersion.Default;
}
}
