Page 1 of 1

Database To Typed-DataSet, With BindingSource III

#1 andrewsw  Icon User is offline

  • blow up my boots
  • member icon

Reputation: 6544
  • View blog
  • Posts: 26,525
  • Joined: 12-December 12

Posted 12 April 2014 - 07:44 AM

Part 1
Part 2
Part 4

Topic List:

  • Extending TableAdapters?
  • Extending the DataSet and DataTables
  • Row and Column Validation
  • DataError Event

MSDN links:

How to: Extend the Functionality of a Dataset
How to: Extend the Functionality of a TableAdapter
Validating Data in Datasets
Walkthrough: Adding Validation to a Dataset
Walkthrough: Handling Errors that Occur During Data Entry in the Windows Forms DataGridView Control

Extending TableAdapters?

The following tutorials are for ASP.NET but still very useful.

Creating a Data Access Layer
Creating a Business Logic Layer

These concentrate on extending the TableAdapters. The second link creates an entirely new BLL (Business Logic Layer) to sit between the Presentation and Data Access Layers. This new layer provides wrappers for all of the TableAdapter methods, and then adds business-logic to these wrapper-methods. So data-access is achieved entirely through the BLL rather than the underlying DAL.

I am taking (exploring) a different approach by extending the DataSet and DataTables, and adding business logic in this layer (the partial classes). I am not the expert that Scott Mitchell is, so my reasoning may be shown to be flawed. Nonetheless, everything that is demonstrated in this tutorial is still relevant even if you decide to extend TableAdapters and build a separate BLL, or take some other approach.

Note: We can still add queries or stored procedures to TableAdapters, which become methods we can call (via helper-methods), without having to explicitly extend a TableAdapter.

This tutorial is about using the DataSet Designer, working with a strongly-typed DataSet, BindingSources, WinForms and DataGridViews, etc.. How you take this forward - and what model or pattern(s) you follow - is, of course, up-to you (or your team).

My Naive Reasoning:

I suspect that extending TableAdapters is more suited to ASP.NET and the state-less browser environment, than to the desktop environment of WinForms (or WPF). A web-page posts data, or submits a request, that the server processes. The server performs this processing with the TableAdapters.

In a desktop environment the user (the consumer of our DAL) creates an instance of our strongly-typed DataSet and is able to fill one, or several, DataTables, and call methods on these. These methods will include (helper) methods to fill, or re-fill, the DataTables, and update the data-layer.

Extending the DataSet and DataTables

