Chapter 25

Databases with C#.NET

• the nature of simple databases and the SQL language;
• how SQL can be used with C#;
• the C# classes which provide database access.

Introduction

• as one of its tasks, the C# program needs some form of data store, and a database is most suitable.
• the programmer wishes to take advantage of the power of C# and its classes to provide a more powerful interface to a database than a conventional stand- alone database product allows.

Though you are likely to be familiar with SQL (Standard Query Language), we provide a short introduction.

The elements of a database

Here is an example of a database, which was created with Microsoft Access. It was stored in a file named MusicSales.mdb. It consists of two tables: the first was named Artists, and the second was named Companies. Incidentally, we could have created the database by writing C# code, but using Access is simpler.

Here is the Artists table, involving recording artists, their management company, and their music sales (in millions of dollars):

• The table is made up of a collection of similar records. In the above, we have represented each record as a row.
• A record is made up of a number of fields. In the above, we have three fields, named Artist, Company, and Sales . Effectively, a field name is a column name.
• The assumption has been made that artist names are unique, so we used this as the primary key. However, if the table contained for example student names in a college, we would have to introduce a unique student I.D number. For the above table, we set up the Artist field as the primary key when we created it, and this prohibits us from introducing other artists with the same name.

The SQL language - introduction

Before we do this, here are some examples of the most useful SQL statements. In traditional use, SQL statements must be terminated with a semicolon, but this is not needed when we pass an individual statement to the ADO.NET classes.

The select statement

• 
  select Artist from Artists
  

  This returns an item from every Artist field in the Artists table (i.e. the complete Artist column).

• 
  select * from Artists
  

  This returns the items from every field in the Artists table. Effectively, it returns the complete table.

• 
  select * from Artists where Company = 'Media Ltd'
  

  returns only the records from Artists where the company is Media Ltd. We enclose strings in single quotes. Strings are case- sensitive, so use capitals where they were used in the original database.

• 
  select * from Artists where Sales > 20 
  

  Returns only those records with sales above 20. Note that there are no quotes around numbers. The available operators are

  
      >    <    >=    <=    <>    =
  

  where <> means 'not equals'.

• 
  select * from Companies where Company = 'ABC Co'
  

  This returns the single record from Companies, involving ABC Co

• 
  select * from Artists order by Sales asc
  

  The order by item can be added to any selection. We can use asc or desc to specify ascending or descending order. The above statement puts the records in ascending order of sales.

The insert statement

  
insert into Artists (Artist, Company, Sales) 
                    values('The Regulars', 'ABC Co', 23.8)

The delete statement

delete from Artists where Artist = 'RadioStar'
delete from Artists where Company = 'ABC Co'

The update statement

update Artists set Sales = 30.8 where Artist = 'RadioStar'
update Artists set Company = 'Class UK', Sales = 56.8 
    where Artist = 'RadioStar'

This concludes our SQL overview. There are more statements, and even those we illustrated have extensions. However, the above will suffice for the programs in this chapter.

The C# database classes

The ADO.NET classes we will discuss are:

OleDbConnection, OleDbDataAdapter, DataTable, DataGrid, and OleDbCommandBuilder.

The namespaces for these classes are automatically imported into your program.

You might wish to read this section whilst looking at the complete examples, whose screen shots are shown in figures 25.1 and 25.2.

The OleDbConnection class

The OleDbDataAdapter class

To use the class, we drop an instance on our form, then work through the steps provided by a wizard (explained below). The instance is then ready for use.

The class structure of OleDbDataAdapter is rather complicated: it has some properties (named SelectCommand, InsertCommand, and DeleteCommand) which in their turn are instances of another complex class named OleDbCommand. In its turn, this class has its own properties and methods (such as CommandText and ExecuteNonQuery). However, because you will not use the OleDbCommand class separately, we will present the facilities as if they all belonged to OleDbDataAdapter.

The main methods and properties are:

• CommandText

  This property allows us to set up an SQL string to be used later. Here is an example:

  oleDbDataAdapter1.SelectCommand.CommandText =
      "select * from Artists where Company ='ABC Co'";
  

  (Recall that the two '.' symbols are needed because we are using the property of a property.)

