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

Document Sample
A Step-by-Step Guide To Using MySQL with ASP.NET Powered By Docstoc
					A Step-by-Step Guide To Using MySQL with ASP.NET - Part 1

By Ziran Sun        Rating: 4.3 out of 5

Rate this article

# print this article

# email this article to a colleague

# suggest an article


$112B in Govt IT Opportunities by 2015

Maximize Your Market Position: FREE Summary of Federal IT Forecast 2010-2015

IT Resellers Earn 25% Monthly Commission

With KineticD Award Winning Backup Solution Perfect for SMB Clients. Try It Now

AutoCAD LT - 30-Day Trial

Professional Starts Here. See what AutoCAD LT 2011® Can Do for You.


Back in the days of classic ASP, if you were building a database-driven web site, your choice was either
to invest a lot of money to get a copy of Microsoft SQL Server (or some other enterprise-ready database)
or invest a lot of time finding a way to deal with the performance and scalability limitations of Microsoft
Access. Luckily these days there's another viable alternative: MySQL.
What is MySQL?

MySQL is an open source database server. While many organizations may choose to purchase a
commercial version of the product, the GNU General Public License (commonly known as the "GPL")
ensures that the source code will remain available and therefore the software can be used free of
charge for those willing to forego official support and support it themselves.

For more information, see the MySQL website.
Step 1 - Download and Installation

As with most any software, the first step to getting up and running with MySQL is to obtain and install
the product. You can download the setup file from As of
this writing the current version is MySQL 4.1 so that's the version I'll be using for the rest of this article.
Unless you have a reason to do otherwise, I'd recommend just downloading the pre-compiled binaries
for you current platform. In this case I'll be installing on a Windows XP machine so I downloaded the
normal Windows version which includes the installer. T
Mayor abram

he download is just under 35 MB so over it shouldn't take too long to get via any resonable internet

The installation is straight-forward and caused no problems for the very modest laptop I installed it on.
Just so you have an idea of what to expect, I'm including screen captures of several steps of the setup

As you can see in the screen capture above, at the end of the setup process the installer asks if you'd like
to configure the MySQL Server. If you choose to do so, it will launch the MySQL Server Instance
Configuration Wizard which brings us to the next step in the process.

Step 2 - Configuration

The MySQL Server Instance Configuration Wizard makes configuring your server really simple.
Configuration is straight forward and I just used the default setting for most everything.

If your're installing on a dedicated database server or a shared server you should obviously select the
appropriate choice. Since I'm installing on my laptop, I simply left the server type as "Developer
Machine". This setting won't offer the same performance, but it also won't use as many system

I made sure to enable TCP/IP networking in order to allow the web server to connect to the database
when we get to building a web page to query the database. If you'll be running the database and web
servers on the same physical computer then you can disable this option to prevent access to the
database via the network.
Step 3 - MySQL Administrator

While it's certainly not required, I highly recommend you download and install the MySQL
Administrator. It's a great little application that provides a GUI to help you manage your new database
server. While you can get up and running using only the command line, for users who are used to using
Windows applications and wince at the thought of editing configuration files by hand or using a
command prompt it's almost a necessity. For the rest of this article, I'll assume you've installed MySQL
Administrator and I'll be using it for illustration.

Step 4 - Creating a Database

In order to create our database, we first need to connect to the server. Run MySQL Administrator and
login to your server using the password you set during installation.

You'll then want to select the "Catalogs" item at the bottom left of the MySQL Administrator window.
This should bring up a list of the current databases on the server (There should be two of them: "mysql"
and "test"). If you right-click in the small window where they are listed you should get the option to
"Create New Schema".

You'll then be prompted to enter a name for the new database. I'll be using "mydatabase" as the
example for the remainder of this article.

Once created, your new database will appear in the Schemata list along with the other databases on the
server. Selecting it from this list will bring up its details in the right hand pane.

There's not much to see because the database is still empty... so let's put something in it.

Step 5 - Creating a Table

To create a table simply click on the "Create Table" button. This brings up the following dialog box:
As you can see, I've named the table "mytable" and added 4 fields to it: an auto-incrementing primary
key id field, an integer field, a text field, and a date/time field.

When you're done making changes, you simply click the "Apply Changes" button. A window that looks
something like the one below will pop up showing you the SQL that will be executed and asking you to
confirm that you want to save changes to the table design.

At this point, we've got a database named "mydatabase" that contains a table named "mytable". Now
all we need is to add some rows of data to the table.

Step 6 - Adding Data

In the real world, data in your table would probably come in via your application. To get some sample
data into our table, I'm simply going to insert a few lines by hand. To do this I'll use the MySQL
Command Line Client. If you're still in the MySQL Administrator you can access the command line from
the "Tools" menu (Tools -> MySQL Command Line Client) otherwise you can run it from the MySQL
group on the Start Menu.

The first command in the screen above tells the server which database I want to be working in. The
second and third commands simple insert some dummy data and are the same except for the the
differences in the data being inserted.

Now we've got two sample rows of data in our table. At this point our database server is up and running
with a database, a table and even some data.

