A Step-by-Step Guide To Using MySQL with ASP.NET - Part 3

By Ziran Sun

The first part of this article illustrated how to install and configure the MySQL Database Server, install and use the MySQL Administrator, create a new database, and create and populate a new sample table with some sample data.

The second part covered creating a MySQL user account, granting that user access to the database, and examined the basic steps required to connect to the database from an ASP.NET web page.

When it was written, part two was designed to be the end of the article. However, since then I've received a number of email messages requesting additional information. Therefore this part will attempt to address the questions raised in those messages and share what I've found while researching them.

Please note that the code listings below assume that your tables and users are set up as outlined in the earlier parts of the article. The information in this part may be useful even if you haven't read parts one and two, but if that's the case, it is highly unlikely that the code will actually run without some minor tweaks. For example, I assume most people who haven't followed along with the earlier parts won't have a database named "mydatabase" and most certainly wouldn't have a user named "15secs".   ;)

Namespace Not Found Errors

As I mentioned in part two, I ran into a problem where I was unable to get my ASP.NET pages to access the MySQL Connector/Net namespace without placing a copy of the MySql.Data.dll file into my application's /bin directory. Well, a reader was kind enough to send me the following email explaining the situation and so I thought I should pass it along.

Hello Ziran,

I was reading [A Step-by-Step Guide To Using MySQL with ASP.NET], and in the second half of the tutorial you are having a problem that Mysql.Data could not be found. The solution you give of putting the .dll in the bin directory is just fine but there is a more 'official' manner for this.

You need to change the machine.config file of the server and add a reference to the assembly in the GAC or, if you are using the latest .NET Framework, you can make this reference in the web.config file. You can find it here:
C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\CONFIG

You need some information about the MySql.Data assembly in the GAC, which you can obtain by typing the following at the SDK Command Prompt:

gacutil -l Mysql.Data

With this information you can put sometime like this in the web.config file:

<add assembly="MySql.Data, Version=1.0.7.30072, Culture=neutral, PublicKeyToken=c5687fc88969c44d" />

And then is it works like magic.  :)

Please tell me if I can do something for this great community. I like to be involved. I am from The Netherlands, Europe.

Regards,
Rene Veenstra

I hope this helps explain things a bit and in response to Rene, you already have done something for the community... keep up the good work!

A C# Version of the Sample ASP.NET 1.x Page to Query a MySQL Database

I didn't realize how many people were actually using C# instead of VB as their language of choice for .NET development. One of the most popular requests I received was for a C# version of the MySQL.aspx page that I published in part two. While C# is not my development language of choice, I was able to get a C# version of the page working and am including the listing for it here for your reference.

MySqlC.aspx

<%@ Page Language="C#" Debug="true" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="MySql.Data.MySqlClient" %>
<script runat="server">

protected void Page_Load(Object sender, EventArgs e)
{
    MySqlConnection myConnection = new MySqlConnection(
        "server=localhost; user id=15secs; password=password; database=mydatabase; pooling=false;");

    String strSQL = "SELECT * FROM mytable;";

    MySqlDataAdapter myDataAdapter = new MySqlDataAdapter(strSQL, myConnection);

    DataSet myDataSet = new DataSet();
    myDataAdapter.Fill(myDataSet, "mytable");

    MySQLDataGrid.DataSource = myDataSet;
    MySQLDataGrid.DataBind();
}

</script>
<html>
<head>
    <title>Simple MySQL Database Query</title>
</head>
<body>
    <form runat="server">
        <asp:DataGrid id="MySQLDataGrid" runat="server"></asp:DataGrid>
    </form>
</body>
</html>

You'll have to pardon me if the style of the code isn't perfect, but like I said C# isn't really my thing and getting it running was about the best I could do. (The case sensitivity threw me off for a bit and those C# error messages aren't really all that helpful.) I know it's not much, but hopefully those of you who were looking for a C# version at least now have something to start with.

Connecting to MySQL from ASP.NET 2.0

While you can still connect to MySQL from ASP.NET 2.0 using the same old ASP.NET 1.x style of code (using the MySql.Data.MySqlClient) that sort of defeats the point of all the cool .NET 2.0 stuff. Hopefully the official MySQL Connector/Net will support the new fully declarative data binding model soon, but in the mean time you'll need to turn to a third party. While there may well be others, one third-party connector that does support the new provider model is CoreLab's MySQLDirect .NET Data Provider. They provide a fully-functional 30-day trial version so you can determine whether their product is right for you.

