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:
  1. using System;
  2. using System.Text;
  3. using System.Data;
  4. using System.Data.Odbc;
  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;
  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>
  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. }
  91. }
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;
  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. }
  14. }
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;
  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);
  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. }
  21. }
That's it
God dotneting!!!