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

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.

Now that the database server is up and running, this part of the article will complete our coverage of using MySQL with ASP.NET by covering how to:

  • Add a new MySQL user.
  • Assign the new user the appropriate permisions to the database.
  • Connect to the MySQL server from .NET.
  • Build a simple ASP.NET page to query the database.

Step 7 - Creating a New MySQL User Account

I'm assuming that you're been following along with part one, so at this point you should have the MySQL Server and MySQL Adminstrator installed, and have a sample database named "mydatabase" which contains a sample table named "mytable" with two rows of data in it.

In order to add a new user account, we'll once again need to run MySQL Administrator and login to your server using the password you set during installation. You'll then want to select the "User Administration" item from the list of items at the left of the MySQL Administrator window. This should bring up a list of the current user account on the server (There should already be once called "root"). If you right-click in the small window where the users are listed you should get the option to "Add new User".

You'll then be prompted to enter the new user's details. I've named the user "15secs" and assigned a password of "password".

Once you've finished entering the user's details press "Apply Changes" to save your modifications.

Step 8 - Granting the User Account Access to the Database

By default new users have permission to do almost nothing. In order to allow our new user to connect to the MySQL database server, we need to grant him what MySQL calls "Schema Privileges". This is naturally done from the "Schema Privileges" tab in MySQL Administrator.

Notice in the above screen capture that the user has no "Assigned Privileges" to the "mydatabase" database. Since the user will need to be able to query the database to run the sample code which follows, I'm now going to assign the user the "SELECT" privilege by highlighting it in the "Available Privileges" column and clicking the arrow to move it to the "Assigned Privileges" column. Once again I'll click the "Apply Changes" button to save changes.

Depending on your application the user may very well need more permission then I've assigned to "15secs". Most non-trivial applications will probably need at least "INSERT", "UPDATE", and "DELETE" permissions in addition to "SELECT", but it's always best to err on the side of caution and add more permissions as they are needed then to simply give everyone full control.

Step 9 - Connecting to the Database Server from an ASP.NET Page

To my knowledge there are currently two main ways to connect to a MySQL database server from .NET: MySQL Connector/ODBC (aka. MyODBC) and MySQL Connector/Net. While the ODBC connector is cross-platform and is compliant with ODBC standards, the .NET version is generally the better choice when using MySQL with .NET.

The setup files can be downloaded from the MySQL Connector/Net page and installation is straight-forward.

Note: Even though I selected the option to register Connector/NET in the Global Assembly Cache and when I checked it later I found that it actually was installed there, until I copied the MySql.Data.dll file to my application's /bin folder I couldn't get the import statement to find the Connector/NET's namespace. The exact error message was:

BC30466: Namespace or type specified in the Imports 'MySql.Data.MySqlClient' cannot be found.

I'm not sure what the problem was/is, but I looked around and it seems some others are having the same issue. So, for the time being, placing another copy of the file from it's installed location (ie. C:\Program Files\MySQL\MySQL Connector Net 1.0.4\bin\.NET 1.1\) to your application's /bin folder (ie. C:\Inetpub\wwwroot\bin\) should resolve the issue.

Step 10 - A Sample ASP.NET Page to Query a MySQL Database

So with our MySQL database finally squared away and MySQL Connector/Net installed, we can now start using MySQL from our ASP.NET web pages. For illustration I'm going to provide a very simple script with no bells and whistles. You can find fancy database scripts all over the web. The point of this one is simply to show you what you need to do to connect to MySQL. Don't get me wrong... all that fancy stuff works just fine using MySQL (and usually with very few changes) but that's just not the point of this script.

MySQL.aspx

<%@ Page Language="VB" debug="true" %>
<%@ Import Namespace = "System.Data" %>
<%@ Import Namespace = "MySql.Data.MySqlClient" %>
<script language="VB" runat="server">

