Title: Let's Do C# and MySQL - Part 1 - MySQL

Before I start, this is going to be fun. Understand? Fun. Not dull, but fun. I intend writing this in a banana suit with my feet in a bucket of warm rice pudding^[1]. That's how much fun it's going to be!

What is the point of the series of articles? I intend it to be a learning progression to take you from the very basics of an application through to implementation. I've chosen to write a small MySQL front end in C# to do this. There will be a progression from setting up the MySQL database, testing using a command line application, moving it over to a System.Windows.Forms application and then adding in functionality. I will also look at using db4o. The question is though - why C#?

The MySQL API is very well documented and is accessible through many different programming languages with the minimum of fuss. I've chosen C# as I'm yet to find any really good tutorials for using the language. Sure, MSDN contains a great deal of information, but without the linking arguments, the information is almost context less.

You will need a copy of MySQL installed on your computer to benefit from this series of articles and some form of C# compiler. The code works fine with both Mono and Microsoft C# - I have not tested the C# using gnu.NET. When compiling the code, Microsoft users should use csc and Mono users mcs. Any additional dlls to be used will be listed as -r:System.Drawing -r:System.Windows.Forms (though if you are using Visual Studio, these are normally be hidden).

The first thing we'll need is a working MySQL database. I'm not going to assume that you've set up a database before now, but there are effectively two things you need to decide. What's going into it and how it's laid out. Take the following list of elements:

I could have one very large database which would contain parameters such as the period number, CAS registry, element block, shell filling, picture URL, if it is radioactive, man made and so on. This would be fine, but very messy and a pain to administer too.

A simpler solution is to break the database down and to do that, we need a couple of generic categories.

The list above gives three types of element: metal, gas and man-made. They are good enough and more can be added as and when I need them (such as trans-metal, halide and trans-actinide). But what of the actual data? For that, a second table is created which is linked to the generic first table. In the second table, the specifics of the element are held (such as discovery, relative atomic weight, shell filling, etc.).

Graphically, the database looks like this.

For the Element Info column, ID represents the classification ID - for example, metal = 1, gas = 2. Name is the generic name, Description a generic description and Image a generic picture. Element Info links to The Element in what is known as a 1 to many (sometimes known as 1 to infinity) relationship (1 item in the generic table can link to many items in the second table, as long as they match the type of the generic item). The Element list is straight forward to understand which again is linked to a third database (again via a 1 to many relationship) for other information.

Okay, what you should now be asking is why the other information should be in its own table?

When you think about it, the answer to that is simple. Say a new element is discovered (or created). While it is entirely possible to enter all of the details into one big table, discovery of elements and the dates are sometimes contested (especially for the trans-actinides where there was originally contested names for elements 104 to 111) - after the dust settles, it may be that the original name has to be changed, the discoverer changed and a few other details to boot. It is far simpler to have all of the "additional" information elsewhere so that the main database is still correct.

When considering a MySQL database, it could also be thought of as spreadsheet in style with columns and rows. A column will always be of a particular type which will be associated with a row. For example

The white blocks indicate the type associated. It's not quite how things are done, but is a convenient way to represent the database. (In reality, it's actually a two column table with the ID in column 1 and the type in column 2 - my representation is just handy - well, to me at least!)

If you think of the MySQL server as a form of computer, you can quickly see that objects within the database can be thought of as variables and all variables need to be of a specific type (such as int, char and float). MySQL has specific data types for the components of a database. Effectively,there are three main types for MySQL : Date & Time, Numeric and String.

MySQL can be set up from the command line or by using something like mysqlcc [_1] or phpMyAdmin [_2]. Being a bit of a traditionalist, I'll go with the terminal windoUw (mainly as it also means that you don't need to download anything else!).

To call up MySQL, open a terminal window and type mysql -u root -p. This tells MySQL that you want to start up with the user "root" and with a password. The actual nuts and bolts of setting up MySQL isn't that hard.

If you've never used MySQL before, then the default password is nothing - just hit the return key. This is a hideous security hole and one which needs to be rectified as soon as you log in.

Once in, you'll see something similar to the image on the right.

If you've not set a password, then enter the following:

