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.
As I've mentioned before... I thought part two would be the end of it, but
people kept reading the articles and the email kept pouring in. I finally
decided that I would put together a
third part in an effort
to address some of the questions raised in those messages. Part three
discussed some of the problems users were having with namespace errors,
provided a C# version of the ASP.NET 1.x query page, and showed how you
could connect to MySQL using the newer ASP.NET 2.0 data access syntax.
Just as with part three, the inspiration for this article once again
comes from email sent in by readers. The most common question
these days is "Why don't you discuss stored procedures?" It's not because I
don't like them or didn't think about it. The real reason behind this conspiracy
is simple. The original article used MySQL 4.1 (the current version at the time)
which did not yet include stored procedure support.
For the rest of this article, the screenshots and examples I give will assume
that you've followed the setup from parts one and two. If you choose not to
do so, please realize that you may need to change some things. That is unless
you happen to have a database named "mydatabase" and a user named
"15secs" with a password of "password". In which case...
you've got me a little scared. ;)
Oh and one last note... I did cheat a little and add two new rows to the table.
I was finding it hard to illustrate stored procedures with only two rows to return.
Anyway... if you're really trying to follow along exactly... our data set now looks like this:
What Are Stored Procedures and Why Should I Care?
Stored procedures live in the database and work with data, but they're
very similar to subroutines or functions in a programming language.
You write them ahead of time and then when you want one to run you call it.
You can also pass a stored procedure parameters and it can return results
just like a function.
I'm not going to spend much time explaining stored procedures because it's
outside the scope of this article. If you'd like to learn more about stored
procedures, there's plenty of information available on the web. A good place
to start is Wikipedia
and for a more MySQL specific resource you might want to check out:
MySQL 5.0 New Features: Stored Procedures.
There are lots of benefits to using stored procedures. I'm sure there are others, but
here are the big three that I've always used to convince people to use them.
They're fast. Aside from caching and other optimizations that the
database does, they're often faster because things like loops can be done right
in the database. This means you'll end up needing to move less of the actual
data to your application. This is a good thing because moving data around is relatively slow.
They're reusable. Once you write a stored procedure you can use it
over and over again. Even if you've got a hundred different web pages
that need to use it, you still only have to write it once.
They're stored with the data. Move your database and the stored procedures go with it.
Make a database change and you're more likely to remember to update a stored procedure
that lives right there with the data then a SQL query buried deep in a component or
on a web server that isn't even on the same machine.
Upgrading to MySQL 5.x
First things first... as I mentioned, stored procedures weren't available in versions
prior to MySQL 5.0. So, in order to start using them you'll need to make sure your server is
running at least 5.0. As of this writing,
MySQL 5.0 Community Server is the latest
production version. There are some beta versions available, but if you care about
your data at all... I'd recommend you leave those alone unless you know what you're doing.
The actual process of upgrading is not that interesting so I won't waste your time, so
let's just assume you've gotten through the upgrade and while you were at it you also
happened to upgrade to the latest version of
MySQL GUI Tools (includes the MySQL Administrator)
and the
MySQL Connector/Net.
They're both available as free downloads.
For those of you who really want to know, I'll be using MySQL 5.0 Community Server
version 5.0.45, MySQL Administrator version 1.2.12, and MySQL Connector/Net 5.0.8.1
for the rest of the article.
Our Sample Stored Procedures
Once again, I'm going to be using very basic examples. The focus here is not that you
learn to write stored procedures, but simply that you learn how they work from MySQL.
The easiest way to create and manage stored procedures is probably to use our old
friend the MySQL Administrator. Run MySQL Administrator, log in to your database server,
select the "Catalogs" option from the left hand pane, and then select
"mydatabase" in the list of "Schemata" that appears.
If you've installed the latest version of both MySQL and the MySQL Administrator, you should see
a "Stored procedures" tab. From here, creating a stored procedure is as easy as clicking the
"Create Stored Proc" button, giving the procedure a name, and entering the procedure's body in the
window.
Don't panic... I'm not expecting you to come up with your own sample stored procs for this
article... simply cut and paste the ones below into the pop-up windows and you should be fine.
GetRecordById:
CREATE PROCEDURE `GetRecordById`
(
IN parId INTEGER
)
BEGIN
SELECT id, int_field, text_field, date_field
FROM mytable
WHERE id = parId;
END
GetRecordsNewerThan:
CREATE PROCEDURE `GetRecordsNewerThan`
(
IN parTimestamp DATETIME
)
BEGIN
SELECT id, int_field, text_field, date_field
FROM mytable
WHERE date_field > parTimestamp;
END
GetRecordsWhereTextEquals:
CREATE PROCEDURE `GetRecordsWhereTextEquals`
(
IN parText VARCHAR(45)
)
BEGIN
SELECT id, int_field, text_field, date_field
FROM mytable
WHERE text_field = parText;
END
Now that we've created our stored procedures, we need some way
to run them and return the results.
In the next section we'll build an ASP.NET page to do just that.
In the mean time, if you want to test that your stored procedures are working
at the database level, you can use the "MySQL Query Browser" which should be
available from the "Tools" menu in "MySQL Administrator".
As you can see in the image above, executing a stored procedure by typing the
command into the box at the top should return the results in the pane below.
In case you can't quite make it out the two lines I typed were:
USE mydatabase;
CALL GetRecordsWhereTextEquals('World');
ASP.NET Page to Execute a Stored Procedure
Now that we've created and tested our stored procedures, we need to create an ASP.NET page
that will execute one and return any results.
Please note that although all the samples provided actually return data, in the
real world, there may not be any results. You can have stored procedures that
simply insert, update, or delete data, or maybe there were simply no records in the table that
matched your query. All the situations that happen with plain old SQL
commands can and do happen with stored procedures as well. I just didn't show any of
those situations because they don't work very well if you're trying
to show that something actually happened!
If you're at all familiar with stored procedures, the following code should seem quite
familiar. Aside from the objects being of the MySql variety, the code is the same as
it would be for a Microsoft SQL Server database.
' Call our stored procedure "GetRecordById" passing it
' the value of 1 for the id of a record to return.
Dim myConnection As MySqlConnection
Dim myCommand As MySqlCommand
Dim myParameter As MySqlParameter
Dim myDataReader As MySqlDataReader
' Create a connection to our MySQL database
myConnection = New MySqlConnection("server=localhost; user id=15secs; " _
& "password=password; database=mydatabase; pooling=false;")
' Create a command, set it as a stored proc, and give it the proc name
myCommand = myConnection.CreateCommand()
myCommand.CommandType = CommandType.StoredProcedure
myCommand.CommandText = "GetRecordById"
' Create a new parameter and set its name and value
myCommand.Parameters.Add(New MySqlParameter("parId", 1))
' Open the connection and use a DataReader to get our data
myConnection.Open()
myDataReader = myCommand.ExecuteReader()
' Bind the reader to our datagrid
dgStoredProc1.DataSource = myDataReader
dgStoredProc1.DataBind()
' Close our DataReader
myDataReader.Close()
' Normally you'd close the connection, but I'm gonna reuse it
'myConnection.Close()
' Call our stored procedure "GetRecordsNewerThan" passing it the
' value '2006-01-01 00:00:00' to get records from 2006 and later
' Create a new parameter and set its name and value
myCommand.Parameters.Add(New MySqlParameter("parTimestamp", _
"2006-01-01 00:00:00"))
' Left the connection open so just make the call to ExecuteReader
myDataReader = myCommand.ExecuteReader()
' Bind the reader to our datagrid
dgStoredProc2.DataSource = myDataReader
dgStoredProc2.DataBind()
' Close our DataReader and Connection
myDataReader.Close()
myConnection.Close()
End Sub
</script>
<html>
<head>
<title>Simple MySQL Database Query (Stored Procedures)</title>
</head>
<body>
<form runat="server">
<p>
Results from stored procedure "GetRecordById"
passed the parameter "parId" with a value of "1".
</p>
<asp:DataGrid id="dgStoredProc1" runat="server" />
<br /><br />
<p>
Results from stored procedure "GetRecordsNewerThan"
passed the parameter "parTimestamp" with a value of
"2006-01-01 00:00:00".
</p>
<asp:DataGrid id="dgStoredProc2" runat="server" />
</form>
</body>
</html>
I know it may seem long, but a lot of it is comments and I actually execute both the
"GetRecordById" and "GetRecordsNewerThan" procedures so a
good portion of the code is repeated twice.
Oh and here are the results:
I know it doesn't look like much, but as usual the goal was to get it working
and keep things plain and simple so as not to confuse the issue. I'm the last
person you'd come to in order to make it look pretty.
And please don't think that this is the only way to execute a stored procedure.
You can use whatever method you like. Do a web search for "ASP.NET stored procedure"
and you'll get back hundreds if not thousands of code snippets illustrating different methods...
most of which will work just as well with MySQL as they will with any other database.
You may need to tweak the syntax some, but with a little trial and error you should get the hang of it in no time.
Conclusion
As if you couldn't tell by the fact that I wrote the first three parts of this article,
I really think that MySQL is a great database platform and should be considered for anyone
in the market for a database. The fact that it lacked stored procedure and view support
was one of the major stumbling blocks for users trying to migrate from other platforms.
With that major shortcoming corrected, hopefully MySQL will go from the little database
that could to the big database that can.
I hope this part of the article has helped address some of your concerns about
stored procedures in MySQL. As always, 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.
While the .NET Framework made building ASP.NET applications easier then it had ever been in the past, .NET 2.0 builds on that foundation in order to take things to the next level. This article shows you to how to construct an N-Tier ASP.NET 2.0 Web application by leveraging the new features of ASP.NET 2.0 and SQL Server 2005.
[Read This Article][Top]
With the release of ASP.NET 2.0, Microsoft has greatly increased the power of ASP.NET by introducing a suite of new features and functionalities. As part of this release, ASP.NET 2.0 also comes with a host of new special files and folders that are meant to be used to implement a specific functionality. This article examines these new files and folders in detail and provides examples that demonstrate how to utilize them to create ASP.NET 2.0 applications.
[Read This Article][Top]
Alex Homer continues his detailed look at the major changes to the DataSet class. In this part, he looks at two features that allow developers to work with data in a more structured and efficient way when using the DataSet with a SQL Server 2005 database server.
[Read This Article][Top]
Alex Homer continues his detailed look at the major changes to the DataSet class. In this part, he looks at two features that allow developers to work with data in a more structured and efficient way when using the DataSet with a SQL Server 2005 database server. [Read This Article][Top]
In this article, Alex Homer looks at the changes between the version 1.x and version 2.0 DataSet and their associated classes, showing you how you can take advantage of the new features to improve your applications' capabilities and performance. [Read This Article][Top]
In this article, Alex Homer looks at the changes between the version 1.x and version 2.0 DataSet and their associated classes, showing you how you can take advantage of the new features to improve your applications' capabilities and performance. [Read This Article][Top]
In ASP.NET 2.0 and Visual Studio 2005, you can quickly program custom authentication pages with the provided Membership Login controls. In this article, Dina Fleet Berry examines the steps involved in using the Login control with a custom SQL Server membership database.
[Read This Article][Top]
In this article, Thiru Thangarathinam examines .NET 2.0's new ClickOnce deployment technology that is designed to ease deployment of Windows forms applications. This new technology not only provides an easy application installation mechanism, it also eases deployment of upgrades to existing applications. [Read This Article][Top]
With ASP.NET 2.0, Microsoft has made great strides in increasing developer productivity and has made implementing previously complex solutions relatively easy. Where this version of ASP.NET really shines, however, is in its new administrative tools that allow developers to spend less time managing the configuration of the servers and software and more time developing great code.
[Read This Article][Top]
Thiru Thangarathinam introduces ASP.NET 2.0's new TreeView control which provides a seamless way to consume and display information from hierarchical data sources. The article discusses this new control in depth and explains how to use this feature rich control in your ASP.NET applications. [Read This Article][Top]
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
