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.





More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read