UPDATE user set password = PASSWORD("newpassword") 
where user = 'root'; FLUSH PRIVILEGES; exit;

(In this instance, newpassword is a password of your choice)

You now have a new password for the root user on your MySQL server. FLUSH PRIVILEGES clears all privileges on the server and exit does what it says on the tin - it exits the MySQL terminal.

That done, login to the server again. This time, let's examine what tables come by default.

mysql> show databases;

This will produce a table containing all of the databases the MySQL server is currently serving (right). You can see there is only 1 database on the MySQL server this series is being composed on. mysql is the database which contains details about users, passwords and the such.

When show databases; didn't show up the name of the database to be created, that gave the green light to say the database can be created.

Before creating the database (and as is common with creating anything on a computer), the database, linking, names and types should be written on paper first.

I have already specified earlier the names which I will use for the columns, but what about the types?

ID (here) is an INT and is also given the special type of PRIMARY_KEY when defining. The tables below show all of the types for all of the names.

The PRIMARY_KEYs all link to each other. KEY(1) is the "root" key which links to KEY(2). KEY(3) is linked from Others ID to the Catalogue table.

At the end of the second table, there is an "others" id. This is the anchor from which the ID in the Others table is attached. It would be pointless to try and add further to these tables as they are pretty much in their clearest format.

VARCHAR has been used for the images. While I could have used BLOB, using VARCHAR allows me to say, "okay, the URL for the images is going to be fixed, this is just the filename".

Finally, I have "Last ordered" set as a DATE which is ZEROFILLed. This means I can set the date myself which is more useful than reliance on a TIMESTAMP (useful as it means I can say that I had the stock before the database was set up!)

Simple enough.

mysql> create database theelements;
mysql> use theelements;
mysql> create table catagoryinfo (
  `id` smallint(6) NOT NULL auto_increment,
  `name` varchar(30) NOT NULL default '',
  `description` varchar(30) NOT NULL default '',
  `image` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`id`)
);

mysql> create table elementinfo (
  `id` longint NOT NULL auto_increment,
  `elementcatagory` int NOT NULL,
  `name` varchar(50) NOT NULL default '',
  `symbol` varchar(5) NOT NULL default '',
  `image` varchar(50) NOT NULL default '',
  `atweight` float(1) NOT NULL,
  `atnumber` int NOT NULL,
  `classification` varchar(50) NOT NULL default '',
  `casreg` longint NOT NULL,
  `block` varchar(5) NOT NULL,
  `outershell` varchar(5) NOT NULL default '',
  PRIMARY KEY  (`id`)
);

mysql> create table other(
  `id` smallint(6) NOT NULL auto_increment,
  `discovered` int NOT NULL,
  `colour` varchar(20) NOT NULL default '',
  `oxstates` varchar(50) NOT NULL default '',
  `allotropes` varchar(200) NOT NULL default '',
  PRIMARY KEY  (`id`)
);

That's the tables set. Next stage is to get the data in. As the datasets are somewhat large for this article, I've placed them on my website [_3] - you will need to grab the file article1.zip and de-archive it; I would suggest saving it to your CSD^[2]. There are three files: catinfo.csv, elemitems.csv and other.csv. These will need importing into your database and it is very easy to do.

mysql> LOAD DATA LOCAL infile 'catinfo.csv' INTO TABLE catagoryinfo 
FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (id, name, description, image);

Repeat for the other files. The only difference being to change the filename and the contents of the () to reflect the names given to the table rows. Once imported, you should get the following message back from the MySQL server

Query OK, 10 rows affected, 0 warnings (0.51 secs)
Records: 10 Deleted: 0 Skipped: 0 Warnings: 0

This means the data has been read in happily. A quick test to show that the data is indeed in the table should confirm this

mysql> select name,description from catagoryinfo;

This will display all of the category names and their descriptions from the catagoryinfo table. Similar tests can be performed for the other tables.

I'll leave this article at this point. Next time, I'll cover SQL manual insertion, manual alteration and manual deletion and introduce the C# command line interface which I will subsequently be developing.

I must thank the following people for helping me out on this project:

No furry animals were hurt during the production of this article. I did step on something while chasing some information, but it didn't squeak, so I'm not going to count that.