Data Binding in DataGrid Control Using C#

C# DataGrid control is the most popular data-bound controls in Windows Forms. In this article, learn how to use an ADO.NET DataGrid control to fill data from a database using ADO.NET.

C# DataGrid control displays data in a tabular form, rows and columns. A DataGrid control also provides functionality to sort, filter, scroll, and find records. In this article, let's see how to bind data in a DataGrid control using C# and ADO.NET.
 
To display a table in the System.Windows.Forms.DataGrid at run time, use the SetDataBinding method to set the DataSource and DataMember properties to a valid data source. The following data sources are valid: 
  •  A DataTable 
  •  A DataView 
  •  A DataSet 
  •  A DataViewManager 
  •  A single dimension array 
  •  Any component that implements the IListSource interface 
  •  Any component that implements the IList interface
Data sources are further managed by BindingManagerBase objects. For each table in a data source, a BindingManagerBase can be returned from the form's BindingContext. For example, you can determine the number of rows contained by a data source by returning the associated BindingManagerBase object's Count Property.

To validate data, use the underlying objects that represent data and their events. For example, if the data comes from a DataTable in a DataSet, use the ColumnChanging and RowChanging events. Note Because the number of columns can be customized (by adding or deleting members of the GridColumnStylesCollection) and the rows may be sorted by column, the RowNumber and ColumnNumber property values cannot be guaranteed to correspond to DataRow and DataColumn indexes in a DataTable. Therefore you should avoid using those properties in the Validating event to validate data.

To determine which cell is selected, use the CurrentCell property. Change the value of any cell by using the Item property, which can take both the row and column indexes of the cell, or a single DataGridCell. Monitor the CurrentCellChanged event to detect when the user selects another cell. To determine which part of the control the user clicked, use the HitTest method in the MouseDown event. The HitTest method returns a DataGrid.HitTestInfo object, which contains the row and column of a clicked area.

To manage the appearance of the control at run time, several properties for setting the color and caption attributes are available, including the CaptionForeColor, CaptionBackColor, CaptionFont, and so on. The appearance of the displayed grid (or grids) can be further modified by creating DataGridTableStyle objects and adding them to the GridTableStylesCollection, which is accessed through the TableStyles property. For example, if the DataSource is set to a DataSet containing three DataTable objects, you can add three DataGridTableStyle objects to the collection, one for each table. To synchronize each DataGridTableStyle object with a DataTable, set the MappingName of the DataGridTableStyle to the TableName of the DataTable.

To create a customized view of a table, create a collection of DataGridColumnStyle objects, and set each column's MappingName to the ColumnName of a column that you want to show in the grid. To hide a column, set its MappingName to something other than a valid ColumnName. For each DataGridTableStyle, you can set color and caption attributes that override the settings for the System.Windows.Forms.DataGrid control. However, if those properties are not set, the settings for the control are used by default. The following properties can be overridden by DataGridTableStyle properties:
  •  AllowSorting 
  •  AlternatingBackColor 
  •  BackColor 
  •  ColumnHeadersVisible 
  •  ForeColor 
  •  GridLineColor 
  •  GridLineStyle 
  •  HeaderBackColor 
  •  HeaderFont 
  •  HeaderForeColor 
  •  LinkColor 
  •  PreferredColumnWidth 
  •  PreferredRowHeight 
  •  ReadOnly 
  •  RowHeadersVisible 
  •  RowHeaderWidth 
  •  SelectionBackColor 
  •  SelectionForeColor
To customize the appearance of individual columns, add DataGridColumnStyle objects to the GridColumnStylesCollection, which is accessed through the GridColumnStyles property of each DataGridTableStyle. To synchronize each DataGridColumnStyle with a DataColumn in the DataTable, set the MappingName to the ColumnName of a DataColumn. When constructing a DataGridColumnStyle, you can also set a formatting string that specifies how the column displays data. For example, you can specify that the column use a short-date format to display dates contained in the table.

Please pay attention to you need always create DataGridColumnStyle objects and add them to the GridColumnStylesCollection before adding DataGridTableStyle objects to the GridTableStylesCollection. When you add an empty DataGridTableStyle to the collection, DataGridColumnStyle objects are automatically generated for you. Consequently, an exception will be thrown if you try to add new DataGridColumnStyle objects with duplicate MappingName values to the GridColumnStylesCollection.
 
