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