Host a combo box column within a DataGridView control

Bechir Bejaoui
16y
155.1k
0
0

Article

Introduction

Three weeks ago, I encountered a problem when developing a windows application which contains a data grid view, the problem is:

Imagine if there is a database witch contains two tied tables A and B. The Column A belongs to Table A and represents the primary key. The Column B belongs to the Table B and represents the foreign key. To keep safe the coherence in the database. It will be crucial to preserve the information integrity, in other words, each data element added to the Column B must absolutely be already in the Column A. Otherwise, the database coherence will be broken. To resolve this problem, it is possible to throw an exception and prevent user that he violate the information integrity at the adequate moment, but in my point of view, I'm not satisfied by this solution. And in order to avoid this entire disturbance, we can always use a data grid view combo box column instead of the ordinary data grid view column.

A very few articles talk about this issue, and even "the how to do this?" in very clear and practical steps are not provided. For this reason a write this article to help friends to know how to deal, as simplest as, possible.

In this case, two tables are used. The Employees and the Orders tables from the Northwind database sample witch can be downloaded from the Microsoft official web site. Otherwise, if it is not possible to download it, you can use another database of your choose but it must contain two tied tables because, anyway, the procedure is the same. The diagram below shows us the two given tables' relation:

Figure 1

To discover how to host a data grid view combo box column, I invite you to follow those steps:

Add a new project by clicking File --> New project
The form showed below appears

Figure 2

You can select Windows application after expanding the tree view at left and selecting Windows as shown in the figure 2 above.
After clicking Ok, Form1 appears in the editor.
Drag and drop two data grid view on Form1.
Name the first grid view employeesGridView and Name the second ordersGridView
Add a button to Form1, name it btnSaveData and set its text property to 'Save data from ordersGridView to a separate XML file'.
Drag and drop two labels on Form1, change their text properties, respectively, to Employees grid view and Orders grid view and put each one of them above its corresponding data grid view. The Form 1 appears as follow:

Figure 3

Define those objects:

DataSet myDataSet;
DataTable EmployeesTable;
DataTable OrdersTable;
DataTable OrdersTable1;
Implement the private void Form1_Load(object sender, EventArgs e) event handler:

Create new instance of the defined objects above

myDataSet = new DataSet();
EmployeesTable = new DataTable();
OrdersTable = new DataTable();
Establish a connection to the Northwind database and fill the EmployeesTables and the OrdersTables using data adapters as follow:

// Create a new connection and parameter it to connect with Northwind database

