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();
}
}
}
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 done you can choose either to work within a connected mode, if you do so then implement the main method as follow:
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();
}
}
}
}
If you want to do the same think but in disconnected mode then implement the Main method as follow:
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!!!
|
No responses found. Be the first to respond and make money from revenue sharing program.
|