Introduction
Displaying data in tabular format is one of the common requirements in Windows-based applications. No wonder the DataGridView control is popular amongst Windows Forms developers. The DataGridView control has many enhancements over the earlier DataGrid and it is worthwhile for any Windows developer to master this control. In this article, I am going to illustrate some of the features that were missing (or tedious to implement) in the earlier versions of .NET. Specifically, I will show:
- How to perform data binding with DataSet
- How to add button and combo box columns to the grid
- How to validate data in the grid
Sample Scenario
Throughout the examples, you will use the Customers table of the Northwind database. So, make sure that you have installed the Northwind database in your installation of SQL Server. Although the Customers table contains many columns, you will restrict your examples to use the CustomerID, CompanyName, ContactName, and Country columns only. You will develop a Windows application that displays data from Customers table using the DataGridView control.
To begin with, create a new Windows application using C# as the programming language (see Figure 1).
Then, add a new class named Customer to the project and add the following code to it.
public class Customer
{
private static string strConn;
static Customer()
{
strConn = "data source=.;initial catalog=northwind;
user id=sa;password=sa";
}
public static DataSet GetDataSet()
{
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select customerid,
companyname,contactname,country from customers", strConn);
da.Fill(ds, "customers");
return ds;
}
public static void UpdateDataSet(DataSet ds)
{
SqlConnection cnn = new SqlConnection(strConn);
string sqlInsert, sqlUpdate, sqlDelete;
sqlInsert = "insert into customers(customerid,companyname,
contactname,country) values(@p1,@p2,@p3,@p4)";
sqlUpdate = "update customers set companyname=@p2,
contactname=@p3,country=@p4 where customerid=@p1";
sqlDelete = "delete from customers where customerid=@p1";
SqlParameter[] pInsert = new SqlParameter[4];
SqlParameter[] pUpdate = new SqlParameter[4];
SqlParameter[] pDelete = new SqlParameter[1];
pInsert[0] = new SqlParameter("@p1", SqlDbType.VarChar, 5,
"CustomerID");
pInsert[1] = new SqlParameter("@p2", SqlDbType.VarChar, 40,
"CompanyName");
pInsert[2] = new SqlParameter("@p3", SqlDbType.VarChar, 40,
"ContactName");
pInsert[3] = new SqlParameter("@p4", SqlDbType.VarChar, 40,
"Country");
pUpdate[0] = new SqlParameter("@p2", SqlDbType.VarChar, 40,
"CompanyName");
pUpdate[1] = new SqlParameter("@p3", SqlDbType.VarChar, 40,
"ContactName");
pUpdate[2] = new SqlParameter("@p4", SqlDbType.VarChar, 40,
"Country");
pUpdate[3] = new SqlParameter("@p1", SqlDbType.VarChar, 5,
"CustomerID");
pDelete[0] = new SqlParameter("@p1", SqlDbType.VarChar, 5,
"CustomerID");
SqlCommand cmdInsert = new SqlCommand(sqlInsert,cnn);
SqlCommand cmdUpdate = new SqlCommand(sqlUpdate,cnn);
SqlCommand cmdDelete = new SqlCommand(sqlDelete,cnn);
cmdInsert.Parameters.AddRange(pInsert);
cmdUpdate.Parameters.AddRange(pUpdate);
cmdDelete.Parameters.AddRange(pDelete);
SqlDataAdapter da = new SqlDataAdapter();
da.InsertCommand = cmdInsert;
da.UpdateCommand = cmdUpdate;
da.DeleteCommand = cmdDelete;
da.Update(ds, "customers");
ds.AcceptChanges();
}
public static DataSet GetCountries()
{
DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter("select distinct
country from customers", strConn);
da.Fill(ds, "countries");
return ds;
}
}
The Customer class stores the database connection string in a static variable (strConn). You use a static variable because all the methods of the Customer class are static. Note that the strConn variable is initialized in a static constructor.
The GetDataSet() static method returns a DataSet filled with all the records from the Customers table. Inside, it uses an SqlDataAdapter instance to fill a DataSet. The name of the DataTable being created is supplied in the second parameter of the Fill() method (customer in above code).
The UpdateDataSet() static method accepts a DataSet whose changes are to be saved back to the database. Inside, it declares three SqlCommand objects, each representing INSERT, UPDATE, and DELETE queries respectively. Instead of specifying the CommandText and Connection properties, you use one of the constructors of the SqlCommand class that accepts the query to be executed and underlying connection. Your INSERT, UPDATE, and DELETE queries have one or more parameters. Hence, you declare three arrays of SqlParameter objects viz. pInsert, pUpdate, and pDelete. These parameters are added to the Parameters collection of SqlCommand objects by using the AddRange() method of the Parameters collection. Then, a new instance of the SqlDataAdapter class is created and its InsertCommand, UpdateCommand, and DeleteCommand properties are set to respective SqlCommand objects. To save the changes from DataSet back to the database, the Update() method of SqlDataAdapter class is called.
The GetCountries() method simply returns a DataSet filled with distinct country values from the Customers table. You need these countries later when creating your data entry form.
Simple Data Binding
Now that you have created a class that supplies the required data, create a Windows Form that displays it in a tabular form. Open the default Windows Form and drag and drop a DataGridView control on it. The DataGridView control is present on the Data node of the toolbox (see Figure 2).
Then, open the smart tag of the DataGridView control (see Figure 3) and select the “Edit Columns” option.
Doing so will open the “Edit Columns” dialog, as shown in Figure 4.
Click on the Add button to open the “Add Columns” dialog (see Figure 5).
The “Add Columns” dialog allows you to add a new column to the DataGridView control. While adding a column, you can specify its programmatic Name, column heading (Header Text), and its type (Textbox, Button, ComboBox, and so forth). These properties can be changed even after adding the column. Add in all four textbox columns to the grid for showing CustomerID, CompanyName, ContactName, and Country columns, respectively. Then, set the DataPropertyName property of each of the columns to CustomerID, CompanyName, ContactName, and Country, respectively. The DataPropertyName property indicates the name of a column from the underlying data source that is to displayed in the column of the grid.
After configuring the columns, go to the Load event handler of the form and write the following code.
private void Form1_Load(object sender, EventArgs e)
{
dataGridView1.DataSource = Customer.GetDataSet();
dataGridView1.DataMember = "customers";
}
The above code sets the DataSource property of the DataGridView control to the DataSet returned by GetDataSet() method. The DataSource property can specify any type of “collection” such as DataSet, DataTable, ArrayList, or generic List. If your data source is a DataSet, you must specify the DataMember property also. The DataMember property specifies the name of the DataTable from the DataSet that is to be bound with the grid.
Whatever code you have written up till now will take care of displaying the data in the DataGridView. However, any changes made to the data are made to the DataSet only. To save these changes back to the database, you must call UpdateDataSet() method of the Customer class. To call this method, add a Button control below the DataGridView control and write the following code in its Click event handler.
private void button1_Click(object sender, EventArgs e)
{
Customer.UpdateDataSet((DataSet)dataGridView1.DataSource);
}
Here, you simply call the UpdateDataSet() method. Notice that you need to typecast the object specified by DataSource property to the appropriate type.
Run the application and you should see the DataGridView control filled with the data (see Figure 6).
Notice that You can add, edit, and delete data from the grid without writing any specific code.
Using Button and ComboBox Columns
Although your grid is functional and allows you to add, edit, and delete data, it would be nice if you could:
- Show a list of acceptable countries in the Country column from which the user can pick the value.
- Provide a Delete button for each row. Because the built-in delete functionality is not very obvious (you need to click in the left hand side margin and then hit the Delete button), some users may find it confusing.
Fortunately, the DataGridView control comes with a set of predefined column types. The six types of columns supported by the DataGridView control are:
- DataGridViewTextBoxColumn: Displays a Textbox in each cell of the column for editing the values
- DataGridViewButtonColumn: Displays a push button in each cell of the column
- DataGridViewCheckBoxColumn: Displays a checkbox in each cell of the column
- DataGridViewComboBoxColumn: Displays a combo box in each cell of the column
- DataGridViewImageColumn: Displays an image in each cell of the column
- DataGridViewLinkColumn: Displays a link button in each cell of the column
To see these column types in action, you will modify your preceding example. Open the “Edit Columns” dialog of the DataGridView control and delete the existing Country column. Then, add a new DataGridViewComboBoxColumn column to the grid and set its Header Text to Country. Finally, add a new DataGridViewButtonColumn. Then, set the DataPropertyName property of the Country combo box column to Country. Also, set the Text property of the button column to Delete and UseColumnTextForButtonValue property to True. The UseColumnTextForButtonValue property governs if the value indicated by the Text property will be used as the button caption.
Now, modify the Load event handler of the form as shown below:
private void Form1_Load(object sender, EventArgs e)
{
DataGridViewComboBoxColumn lst=
(DataGridViewComboBoxColumn)dataGridView1.Columns[3];
lst.DataSource = Customer.GetCountries().Tables[0];
lst.DisplayMember = "Country";
lst.ValueMember = "Country";
dataGridView1.DataSource = Customer.GetDataSet();
dataGridView1.DataMember = "customers";
}
The above code first gets a reference to the combo box column and sets its DataSource property to the countries DataTable. The DisplayMember property specifies the column name to be displayed in the combobox. Similarly, the ValueMember property specifies the column name whose value is to be accessed programmatically. Finally, the DataGridView is bound with the DataSet as before.
If you run the form now, you should see something similar to Figure 7.
Notice how the Country column shows a combo box from which you can pick up a country value. Also, see how the button column is displayed as a push button. At this stage, clicking the Delete button won’t do anything. To add this functionality, you must handle a CellClick event. The following code shows what the CellClick event handler looks like:
private void dataGridView1_CellClick(object sender,
DataGridViewCellEventArgs e)
{
if (e.ColumnIndex >= 0)
{
if (dataGridView1.Columns[e.ColumnIndex].Name == "Column5")
{
dataGridView1.Rows.RemoveAt(e.RowIndex);
}
}
}
Inside the CellClick event handler, you check whether the name of the cell being clicked is Column5—the button column. If so, you call the RemoveAt() method of the Rows collection of the DataGridView. The DataGridViewCellEventArgs parameter received by the event handler supplies the row and column index.
Validating Data in DataGridView
In the examples above, tiy were able to edit the data at will. For example, even if the CustomerID column is the primary key column, you are able to change its values. Similarly, there is no check on the new values being entered in other columns. There are many properties and events of the DataGridView control that help you build a robust validation mechanism. To understand how this is done, implement the following constraints and validations:
- The CustomerID column must be read only for the existing rows. However, a new row must be editable.
- The newly entered CustomerID must be at least five characters long.
- The user must be prompted with a confirmation message before deleting a row.
To implement the first constraint, open the “Edit Columns” dialog of the DataGridView control and set the ReadOnly property of the CustomerID column to True. This will ensure that the CustomerID column is non editable.
To implement the second requirement, you must handle the DefaultValuesNeeded event. The DefaultValuesNeeded event is raised when the user inserts a new row in the DataGridView. This event can be used to provide default values to the new record. In the example, you will use it to make CustomerID column editable. Write the following code in the DefaultValuesNeeded event handler.
private void dataGridView1_DefaultValuesNeeded(object sender,
DataGridViewRowEventArgs e)
{
e.Row.Cells["Column1"].ReadOnly = false;
}
The DataGridViewRowEventArgs parameter gives a reference to the new row being added. You simply set the ReadOnly property of Column1 (CustomerID) to False. This way, the user can enter a value in the CustomerID column for just the new row.
To prompt the user about row deletion, you need to handle the UserDeletingRow event of the DataGridView control. This event is raised prior to deleting a row from the grid. You can cancel the delete operation if some validation fails. In your case, the UserDeletingRow event handler looks like this:
private void dataGridView1_UserDeletingRow(object sender,
DataGridViewRowCancelEventArgs e)
{
DialogResult result = MessageBox.Show("Do you want to delete
this record?", "Warning", MessageBoxButtons.YesNo);
if (result == DialogResult.No)
{
e.Cancel = true;
}
}
Here, you display a message box to the user with a warning message. If the user decides not to delete the row, you set the Cancel property of DataGridViewRowCancelEventArgs parameter to True. Doing so cancels the delete operation. Note that the UserDeletingRow event is not raised if you remove a row using the RemoveAt() method. If you try to delete a row (by selecting it and pressing the delete key), you will see a message box, as shown below:
To perform any custom validation on the cell or row being edited, you can use the CellValidating and RowValidation events, respectively. If you want to validate the data cell by cell, the former event is useful where you want to validate the data for the entire row at once then the later event is handy. In your example, you use the CellValidating event, as shown below:
private void dataGridView1_CellValidating(object sender,
DataGridViewCellValidatingEventArgs e)
{
if (dataGridView1.Columns[e.ColumnIndex].Name == "Column1")
{
if (e.FormattedValue.ToString().Length < 5)
{
dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].
ErrorText = "Customer ID must be at least 5
characters long!";
}
}
}
Here, you want to ensure that the length of the new CustomerID is at least five characters. You do this by using the FormattedValue property of the DataGridViewCellValidatingEventArgs parameter. This property returns the new value entered in the cell. If it is less than five characters, you set the ErrorText property of that cell to some error message. Notice how the ColumnIndex and RowIndex properties of DataGridViewCellValidatingEventArgs parameter are used. At run time, if the validation fails, the DataGridView displays a red exclamation icon in that cell (see below). Hovering the mouse pointer on the icon displays the ErrorText that you set earlier.
Summary
The DataGridView control of Windows Forms is a versatile control that can help you create professional-looking data entry forms. It not only supports data binding but also provides frequently needed column types, such as combo box and button. The DataGridView event model is really extensive and one can take control of validation and entry by using many of these events. Although you used DataSet as the source of your data, you also could have used generic collections if required.
Download the Code
You can download the code that accompanies the article here.
About the Author
Bipin Joshi is the proprietor of BinaryIntellect Consulting, where he conducts premier training programs on .NET technologies. He wears many hats, including software consultant, mentor, prolific author, webmaster, Microsoft MVP, and member of ASPInsiders. Having adopted the Yoga way of life, Bipin also teaches Kriya Yoga to interested individuals. His detailed profile can be read on his blog. He also can be reached there.
