Top Linux Links You Must Click On


An Introduction to MySQL, LAMP Stack and Microsoft Windows Development
Use a free alternative to SQL Server or Oracle

When open source software is mentioned, one of the first thoughts that comes to mind is the LAMP stack (Linux, Apache, MySQL, and PHP/Perl/Python). While these products are not generally associated with Windows development, at least one of them is certainly well suited for Windows development, particularly when using .NET. The MySQL database server is an open source relational database developed by MySQL AB. MySQL is available for Windows, Linux, UNIX, and the Macintosh operating system. Using MySQL with Windows has never been easier with .NET and ADO.NET.

Installing MySQL became one of the easiest and most painless server installs out there with version 4.1. You can download the installer for a variety of operating systems at http://dev.mysql.com/downloads/mysql/4.1.html. The setup wizard is fantastic. A series of dialog boxes asks you some simple questions to configure your server: where to install the server, the root password, the typical load expectation, etc. During the setup, the installer even correctly diagnosed that my firewall was blocking access and suggested that I open up port 3306 to allow MySQL to run. The actual time that it took to do the installation was approximately seven seconds on my modest Athlon XP 1800+ 512MB of RAM home machine.

After installing MySQL server there are a few other tools that I recommend acquiring to make your life easier. With SQL Server you would use the Enterprise Manager GUI for data manipulation and database administration, and MySQL is no different. For query capabilities, MySQL offers the MySQL Query Browser (http://dev.mysql.com/downloads/query-browser/1.1.html), which is a relatively new tool. The Query Browser has tons of great features: tabbed query windows, a graphical SQL statement builder, built-in SQL syntax help, bookmarks, and query history. The user interface for the Query Browser has received a much-needed upgrade from its predecessor, the MySQL Control Center. So, for data access and manipulation we have the MySQL Query Browser - what about database administration? For this MySQL provides another tool, the aptly named MySQL Administrator (http://dev.mysql.com/downloads/administrator/1.1.html). The MySQL Admin-istrator gives you all of the func- tionality you'll need to keep your server running smoothly. From here you can configure the server, start/stop the server, modify startup variables, and toggle certain features of MySQL. User administration is also done within MySQL Administrator. You can add users, remove users, and modify permissions up to the database level. MySQL Administrator, like MySQL Query Browser, has a sophisticated user interface that is stereotypically absent from most open source software.

Let's discuss some differences between MySQL and SQL Server. I'll mention right off the bat that there are certain core features that MySQL lacks (for now!) that SQL Server offers. Stored procedures, triggers, views, and transactions are all features that SQL Server users have come to know and love, and they will be disturbed to know that in the current release of MySQL these features are nowhere to be found. Calm down, take a deep breath. The 5.0 release of MySQL will have stored procedures, triggers, AND views. There are some things that MySQL has that SQL Server is lacking as well. Take the LIMIT keyword, for example, which makes paging your data MUCH simpler. Nobody claims that MySQL is a silver bullet for databases; you still need to pick the one that best suits your needs. However, you'll never be able to beat the price of MySQL.

SQL Server has many custom functions, such as the string manipulation functions, and many of these appear in MySQL as well. SQL Server has the LTRIM() and RTRIM() functions that remove white space from the left and right respectively, and MySQL has LTRIM(), RTRIM(), and TRIM() which remove white space from the left, right, or both, respectively. MySQL also has functionality that SQL Server does not. My personal favorite is the LIMIT keyword. The LIMIT keyword can be added to SELECT statements to internally page through the result set before it comes back from the server. Its definition is this:

LIMIT startIndex, count

Here is an example of its usage that returns 50 customers, starting at index 0:

SELECT * FROM customers LIMIT 0, 50

Here is another example that retrieves 100 customers, starting at the 300th customer in the list:

SELECT * FROM customers LIMIT 300, 100

This is something that I have longed for in SQL Server, it makes paging so easy and intuitive - so much nicer than the TOP * syntax that SQL Server uses.

Another feature that MySQL has that SQL Server doesn't is the ability to back up a database at the SQL statement level. Using the MySQL Administrator mentioned earlier, you have several backup options, one of which is a huge .sql file of all of the records of each table. This is such a convenient way of making a backup because it enables you to easily move data from MySQL to SQL Server, Oracle, or even Access with few modifications to the syntax. It also makes database restoration a breeze because you can add the database schema right in line with the data.

MySQL also has export options as an SQL language extension. What this means is that you can export any SELECT statement directly to a text file on the file system. The following query shows an example of how this works:

SELECT * INTO OUTFILE 'C:/customer_file.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM customers;

This query will select all customers from the customers table and write them to the file specified. Each column will be delimited by a comma, with the column enclosed by a double quote if the column value happens to contain a comma. Each line will have a new line character ('\n') placed at the end. Thus the export options are easy to implement, in addition to being customizable. In the previous query you can see where I've specified the characters to separate the data and you can change these to suit your needs.

Once you have the MySQL server set up and running, it's time to get our hands dirty and get our .NET applications talking to it! There are a few options for connecting to a MySQL server from .NET. One option is using the System.Data.Odbc namespace in the .NET framework, and another is using the MySQL native provider that MySQL distributes. We're going to go over both of these options to show how we can use our new database server in our applications.

In the 1.0 release of the .NET framework, Microsoft did not include native ODBC support. They ended up releasing an add-on to the framework (www.microsoft.com/downloads/details.aspx? FamilyID=6ccd8427-1017-4f33-a062-d165078e32b1&displaylang=en ) that contained the Microsoft.Data.Odbc namespace that contained the ODBC classes. When version 1.1 of the framework was released, ODBC got first-class treatment and was included in the System.Data namespace with the other data providers. In the System.Data.Odbc namespace, you will find the usual suspects:

  • OdbcConnection
  • OdbcCommand
  • OdbcDataReader
  • OdbcDataAdapter
These classes implement the same interfaces that you are used to from the System.Data.OleDb namespace, so the usage pattern will be familiar if you've used other data providers in .NET. In order to use the ODBC classes with MySQL, you must install the MySQL ODBC driver (http://dev.mysql.com/downloads/connector/odbc/3.51.html).

The connection string you will use to connect to MySQL will look like other ODBC connection strings that you may have seen, just with a new driver - namely, the MySQL ODBC driver. Here is a sample connection string that you might use:

string connectionString = "DRIVER={MySQL ODBC 3.51
Driver};SERVER=localhost;DATABASE=northwind;USER=user;PASSWORD=pass;";

