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

Introduction

 

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 a database system and even the minority that offers Microsoft SQL server is very expensive. Hence you haven't any choice but to store 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 use Linux and Unix platforms, moreover, even if they make use of windows server platform MySQL server is more present than Microsoft SQL server	It is rare to find a hosting server machine that offers Microsoft SQL Server as a front end database, and even if you find one, it is more expensive than the equivalent that offers MySQL server
Features	Offers fewer features	Offers more features
Coherence with .Net application	Less compatibility and more bugs in spite of the endeavors made to make easy the connection with .net applications	The best choice, if you are a .Net developer, is the SQL server, it demonstrates the best performance, easy to establish secure and stable connections. In addition, it offers good security features
Simplicity	It is more simple to deal with the MySQL server	It is less simple to deal with Microsoft SQL server
Connection	More difficult to connect to MySQL database, the best and the only way is to use ODBC the 3.51 ODBC connector and the rest, I mean OLEDB connectors, the recent ODBC connectors versions, and event the other tools represent a lot of bugs and they are not stables	There are several ways to connect to a Microsoft SQL Server. The .Net environment provides special libraries to deal only with SQL server connections. Moreover, you can use ODBC and even OLEDB for the older Microsoft SQL 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 forums, English forums, French forums, and German forums too. Always the same question as "how to pass the 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 a 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 the MySQL server first. If you are a PHP programmer then you will be familiar with this product. In our case, we are concerned only with the MySQL database server, I personally use PhpMyAdmin in order to create and request data within MySQL format. You can also download the MySQL server directly from www.mysql.com, the MySQL official web site. Moreover, you can download another 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 that topic. If you are interested in this kind of database you can simply take a look at tutorials at www.mysql.com. But for the moment, our unique concern is how to connect to a MySQL database from a .Net environment. In our case we suppose that we have a database that is called a database, using localhost as a server, me as user id, and me as a 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 data sources
2. Select the user data sources tab, then click Add
    
    
   Figure 1
    
3. Then the below window appears, then select MySQL 3.51 Driver and click finish
    
    
   Figure 2
    
4. Add your database parameters, namely the data source name (The alias used later in the connection string "database" in this case), the description, the server name (Localhost if you use a local machine or the server name if you use a distant machine), the user name (root for example or you can create a new user from within MySQL Server), the password and finally database name.
    
5. You can test the connection by clicking the Test button, if it is OK then click OK, then click OK again 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 the data source name.

 

Part II 

 

GoodDotneting!!!