using System; using System.Data; using System.Configuration; using System.Web; using System.Web.Security; using System.Web.UI; using System.Web.UI.WebControls; using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls; using MySql.Data.Types; using MySql.Data.MySqlClient; /// <summary> /// C#操作mysql基类 /// </summary> public class MySqlobj { private MySqlConnection conn; private MySqlCommand com; private bool _alreadyDispose = false; #region 构造与柝构 public MySqlobj() { try { conn = new MySqlConnection(ConfigurationManager.AppSettings["mysqlconn"]); conn.Open(); com = new MySqlCommand(); com.Connection = conn; } catch (Exception ee) { throw new Exception("连接数据库出错"); } } ~MySqlobj() { Dispose(); } protected virtual void Dispose(bool isDisposing) { if (_alreadyDispose) return; if (isDisposing) { // TODO: 此处释放受控资源 if (com != null) { com.Dispose(); } if (conn != null) { try { conn.Close(); conn.Dispose(); } catch (Exception ee) { } finally { conn = null; } } } // TODO: 此处释放非受控资源。设置被处理过标记 _alreadyDispose = true; } #endregion #region IDisposable 成员 public void Dispose() { Dispose(true); GC.SuppressFinalize(this); } #endregion #region 获取DataSet public DataSet GetDataSet(string sqlString) { DataSet ds = new DataSet(); try { MySqlDataAdapter da = new MySqlDataAdapter(sqlString, conn); da.Fill(ds); } catch (Exception ee) { throw new Exception("SQL:" + sqlString + "<br />" + ee.Message.ToString()); } return ds; } #endregion #region 执行插入或删除操作 public void ExecuteNonQuery(string sqlString) { int ret = 0; com.CommandText = sqlString; com.CommandType = CommandType.Text; try { ret = com.ExecuteNonQuery(); } catch (Exception ee) { throw new Exception("SQL:" + sqlString + "<br />" + ee.Message.ToString()); } finally { com.Dispose(); } } #endregion } 另一个类 using System; using System.Collections.Generic; using System.Text; using System.Data; using MySql.Data.MySqlClient; namespace DevSDK.Data { public class MySQL { private string _ConnectionString = ""; private string _CommandText = ""; private MySqlConnection Conn = null; private DataSet _DSAFDataSet = null; /// <summary> /// 获取或设置数据库连接字串 /// </summary> public string ConnectionString { get { return this._ConnectionString; } set { this._ConnectionString = value; } } /// <summary> /// 获取或设置SQL语句 /// </summary> public string CommandText { get { return this._CommandText; } set { this._CommandText = value; } } public MySQL() { this._DSAFDataSet = new DataSet(); } /// <summary> /// 执行 SQL 语句,并把执行结果集填充到一个DataSet容器中 /// </summary> /// <returns></returns> public DataSet Execute() { if (string.IsNullOrEmpty(_ConnectionString)) { throw (new Exception("无传入数据连接字串")); } if (string.IsNullOrEmpty(_CommandText)) { throw (new Exception("无传入SQL语句")); } if (!DSAFConnectionTest()) { throw (new Exception("尝试数据库连接失败")); } try { Conn = new MySqlConnection(); Conn.ConnectionString = _ConnectionString; MySqlDataAdapter MySqlDa = new MySqlDataAdapter(); MySqlDa.SelectCommand.CommandText = _CommandText; MySqlDa.SelectCommand.Connection = Conn; MySqlDa.Fill(_DSAFDataSet); MySqlDa.Dispose(); } catch (MySqlException MySqlEx) { throw MySqlEx; } catch (Exception ex) { throw ex; } return _DSAFDataSet; } /// <summary> /// 执行 SQL 语句,并返回受影响的行数 /// </summary> /// <returns></returns> public int ExecuteNonQuery() { if (string.IsNullOrEmpty(_ConnectionString)) { throw (new Exception("无传入数据连接字串")); } if (string.IsNullOrEmpty(_CommandText)) { throw (new Exception("无传入SQL语句")); } if (!DSAFConnectionTest()) { throw (new Exception("尝试数据库连接失败")); } int iQuery = 0; MySqlTransaction Transaction = null; try { MySqlCommand Comm = new MySqlCommand(); Conn = new MySqlConnection(); Conn.ConnectionString = _ConnectionString; Comm.Connection = Conn; Comm.CommandText = _CommandText; Transaction = Conn.BeginTransaction(); Comm.Transaction = Transaction; iQuery = Comm.ExecuteNonQuery(); Transaction.Commit(); return iQuery; } catch (MySqlException MySqlEx) { Transaction.Rollback(); throw MySqlEx; } catch (Exception ex) { throw ex; } finally { Transaction.Dispose(); Conn.Close(); Conn.Dispose(); } } /// <summary> /// 执行 SQL 语句,并返回结果集中的第一行第一列。忽略其它行或列 /// </summary> /// <returns></returns> public object ExecuteScalar() { if (string.IsNullOrEmpty(_ConnectionString)) { throw (new Exception("无传入数据连接字串")); } if (string.IsNullOrEmpty(_CommandText)) { throw (new Exception("无传入SQL语句")); } if (!DSAFConnectionTest()) { throw (new Exception("尝试数据库连接失败")); } object iQuery = null; MySqlTransaction Transaction = null; try { MySqlCommand Comm = new MySqlCommand(); Conn = new MySqlConnection(); Conn.ConnectionString = _ConnectionString; Comm.Connection = Conn; Comm.CommandText = _CommandText; Transaction = Conn.BeginTransaction(); Comm.Transaction = Transaction; iQuery = Comm.ExecuteScalar(); Transaction.Commit(); return iQuery; } catch (MySqlException MySqlEx) { Transaction.Rollback(); throw MySqlEx; } catch (Exception ex) { throw ex; } finally { Transaction.Dispose(); Conn.Close(); Conn.Dispose(); } } /// <summary> /// 测试数据库连接 /// </summary> /// <returns></returns> private bool DSAFConnectionTest() { try { Conn = new MySqlConnection(); Conn.ConnectionString = _ConnectionString; Conn.Open(); Conn.Close(); } catch { return (false); } return (true); } /// <summary> /// 释放由 DevSDK.Data.MySql 使用的所有资源 /// </summary> public void Dispose() { if (Conn != null) { this.Conn.Dispose(); } if (_DSAFDataSet != null) { this._DSAFDataSet = null; } } } }