This is a bonus chapter for the book Visual C#.NET For Students, by Bell and Parr. The site has some additional sample chapters.

Chapter 25

Databases with C#.NET

This chapter explains:
Note: the sample database file (MusicSales.mdb) can be downloaded from here. You should save the file when downloading.

Introduction

This chapter is for those who are familiar with creating a relational database (for example via Microsoft Access) and who wish to manipulate the database with a C# program. They might wish to do this because:

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

The elements of a database

Though this book assumes that the reader can create relational databases (and can perform the prior analysis stages involving for example normalisation), we will review the essential terminology.

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):

Artists
 
Artist Company Sales
The Visuals ABC Co 65.2
RadioStar ABC Co 22.7
Mike Parr Media Ltd 3.5
The Objects Class UK 12.6
Assignment 182 Media Ltd 34.6
The Trees United Co 3.72
Note that: A database can consist of one or more such tables. Here we have the second table, named Companies (with Company set as the primary key for the table) , which lists company names together with their location, as shown here:

Companies
 
Company Location
ABC Co London
Media Ltd Chicago
Class UK Madrid
United Co London
The relationship between the tables is that the Company column of the Artists table is a foreign key of The Company column in the Companies table. This was specified when we designed the database.

The SQL language - introduction

SQL (Structured Query Language) was standardised in the mid-eighties, and since then has become the major language for database access. SQL statements can be used to retrieve and modify the contents of a database. Though it is possible to enter SQL statements manually, it is normal to present a simpler interface to the user. We shall do this in C# by building strings which contain SQL statements, and then sending the strings to the database. Thus, the SQL statements are hidden from the user.

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

This is used to retrieve records from a database. We can specify criteria that a record must match. Here are some examples, using the database we introduced earlier. Here we use lower-case for SQL key words (such as select and insert), but capitals can also be used.

The insert statement

  
insert into Artists (Artist, Company, Sales) 
                    values('The Regulars', 'ABC Co', 23.8)
Inserts a new record with the specified values. We provide a bracketed list of column names, and a bracketed list of values. Note that we can choose to split a long statement into several lines to improve its readability.

The delete statement


delete from Artists where Artist = 'RadioStar'
delete from Artists where Company = 'ABC Co'
Deletes the specified record(s).

The update statement


update Artists set Sales = 30.8 where Artist = 'RadioStar'
update Artists set Company = 'Class UK', Sales = 56.8 
    where Artist = 'RadioStar'
Updates the RadioStar record. The examples show firstly a single field being updated, then two fields. As with delete, it is possible to specify several records in the where item.

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

Throughout its life, Microsoft has provided a series of approaches for database manipulation, including ODBC (Open DataBase Connectivity), and ADO (ActiveX Data Objects). The .NET framework now provides ADO.NET. It is termed an API (applications program interface) - a set of classes and their methods which provide access to a complex item. For example, the file classes such as Directory are a kind of API, in that they enable programs to access to the Windows file system.

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

This class deals with connecting to the database. We will choose to initialise the connection via the Server Explorer in the IDE, rather than by code. During this stage, we can select the appropriate software to access the type of database (e.g. Access, Oracle etc). The database can be on your computer, or on a network. After the initialisation process has been carried out, we can use the Open and Close methods to manipulate the connection.

The OleDbDataAdapter class

This class provides facilities for sending SQL statements to a database. This can involve transferring data from the database into DataTable objects held temporarily in RAM.

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:

The DataTable class

Instances can hold a single table in RAM. The data is presented to the programmer in row/column form, rather like a 2-dimensional array. Note that it provides no visual representation of a table.

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]);
Note that: To find out how many rows a data table currently contains, we use the Count property, as in:
int lastRowNumber;
lastRowNumber = table.Rows.Count - 1;
Remember that the rows are numbered from 0 upwards.

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

This control provides for the display of a DataTable in row/column format. It also allows the editing of existing data, and the insertion of new records at the bottom of the grid. Note that changes to the data are not automatically transferred to the database. We make use of an OleDbCommandBuilder to perform the updating.

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

This class deals with generating a set of commands to update a database, based on changes that might have been made to a data table (perhaps via a data grid user interface). The Save button in the following DataGrid program shows it in use.

Creating a database program

Whatever database program you are creating, the connection and the data adapter need initialising. It is possible to do this via code, but here we show how you do the setting-up via the IDE. Follow the steps carefully.

  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.
The setting-up is now complete, and you can enter code which manipulates your database.

Example - database manipulation

This program (Database Example) provides a form which allows the user to search, delete, insert, and update records.

[pic of database manip]
Figure 25.1 - 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:


SELF-TEST QUESTION 25.1 (Self-Test answers are at the end of the chapter)
In the database example program, which text boxes need values when
a). deleting a record?

b). inserting a record?


Example - the data grid

This program (Data Grid) shows the data grid in action. Figure 25.2 shows the screen.
[pic of data grid]
Figure 25.2 - 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:


SELF-TEST QUESTION 25.2
How would you provide a Show All Records button for the user?

SELF-TEST QUESTION 25.3
Write a program which allows the user to enter and execute any SQL query.

Programming principles

This chapter has no new C# principles - we are using the power of the ADO classes.

Programming pitfalls

Grammar spot

New language elements

The classes:

OleDbConnection
OleDbDataAdapter
DataTable
DataGrid
OleDbCommandBuilder

New IDE facilities

The C# IDE can be used to initialize the data connector and the data adapter. These tasks can be done by writing C# code, but we recommend the use of the IDE.

Summary

Exercises


We have based these exercises on the on the MusicSales.mdb database, because you may not have facilities to create your own database. When you download it, we advise you to save a backup copy, in case you accidentally delete all the contents.
1. Write a program which allows the user to enter a company name, and which then displays the location of the company. Provide suitable error messages. Do not provide delete, insert, and update facilities.

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.


ANSWERS TO SELF-TEST QUESTIONS

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;

************ end **************