• Fill

  This method provides a query facility. It copies the contents of the connected database (as selected by the SQL statement we place in CommandText) into a DataTable held in RAM. The returned integer provides a count of the number of matching records found. We can then access the data table by row and column position. Here is an example of Fill, showing CommandText in use:

  DataTable table = new DataTable();
  int recordCount;
  recordCount = oleDbDataAdapter1.Fill(table);
  

  The Fill method automatically opens the connection if it needs to. This is not the case with other methods, where we need to explicitly open and close the connection.

• ExecuteNonQuery

  This method executes SQL commands which can update, delete, and insert records. Examples are provided below.

• UpdateCommand

  This property provides facilities for updating existing records via an SQL command. Here is an example:

  oleDbDataAdapter1.UpdateCommand.CommandText = 
      "update Artists set Sales = 30.8 where Artist = 'RadioStar'";
  oleDbDataAdapter1.UpdateCommand.ExecuteNonQuery();
  

• DeleteCommand

  This property provides facilities for deleting records via an SQL command. Here is an example:

  oleDbDataAdapter1.DeleteCommand.CommandText =
      "delete from Artists where Artist = 'RadioStar'";
  oleDbDataAdapter1.DeleteCommand.ExecuteNonQuery();
  

• InsertCommand

  This property provides facilities for inserting new records via an SQL command. Here is an example:

  oleDbDataAdapter1.InsertCommand.CommandText =
      "insert into Artists(Artist, Company, Sales) " 
      + "values ('The Famous Five', 'Class UK', 0)";
  oleDbDataAdapter1.InsertCommand.ExecuteNonQuery();
  

  In our particular MusicSales database, an exception will be thrown if we try to insert a record into Artists with a company that does not already exist in Companies.

The DataTable class

We can choose to fill a data table with an exact copy of a table in a database, or we can pick out the records we need. This is determined by the SQL string we place in CommandText. Here is an example:

DataTable table = new DataTable();
int recordCount;
recordCount = oleDbDataAdapter1.Fill(table);

We can access individual items of a data table by using the Rows property, followed by a row number and column number, as in:

string  name;
name = Convert.ToString(table.Rows[0][1]);

• rows and columns are numbered from 0 upwards.
• The data table holds instances of the class Object, so when we extract an item, we convert it to the appropriate type. (For example we convert an item to a String if it is to be displayed in a text box.)

int lastRowNumber;
lastRowNumber = table.Rows.Count - 1;

To empty a data table, we use the Clear method.

The row/column structure will be familiar to you from using arrays, and it is tempting to begin to write loops to search data tables. However, this is to be avoided. Try to do as much work as possible in SQL. This approach results in simpler code.

The DataGrid class

We use the DataSource property to 'bind' a data grid to a data table. From then on, any changes to the table are automatically displayed in the grid.

The data grid is in the toolbox, and is positioned in the same way as most controls.

The OleDbCommandBuilder class

Creating a database program

1. Create a new project. Wait until the blank form appears.
2. View | Server Explorer
3. Right-click on Data Connections, then choose Add Connection...
4. A Data Link Properties windows appears. Select Provider at the top, then select the appropriate provider for your database. (For Microsoft Access databases, choose Microsoft Jet 4.0) , then click Next
5. The Connection tab appears. At step 1, browse to your database file, and select it.
6. Delete Admin from the User name text box.
7. Click OK
8. Move to the Server Explorer window, and left-click on Data Connections. The file name that you selected should appear. (Double- clicking on the name allows you to examine the database.)
9. Now we add the data connection to the program. Click on the name of your database file in the Server Explorer window, and drag it onto your form. An object named oleDbConnection1 appears in the system tray.

Now we place a data adapter on the form:

1. close the Server Explorer window, and view the toolbox. Instead of selecting Windows Forms, choose Data
2. Place an OleDbDataAdapter on the form. (Assuming you have an Access database). A wizard opens up.
3. The wizard can create SQL commands for you. We will write our own, but the wizard steps must be followed. Click Next
4. Ensure that your data connection is selected in the drop-down list. Click Next.
5. Ensure the Use SQL Statements is selected, and click Next.
6. On the Generate the SQL statements pane, choose Query Builder... An Add Table window appears.
7. Select one of your data tables (any one will do) and click Add, then Close.
8. A list of all the fields of the chosen table appears. Put a tick against All Columns. Click OK
9. An SQL Select query is shown. Click Finish.