The following example creates a Windows form, a DataSet containing two DataTable objects, and a DataRelation that relates the two tables. To display the data, a System.Windows.Forms.DataGrid control is then bound to the DataSet through the SetDataBinding method. A button on the form changes the appearance of the grid by creating two DataGridTableStyle objects and setting the MappingName of each object to a TableName of one of the DataTable objects. The example also contains code in the MouseUp event that uses the HitTest method to print the column, row, and part of the grid that has been clicked.
  1. using System;  
  2. using System.ComponentModel;  
  3. using System.Data;  
  4. using System.Drawing;  
  5. using System.Windows.Forms;  
  6. public class Form1 : System.Windows.Forms.Form  
  7. {  
  8.     private System.ComponentModel.Container components;  
  9.     private Button button1;  
  10.     private Button button2;  
  11.     private DataGrid myDataGrid;  
  12.     private DataSet myDataSet;  
  13.     private bool TablesAlreadyAdded;  
  14.     public Form1()  
  15.     {  
  16.         // Required for Windows Form Designer support.  
  17.         InitializeComponent();  
  18.         // Call SetUp to bind the controls.  
  19.         SetUp();  
  20.     }  
  21.     protected override void Dispose(bool disposing)  
  22.     {  
  23.         if (disposing)  
  24.         {  
  25.             if (components != null)  
  26.             {  
  27.                 components.Dispose();  
  28.             }  
  29.         }  
  30.         base.Dispose(disposing);  
  31.     }  
  32.     private void InitializeComponent()  
  33.     {  
  34.         // Create the form and its controls.  
  35.         this.components = new System.ComponentModel.Container();  
  36.         this.button1 = new System.Windows.Forms.Button();  
  37.         this.button2 = new System.Windows.Forms.Button();  
  38.         this.myDataGrid = new DataGrid();  
  39.         this.AutoScaleBaseSize = new System.Drawing.Size(5, 13);  
  40.         this.Text = "DataGrid Control Sample";  
  41.         this.ClientSize = new System.Drawing.Size(450, 330);  
  42.         button1.Location = new Point(24, 16);  
  43.         button1.Size = new System.Drawing.Size(120, 24);  
  44.         button1.Text = "Change Appearance";  
  45.         button1.Click += new System.EventHandler(button1_Click);  
  46.         button2.Location = new Point(150, 16);  
  47.         button2.Size = new System.Drawing.Size(120, 24);  
  48.         button2.Text = "Get Binding Manager";  
  49.         button2.Click += new System.EventHandler(button2_Click);  
  50.         myDataGrid.Location = new Point(24, 50);  
  51.         myDataGrid.Size = new Size(300, 200);  
  52.         myDataGrid.CaptionText = "Microsoft DataGrid Control";  
  53.         myDataGrid.MouseUp += new MouseEventHandler(Grid_MouseUp);  
  54.         this.Controls.Add(button1);  
  55.         this.Controls.Add(button2);  
  56.         this.Controls.Add(myDataGrid);  
  57.     }  
  58.     public static void Main()  
  59. Application.Run(new Form1());  
  60. }  
  61. private void SetUp()  
  62. {  
  63.     // Create a DataSet with two tables and one relation.  
  64.     MakeDataSet();  
  65.     /* Bind the DataGrid to the DataSet. The dataMember 
  66.     specifies that the Customers table should be displayed.*/  
  67.     myDataGrid.SetDataBinding(myDataSet, "Customers");  
  68. }  
  69. protected void button1_Click(object sender, System.EventArgs e)  
  70. {  
  71.     if (TablesAlreadyAdded) return; AddCustomDataTableStyle();  
  72. }  
  73. private void AddCustomDataTableStyle()  
  74. {  
  75.     DataGridTableStyle ts1 = new DataGridTableStyle();  
  76.     ts1.MappingName = "Customers";  
  77.     // Set other properties.  
  78.     ts1.AlternatingBackColor = Color.LightGray;  
  79.     /* Add a GridColumnStyle and set its MappingName  
  80.     to the name of a DataColumn in the DataTable.  
  81.     Set the HeaderText and Width properties. */  
  82.     DataGridColumnStyle boolCol = new DataGridBoolColumn();  
  83.     boolCol.MappingName = "Current";  
  84.     boolCol.HeaderText = "IsCurrent Customer";  
  85.     boolCol.Width = 150;  
  86.     ts1.GridColumnStyles.Add(boolCol);// Add a second column style.  
  87.     DataGridColumnStyle TextCol = new DataGridTextBoxColumn();  
  88.     TextCol.MappingName = "custName";  
  89.     TextCol.HeaderText = "Customer Name";  
  90.     TextCol.Width = 250; ts1.GridColumnStyles.Add(TextCol);  
  91.     // Create the second table style with columns.  
  92.     DataGridTableStyle ts2 = new DataGridTableStyle();  
  93.     ts2.MappingName = "Orders";  
  94.     // Set other properties.  
  95.     ts2.AlternatingBackColor = Color.LightBlue;  
  96.     // Create new ColumnStyle objects  
  97.     DataGridColumnStyle cOrderDate =  
  98.     new DataGridTextBoxColumn();  
  99.     cOrderDate.MappingName = "OrderDate";  
  100.     cOrderDate.HeaderText = "Order Date";  
  101.     cOrderDate.Width = 100;  
  102.     ts2.GridColumnStyles.Add(cOrderDate);  
  103.     /* Use a PropertyDescriptor to create a formatted 
  104.     column. First get the PropertyDescriptorCollection 
  105.     for the data source and data member. */  
  106.     PropertyDescriptorCollection pcol = this.BindingContext  
  107.     [myDataSet, "Customers.custToOrders"].GetItemProperties();  
  108.     /* Create a formatted column using a PropertyDescriptor. 
  109.     The formatting character "c" specifies a currency format. */  
  110.     DataGridColumnStyle csOrderAmount = new DataGridTextBoxColumn(pcol  
  111.     ["OrderAmount"], "c"true);  
  112.     csOrderAmount.MappingName = "OrderAmount";  
  113.     csOrderAmount.HeaderText = "Total";  
  114.     csOrderAmount.Width = 100;  
  115.     ts2.GridColumnStyles.Add(csOrderAmount);  
  116.     /* Add the DataGridTableStyle instances to  
  117.     the GridTableStylesCollection. */  
  118.     myDataGrid.TableStyles.Add(ts1);  
  119.     myDataGrid.TableStyles.Add(ts2);  
  120.     // Sets the TablesAlreadyAdded to true so this doesn't happen again.  
  121.     TablesAlreadyAdded = true;  
  122. }  
  123. protected void button2_Click(object sender, System.EventArgs e)  
  124. {  
  125.     BindingManagerBase bmGrid;  
  126.     bmGrid = BindingContext[myDataSet, "Customers"];  
  127.     MessageBox.Show("Current BindingManager Position: " +  
  128.     bmGrid.Position);  
  129. }  
  130. private void Grid_MouseUp(object sender, MouseEventArgs e)  
  131. {  
  132.     // Create a HitTestInfo object using the HitTest method.  
  133.     // Get the DataGrid by casting sender.  
  134.     DataGrid myGrid = (DataGrid)sender;  
  135.     DataGrid.HitTestInfo myHitInfo = myGrid.HitTest(e.X, e.Y);  
  136.     Console.WriteLine(myHitInfo);  
  137.     Console.WriteLine(myHitInfo.Type);  
  138.     Console.WriteLine(myHitInfo.Row);  
  139.     Console.WriteLine(myHitInfo.Column);  
  140. }  
  141. // Create a DataSet with two tables and populate it.  
  142. private void MakeDataSet()  
  143. {  
  144.     // Create a DataSet.  
  145.     myDataSet = new DataSet("myDataSet");  
  146.     // Create two DataTables.  
  147.     DataTable tCust = new DataTable("Customers");  
  148.     DataTable tOrders = new DataTable("Orders");// Create two columns, and add them to the   
  149.     first table.  
  150. DataColumn cCustID = new DataColumn("CustID"typeof(int));  
  151.     DataColumn cCustName = new DataColumn("CustName");  
  152.     DataColumn cCurrent = new DataColumn("Current"typeof(bool));  
  153.     tCust.Columns.Add(cCustID);  
  154.     tCust.Columns.Add(cCustName);  
  155.     tCust.Columns.Add(cCurrent);// Create three columns, and add them to the second table.  
  156.     DataColumn cID = new DataColumn("CustID"typeof(int));  
  157.     DataColumn cOrderDate = new DataColumn("orderDate"typeof(DateTime));  
  158.     DataColumn cOrderAmount = new DataColumn("OrderAmount"typeof(decimal));  
  159.     tOrders.Columns.Add(cOrderAmount);  
  160.     tOrders.Columns.Add(cID);  
  161.     tOrders.Columns.Add(cOrderDate);  
  162.     // Add the tables to the DataSet.  
  163.     myDataSet.Tables.Add(tCust);  
  164.     myDataSet.Tables.Add(tOrders);  
  165.     // Create a DataRelation, and add it to the DataSet.  
  166.     DataRelation dr = new DataRelation  
  167.     ("custToOrders", cCustID, cID);  
  168.     myDataSet.Relations.Add(dr);  
  169.     /* Populates the tables. For each customer and order,  
  170.     creates two DataRow variables. */  
  171.     DataRow newRow1;  
  172.     DataRow newRow2;  
  173.     // Create three customers in the Customers Table.  
  174.     for (int i = 1; i < 4; i++)  
  175.     {  
  176.         newRow1 = tCust.NewRow();  
  177.         newRow1["custID"] = i;  
  178.         // Add the row to the Customers table.  
  179.         tCust.Rows.Add(newRow1);  
  180.     }  
  181.     // Give each customer a distinct name.  
  182.     tCust.Rows[0]["custName"] = "Alpha";  
  183.     tCust.Rows[1]["custName"] = "Beta";  
  184.     tCust.Rows[2]["custName"] = "Omega";// Give the Current column a value.  
  185.     tCust.Rows[0]["Current"] = true;  
  186.     tCust.Rows[1]["Current"] = true;  
  187.     tCust.Rows[2]["Current"] = false;  
  188.     // For each customer, create five rows in the Orders table.  
  189.     for (int i = 1; i < 4; i++)  
  190.     {  
  191.         for (int j = 1; j < 6; j++)  
  192.         {  
  193.             newRow2 = tOrders.NewRow();  
  194.             newRow2["CustID"] = i;  
  195.             newRow2["orderDate"] = new DateTime(2001, i, j * 2);  
  196.             newRow2["OrderAmount"] = i * 10 + j * .1;  
  197.             // Add the row to the Orders table.  
  198.             tOrders.Rows.Add(newRow2);  
  199.         }  
  200.     }  
  201. }  
  202. }  
