Cheap and Easy Database Solution for DotNet

As a developer I need to use a database for most applications I write. On my local pc, I have been using SQL Server, (From my MSDN subscription). The problem with SQL Server is that hosting is very expensive. For all my web apps so far I have used access database. Access is free to use, but is not very scalable when it comes to Web Applications.

I recently bought some server space with MySQL databases. I thought I would look at how easy it would be to write an ASP.Net web application with a MySQL database backend. It was suprisingly easier than I thought. Heres a quick run through of how I set it up. So far I have only tried this on my local pc, but I cant see why it would not work with my hosting.

1. Download MySql from http://www.mysql.com/
Make sure you go to the community link to get the free version. i set this up locally with all the default settings. I downloaded MySql 5.0.

2. Download the MySql GUI tools from the same site.
These tools include the Administration GUI that you can use to view tables.

3. Download the .Net MySQL drivers from the same site.
I am currently using ASP.Net 1.1 so I needed to download MySQL Connector/Net 1.0. These provide the MySQL.Data namespace from DotNet.

4. Download the SQLyog Community Edition from http://www.webyog.com/en/
This is similar to SQL Server Enterprise Manager and I found it quite easy to create a simple database in MySQL using it.


5. Create the database
Once Installed create a sample database (schema) using SQLyog called dotnettest. Add a table called user with the following columns:

intUserID, vchrUserName, vchrPassword

The following SQL will create it:

/*
SQLyog Community Edition- MySQL GUI v5.22a
Host - 5.0.27-community-nt : Database - dotnettest
*********************************************************************
Server version : 5.0.27-community-nt
*/


/*!40101 SET NAMES utf8 */;

/*!40101 SET SQL_MODE=''*/;

create database if not exists `dotnettest`;

USE `dotnettest`;

/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;

/*Table structure for table `user` */

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
`intUserID` int(10) unsigned NOT NULL auto_increment,
`vchrUserName` varchar(45) NOT NULL,
`vchrPassword` varchar(45) NOT NULL,
PRIMARY KEY (`intUserID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

/*Data for the table `user` */

insert into `user`(`intUserID`,`vchrUserName`,`vchrPassword`) values (1,'paul','test'),(2,'karl','test');

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;

6. Create the ASP.Net Application
I created a simple VB ASP.NET application.

6.1. Add the MySql.Data.dll to the references.
6.2. Add the sql namespaces to the webform


Imports System.Data
Imports MySql.Data.MySqlClient

6.3. Add a listbox to the webform called lbUsers
6.4. Add a button to populate the listbox
6.5. Add some global variables

Dim mobjConn As MySqlConnection
Dim mobjDataTable As DataTable
Dim mobjDA As MySqlDataAdapter

6.6. Add code to the button click event to populate the listbox when the button is pressed


Dim strConn As String
strConn = String.Format("server={0};user id={1}; password={2}; database=dotnettest; pooling=false", _
"localhost", "root", "***")

Try
mobjConn = New MySqlConnection(strConn)
mobjDataTable = New DataTable
Dim objCommand As New MySqlCommand("select vchrUserName from user", mobjConn)
mobjDA = New MySqlDataAdapter(objCommand)

mobjDA.Fill(mobjDataTable)
lbUsers.DataSource = mobjDataTable
lbUsers.DataTextField = "vchrUserName"
lbUsers.DataBind()


Catch ex As MySqlException
Response.Write(ex.Message)
End Try

Thats it!

The thing I noticed was that once you have the driver dll, most of the code is very similar to the standard SQL Server code. All existing code can be very easily updated to support SQL server.