In this series of articles, I want to introduce you to using MySQL 5.0 with ASP.NET. I hope to cover the most common uses for MySQL, as well as take advantage of some of the new features in version 5.0. To do this, we will look at creating a database of Bible verses and ASP.NET pages to view and manage the verses.
To begin development, you will need to have the following tools.
Required Components:
- .NET Framework 1.0 or higher
- MySQL 5.0 database server
- MySQL Connector/Net 1.0.6 or higher
Optional Components:
- MySQL Query Browser 1.1.15 or higher
- MySQL Administrator 1.1 or higher
- Visual Studio 2002 or higher
MySQL database server comes with command-line utilities to administer and develop MySQL. However, MySQL Query Browser and MySQL Administrator are great utilities that will make your life much easier. Visual Studio is not required, but certainly makes developing ASP.NET applications much easier. Alternatively, you can use the free ASP.NET Web Matrix or simply create your ASP.NET web pages using any text editor.
Installing each of these components is outside the scope of this article. Please refer to the documentation provided with each for installation and configuration instructions.
Setting up the Database
Here are the scripts you’ll need to create the verse schema (database). To execute the scripts, you’ll need to use the mysql command line utility or MySQL Query Browser application.
CREATE DATABASE versedb;
CREATE TABLE `verse` (
`verse_id` int(10) unsigned NOT NULL auto_increment,
`verse_text` varchar(1024) NOT NULL default '',
`verse_ref` varchar(50) NOT NULL default '',
PRIMARY KEY (`verse_id`)
)
As you can see, we are going to work with just a single table named verse to start with. Before we can get data out of MySQL, obviously we have to put some data in. Use the following scripts to add two verses to the table.
INSERT INTO `verse` (`verse_text`,`verse_ref`) VALUES
('And God is able to make all grace abound to you, so that in all things
at all times, having all that you need, you will abound in every good work.',
'2 Corinthians 9:8'),
('Whatever you do, work at it with all your heart, as working for the
Lord, not for men, since you know that you will receive an inheritance from
the Lord as a reward. It is the Lord Christ you are serving.',
'Colossians 3:23-24');
Setting Up the Web Site
This article assumes you will set up a virtual directory on your local computer or you will upload the examples to a Web host that supports ASP.NET. Setting up the test web site locally is outside the scope of this document. If you plan to develop and debug ASP.NET on your local computer and are not already familiar with setting up Internet Information Server (IIS) or creating virtual directories, you can use the links provided on the Online Resources page to find tutorials that cover this subject.
Connecting to MySQL from ASP.NET
As mentioned before, you will need to download and install the MySQL Connector/Net ADO.NET library. Once you’ve added the MySql.Data.dll assembly as a reference to your project or placed it in your /bin folder, you are ready to start writing code to access MySQL. Here is an example of connecting to MySQL and retrieving all of the rows from the verse table into a DataSet.
// Connection string for a typical local MySQL installation
string cnnString =
"Server=localhost;Port=3306;Database=versedb;Uid=root;Pwd=MySecretPassword";
// Create a connection object and data adapter
MySqlConnection cnx = new MySqlConnection(cnnString);
MySqlDataAdapter adapter = new MySqlDataAdapter();
// Create a SQL command object
string cmdText = "SELECT * FROM verse";
MySqlCommand cmd = new MySqlCommand(cmdText, cnx);
// Create a fill a Dataset
DataSet ds = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(ds);
// Bind the DataSet
// ... Place your databinding code here ...
If you’re already accustomed to data binding in .NET, you should immediately recognize the similarities. The only difference is you use the MySqlXxx classes instead of the OleDbXxx or SqlXxx classes.
Hello World…
The following code provides a simple yet complete example of connecting to MySQL and displaying the data retrieved from the database. To use this code, add a new Web Form (.aspx) to your ASP.NET project, copy and paste, change the connection string to match your MySQL database user name and password, and save.
<%@ Page language="c#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="MySql.Data.MySqlClient" %>
<html>
<head>
<script runat="server" type="text/javascript">
void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindVerses();
}
}
private void BindVerses()
{
try
{
// Connection string for a typical local MySQL installation
string cnnString =
"Server=localhost;Port=3306;Database=versedb;Uid=root;Pwd=myPassword";
// Create a connection object and data adapter
MySqlConnection cnx = new MySqlConnection(cnnString);
MySqlDataAdapter adapter = new MySqlDataAdapter();
// Create a SQL command object
string cmdText = "SELECT * FROM verse";
MySqlCommand cmd = new MySqlCommand(cmdText, cnx);
// Create a fill a Dataset
DataSet ds = new DataSet();
adapter.SelectCommand = cmd;
adapter.Fill(ds);
// Bind the DataSet
VerseRepeater.DataSource = ds;
VerseRepeater.DataBind();
}
catch (Exception ex)
{
lblError.Text = ex.Message;
}
}
</script>
<title></title>
<style type="text/css">
body { font-family:Verdana, Arial, Sans-Serif; font-size:small; }
h1, h2, h3, h4, h5 { font-family:Trebuchet MS, Verdana, Arial, Sans-Serif;
}
input, textarea { font-family: Verdana, Arial, Sans-Serif; font-size:small;
}
.Error { font-weight:bold; color:#c00; }
.VerseList { width:400px; }
.VerseHeader {
padding:5px;
border:1px solid #999;
background-color:#ddd;
font-weight:bold;
}
.VerseText {
font-family: Trebuchet MS, Verdana, Arial;
font-size: small;
padding:5px 5px 0px 5px;
border-left:1px solid #999;
border-right: 1px solid #999;
}
.VerseRef {
font-family: Arial, sans-serif;
font-style:italic;
font-size: x-small;
text-align:right;
padding:5px 15px 0px 5px;
border-left:1px solid #999;
border-right: 1px solid #999;
border-bottom:1px solid #ccc;
}
.VerseFooter {
border-left:1px solid #999;
border-right: 1px solid #999;
border-bottom:1px solid #999;
}
</style>
</head>
<body>
<form runat="server" method="post" id="Form1">
<p><asp:label cssclass="Error" enableviewstate="False" runat="server"
id="lblError"></asp:label></p>
<div class="VerseList">
<asp:repeater runat="server" id="VerseRepeater">
<headertemplate>
<div class="VerseHeader">Verse List</div>
</headertemplate>
<itemtemplate>
<div class="VerseText"><%# DataBinder.Eval(Container.DataItem, "verse_text") %></div>
<div class="VerseRef">--
<%# DataBinder.Eval(Container.DataItem, "verse_ref") %>
</div>
</itemtemplate>
<footertemplate>
<div class="VerseFooter"><img width="1" height="2" alt="" src="images/pixel.gif" /></div>
</footertemplate>
</asp:repeater>
</div>
</form>
</body>
</html>