Introducing ComponentOne ADO.NET DataExtender

Applies To:

ADO.NET DataExtender

Author:

John Juback

Published On:

11/7/2005

ComponentOne ADO.NET DataExtender (a.k.a. C1DataExtender) simplifies data modeling and programming for Windows Forms applications developed with Visual Studio 2005. A single C1DataViewSet component on a form represents a set of views based on DataTables from an ADO.NET 2.0 DataSet, any master-detail relationships between them, and optional runtime characteristics such as display formats and validation rules. Using a visual designer, a developer specifies in a central location a set of views available to bound controls, along with custom application logic. Some of the key benefits of C1DataExtender include:

Connectivity

C1DataViewSet can directly consume an existing typed or untyped ADO.NET DataSet. It can also work without a DataSet by accepting a database connection string, in which case it automatically retrieves the schema.

Programmability

Clear navigational and data changing event handlers are provided for each view defined.

Smart Update

The Update method of C1DataViewSet automatically determines the correct order in which rows should be committed to the server, taking into account master-detail relationships between tables. It also refreshes client row columns with new values generated on the server, including server-generated auto-increment columns.

Composite Views

Developers can expose multiple related tables as a single rowset by defining a composite view, either visually or using an SQL-like statement. As updates occur at the view or table level, C1DataExtender automatically synchronizes the affected rowsets.

Column Styles

UI-related attributes such as format strings, edit masks, and lookup lists can be associated with DataColumns in one centralized location. These column styles propagate to any view based on the underlying table, and are in turn consumed by ComponentOne grid and input controls. A sample project demonstrates how to apply column styles to any intrinsic or third-party control.

Calculated Columns

Read-only calculated columns can be specified in the visual designer using any .NET-compatible language, or as part of the SQL-like statement that defines the view. The column expressions are evaluated at run time as navigation and updates occur through interaction with bound controls.

Constraint Expressions

C1DataExtender supports column and row level data validation expressions using the syntax of any .NET-compatible language, allowing developers to define constraints of unlimited complexity. The visual designer also provides expression builders that handle many common data validation scenarios.

The easiest way to get started with C1DataExtender is to place a C1DataViewSet component on a form, then use its Smart Tag panel to specify a data provider and a connection string. Using this approach, you do not need to create a project data source, nor do you need to write any code.

The C1DataViewSet component exposes a collection of C1DataView objects, each of which represents a simple DataTable or a composite view (similar to an SQL join) that combines multiple DataTables. When a bound control such as DataGridView has its DataSource property set to a C1DataViewSet instance, its DataMember dropdown will show the list of available C1DataView objects.

At this point, the C1DataViewSet component does not have any views defined. Using the C1DataViewSet Editor (accessible from the Edit verb on the Smart Tag panel), you can define one or more views at design time. If the underlying data source has table relationships defined, then you can nest individual tables accordingly. For example, the following illustration depicts a C1DataViewSet that exposes two related DataTables, Customers and Orders.

The left side of the C1DataViewSet Editor shows the hierarchical C1DataView structure, while the right side provides a design surface for editing the selected element, such as a C1DataView or one of its C1ViewColumn objects (derived from the underlying DataColumn). A Preview tab is also provided for examining the views that will be exposed to bound controls.

To bind a control to a C1DataView object, first set its DataSource property to a C1DataViewSet component, which appears in the List Instances node for the current form.

Next, choose one of the available C1DataView objects from the DataMember dropdown. Note that this list will always be non-hierarchical, even if you defined master-detail relationships in the C1DataViewSet Editor.

To implement master-detail grids, you would simply set the DataMember of one grid to Customers and the DataMember of the other grid to Orders. At run time, C1DataExtender automatically handles row currency as the user navigates within the parent grid.

The C1DataViewSet component can also consume an existing DataSet component directly. In this case, you would still use the C1DataViewSet Editor to specify the set of C1DataView objects to be exposed as DataMembers.

Simplicity

A single C1DataViewSet component serves as the binding agent for all tables and relations. Without C1DataExtender, two components (a BindingSource and a DataAdapter) are required to represent each table or relation.

Connectivity