Sub Page_Load(sender As Object, e As EventArgs)

    Dim myConnection  As MySqlConnection
    Dim myDataAdapter As MySqlDataAdapter
    Dim myDataSet     As DataSet

    Dim strSQL        As String
    Dim iRecordCount  As Integer

    myConnection = New MySqlConnection("server=localhost; user id=15secs; password=password; database=mydatabase; pooling=false;")

    strSQL = "SELECT * FROM mytable;"

    myDataAdapter = New MySqlDataAdapter(strSQL, myConnection)
    myDataSet = New Dataset()
    myDataAdapter.Fill(myDataSet, "mytable")

    MySQLDataGrid.DataSource = myDataSet
    MySQLDataGrid.DataBind()

End Sub

</script>

<html>
<head>
<title>Simple MySQL Database Query</title>
</head>
<body>

<form runat="server">

<asp:DataGrid id="MySQLDataGrid" runat="server" />

</form>

</body>
</html>

In the above script, I've highlighted the places where the script varies from one you would use to perform the same thing using Microsoft SQL Server or Access. As you can see there's really nothing new here. Instead of importing System.Data.SQLClient like we would for MS SQL, we import MySql.Data.MySqlClient. Oh and if at any point you need a reference for the MySql.Data.MySqlClient namespace, you're in luck... it ships with one.

Here's a screen capture of what the script above produces when run against the sample database and table we set up in this article. Like I said, it may not be pretty, but there's no reason it couldn't be. I'm just keeping it simple for illustration.

Conclusion

In part one of this article I illustrated how to:

  • Download and install the MySQL Database Server.
  • Configure the server.
  • Install MySQL Administrator to make managing the database easier.
  • Create a new database named "mydatabase".
  • Create a new table named "mytable" in that database.
  • Add a couple rows of sample data to that table.

This part illustrated how to do the following:

  • Add a new MySQL user.
  • Assign the new user the appropriate permisions to the database.
  • Connect to the MySQL server from .NET.
  • Build a simple ASP.NET page to query the database.

I hope this article helped ease your introduction to this great little database server. Whether you are upgrading from Access or starting from scratch, MySQL is certainly a viable option and despite the fact that it's open-source and not shipped from Redmond, it really does work great with .NET. And, with the addition of MySQL Administrator, management is no longer the source of nightmares that it used to be. Hopefully the next time you're looking for a database server to use as the backend for your .NET application, you'll at least consider using MySQL.



Comments

  • how to connect mysql 5.0 with asp.net

    Posted by Ravi on 03/26/2014 09:35am

    how to connect mysql 5.0 with asp.net

    Reply
  • Helpful

    Posted by luke on 01/06/2013 03:04pm

    This really helps clear things up, thank you!

    Reply
  • Great start

    Posted by Luiz Rebelo on 01/02/2013 03:46am

    This help me a lot, it´s a great start.. From here it's delete and update :P. Thanks

    Reply
  • missing pics

    Posted by apoorv on 11/28/2012 11:31am

    hey some of the pictures are missing. can you please replace them for a better read.

    Reply
  • Thanks..

    Posted by baris on 08/01/2012 09:18am

    hey..this article is really helpful..thanks buddy...

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

Top White Papers and Webcasts

  • As mobile devices have pushed their way into the enterprise, they have brought cloud apps along with them. This app explosion means account passwords are multiplying, which exposes corporate data and leads to help desk calls from frustrated users. This paper will discover how IT can improve user productivity, gain visibility and control over SaaS and mobile apps, and stop password sprawl. Download this white paper to learn: How you can leverage your existing AD to manage app access. Key capabilities to …

  • Live Event Date: September 17, 2014 @ 1:00 p.m. ET / 10:00 a.m. PT Another day, another end-of-support deadline. You've heard enough about the hazards of not migrating to Windows Server 2008 or 2012. What you may not know is that there's plenty in it for you and your business, like increased automation and performance, time-saving technical features, and a lower total cost of ownership. Check out this upcoming eSeminar and join Rich Holmes, Pomeroy's practice director of virtualization, as he discusses the …

Most Popular Programming Stories

More for Developers

Latest Developer Headlines

RSS Feeds