c#连mysql源码_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();

}

///

/// Initialize database connection parameters

///

///

///

///

/// Databasename

public static void InitializeDatabaseParameters(string host, string login, string password, string databaseName)

{

_host = host;

_login = login;

_password = password;

_databaseName = databaseName;

}

///

/// Setup of the Connections to the local and the remote database server with standard user credentials.

///

public void Connect()

{

lock (_locker)

{

if (_connection != null)

{

if (_connection.Ping())

return; // already connected

Disconnect();

}

_connection = Connect(GetConnectionString());

}

}

///

/// 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.

///

/// Connection Data are used to setup the connection

/// Handler which occurs when the state of connection cahnged

/// Function returns the mysql connection

/// If the connection could not be setup to the database

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;

}

///

/// Disconnect from the database server

///

/// If it was not possible to discinnect from a database server.

/// For more details see exception message.

public void Disconnect()

{

lock (_locker)

{

try

{

_connection.Close();

_connection.Dispose();

_connection = null;

}

catch (Exception ex)

{

throw new CouldNotDisconnectFromTheDBException(ex.Message, ex);

}

}

}

///

/// Execute "select" statement and return result in DataTable

///

///

///

///

///

///

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

}

}