Title: Let's Do C# and MySQL - Part 2 - A Beginning

Last time, I didn't even touch on any form of programming, but I did cover what MySQL is, how to set up a database and set up some tables which we will make use of over time. It was quite a useful exercise for me as I was able to consolidate material which for some reason didn't quite gel when I was originally taught it.

I did not cover inserting data, deleting data or even interrogation of data held within the database. There was a reason for this - it gives me a basis for introducing the MySQL API and how to code it up. But first, a health warning.

Mono ships with the ByteFX library which includes the definitions required for working with MySQL. It doesn't ship the version from the MySQL website, but that can certainly be compiled and is very easy to get running with the current release of mono. From what I have been told, VisualStudio.NET also ships with ByteFX so I will be using that instead of the version from the MySQL website. It shouldn't really make much of a difference as to which one is used as they are following the same API1. The names of the methods used to call a particular activity may not be the same though.

The best way though is that before the programming begins, that you understand how to use the MySQL interface to insert, delete and query data. After that, everything will fall into place.

This is very simple indeed and follows the following form

insert into <tablename>(<table_elements>) values(<values_to_be_inserted>);

In the case of the database started from the last issue (and which is still available from my home website), I did the following:

As I say, nothing to it.

I have not updated id - this is set as auto_increment. This means though that it may not be the number you would expect it to be. It is now down as being 108 (the database I provided has the last id number as 107). The problem is that I actually am using id incorrectly (and quite deliberately so). I had planned to create the database and tables in one go with id also being the element's number. While this isn't that important in the bigger scheme of things, it does mean that I have to enter the data in the correct elemental order with any snafu's requiring to be deleted.

id can set manually in the same way as any element within the table. It is, after all, nothing special.

It is possible to edit the id datafield without resorting to violence as well (which is quite handy as it means that if instead of a table of periodic elements, you had something like a stock list, you can dynamically alter the number of items in stock quickly and simply).

To edit a parameter

update `<tablename>`
set `<element_name>` ='<new_value>'
where `<element_name>`=<old_value> limit 1;

or for my example

Deleting data is an operation that everyone will need to do at some point or other with a database. An entry may be so messed up that it is not worth issuing a number of update commands to the server. It is simpler to just delete the row and re-enter the data.

To remove a row

delete from `<table_name>`
where `<primary_key>`=<value> limit 1;

And again, from my database:

Of course, there may be times when an entire table is so completely mucked up that the only real way to resolve the problem is to delete the table, or in extreme cases, the database. This is performed using the drop command. Once this command is issued, there is no going back. The table or database is gone. No more. Made like an old oak table and vanished.

drop table `<table_name>`

or

drop database `<database_name>`

Once data is in the table, you will someday wish to make use of it. Again, not a difficult task and a task which is very easy to modify

select <table_elements> from <table_name>;

Say I wish to see all elements, their atomic weights and the CAS registry number, all I do is issue

select name, atweight, casreg from elementinfo;

which will give

While the database is small, that is useful. However, there are 120 elements in the periodic table - that will mean that the data presented will certainly fill more than the server window. The search command is flexible and allows the user to specify to print (say) all base metal elements. This is achieved by using where within the select command sequence.

select <element_names> from <table_name> where (...);

For me to select just the base metal elements, I would use

select name, atweight, casreg from elementinfo where (classification = "Base Metal");

The where modified can be used in the same was as an if conditional can in programming. You can include or and and and even sort the output!

select name, atweight, casreg from elementinfo where (classification="Base Metal" and atnumber < 12); (See figure 1)

Figure 1.

select name, atweight, casreg from elementinfo where (classification="Base Metal" or block="f"); (See figure 2)

Figure 2.

select name, atweight, casreg from elementinfo where ((classification="Base Metal" and atnumber < 10) or block="f") order by "id";  (See figure 3)

Figure 3.

Powerful stuff!

As you can also see from the times, retrieval is almost instant. From tests I've performed, even with absolutely huge databases (in excess of 2/3rd of a million rows in a single table with the database containing another 6 tables, each with around ¼ million rows), retrieval from the database still gives very small times - even with a large conditional in the where conditional.

