/// <summary>
/// 数据库查询
/// </summary>
/// <param name="sql"></param>
/// <returns>结果集</returns>
public static DataTable Select(string sql)
{
MySqlConnection Conn = dbConn;
DataTable dtResult = null;
MySqlCommand Cmd = null;
MySqlDataAdapter Adapt = null;
DataSet Ds = null;
for (int i = 0; i <= ConnTimes; i++)
{
try
{
Cmd = new MySqlCommand(sql, Conn);
Cmd.CommandType = CommandType.Text;
Adapt = new MySqlDataAdapter(Cmd);
Ds = new DataSet();
Adapt.Fill(Ds);
dtResult = Ds.Tables[0];
break;
}
catch (MySqlException e)
{
string LogCont = "错误信息：" + e.Message.Trim() + "，错误语句：" + sql + "，错误时间：" + System.DateTime.Now.ToString().Trim() + "\r\n";
DatabaseLog.WriteLog(LogCont);
if (MessageBox.Show("当前数据库连接失败，您是否尝试重新连接？\r\n 点击“是”继续连接；点击“否”退出系统。", "", MessageBoxButtons.YesNo) == DialogResult.No)
{
Environment.Exit(0);
}
else
{
if (i >= ConnTimes)
{
MessageBox.Show("已经超过最大连接次数，点击确定后退出！");
Environment.Exit(0);
}
else
{
Connect();
}
}
}
finally
{
if (Cmd != null)
{
Cmd.Dispose();
}
if (Adapt != null)
{
Adapt.Dispose();
}
if (Ds != null)
{
Ds.Dispose();
}
}
}
return(dtResult);
}
public static DataTable Select(string sql)
{
OleDbConnection conn = dbConn;
DataTable dtResult = null;
OleDbDataAdapter adapt = null;
DataSet ds = null;
for (int i = 0; i <= ConnTimes; i++)
{
try
{
adapt = new OleDbDataAdapter(sql, conn);
ds = new DataSet();
adapt.Fill(ds);
dtResult = ds.Tables[0];
break;
}
catch (SqlException e)
{
string LogCont = "错误信息：" + e.Message.Trim() + "，错误语句：" + sql + "，错误时间：" + System.DateTime.Now.ToString().Trim() + "\r\n";
DatabaseLog.WriteLog(LogCont);
if (MessageBox.Show("当前数据库连接失败，您是否尝试重新连接？\r\n 点击“是”继续连接；点击“否”退出系统。", "", MessageBoxButtons.YesNo) == DialogResult.No)
{
Environment.Exit(0);
}
else
{
if (i >= ConnTimes)
{
MessageBox.Show("已经超过最大连接次数，点击确定后退出！");
Environment.Exit(0);
}
else
{
Database.Connect();
}
}
}
finally
{
if (adapt != null)
{
adapt.Dispose();
}
if (ds != null)
{
ds.Dispose();
}
}
}
return(dtResult);
}
public static void ExecuteProc(string sql, params MySqlParameter[] cmdParms)
{
MySqlConnection Conn = dbConn;
int iResult = -1;
MySqlCommand Cmd = null;
for (int i = 0; i <= ConnTimes; i++)
{
try
{
Cmd = new MySqlCommand(sql, Conn);
if (cmdParms != null && cmdParms.Length > 0)
{
Cmd.Parameters.AddRange(cmdParms);
}
Cmd.CommandType = CommandType.StoredProcedure;
iResult = Cmd.ExecuteNonQuery();
Cmd.Parameters.Clear();
break;
}
catch (MySqlException e)
{
string LogCont = "【错误】：" + "存储过程：" + sql + ",错误信息:" + e.Message.Trim() + "，错误语句：" + sql + "，错误时间：" + System.DateTime.Now.ToString().Trim() + "\r\n";
DatabaseLog.WriteLog(LogCont);
if (MessageBox.Show("当前数据库连接失败，您是否尝试重新连接？\r\n 点击“是”继续连接；点击“否”退出系统。", "", MessageBoxButtons.YesNo) == DialogResult.No)
{
Environment.Exit(0);
}
else
{
if (i >= ConnTimes)
{
MessageBox.Show("已经超过最大连接次数，点击确定后退出！");
Environment.Exit(0);
}
else
{
Connect();
}
}
}
finally
{
if (Cmd != null)
{
Cmd.Dispose();
}
}
}
}
//批量插入语句
//MySQL
public static int BatchInsertSql(string insertSql, List <string> values, int batchSize = 500)
{
int iResult = 0;
using (MySqlConnection conn = new MySqlConnection(ConnectionString))
{
conn.Open();
if (conn.State != ConnectionState.Open)
{
return(-1);
}
string sql = "";
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = conn;
MySqlTransaction tx = null;
//计算批量提交次数
int commitCount = 1;
if (values.Count % batchSize > 0)
{
commitCount = values.Count / batchSize + 1;
}
else
{
commitCount = values.Count / batchSize;
}
try
{
for (int i = 0; i < commitCount; i++)
{
tx = conn.BeginTransaction();
cmd.Transaction = tx;
sql = GetBatchInsertSql(insertSql, values, i * batchSize, batchSize);
cmd.CommandText = sql;
iResult += cmd.ExecuteNonQuery();
tx.Commit();
}
}
catch (System.Data.SqlClient.SqlException e)
{
tx.Rollback();
string LogCont = "错误信息：" + e.Message.Trim() + "，错误语句：" + cmd.CommandText + "，错误时间：" + System.DateTime.Now.ToString().Trim() + "\r\n";
DatabaseLog.WriteLog(LogCont);
}
}
return(iResult);
}
/// <summary>
/// 更新数据库操作，包括删除、修改与添加
/// </summary>
public static int ExecuteSQL(string sql, string modelForm = "")
{
MySqlConnection Conn = dbConn;
int iResult = -1;
MySqlCommand Cmd = null;
for (int i = 0; i <= ConnTimes; i++)
{
try
{
Cmd = new MySqlCommand(sql, Conn);
Cmd.CommandType = CommandType.Text;
iResult = Cmd.ExecuteNonQuery();
string LogCont = "【正常】：" + "模块名称：" + modelForm + "，执行语句：" + sql + "，执行时间：" + System.DateTime.Now.ToString().Trim() + "\r\n";
DatabaseLog.WriteLog(LogCont);
break;
}
catch (MySqlException e)
{
string LogCont = "【错误】：" + "模块名称：" + modelForm + ",错误信息:" + e.Message.Trim() + "，错误语句：" + sql + "，错误时间：" + System.DateTime.Now.ToString().Trim() + "\r\n";
DatabaseLog.WriteLog(LogCont);
if (MessageBox.Show("当前数据库连接失败，您是否尝试重新连接？\r\n 点击“是”继续连接；点击“否”退出系统。", "", MessageBoxButtons.YesNo) == DialogResult.No)
{
Environment.Exit(0);
}
else
{
if (i >= ConnTimes)
{
MessageBox.Show("已经超过最大连接次数，点击确定后退出！");
Environment.Exit(0);
}
else
{
Connect();
}
}
}
finally
{
if (Cmd != null)
{
Cmd.Dispose();
}
}
}
return(iResult);
}
public static bool Connect()
{
if (dbConn == null)
{
dbConn = new MySqlConnection();
}
string strCon = String.Format("Database='{0}';Data Source='{1}'; Port = '{2}';User Id='{3}';Password='******';charset='{5}';pooling=true ",//;Persist Security Info=True
DbName,
Host,
Port,
User,
Psw,
CharSet);
MySqlConnection conn = dbConn;
try
{
if (conn.State != ConnectionState.Closed)
{
conn.Close();
}
conn.ConnectionString = strCon;
conn.Open();
if (conn.State == ConnectionState.Open)
{
return(true);
}
else
{
return(false);
}
}
catch (MySqlException e)
{
string LogCont = "错误信息：" + e.Message.Trim() + "，错误时间：" + System.DateTime.Now.ToString().Trim() + "\r\n";
DatabaseLog.WriteLog(LogCont);
return(false);
}
}