Reader Level:
ARTICLE

Connect to MySQL database via ODBC using a DSN: Part I

Posted by Bechir Bejaoui Articles | ADO.NET August 01, 2008
This article shows how to connect with MySQL database via ODBC.
  • 0
  • 0
  • 64243

Certainly if you ask me which one is better: Using Microsoft SQL server or MySQL server as a database to store your application data, I say, of course, Microsoft SQL server as it offers more features and demonstrates less number of bugs and problems. But what if the majority of hosting servers provides MySQL server as database system and even the minority that offers Microsoft SQL server is very expensive. Hence you haven't any choice but storing and manipulating your data from a MySQL database.   

In the below table, I try to compare the two products:

Characteristics  Microsoft SQL server MySQL server
Disponibility in the market according to my region(Tunisia) Less offered by the hosting server machines as they almost useLinux and Unix platforms , moreover, even if they make use of windows serverplatform MySQL server is more present than Microsoft SQL server It is rare to find a hosting server machine that offersMicrosoft SQL server as a front end database, and even if you find one, it ismore expensive than the equivalent that offers MySQL server
Features Offers less features Offers more features
Coherence with .Net application Less compatibility and more bugs in spite of the endeavors madeto make easy the connection with .net applications The best choice if you are .Net developer is SQL server, itdemonstrates the best performance, easy to establish secure and stableconnections. In addition it offers good security features
Simplicity It is more simple to deal with MySQL server It is less simple to deal with Microsoft SQLserver
Connection More difficult to connect to MySQL database, the best and theonly way is to use ODBC the 3.51 ODBC connector and the rest, I mean OLEDBconnectors, the recent ODBC connectors versions and event the others toolsrepresent a lot of bugs and they are not stables There are several ways to connect to Microsoft SQL server. The.Net environment provides special libraries to deal only with SQL serverconnections. More over you can use ODBC and even OLEDB for the older MicrosoftSQL server versions.

Connecting to a MySQL database is not something difficult, but when visiting several forums you can remark that the question is frequently posted by a lot of people, I think, as they are .Net developers, they deal almost all the time with an SQL Server database to store data, the other database types are not used. I, personally, saw this problematic in a dozen of forums, English forums, French forums and German forum too. Always the same question like "how to pass parameter" and "connect to a MySQL database from a .Net application" can be seen in a forum.

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 a second article, I will provide 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 database 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 databases and download it or finally, it is possible to deal with MySQL database 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 those topic. If you are interested in this kind of databases 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 database from .Net environment.  In our case we suppose that we have a database that is called database, using localhost as server, me as user id and me as password. This database contains a table called user; this last one contains two fields UserID, and Password which I have already populated with some data.

Walkthrough:

First, you have to download the MySQL ODBC connector 3.51 from 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 stable, I personally had problems with the ODBC connector 5.1. After downloading and installing the connector, create a data source name DSN, to do so follow those steps:

  1. Go to Start >Configuration panel > Administration tools > ODBC datasources

  2. Select user data sourcestab, then click Add



    Figure 1

  3. Then the below windowappears, then select MySQL 3.51 Driver and click finish



    Figure 2

  4. Add your databaseparameters, namely the data source name (The alias used later in the connectionstring "database" in this case), the description, the server name (Localhost ifyou use a local machine or the server name if you use a distant machine), theuser name (root for example or you can create a new user from within MySQLServer), the password and finally database name.

  5. You can test theconnection by clicking Test button, if it is OK then click OK, then click OKagain to 



    Figure 3 

Once the DSN is configured, move to Visual studio and perform the following tasks:

Create a new Console application project then add a new class to the new project and name it ODBCClass, finally implement it as below:

using System;

using System.Text;

using System.Data;

using System.Data.Odbc; 

 

namespace MysqlProj_1

{

class ODBCClass : IDisposable

{

/// <summary>

/// OdbcConnection : This is the connection

/// </summary>

OdbcConnection oConnection;

/// <summary>

/// OdbcCommand : This is the command

/// </summary>

OdbcCommand oCommand;

/// <summary>

/// Constructor: This is the constructor

/// </summary>

/// <param name="DataSourceName">string: This is the data source name</param>

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();

}

}

/// <summary>

/// void: It is used to close the connection if you work within disconnected

/// mode

/// </summary>

public void CloseConnection()

{

oConnection.Close();

}

/// <summary>

/// OdbcCommand: This function returns a valid odbc connection

/// </summary>

/// <param name="Query">string: This is the SQL query</param>

/// <returns></returns>

public OdbcCommand GetCommand(string Query)

{

oCommand = new OdbcCommand();

oCommand.Connection = oConnection;

oCommand.CommandText = Query;

return oCommand;

}

/// <summary>

/// void: This method close the actual connection

/// </summary>

public void Dispose()

{

oConnection.Close();

}

 

}

}

If you want to work with connecting mode then do implement the main method as follows:

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 to work with disconnected mode then implement the main method as follows:

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

COMMENT USING

Trending up