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