Example - database manipulation

Figure 25.1 shows the form, and here is the code:

private void deleteButton_Click(object sender, System.EventArgs e)
{
    try
    {
        oleDbConnection1.Open();
        string command;

        //set up an SQL delete
        command = "delete from Artists where Artist = '" 
            + artistBox.Text + "';";
        oleDbDataAdapter1.DeleteCommand.CommandText = command;
        sqlLabel.Text = command;

        //do the delete
        oleDbDataAdapter1.DeleteCommand.ExecuteNonQuery();
    }
    catch (Exception exceptionObject)
    {
        MessageBox.Show(exceptionObject.Message);
    }
    finally
    {
        oleDbConnection1.Close();
    }

}

private void updateButton_Click(object sender, System.EventArgs e)
{
    try
    {
        oleDbConnection1.Open();
        string command;

        //set up an SQL update
        command = "update Artists set Company = '" +
            companyBox.Text + "', " + "Sales = " + salesBox.Text
            + " where Artist =  '" + artistBox.Text + "'";
        oleDbDataAdapter1.UpdateCommand.CommandText = command;
        sqlLabel.Text = command;

        //do the update
        oleDbDataAdapter1.UpdateCommand.ExecuteNonQuery();
    }
    catch (Exception exceptionObject)
    {
        MessageBox.Show(exceptionObject.Message);
    }
    finally
    {
        oleDbConnection1.Close();
    }

}

private void insertButton_Click(object sender, System.EventArgs e)
{
    try
    {
        oleDbConnection1.Open();
        string command;
        //set up an SQL insert
        command = "insert into Artists(Artist, Company, Sales )" 
            + " values('" + artistBox.Text + "', '"
            + companyBox.Text + "', " + salesBox.Text + ")";

        oleDbDataAdapter1.InsertCommand.CommandText = command;
        sqlLabel.Text = command;

        //do the insert
        oleDbDataAdapter1.InsertCommand.ExecuteNonQuery();
    }
    catch (Exception exceptionObject)
    {
        MessageBox.Show(exceptionObject.Message);
    }
    finally
    {
        oleDbConnection1.Close();
    }
         

}

private void findButton_Click(object sender, System.EventArgs e)
{
    try
    {
        DataTable table = new DataTable();
        string command;
        int recordCount;

        //set up an SQL query
        command = "select * from Artists where "
            + "Artist = " + " '" + artistBox.Text + "'";

        oleDbDataAdapter1.SelectCommand.CommandText = command;
        sqlLabel.Text = command;

        //do the query
        table.Clear();
        recordCount = oleDbDataAdapter1.Fill(table);

        //display any result
        if (recordCount != 0)
        {
            companyBox.Text = Convert.ToString(table.Rows[0] [1]);
            salesBox.Text = Convert.ToString(table.Rows[0][2]);
        }
        else
        {
            MessageBox.Show("Artist not found!!");
        }
    }
    catch (Exception exceptionObject)
    {
        MessageBox.Show(exceptionObject.Message);
    }
}

Here are some points on the program:

• The user-interface is not error-proofed. For example, an insert can be attempted even if all the fields are empty. The omission is intentional, to avoid obscuring the database code.
• To create the program, follow the setting-up process for the data connection and data adapter. Then create the GUI and enter the code. The three text boxes have been renamed to artistBox, companyBox, and salesBox.
• Every time we create an SQL command, we display it in SQLLabel at the bottom of the form, before executing the command. The reason is that, although the C# compiler makes detailed checks on your C# coding, it does not check the contents of strings, and knows nothing about SQL. Thus, SQL statement errors are only detected at run-time, and it is useful to have a display of the SQL code. Many errors are simple mistakes with single quotes.
• It is essential to use exception-handling. In this program, we display the Message property, which provides an explanation of any errors.
• When deleting, inserting, and updating, we need to open and close the connection. We place the Close call within the finally of the exception-handling code, so that it is performed whatever happens within the try.