We've now covered the essentials that we will later use for our database. We have gone through the data types, how to create, modify and delete as well as interrogate our database and tables. Let's get on with some coding!

While it would be simple for me to hack out the source using C and MySQL, I have deliberately chosen C# for two reasons

Alright, the second isn't a reason as such, but hey, who is writing this?

What I'll cover for the rest of this article is connecting to the server, reading data, inserting data and editing data. It will not be that hard.

As these are stand-alone examples, they will all follow the same method.

There is actually nothing difficult about any of what we are about to do, essentially, we will be using a small number of methods to perform the tasks we need. What makes this even simpler, is all that we need to do is define a string which contains what we want to do and then pass that to the C# controller. Who could ask for anything more?

As you can see in the flowchart (figure 4) I will test to see if an error has occurred at the connection stage. I'll also be testing after any connection or process is passed to the database. It is essential to do this as the application will complain violently if you attempt to insert, delete or interrogate a table when the query to the database fails.

Figure 4.

There are two ways to catch the error. The simplest is that if the query fails, a non-zero is returned, so using if (!query) ... should suffice. The second method is to use try / catch (much in the same way as you would for C++). Below is the simplest method to connect to the MySQL server

using System;
using System.Data;
using ByteFX.Data.MySqlClient;
public class Test
{
  public static void Main(string [] args)
  {
    string connectionString = "Server=localhost;"+
       "Database=theelements";+
       "User ID=paul;"+
       "Password=paulpassword;";
    IDbConnection dbcon;
    dbcon = new MySqlConnection(connectionString);
    dbcon.Open();
    if (!dbcon)
    {
      Console.WriteLine("Unable to connect to the
                        MySQL server");
      exit(1);
    }
    else
      Console.WriteLine("Connection made");
    dbcon.Close();
   dbcon = null;
  }
}

IDbConnection dbcon; creates an instance of the C# database connection class. At this point, this can be a connection to any number of different database types. dbcon.Open() opens the connection to the database which by virtue of the line before it, is given as a MySQL connection.

This can be compiled by issuing (<compiler> can be either csc or mcs here) <compiler> mysql.cs -r:System.Data -r:ByteFX.Data which will create an executable called mysql.exe.

Test the binary. Assuming everything went well, you will have seen the line Connection made before the application terminated. If it didn't work, you will need to ensure that your listing matches mine and that the MySQL server is running (it's caught me out a couple of times before now).

We can now connect to any MySQL database! Let's do something a bit more taxing - reading data from the database (which is defined in the commandString line).

This creates another instance of IDbCommand, this time it is an instance of dbcon.CreateCommand(). The command to be used is created and dbcmd.CommandText is set to equal the command. The command is then acted upon. CommandText is able to either get or set from the database by the type passed to it. If it is a string (as it is here), the command is a get (the information is retrieved). If it is passed a void, the command is set. Again, test using if (!(dbcmd.CommandText = sqlcommand)) ...

I need to explain IDbCommand here as this interface represents a database command. To execute a command against a database, there has to be an open connection and a properly configured command object for the database type. These "command objects" can be created using a constructor, but it is far simpler to use the CreateCommand method. This returns a command object of the correct type for the database type and even goes as far as to configure the command object with the basic information obtained in the connection. The command object should have the properties in table 1 set; these are common to all command implementations

You will meet these later in the article.

Next is to read the data. This is performed using IDataReader. This provides a means of reading one or more forward-only streams of result sets obtained by executing a command at a data source and has quite a number of classes. The important aspect to this though is that it is a sequential reader (in other words, it grabs one line at a time until the end of the read process).

