Tutorial 6 - Defining Unbound Columns in a Bound Grid

In this tutorial, you will learn how to use the UnboundColumnFetch event to display two fields (FirstName and LastName) together in one column. You will also learn how to use an SQL statement to create a join between two tables in a database.

1. Start a new .NET project.

2. From the Toolbox on the left side of the IDE draw a C1TrueDBGrid object onto the form. The C1TrueDBGrid icon looks like this:

3. In the TrueDBGrid Tasks menu, locate the Choose Data Source drop-down and select Add Project Data Source. In the adapter’s Data Source Configuration Wizard, either select a connection to TDBGDemo.mdb or create a new connection to this database. On the Choose your database objects page of the wizard, select all fields in the Contacts table and type "DsContacts" into the DataSet name box, and then finish out the wizard.

4. Double click on DsContacts.xsd in the Solution Explorer window to edit it in the Designer. Right click on the Contacts table and choose Configure from the context menu.

5. Modify the SQL string in the Table Adapter Configuration Wizard to: SELECT Customers.FirstName, Customers.LastName, Customers.CustType, Contacts.ContactType, Contacts.Callback, Contacts.ContactDate, Contacts.UserCode, Customers.UserCode AS Expr1 FROM Contacts INNER JOIN Customers ON Contacts.UserCode = Customers.UserCode.

6. The Contacts table is now joined with the Customers table. Click the Finish button to exit the wizard.

7. Return to Design view and if prompted to replace existing column layout, click Yes.

Note: If all of the columns are not showing up in C1TrueDBGrid, select the DataSource again from the drop-down menu.

8. Declare a new global DataTable object in Form1:

· Visual Basic

Dim dtCopy As New DataTable

· C#

DataTable dtCopy = new DataTable;

· Delphi

var dtCopy: DataTable;

dtCopy := DataTable.Create;

9. Now in the Form_Load event add the following code. The first line, supplied by Visual Studio, fills the dataset and the second line makes a copy of this DataSet, which we will use later to populate the unbound column:

· Visual Basic

Me.ContactsTableAdapter.Fill(Me.DsContacts.Contacts)

dtCopy = Me.DsContacts1.Tables(0).Copy()

· C#

this.ContactsTableAdapter.Fill(this.DsContacts.Contacts);

dtCopy = this.DsContacts1.Tables(0).Copy();

· Delphi

Self.ContactsTableAdapter.Fill(Self.DsContacts.Contacts);

dtCopy := Self.DsContacts1.Tables[0].Copy;

10. To create an unbound column, open up the C1TrueDBGrid Designer by clicking on the ellipsis button (…) next to the Columns property in the Properties window. Next click the Insert column button to create a new column. Set the new column's Caption property to “Name” in the left pane. Notice that a value resides in the Caption field, but no value in the DataField, which is how the grid knows that this is an unbound column. The grid now knows to fire the UnboundColumnFetch event.

11. Open the SplitCollection editor by clicking on the ellipsis button next to the Splits property in the Properties window. Now open up the C1DisplayColumnCollection editor by clicking on the ellipsis button next to the DisplayColumns property. In this editor, find the unbound column in the left pane that we just created. It is positioned as the last column in the grid. The DisplayColumns Collection determines the position of the field. Maneuver the column to the desired location by using the up and down arrow buttons in the left pane. Then in the right pane, set its Visible property equal to True. Now our unbound column is visible to the end-user and not just the True DBGrid.

You can hide columns here that are used in the unbound column. Select the FirstName column from the left pane, then in the right, set its Visible property equal to False. This hides the FirstName column from view. Repeat, selecting the LastName column.

12. Add the following code to the UnboundColumnFetch event. This code uses dtCopy to gather values to place into the unbound column, then setting these values equal to e.Value, places the value into the unbound column:

· Visual Basic

Private Sub C1TrueDBGrid1_UnboundColumnFetch(ByVal sender As System.Object, ByVal e As C1.Win.C1TrueDBGrid.UnboundColumnFetchEventArgs) Handles C1TrueDBGrid1.UnboundColumnFetch

If e.Column.Caption = "Name" AndAlso e.Row < dtCopy.Rows.Count Then

e.Value = Me.C1TrueDBGrid1(e.Row, "FirstName").ToString + Me.C1TrueDBGrid1(e.Row, "LastName").ToString

End If

End Sub

· C#

private void c1TrueDBGrid1_UnboundColumnFetch( object sender, C1.Win.C1TrueDBGrid.UnboundColumnFetchEventArgs e)

{

if(e.Column.Caption == "Name" && e.Row < dtCopy.Rows.Count)

{

e.Value = this.c1TrueDBGrid1[e.Row, "FirstName"].ToString() + this.c1TrueDBGrid1[e.Row, "LastName"].ToString();

}

· Delphi

procedure TWinForm.c1TrueDBGrid1_UnboundColumnFetch(sender: System.Object; e: C1.Win.C1TrueDBGrid.UnboundColumnFetchEventArgs);

begin

if ((e.Column.Caption = 'Name') and (e.Row < dtCopy.Rows.Count)) then

e.Value := (Self.C1TrueDBGrid1[e.Row].ToString + Self.C1TrueDBGrid1[e.Row].ToString);

end;

When the application runs, it should look like the following:

Run the program and observe the following:

· C1TrueDBGrid1 displays data from the joined table according to the five columns configured at design time.

· The first column displays the combined FirstName and LastName fields as defined in the UnboundColumnFetch event.

· The CustType, ContactType and Callback columns display numeric values that are quite cryptic to users and provide an unappealing data presentation. In the next three tutorials (7, 8, and 9), techniques will be illustrated that improve both the display and the user interface.

This concludes the tutorial.