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

  • More concessions with herveleger, more lead upon!

    Posted by mrslisaiyd on 04/29/2013 10:25am

    girlfriendmicroscopic shaverturtle-dovecome byeulogisticrep

    Reply
  • Hot Nurse Bedroom Costume

    Posted by Fishnetid1076 on 03/29/2013 09:59am

    http://sexystockings.webs.com - lace bodystockingAs the name implies, Three Wishes is an online dreamgirl lingerie retailer who aims at giving all the lingerie wishes of a purchaser, from sexy costumes to sexy lingerie down to all the basics of lingerie?Foxy Lady Boutique http://G-string.webs.com - Lingerie TeddiesSo, what is a wholesale lingerie? Let us know about it Hence, a blue bed sheet is also a unique gift http://spicylingeries.webs.com - Sexy LingerieCarroll Baker was in the lead role Also some websites have coupons and discount codes category http://sexystockings.webs.com - sexy stockingsThese styles are just as sexy as their more extreme cousins, often enhanced with fake fur, lace or other detailing As their tagline says, it is the sexiest online superstore http://sexylingerieshops.webs.com - Spicy LingerieYou are no longer Jennifer, the accountant Besides that, black also indicates prestigious, modernism and sophisticated, such as a black stationery set or handphone

    Reply
  • Lingerie Teddies

    Posted by Fishnetzk1011 on 03/29/2013 09:47am

    http://sexycostumesboutique.webs.com - Nurse CostumesSizing essentially stipulates an emphasis on sensual appearance Make sure you always read the label as to the type of wash as mistakes can ruin your lingerie sets http://sexystockings.webs.com - Body StockingsDreamgirl lingerie retailers are ably available in the net, each of them offering wide variety of products from the dreamgirl line What color does he likes? It’s good if you know that but what if you don’t? Imagine a great gift with a wrong color http://sexylingeriecostumese.webs.com - Maid LingerieSchedule in home parties and order from wholesalers, distributors and dropshippers These refer to garters and stockings http://SexyChemise.webs.com - Chemise LingerieThe wholesale lingerie may include several items that include garter belts, brassiere, camisoles, gowns, panties, and so on We also know that people can get seriously depressed and stressed during this two-month season http://discountsexylingerie.webs.com - lingerie manufacturers sex appeal It is designed to accentuate a woman's curves, not to alter them

    Reply
  • Cheap Babydoll Lingerie?

    Posted by Fishnettu1098 on 03/29/2013 09:39am

    http://discountsexylingerie.webs.com - Sexy Lingerie storeBaby dolls are nowadays characterized with a small gown type apparel, usually made of translucent materials, like chiffon, nylon or silk to expose the legs and often the panty of the women If your bust is your best feature, find a costume that reveals your cleavage http://Lingeriesv.webs.com - black Lingeries boxers, and they have it Perhaps you would offer to take photos for a publication, website or edit a manuscript http://cheapspicylingerie.webs.com - Cheap CorsetsStorefront owners receive discounted listings, along with the ability to list items for up to 30 days And why only women? Men to have their own reasons for fascination towards exotic looking lingerie http://Lingeriesv.webs.com - black LingerieNothing diminishes sexiness more than feeling uncomfortable and ill at ease in an outfit Choice of material is also varied, ranging from lace, satin, silk, PVC, sheer mesh, velvet to even leather http://discountsexylingerie.webs.com - lingerie manufacturerOften, wholesalers, distributors or even manufacturers offer discount pricing for quantity purchases8

    Reply
  • Sexy Underwear

    Posted by Fishnetyn1082 on 03/29/2013 09:26am

    http://lingeriemall.webs.com - lace babydoll lingerieThis company grew for 35 years and had continuously offered a wide array of products such as sexy costumes, fetish wear, plus size costumes, sexy lingerie and even men In this way, October 31st is not only a gateway to the realm of ghosts, ghouls, and goblins, but it's also a gateway to the realm of increased sex appeal and sexual confidence http://cheapspicylingerie.webs.com - Cheap Discount Lingerie1Overall, the general guidelines when choosing the colors are like bright colors for children; black or vibrant colors for teenagers; blue, black or bold colors for adult; light colors for elderly http://cheapspicylingerie.webs.com - Cheap Discount LingerieMost stores sell lingerie in plus sizes, which are a little less revealing Women often wore short negligees with matching "bed jackets," which were short, sensual and sometimes trimmed with feathers or lace http://cheapspicylingerie.webs.com - plus size corsets cheapShopping and deciding appropriate bridal lingerie is a difficult task, as individual brides require different types of lingerie And it always gives off the impression that a woman wearing this color is one who is confident, headstrong and sexy http://cheapspicylingerie.webs.com - Sexy Cheap LingerieOnce you feel that you are comfortable in your costume, find a mirror and admire yourself in it Here are some tips for creating a sexy Halloween holiday that might just stick with you year round

    Reply
  • http://www.oakleysunglassesoutc.com/ cfmskt

    Posted by http://www.oakleysunglassesoutc.com/ Suttoneyb on 03/29/2013 05:44am

    In fact, for the domestic policy and to subtle changes cheap ghd felt. That domestic policy changes and trends undermine their own efforts to work as a diplomat ghd australia. While in China is still in a conservative state. Foreign capital is not allowed to create a capital of over one hundred thousand yuan to the factory. With between ghd hair straightener and Tan Yan.ghd, There are China and the United States work closely on education.ghd straightener, United States enjoys in China the same ground treatment and Germany. In start-up capital to 100 million for the factories in the Mainland. Of foreign capital to build factories in China to strict control. Tony Minister said with understanding. The tariffs also be dominated in the UK in the hands. Ground local light and heavy industry in the absence of tariff protection. Easily swallowed up by foreign capital. If this situation on any one country are not normal.

    Reply
  • cheap hats

    Posted by xxds1is on 03/29/2013 01:32am

    [url=http://snapbackhatwholesale.webs.com]wholesale fitted hats[/url] wholesale fitted hats j umuk [url=http://snapbackhatwholesale.webs.com]snapback hats wholesale[/url] snapback hats wholesale k aaps[url=http://snapbackhatwholesale.webs.com]wholesale snapbacks[/url] wholesale snapbacks z ahmk[url=http://cheaphatsmall.webs.com]snapbacks for cheap[/url] snapbacks for cheap q eegz[url=http://wholesalefittedhat.webs.com]snapback wholesale[/url] snapback wholesale z yvre[url=http://cheapsnapbackshat.webs.com]cheap snapbacks online[/url] cheap snapbacks online b kqak [url=http://bestbaseballcap.webs.com]hats wholesale[/url] hats wholesale p tjfx [url=http://cheapsnapbackshat.webs.com]cheap snapbacks hats[/url] cheap snapbacks hats b qzcq[url=http://cheapsnapbackshat.webs.com]cheap snapbacks hats[/url] cheap snapbacks hats a vdej[url=http://cheapsnapbackshat.webs.com]cheap hats for sale[/url] cheap hats for sale g ypel[url=http://snapbackswholesalezone.webs.com]snapback wholesale[/url] snapback wholesale j bqxt[url=http://cheaphatsmall.webs.com]snapbacks for cheap[/url] snapbacks for cheap v nbxo [url=http://wholesalefittedhat.webs.com]snapbacks wholesale[/url] snapbacks wholesale r nyhr [url=http://snapbackswholesalezone.webs.com]hats wholesale[/url] hats wholesale v ybvv[url=http://bestbaseballcap.webs.com]hats wholesale[/url] hats wholesale w zwcl[url=http://snapbackswholesalezone.webs.com]hats wholesale[/url] hats wholesale v gqsv[url=http://bestbaseballcap.webs.com]wholesale snapback caps[/url] wholesale snapback caps i mill[url=http://goodsnapbackhatscheap.webs.com]cheap snapbacks free shipping[/url] cheap snapbacks free shipping f ulhc

    Reply
  • discount oakley sunglasses

    Posted by xgliliImpumpgae on 03/28/2013 11:23pm

    cheap ray ban [url=http://guccicheapsunglass.webs.com]cheap ray ban[/url] discount oakleys oakley discount [url=http://discountsunglassesfinewebs.com]oakley discount[/url] fake ray ban sunglasses oakley discount [url=http://discountsunglassesfinewebs.com]oakley discount[/url] cheap oakley wayfarer sunglasses cheap [url=http://onlineguciisunglass.webs.com]wayfarer sunglasses cheap[/url] wholesale oakley sunglasses oakley discount [url=http://discountoakleysunglassesho.webs.com]oakley discount[/url] cheap ray ban sunglasses cheap wayfarer sunglasses [url=http://sunglasspomoteauthentic.webs.com]cheap wayfarer sunglasses[/url] cheap ray ban wayfarer akley discount [url=http://discountsunglassessale.webs.com]akley discount[/url] cheap sunglasses oakleys for cheap [url=http://sunglasswholesaleofgucci.webs.com]oakleys for cheap[/url] sunglasses wholesale

    Reply
  • wholesale oakley sunglasses

    Posted by fgliliImpumpalk on 03/28/2013 10:48pm

    http://akeoakleysunglasses.webs.com - fake oakley sunglasses discount ray ban http://bestsunglassesshop.webs.com - cheap fake oakleys replica sunglasses http://onlineguciisunglass.webs.com - sunglasses cheap discount ray ban http://guccicheapsunglass.webs.com - ray ban cheap cheap oakley frogskins http://sunglassdicountsaleu.webs.com - designer sunglasses cheap discount sunglasses

    Reply
  • cheap oakleys for sale

    Posted by kgliliImpumptiz on 03/28/2013 07:46pm

    http://qualityguccisunglass.webs.com - cheap oakley fake oakleys http://guccicheapsunglass.webs.com - cheap oakley cheap sunglasses http://wholesalesunglasseschic.webs.com - sunglasses wholesale cheap ray ban http://sunglassdicountsaleu.webs.com - ray ban wayfarer cheap oakley sunglasses discount http://discountsunglassesfinewebs.com - discount oakleys discount sunglasses

    Reply
  • Loading, Please Wait ...

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

Top White Papers and Webcasts

Most Popular Programming Stories

More for Developers

RSS Feeds

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