SUMMARY
Use this step-by-step guide to extend the Windows Form
DataGridTextBoxColumn control so that you can display data from other tables in the
DataGrid, effectively joining two or more related
DataTables for the purposes of display.
NOTE: This technique is not required if you use individual controls, because you can link them hierarchically.
The sample application later in this article demonstrates how to display fields from the Orders, Employees, and Customers tables in the Northwind database in a Windows Form
DataGrid at the same time. The fields from the Orders table are editable; the fields from the related reference tables are not. You can select a new EmployeeID or CustomerID to change the reference table fields automatically to reflect the new value.
The
JoinTextBoxColumn class must be mapped to a field in the many-side table (in this case, Orders), even though it displays a field from the reference table. The actual field does not matter. However, you cannot bind more than one column style to a field in the underlying DataView. If you run out of fields, you can add some dummy fields to the
DataTable and bind to those, as demonstrated in the Form.Load code in the sample application.
back to the top
Requirements
The following list outlines the recommended hardware, software, network infrastructure, and service packs that you need:
- Microsoft Windows 2000 Professional, Microsoft Windows 2000 Server, Microsoft Windows 2000 Advanced Server, or Microsoft Windows NT 4.0 Server.
- Microsoft Visual Studio .NET.
- Microsoft SQL Server 7.0 or later.
This article assumes that you are familiar with the following topics:
- Microsoft Visual Studio .NET.
- Microsoft ADO.NET fundamentals and syntax.
back to the top
Create the Project and Add Code
-
Start Visual Studio .NET and create a new Visual C# Windows Form application.
-
Add a reference to System.Data.SQLClient at the top of the Form1.cs file:
using System.Data.SqlClient;
-
Add the following DataSet variable declaration at the Form1 class level:
private DataSet ds;
-
Add a DataGrid control to the form.
-
Double-click the form background. Type or paste the following code to add a Form1_Load event handler to generate sample data and to initialize the DataGrid to use the JoinTextBoxColumn class.
// Read data and set DataRelation objects.
SqlConnection cn = new SqlConnection("Server=localhost;uid=myuser;pwd=mypassword;Database=Northwind");
SqlDataAdapter daCust = new SqlDataAdapter("SELECT * FROM Customers ", cn);
SqlDataAdapter daEmp = new SqlDataAdapter("SELECT * FROM Employees", cn);
SqlDataAdapter daOrd = new SqlDataAdapter("SELECT * FROM Orders WHERE CustomerID LIKE 'B%'", cn);
ds = new DataSet();
daCust.Fill(ds, "Cust");
daEmp.Fill(ds, "Emp");
daOrd.Fill(ds, "Ord");
ds.Relations.Add("CustOrd", ds.Tables["Cust"].Columns["CustomerID"], ds.Tables["Ord"].Columns["CustomerID"]);
ds.Relations.Add("EmpOrd", ds.Tables["Emp"].Columns["EmployeeID"], ds.Tables["Ord"].Columns["EmployeeID"]);
/*
Add dummy fields for mapping the JoinTextBoxColumn styles.
Do this only if you are mapping more fields than
there are in the underlying DataTable. You can map the
JoinTextBoxColumn class to any column because it references
fields in another table.
*/
ds.Tables["Ord"].Columns.Add("FirstName", typeof(string));
ds.Tables["Ord"].Columns.Add("LastName", typeof(string));
ds.Tables["Ord"].Columns.Add("CompanyName", typeof(string));
ds.Tables["Ord"].Columns.Add("ContactName", typeof(string));
/*
Map fields from all three tables. Use a standard
DataGridTextBoxColumn style for base table fields because they can
be updated. Use the JoinTextBoxColumn style for reference table
fields. These are read-only.
*/
DataGridTableStyle ts = new DataGridTableStyle();
DataGridColumnStyle cs;
cs = new DataGridTextBoxColumn();
cs.Width = 75;
cs.MappingName = "OrderID";
cs.HeaderText = "Order ID";
ts.GridColumnStyles.Add(cs);
cs = new DataGridTextBoxColumn();
cs.Width = 75;
cs.MappingName = "EmployeeID";
cs.HeaderText = "Emp ID";
ts.GridColumnStyles.Add(cs);
cs = new JoinTextBoxColumn("EmpOrd", ds.Tables["Emp"].Columns["FirstName"]);
cs.Width = 120;
cs.MappingName = "FirstName";
cs.HeaderText = "First Name";
ts.GridColumnStyles.Add(cs);
cs = new JoinTextBoxColumn("EmpOrd", ds.Tables["Emp"].Columns["LastName"]);
cs.Width = 120;
cs.MappingName = "LastName";
cs.HeaderText = "Last Name";
ts.GridColumnStyles.Add(cs);
cs = new DataGridTextBoxColumn();
cs.Width = 75;
cs.MappingName = "CustomerID";
cs.HeaderText = "Cust ID";
ts.GridColumnStyles.Add(cs);
cs = new JoinTextBoxColumn("CustOrd", ds.Tables["Cust"].Columns["CompanyName"]);
cs.Width = 120;
cs.MappingName = "CompanyName";
cs.HeaderText = "Company";
ts.GridColumnStyles.Add(cs);
cs = new JoinTextBoxColumn("CustOrd", ds.Tables["Cust"].Columns["ContactName"]);
cs.Width = 120;
cs.MappingName = "ContactName";
cs.HeaderText = "Contact";
ts.GridColumnStyles.Add(cs);
cs = new DataGridTextBoxColumn();
cs.Width = 75;
cs.MappingName = "OrderDate";
cs.HeaderText = "Ord. Date";
ts.GridColumnStyles.Add(cs);
ts.MappingName = "Ord";
dataGrid1.TableStyles.Add(ts);
dataGrid1.DataSource = ds;
dataGrid1.DataMember = "Ord";
-
Change the connection string that is being passed to the SqlConnection constructor so that it contains the correct server name, username, and password for your instance of SQL Server.
-
Add the following class definition to the Form1.cs file. You can place it inside the Form1 class definition or outside the form definition. This code contains the JoinTextBoxColumn class that extends the DataGridTextBoxColumn class.
public class JoinTextBoxColumn : DataGridTextBoxColumn
{
// Store information necessary to retrieve fields.
private string relationName;
private DataColumn parentField;
public JoinTextBoxColumn(string RelationName, DataColumn ParentField)
{
relationName = RelationName;
parentField = ParentField;
base.ReadOnly = true;
}
protected override object GetColumnValueAtRow(CurrencyManager cm, int RowNum)
{
// Get the current DataRow from the CurrencyManager.
// Use the GetParentRow and the DataRelation name to get
// the parent row.
// Return the field value from the parent row.
try
{
DataRow dr = ((DataView)cm.List)[RowNum].Row;
DataRow drParent = dr.GetParentRow(relationName);
return drParent[parentField].ToString();
}
catch
{
return ""; // Necessary when adding rows.
}
}
protected override bool Commit(CurrencyManager cm, int RowNum)
{
return false; // Dummy implementation because it is read-only
}
public new bool ReadOnly // Hide base member ReadOnly.
{
get
{
return true;
}
}
}
-
Run the application. The DataGrid has several columns. Besides the OrderID, CustomerID, EmployeeID, and OrderDate columns in the Orders table, the DataGrid contains the FirstName and LastName fields from the Employees table and the CompanyName and ContactName fields from the Customers table.
- Edit the data (add, delete, modify rows). The fields from the Employees and Customers tables are read-only. If you edit the EmployeeID or CustomerID fields from the Orders table, the referenced fields will reflect the change.
back to the top
Troubleshooting
Some common problems that you may run into when you use this code are:
-
If you give the JoinTextBoxColumn object a non-existant DataRelation name, you may receive an exception error when the DataGrid is rendered. The exception error does not identify which line of code caused the error. You can redesign the constructor of the class to accept a DataRelation object so that this type of error can be caught during the initialization phase and to isolate the problem to a specific line of code.
-
The column is not displayed unless you set the Width property. The default setting is 0 (hidden).
-
The data is not displayed unless you set the MappingName for both the DataGridTableStyle and DataGridColumnStyle objects and assign them to their respective collections. The Mapping name for the JoinTextBoxColumn object can be any field name; the actual field that is displayed is set by the constructor.
-
The column heading will be blank if you don't set the HeaderText property.
back to the top