ASP.NET 37 :MySQL First access

MySQL - First access

Okay, let's try accessing our test table for the first time, using ASP.NET. First of all, we need to import the System.Data.Odbc namespace. Add the following line in the top of your CodeBehind file:

using System.Data.Odbc;

This will give us access to a bunch of ODBC related classes. For now, we will need 3 of them: 

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 which the OdbcCommand will bring us.


The first example will focus on getting access to our test data, and simply printing it out, and while this is not how we normally do things with ASP.NET, it's simply meant to show you the most basic example of data access. Since we write directly to the (top of the) page, we don't need to add any markup code. Simply go to the CodeBehind (.cs) file, and add the following code to the Page_Load method:

try
{
    using(OdbcConnection connection = new OdbcConnection(ConfigurationManager.ConnectionStrings["MySQLConnStr"].ConnectionString))
    {
        connection.Open();
        using(OdbcCommand command = new OdbcCommand("SELECT name FROM test_users", connection))
        using(OdbcDataReader dr = command.ExecuteReader())
        {
            while(dr.Read())
                Response.Write(dr["name"].ToString() + "<br />");
            dr.Close();
        }
        connection.Close();
    }
}
catch(Exception ex)
{
    Response.Write("An error occured: " + ex.Message);
}

Okay, if you come from the PHP world, you're probably about to run away because of the big amount of code needed to perform a simple database extraction. However, C# is an Object Oriented language, and sometimes it just takes a bit more code to make a more elegant solution. Of course, code like this can be gathered in reusable classes and methods, but for now, we do it the basic way. 

Now, let me try to explain the various lines of code. First we create an OdbcConnection object, to establish the connection with the database. We use the ConfigurationManager class to obtain the connection string which we stored in the web.config file in the previous chapter. It's with in a using() construct, which is a convenint way to tell C# that when the block of code ends, it should dispose the object. If we didn't use it, we would instead have to call connection.Dispose(); when we were done. Next, we open the connection, and then we create an instance of the OdbcCommand class, which is used 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. 

We loop through the datareader, and on each iteration, we output the name field from the database, along with a linebreak tag. Pretty simple, right? As you can see, the entire data access block is encapsulated in a try..catch block. Lots of things can go wrong when interacting with a database, so using a try..catch block is recommended. Using dr["name"], we get the result as an object, which is why I'm calling the ToString(). If you wish for more control of the datatype you receive from the reader, you can use the Get* methods, like GetInt32(), GetString() etc. However, these methods require you to use the index of the field in your query, and not the name of the field, which is not as convenient. 

Run the website, and have a look at the output. It should contain a bunch of names from our test table, or which ever table you're using.