Part II - Connect to MySQL data base- via ODBC without using data source name

Part II - Connect to MySQL data base- via ODBC without using data source name

Introduction:

In a previous article, Part I - Connect to MySQL data base- via ODBC using data source name, I proposed a method of how to deal with my sql server data base connection using a data source name "DSN". In the present article I will show how to perform the same task but without using a data source name the "DSN".
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 mode should we use. I mean, ADO connected mode using data reader or disconnected mode using data adapter and data set.

Walkthrough:

Remarque: Of Corse, I suppose that MySQL server is installed in your machine, a data base already exists, and all information and permissions to use the given data base are ready. Here is a class that helps you connect and deal with your MySQL data base:

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

/// This is the constructor
/// 

/// string: The server name
/// string: The port number
/// string: The data base name
/// string: The user name
/// string: The password
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
    }
}
/// 

/// OdbcCommand : This method returns a command object
/// 

/// string: This is the sql query
/// returns an OdbcCommand

/// 

/// void: It is used to close the connection if you work within disconnected
/// mode
/// 

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

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","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();
            }
     }
  }
}

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