In order to connect to on MySQL method, I propose this more flexible solution, thus, it enables us to customize the connection parameters in one hand, moreover, it enables us to choose which model should we use. I mean, ADO connected mode using data reader or disconnected mode using data adapter and data set.
Walkthrough
Remarque: Of course, I suppose that MySQL server is installed in your machine, a database already exists, and all information and permissions to use the given database are ready.
Here is a class that helps you connect and deal with your MySQL database:
- using System;
- using System.Text;
- using System.Data;
- using System.Data.Odbc;
-
- namespace MySqlProj {
-
-
-
- public class ODBCClass: IDisposable {
-
- private string _Password;
-
- public string Server { get;
- set; }
-
- public string Port { get;
- set; }
-
- public string DataBaseName { get;
- set; }
-
- public string UserID { get;
- set; }
-
- public string Password {
- set { _Password = value; }
- }
-
- public string Query { get;
- set; }
-
- private OdbcConnection myConnection;
-
- OdbcCommand myCommand;
-
-
-
-
-
-
-
-
- public ODBCClass(string Server, string Port, string DataBaseName, string UserID, string Password, string Query) {
- this.Server = Server;
- this.Port = Port;
- this.DataBaseName = DataBaseName;
- this.UserID = UserID;
- this.Password = Password;
- this.Query = Query;
-
- myConnection = new OdbcConnection();
- myConnection.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" + Server + "; PORT=" + Port + ";DATABASE= " + DataBaseName + ";UID= " + UserID + ";PWD=" + Password;
- try {
-
- myConnection.Open();
-
- Console.WriteLine("Connected to the data base");
-
- myCommand = new OdbcCommand(Query, myConnection);
-
-
- } catch (OdbcException caught) {
-
- } catch (InvalidOperationException caught) {
-
- }
- }
-
-
-
-
-
-
-
-
-
-
- public void CloseConnection() {
- myConnection.Close();
- }
- public OdbcCommand GetOdbcCommand() {
-
- return myCommand;
- }
-
- public void Dispose() {
- myConnection.Close();
- }
- }
- }
Now, open a new Project>Console application and name it as you like, create a new empty class and name it ODBCClass, then copy and paste the above class in the code editor.
Once this is done you can choose either to work within a connected mode, if you do so then implement the main method as follows:
- using System.Data.Odbc;
-
- namespace MySqlProj {
- class Program {
- static void Main(string[] args) {
- using(ODBCClass o = new ODBCClass("localhost", "3306", "database", "me", "me", "select * from user")) {
- OdbcCommand comm = o.GetOdbcCommand("Select * from user");
- OdbcDataReader oReader = comm.ExecuteReader();
- while (oReader.Read()) { Console.WriteLine(oReader[0] + " " + oReader[1]); }
- Console.Read();
- }
- }
- }
- }
If you want to do the same thing but in disconnected mode then implement the Main method as follows:
- using System;
- using System.Text;
- using System.Data;
- using System.Data.Odbc;
-
- namespace MySqlProj {
- class Program {
- static void Main(string[] args) {
- using(ODBCClass o = new ODBCClass("localhost", "3306", "database", "me", "me")) {
- OdbcCommand comm = o.GetOdbcCommand("Select * from user");
- OdbcDataAdapter oAdapter = new OdbcDataAdapter(comm);
- DataSet Ds = new DataSet();
- oAdapter.Fill(Ds);
-
- Console.WriteLine("Data set is filled you can make use of it now");
-
- Console.Read();
- }
- }
- }
- }
That's it
God dotneting!!!