This article illustrated how to do the following:
  * 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.

Next time we'll look at adding users to the database server, the different options available for
connecting to your new database from .NET, and how to build a basic ASP.NET page that performs
queries against the database.
Continue to Part 2 -->

Rate This Article

Not Helpful              Most Helpful


Supporting Products/Tools

        Proposion N2N

Proposion N2N connects Microsoft .NET applications to Lotus Notes and Lotus Domino databases. This
ADO.NET managed data provider allows you to perform blindingly fast queries and updates of Notes
data from ASP.NET pages, .NET web services, Windows, or Mobile applications. An innovative SQL-like
query language leverages the unique features of Notes and makes collaborative software accessible to
relational database programmers.


Other Articles

        Sep 15, 2005 - Building an Image Keyword System

          Unlike text-based file formats image files aren't made up of words, which makes searching for
an image file by keyword difficult. Instead of being able to simply open the file to see what it contains,
we're stuck looking at the text around it and other metadata to determine the image's meaning. In this
article, Ziran Sun shows you how to build a simple database-based image keyword system that allows
you to associate keywords with images and use these keywords to make finding images easier.

[Read This Article] [Top]

        Apr 7, 2005 - A Step-by-Step Guide To Using MySQL with ASP.NET - Part 2

      In the second part of of his article on using MySQL with ASP.NET, Ziran Sun covers how to add a
new MySQL user to the database server, assign the user the appropriate permissions, connect to the
database, and build a simple ASP.NET page to perform a query.

[Read This Article] [Top]

        Jan 27, 2005 - Moving a Database from SQL Server 7.0 to SQL Server 2000

       Moving or copying a SQL Server database from one machine to another requires a lot of
preparation in order to ensure a smooth transfer. In this article, Dina Fleet Berry examines the different
methods and highlights the different issues associated with each of them.

[Read This Article] [Top]

        Jan 6, 2005 - Debugging a SQL Stored Procedure from inside SQL Server 2000 Query Analyzer

        There are many times when using SQL Server 2000 Query Analyzer to debug SQL statements is a
better choice than debugging in Visual Studio .NET. In this article, Dina Fleet Berry explains why and
walks you through the debugging process step-by step.

[Read This Article] [Top]

        Nov 24, 2004 - Persisting .NET Objects to SQL Server Using SQLXML and Serialization

         As a follow up to his article on retrieving objects from SQL Server using SQLXML and
serialization, Gianluca Nuzzo discusses saving objects back to SQL Server using a schema definition file
and updategrams.
[Read This Article] [Top]

        Sep 14, 2004 - Transaction Processing in ADO.NET 2.0

         One area that stands out when comparing ADO.NET 1.x to ADO.NET 2.0 is transaction
processing. Bill Ryan shows just how easy transaction processing has become with the TransactionScope
object in ADO.NET 2.0.

[Read This Article] [Top]

        Sep 8, 2004 - Custom Object Data Binding with .NET

        Developers often use brute force coding to marshal data between the GUI and application
objects. In this article, Luther Stanton explains how to use .NET's out-of-the box data-binding
functionality to make this job much easier.

[Read This Article] [Top]

        Sep 2, 2004 - Queue MSMQ Messages from SQL Server

         Learn how to create a console application to queue a message in Microsoft Message Queuing
(MSMQ) and then use an extended stored procedure to call the console application from a SQL Server

[Read This Article] [Top]

        Aug 30, 2004 - Tuning Up ADO.NET Connection Pooling in ASP.NET Applications

       Connection pooling increases the performance of Web applications by reusing active database
connections instead of creating a new connection with every request. This article shows how to monitor
the connection pool, diagnose a potential problem, and apply the appropriate fix.

[Read This Article] [Top]

        Jul 13, 2004 - Retrieving Objects from SQL Server Using SQLXML and Serialization

        This article will describe how to design a data access layer for a set of entities. You'll learn how
to write an XSD schema and design two simple helper classes -- one for reading an XML stream from SQL
Server using SQLXML and another for deserializing the XML stream.

[Read This Article] [Top]

Mailing List

Want to receive email when the next article is published? Just Click Here to sign up.

Support the Active Server Industry

The Network for Technology Professionals



Copyright 2010 QuinStreet Inc. All Rights Reserved.

Legal Notices, Licensing, Permissions, Privacy Policy.

Advertise | Newsletters | E-mail Offers


Whitepapers and eBooks

Helpful Cloud Computing Resources

Article: An Introduction to Hyper-V Virtualization

                Microsoft PDF: Fact-Based Comparison of Hosted Services: Google vs. Microsoft



Ensuring Performance Meets Business and Web User Needs


Downloads and eKits

HP PartnerONE | SolutionsINFINITE


Tutorials and Demos

Guide: Recommendations for Implementing Cloud Security

Article: Explore Application Lifecycle Management Tools in Visual Studio 2010 Hot List: Get the Inside Scoop on IT and Developer Products

               New Security Solutions Using Intel(R) vPro(TM) Technology

All About Botnets


Shared By:
Description: A Step-by-Step Guide To Using MySQL with ASP.NET