了解DbProviderFactory
前不久想使用下EF的通用单表增删改的特性，当时使用控制台做测试，怎么弄都没成功，原因出在app.config中，反而在mvc项目中，就没有任何问题。在反复的更改配置的过程中，发现了DbProviderFactory这个类，好奇打开看看其定义。
// 摘要:
// 表示一组方法，这些方法用于创建提供程序对数据源类的实现的实例。
public abstract class DbProviderFactory
{
// 摘要:
// 初始化 System.Data.Common.DbProviderFactory 类的新实例。
protected DbProviderFactory(); // 摘要:
// 指定特定的 System.Data.Common.DbProviderFactory 是否支持 System.Data.Common.DbDataSourceEnumerator
// 类。
//
// 返回结果:
// 如果 System.Data.Common.DbProviderFactory 的实例支持 System.Data.Common.DbDataSourceEnumerator
// 类，则为 true；否则为 false。
public virtual bool CanCreateDataSourceEnumerator { get; } // 摘要:
// 返回实现 System.Data.Common.DbCommand 类的提供程序的类的一个新实例。
//
// 返回结果:
// System.Data.Common.DbCommand 的新实例。
public virtual DbCommand CreateCommand();
//
// 摘要:
// 返回实现 System.Data.Common.DbCommandBuilder 类的提供程序的类的一个新实例。
//
// 返回结果:
// System.Data.Common.DbCommandBuilder 的新实例。
public virtual DbCommandBuilder CreateCommandBuilder();
//
// 摘要:
// 返回实现 System.Data.Common.DbConnection 类的提供程序的类的一个新实例。
//
// 返回结果:
// System.Data.Common.DbConnection 的新实例。
public virtual DbConnection CreateConnection();
//
// 摘要:
// 返回实现 System.Data.Common.DbConnectionStringBuilder 类的提供程序的类的一个新实例。
//
// 返回结果:
// System.Data.Common.DbConnectionStringBuilder 的新实例。
public virtual DbConnectionStringBuilder CreateConnectionStringBuilder();
//
// 摘要:
// 返回实现 System.Data.Common.DbDataAdapter 类的提供程序的类的一个新实例。
//
// 返回结果:
// System.Data.Common.DbDataAdapter 的新实例。
public virtual DbDataAdapter CreateDataAdapter();
//
// 摘要:
// 返回实现 System.Data.Common.DbDataSourceEnumerator 类的提供程序的类的一个新实例。
//
// 返回结果:
// System.Data.Common.DbDataSourceEnumerator 的新实例。
public virtual DbDataSourceEnumerator CreateDataSourceEnumerator();
//
// 摘要:
// 返回实现 System.Data.Common.DbParameter 类的提供程序的类的一个新实例。
//
// 返回结果:
// System.Data.Common.DbParameter 的新实例。
public virtual DbParameter CreateParameter();
//
// 摘要:
// 返回提供程序的类的新实例，该实例可实现提供程序的 System.Security.CodeAccessPermission 类的版本。
//
// 参数:
// state:
// System.Security.Permissions.PermissionState 值之一。
//
// 返回结果:
// 指定 System.Security.Permissions.PermissionState 的 System.Security.CodeAccessPermission
// 对象。
public virtual CodeAccessPermission CreatePermission(PermissionState state);
}
数据库5大对象全了。我想是不是可以通过它来构建出更抽象的数据库访问。后来查询相关资料，并阅读了 PetaPoco 这个微型Orm的源代码，发现其也是使用DbProviderFactory 来创建数据库连接等对象的。
反观自己在最初学习SQLServer，Oracle,Sqlite时，分别封装了SqlHelp，OracleHelp，SqliteHelp等简化数据库的操作，当时还洋洋自得，现在看来很是无语，代码几乎是一致的，无非是使用的Connection、Command、Adapter、Parameter 等对象的不同，如果使用DbProviderFactory来隔绝具体的实例，是不是可以把这3个数据库Help类封装成一个通用的DbHelp呢？
DbProviderFactory 导入外部的Provider
初步测试是可以的，不过在通过config文件载入DbProviderFactory时遇到点问题，花费了好一会。过程就不说了，如使用Oracle官网的dll，正确的配置是这样：
<system.data>
<DbProviderFactories>
<add name="OracleClientFactory" invariant="OracleClientFactory" description="Oracle.ManagedDataAccess.Client.OracleClientFactory"
type="Oracle.ManagedDataAccess.Client.OracleClientFactory,Oracle.ManagedDataAccess, Version=4.121.1.0, Culture=neutral, PublicKeyToken=89b483f429c47342" />
</DbProviderFactories>
</system.data>
其中要注意的，是 invariant 和 type 属性。
invariant: 可以通过这个属性值获取到对应DbProviderFactory类型。如，DbProviderFactories.GetFactory("OracleClientFactory") ;
type:类名(含命名空间),程序集名称,程序集信息。
我们经常可以在web.config文件中看到类似的type信息，但如果是自己拓展，或者刚刚从Oracle官网上下载的组件呢，该如何得到这些信息。使用Reflector就可以轻松看到，以前一直用Reflector，但从没关注过这一块。
另外通过 DbProviderFactories.GetFactoryClasses(); 可以查看到所有被安装的Provider 哦，通过配置文件导入的也会在其中显示。
<system.data>
<DbProviderFactories>
<add name="SQLiteFactory" invariant="SQLiteFactory"
description="xxxxxxx"
type="System.Data.SQLite.SQLiteFactory,System.Data.SQLite" />
</DbProviderFactories>
</system.data> 检测配置是否成功 var sqlite = DbProviderFactories.GetFactory("SQLiteFactory");
SQLite配置
改善自己的DbHelp类
数据库操作无非是执行查询与非查询，在与之相关的方法上，封装的没有任何问题。但是在原来的Oracle和Sqlite封装类中，支持分页。如传递普通的查询sql语句会自动生成分页sql呢。其中Oracle 是使用ROWNUM这个来分页，并没有使用ROW_NUMBER()这个分析函数，而Sqlite 则使用limit 关键字，分页更简单。唯独SQLServer，从2000 到 2012，有3种分页方式，这个处理起来有些麻烦。
在借鉴了Patacoco通过正则拆分SQL，最终拼接出分页的SQL语句的相关代码。具体生成分页语句的思路,则请看 模仿Orm生成分页SQL 。
代码有点长，展开请注意。
public partial class DbHelp
{
enum DBType
{
SqlServer2000,
SqlServer,
Oracle,
SQLite
} #region 成员定义
private DbProviderFactory dbProvider;//数据库Provider
private DBType dbType;//数据库类型
private char pSymbol = '@';//参数符号 private DbConnection conn;//连接对象
private string connectionString;//连接字符串
private DbTransaction tran;//事务对象 private IList parameterList = new List<DbParameter>();//过程参数列表
private bool hasOutput = false;//是否包含输出参数
private Dictionary<string, object> dicPara = new Dictionary<string, object>();//输出参数列表
#endregion #region 构造方法，实例化连接字符串 /// <summary>
/// 读取WebConfig链接字符串
/// </summary>
/// <param name="connectionName">ConnectionString配置名</param>
public DbHelp(string connectionName = "")
{
//默认使用ConnectionString第一项
var config = string.IsNullOrEmpty(connectionName) ?
ConfigurationManager.ConnectionStrings[] :
ConfigurationManager.ConnectionStrings[connectionName];
dbProvider = DbProviderFactories.GetFactory(config.ProviderName);
connectionString = config.ConnectionString;
CommonConstruct(config.ProviderName);
} /// <summary>
/// 有参构造，实例化连接字符串
/// </summary>
/// <param name="provider">DbProvider</param>
/// <param name="connectionString">连接字符串</param>
public DbHelp(DbProviderFactory provider, string connectionString)
{
this.dbProvider = provider;
this.connectionString = connectionString;
CommonConstruct(provider.GetType().Name);
} private void CommonConstruct(string _dbtype = "")
{
// Try using type name first (more reliable)
if (_dbtype.StartsWith("Oracle")) dbType = DBType.Oracle;
else if (_dbtype.StartsWith("SQLite")) dbType = DBType.SQLite;
else if (_dbtype.StartsWith("System.Data.SqlClient")) dbType = DBType.SqlServer;
// else try with provider name
else if (_dbtype.IndexOf("Oracle", StringComparison.InvariantCultureIgnoreCase) >= ) dbType = DBType.Oracle;
else if (_dbtype.IndexOf("SQLite", StringComparison.InvariantCultureIgnoreCase) >= ) dbType = DBType.SQLite; if (dbType == DBType.Oracle)
pSymbol = ':';
else
pSymbol = '@';
}
#endregion #region 实现接口IDisposable
/// <释放资源接口>
/// 实现接口IDisposable
/// </释放资源接口>
public void Dispose()
{
if (conn != null)
{
if (conn.State == ConnectionState.Open)//判断数据库连接池是否打开
{
conn.Close();
} if (parameterList.Count > )//判断参数列表是否清空
{
parameterList.Clear();
}
conn.Dispose();//释放连接池资源
GC.SuppressFinalize(this);//垃圾回收
}
}
#endregion #region 执行SQL或存储过程 并返回影响的行数
/// <summary>
/// 执行SQL，并返回影响的行数
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns></returns>
public int ExecuteNonQuery(string sql)
{
using (var cmd = CreateCommand(sql))
{
return ExecuteNonQuery(cmd);
}
} /// <summary>
/// 执行存储过程，并返回影响的行数
/// </summary>
/// <param name="storeProcedureName">存储过程名</param>
/// <returns></returns>
public int ExecuteProceudre(string storeProcedureName)
{
using (var cmd = CreateCommand(storeProcedureName, CommandType.StoredProcedure))
{
return ExecuteNonQuery(cmd);
}
}
#endregion #region 执行SQL或者存储过程，并返回DataTable
/// <summary>
/// 执行SQL语句并返回DataTable
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns></returns>
public DataTable ExecuteSql(string sql)
{
using (var cmd = CreateCommand(sql))
{
return Execute(cmd);
}
} /// <summary>
/// 执行存储过程并返回DataTable
/// </summary>
/// <param name="storeProcedureName">存储过程名</param>
/// <returns></returns>
public DataTable ExecuteProc(string storeProcedureName)
{
using (var cmd = CreateCommand(storeProcedureName, CommandType.StoredProcedure))
{
return Execute(cmd);
}
}
#endregion #region 执行SQL或存储过程并返回DbDataReader
/// <summary>
/// 执行SQL语句并返回DbDataReader
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>返回DbDataReader</returns>
public DbDataReader ExecuteReader(string sql)
{
using (var cmd = CreateCommand(sql))
{
return ExecuteReader(cmd);
}
} /// <summary>
/// 执行存储过程并返回DbDataReader
/// </summary>
/// <param name="storeProcedureName">存储过程名</param>
/// <returns>返回DbDataReader</returns>
public DbDataReader ExecuteProcReader(string storeProcedureName)
{
using (var cmd = CreateCommand(storeProcedureName, CommandType.StoredProcedure))
{
return ExecuteReader(cmd);
}
}
#endregion #region 执行统计
/// <summary>
/// 执行SQL语句 返回首行首列的值,一般用于统计
/// </summary>
/// <param name="sql">SQL语句</param>
/// <returns>查询结果首行首列的值转换为整形，转换失败则返回-1</returns>
public int Count(string sql)
{
using (var cmd = CreateCommand(sql))
{
return ExecuteScalar(cmd);
}
}
#endregion #region 测试连接是否成功
/// <summary>
/// 测试连接是否成功
/// </summary>
/// <returns></returns>
public bool HasConnection
{
get
{
try
{
conn = new SqlConnection(connectionString);
conn.Open();
return true;
}
catch
{
return false;
}
finally
{
conn.Close();
}
}
}
#endregion #region 索引器访问
public object this[string name]
{
set
{
this[name, DbType.Object, ParameterDirection.Input] = value;
}
get
{
object obj;
if (dicPara.TryGetValue(name, out obj))
{
return obj;
}
return null;
}
} public object this[string name, DbType dbtype]
{
set
{
this[name, dbtype, ParameterDirection.Input] = value;
}
} public object this[string name, DbType dbType, ParameterDirection direction]
{
set
{
if (name[] != pSymbol) name = pSymbol + name; var para = dbProvider.CreateParameter();
if (dbType != DbType.Object)
para.DbType = dbType;
para.ParameterName = name;
para.Value = value == null ? DBNull.Value : value;
parameterList.Add(para);
}
}
#endregion #region 命令相关处理
/// <summary>
/// 创建DbCommand
/// </summary>
/// <param name="cmdText">命名文本</param>
/// <param name="cmdType">命名类型</param>
/// <returns></returns>
private DbCommand CreateCommand(string cmdText, CommandType cmdType = CommandType.Text)
{
//创建数据库连接对象
if (conn == null || conn.State != ConnectionState.Open)
{
conn = dbProvider.CreateConnection();
conn.ConnectionString = connectionString;
conn.Open();//打开数据库连接池
} //创建Command命令
var cmd = conn.CreateCommand();
cmd.Connection = conn;
cmd.CommandType = cmdType;
if (!string.IsNullOrEmpty(cmdText))
cmd.CommandText = cmdText;
if (tran != null) cmd.Transaction = tran;
cmd.CommandTimeout = ; //加载过程参数
LoadParamter(cmd);
return cmd;
} /// <summary>
/// 创建过程参数
/// </summary>
/// <param name="name">参数名</param>
/// <param name="value">参数值</param>
/// <param name="t">参数值类型</param>
/// <param name="pDirection">参数类型</param>
/// <returns></returns>
private DbParameter CreateParameter(string name, object value, DbType t = DbType.Object, ParameterDirection pDirection = ParameterDirection.Input)
{
var para = dbProvider.CreateParameter();
if (t != DbType.Object) para.DbType = t;
para.Direction = pDirection;
if (name[] == pSymbol)
{
para.ParameterName = name;
}
else
{
para.ParameterName = pSymbol + name;
}
para.Value = value;
return para;
} /// <summary>
/// 执行Command 并返回影响的行数
/// </summary>
/// <param name="cmd">命令</param>
/// <returns></returns>
private int ExecuteNonQuery(DbCommand cmd)
{
try
{
return cmd.ExecuteNonQuery();
}
catch (Exception)
{
conn.Close();
throw;
}
finally
{
if (tran == null) Dispose();
}
} /// <summary>
/// 执行Command 并返回影响的行数
/// </summary>
/// <param name="cmd">命令</param>
/// <returns></returns>
private int ExecuteScalar(DbCommand cmd)
{
try
{
var ret = cmd.ExecuteScalar().ToString();
int i;
if (!int.TryParse(ret, out i))
{
throw new Exception("can't parse it to int,the value is " + ret);
}
return i;
}
catch (Exception)
{
conn.Close();
throw;
}
finally
{
if (tran == null) Dispose();
}
} /// <summary>
/// 执行Command 并返回DataTable
/// </summary>
/// <param name="cmd">命令</param>
/// <returns></returns>
private DataTable Execute(DbCommand cmd)
{
try
{
using (var adapter = dbProvider.CreateDataAdapter())//创建适配器
{
adapter.SelectCommand = cmd;
adapter.SelectCommand.CommandTimeout = ;
var dt = new DataTable();
adapter.Fill(dt);
return dt;//返回结果集
}
}
catch (Exception)
{
conn.Close();
throw;
}
finally
{
if (tran == null) Dispose();
}
} /// <summary>
/// 执行Command 并返回DbDataReader
/// </summary>
/// <param name="cmd">命令</param>
/// <returns></returns>
private DbDataReader ExecuteReader(DbCommand cmd)
{
try
{
return cmd.ExecuteReader(CommandBehavior.CloseConnection);
}
catch (Exception)
{
conn.Close();
throw;
}
finally
{
if (tran == null) Dispose();
}
} /// <summary>
/// 加载输出参数至字典中 仅当执行非查询时才调用
/// </summary>
/// <param name="Parameters"></param>
private void InitDic(DbParameterCollection Parameters)
{
if (hasOutput)
{
dicPara.Clear();
foreach (DbParameter Para in Parameters)
{
if (Para.Direction != ParameterDirection.Input)
{
dicPara.Add(Para.ParameterName, Para.Value);
}
}
hasOutput = false;
}
} /// <summary>
/// 加载过程参数输入至Commond中
/// </summary>
/// <param name="cmd"></param>
private void LoadParamter(DbCommand cmd)
{
if (parameterList.Count != )
{
foreach (DbParameter Para in parameterList)
{
if (!hasOutput && Para.Direction != ParameterDirection.Input)
{
hasOutput = true;
}
cmd.Parameters.Add(Para);
}
parameterList.Clear();
}
} /// <summary>
/// 将参数化Sql转换成纯Sql，便于调试
/// </summary>
/// <param name="strSql">SQL语句</param>
/// <returns></returns>
private string getSqlOnly(DbCommand cmd)
{
var sql = cmd.CommandText;
foreach (DbParameter para in cmd.Parameters)
{
if (para.DbType == DbType.Int16 || para.DbType == DbType.Int32 || para.DbType == DbType.Int64 || para.DbType == DbType.UInt16 || para.DbType == DbType.UInt32 || para.DbType == DbType.UInt64 || para.DbType == DbType.Decimal || para.DbType == DbType.Double || para.DbType == DbType.Single)
{
sql = sql.Replace(para.ParameterName, para.Value.ToString());
}
else if (pSymbol == '@' || para.DbType == DbType.AnsiString || para.DbType == DbType.String || para.DbType == DbType.StringFixedLength)
{
sql = sql.Replace(para.ParameterName, "'" + para.Value.ToString() + "'");
}
else if (pSymbol == ':' || para.DbType == DbType.DateTime || para.DbType == DbType.DateTime2 || para.DbType == DbType.DateTimeOffset)
{
//排除未知的时间类型
DateTime time;
if (DateTime.TryParse(para.Value.ToString(), out time))
{
sql = sql.Replace(para.ParameterName, "to_date('" + time.ToString() + "','yyyy-MM-dd hh24:mi:ss')");
}
}
}
return sql;
}
#endregion #region 分页查询
/// <summary>
/// 对指定Sql语句查询的结果集进行分页
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="start">结果集起始行号，不包括此行</param>
/// <param name="limit">取出的行数</param>
/// <returns></returns>
public DataTable ExecuteSql(string sql, int start, int limit)
{
string[] sqls;
var pageParms = CreatePageSql(sql, out sqls, start, limit);
using (var cmd = CreateCommand(sqls[]))
{
cmd.Parameters.AddRange(pageParms);
return Execute(cmd);
}
} /// <summary>
/// 对指定Sql语句查询的结果集进行分页
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="start">结果集起始行号，不包括此行</param>
/// <param name="limit">取出的行数</param>
/// <returns></returns>
public DbDataReader ExecuteReader(string sql, int start, int limit)
{
string[] sqls;
var pageParms = CreatePageSql(sql, out sqls, start, limit);
using (var cmd = CreateCommand(sqls[]))
{
cmd.Parameters.AddRange(pageParms);
return ExecuteReader(cmd);
}
} /// <summary>
/// 对指定Sql语句查询的结果集进行分页
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="start">结果集起始行号，不包括此行</param>
/// <param name="limit">取出的行数</param>
/// <param name="count">输出总行数</param>
/// <returns></returns>
public DataTable ExecuteSql(string sql, int start, int limit, out int count)
{
//查看是否已经使用事务,若没有使用事务，这里必须使用事务执行
var alreadyUserTran = tran != null;
if (!alreadyUserTran)
BeginTransation(); string[] sqls;
var pageParms = CreatePageSql(sql, out sqls, start, limit, true);
using (var cmd = CreateCommand(sqls[]))
{
count = ExecuteScalar(cmd); //加载逆序分页 并返回过程参数
var pageReverse = CreatePageSqlReverse(sql, ref sqls, start, limit, count);
if (pageReverse != null)
cmd.Parameters.AddRange(pageParms);
else
cmd.Parameters.AddRange(pageParms); cmd.CommandText = sqls[];
var dt = Execute(cmd); //如果事先已开启事务，则不在此处提交事务，应由用户调用时手动提交，否则自动提交方法
if (!alreadyUserTran)
tran.Commit(); return dt;
}
} /// <summary>
/// 对指定Sql语句查询的结果集进行分页
/// </summary>
/// <param name="sql">sql语句</param>
/// <param name="start">结果集起始行号，不包括此行</param>
/// <param name="limit">取出的行数</param>
/// <param name="count">输出总行数</param>
/// <returns></returns>
public DbDataReader ExecuteReader(string sql, int start, int limit, out int count)
{
//查看是否已经使用事务,若没有使用事务，这里必须使用事务执行
var alreadyUserTran = tran != null;
if (!alreadyUserTran)
BeginTransation(); string[] sqls;
var pageParms = CreatePageSql(sql, out sqls, start, limit, true);
using (var cmd = CreateCommand(sqls[]))
{
count = ExecuteScalar(cmd); //加载逆序分页 并返回过程参数
var pageReverse = CreatePageSqlReverse(sql, ref sqls, start, limit, count);
if (pageReverse != null)
cmd.Parameters.AddRange(pageParms);
else
cmd.Parameters.AddRange(pageParms); cmd.CommandText = sqls[];
return ExecuteReader(cmd);
}
}
#endregion #region 开启事务
/// <summary>
/// 开启事务
/// </summary>
/// <returns></returns>
public void BeginTransation()
{
//创建数据库连接对象
if (conn == null || conn.State != ConnectionState.Open)
{
conn = dbProvider.CreateConnection();
conn.ConnectionString = connectionString;
conn.Open();//打开数据库连接池
}
tran = conn.BeginTransaction();
} /// <summary>
/// 提交事务
/// </summary>
public void Commit()
{
if (tran != null)
{
tran.Commit();
tran.Dispose();
tran = null;
Dispose();
}
} /// <summary>
/// 回滚事务
/// </summary>
public void Rollback()
{
if (tran != null)
{
tran.Rollback();
tran.Dispose();
tran = null;
Dispose();
}
}
#endregion #region 生成 分页SQL语句
/// <summary>
/// 匹配移除Select后的sql
/// </summary>
private Regex rxColumns = new Regex(@"\A\s*SELECT\s+((?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|.)*?)(?<!,\s+)\bFROM\b", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
/// <summary>
/// 匹配SQL语句中Order By字段
/// </summary>
private Regex rxOrderBy = new Regex(@"\b(?<ordersql>ORDER\s+BY\s+(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+)(?:\s+(?<order>ASC|DESC))?(?:\s*,\s*(?:\((?>\((?<depth>)|\)(?<-depth>)|.?)*(?(depth)(?!))\)|[\w\(\)\.])+(?:\s+(?:ASC|DESC))?)*", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
/// <summary>
/// 匹配SQL语句中Distinct
/// </summary>
private Regex rxDistinct = new Regex(@"\ADISTINCT\s", RegexOptions.IgnoreCase | RegexOptions.Multiline | RegexOptions.Singleline | RegexOptions.Compiled);
/// <summary>
/// 分析Sql语句 输出分析数组 信息依次为:
/// 0.countsql
/// 1.pageSql(保留位置此处不做分析)
/// 2.移除了select的sql
/// 3.order by 字段 desc
/// 4.order by 字段
/// 5.desc
/// </summary>
/// <param name="sql"></param>
/// <returns></returns>
private string[] SplitSqlForPaging(string sql)
{
var sqlInfo = new string[];
// Extract the columns from "SELECT <whatever> FROM"
var m = rxColumns.Match(sql);
if (!m.Success)
return null; // Save column list and replace with COUNT(*)
Group g = m.Groups[];
sqlInfo[] = sql.Substring(g.Index); if (rxDistinct.IsMatch(sqlInfo[]))
sqlInfo[] = sql.Substring(, g.Index) + "COUNT(" + m.Groups[].ToString().Trim() + ") " + sql.Substring(g.Index + g.Length);
else
sqlInfo[] = sql.Substring(, g.Index) + "COUNT(*) " + sql.Substring(g.Index + g.Length); // Look for an "ORDER BY <whatever>" clause
m = rxOrderBy.Match(sqlInfo[]);
if (!m.Success)
{
sqlInfo[] = null;
}
else
{
g = m.Groups[];
sqlInfo[] = g.ToString();
//统计的SQL 移除order
sqlInfo[] = sqlInfo[].Substring(, g.Index) + sqlInfo[].Substring(g.Index + g.Length);
//存储排序信息
sqlInfo[] = m.Groups["ordersql"].Value;//order by xxx
sqlInfo[] = m.Groups["order"].Value;//desc //select部分 移除order
sqlInfo[] = sqlInfo[].Replace(sqlInfo[], string.Empty);
} return sqlInfo;
} /// <summary>
/// 生成逆序分页Sql语句
/// </summary>
/// <param name="sql"></param>
/// <param name="sqls"></param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <param name="total"></param>
private DbParameter[] CreatePageSqlReverse(string sql, ref string[] sqls, int start, int limit, int total = )
{
//如果总行数不多或分页的条数位于前半部分，没必要逆序分页
if (total < || start <= total / )
{
return null;
} //sql正则分析过后的数组有5个值，若未分析，此处分析
if (sqls == null || sqls.Length == )
{
sqls = SplitSqlForPaging(sql);
if (sqls == null)
{
//无法解析的SQL语句
throw new Exception("can't parse sql to pagesql ,the sql is " + sql);
}
} //如果未定义排序规则，则无需做逆序分页计算
if (string.IsNullOrEmpty(sqls[]))
{
return null;
} //逆序分页检查
string sqlOrder = sqls[];
int end = start + limit; //获取逆序排序的sql
string sqlOrderChange = string.Compare(sqls[], "desc", true) == ?
string.Format("{0} ASC ", sqls[]) :
string.Format("{0} DESC ", sqls[]); /*理论
* total:10000 start:9980 limit:10
* 则 end:9990 分页条件为 RN >= 9980+1 and RN <= 9990
* 逆序调整后
* start = total - start = 20
* end = total - end + 1 = 11
* 交换start和end，分页条件为 RN >= 11 and RN<= 20
*/
//重新计算start和end
start = total - start;
end = total - end + ;
//交换start end
start = start + end;
end = start - end;
start = start - end; //定义分页SQL
var pageSql = new StringBuilder(); if (dbType == DBType.SqlServer2000)
{
pageSql.AppendFormat("SELECT TOP @PageLimit * FROM ( SELECT TOP @PageEnd {0} {1} ) ", sqls[], sqlOrderChange);
}
else if (dbType == DBType.SqlServer)
{
//组织分页SQL语句
pageSql.AppendFormat("SELECT PageTab.* FROM ( SELECT TOP @PageEnd ROW_NUMBER() over ({0}) RN , {1} ) PageTab ",
sqlOrderChange,
sqls[]); //如果查询不是第一页，则需要判断起始行号
if (start > )
{
pageSql.Append("Where RN >= :PageStart ");
}
}
else if (dbType == DBType.Oracle)
{
pageSql.AppendFormat("SELECT ROWNUM RN, PageTab.* FROM ( Select {0} {1} ) PageTab where ROWNUM <= :PageEnd ", sqls[], sqlOrderChange); //如果查询不是第一页，则需要判断起始行号
if (start > )
{
pageSql.Insert(, "SELECT * FROM ( ");
pageSql.Append(" ) ");
pageSql.Append(" WHERE RN>= :PageStart ");
}
}
else if (dbType == DBType.SQLite)
{
pageSql.AppendFormat("SELECT * FROM ( SELECT {0} {1} limit @PageStart,@PageLimit ) PageTab ", sqls[], sqlOrderChange);
} //恢复排序
pageSql.Append(sqlOrder); //存储生成的分页SQL语句
sqls[] = pageSql.ToString(); //临时测试
//sqls[1] = sqls[1].Replace("@", "").Replace(":", "").Replace("PageStart", start + "").Replace("PageEnd", end + "").Replace("PageLimit", limit + ""); //组织过程参数
DbParameter[] paras = null;
if (dbType == DBType.SqlServer2000 || dbType == DBType.SQLite)
{
paras = new DbParameter[];
paras[] = CreateParameter("@PageLimit", limit);
paras[] = CreateParameter("@PageEnd", end);
}
else if (start > )
{
paras = new DbParameter[];
paras[] = CreateParameter("@PageStart", start);
paras[] = CreateParameter("@PageEnd", end);
}
else
{
paras = new DbParameter[] { CreateParameter("@PageEnd", end) };
} return paras;
} /// <summary>
/// 生成常规Sql语句
/// </summary>
/// <param name="sql"></param>
/// <param name="sqls"></param>
/// <param name="start"></param>
/// <param name="limit"></param>
/// <param name="createCount"></param>
private DbParameter[] CreatePageSql(string sql, out string[] sqls, int start, int limit, bool createCount = false)
{
//需要输出的sql数组
sqls = null; //生成count的SQL语句 SqlServer生成分页，必须通过正则拆分
if (createCount || dbType == DBType.SqlServer || dbType == DBType.SqlServer2000)
{
sqls = SplitSqlForPaging(sql);
if (sqls == null)
{
//无法解析的SQL语句
throw new Exception("can't parse sql to pagesql ,the sql is " + sql);
}
}
else
{
sqls = new string[];
} //组织分页SQL语句
var pageSql = new StringBuilder(); //构建分页参数
var end = start + limit;
start++; if (dbType == DBType.SqlServer2000)
{
pageSql.AppendFormat("SELECT TOP @PageEnd {0} {1}", sqls[], sqls[]); if (start > )
{
var orderChange = string.IsNullOrEmpty(sqls[]) ? null :
string.Compare(sqls[], "desc", true) == ?
string.Format("{0} ASC ", sqls[]) :
string.Format("{0} DESC ", sqls[]);
pageSql.Insert(, "SELECT TOP 100 PERCENT * FROM (SELECT TOP @PageLimit * FROM ( ");
pageSql.AppendFormat(" ) PageTab {0} ) PageTab2 {1}", orderChange, sqls[]);
}
}
else if (dbType == DBType.SqlServer)
{
pageSql.AppendFormat(" Select top (@PageEnd) ROW_NUMBER() over ({0}) RN , {1}",
string.IsNullOrEmpty(sqls[]) ? "ORDER BY (SELECT NULL)" : sqls[],
sqls[]); //如果查询不是第一页，则需要判断起始行号
if (start > )
{
pageSql.Insert(, "Select PageTab.* from ( ");
pageSql.Append(" ) PageTab Where RN >= @PageStart");
}
}
else if (dbType == DBType.Oracle)
{
pageSql.Append("select ROWNUM RN, PageTab.* from ");
pageSql.AppendFormat(" ( {0} ) PageTab ", sql);
pageSql.Append(" where ROWNUM <= :PageEnd "); //如果查询不是第一页，则需要判断起始行号
if (start > )
{
pageSql.Insert(, "select * from ( ");
pageSql.Append(" ) Where RN>= :PageStart ");
}
}
else if (dbType == DBType.SQLite)
{
pageSql.AppendFormat("{0} limit @PageStart,@PageLimit", sql, start, limit);
} //存储生成的分页SQL语句
sqls[] = pageSql.ToString(); //临时测试
//sqls[1] = sqls[1].Replace("@", "").Replace(":", "").Replace("PageStart", start + "").Replace("PageEnd", end + "").Replace("PageLimit", limit + ""); //组织过程参数
DbParameter[] paras;
if (dbType == DBType.SqlServer2000 || dbType == DBType.SQLite)
{
paras = new DbParameter[];
paras[] = CreateParameter("@PageLimit", limit);
paras[] = CreateParameter("@PageEnd", end);
}
else if (start > )
{
paras = new DbParameter[];
paras[] = CreateParameter("@PageStart", start);
paras[] = CreateParameter("@PageEnd", end);
}
else
{
paras = new DbParameter[] { CreateParameter("@PageEnd", end) };
} return paras;
}
#endregion }
DbHelp