mikec276

using System.Data.SqlClient;

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();
        }
    }
}

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.
    SqlConnection oConnection = null;
    DataSet oCustomersDataSet = new DataSet();

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

        //set up the connection
        oConnection = new 
            SqlConnection("server=localhost;uid=sa;pwd=;database=Sales");

        //set up the data adapter to get us our data...
        //Adapters xfer data to/from Database and Dataset.
        SqlDataAdapter oDataAdapter = new 
            SqlDataAdapter(); 
        string sCommand = "SELECT FirstName + ' ' + 
                    LastName as Name FROM Customer";
        oDataAdapter.SelectCommand = new 
                    SqlCommand(sCommand,oConnection);

        //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.
        oDataAdapter.Fill(oCustomersDataSet,"BoxCustomers");

        //can now close the database connection, we'll work off the dataset
        // which is in memory.
        oConnection.Close();
        //we are now disconnected.

        //put the data from the dataset into the combobox
        DataTable oDataTable = oCustomersDataSet.Tables["BoxCustomers"];
        if (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.
        cboDatasetBox.SelectedIndex = 0;
    }
    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();
        }
    }

}

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