Firstly, we will modify the static methods that we created in the DAL (in the partial class) to make them methods of the DataSet instance, or appropriate DataTables. Once we've done this we will need to modify the corresponding method-calls in the forms.
using System;
namespace StaffDAL {
    public partial class StaffDS {
        public void PopulateStaff() {
            StaffDSTableAdapters.StaffTableAdapter adaptStaff = new StaffDSTableAdapters.StaffTableAdapter();
            StaffDSTableAdapters.DepartmentTableAdapter adaptDepts = new StaffDSTableAdapters.DepartmentTableAdapter();
            StaffDSTableAdapters.HolidayTableAdapter adaptHols = new StaffDSTableAdapters.HolidayTableAdapter();
            StaffDSTableAdapters.ClubTableAdapter adaptClubs = new StaffDSTableAdapters.ClubTableAdapter();
            StaffDSTableAdapters.StaffClubTableAdapter adaptStaffClubs = new StaffDSTableAdapters.StaffClubTableAdapter();

This is quite straight-forward. Instead of passing-in an instance of the DataSet we use this to refer to the current instance.
        public string UpdateEntireDSet() {
            StaffDAL.StaffDSTableAdapters.TableAdapterManager manager =
                new StaffDAL.StaffDSTableAdapters.TableAdapterManager();
            try {
                manager.StaffTableAdapter = new StaffDAL.StaffDSTableAdapters.StaffTableAdapter();
                manager.DepartmentTableAdapter = new StaffDAL.StaffDSTableAdapters.DepartmentTableAdapter();
                manager.HolidayTableAdapter = new StaffDAL.StaffDSTableAdapters.HolidayTableAdapter();
                manager.ClubTableAdapter = new StaffDAL.StaffDSTableAdapters.ClubTableAdapter();
                manager.StaffClubTableAdapter = new StaffDAL.StaffDSTableAdapters.StaffClubTableAdapter();

                return "Updated.";
            } catch (Exception ex) {
                System.Console.Error.WriteLine("Update failed: \n" + ex.Message);
                return "Error: Update failed.";

To keep the example simple I am just returning a string value if this mass-update fails. (It could be a boolean.) In reality there should probably be a formal Exception Handling model in place. It is also unlikely that a real system would have a method that attempts to update the entire DataSet in this optimistic way.
        public int InsertDept(string Dept) {
            StaffDSTableAdapters.DepartmentTableAdapter adaptDepts = new StaffDSTableAdapters.DepartmentTableAdapter();
            int newNo = Convert.ToInt32(adaptDepts.InsertDepartment(Dept));
            return newNo;

Notice that the Department-DataTable is accessible as this.Department within this DataSet class.

I considered making this a method of a Department-DataTable but this isn't appropriate. The user may not have this DataTable available and it isn't necessary to perform the insert.

On reflection, re-filling the Department-DataTable shouldn't occur here - the method should just insert the new department. We can provide a separate helper method to re-fill the DataTable, if the user wants to do this. I do this for the StaffDataTable:
        public partial class StaffDataTable {


This partial class MUST BE within the DataSet's partial class - a nested class.

You can create this partial class by double-clicking the table-name in the DataSet Designer.
        public partial class StaffDataTable {

            public void FillIt() {
                StaffDAL.StaffDSTableAdapters.StaffTableAdapter adaptStaff = new

The user can fill their Staff-DataTable using _StaffDS.Staff.FillIt() without any knowledge of table-adapters.

I use the name FillIt() and we need to be a little creative about naming our new members. Each DataTable (DataSet and TableAdapter, etc.) already provides quite a few methods and we don't want to conflict. In reality this shouldn't happen because it would require us to override the method. However, if the method-signature differs, we will be adding an over-loaded method, which could be confusing.

Typing 'this.' within a class will produce a list of existing methods, or we can explore them from the Class View window.

Type 'this.' and examine the list anyway, as you might be creating a method when one is already provided. For example, there already was a method (of DepartmentTableAdapter) to insert a new Department.


Double-click the Holiday table-name in the DataSet Designer to create its partial-class which, again, must be within the DataSet-class:
        public partial class HolidayDataTable {
            public void UpdateIt() {
                StaffDAL.StaffDSTableAdapters.HolidayTableAdapter adaptHols =
                    new StaffDAL.StaffDSTableAdapters.HolidayTableAdapter();

Again, the user doesn't need to instantiate a TableAdapter, they can just call UpdateIt() on their Holiday-DataTable.

Build, close and save our DAL, and re-open our StaffApp1 application. We need to fix it to use our revised DAL methods.

Updating the Form Code

frmNext2 I added a save button to this form, which only saves the holiday details:
        private void btnSave_Click(object sender, EventArgs e) {
            try {
                if (_staffDS.HasChanges()) {
                } else {
                    MessageBox.Show("Nothing has changed.");
            } catch (Exception ex) {
                MessageBox.Show("Update failed: \n" + ex.Message);

Here is the complete frmNext2 code if you need it for reference:


frmManyToMany This form is completely unchanged, but I'll post the code here for reference.


        private void frmStaff_Load(object sender, EventArgs e) {

I've added a helper-method:
        private DataGridViewTextBoxColumn SimpleColumn(string name) {
            DataGridViewTextBoxColumn col = new DataGridViewTextBoxColumn();
            col.DataPropertyName = name;
            col.HeaderText = name;
            return col;

so that adding a simple text-column to the DataGridView is like this:
        DataGridViewTextBoxColumn colID = SimpleColumn(_staffDT.StaffIDColumn.ColumnName);

This just makes the code a little easier to read. (The full form-code is shown at the bottom.)

Constructing a DGV:


        DataGridViewTextBoxColumn colGrade = SimpleColumn(_staffDT.GradeColumn.ColumnName);
        colGrade.DefaultCellStyle.Alignment = DataGridViewContentAlignment.MiddleCenter;

I built the helper-method in such a way that we can still add additional formatting to the column.

The following button-events were changed to use the non-static methods in the DAL:


You should be able to run the application after these changes. (Again, the full code occurs at the bottom.)

Close the application and return to the DAL.

Row and Column Validation

Data validation should, first and foremost, occur in the database itself, using indexes, key-fields, NOT NULL, constraints, and other database-paraphernalia. The integrity of the database and its data is then assured. Where, and how, other validation occurs, and at what level (in which layer) is then less of a concern - although still important.

From the DataSet Designer, right-click a field and choose Properties.


You can see that the properties here reflect those in the database. Personally, I don't want to change these properties here; if they need to be changed then it is very likely that they should be changed in the database. (It is also possible to add additional Constraints to DataTables but, again, this should typically be done in the database itself.)

A WinForm has validation features, which DataGridViews also participate in. BindingSources and DGVs raise events that we can tap-into. Decisions, decisions!?

Importantly, DataTables have events that we can use at the row and column level: ColumnChanging and (DataTable)RowChanging. Using these we can keep significant validation in our DAL.

  • If validation fails in these events we can use Row.SetColumnError()
  • WinForms have the Error Provider Class to produce error indicators in the presentation-layer
  • Because we are using BindingSources we can associate an ErrorProvider with a BindingSource so that ColumnErrors (in the DAL) can be automatically displayed on the form.

It is possible to feed ColumnErrors into the presentation-layer without a BindingSource, although it requires a bit more work. I don't explore this here. I feel certain that WPF also has a mechanism to register these ColumnErrors, perhaps something akin to ErrorProvider.

This is a good approach in my opinion (but see the section below on the DataError Event). The validation occurs centrally in the DAL and provides appropriate error messages. The presentation-layer can discover these errors (with or without an ErrorProvider) and take appropriate action - appropriate to the presentation-layer. We can check for ColumnErrors in methods of our DAL, thus providing the validation-layer that we need. (I don't think that handling these ColumnErrors is, alone, sufficient to be considered a full Business Logic Layer; this requires more methods.)

Add the following method into the DAL:
    public partial class StaffDataTable {

        public override void EndInit() {
            this.ColumnChanging += ColValidation;
            this.StaffRowChanging += RowValidation;

When the DataTable has been initiated we register event-handlers on the ColumnChanging and StaffRowChanging events; notice the slight difference in the names of these events.

In the same partial class:
        private void ColValidation(object sender, System.Data.DataColumnChangeEventArgs e) {
            if (e.Column.ColumnName == GradeColumn.ColumnName) {
                int newGrade = Convert.ToInt32(e.ProposedValue);
                // ..we should allow for null or text/empty as well
                if (newGrade < 1 || newGrade > 12) {
                    e.Row.SetColumnError(GradeColumn.ColumnName, "Must be between 1 and 12.");
                } else {
                    e.Row.SetColumnError(GradeColumn.ColumnName, "");

This should make some sense: we want to restrict the Grade to a value between 1 and 12. If the check fails we provide some text in SetColumnError(), otherwise we clear this text.

Note that this event fires for all columns, so we need to confirm which column triggered the event, and provide the error-message for this particular column.

e.ProposedValue is an object, which makes sense as it could be any kind of value. Before casting to an integer we should include checks for a null value (shown in the next code) or a string.
        private void RowValidation(object sender, StaffRowChangeEvent e) {
            if (e.Row.IsStartDateNull() || e.Row.IsEndDateNull()) {
                e.Row.SetColumnError(StartDateColumn.ColumnName, "");
            DateTime start = Convert.ToDateTime(e.Row.StartDate);
            DateTime end = Convert.ToDateTime(e.Row.EndDate);
            if (start > end) {
                e.Row.SetColumnError(StartDateColumn.ColumnName, "StartDate cannot be after EndDate.");
            } else {
                e.Row.SetColumnError(StartDateColumn.ColumnName, "");

This code runs when the row is changed, rather than an individual column. I am ignoring null-values. Without this check an error would occur immediately on loading the form because of the attempt to convert to DateTime. (We should again, of course, handle other non-date values.)

Note that we are using both events, for the row and columns. This shouldn't be a problem but test it thoroughly to make sure that there is no conflict between them, bearing in mind that column-validation will occur first.

The DAL is complete, here's the full code:


Update the Staff Form
namespace StaffApp1 {
    public partial class frmStaff : Form {
        private StaffDS _staffDS = new StaffDS();
        private BindingSource _bsStaff = new BindingSource();
        private BindingSource _bsDept = new BindingSource();
        private ErrorProvider _errProv = new ErrorProvider();   // add this

Add this at the end of the form's Load event:
        _errProv.DataSource = _bsStaff;  // do not set the DataMember property

It is important that you don't set the DataMember, the ErrorProvider just hooks into the Staff-BindingSource.

Run the application and try to enter a Grade outside 1-12 or a StartDate after an EndDate. You should see the ColumnErrors propagating back to the form.

Setting ColumnErrors alone are not sufficient to prevent invalid data being submitted to the data layer. We need to check the DataTable.HasErrors Property (the DataSet also has this property). Here is sample code from the MSDN link:


Typically an Exception (perhaps Application or Argument Exception) might be thrown that the presentation layer needs to handle. (This is demonstrated in the tutorials I linked to earlier.)

There are ways to prevent the user leaving a column, or row, that is in an invalid state. However, this is a decision, and responsibility, of the presentation-layer. The role of the DAL is, in my opinion, to:

  • Provide the strongly-typed DataSet
  • Provide methods on the DataSet and DataTables, etc.
  • Use ColumnErrors and methods to notify the presentation-layer of invalid state
  • Reject invalid submissions to the data-layer, typically by raising an Exception.

DataError Event

If you type some text for, say, the Salary, you should see a significant error. You may have already encountered such errors.

DataGridView.DataError Event :MSDN

MSDN said:

Occurs when an external data-parsing or validation operation throws an exception, or when an attempt to commit data to a data source fails.

BindingSource.DataError Event :MSDN

MSDN said:

Occurs when a currency-related exception is silently handled by the BindingSource.

The BindingSource version of this event is just for debugging purposes. You can use this code if you want to test it:
    _bsStaff.DataError +=_bsStaff_DataError;

    private void _bsStaff_DataError(object sender, BindingManagerDataErrorEventArgs e) {
        MessageBox.Show("Saw it here first?");

although if the form has a DGV then I suspect that it might mask this event. Rather than a MessageBox you could send details to the default Console.Error output stream.

The DataGridView version is more significant and produces the error message. Notice that it produces an error message not an Exception.
    private void dgvStaff_DataError(object sender, DataGridViewDataErrorEventArgs e) {
        if (e.Exception != null && e.Context == DataGridViewDataErrorContexts.Commit) {
            MessageBox.Show("Commit error.");
        } else if (e.Exception != null) {
            MessageBox.Show("Other Data Error!");

We can also examine e.RowIndex and e.ColumnIndex.

The presentation-layer can resolve this error using the CellValidating Event of the DGV, which occurs first.

Although we can use, and monitor, ColumnErrors in our DAL, a DGV's DataAccess event occurs before our column-validation. So, unless the presentation-layer uses cell-validation on the DGV, this DataError event might still arise.

Someone suggested, for example, setting AllowDBNull = False on a DataTable-Column to avoid the DataError and allow our column-validation to occur. I can see the sense in this, in that it brings the validation back into the Data Access Layer. However, I am not comfortable with this. A DataGridView is a WinForm Control so, unfortunately, it may have to be left to the presentation-layer to handle this DataError event, or to use cell-validation on the DGV.

frmStaff full code:


This post has been edited by andrewsw: 18 April 2014 - 04:36 AM

Is This A Good Question/Topic? 0
  • +

Page 1 of 1