Editing with DataGridTextBox and DataGridTextColumn

The DataGridTextBox and the DataGridTextBoxColumn work together to allow users to directly edit values in a DataGrid control column. The DataGridTextBoxColumn derives from DataGridColumnStyle, and is designed to host the DataGridTextBox, which derives from the TextBox control. In addition to the properties, events, and methods of the base control, you can call the KeyPress and KeyDown events with the OnKeyPress and OnMouseDown methods.
  1. private void GetDataGridTextBox()  
  2. {  
  3.     // Gets the DataGridTextBoxColumn from the DataGrid control.DataGridTextBoxColumn   
  4.     myTextBoxColumn;  
  5.     // Assumes the CompanyName column is a DataGridTextBoxColumn.  
  6.     myTextBoxColumn = (DataGridTextBoxColumn)dataGrid1.  
  7.     TableStyles[0].GridColumnStyles["CompanyName"];  
  8.     // Gets the DataGridTextBox for the column.  
  9.     DataGridTextBox myGridTextBox;  
  10.     myGridTextBox = (DataGridTextBox)myTextBoxColumn.TextBox;  
  11. }  
The DataGridTextBoxColumn class derives from the abstract (MustInherit in Visual Basic) class DataGridColumnStyle. At run time, the DataGridTextBoxColumn hosts a DataGridTextBox control that allows users to edit text. Special properties added to the class include Format, and HideEditBox. These properties allow you to access the hosted DataGridTextBox control and its attributes, and set the format for displaying values.

