Using MySQL in ASP.Net

Many people have asked me for this tutorial, so here goes.For those of you who don’t know, MySQL is an open-source DataBase server. Being that it’s open-source, it’s also free. That’s about as low-cost as you can get. Of course, you may ask ‘Why use MySQL’? Did you read the previous sentences?? It’s free, as well as being a fairly robust database server!

To be able to use MySQL, there are a couple of downloads that must be done:

  1. MySQL Itself
  2. MySQL ODBC Driver

The Database Server (MySQL), itself can be downloaded here: Driver (for Windows) can be downloaded here:

The biggest hurdle in using MySQL, is the setup – much like many other users of MSDE have found out. Without a user interface, it’s quite cumbersome, requiring a command prompt to do all the work. Here’s the best page I’ve found so far to take you, step-by-step through this process:

Now, once this is all set up – guess what – you have no data. If you want, the above installation page will also help you get started creating databases, tables and fields for your sample data. But, if you want a much quicker way to populate your database server, so you can get started, AugustWind Software has user interface for MySQL called Data Management Toolkit. Check it out here

Now – the part you actually came here for – – the easy part!

On other parts of this site, you’ve seen many samples, using MS Access and SQL Server. As you might have noticed, there are only three major differences in using these.

  1. Namespaces used
  2. Connection String
  3. Prefix to data classes (like: OleDbDataAdapter vs. SQLDataAdapter, specific to the Imported Namespaces

For MySQL, the driver which you downloaded (above), is an ODBC Driver, therefore, numbers 1 and two in the above list use ‘ODBC’:

<%@ Import Namespace="System.Data.ODBC" %>

And, you use classes like ODBCDataAdapter, ODBCDataReader, ODBCCommand, etc.To finish out the three items in the above list, here, we show you the Connection String needed:

"Driver={MySQL ODBC 3.51 Driver};uid=YourUID;password=YourPWD;Server=YourServerIP;Option=16834;Database=YourDB;" or, you can use: "DRIVER={MySQL};SERVER=ServerIP; DATABASE=YourDB;USER=YourUID;PASSWORD=YourPWD; OPTION=3;"

Just change these few things, in your code, and you’re off and running, using MySQL in your own ASP.Net documents!Now, do you believe me, when I say that using MySQL in your ASP.Net documents is the easy part? In fact, if you have a hosted web site, chances are, most of the setup can be done by someone else and all you will need to do is the pages themselves!

Now, you can’t put off using MySQL, for lack of knowledge!

This sample shows how to use the SQLDataSource control (yes – SQL!), to connect to a MySQL database. This can, by the way, be done with no code at all, by assigning the ConnectionsString in the SQLDataSource control itself. However, to preserve horizontal screen real estate, by using multiple lines to define the connectionstring, this sampple is done, assigning the connectionstring in the Page_Load event.

Note, that, to accomplish this, we use ‘System.Data.Odbc’ as the ProviderName in the SQLDataSource control


Deixe uma resposta

O seu endereço de email não será publicado. Campos obrigatórios marcados com *