Populating a Windows Forms ComboBox
Problem
You need to populate a ComboBox from a database, bind a field in a database to the ComboBox so that the correct value is selected from the list, and use the value selected in the ComboBox to update the database.
Solution
You must:
- Fill a ComboBox from a database (pay attention to the difference between the SelectedIndex and SelectedValue ).
- Bind a ComboBox to a field in a result set so that the value is selected in the ComboBox corresponding to the value in a field for the record displayed.
- Use the selection events returned by the ComboBox .
- Update the database with the value selected in the ComboBox .
The schema of table TBL0711 used in this solution is shown in Table 7-11.
Table 7-11. TBL0711 schema
Column name |
Data type |
Length |
Allow nulls? |
---|---|---|---|
Id |
int |
4 |
No |
ComboBoxItemId |
int |
4 |
No |
Field1 |
nvarchar |
50 |
Yes |
The schema of table TBL0711_ComboBoxSource used in this solution is shown in Table 7-12.
Table 7-12. TBL0711_ComboBoxSource schema
Column name |
Data type |
Length |
Allow nulls? |
---|---|---|---|
ComboBoxItemId |
int |
4 |
No |
Description |
nvarchar |
50 |
No |
The sample code contains seven event handlers:
Form.Load
Sets up the sample by creating a DataAdapter with the logic to select all records from table TBL0709 in the sample database and to update changes made back to the database. The DataAdapter is used to fill a DataTable in a new DataSet with the schema and data from TBL0709. A DataTable is filled from table TBL0709_ComboBoxSource and added to the DataSet . A DataRelation is created between the two tables in the DataSet .
The ComboBox control is filled by linking its properties to table TBL0709_ComboBoxSource in the DataSet . The text boxes displaying the data for the ID and Field1 columns and the ComboBox displaying data for the ComboBoxItemId column are bound to the DataTable TBL0709. Finally, the BindingManagerBase is obtained for the TBL0709 table in the DataSet .
Update Button.Click
Ends the current edit and uses the DataAdapter created in the Form.Load event handler to update the database.
ComboBox.SelectionChangeCommitted
Displays the values for the SelectedIndex , SelectedValue , and SelectedText properties of the ComboBox after its selected item has changed.
Move First Button.Click
Sets the current record of the bound controls to the first record by setting the Position property of the BindingManagerBase object to 0.
Move Previous Button.Click
Sets the current record of the bound controls to the previous record by decrementing the Position property of the BindingManagerBase object by 1.
Move Next Button.Click
Sets the current record of the bound controls to the next record by incrementing the Position property of the BindingManagerBase object by 1.
Move Last Button.Click
Sets the current record of the bound controls to the last record by setting the Position property of the BindingManagerBase object to the total number of records, as returned by the Count property, minus 1.
The C# code is shown in Example 7-19.
Example 7-19. File: ComboBoxForm.cs
// Namespaces, variables, and constants
using System;
using System.Configuration;
using System.Windows.Forms;
using System.Data;
using System.Data.SqlClient;
private const String TABLENAME = "TBL0711";
private const String TABLENAME_COMBOBOXSOURCE = "TBL0709_ComboBoxSource";
private const String RELATIONNAME = "REL0711";
private DataSet ds;
private SqlDataAdapter da;
private BindingManagerBase bm;
// . . .
private void ComboBoxForm_Load(object sender, System.EventArgs e)
{
// Create the DataSet.
ds = new DataSet( );
// Create the select and update commands for the DataAdapter.
String selectCommand = "SELECT Id, ComboBoxItemId, Field1 FROM " +
TABLENAME;
String updateCommand = "UPDATE " + TABLENAME + " " +
"SET ComboBoxItemId = @ComboBoxItemId, Field1 = @Field1 " +
"WHERE Id = @Id";
// Create the DataAdapter.
da = new SqlDataAdapter(selectCommand,
ConfigurationSettings.AppSettings["Sql_ConnectString"]);
da.UpdateCommand = new SqlCommand(updateCommand,
da.SelectCommand.Connection);
da.UpdateCommand.CommandType = CommandType.Text;
da.UpdateCommand.Parameters.Add("@Id", SqlDbType.Int, 0, "Id");
da.UpdateCommand.Parameters.Add("@ComboBoxItemId", SqlDbType.Int, 0,
"ComboBoxItemId");
da.UpdateCommand.Parameters.Add("@Field1", SqlDbType.NVarChar, 50,
"Field1");
// Retrieve the data and schema for the table.
da.FillSchema(ds, SchemaType.Source, TABLENAME);
da.Fill(ds, TABLENAME);
// Create and fill the schema for the ComboBox table.
String sqlText = "SELECT ComboBoxItemId, Description FROM " +
TABLENAME_COMBOBOXSOURCE;
SqlDataAdapter daCB = new SqlDataAdapter(sqlText,
da.SelectCommand.Connection);
DataTable comboBoxSourceTable =
new DataTable(TABLENAME_COMBOBOXSOURCE);
daCB.FillSchema(comboBoxSourceTable, SchemaType.Source);
// Sdd the instructions for the user as the first element.
comboBoxSourceTable.Rows.Add(new object[] {-1, ""});
// Fill the rest of the data for the ComboBox.
daCB.Fill(comboBoxSourceTable);
// Add the ComboBox source table to the DataSet.
ds.Tables.Add(comboBoxSourceTable);
// Relate the parent and ComboBox tables.
ds.Relations.Add(new DataRelation(RELATIONNAME,
ds.Tables[TABLENAME_COMBOBOXSOURCE].Columns["ComboBoxItemId"],
ds.Tables[TABLENAME].Columns["ComboBoxItemId"],
true));
// Set up the ComboBox with the DataSet.
comboBox.DataSource = ds.Tables[TABLENAME_COMBOBOXSOURCE];
comboBox.ValueMember = "ComboBoxItemId";
comboBox.DisplayMember = "Description";
// Bind all of the controls to the DataSet.
idTextBox.DataBindings.Add("Text", ds, TABLENAME + ".Id");
comboBox.DataBindings.Add("SelectedValue", ds,
TABLENAME + ".ComboBoxItemId");
field1TextBox.DataBindings.Add("Text", ds, TABLENAME + ".Field1");
// Get the binding manager base for the parent table.
bm = BindingContext[ds, TABLENAME];
}
private void updateButton_Click(object sender, System.EventArgs e)
{
// End the current update and update the record using the DataAdapter.
bm.EndCurrentEdit( );
da.Update(ds.Tables[TABLENAME]);
}
private void comboBox_SelectionChangeCommitted(object sender, System.EventArgs e)
{
resultTextBox.Text="SelectedIndex = " + comboBox.SelectedIndex +
Environment.NewLine +
"SelectedValue = " + comboBox.SelectedValue +
Environment.NewLine +
"SelectedText = " +
((DataRowView)comboBox.Items[comboBox.SelectedIndex])
["Description"];
}
private void moveFirstButton_Click(object sender, System.EventArgs e)
{
bm.Position = 0;
}
private void movePreviousButton_Click(object sender, System.EventArgs e)
{
bm.Position -= 1;
}
private void moveNextButton_Click(object sender, System.EventArgs e)
{
bm.Position += 1;
}
private void moveLastButton_Click(object sender, System.EventArgs e)
{
bm.Position = bm.Count - 1;
}
Discussion
Combo boxes are most commonly used to browse data, enter new
data, or edit existing data in a data source.
There are two ways to fill a
ComboBox
: either use the
Add( )
method or bind the
ComboBox
to a data
source. The Windows Forms
ComboBox
control has three
properties that are used to control data binding to an ADO.NET data
source. These are described in Table 7-13.
Table 7-13. ComboBox properties for data binding
Property
Description
DataSource
Gets or sets the data source for the control. This can be a
DataTable
,
DataView
, or any class that implements
the
IList
interface.
DisplayMember
Gets or sets the property of the data source that is displayed
in the control. In a
DataTable
or
DataView
, this
is the name of a column.
ValueMember
Gets or sets the property of the data source that
supplies
the
value for the control. In a
DataTable
or
DataView
, this is the name of a column. The default is an
empty string.
The
DataBindings
property of a control exposes the
ControlBindingsCollection
object for the control. The
Add( )
method of this collection adds a
Binding
to the collection. The overload of the
Add( )
method used
in the solution takes three arguments:
PropertyName
The name of the control property to bind
DataSource
The name of the data source to bind
DataMember
The name of the property of the data source to bind
In the solution, as shown in this excerpt, the
SelectedValue
property of the
ComboBox
is bound
to the
ComboBoxItemId
field in the destination table
TBL0709 in the
DataSet
ds
:
comboBox.DataBindings.Add("SelectedValue", ds,
TABLENAME + ".ComboBoxItemId");
The
SelectionChangeCommitted
event raised by the
ComboBox
occurs when the item selected is changed and the
change is committed. The event handler receives an
EventArgs
argument. You can use the handler for the event
to get the new value of the
ComboBox
once it has been
changed.
Recipe 7 12 Binding a Windows DataGrid to Master Detail Data
Категории