Introduction:
Connecting to a MySQL database is not something difficult, but when visiting several forums you can remark that the question is frequently posed by a lot of people. I think, as they are .Net developers, they deal almost of the time with an SQL Server data base as a front end application to store data, the other data base types are not used. I, personally, saw this problem in a dozen of forums, English, French and German forums. Always the same question how to parameter and connect to a MySQL data base from a .Net application?
Therefore, I will provide more than one method to deal with the issue. In this first article, we will see the simplest method to deal with the problem. In the second article, I will provide the a second solution. Let’s begin the trip!!!
First, if you have MySQL server already installed on your machine then it is OK, else, if you want to start from the beginning then you have to install MySQL server first. If you are a php programmer then you will be familiar with this product. In our case, we are concerned only by MySQL data base server, I personally use PhpMyAdmin in order to create and request data within MySQL format. You can also download MySQL server directly from www.mysql.com, the MySQL official web site. Moreover, you can download other kind of management consoles also provided by the same web site or you can simply Google it, find the suitable management console for MySQL data bases and download it or finally, it is possible to deal with MySQL data base system using what I can say the “ugly” console. But as a part of this tutorial, I will deal with MySQL via PhpMyAdmin. Anyway, the installation, the configuration and the creation of MySQL databases are out of the scope of this tutorial. There are a lot of articles about this issue. If you are interested by this kind of data bases you can simply take a look on tutorials in www.mysql.com. But for the moment, our unique concern is how to connect to a MySQL data base from within .Net environment. In our case we suppose to connect to a data base that called database using localhost as server, me as user id and me as password. This data base contains a table called user; this last one contains two fields UserID, and Password witch I have already populated with some data.
Walkthrough:
First, you have to download the MySQL ODBC connector 3.51 from this link http://dev.mysql.com/downloads/connector/odbc/3.51.html and then install it. There is a newer version which is the ODBC connector 5.1 but I don’t advise to use it for the moment because it is not a stable, I personally had problems with the ODBC connector 5.1. After downloading and installing the connector3.51, create a data source name DSN. To do so follow those steps:
1. Go to Start > Configuration panel > Administration tools > ODBC data sources
2. Select user data sources tab, then click Add
3. A window with a large list of connection drivers appears
4. Select MySQL ODBC 3.51 Driver(if not already installed you can't find it)
5. Then confirm your choise by clicking the confirmation button
6. A second window appears and invite you to enter your connection parameters
7. Enter the parameters,each one in its right place
Data source name:
you can enter any name, this one will be used later as a data source name
Server:
Enter the server name, if you use a local machine then enter localhost
User:
Enter the user name, it is root by default but you can use other users
Password:
Enter a password
Data base:
Enter the data base name
8. Test the connection by clicking the Test button
9. If the result is negative check the parameters or verify if the server is running if it isn't then lunch it using this command (...\mysql\bin\mysql -u the user name here –p the password here) if there is not password then use -p
10. Create a new Console application project then add a new class to the new project and name it ODBCClass, finally implement it as bellow:
using System;
using System.Text;
using System.Data;
using System.Data.Odbc;
namespace MysqlProj_1
{
class ODBCClass : IDisposable
{
///
/// OdbcConnection : This is the connection
///
OdbcConnection oConnection;
///
/// OdbcCommand : This is the command
///
OdbcCommand oCommand;
///
/// Constructor: This is the constructor
///
/// string: This is the data source name
public ODBCClass(string DataSourceName )
{
//Instantiate the connection
oConnection = new OdbcConnection("Dsn=" + DataSourceName);
try
{
//Open the connection
oConnection.Open();
//Notify the user that the connection is opened
Console.WriteLine("The connection is established with the database");
}
catch (OdbcException caught)
{
Console.WriteLine(caught.Message);
Console.Read();
}
}
///
/// void: It is used to close the connection if you work within disconnected
/// mode
///
public void CloseConnection()
{
oConnection.Close();
}
///
/// OdbcCommand: This function returns a valid odbc connection
///
/// string: This is the SQL query
///
public OdbcCommand GetCommand(string Query)
{
oCommand = new OdbcCommand();
oCommand.Connection = oConnection;
oCommand.CommandText = Query;
return oCommand;
}
///
/// void: This method close the actual connection
///
public void Dispose()
{
oConnection.Close();
}
}
}
If you want to work with connecting mode do implement the main method as follow:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;
namespace MysqlProj_1
{
class Program
{
static void Main(string[] args)
{
using (ODBCClass o = new ODBCClass())
{
OdbcCommand oCommand = o.GetCommand("select * from user");
OdbcDataReader oReader = oCommand.ExecuteReader();
while (oReader.Read())
{
Console.WriteLine(oReader[0] + " " + oReader[1]);
}
Console.Read();
}
}
}
}
Else, if you want work with disconnected mode then implement the main method as follow:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Odbc;
namespace MysqlProj_1
{
class Program
{
static void Main(string[] args)
{
using (ODBCClass o = new ODBCClass())
{
OdbcCommand oCommand = o.GetCommand("select * from user");
OdbcDataAdapter oAdapter = new OdbcDataAdapter(oCommand);
DataSet ds = new DataSet();
oAdapter.Fill(ds);
//TO DO : Make use of the data set
}
}
}
}
This is one of the two methods used to connect and deal with a MySQL data base. In subsequent articles, I will expose other techniques to achieve the same task, for instance, you shouldn’t miss the second method to connect via ODBC without using data source name.
GoodDotneting!!!
|
No responses found. Be the first to respond and make money from revenue sharing program.
|