b). inserting a record?

Example - the data grid

The user enters a sales figure, and an SQL query is constructed which fills a data table with records whose sales are equal to or above the entered value. A value of 0 will select every record. The data grid can be edited, and changes can be made permanent via a Save button.

private DataTable table = new DataTable();   //at the top of the code

private void saveButton_Click(object sender, System.EventArgs e)
{    

    try
    {      //NB************* diff from vb- see usings
        OleDbCommandBuilder commandbuilder = new
            OleDbCommandBuilder(oleDbDataAdapter1);
                
        oleDbDataAdapter1.Update(table);
    }
    catch (Exception exceptionObject)
    {
        MessageBox.Show(exceptionObject.Message);
    }
}

private void highSalesButton_Click(object sender,
    System.EventArgs e)
{        
    try
    {
        dataGrid1.DataSource = table;

        //set up an SQL Query
        oleDbDataAdapter1.SelectCommand.CommandText =
            "select * from Artists where "
            + "Sales >= " + salesAboveBox.Text;

        //fill data table with results from query
        table.Clear();
        oleDbDataAdapter1.Fill(table);
    }
    catch (Exception exceptionObject)
    {
        MessageBox.Show(exceptionObject.Message);
    }
}

Note:

• To create the program, follow the setting-up instructions for the data connection and data adapter.
• Place a data grid on the form. It can be found in the toolbox.
• The data table is declared as a Private variable, because it is shared by both methods.
• The DataSource property of the grid is used to bind table. This means that when table changes (via the Fill method) then the table is re- displayed in the grid automatically
• The data grid can be altered by the user:
  • To update a record, type in the new values.
  • To insert a new record, type it at the bottom of the grid, alongside the *.
  • To delete a record, click the button at the left of the record. This highlights all the fields. Press the delete key on the keyboard to delete it.
  • To resize the columns, place the mouse between the buttons at the top of the columns, and drag.
  • To sort the records based on a particular column, click the button at the top of a column. Clicking again changes from ascending order to descending order.
• The program does not check for errors after each modification of the data grid. For example, if the user enters a company that does not already exist in the Companies table, then this error will only come to light when we click Save. Thus, exception-handling is essential at the saving stage.

Programming principles

Programming pitfalls

• It is very easy to make errors when creating SQL strings. Take care with single-quotes, and, when debugging, display the SQL command.
• When executing queries with Fill, you do not need to use Open and Close. In every other case, they are needed.

Grammar spot

• The Rows property of a data table takes two bracketed integer expressions, and returns an Object. We convert it to our required type, as in:

  
  int n = Convert.ToInt32(table.Rows[3)] [4]);
  

New language elements

OleDbConnection
OleDbDataAdapter
DataTable
DataGrid
OleDbCommandBuilder

New IDE facilities

Summary

• We have used the ADO.NET classes to access databases.
• A range of databases can be accessed, not only those created by Microsoft Access.
• The basic approach is to create and execute strings containing SQL statements.

Exercises

2. Write a program which displays the number of companies in the database.

3. Write a program which displays the highest sales figure, along with the name of the artist. (Reminder: SQL has an order by facility.)

4. Write a program which displays the Artists table in a data grid, in ascending order of sales.

5. Modify the Database Example program so that the insert operation firstly checks that the entered company exists in the Companies table. If it does not, ask the user if they wish to add a new company, and insert a new record into Companies if required.

25.1 ANSWER:
a). The Artist field needs a value. In the program, any values in other text boxes are ignored. We could enforce this with an if as follows:

if (artistBox.Text != "")
{
    ...code to delete a record
}

b). All three textboxes need values.

25.2 ANSWER:
The coding for the button would be almost identical to the Sales Above button, but the SQL command can be simplified to:

command = "select * from Artists"

25.3 ANSWER:
The program is essentially the same as the data grid example. The only difference is the setting-up of the CommandText property. Here, we provide a text box to allow the user to enter a query, and we transfer its text to CommandText by

oleDbDataAdapter1.SelectCommand.CommandText = commandBox.Text;