private void buttonPage_Click(object sender, EventArgs e)
{
try
{
labelInfo.Text = "";
TimeSpan ts = new TimeSpan();
string dbType = comboBoxDbType.Text;
string sql = textBoxSql.Text;
int pageSize = (int)numericUpDownPageSize.Value;
int pageIndex = (int)numericUpDownPageIndex.Value;
int start = (pageIndex - 1) * pageSize + 1;
DataTable dt = new DataTable();
using (BDBHelper dbHelper = new BDBHelper(dbType, textBoxConn.Text))
{
DateTime begin = DateTime.Now;
dt = dbHelper.ExecuteDataTablePage(sql, pageSize, pageIndex);
ts = DateTime.Now - begin;
}
dataGridView1.DataSource = dt;
labelTime.Text = ts.TotalMilliseconds + "毫秒";
labelInfo.Text = "返回时间：" + DateTime.Now.ToString();
}
catch (Exception ex)
{
labelInfo.Text = ex.Message;
}
}
/// <summary>
/// 查询报表并返回结果集
/// </summary>
/// <param name="entity"></param>
/// <param name="generatedSQL">解析后的SQL语句</param>
/// <param name="paramList">参数列表</param>
/// <param name="pageSize"></param>
/// <param name="pageIndex"></param>
/// <returns></returns>
public static DataTable QueryTable(Entity entity, string generatedSQL, List <object> paramList, int pageSize, int pageIndex)
{
if (entity == null || string.IsNullOrWhiteSpace(generatedSQL))
{
return(new DataTable());
}
string sql = generatedSQL;
if (entity.DB_ID <= 0)
{
using (BDBHelper dbHelper = new BDBHelper())
{
if (paramList == null || paramList.Count < 1)
{
if (pageSize < 1)
{
return(dbHelper.ExecuteDataTable(sql));
}
return(dbHelper.ExecuteDataTablePage(sql, pageSize, pageIndex));
}
if (pageSize < 1)
{
return(dbHelper.ExecuteDataTableParams(sql, paramList));
}
return(dbHelper.ExecuteDataTablePageParams(sql, pageSize, pageIndex, paramList));
}
}
else
{
BF_DATABASE.Entity db = BF_DATABASE.Instance.GetEntityByKey <BF_DATABASE.Entity>(entity.DB_ID);
if (db == null)
{
throw new Exception("数据库不存在");
}
string dbType = Enums.DBType.Oracle.ToString();
try
{
dbType = ((Enums.DBType)db.DB_TYPE).ToString();
}
catch
{
throw new Exception("未知的数据库类型");
}
using (BDBHelper dbHelper = new BDBHelper(dbType, db.IP, db.PORT, db.USER_NAME, db.PASSWORD, db.DB_NAME, db.DB_NAME))
{
if (paramList == null || paramList.Count < 1)
{
if (pageSize < 1)
{
return(dbHelper.ExecuteDataTable(sql));
}
return(dbHelper.ExecuteDataTablePage(sql, pageSize, pageIndex));
}
if (pageSize < 1)
{
return(dbHelper.ExecuteDataTableParams(sql, paramList));
}
return(dbHelper.ExecuteDataTablePageParams(sql, pageSize, pageIndex, paramList));
}
}
}
/// <summary>
/// 在指定数据库执行查询语句
/// </summary>
/// <param name="dbID">数据库ID</param>
/// <param name="sql">SQL语句</param>
/// <param name="paramList">参数列表</param>
/// <param name="rowsCount">记录数（如果传入值等于0，则会重新计算此值，反之不计算）</param>
/// <param name="pageSize">分页大小（默认为10，0表示不分页）</param>
/// <param name="pageIndex">页号（从1开始）</param>
/// <returns>DataTable</returns>
public DataTable ExecuteSelectSQL(int dbID, string sql, List <object> paramList, ref int rowsCount, int pageSize = 10, int pageIndex = 1)
{
if (dbID < 0)
{
throw new Exception("错误的数据库ID");
}
if (string.IsNullOrWhiteSpace(sql))
{
throw new Exception("SQL语句不可为空");
}
string s = sql.Trim();
if (s.ToUpper().StartsWith("SELECT ") == false)
{
throw new Exception("只能执行SLECT语句！");
}
DataTable dt = null;
using (BDBHelper dbHelper = GetBDBHelper(dbID))
{
try
{
if (pageSize <= 0)
{
if (paramList == null || paramList.Count < 1)
{
dt = dbHelper.ExecuteDataTable(sql);
}
else
{
dt = dbHelper.ExecuteDataTableParams(sql, paramList);
}
if (dt != null)
{
rowsCount = dt.Rows.Count;
}
}
else
{
if (paramList == null || paramList.Count < 1)
{
dt = dbHelper.ExecuteDataTablePage(sql, pageSize, pageIndex);
}
else
{
dt = dbHelper.ExecuteDataTablePageParams(sql, pageSize, pageIndex, paramList);
}
if (dt != null && dt.Rows.Count < pageSize && pageIndex <= 1)
{
rowsCount = dt.Rows.Count;
}
else if (rowsCount == 0)
{
if (paramList == null || paramList.Count < 1)
{
rowsCount = dbHelper.ExecuteScalarInt("SELECT COUNT(*) C FROM (" + sql + ")");
}
else
{
rowsCount = dbHelper.ExecuteScalarIntParams("SELECT COUNT(*) C FROM (" + sql + ")", paramList);
}
}
}
}
catch (Exception ex)
{
throw new Exception("在数据库" + dbID + "执行SQL查询出错：" + ex.Message);
}
}
if (pageIndex <= 1 && (dt == null || dt.Rows.Count == 0))
{
rowsCount = 0;
}
return(dt);
}
