Connect to MySQL database - via ODBC without using DSN: Part II

Part I 

 

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 {
    /* The class implements IDisposable interface
    * inorder to close the connection once the class instance
    is disposed*/
    public class ODBCClass: IDisposable {
        //This is the password private field
        private string _Password;
        //The server name
        public string Server { get;
            set; }
        //The port number
        public string Port { get;
            set; }
        //The data base name
        public string DataBaseName { get;
            set; }
        //The user name
        public string UserID { get;
            set; }
        //The password is only set for security issues
        public string Password {
            set { _Password = value; }
        }
        //Set a query
        public string Query { get;
            set; }
        //Define a private connection
        private OdbcConnection myConnection;
        //Define a command
        OdbcCommand myCommand;
        /// <summary>
        /// This is the constructor
        /// </summary>
        /// <param name="Server">string: The server name</param>
        /// <param name="Port">string: The port number</param>
        /// <param name="DataBaseName">string: The data base name</param>
        /// <param name="UserID">string: The user name</param>
        /// <param name="Password">string: The password</param>
        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 {
                //Open the connection
                myConnection.Open();
                //Notify the user that the connection is opened
                Console.WriteLine("Connected to the data base");
                //Create a new command object
                myCommand = new OdbcCommand(Query, myConnection);
                /* CommandBehavior.CloseConnection option forces the connection to close if
                 somethig id wrong*/
            } catch (OdbcException caught) {
                //TO DO Deal with  the exception
            } catch (InvalidOperationException caught) {
                //TO DO Deal with  the exception
            }
        }
        /// <summary>
        /// OdbcCommand : This method returns a command object
        /// </summary>
        /// <param name="Query">string: This is the sql query</param>
        /// <returns>returns an OdbcCommand</returns>

        /// <summary>
        /// void: It is used to close the connection if you work within disconnected
        /// mode
        /// </summary>
        public void CloseConnection() {
            myConnection.Close();
        }
        public OdbcCommand GetOdbcCommand() {
            //Returns a command object
            return myCommand;
        }
        //When the object is disposed the connection is  closed
        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");
                //TO DO  Make use of the populated data set
                Console.Read();
            }
        }
    }
}

That's it

 

God dotneting!!!