using MySql.Data.MySqlClient;
|
using System;
|
using System.Collections;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Text;
|
|
namespace DbAccess
|
{
|
public class MySqlHelper : ISysSqlHelper
|
{
|
private string connectionString = "";
|
public MySqlHelper()
|
{
|
connectionString = Encrypt.DecryptDES_DB(DbInfo.GetDataBaseInfo("Main"));
|
}
|
|
|
public MySqlHelper(string rdsType)
|
{
|
try
|
{
|
connectionString = DbInfo.GetDataBaseInfo(rdsType);
|
connectionString = Encrypt.DecryptDES_DB(connectionString);
|
}
|
catch (Exception ex)
|
{
|
|
}
|
|
}
|
|
|
/// <summary>
|
/// 执行查询语句，返回DataSet
|
/// </summary>
|
/// <param name="SQLString"></param>
|
/// <returns></returns>
|
public DataSet ExecuteDataSet(string SQLString)
|
{
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
{
|
using (MySqlCommand cmd = new MySqlCommand())
|
{
|
cmd.Connection = connection;
|
cmd.CommandText = SQLString;
|
cmd.CommandTimeout = 10000;
|
DataSet ds = new DataSet();
|
try
|
{
|
DateTime time = DateTime.Now;
|
MySqlDataAdapter da = new MySqlDataAdapter(cmd);
|
da.Fill(ds);
|
}
|
catch (MySqlException ex)
|
{
|
throw new Exception(ex.Message);
|
}
|
finally
|
{
|
cmd.Clone();
|
connection.Close();
|
}
|
return ds;
|
}
|
}
|
}
|
|
|
/// <summary>
|
/// 执行查询语句，返回DataTable
|
/// </summary>
|
/// <param name="SQLString"></param>
|
/// <returns></returns>
|
public DataTable ExecuteDataTable(string SQLString)
|
{
|
DateTime time = DateTime.Now;
|
DataSet ds = ExecuteDataSet(SQLString);
|
if (ds.Tables.Count > 0)
|
{
|
return ds.Tables[0];
|
}
|
else
|
{
|
return null;
|
}
|
}
|
|
/// <summary>
|
/// 返回数据表(分页)
|
/// </summary>
|
/// <param name="page"></param>
|
/// <param name="pageSize"></param>
|
/// <param name="recordCount"></param>
|
/// <param name="strSql"></param>
|
/// <returns></returns>
|
public DataTable ExecuteDataTable(int page, int pageSize, ref int recordCount, string strSql)
|
{
|
DateTime time = DateTime.Now;
|
DataTable dt = new DataTable();
|
//获取总记录数
|
recordCount = Convert.ToInt32(this.ExecuteScalar("select count(1) from (" + strSql + ") as t"));
|
StringBuilder sb = new StringBuilder();
|
int beg = (page - 1) * pageSize;
|
sb.Append(strSql + " limit " + beg.ToString() + "," + pageSize);
|
dt = this.ExecuteDataTable(sb.ToString());
|
return dt;
|
}
|
|
/// <summary>
|
/// 返回数据表(分页) add zhangjun 2014-12-19 优化大数据分页，计算总记录数的sql
|
/// </summary>
|
/// <param name="page"></param>
|
/// <param name="pageSize"></param>
|
/// <param name="recordCount"></param>
|
/// <param name="strSql"></param>
|
/// <param name="tableName"></param>
|
/// <param name="strWhere"></param>
|
/// <returns></returns>
|
public DataTable ExecuteDataTable(int page, int pageSize, ref int recordCount, string strSql, string tableName, string strWhere)
|
{
|
DateTime time = DateTime.Now;
|
DataTable dt = new DataTable();
|
//获取总记录数
|
recordCount = Convert.ToInt32(this.ExecuteScalar("select count(*) from " + tableName + " " + strWhere));
|
StringBuilder sb = new StringBuilder();
|
int beg = (page - 1) * pageSize;
|
sb.Append(strSql + " limit " + beg.ToString() + "," + pageSize);
|
dt = this.ExecuteDataTable(sb.ToString());
|
return dt;
|
}
|
|
/// <summary>
|
/// 返回查询的所有结果，突破rds的1万条的限制
|
/// </summary>
|
/// <param name="strSql"></param>
|
/// <returns></returns>
|
public DataTable ExecuteDataTableAll(string strSql)
|
{
|
DateTime time = DateTime.Now;
|
int recordCount = Convert.ToInt32(this.ExecuteScalar("select count(1) from (" + strSql + ") as t"));
|
//如果记录总数
|
if (recordCount < 10001)
|
{
|
return this.ExecuteDataTable(strSql);
|
}
|
//如果超过1万条记录，分次从rds提取
|
DataTable dt = new DataTable();
|
int mi = recordCount % 10000;
|
int pi = recordCount / 10000;
|
if (mi > 0)
|
{
|
pi = pi + 1;
|
}
|
for (int i = 1; i < pi + 1; i++)
|
{
|
DataTable cdt = this.ExecuteDataTable(i, 10000, ref recordCount, strSql);
|
if (dt.Rows.Count == 0)
|
{
|
dt = cdt.Clone();
|
}
|
dt.Merge(cdt);
|
dt.AcceptChanges();
|
}
|
return dt;
|
}
|
|
|
/// <summary>
|
/// 执行SQL语句，返回影响的记录数
|
/// </summary>
|
/// <param name="SQLString"></param>
|
/// <returns></returns>
|
public int ExecuteNonQuery(string SQLString)
|
{
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
{
|
using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
|
{
|
try
|
{
|
DateTime time = DateTime.Now;
|
connection.Open();
|
cmd.CommandTimeout = 10000;
|
int rows = cmd.ExecuteNonQuery();
|
return rows;
|
}
|
catch (MySqlException E)
|
{
|
if (E.ToString().Contains("Deadlock"))
|
{
|
//WriteLine("物流签收锁定日志跟踪", E.ToString() + "_____" + SQLString);
|
}
|
throw new Exception(E.Message);
|
}
|
finally
|
{
|
connection.Close();
|
}
|
}
|
}
|
}
|
|
/// <summary>
|
/// 执行一条计算查询结果语句，返回查询结果（object）。
|
/// </summary>
|
/// <param name="SQLString"></param>
|
/// <returns></returns>
|
public object ExecuteScalar(string SQLString)
|
{
|
using (MySqlConnection connection = new MySqlConnection(connectionString))
|
{
|
using (MySqlCommand cmd = new MySqlCommand(SQLString, connection))
|
{
|
try
|
{
|
DateTime time = DateTime.Now;
|
|
connection.Open();
|
cmd.CommandTimeout = 10000;
|
object obj = cmd.ExecuteScalar();
|
if ((Object.Equals(obj, null)) || (Object.Equals(obj, System.DBNull.Value)))
|
{
|
return null;
|
}
|
else
|
{
|
return obj;
|
}
|
}
|
catch (MySqlException e)
|
{
|
throw new Exception(e.Message);
|
}
|
finally
|
{
|
cmd.Clone();
|
connection.Close();
|
}
|
}
|
}
|
}
|
|
/// <summary>
|
/// 执行多条SQL语句，实现数据库事务。
|
/// </summary>
|
/// <param name="SQLStringList"></param>
|
public void ExecuteSqlTran(ArrayList SQLStringList)
|
{
|
using (MySqlConnection conn = new MySqlConnection(connectionString))
|
{
|
conn.Open();
|
MySqlCommand cmd = new MySqlCommand();
|
cmd.Connection = conn;
|
MySqlTransaction tx = conn.BeginTransaction();
|
cmd.Transaction = tx;
|
try
|
{
|
for (int n = 0; n < SQLStringList.Count; n++)
|
{
|
string strsql = SQLStringList[n].ToString();
|
if (strsql.Trim().Length > 1)
|
{
|
cmd.CommandText = strsql;
|
cmd.CommandTimeout = 10000;
|
cmd.ExecuteNonQuery();
|
}
|
}
|
tx.Commit();
|
}
|
catch (MySqlException E)
|
{
|
tx.Rollback();
|
throw new Exception(E.Message);
|
}
|
finally
|
{
|
conn.Close();
|
cmd.Dispose();
|
}
|
}
|
}
|
|
public string GetConnectString()
|
{
|
return connectionString;
|
}
|
}
|
}
|