C1DataViewSet can represent data from the following types of data sources:

Typed ADO.NET DataSet

The DataSet property connects C1DataViewSet to a typed dataset component that has been placed on a Form. In this case, C1DataViewSet retrieves Table Adapters, defined as a part of typed dataset definition, for each DataTable. To fetch data and commit changes back to the server, C1DataViewSet uses the information represented by the adapters.

Untyped ADO.NET DataSet

The DataSet property connects C1DataViewSet to an untyped dataset component that has been placed on a Form. In this case, C1DataViewSet is not able to fetch data and commit changes back to the server. Your application should create, fill, and update the appropriate DataTable objects manually.

Connection String

In this case, the ConnectionString and ConnectionProvider properties define a connection to a database. The ConnectionString property defines a database that is used as data source. The ConnectionProvider property defines the name of the ADO.NET Data Provider that is used when connecting to the database. C1DataViewSet retrieves database schema information, automatically creates an internal ADO.NET dataset, which does not appear on the form, and works with it. This dataset is still accessible at run time via the DataSet property.

Programmability

The C1DataViewSet component supports the following events, which are useful for responding to changes in row currency, current row values, and connections:

CalculateColumn

Occurs when a C1ViewColumn value is being retrieved.

ColumnChanged

Occurs after a value has been changed for the specified C1ViewColumn in a C1ViewRow.

ColumnChanging

Occurs when a value is being changed for specified C1ViewColumn in a C1ViewRow.

ConnectionOpening

Occurs before C1DataViewSet opens a connection for data and schema fetching or data updating. This event allows you to set up connection properties such as login credentials prior to opening.

CurrentChanged

Occurs when the current C1ViewRow or any of its values have been changed.

PositionChanged

Occurs when the Position property has been changed for the specified C1DataView object.

RowChanged

Occurs after editing of a C1ViewRow has been completed.

RowChanging

Occurs before editing of a C1ViewRow has been completed.

ViewListChanged

Occurs on any change in rows of the specified C1DataView.

Within these event handlers, the following properties can be used to address the current row for the affected C1DataView object:

Current

Returns the current C1ViewRow of the CurrencyManager that services this C1DataView.

Position

Returns the position of the current C1ViewRow in the CurrencyManager that services this C1DataView.

Smart Update

The Update method of the C1DataViewSet component automatically determines the correct order in which rows should be committed to the server. It also refreshes client row columns with new values generated on the server, including the cases of server-generated auto-increment columns in conjunction with master-detail relationships between tables.

Without C1DataExtender, the developer must call the Update method of one or more DataAdapters in order to commit changes from a DataSet to the underlying data source. In many circumstances, the order in which DataSet changes are sent to the data source is crucial. For example, if a primary key value for an existing row is updated, and a new row has been added with the new primary key value, the update must be processed before the insertion.

When multiple related tables are involved, the update logic can get rather tricky. Consider the following example, which handles insertions, updates, and deletions for two related tables, Customers and Orders:

// handle parent table insertions  
DataTable dt = this.nwindDataSet1.Customers.GetChanges(DataRowState.Added);  
if (dt != null)  
    this.customersTableAdapter.Update(dt as NwindDataSet.CustomersDataTable);  

// handle child table insertions  
dt = this.nwindDataSet1.Orders.GetChanges(DataRowState.Added);  
if (dt != null)  
    this.ordersTableAdapter.Update(dt as NwindDataSet.OrdersDataTable);  

// handle parent table modifications  
dt = this.nwindDataSet1.Customers.GetChanges(DataRowState.Modified);  
if (dt != null)  
    this.customersTableAdapter.Update(dt as NwindDataSet.CustomersDataTable);  

// handle child table modifications  
dt = this.nwindDataSet1.Orders.GetChanges(DataRowState.Modified);  
if (dt != null)  
    this.ordersTableAdapter.Update(dt as NwindDataSet.OrdersDataTable);  

// handle child table deletions  
dt = this.nwindDataSet1.Orders.GetChanges(DataRowState.Deleted);  
if (dt != null)  
    this.ordersTableAdapter.Update(dt as NwindDataSet.OrdersDataTable);  

