World Wide Tutorial

This tutorial will show you three example of populating a WinForm control with database-sourced data. In each example the same control will be used (a combo box), it will display customer name data pulled from a simple table in a fictitious Sales database stored in MS SQL2000. The table is called ‘Customer’, and has the following fields:

Table: Customer

CustomerID an int which is an Identity field (an automatic sequential number)
FirstName a varchar(30)
LastName a varchar(30)

The simple aim is to display all customer names from this table in a combo box.

Possible Design Choices

There are basically two possible designs with ADO.NET. The simplest is to use a DataReader. DataReaders act much like a forward-only Recordset in ADO. We would sequentially read in the customer names, adding them to the combo box as the reading occurs.

Another possibility is to use a DataSet. Think of a DataSet as an in-memory relational database. We create a DataSet that mirrors the portion of the database we are interested in, and use that to populate the combo-box. The advantage of this approach is that as soon as we have loaded the data we require we can close the database connection. The DataSet can then be used disconnected from the database, saving connection resources. The downside is of course that all the data and associated relational structure is held in memory. The appropriate resource trade-off will depend on your application.

This tutorial will show examples of both approaches, first using a DataReader, and then using a DataSet. It will then show a third example that also uses a DataSet, but instead of populating the combo-box from the DataSet via code, it binds the combo-box directly to a DataTable object inside the DataSet.

ADO.NET Providers

For this example it would have been easy to use either OLEDB Provider, or the SQLServer provider. Since the target database was SQL2000, these examples will use the SQLServer provider. You will need to add this using clause to your code for these examples to work:

using System.Data.SqlClient;

Using a DataReader to Populate a Combo-box

Imagine we have a combo-box cboReaderBox, and a button called PopulateReaderBox that we want to use to populate that box. The code for the button Click handler would be as follows:

private void btnPopulateReaderBox_Click(object sender, System.EventArgs e)
{
    //Connect to the Sales database
    //Read in each of the Customers from the Customer table,
    //Adding their name to the Combo box
    //Do this using a DataReader - much like a forward only ADO cursor.

    //declare these outside the try so we can close them off in the
    //finally clause.
    SqlDataReader oReader = null;
    SqlConnection oConnection = null;

    try
    {
        //ensure the box is cleared
        cboReaderBox.Items.Clear();

        //Use the SQLServer provider..
        //set up the connection and the command...
        oConnection = new
            SqlConnection("server=localhost;uid=sa;pwd=;database=Sales");

        string sSelectSQL = "SELECT FirstName +
                        ' ' + LastName as Name FROM Customer";
        SqlCommand oCommand = new SqlCommand(sSelectSQL,oConnection);

        //open the connection, and use the reader to populate the combobox
        oConnection.Open();
        oReader = oCommand.ExecuteReader();
        if (oReader != null)
        {
            while (oReader.Read())
            {
                cboReaderBox.Items.Add(oReader["Name"]);
            }
        }

        //the finally clause will tidy up for us.
        cboReaderBox.SelectedIndex = 0;
    }
    catch (Exception oE)
    {
        MessageBox.Show("Problem Populating Reader Box:
                        [" + oE.ToString() + "]");
    }
    finally
    {
        if (oReader!=null) oReader.Close();
        if (oConnection!= null)
        {
            if (oConnection.State == ConnectionState.Open)
                    oConnection.Close();
        }
    }
}

This code does the following things:

1. Sets up a connection to the database

2. Sets up the command (a SELECT) ready to run

3. Opens a DataReader (like a forward-only record set) on that connection and command

4. Loops through the rows that get returned by the reader, adding the ‘Name’ column to the combo-box’s Item collection.

5. There is a try-catch-finally structure here just to add robustness, you don’t necessarily need this to get the code to work, but I like error handling.

Using a DataSet to Populate a Combo-box

Here is the code to populate another combo-box (cboDatasetBox) with the same data, but this time from an in-memory copy the database or DataSet.

private void btnPopulateDatasetBox_Click(object sender, System.EventArgs e)
{
//Connect to the Sales database//Read in all of the Customers from the Customer table

// into a Dataset (an in-memory relational//database.//Run through this dataset,

 adding their names to the combobox.//We can blow away the dataset at that point.

//declare these outside the try so we can close them off in the finally clause.new DataSet();
try
{
//ensure the box is cleared//set up the connectionnew //set up the data adapter to get us our data.

..//Adapters xfer data to/from Database and Dataset.new string sCommand = "SELECT FirstName + ' ' + 
LastName as Name FROM Customer";
oDataAdapter.SelectCommand = new //use the data adapter to fill the dataset with the result

// of that SQL statement//Note we have 'changed' the table name so that in memory we run

// off the BoxCustomers//'table'...just to make the point that we are working off a

// construct of our own devising.//can now close the database connection, we'll work off the dataset

// which is in memory.//we are now disconnected.//put the data from the dataset 

into the comboboxif (oDataTable == null) 
throw new Exception("BoxCustomers table not 
found in oCustomersDataSet.");
foreach (DataRow oRow in oDataTable.Rows)
{
cboDatasetBox.Items.Add(oRow["Name"]);
}
//the finally clause will tidy up for us.catch (Exception oE)
{
MessageBox.Show("Problem Populating Dataset Box: 
[" + oE.ToString() + "]");
}
finally
{
//clear the inmemory data in the dataset, and close the database// connection, if it's open.if 

(oCustomersDataSet != null) oCustomersDataSet.Clear();

        if (oConnection!= null)

        {

            if (oConnection.State == ConnectionState.Open)

                 oConnection.Close();

        }

    }



}
SqlConnection oConnection = null;
DataSet oCustomersDataSet = 
cboDatasetBox.Items.Clear();
oConnection = 
SqlConnection("server=localhost;uid=sa;pwd=;database=Sales");
SqlDataAdapter oDataAdapter = 
SqlDataAdapter(); 
SqlCommand(sCommand,oConnection);
oDataAdapter.Fill(oCustomersDataSet,"BoxCustomers");
oConnection.Close();
DataTable oDataTable = oCustomersDataSet.Tables["BoxCustomers"];
cboDatasetBox.SelectedIndex = 0;
}

There are a few differences from the DataReader example:

1. It uses a DataAdapter. DataAdapters are the objects that handle communication between the database itself and your in-memory DataSet. We use this DataAdapter to ‘Fill()’ the DataSet with the results of our SELECT statement. The DataSet will then contain an in-memory version of the database Customer table.

2. Just to make the point that we don’t need the database anymore, the second parameter of the Fill() method specifies what to call the new in-memory table. I decided to call it ‘BoxCustomers’. In other respects it is identical to the actual Customers table in the database.

3. Notice we Close() the Connection to the database once the Fill() is done. With the data in memory, we no longer need the database.

4. DataSets can contain whole databases, so to populate the combo-box we explicitly specify the DataTable object pertaining to our new BoxCustomers in-memory table. We use this DataTable object to populate the combo-box.

Binding a Combo-box to a DataTable

With the table loaded into memory, we have a third option, which is to Bind the combo-box directly to that DataTable. The code for this is identical to the second example above, except:

1. Instead of foreach-ing through the DataRows in the DataTable, we simply:

cboBoundSetBox.DataSource = oDataTable;
cboBoundSetBox.DisplayMember = "Name";

2. Add the end (in the finally clause), we don’t Clear() the DataSet, as we would lose the data in the Table we have bound to the combo-box, thus emptying the combo-box again (except for any selected item)!