C# Classes Used in Database Work

"ADO" stands for Microsoft's "ActiveX Data Objects". This is a Microsoft API that lets a Windows application programmer get access to a database.

The data providers supported by ADO.NET are:

• SQL Server (System.Data.SqlClient)

  SQL: Structured Query Language, ANSI standard

• OLEDB (System.Data.OleDb)

  OLEDB: Object Linking and Embedding, Microsoft

• ODBC (System.Data.Odbc)

  ODBC: Open Database Connectivity, Microsoft)

• Oracle (System.Data.OracleClient)

We will be using a MySql database, which is none of the above. The C# methods we need will be very similar to the methods used for SQL server, but they are in a different namespace:

     using MySql.Data;
     using MySql.Data.MySqlClient;

This is a list of some information about several different C# classes, methods and properties we may need to use in accessing a relational database.

It is by no means a complete list.

MySqlConnection class

A MySqlConnection object represents a session with a MySql server.

Its most useful constructor looks like this:

     MySqlConnection(String MyConString)

Here MyConString is the "connection string", which is a semicolon-delimited list of keyword=value phrases indicating:

• a data source (server, address, network address, "local", etc.)
• the database name (database = "name")
• the user ID (uid = "value")
• the user password (pwd = "value")

There are more options: specify the packet size or require encryption, etc.

The MySqlConnection class has a property called ConnectionString whose default value is an empty string.

Example of a connection string:

     "server=10.158.56.48;port=5432;uid=group1;pwd=Pap34-T1gr;database=db473;"

Example of MySqlConnection:

     MySqlConnection MyConnection = new MySqlConnection(MyConString);

The connection is then opened with

     MyConnection.Open();

which may throw an exception if something goes wrong, such as

     InvalidOperationException

if the connection is already open or if the connection string does not specify a data source or server or some other kind of exception if a connection-level error has occurred (e.g., the password needs to be reset)

Sooner or later, the connection must be closed. It can be closed by using

     MyConnection.Close();

or

     MyConnection.Dispose();

If a connection passes out of an ordinary scope, it will not automatically be closed. This will happen, however, with a "using" block:

     using (MySqlConnection MyConnection = new MySqlConnection(MyConString))
     {
      MyConnection.Open();
      ...
     }

At the end of the "using" block, MyConnection will be closed for us. We may have code in the block to close the connection, but it might not be executed for one reason or another. This technique will guarantee that the connection will be closed.

The MySqlConnection class has various properties and methods. For instance:

public override DataTable GetSchema()

This will retrieve schema data about the database into a DataTable. Schema data is very extensive and we seldom want all of it. There are alternate versions of GetSchema which allow us to request just some information, such as only names of tables or only names of columns in one table.

The DataTable class is used to represent one table in memory. It has elaborate structure; we can get hold of rows and columns, for instance.

MySqlCommand class

This class stores various information about an SQL command, such as the text of the SQL string, the connection itself and the "time out" value (how long before it runs out of time).

One of the constructors is:

     public MySqlCommand(String MyCommand, MySqlConnection MyConnection)

There are numerous methods including:

• public MySqlDataReader ExecuteReader()

  This executes commands (queries) that return rows from a table of results (a "result set").

• public override object ExecuteScalar()

  This executes a query and returns the first column of the first row of the result set, a single value such as a string or a number. It is used when the query expects to obtain a single value (as in "How many of such-and-such exist?").

• public overide int ExecuteNonQuery()

  This executes a SQL statement and returns the number of rows affected. It is used for SQL statements such as Create, Insert, Delete, etc.

MySqlDataReader and IDataRecord

Together these provide a way to read rows from a table and then access columns within each row. Rows are read one at a time, first to last.

We create a MySqlDataReader by using the ExecuteReader method of the MySqlCommand class, to execute a query, rather than by using a constructor. Thus we might have

     MySqlDataReader MyReader = MyCommand.ExecuteReader();

When we are done with the reader, we need to close it:

     MyReader.Close();

The data can be obtained using the Read method:

     public override bool Read()

which will return true if there are more rows left and false otherwise. We do not have data until the first use of Read. The effect of Read is that MyReader now contains data from one row of the table. We can then cast the reader as an IDataRecord, which allows us access to the individual columns.

So we have:

     IDataRecord MyRecord = (IDataRecord) MyReader;
     String S0 = MyRecord[0].ToString();
     String S1 = MyRecord[1].ToString();
     String S2 = MyRecord[2].ToString();

Since we know what query we just used, we presumably know what type of data is in each column, so we don't necessarily have to convert it to a String. We could in fact use MyRecord["Name"] or MyRecord["SSN"] to retrieve the data in the Name column or the SSN column.

IDataRecord has several properties:

• FieldCount = number of columns
• Item[integer N] returns an object from column N in this row
• Item[String S] returns an object from the column with name S in this row

IDataRecord has numerous methods, such as:

• GetValue(integer N) returns the object from column N in this row
• GetInt32(integer N) returns the integer from column N in this row (where we presumably do know that it is an integer)
• GetFieldType(integer N) returns a string = the name of the type of data in column N
• GetValues(object [] V) retrieves the values of all the columns into an array V of objects

It is important to understand that the Read() method makes one pass through the table, a row at a time.