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

By Ziran Sun

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 http://dev.mysql.com/downloads/index.html. 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. The download is just under 35 MB so over it shouldn't take too long to get via any resonable internet connection.

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 process.


Figure 1


Figure 2


Figure 3


Figure 4

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.


Figure 5


Figure 6

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 resources.


Figure 7


Figure 8

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.


Figure 9


Figure 10


Figure 11


Figure 12

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.


Figure 14

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.


Figure 15

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".


Figure 16

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.


Figure 17

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.


Figure 18

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:


Figure 19

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.


Figure 20

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.


Figure 21

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.

Conclusion

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.

 



Comments

  • all images are broken

    Posted by James on 06/11/2013 05:17am

    all images are broken...sorry

    Reply
  • Stil og design ense deres øyne

    Posted by mantouhmmm on 06/03/2013 10:58pm

    [url=http://www.beatsbynopro.manifo.com/]Beats By Dre[/url] Dette Tesla programmer produsere betydelige elektriske strømforsyninger for bare en aktiv, undistorted støy. Dette fornuftig, vogue mønster fungerer ved hjelp av premium kvalitet til å produsere en hodetelefon som er sikkert både like praktisk å bruke og også solid. Denne sålen ensidig kabeltilkobling kan sikkert motstå mange i det minste 20 kg gjør den sterk i tillegg til spesielt designet for dag-til-dag arbeid med. Dette extractible overhoder i tillegg til foranderlig, dele lavpris iBeats hodebånd sikre at dette Beats By Dre Expedition hodetelefoner forbli sterkt i tillegg til å holde på å være rolig muligens mens i lengre sykluser praktiske. Dette svingbare hodet k-kopper også gjøre det mulig for "one-ear" tilsyn. [url=http://www.beatsbynopro.manifo.com/]Beats By Dre[/url] Dette rolig, beskyttelser utskiftbart hode sammen med smørbare hodebånd forsikrer bemerkelsesverdig letthet i mange år av sin tid sammen med en helt beskyttet sunt. Dette svingbare hodet kopper Bests ved hjelp av Dre Expedition gjør det mulig for sjekkliste å ha bare ett beregnet for røde sammen med den leverte saken sørger for rask bevegelse. Attributter forbundet med Bests ved hjelp av Dre Expedition En slags ytterste støy etterspørsel nivåer samt en overdreven bakgrunn forstyrrelse tilbakegang tilsier realisere dette hodetelefoner suksess muligens med øredøvende omstendigheter. [url=http://www.monsternobeats.350.com/]monster beats[/url] Internett er et sted der flere og flere produsenter og slo beslutningstakere samles for å tillate nye artister til å komme og kjøpe sine beats. Det er hundrevis og hundrevis av beat-selger nettsteder på nettet som imøtekomme til kunstnere som har talent, men kan ikke ha et veldig stort budsjett til å kjøpe instrumentaler. Med det blir sagt, er det også mange beat-nettsteder som ikke er satt opp riktig, og kan lage musikk artist nest gjette når det kommer til å kjøpe en instrumental fra noen av disse sites.First du må være i stand til å finne en "FAQ" side på en beat nettsted for å besvare noen av dine spørsmål du måtte ha om du er usikker på noe som er markedsført på nettsiden. Du bør være i stand til å lese alt klart og presist. Hvis du har noen spørsmål bør det være en måte å komme i kontakt med produsenten av Beats By Dre Mixr

    Reply
  • Shorter blog post exposes the undeniable facts about gucci and ways it might effect everyone.

    Posted by emeseesip on 05/07/2013 10:10pm

    The Biggest And Most Comprehensive gucci Instructions You Ever Read Or else Your Money Back [url=http://www.guccija.biz/]gucci 財布[/url] Geez, incredible merchandise. Your corporation must see nike straight away when it is still in stock : ) [url=http://www.guccija.biz/]グッチ 長財布[/url] adidas will help every one of us by integrating a handful of unique capabilities and options. Its a unvaluable item for every enthusiast of adidas. [url=http://www.guccija.biz/]グッチ トートバッグ[/url] Third party post brings out Ten completely new things of nike that no company is covering. [url=http://www.chanelja.biz/]シャネル 財布[/url] That explains why no one is dealing with adidas and therefore what one ought to execute as we speak. [url=http://www.chanelja.biz/]シャネル チェーンウォレット[/url] Newbie questions on nike replied to and reasons why you really need to read through each concept in this article. [url=http://www.chanelja.biz/]財布 chanel[/url] The essential principles of the nike that you are able make money from commencing today.[url=http://www.nikeja.biz/]nike[/url] Precisely how to understand all sorts of things there is to find out surrounding gucci in Few easy steps.

    Reply
  • A trustworthy double turn on adidas

    Posted by Updatatweda on 05/05/2013 10:54pm

    O [url=http://www.adidaskutuja.com/]adidas スニーカー[/url] wrHoc NrkXnf JobGhd BygF [url=http://www.adidaskutuja.com/adidas-originals-c-4.html]adidas originals[/url] mjYwdYca TlkHzpFmqB [url=http://www.adidaskutuja.com/adidas-superstar-c-2.html]アディダス スタンスミス[/url] jr ZdlTey [url=http://www.adidaskutuja.com/jeremy-scott-c-5.html]adidas jeremy scott[/url]IdtIwl NbqDvj

    Reply
  • Pictuers are not visible....

    Posted by Pankaj on 05/01/2013 10:24pm

    Please give the correct path to pics by which can be visible.

    Reply
  • More concessions with herveleger, more flabbergast!

    Posted by Mrtopflinam on 04/29/2013 05:16am

    girlfriendpetite shaverexaltationmove along disintegrate nigheulogisticmercantilism

    Reply
  • Mysql to asp.net connection step by step

    Posted by chitranjan on 02/11/2013 10:06pm

    Hello Friends if you want to make a project for use mysql database with asp.net then use this link for getting very essay example step by step http://dotnetnukes.blogspot.in/

    Reply
  • Good info

    Posted by Pharmd652 on 11/27/2012 03:25am

    Hello! fegddeg interesting fegddeg site! I'm really like it! Very, very fegddeg good!

    Reply
  • title....

    Posted by mail address will not be published. All fields are required. on 04/18/2012 12:14am

    Please change the title of this article to: How to install MySql. It make no mention on how to make it work for asp.net!

    • Part 2

      Posted by Andy on 08/14/2012 08:34am

      You would have found the second part if you would have clicked on the authour's name ;) here is the link to the second part: http://www.codeguru.com/csharp/.net/net_data/article.php/c19493/A-StepbyStep-Guide-To-Using-MySQL-with-ASPNET--Part-2.htm

      Reply
    Reply
  • WRONG TITLE!

    Posted by Cesar on 04/11/2012 10:21am

    Please change the title of this article to: How to install MySql. It make no mention on how to make it work for asp.net!

    • Mysql to asp.net connection step by step

      Posted by chitranjan on 02/11/2013 10:08pm

      use this link and get how to connect mysql to asp.net step by step if link not clickable then copy and past on browser and press enter http://dotnetnukes.blogspot.in/

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

Top White Papers and Webcasts

  • The explosion in mobile devices and applications has generated a great deal of interest in APIs. Today's businesses are under increased pressure to make it easy to build apps, supply tools to help developers work more quickly, and deploy operational analytics so they can track users, developers, application performance, and more. Apigee Edge provides comprehensive API delivery tools and both operational and business-level analytics in an integrated platform. It is available as on-premise software or through …

  • 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