sqlcommand = "select elementname, atweight, casreg from elementinfo";
dbcmd.CommandText = sqlcommand;
IDataReader reader = dbcmd.ExecuteReader();
while (reader.Read())
{
  string element = (string) reader["elementname"];
  float atweight = (float) reader["atweight"];
  long cas = (long) reader["casreg"];
  Console.WriteLine("Element - " + element + ",
     weight "+ atweight + ", CAS : "+ cas;
}

echo `:CLR:M::MZ::/usr/bin/mono:' >
/proc/sys/fs/binfmt_misc/register

Again, a command is passed to CommandText. However, this time the command needs something to do the reading for it. The IDataReader class performs such a task and it is easy to see what is happening with the interesting part being two fold. The first is the IDataReader is positioned before the first element of the database by default, so a Read() has to be performed before anything useful comes out. The second is that as the database could contain any number of field types, the Read() returns everything as void* which then needs to be recast to the correct type for the data.

To terminate the application, reader and dbcon have to be closed and before dbcon is closed, dbcmd has to be disposed of.

Reader.Close();
reader = null;
dbcmd.Dispose();
dbcmd = null;
dbcon.Close();
dbcon = null;

The full listing is now

using System;
using System.Data;
using ByteFX.Data.MySqlClient;
public class Test
{
  public static void Main(string [] args)
  {
    string connectionString = "Server=localhost;"+
       "Database=theelements;"+ "User ID=paul;"+
       "Password=thelinuxman;";
    IDbConnection dbcon;
    dbcon = new MySqlConnection(connectionString);
    dbcon.Open();
    IDbCommand dbcmd = dbcon.CreateCommand();
    string sql = "SELECT name, atweight, casreg "+
                 "FROM elementinfo";
    dbcmd.CommandText = sql;
    IDataReader reader = dbcmd.ExecuteReader();
    while(reader.Read())
    {
      string element = (string) reader["name"];
      float atweight = (float) reader["atweight"];
      long cas = (long) reader["casreg"];
      Console.WriteLine("Element - "+ element + ",
         weight "+ atweight +", CAS : "+ cas);
    }
    reader.Close();
    reader = null;
    dbcmd.Dispose();
    dbcmd = null;
    dbcon.Close();
    dbcon = null;
  }
}

If this application is now compiled and executed, the following output is seen.

That's reading out of the way - by altering the sqlcommand line, you can include where and order by parameters. Writing and editing is not a great deal more difficult. However, IDataReader does warrant a deeper look.

The IDataReader extends the System.Data.IDataRecorder interface and together, these declare the functionality that gives the access to all aspects of the data contained in the result. Table 2 describes some of the more commonly used members of the interfaces.

In addition to that little lot, the data reader also provides a pile of other methods which takes an integer argument (the zero-based index of the column) - the names are self-explanatory : GetBoolean, GetByte, GetBytes, GetChar, GetChars, GetDateTime, GetDecimal, GetDouble, GetFloat, GetGuid, GetInt16, GetInt32, GetInt64, GetString, GetValue and GetValues.

This is, again, quite simple. We have seen that with reading, CreateCommand was called. Virtually the same procedure is used for writing and updating.

Inserting fresh data is virtually the same as if you were using the MySQL monitor

IDbCommand dbcmd = dbcon.CreateCommand();
string mysqlcommand = "insert into 
   elementinfo(id, name, ...) ";
mysqlcommand += "values(23, @name, ...)";
dbcmd.Parameters.Add("@name", "Sodium");
dbcmd.CommandText = mysqlcommand;

You can see a slight addition here, the use of Parameters.Add(...). The reason for using this is that as you know, when adding data, you can be adding absolutely anything in. If you want to pass in a string literal though, you would normally need these to be passed in quotes. Effectively, that is what is happening here. By using Parameters.Add(), we can pass anything in without the requirement to use quotes.

IDbCommand dbcmd = dbcon.CreateCommand();
string mysqlcommand = "update elementinfo
set id = 23 where id = 108"
dbcmd.CommandText = mysqlcommand;
if (dbcmd.ExecuteNonQuery() == 1)
  Console.WriteLine("ID updated");
else
  Console.WriteLine("ID not updated");

For any operation which doesn't return database data (such as table creation, table deletion or inserting), ExecuteNonQuery() should be used. This method returns an int that specified the number of rows affected (create table returns -1). Both of these code snippets can be placed into code listing already given

That's enough for this time. Next time, I'll expand on this via a simple menu system for interrogation, insertion and deletion of data and move that simple menu system to use System.Windows.Forms