C#连接MySql服务器

C#连接MySql服务器，并进行增删改查的操作 public class MySqlDBConnector : IDisposable { private MySqlConnection _connection; private readonly object _locker = new object(); private static string _host; private static string _login; private static string _password; private static string _databaseName; private static string GetConnectionString() { if (string.IsNullOrEmpty(_host) || string.IsNullOrEmpty(_login) || string.IsNullOrEmpty(_password)) throw new CouldNotConnectToDBException("GetConnectionString(): Database connection parameters not initialized. Call firstly: DBConnector.InitializeDatabaseParameters(...)"); MySqlConnectionStringBuilder cnx = new MySqlConnectionStringBuilder { Server = _host, UserID = _login, Password = _password, //MinimumPoolSize = 10, //MaximumPoolSize = 200 }; if (string.IsNullOrEmpty(_databaseName) == false) cnx.Database = _databaseName; return cnx.ToString(); } /// <summary> /// Initialize database connection parameters /// </summary> /// <param name="host"></param> /// <param name="login"></param> /// <param name="password"></param> /// <param name="databaseName">Databasename</param> public static void InitializeDatabaseParameters(string host, string login, string password, string databaseName) { _host = host; _login = login; _password = password; _databaseName = databaseName; } /// <summary> /// Setup of the Connections to the local and the remote database server with standard user credentials. /// </summary> public void Connect() { lock (_locker) { if (_connection != null) { if (_connection.Ping()) return; // already connected Disconnect(); } _connection = Connect(GetConnectionString()); } } /// <summary> /// Connect to the database server using provided Connection Data. This can look like this: /// After Coonection has been opened, a Ping-check is performed to verify it. /// </summary> /// <param name="connectionData">Connection Data are used to setup the connection</param> /// <param name="evtHandler">Handler which occurs when the state of connection cahnged</param> /// <returns>Function returns the mysql connection</returns> /// <exception cref="CouldNotConnectToDBException">If the connection could not be setup to the database</exception> public static MySqlConnection Connect(string connectionData, StateChangeEventHandler evtHandler = null) { int SleepTmInMilliSec = 100; MySqlConnection mySqlConn; try { mySqlConn = new MySqlConnection(connectionData); if (evtHandler != null) mySqlConn.StateChange = evtHandler; mySqlConn.Open(); Thread.Sleep(SleepTmInMilliSec); var connected = mySqlConn.Ping(); if (connected == false) throw new CouldNotConnectToDBException("Connect(): Could not connect to the database"); } catch (Exception ex) { throw new CouldNotConnectToDBException(ex.Message, ex); } return mySqlConn; } /// <summary> /// Disconnect from the database server /// </summary> /// <exception cref="CouldNotDisconnectFromTheDBException">If it was not possible to discinnect from a database server. /// For more details see exception message.</exception> public void Disconnect() { lock (_locker) { try { _connection.Close(); _connection.Dispose(); _connection = null; } catch (Exception ex) { throw new CouldNotDisconnectFromTheDBException(ex.Message, ex); } } } /// <summary> /// Execute "select" statement and return result in DataTable /// </summary> /// <param name="sqlCommand"></param> /// <param name="transaction"></param> /// <returns></returns> /// <exception cref="NoActiveConnectionToDBException"></exception> /// <exception cref="SQLStatementException"></exception> public DataTable ExecuteSelect(string sqlCommand, DbTransaction transaction = null) { try { lock (_locker) { //check if the connection is already established. If not, setup the connection Connect(); MySqlTransaction mysqltransaction = transaction as MySqlTransaction; if (transaction != null && mysqltransaction == null) throw new Exception("ExecuteSelect(): Transaction object should have type 'MySqlTransaction'"); DataTable resultTable = new DataTable(); using (var mysqlAdapter = new MySqlDataAdapter()) { using (var mysqlCommand = new MySqlCommand { CommandText = sqlCommand, Connection = _connection, Transaction = mysqltransaction }) { mysqlAdapter.SelectCommand = mysqlCommand; mysqlAdapter.Fill(resultTable); } } return resultTable; } } catch (Exception ex) { if (ex is NoActiveConnectionToDBException) { throw; } // Original exception (ex) has to be set as inner exception: throw new SQLStatementException("Exception while executing the SQL statement. Please see inner exception for more details.", ex); } }