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

 
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:
  1. using System;  
  2. using System.Text;  
  3. using System.Data;  
  4. using System.Data.Odbc;  
  5.   
  6. namespace MySqlProj {  
  7.     /* The class implements IDisposable interface 
  8.     * inorder to close the connection once the class instance 
  9.     is disposed*/  
  10.     public class ODBCClass: IDisposable {  
  11.         //This is the password private field  
  12.         private string _Password;  
  13.         //The server name  
  14.         public string Server { get;  
  15.             set; }  
  16.         //The port number  
  17.         public string Port { get;  
  18.             set; }  
  19.         //The data base name  
  20.         public string DataBaseName { get;  
  21.             set; }  
  22.         //The user name  
  23.         public string UserID { get;  
  24.             set; }  
  25.         //The password is only set for security issues  
  26.         public string Password {  
  27.             set { _Password = value; }  
  28.         }  
  29.         //Set a query  
  30.         public string Query { get;  
  31.             set; }  
  32.         //Define a private connection  
  33.         private OdbcConnection myConnection;  
  34.         //Define a command  
  35.         OdbcCommand myCommand;  
  36.         /// <summary>  
  37.         /// This is the constructor  
  38.         /// </summary>  
  39.         /// <param name="Server">string: The server name</param>  
  40.         /// <param name="Port">string: The port number</param>  
  41.         /// <param name="DataBaseName">string: The data base name</param>  
  42.         /// <param name="UserID">string: The user name</param>  
  43.         /// <param name="Password">string: The password</param>  
  44.         public ODBCClass(string Server, string Port, string DataBaseName, string UserID, string Password, string Query) {  
  45.             this.Server = Server;  
  46.             this.Port = Port;  
  47.             this.DataBaseName = DataBaseName;  
  48.             this.UserID = UserID;  
  49.             this.Password = Password;  
  50.             this.Query = Query;  
  51.   
  52.             myConnection = new OdbcConnection();  
  53.             myConnection.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" + Server + "; PORT=" + Port + ";DATABASE= " + DataBaseName + ";UID= " + UserID + ";PWD=" + Password;  
  54.             try {  
  55.                 //Open the connection  
  56.                 myConnection.Open();  
  57.                 //Notify the user that the connection is opened  
  58.                 Console.WriteLine("Connected to the data base");  
  59.                 //Create a new command object  
  60.                 myCommand = new OdbcCommand(Query, myConnection);  
  61.                 /* CommandBehavior.CloseConnection option forces the connection to close if 
  62.                  somethig id wrong*/  
  63.             } catch (OdbcException caught) {  
  64.                 //TO DO Deal with  the exception  
  65.             } catch (InvalidOperationException caught) {  
  66.                 //TO DO Deal with  the exception  
  67.             }  
  68.         }  
  69.         /// <summary>  
  70.         /// OdbcCommand : This method returns a command object  
  71.         /// </summary>  
  72.         /// <param name="Query">string: This is the sql query</param>  
  73.         /// <returns>returns an OdbcCommand</returns>  
  74.   
  75.         /// <summary>  
  76.         /// void: It is used to close the connection if you work within disconnected  
  77.         /// mode  
  78.         /// </summary>  
  79.         public void CloseConnection() {  
  80.             myConnection.Close();  
  81.         }  
  82.         public OdbcCommand GetOdbcCommand() {  
  83.             //Returns a command object    
  84.             return myCommand;  
  85.         }  
  86.         //When the object is disposed the connection is  closed  
  87.         public void Dispose() {  
  88.             myConnection.Close();  
  89.         }  
  90.     }  

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:
  1. using System.Data.Odbc;  
  2.   
  3. namespace MySqlProj {  
  4.   class Program {  
  5.     static void Main(string[] args) {  
  6.       using(ODBCClass o = new ODBCClass("localhost""3306""database""me""me""select * from user")) {  
  7.         OdbcCommand comm = o.GetOdbcCommand("Select * from user");  
  8.         OdbcDataReader oReader = comm.ExecuteReader();  
  9.         while (oReader.Read()) { Console.WriteLine(oReader[0] + "  " + oReader[1]); }  
  10.         Console.Read();  
  11.       }  
  12.     }  
  13.   }  

If you want to do the same thing but in disconnected mode then implement the Main method as follows:
  1. using System;  
  2. using System.Text;  
  3. using System.Data;  
  4. using System.Data.Odbc;  
  5.   
  6. namespace MySqlProj {  
  7.     class Program {  
  8.         static void Main(string[] args) {  
  9.             using(ODBCClass o = new ODBCClass("localhost""3306""database""me""me")) {  
  10.                 OdbcCommand comm = o.GetOdbcCommand("Select * from user");  
  11.                 OdbcDataAdapter oAdapter = new OdbcDataAdapter(comm);  
  12.                 DataSet Ds = new DataSet();  
  13.                 oAdapter.Fill(Ds);  
  14.   
  15.                 Console.WriteLine("Data set is filled you can make use of it now");  
  16.                 //TO DO  Make use of the populated data set  
  17.                 Console.Read();  
  18.             }  
  19.         }  
  20.     }  

That's it
 
God dotneting!!!


Similar Articles