using (SqlConnection oConnection = new SqlConnection("Data Source = STANDARD;Initial Catalog = Northwind; Integrated Security = true"))
{

    /* Create a new command and overload its constructor as shown bellow. There are only 4 columns selected for
    simplify the deal */

    SqlCommand EmployeesCommand = new SqlCommand("SelectEmployeeID,LastName,FirstName,Country From
    Employees", oConnection);

    /* Create an Adapter and overload its constructor by the command object according to the Employees view */

    SqlDataAdapter EmployeesAdapter = new SqlDataAdapter(EmployeesCommand);

    // Fill the EmployeesTable by data from the Employees view

    EmployeesAdapter.Fill(EmployeesTable);

    /* Create a new command and overload its constructor as shown bellow. There are only 3 columns selected in this
    case */

    SqlCommand OrdersCommand = new SqlCommand("Select OrderID,OrderDate,RequiredDate From Orders",oConnection);

    /* Create an Adapter and overload its constructor by the command object according to the Orders view */

    SqlDataAdapter OrdersAdapter = new SqlDataAdapter(OrdersCommand);

    // Fill the OrdersTable by data from Orders view

    OrdersAdapter.Fill(OrdersTable);

}
Add this line of code:

OrdersTable1 = OrdersTable.Clone();

The purpose of clonig the OrdersTable1 from the OrdersTable, is that only the data table structure will be used later. After the ordersGridView being populated with data , they will be saved into a saparate XML file instead of the database Orders table, Of corse, because our pupose here is only the demonstration and not the data base updating.
Add EmployeesTable and OrdersTable1 to myDataSet :

myDataSet.Tables.Add(EmployeesTable);
myDataSet.Tables.Add(OrdersTable1);
Set employeesGridView and ordersGridView DataSet and DataMember:
employeesGridView.DataSource = myDataSet;
employeesGridView.DataMember = myDataSet.Tables[0].TableName;
ordersGridView.DataSource = myDataSet;
ordersGridView.DataMember = myDataSet.Tables[1].TableName;
Create a new Instance of a data grid view combo box column and parameter it as follow:

The topic here is how to add items to each combox box cell so that if a user click this small combo box
snippet, the items list appears and then he can choose one element from this list. In deed, All columns
cells behavior must be the same.

To do this I propose 3 manners :

First manner :

I propse this manner when the number of the combo box items and the items values are fixed for every time. In this case do as follow:

/* Create a new data grid view combo box cell in order to use it as a cell,template for the data grid view combo box column
DataGridViewComboBoxCell EmployeeIDCell = new DataGridViewComboBoxCell();

/* In this case the employeeID is fixed from 1 to 9. Use Items.Add method to add,elements to the combo box cell */
for(int i = 1;i<=9;i++)

EmployeeIDCell.Items.Add(i);

/* Create a new Combo Box Column and set its CellTemplate property to, EmployeeIDCell */
DataGridViewComboBoxColumn EmpIdColumn = new DataGridViewComboBoxColumn();
EmpIdColumn.CellTemplate [1] = EmployeeIDCell;

But this method is not the solution in a such case because EmployeeID can change at any time. In other words, if there is a new Employee the new ID number will not appear in the combo box list, because this last one is fixed at the design time. The same problem is happen when an employee is kicked out from the job, Therefore, I propose the second manner.

Second manner:

This method can be a little better than the first one but it is not the best, but any way, it is a realizable solution:

/* This method returns an array it lets us enter a customized array either, at design time or at run time */
public string[] ElementsArray(int lenthArray ,string[]itemArry)
{

string[] myArray = new string[lenthArray];
for (int i = 0; i < lenthArray; i++)

myArray[i] = itemArry[i];

return myArray;
}

/* Define an array either at design time or at run time via interface proposed, to the final user. In this case, oArray is used */
string[] oArray = {"1","2","3","4","5","6","7","8","9" };

//Create a new Combo Box Column

DataGridViewComboBoxColumn EmpIdColumn = new DataGridViewComboBoxColumn();

// Use the Items.AddRange method and overload it with ElementsArray(9,oArray)

EmpIdColumn.Items.AddRange(ElementsArray(9,oArray));

But the problem is not completely resolved, we want that each addition, retrivement of the employees records will be done automatically in the combo box list from the data source.

Therefore, I propose the best manner to deal with the problem.

Third manner:

As I say, this manner is the best one because the combo box list is up dated automatically from the data source without any intervention either at design or at the run time. In this case the EmployeeID column index is 0, in addition, the Employees table index in myDataSet is also 0 . So, do as follow:

// Create a new Combo Box Column

DataGridViewComboBoxColumn EmpIdColumn = new DataGridViewComboBoxColumn();
// Set the DataSource of EmpIdColumn as bellow

EmpIdColumn.DataSource = myDataSet.Tables[0];

// Set the ValueMember property as done bellow

EmpIdColumn.ValueMember = myDataSet.Tables[0].Columns[0].ColumnName.ToString();

// Set the DisplayMember property as follow

EmpIdColumn.DisplayMember = EmpIdColumn.ValueMember; g. Set the Header text column value:

Set the Header text column value:

There are two ways to do this, you can choose one of them:

EmpIdColumn.HeaderCell.Value = "EmployeeID";
or
EmpIdColumn.HeaderText = "EmployeeID";

Finally, add the combo box column to ordersGridView by doing so:

ordersGridView.Columns.Add(EmpIdColumn);

Implement the private void btnSaveData_Click(object sender, EventArgs e) event handler of btnSaveData button as follow

The following code consists on creating a new result table and coping it from the OrdersTable1, after that, creating a new data column for the EmployeesID and adding it to the result table, then, populating it from the EmployeeID data grid combo box column, and saving all the result table contents in separate XML file C:\OrdersFile.xml.

private void btnSaveData_Click(object sender, EventArgs e)
{
    //This integer represents the no empty rows number
    int RowsNotEmptyNumber = ordersGridView.Rows.Count - 1;
    //This integer represents the columns number
    int ColumnsNumber = ordersGridView.Columns.Count - 1;
    //Create a the result data table
    DataTable ResultTable = OrdersTable1.Copy();
    //Create a new data column for the EmployeesID
    DataColumn EmpIdColumn = new DataColumn("EmployeeID", typeof(int));
    // Add it to the result table
    ResultTable.Columns.Add(EmpIdColumn);
    // Populate the new added column with data from EmployeeID data Grid view combo box column
    for (int i = 0; i < RowsNotEmptyNumber; i++)
    ResultTable.Rows[i]["EmployeeID"] = (int)ordersGridView[ColumnsNumber,i].Value;
    // Write the XML file
    ResultTable.WriteXml(@"C:\OrdersFile.xml", XmlWriteMode.IgnoreSchema);
    // Tell the user that the file is created
    MessageBox.Show(@"The C:\OrdersFile.xml file is written", @"Write C:\OrdersFile.xml file ", MessageBoxButtons.OK,
    MessageBoxIcon.Information);
}

Now, fire up the application and populate the ordersGridView with some data. Try to see how the hosted data grid view combo box cells of this data grid combo box column behave.

Figure 4

When clicking on the button this message will appear:

Figure 5

Close the application and go to C:\OrdersFile.xml. Open this last one. The result will be:

<?xml version="1.0" standalone="yes"?>

</Table2>

</Table2>

</Table2>

</Table2>

</Table2>

</Table2>

</Table2>

</DocumentElement>

Up Next

Ebook Download

View all

Hands on ASP.NET GridView

Read by 12k people

Download Now!

Learn

View all