ASP.Net Integration With MYSQL Database

Using the ODBC classes of the .NET framewhork we can also work with MySQL databases in ASP.NET applications. The easiest way to use MySQL with ASP.NET is to use the MySQL ODBC connecetor from MySQL (if you're work is being hosted by a company that supports MySQL then they have probably already installed this, but if you're testing your code on your own machine with your own instance of MySQL, then you need to install this. MySQL Connector/ODBC 3.51 can be downloaded from this page: 

Download Connector/ODBC )

After once installed, now we are ready to connect to the MySQL database server.

Now first we will create a project in Visual Studio, then we will store the connection-information for our database in one place, this is the standard way and an example of reusability. To do that we will double-click to the web.config file in Solution Explorer. If in case there is not any such file then right-click to the solution and select “Add New Item”. Then select “web.config” file and add this to your project and add the following lines there.

With this pattern we can invoke the connection string all over the application wherever it is required.

We should just need the three values of the name of your database, DatabaseName and the UserName and Password for one of the users of the MySQL database server.

Let's create a table named "users".

And fill in some data to it.

Now we will  access this table for the first time, using ASP.NET. For doing this l, we need to import the System.Data.Odbc namespace. Add this line to the top of your CodeBehind file.

This will allow us access to a bunch of ODBC related classes.

• OdbcConnection: Provides an ODBC connection to a database.
• OdbcCommand: Will execute an SQL command using an existing OdbcConnection.
• OdbcDataReader: Will provide fast access to the data that the OdbcCommand will bring us.

Let's test our connection string for the work.

Explanation

Explain the lines of code. First we created an OdbcConnection object, to establish the connection with the database. We use the ConfigurationManager class to obtain the connection string using the System.Configuration namespace that we stored in the web.config file. It's within a using() construct, that is a convenint way to tell C# that when the block of code ends, it should dispose of the object. If we didn't use it then we would instead need to call connection.Dispose(); when we are done. Next, we open the connection and then we create an instance of the OdbcCommand class to fire off SQL queries against the database. Using the command object, we query the database and in return we get an OdbcDataReader. The DataReader class is the simplest and fastest way to access a database in .NET.

And now press F5 to run the application.