In Focus

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 mode should we use. I mean, ADO connected mode using data reader or disconnected mode using data adapter and data set.

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 mode 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 think 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!!!