/// <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); } }