Please note that there are a few different versions available for download. Make sure that you get the one for .NET Framework 2.0 if you're going to be trying the code that follows. The versions for .NET Framework 1.x are not the same thing. You don't need to use the same version I used, but if for some reason you want to then look for "MySQLDirect .NET version 3.50 beta for .NET Framework 2.0".

I didn't play with the Visual studio plug-ins or any of the bells and whistles, but just did my best to get it working on it's own. The first step is obviously to download and install the connector, which is extremely straight-forward. Once that's done, the next step is to get the connector registered as a data provider. To do this you need to add an entry to either your web server's machine.config or your application's web.config. Here's a copy of the web.config file I used to get my sample page running:

web.config

<?xml version="1.0" encoding="UTF-8"?>
<configuration>
    <system.data>
        <DbProviderFactories>
            <add
                name="MySQLDirect .NET Data Provider"
                invariant="CoreLab.MySql"
                description="CoreLab MySQLDirect .NET Data Provider"
                type="CoreLab.MySql.MySqlDirectFactory, CoreLab.MySql, Version=3.50.10.0, Culture=neutral, PublicKeyToken=09af7300eec23701"
            />
        </DbProviderFactories>
    </system.data>
</configuration>

I don't know why, but I couldn't find this information anywhere in the documentation. I ended up scouring through CoreLab's Forums for a while until I found a few posts that explained the entry. (http://www.crlab.com/forums/viewtopic.php?t=2555 and http://www.crlab.com/forums/viewtopic.php?t=3285)

The connector also has a licensing requirement. How to generate a license is covered in the documentation, but it is a little bit of an exercise so I'm going to outline the steps I used here.

  1. Create a text file named licenses.licx.
  2. Place the text CoreLab.MySql.MySqlConnection, CoreLab.MySql in the file.
  3. Save the file and add it to a Visual Studio 2005 project.
  4. Right click on licenses.licx in the VS's Solution Explorer and select "Build Runtime Licenses" from the context menu.
  5. This will create a file named App_Licenses.dll in the solution's /bin folder.
  6. Copy this file to the /bin folder of your application.

I wasn't actually using Visual Studio to write the code. The licensing step may be simpler if you do, but I was trying to keep things as simple as possible for the sake of illustration.

Once the provider is registered and the licensing is taken care of, all that's left to do is write the code to connect to our data. I'm going to use some very simple code from an earlier article (download below) and simply modify it to connect to MySQL instead of Microsoft SQL Server.

MySqlGridView.aspx

<%@ Page Language="VB" %>
<html>
<head>
  <title>MySQL ASP.NET 2.0 CoreLab GridView</title>
</head>
<body>

<form runat="server">

  <asp:SqlDataSource id="myMySqlDataSrc" runat="server"
    ProviderName="CoreLab.MySql"
    ConnectionString="server=localhost; Database=mydatabase; User Id=15secs; password=password;"
    SelectCommand="SELECT * FROM mytable;"
  />

  <asp:GridView id="myGridView" runat="server"
    DataSourceID="myMySqlDataSrc"
  />

</form>

</body>
</html>

The line in red is the one that tells the script to use CoreLab's provider. Without that the script would attempt to connect via the default SQL Server provider which obviously wouldn't work very well.

And here's a quick screen shot, just to show you that this actually does work:

Now I realize that the output isn't much to look at, but it does run and you automatically get all the goodies that go along with the new ASP.NET 2.0 data-bound controls.

Conclusion

I hope this part of the article has helped address some of the questions and issues that readers have been running into when trying to use ASP.NET with MySQL. Whether you were introduced to the combination by the earlier parts or arrived here simply trying to find a solution to a problem you ran into on your own, please let me know if you found the information useful and/or how it could be made more so. You can reach me by using the site's feedback form and mentioning either my name, Ziran Sun, or the title of the article.



Downloads

Comments

  • There are no comments yet. Be the first to comment!

Leave a Comment
  • Your email address will not be published. All fields are required.

Top White Papers and Webcasts

  • As all sorts of data becomes available for storage, analysis and retrieval - so called 'Big Data' - there are potentially huge benefits, but equally huge challenges...
  • The agile organization needs knowledge to act on, quickly and effectively. Though many organizations are clamouring for "Big Data", not nearly as many know what to do with it...
  • Cloud-based integration solutions can be confusing. Adding to the confusion are the multiple ways IT departments can deliver such integration...

Most Popular Programming Stories

More for Developers

RSS Feeds

Thanks for your registration, follow us on our social networks to keep up-to-date