/// <summary>
/// 获取数据库连接
/// </summary>
/// <param name="type">数据库类型</param>
/// <param name="connectionString">连接字符串</param>
/// <returns></returns>
public static IDbConnection GetConnection(DataBaseType type, string connectionString)
{
try
{
switch (type)
{
case DataBaseType.MSSQL:
return(new SqlConnection(connectionString));
case DataBaseType.MYSQL:
return(new MySqlConnection(connectionString));
case DataBaseType.ORACLE:
throw new NotImplementedException(type.ToString());
case DataBaseType.SQLITE:
return(new SQLiteConnection(connectionString));
default:
throw new NotImplementedException(type.ToString());
}
}
catch (Exception e)
{
throw new Exception(e.Message, e);
}
}
private void SetProviderName(DataBaseType dataBaseType, string connectionString)
{
switch (dataBaseType)
{
case DataBaseType.MsSql:
_providerName = "System.Data.SqlClient";
_paramCharacter = "@";
break;
case DataBaseType.MySql:
_providerName = "MySql.Data.MySqlClient";
_paramCharacter = "@";
break;
case DataBaseType.Oracle:
_providerName = "System.Data.OracleClient";
_paramCharacter = ":";
break;
case DataBaseType.Sqlite:
_providerName = "System.Data.SQLite";
_paramCharacter = "@";
break;
default:
_providerName = "System.Data.OleDb";
_paramCharacter = "@";
break;
}
if (!string.IsNullOrWhiteSpace(connectionString))
{
this.ConnectionString = connectionString;
}
else if (ConfigurationManager.ConnectionStrings[dataBaseType.ToString()] != null)
{
this.ConnectionString = ConfigurationManager.ConnectionStrings[dataBaseType.ToString()].ConnectionString;
}
else
{
throw new ArgumentNullException("connectionString", dataBaseType.ToString() + "的connectionString不能为空！");
}
try
{
_dbfactory = DbProviderFactories.GetFactory(ProviderName);
}
catch (ConfigurationErrorsException ex)
{
throw new ConfigurationErrorsException(ex.Message + "(" + ProviderName + ")", ex);
}
}
/// <summary>
/// 创建带链接的实例
/// </summary>
/// <param name="dbt">数据库类型</param>
/// <param name="host">地址</param>
/// <param name="database">数据库名</param>
/// <param name="username">用户名</param>
/// <param name="password">密码</param>
/// <returns></returns>
public static IDBHelp Create(DataBaseType dbt, string host, string database, string username, string password)
{
string conn = "";
string typeName = "";
string text = "SucLib";
//string a = ConfigurationSettings.AppSettings["DBType"];
string a = dbt.ToString();//"SqlServer"; //ConfigUtil.ConfigHelper.GetConfigString("DBType");
if (a == "Access")
{
typeName = text + ".Data.Dal.AccHelp";
}
else if (a == "SqlServer" || string.IsNullOrEmpty(a))
{
typeName = text + ".Data.Dal.SQLHelp";
conn = string.Format(@"Data Source={0};Initial Catalog={1};User ID={2};Pwd={3}", host, database, username, password);
}
else if (a == "MySql")
{
}
else if (a == "DB2")
{
}
else if (a == "Oracle")
{
}
else if (a == "SqlLite")
{
}
Type t = Type.GetType(typeName);
return((IDBHelp)Activator.CreateInstance(t, new object[] { conn }));
}
/// <summary>
/// 创建带链接的实例
/// </summary>
/// <param name="dbt">数据库类型</param>
/// <param name="conn">连接字符串</param>
/// <returns></returns>
public static IDBHelp Create(DataBaseType dbt, string conn)
{
string typeName = "";
string text = "SucLib";
//string a = ConfigurationSettings.AppSettings["DBType"];
string a = dbt.ToString();//"SqlServer"; //ConfigUtil.ConfigHelper.GetConfigString("DBType");
if (a == "Access")
{
typeName = text + ".Data.Dal.AccHelp";
}
else if (a == "SqlServer" || string.IsNullOrEmpty(a))
{
typeName = text + ".Data.Dal.SQLHelp";
}
else if (a == "MySql")
{
}
else if (a == "DB2")
{
}
else if (a == "Oracle")
{
}
else if (a == "SqlLite")
{
}
Type t = Type.GetType(typeName);
return((IDBHelp)Activator.CreateInstance(t, new object[] { conn }));
}
/*
public Dao()
{
try
{
connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
switch (ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName.ToUpper())
{
case "SQLSERVER":
DataBaseType = DataBaseType.SqlServer;
break;
case "FIREBIRD":
DataBaseType = DataBaseType.Firebird;
break;
case "MYSQL":
DataBaseType = DataBaseType.MySql;
break;
default:
throw new Exception("O banco de dados " + ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName.ToUpper() + " não é suportado por esta biblioteca. Deve-se usar um dos seguintes bancos: SQLSERVER,FIREBIRD ou MYSQL.");
}
BootConnection();
}
catch (Exception ex)
{
throw new Exception("Erro no construtor Connection().", ex);
}
}
*/
public Dao(DataBaseType dataBaseType, string connectionString)
{
try
{
this.connectionString = connectionString;
this.dataBaseType = dataBaseType;
BootConnection();
}
catch (Exception ex)
{
throw new Exception("Erro no construtor Connection(" + connectionString + "," + dataBaseType.ToString() + "). \n" + ex.Message, ex);
}
}
private static string GetSign(DataBaseType dbType)
{
switch (dbType)
{
case DataBaseType.MSSqlServer:
case DataBaseType.PostgreSql:
return("@");
case DataBaseType.MySql:
return("?");
default:
throw new NotSupportedException(dbType.ToString());
}
}
/// <summary>
/// SQL 分页查询 SQL 语句
/// </summary>
/// <param name="querySQL">SQL 语句，注意不带排序字段</param>
/// <param name="orderby">排序字段，注意：不要带 order by 关键字，如: order1 desc,order2 asc</param>
/// <param name="pageData">分页数据</param>
/// <returns>返回整理过的分页 SQL 语句</returns>
protected override string GetPagedQuerySQL(string querySQL, string orderby, PagedData pageData)
{
if (querySQL.IsEmpty())
{
throw new Exception("参数 querySQL 不能为空！");
}
if (orderby.IsEmpty())
{
throw new Exception("参数 orderby 不能为空！");
}
if (pageData == null)
{
throw new Exception("参数 pageData 不能为空！");
}
if (pageData.RecordCount == 0)
{
return(querySQL);
}
switch (MyDataBaseType)
{
case DataBaseType.SQLSERVER2008CE:
{
const string FirstPagedQuerySQLText = "select top {0} * from ({1}) as ___table0 order by {2}";
const string PagedQuerySQLText = "select * from (select top {0} * from (select top {1} * from ({2}) as ___table0 order by {3}) as ___table1 order by {4}) as ___table2 order by {3}";
if (pageData.IsFirstPage)
{
return(string.Format(FirstPagedQuerySQLText, pageData.RecordCountInPage, querySQL, orderby));
}
// 定义一个配置数组用来填充查询语句
object[] configFields = new object[5];
configFields[0] = pageData.RecordCountInPage;
configFields[1] = pageData.LastIndexInPage + 1;
configFields[2] = querySQL;
configFields[3] = orderby;
configFields[4] = this.GetOrderbyDesc(orderby); // 和 orderby 的排序相反的排序字段字符串
// 填充查询语句
return(string.Format(PagedQuerySQLText, configFields));
}
default:
throw new Exception("不正确的数据库类型" + MyDataBaseType.ToString());
}
}
/// <summary>
/// SQL 分页查询 SQL 语句
/// </summary>
/// <param name="querySQL">SQL 语句，注意不带排序字段</param>
/// <param name="orderby">排序字段，注意：不要带 order by 关键字，如: order1 desc,order2 asc</param>
/// <param name="pageData">分页数据</param>
/// <returns>返回整理过的分页 SQL 语句</returns>
protected override string GetPagedQuerySQL(string querySQL, string orderby, PagedData pageData)
{
if (querySQL.IsEmpty())
{
throw new Exception("参数 querySQL 不能为空！");
}
if (orderby.IsEmpty())
{
throw new Exception("参数 orderby 不能为空！");
}
if (pageData == null)
{
throw new Exception("参数 pageData 不能为空！");
}
if (pageData.RecordCount == 0)
{
return(querySQL);
}
switch (MyDataBaseType)
{
case DataBaseType.ORACLE11G:
{
const string PagedQuerySQLText = @"select * from
(select row_number() over(order by {1}) as rownumber___,table0___.* from ({2}) table0___) table1___
where rownumber___>={3} and rownumber___<={3}+{0}
order by rownumber___";
// 定义一个配置数组用来填充查询语句
object[] configFields = new object[5];
configFields[0] = pageData.RecordCountInPage; //top
configFields[1] = orderby; //orderby
configFields[2] = querySQL; //table
configFields[3] = pageData.FirstIndexInPage + 1; //firstindexinpage
// 填充查询语句
return(string.Format(PagedQuerySQLText, configFields));
}
default:
throw new Exception("不正确的数据库类型" + MyDataBaseType.ToString());
}
}
private bool IsExistsDbNameWithCache(string dbName)
{
try
{
string key = DataBaseType.ToString() + "." + dbName;
if (dbList.ContainsKey(key))
{
return(dbList[key]);
}
bool result = IsExistsDbName(dbName);
dbList.Add(key, result);
return(result);
}
catch
{
return(true);
}
}
public static DBAdapter GetAdapter(DataBaseType dbtype, string connectionString)
{
DBAdapter adapter;
switch (dbtype)
{
case DataBaseType.SqlServer:
adapter = new MSSQLAdapter();
break;
case DataBaseType.MySql:
adapter = new MySqlAdapter();
break;
default:
throw new Exception("该数据库的DBAdapter 未实现。" + dbtype.ToString());
}
adapter.ConnectionString = connectionString;
return(adapter);
}
/// <summary>
/// 加载映射文件
/// </summary>
public void AddMappingFile(NetType netGenerationType, DataBaseType databaseType)
{
var path = System.AppDomain.CurrentDomain.BaseDirectory ?? AppDomain.CurrentDomain.RelativeSearchPath;
var files = Directory.EnumerateFiles(path, string.Format("uway.{0}.{1}.mapping.xml", netGenerationType, databaseType.ToString()));
if (files.Any())
{
foreach (var item in files)
{
using (var stream = File.OpenRead(item))
{
try
{
AddFile(stream);
}
catch (Exception ex)
{
sqlLogger().Log(LogLevel.Error, ex, string.Format("读取映射文件:{0}",
item));
continue;
}
}
}
}
}
/// <summary>
/// Initializes a new instance of the <see cref="ExportAsPersonManagerAttribute"/> class.
/// </summary>
/// <param name="databaseType">
/// The database type.
/// </param>
public ExportAsPersonManagerAttribute(DataBaseType databaseType)
: base(ExportedContractName, typeof(IPersonManager))
{
_databaseType = databaseType.ToString();
}
/// <summary>
/// Initializes a new instance of the <see cref="ExportAsRuntimeDatabaseAttribute"/> class.
/// </summary>
/// <param name="databaseType">The database type.</param>
public ExportAsRuntimeDatabaseAttribute(DataBaseType databaseType)
: base(ExportedContractName, typeof(IRuntimeDatabase))
{
DatabaseType = databaseType.ToString();
}
private static DalBase GetDalBaseBy(ConnObject co)
{
DataBaseType dalType = co.Master.ConnDataBaseType;
//License.Check(providerName);//框架模块授权检测。
switch (dalType)
{
case DataBaseType.MsSql:
return(new MsSqlDal(co));
case DataBaseType.Access:
return(new OleDbDal(co));
case DataBaseType.Oracle:
return(new OracleDal(co));
case DataBaseType.SQLite:
return(new SQLiteDal(co));
case DataBaseType.MySql:
return(new MySQLDal(co));
case DataBaseType.Sybase:
return(new SybaseDal(co));
case DataBaseType.PostgreSQL:
return(new PostgreDal(co));
case DataBaseType.DB2:
return(new DB2Dal(co));
case DataBaseType.Txt:
case DataBaseType.Xml:
return(new NoSqlDal(co));
}
return((DalBase)Error.Throw(string.Format("GetHelper:{0} No Be Support Now!", dalType.ToString())));
}
public static IFreeSql DB(this DataBaseType t)
{
return(SelectDBType(t.ToString()));
}
public static DataBase CreateDatabase(DataBaseType nType, string nConnectionString)
{
string remotingString = GetRemotingUrl(nConnectionString);
if (remotingString != "")
{
try
{
string remotingPassword = GetRemotingPassword(nConnectionString);
bool remotingTrusted = GetRemotingTrusted(nConnectionString);
//Inicializar remoting
TcpChannel CanalTCP = ChannelServices.GetChannel("tcp") as TcpChannel;
if (CanalTCP != null)
{
if (CanalTCP.IsSecured != remotingTrusted)
{
try { ChannelServices.UnregisterChannel(CanalTCP); }
catch { }
RegisterChannel(remotingTrusted);
}
}
else
{
RegisterChannel(remotingTrusted);
}
// "tcp://localhost:8085/nAppName"
var instance = (DataBaseProxy)Activator.GetObject(typeof(DataBaseProxy), remotingString);
var ServerPublicKey = instance.getServerPublicKey();
var remotingEncriptedPassword = Crypto.RSAEncryptText(remotingPassword, ServerPublicKey);
DataBase db;
string Message;
if (!instance.CreateDataBase(nType, nConnectionString, remotingEncriptedPassword, out db, out Message))
{
throw new Exception(Message);
}
return db;
}
catch (Exception ex)
{
throw new Exception("No fue posible conectarse con el servidor remoto, " + ex.Message, ex);
}
}
else
{
try
{
switch (nType)
{
case DataBaseType.Postgres:
return new PostgresDataBase(GetInnerConnectionString(nConnectionString));
case DataBaseType.SqlServer:
return new SqlServerDataBase(GetInnerConnectionString(nConnectionString));
case DataBaseType.Oracle:
return new OracleDataBase(GetInnerConnectionString(nConnectionString));
default:
throw new Exception("Dase de datos no permitida " + nType.ToString());
}
}
catch (Exception ex)
{
throw new Exception("No fue posible inicializar el control de base de datos, " + ex.Message, ex);
}
}
}
public static DataBase CreateDatabase(DataBaseType nType, string nConnectionString)
{
string remotingString = GetRemotingUrl(nConnectionString);
if (remotingString != "")
{
try
{
string remotingPassword = GetRemotingPassword(nConnectionString);
bool remotingTrusted = GetRemotingTrusted(nConnectionString);
//Inicializar remoting
TcpChannel CanalTCP = ChannelServices.GetChannel("tcp") as TcpChannel;
if (CanalTCP != null)
{
if (CanalTCP.IsSecured != remotingTrusted)
{
try { ChannelServices.UnregisterChannel(CanalTCP); }
catch { }
RegisterChannel(remotingTrusted);
}
}
else
{
RegisterChannel(remotingTrusted);
}
// "tcp://localhost:8085/nAppName"
var instance = (DataBaseProxy)Activator.GetObject(typeof(DataBaseProxy), remotingString);
var ServerPublicKey = instance.getServerPublicKey();
var remotingEncriptedPassword = Crypto.RSAEncryptText(remotingPassword, ServerPublicKey);
DataBase db;
string Message;
if (!instance.CreateDataBase(nType, nConnectionString, remotingEncriptedPassword, out db, out Message))
{
throw new Exception(Message);
}
return(db);
}
catch (Exception ex)
{
throw new Exception("No fue posible conectarse con el servidor remoto, " + ex.Message, ex);
}
}
else
{
try
{
switch (nType)
{
case DataBaseType.Postgres:
return(new PostgresDataBase(GetInnerConnectionString(nConnectionString)));
case DataBaseType.SqlServer:
return(new SqlServerDataBase(GetInnerConnectionString(nConnectionString)));
case DataBaseType.Oracle:
return(new OracleDataBase(GetInnerConnectionString(nConnectionString)));
default:
throw new Exception("Dase de datos no permitida " + nType.ToString());
}
}
catch (Exception ex)
{
throw new Exception("No fue posible inicializar el control de base de datos, " + ex.Message, ex);
}
}
}
private void tsbImport_Click(object sender, EventArgs e)
{
try
{
int iDbType = int.Parse(cbbDbType.SelectedValue.ToString());
_selectDBType = (DataBaseType)iDbType;
_dicQuery[_strTableName] = DBToolStaticString.DataGenerate_Table + " AND 数据库类型 = '" + _selectDBType.ToString() + "' order by [序号]";
_dicQuery[_strColName] = DBToolStaticString.DataGenerate_Column;
dsExcel = ExportHelper.GetExcelData(_dicQuery, out _DBConnString);
if (dsExcel != null)
{
bsTable.DataSource = dsExcel.Tables[_strTableName];
bsCos.DataSource = dsExcel.Tables[_strColName];
dgvTableList.DataSource = bsTable;
dgvColList.DataSource = bsCos;
//初始化变量
//MsgHelper.ShowInfo("导入成功！");
lblInfo.Text = _strImportSuccess;
tsbAutoSQL.Enabled = true;
_importDBType = _selectDBType;
}
}
catch (Exception ex)
{
MsgHelper.ShowErr(ex.Message);
}
}
public static DataType ParseDbDataType(DataBaseType dbType, string dataType)
{
return(GetDataType(dbType.ToString().ToLower(), dataType));
}
/// <summary>
/// 构造函数初始化连接对象
/// </summary>
/// <param name="type">数据库类型</param>
public DbHelper(DataBaseType type)
{
ConnectionSettings = ConfigurationManager.ConnectionStrings[type.ToString() + "ConnectionString"];
connection = this.CreateConnection();
}
/// <summary>
/// 获取IDatabase实现(重载)。
/// </summary>
/// <param name="connString">连接名。</param>
/// <param name="dbType">数据库类型。</param>
/// <returns>IDataBase。</returns>
public static IDataBase GetIDatabase(string connString, DataBaseType dbType)
{
return(GetIDatabaseByIoc(dbType.ToString(), connString));
}
/// <summary>
/// 创建IDB对象
/// </summary>
/// <param name="connStr">
/// <para>连接字符串:</para>
/// <para>SQLSERVER: Data Source=.;Initial Catalog=JACKOA;User ID=sa;Password=xx;</para>
/// <para>ORACLE: Data Source=ORCLmyvm2;Password=sys123;User ID=sys;DBA Privilege=SYSDBA;</para>
/// <para>MYSQL: Data Source=localhost;Initial Catalog=test;User ID=root;Password=xxxx;</para>
/// <para>POSTGRESQL: Server=localhost;Port=5432;UserId=postgres;Password=xxxx;Database=test</para>
/// <para>ACCESS: Provider=Microsoft.Jet.OLEDB.4.0;Data Source=G:\work\Multiplan.mdb;</para>
/// <para>ACCESS: Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Administrator\Desktop\demo.accdb;</para>
/// <para>SQLITE: Data Source=f:\demo.db;</para>
/// </param>
/// <param name="DBType">数据库类型:SQLSERVER、ORACLE、MYSQL、SQLITE、ACCESS、POSTGRESQL</param>
/// <returns></returns>
public static IDbAccess CreateIDB(string connStr, DataBaseType DBType)
{
string dbtype = DBType.ToString();
return(CreateIDB(connStr, dbtype));
}
public static string GetSql(DataBaseType dalType, string version, int pageIndex, int pageSize, object objWhere, string tableName, int rowCount, string columns, string primaryKey, bool primaryKeyIsidentity)
{
if (string.IsNullOrEmpty(columns))
{
columns = "*";
}
pageIndex = pageIndex == 0 ? 1 : pageIndex;
string where = SqlFormat.GetIFieldSql(objWhere);
if (string.IsNullOrEmpty(where))
{
where = "1=1";
}
if (pageSize == 0)
{
return(string.Format(top1Pager, columns, tableName, where));
}
if (rowCount > 0)//分页查询。
{
where = SqlCreate.AddOrderBy(where, primaryKey);
}
int topN = pageIndex * pageSize;//Top N 最大数
int max = (pageIndex - 1) * pageSize;
int rowStart = (pageIndex - 1) * pageSize + 1;
int rowEnd = rowStart + pageSize - 1;
string orderBy = string.Empty;
if (pageIndex == 1 && dalType != DataBaseType.Oracle)//第一页（oracle时 rownum 在排序条件为非数字时，和row_number()的不一样，会导致结果差异，所以分页统一用row_number()。）
{
switch (dalType)
{
case DataBaseType.Access:
case DataBaseType.MsSql:
case DataBaseType.Sybase:
case DataBaseType.Txt:
case DataBaseType.Xml:
return(string.Format(top1Pager, "top " + pageSize + " " + columns, tableName, where));
//case DalType.Oracle:
// return string.Format(top1Pager, columns, tableName, "rownum<=" + pageSize + " and " + where);
case DataBaseType.SQLite:
case DataBaseType.MySql:
case DataBaseType.PostgreSQL:
return(string.Format(top1Pager, columns, tableName, where + " limit " + pageSize));
}
}
else
{
switch (dalType)
{
case DataBaseType.Access:
case DataBaseType.MsSql:
case DataBaseType.Sybase:
int leftNum = rowCount % pageSize;
int pageCount = leftNum == 0 ? rowCount / pageSize : rowCount / pageSize + 1; //页数
if (pageIndex == pageCount && dalType != DataBaseType.Sybase) // 最后一页Sybase 不支持双Top order by
{
return(string.Format(top2Pager, pageSize + " " + columns, "top " + (leftNum == 0 ? pageSize : leftNum) + " * ", tableName, ReverseOrderBy(where, primaryKey), GetOrderBy(where, false, primaryKey))); //反序
}
if ((pageCount > 1000 || rowCount > 100000) && pageIndex > pageCount / 2) // 页数过后半段，反转查询
{
orderBy = GetOrderBy(where, false, primaryKey);
if (dalType != DataBaseType.MsSql) //mssql是用rownumber，不用反转，
{
where = ReverseOrderBy(where, primaryKey); //事先反转一次。
}
topN = rowCount - max; //取后面的
int rowStartTemp = rowCount - rowEnd;
rowEnd = rowCount - rowStart + 1; //网友反馈修正（数据行要+1）
rowStart = rowStartTemp + 1; //网友反馈修正（数据行要+1）
}
break;
case DataBaseType.Txt:
case DataBaseType.Xml:
return(string.Format(top1Pager, columns, tableName, where + " limit " + pageSize + " offset " + pageIndex));
}
}
switch (dalType)
{
case DataBaseType.MsSql:
case DataBaseType.Oracle:
if (version.StartsWith("08"))
{
goto temtable;
// goto top3;//sql 2000
}
int index = tableName.LastIndexOf(')');
if (index > 0)
{
tableName = tableName.Substring(0, index + 1);
}
string v = dalType == DataBaseType.Oracle ? "" : " v";
string onlyWhere = "where " + SqlCreate.RemoveOrderBy(where);
onlyWhere = SqlFormat.RemoveWhereOneEqualsOne(onlyWhere);
return(string.Format(rowNumberPager, GetOrderBy(where, false, primaryKey), (columns == "*" ? "t.*" : columns), tableName, onlyWhere, v, rowStart, rowEnd));
case DataBaseType.Sybase:
temtable:
if (primaryKeyIsidentity)
{
bool isOk = columns == "*";
if (!isOk)
{
string kv = SqlFormat.NotKeyword(primaryKey);
string[] items = columns.Split(',');
foreach (string item in items)
{
if (string.Compare(SqlFormat.NotKeyword(item), kv, StringComparison.OrdinalIgnoreCase) == 0)
{
isOk = true;
break;
}
}
}
else
{
columns = "t.*";
index = tableName.LastIndexOf(')');
if (index > 0)
{
tableName = tableName.Substring(0, index + 1);
}
tableName += " t ";
}
if (isOk)
{
return(string.Format(tempTablePagerWithidentity, DateTime.Now.Millisecond, topN, primaryKey, tableName, where, pageSize, columns, rowStart, rowEnd, orderBy));
}
}
return(string.Format(tempTablePager, DateTime.Now.Millisecond, pageIndex * pageSize + " " + columns, tableName, where, pageSize, rowStart, rowEnd, orderBy));
case DataBaseType.Access:
top3:
if (!string.IsNullOrEmpty(orderBy)) // 反转查询
{
return(string.Format(top4Pager, columns, (rowCount - max > pageSize ? pageSize : rowCount - max), topN, tableName, where, GetOrderBy(where, true, primaryKey), GetOrderBy(where, false, primaryKey), orderBy));
}
return(string.Format(top3Pager, (rowCount - max > pageSize ? pageSize : rowCount - max), columns, topN, tableName, where, GetOrderBy(where, true, primaryKey), GetOrderBy(where, false, primaryKey)));
case DataBaseType.SQLite:
case DataBaseType.MySql:
case DataBaseType.PostgreSQL:
if (max > 500000 && primaryKeyIsidentity && Convert.ToString(objWhere) == "" && !tableName.Contains(" ")) //单表大数量时的优化成主键访问。
{
where = string.Format("{0}>=(select {0} from {1} limit {2}, 1) limit {3}", primaryKey, tableName, max, pageSize);
return(string.Format(top1Pager, columns, tableName, where));
}
return(string.Format(top1Pager, columns, tableName, where + " limit " + pageSize + " offset " + max));
}
return((string)Error.Throw("Pager::No Be Support:" + dalType.ToString()));
}
/// <summary>
/// SQL 分页查询 SQL 语句
/// </summary>
/// <param name="querySQL">SQL 语句，注意不带排序字段</param>
/// <param name="orderby">排序字段，注意：不要带 order by 关键字，如: order1 desc,order2 asc</param>
/// <param name="pageData">分页数据</param>
/// <returns>返回整理过的分页 SQL 语句</returns>
protected override string GetPagedQuerySQL(string querySQL, string orderby, PagedData pageData)
{
if (querySQL.IsEmpty())
{
throw new Exception("参数 querySQL 不能为空！");
}
if (orderby.IsEmpty())
{
throw new Exception("参数 orderby 不能为空！");
}
if (pageData == null)
{
throw new Exception("参数 pageData 不能为空！");
}
if (pageData.RecordCount == 0)
{
return(querySQL);
}
switch (MyDataBaseType)
{
case DataBaseType.SQLSERVER2000:
{
const string FirstPagedQuerySQLText = "select top {0} * from ({1}) as ___table0 order by {2}";
const string PagedQuerySQLText = "select * from (select top {0} * from (select top {1} * from ({2}) as ___table0 order by {3}) as ___table1 order by {4}) as ___table2 order by {3}";
if (pageData.IsFirstPage)
{
return(string.Format(FirstPagedQuerySQLText, pageData.RecordCountInPage, querySQL, orderby));
}
// 定义一个配置数组用来填充查询语句5748
object[] configFields = new object[5];
configFields[0] = pageData.RecordCountInPage;
configFields[1] = pageData.LastIndexInPage + 1;
configFields[2] = querySQL;
configFields[3] = orderby;
configFields[4] = this.GetOrderbyDesc(orderby); // 和 orderby 的排序相反的排序字段字符串
// 填充查询语句
return(string.Format(PagedQuerySQLText, configFields));
}
case DataBaseType.SQLSERVER2005:
case DataBaseType.SQLSERVER2008:
{
//对sql2005, sql2008，换成下面一种写法，以提高查询效率
const string FirstPagedQuerySQLText = "select top {0} * from ({1}) as ___table0 order by {2}";
const string PagedQuerySQLText = @"select top {0} * from
(select row_number() over(order by {1}) as ___rownumber,* from ({2}) as ___table0) as ___table1
where ___rownumber>={3} order by ___rownumber";
if (pageData.IsFirstPage)
{
//return string.Format(FirstPagedQuerySQLText, pageData.RecordCountInPage, querySQL, orderby);
/* 上面这个写法，当pageData.RecordCountInPage小于10，且orderby中有排序要求时，查询会比较慢（可能是sqlserver2008的一个bug）；
* 故改成了下面这个写法，这时，在业务程序中应注意设置PageSize大于等于10。缪卫华 2015-07-31
*/
return(string.Format(FirstPagedQuerySQLText, pageData.PageSize, querySQL, orderby));
}
// 定义一个配置数组用来填充查询语句
object[] configFields = new object[5];
configFields[0] = pageData.RecordCountInPage; //top
configFields[1] = orderby; //orderby
configFields[2] = querySQL; //table
configFields[3] = pageData.FirstIndexInPage + 1; //firstindexinpage
// 填充查询语句
return(string.Format(PagedQuerySQLText, configFields));
}
default:
throw new Exception("不正确的数据库类型" + MyDataBaseType.ToString());
}
}
