ASP.NET & MySQL

The community is working on translating this tutorial into Malay, but it seems that no one has started the translation process for this article yet. If you can help us, then please click "More info".

If you are fluent in Malay, then please help us - just point to any untranslated element (highlighted with a yellow left border - remember that images should have their titles translated as well!) inside the article and click the translation button to get started. Or have a look at the current translation status for the Malay language.

If you see a translation that you think looks wrong, then please consult the original article to make sure and then use the vote button to let us know about it.

Metadata

Please help us by translating the following metadata for the article/chapter, if they are not already translated.

If you are not satisfied with the translation of a specific metadata item, you may vote it down - when it reaches a certain negative threshold, it will be removed.
Please only submit an altered translation of a metadata item if you have good reasons to do so!

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:

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.