The SQL command is sent to the MySQL server using the ExecuteReader() method. The return value is a MySqlDataReader object, which contains the result set returned from the server. The Read() method is used to read forward through the result set, one record at a time. The GetString(), GetFloat(), GetDate(), and GetInt32() methods are used to extract the individual column data elements from the retrieved record. Columns are referenced in the order they appear in the table, starting with 0.

Listing 1 shows the getdata.cs program. This program demonstrates the steps required to extract data from a MySQL database table using Connector/NET. Remember you must reference the MySql.Data.dll file when compiling the application:

csc /r:MySql.Data.dll getdata.cs

After compiling the program, you can run it against a test database:

C:\> getdata
100 - Blum, Rich    $20000
101 - Blum, Barbara    $45000

The individual data records are extracted from the result set, and the individual data elements within the records are extracted and displayed.

Using PostgreSQL The PostgreSQL open source database is known for having the most features of all the open source databases. PostgreSQL can compare head-to-head with most commercial database packages. It can be downloaded for free from the PostgreSQL Web site (www.postgresql.org). Again, make sure to download the Windows binary version.

The PostgreSQL installation wizard guides you through the steps of installing and configuring a basic PostgreSQL server all in one process. The Installation Options window shown in Figure 3 provides an opportunity for you to select the PostgreSQL options to install.

For .NET development, make sure you install the Npgsql option. This is the PostgreSQL .NET connector library that allows you to create .NET applications for PostgreSQL databases.

Using the Npgsql classes
The Npgsql library contains all the classes required to connect to a PostgreSQL server, send an SQL command, and retrieve the result set data. The Npgsql classes are shown in Table 3.

You might see a trend here. Most of the basic Npgsql library classes are similar to the MySQL Connector/NET classes, and they behave in similar ways. This makes it relatively easy for .NET developers to switch code from one open source database server to another.

In Npgsql, the connection is started using the NpgsqlConnection class. The class constructor uses a connection string to define the server and database to connect to:

NpgsqlConnection conn = new NpgsqlConnection
("Server=localhost;Database=test;User Id=postgres;Password=testing");

The resulting NpgsqlConnection object is then used in NpgsqlCommand objects to send SQL commands to the database server. As expected, the NpgsqlCommand class contains the SQL command to send to the server. It also contains three separate methods for sending SQL commands to the server, as shown in Table 4.

If the SQL command sent to the server returns a result set, use the NpgsqlDataReader class to extract the individual data elements from the result set:

NpgsqlCommand comm. = new NpgsqlCommand(query, conn);
NpgsqlDataReader data = comm.ExecuteReader();
while(data.Read())
{
      int empid = data.GetInt32(0);
      String lastname = data.GetString(1);
      String firstname = data.GetString(2);
      Float salary = data.GetFloat(9);
      Console.WriteLine("{0} - {1},{2} ${3}", empid, lastname, firstname, salary);
}

Look familiar? This is the same code format as used for the MySQL Connector/.NET library. For SQL commands that only return a single value (such as functions), you can use the ExecuteScalar() method:

NpgsqlCommand comm. = new NpgsqlCommand("Select pi()", conn);
Double result = (Double)comm.ExecuteScalar();

Again, this is similar to the structure used for the MySQL Connector/NET library.

Summary
The MySQL and PostgreSQL open source databases are excellent resources for .NET developers. Having a database back end available that can be scaled from a simple Windows workstation to a large UNIX server platform can be invaluable for growing applications. Both MySQL and PostgreSQL supply .NET Data Providers that can be used to write .NET code that directly accesses open source databases. Both of the Data Provider packages contain similar classes that are used for connecting to the server, sending queries, and retrieving result sets. With these tools in hand, it is easy to start using open source database servers with your .NET applications.