About Ben Reichelt
Ben Reichelt is a software developer for Magenic Technologies Inc., a software consulting company based in Minneapolis that specializes in Microsoft solutions. He has been working in software for three years and lives in Saint Paul, MN with his wife Erin. You can catch up with Ben by reading his blog at http://codebetter.com/blogs/ben.reichelt.

In order to post a comment you need to be registered and logged in.

Register | Sign-in

Reader Feedback: Page 1 of 1

Don't forget about EMS MySQL Manager Lite Edition (completely free for *commercial* use as well!) which can be found at www.sqlmanager.net. Nice article though for beginners.

An Introduction to MySQL, LAMP Stack and Microsoft Windows Development. When open source software is mentioned, one of the first thoughts that comes to mind is the LAMP stack (Linux, Apache, MySQL, and PHP/Perl/Python). While these products are not generally associated with Windows development, at least one of them is certainly well suited for Windows development, particularly when using .NET. The MySQL database server is an open source relational database developed by MySQL AB. MySQL is available for Windows, Linux, UNIX, and the Macintosh operating system. Using MySQL with Windows has never been easier with .NET and ADO.NET.


  Subscribe to our RSS feeds now and receive the next article instantly!
In It? Reprint It! Contact advertising(at)sys-con.com to order your reprints!
ADS BY GOOGLE
Subscribe to the World's Most Powerful Newsletters