If the data source is a DataTable containing DataColumn objects, the DataType property of the DataColumn should be set to a data type that can logically be edited in a text box control. The following data types are automatically associated with a DataGridTextBoxColumn: Byte, DateTime, Decimal, Double, Int16, Int32, Int64, UInt16, UInt32, UInt64, Single, and String.
  1. private void AddColumn()  
  2. {  
  3.     DataTable myTable = new DataTable();  
  4.     // Add a new DataColumn to the DataTable.  
  5.     DataColumn myColumn = new DataColumn("myTextBoxColumn");  
  6.     myColumn.DataType = System.Type.GetType("System.String");  
  7.     myColumn.DefaultValue = "default string";  
  8.     myTable.Columns.Add(myColumn);  
  9.     // Get the CurrencyManager for the DataTable.  
  10.     CurrencyManager cm = (CurrencyManager)this.BindingContext[myTable];  
  11.     // Use the CurrencyManager to get the PropertyDescriptor for the   
  12.     // new column.  
  13.     PropertyDescriptor pd = cm.GetItemProperties()["myTextBoxColumn"];  
  14.     DataGridTextBoxColumn myColumnTextColumn;  
  15.     // Create the DataGridTextBoxColumn with the PropertyDescriptor.  
  16.     myColumnTextColumn = new DataGridTextBoxColumn(pd);  
  17.     // Add the new DataGridColumn to the GridColumnsCollection.  
  18.     dataGrid1.DataSource = myTable;  
  19.     dataGrid1.TableStyles.Add(new DataGridTableStyle());  
  20.     dataGrid1.TableStyles[0].GridColumnStyles.Add(myColumnTextColumn);  
  21. }
In this article, we learned how to use a DataGrid control using C#. 
 
ADO.NET DataReader is another object plays a major role in C#.