// handle parent table deletions  
dt = this.nwindDataSet1.Customers.GetChanges(DataRowState.Deleted);  
if (dt != null)  
    this.customersTableAdapter.Update(dt as NwindDataSet.CustomersDataTable);

If more than two tables are related, the update logic becomes even more complex. Compare this with C1DataExtender, which requires only a single line of code regardless of how many tables are involved:

this.c1DataViewSet1.Update();

Composite Views

Composite views expose multiple related tables as a single "flat" table. As updates are made to the composite view, they are automatically reflected in the constituent tables, and vice versa.

The following illustration depicts a diagram of a composite view created in the C1DataViewSet Editor.

Column Styles

Column styles are UI-related attributes that can be assigned to data columns in the C1DataViewSet Editor, then realized at run time when bound to ComponentOne grid or input controls. Some of the available column style attributes include:

  • General characteristics such as column width, horizontal alignment, and visibility
  • Format strings (predefined or custom)
  • Input masks
  • Lookup lists (derived from foreign key values or specified directly)

The following illustration depicts an itemized list of static lookup values specified for an individual data column (Employees.TitleOfCourtesy).

At run time, when the Employees view is bound to a C1TrueDBGrid control, a dropdown combo box is automatically created for that column. Note also that the column caption has been changed from TitleOfCourtesy to Honorific. This was done by setting the column's Caption property in the C1DataViewSet Editor.

Column styles are only honored by ComponentOne controls such as C1TrueDBGrid. However, a sample project is provided that demonstrates how to add column style support to the DataGridView included with Microsoft Visual Studio 2005. You can use this sample as a model for extending any intrinsic or third-party control.

Calculated Columns

Read-only calculated columns can be specified in the C1DataViewSet Editor using any .NET-compatible language, or as part of the SQL-like statement that defines the view. The column expressions are evaluated at run time as navigation and updates occur through interaction with any bound control, not just ComponentOne controls.

Note that calculated columns in bound controls are updated immediately as the underlying field values change, even if the current row does not change.

Constraint Expressions

Column and row level constraint expressions can be specified in the C1DataViewSet Editor using any .NET-compatible language. The expressions are evaluated at run time as updates occur through interaction with any bound control, not just ComponentOne controls.

Column level constraints are useful for enforcing upper and lower bounds and non-null values. For example, the following constraint expression on the Discount field sets the allowable range from 0% to 30%, inclusive:

String s = newValue.ToString().Trim();  
Single v = Single.Parse(s);  
return (v >= 0) && (v <= 0.3);

Row level constraints are typically used to express validation criteria involving multiple columns, such as a maximum discount that varies based on the total amount of an order.

Decimal totalPrice = (Decimal) row["TotalPrice"];  
Single discount = (Single) row["Discount"];  

if (totalPrice < 100)  
    return (discount == 0);  
else if (totalPrice < 1000)  
    return (discount <= 0.15);  
else  
    return true;

In this example, a row level constraint is established between the TotalPrice and Discount fields as follows:

  • No discount is permitted for orders under $100.
  • A maximum discount of 15% is permitted for orders less than $1000.
  • No maximum discount is specified for orders of $1000 or more.

Even though no maximum value is specified at the row level, any column level constraints established on the Discount field still apply. Therefore, the maximum discount remains 30%, even if the total amount is over $1000.

The C1DataViewSet Editor includes built-in code snippets and expression builder dialogs that cover some of the most common data validation scenarios.

ComponentOne ADO.NET DataExtender builds upon the enhancements in ADO.NET 2.0 to streamline Windows Forms database development. A single component, C1DataViewSet, serves as the binding agent for all controls on a form. A visual designer lets you expose simple tables or composite views with optional constraint expressions for centralized data validation.

You can also use the Column Styles tab of the visual designer to assign format strings, edit masks, and lookup items to individual columns that will be rendered using ComponentOne controls such as C1TrueDBGrid.

For more information, or to download a free 30-day trial version of ComponentOne Studio for WinForms, visit the link below:

Download evaluation

MESCIUS inc.

comments powered by Disqus