Page 1 of 1

Database To Typed-DataSet, With BindingSource II

#1 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3252
  • View blog
  • Posts: 10,903
  • Joined: 12-December 12

Posted 10 April 2014 - 10:23 AM

This continues from the first part (found here) to explore:

  • Updating the DAL (Data Access Layer)
  • TableAdapter Stored Procedures (or Queries)
  • BindingSource (and DGV) Filtering
  • Extracting DataMember from List (Details-Master)
  • Many-to-Many Binding

Part 3 is here.

Resources

I've struggled to find complete, or up-to-date, material related to using strongly-typed DataSets, particularly with BindingSources. Some that I've found useful (even if a little old) are:

A Detailed Data Binding Tutorial
Tackle Complex Data Binding with Windows Forms 2.0
Building a DAL using Strongly Typed TableAdapters and DataTables in VS 2005 and ASP.NET 2.0
(This is for ASP and VB but still contains a lot of very useful information.)
These tutorials are still for ASP but are in C# and more up-to-date.

How to: Create TableAdapter Queries :MSDN

The book Pro VB 2010 and the .NET 4 Platform by Troelsen and Agarwal is excellent generally. I assume the C# version is identical (I don't need two copies of the same book!). It has good chapters on ADO.NET, leading on to strongly-typed DataSets. (It doesn't go as far as the material in this tutorial.)

The Database

Modify the database to add two additional tables, Club and StaffClub. These will provide a many-to-many relationship. (Again, you can use a database of your own, as long as it contains a many-to-many relationship.)

Club
ClubID int (primary key)
Club varchar(50)

StaffClub
StaffID int
ClubID int
(both are used as a composite primary key)

Attached Image

Add a few clubs and sign-up some staff members to some of these clubs.

Attached Image

If you are comfortable with creating stored procedures you might create one so that you could explore its use with the DataSet Designer later.

Update the DAL

I chose to continue with the DAL and our Application from part 1, rather than copying anything. (You can copy if you prefer.) I wanted to continue to explore how well the DAL coped with changes. I haven't encountered any problems at all but this is still a very small application. (As I mentioned in part 1, some people have expressed concerns about the behaviour of the DataSet Designer - research this if it concerns you.)

Open our DAL (StaffDAL) and the DataSet Designer Surface. Drag our two new tables from the Database Explorer window onto the surface.

It is possible to right-click the designer to add these. We could also have originally built our DataSet by adding a DataSet control from the Toolbox, rather than dragging and dropping. I chose the drag-n-drop approach.

Attached Image

We will add a stored procedure to our DepartmentTableAdapter that will allow us to insert a new Department using the text in a TextBox. The initial steps are the same to create a query, or to use an existing stored procedure.

Right-click DepartmentTableAdapter in the designer and choose Add, Query. (If you right-click an empty area to do this then a new QueriesTableAdapter will be created - to manage queries that are not members of a specific table-adapter.)

Spoiler

Choose the option to Create new stored procedure. Press Next and choose the INSERT option, to insert a new row to a table.

Attached Image

The default SQL-statement inserts a new row using a parameter, and returns this new row. I just want to return the new auto-number, to show that it worked. So modify the statement to:
INSERT INTO [dbo].[Department] ([Department]) VALUES (@Department);
SELECT @@IDENTITY


You can click Query Builder if you like to help build, and test, a query.

Continue through the Wizard using default options. I used the names:

InsertDepartmentSP - name of SP
InsertDepartment - name of function/method

Display the Class View window and drill-down to find our new method. If you right-click and choose 'Go To Definition' you can see the code that has been generated.

Spoiler

Notice in particular that the return-type of the value is object rather than the integer that we would prefer. Although this code can be edited I WOULD NOT. Your changes will be over-written anyway if parts of the DataSet are rebuilt. We have a separate partial class that we can use to add to the model.

Notice also that it uses ExecuteNonQuery, and returns the number of affected rows rather than the auto-number that we want. Close this tab and go back to the design-surface. Right click our new method and choose Properties. In here we can change the ExecuteMode from NonQuery to Scalar - this will give us our auto-number.

Spoiler

On reflection, I think I prefer to build stored procedures in the database and drag them onto the Design Surface, rather than letting the Wizard build them.

This stored procedure will be used on the main form, discussed later. Here's a quick look at the relevant code in the form:

Spoiler

Double-click an empty area of the design-surface to open StaffDS.cs. This is the partial class where we created our PopulateStaff() method in part 1. We need to modify this to also populate our new DataTables.
using System;
namespace StaffDAL {
    
    public partial class StaffDS {
        public static void PopulateStaff(StaffDS staffDS) {
            StaffDSTableAdapters.StaffTableAdapter adaptStaff = new StaffDSTableAdapters.StaffTableAdapter();
            adaptStaff.Fill(staffDS.Staff);
            StaffDSTableAdapters.DepartmentTableAdapter adaptDepts = new StaffDSTableAdapters.DepartmentTableAdapter();
            adaptDepts.Fill(staffDS.Department);
            StaffDSTableAdapters.HolidayTableAdapter adaptHols = new StaffDSTableAdapters.HolidayTableAdapter();
            adaptHols.Fill(staffDS.Holiday);
            // part 2
            StaffDSTableAdapters.ClubTableAdapter adaptClubs = new StaffDSTableAdapters.ClubTableAdapter();
            adaptClubs.Fill(staffDS.Club);
            StaffDSTableAdapters.StaffClubTableAdapter adaptStaffClubs = new StaffDSTableAdapters.StaffClubTableAdapter();
            adaptStaffClubs.Fill(staffDS.StaffClub);
        }


Although our InsertDepartment() method is available to us from our form-class we really want to continue to develop our Data Access Layer using this partial class. In particular, an immediate goal should be to push all uses of TableAdapters into this layer.

Homework: The completed tutorial still uses TableAdapter in several places in the form-code. Create methods in the partial-class so that none of our form-code directly uses our TableAdapters.

Rather than using InsertDepartment() directly (requiring a TableAdapter) we will create a helper (static) method named InsertDept() in this partial class.
        // part 2
        public static int InsertDept(StaffDS staffDS, string dept) {
            StaffDSTableAdapters.DepartmentTableAdapter adaptDepts = new StaffDSTableAdapters.DepartmentTableAdapter();
            int newNo = Convert.ToInt32(adaptDepts.InsertDepartment(dept));
            adaptDepts.Fill(staffDS.Department);
            return newNo;
        }


I continue to use static methods of the StaffDS class, passing in our DataSet instance. It is a class so we don't have to use static methods. We can add properties, instance methods, etc., as we can for any class, but I will keep the example simple. To see what is available to a DataSet-instance you could type:
    public void Test() {
        this.       // lots of stuff appears in the intelli-sense list
    }


Ok, we've finished with our DAL. Build, close and save.

Our Main Form

Return to our main application (StaffApp1) and add a few more controls to the main form (frmStaff) as indicated by the stars in this image:

Attached Image

txtFirstNameFilter, txtNewDepartment, btnAddDept, txtDepartment, btnMany (plus labels).

Complete code of frmStaff, for reference:

Spoiler

(additions are preceded by // part 2)
        private void frmStaff_Load(object sender, EventArgs e) {
            StaffDS.PopulateStaff(_staffDS);


This code is unchanged but will now also populate the club-details.

I have modified the SaveAll code so that it does, indeed, save the whole DataSet rather than just the Staff table. This uses the TableAdapterManager's UpdateAll method:
    private void btnSaveAll_Click(object sender, EventArgs e) {
        // part 2 - update all DTs
        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();

            _bsStaff.EndEdit();
            if (_staffDS.HasChanges()) {
                manager.UpdateAll(_staffDS);
            } else {
                MessageBox.Show("Nothing has changed.");
            }

        } catch (Exception ex) {
            MessageBox.Show("Update failed: \n" + ex.Message);
        }
    }


BindingSource (and DGV) Filtering

This is straight-forward:
        // part 2
        private void txtFirstNameFilter_TextChanged(object sender, EventArgs e) {
            _bsStaff.Filter = string.Format("FirstName LIKE '{0}*'", txtFirstNameFilter.Text);
        }


The Staff-BindingSource is filtered according to what we type in the txtFirstName textbox. This in-turn filters the DGV. Deleting the text in this box will bring back all of the records.

Investigate the BindingSource.Sort Property as well. You can even click on the DGV column-headers - except for the departments, that would require a bit more effort.

Extracting DataMember from List (Details-Master)

I want the textbox txtDepartment to display the Department-name for the current staff-member in the DGV. (The textbox has Enabled = False.) This is not as simple as it was for the FullName which just refers to an existing DataMember (property of the Staff DataTable). The value we need is tucked-away in the Department-List.
            // part 2
            Binding bDept = new Binding("Text", _bsDept, "Department");
            bDept.NullValue = "Not selected";
            txtDepartment.DataBindings.Add(bDept);

            _bsStaff.CurrentChanged += (s, f) => {
                // I use the letter 'f' because 'e' is already taken.
                DataRowView rowView = _bsStaff.Current as DataRowView;
                StaffDS.StaffRow staffRow = rowView.Row as StaffDS.StaffRow;
                if (staffRow.DepartmentRow == null) {
                    // for a new record
                    txtDepartment.Text = "Not selected";
                } else {
                    txtDepartment.Text = staffRow.DepartmentRow.Department;
                }
            };


Rather than just txtDepartment.AddBinding() we create a Binding instance so that we can supply a NullValue: a value to display if there is no Department.

We can get the Department from the Department-BindingSource. If you were to run this part of the code it would display a Department, without error. But it will only display the first Department-value it encounters and this will never change. It is not synchronized.

We attach a handler to the Staff-BindingSource's CurrentChanged event; I'm using a lambda. The Current property of the Staff-BindingSource is a DataRowView, so we first have to cast this to a StaffRow. From StaffRow we can drill-down to find the Department. I've added a null check in case there is no DepartmentRow.

The tutorial I mentioned at the beginning (here) demonstrates how this can be achieved using a Filter against the Department-BindingSource, rather than drilling-down. I chose not to do this because I anticipated that this filtering would interfere with our Department-combobox, possibly triggering recursive calls.

Using The Stored Procedure
        // part 2
        private void btnAddDept_Click(object sender, EventArgs e) {
            if (String.IsNullOrWhiteSpace(txtNewDepartment.Text))
                return;
            int newNumber = StaffDS.InsertDept(_staffDS, txtNewDepartment.Text);
            MessageBox.Show(String.Format("New auto-number: {0}", newNumber.ToString()));
        }


Having checked that there is something in the txtNewDepartment textbox, this calls our static InsertDept() method to insert this Department into the table.

The static method does all the database grunt-work for us. It uses a TableAdapter to execute the stored procedure, converts the object return-value to an integer, and re-fills the Department-DataTable so that the new department will appear in the comboboxes.

There should be a unique-constraint on the Department field (in the database) to prevent duplicates. In which case, rather than handling an Exception, the helper method could first check if the new department already exists, perhaps using another method of the DAL. Or the stored procedure could do this.

Words about validation:
Spoiler

This tutorial (for ASP) is about creating a separate BLL (Business Logic Layer) between the DAL and Presentation Layer.

Many-to-Many Binding

We want to display and synchronize information from tables which have a many-to-many relationship. This requires a little effort because, for any given form (or other presentation layer), it is our decision as to which table is considered the parent, and which the child. We could either click on a Club and display all the members of this club, or click on a staff-member and display all the clubs that this member belongs to. We will consider Staff as the parent and Club as the child. (The situation is also complicated by the need to include the join table.)

The button-code on the main form:
    private void btnMany_Click(object sender, EventArgs e) {
        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();

            _bsStaff.EndEdit();
            if (_staffDS.HasChanges()) {
                manager.UpdateAll(_staffDS);
            }
        } catch (Exception ex) {
            Console.Error.WriteLine(ex.Message);
        }
        frmManyToMany frmMany = new frmManyToMany(_staffDS);
        frmMany.Show();
    }


Homework: Move this use of AdapterManager into the DAL as a method.

Create a new (simple) form named frmManyToMany with just two DataGridViews:
dgvStaff, dgvClubs

Spoiler

Complete code of frmManyToMany, for reference:

Spoiler

    public partial class frmManyToMany : Form {
        StaffDAL.StaffDS _staffDS;
        BindingSource _bsStaff = new BindingSource();
        BindingSource _bsStaffClubs = new BindingSource();
        BindingSource _bsClubs = new BindingSource();


We need three BindingSources, one for each table involved in the many-to-many relationship.
        public frmManyToMany(StaffDAL.StaffDS staffDS) {
            InitializeComponent();
            _staffDS = staffDS;
        }


For this example we are only sharing the DataSet itself, not BindingSources. Although it is possible to do this, so that all the forms could remain synchronized, there are good reasons not to do this:

  • It will complicate our example.
  • Loading the entire DataSet (as we have done) and attempting to keep data in-sync across a number of forms, should be avoided. The synchronization could easily trip-up but, more importantly, the stability and performance of our application will suffer as the database grows.

Synchronizing two naturally-paired forms is a common requirement, and very useful. If you are considering synch-ing a third form then you should pause to consider other options. Typically there might just be a single-value that needs to be fed to the next form, which then loads a specific DataTable, filtered by this value. To prevent interference with the value you might open the next form modally, or hide or close unrelated forms.
        private void frmManyToMany_Load(object sender, EventArgs e) {
            dgvStaff.DataSource = _bsStaff;
            dgvClubs.DataSource = _bsClubs;

            _bsStaff.DataSource = _staffDS;
            _bsStaff.DataMember = _staffDS.Staff.TableName;

            _bsClubs.DataSource = _staffDS;
            _bsClubs.DataMember = _staffDS.Club.TableName;


These statements are straight-forward and should make sense by now.

            _bsStaffClubs.DataSource = _bsStaff;
            _bsStaffClubs.DataMember = "FK_StaffClub_Staff";


This is the crux of the matter. By setting the DataSource of the StaffClub's BindingSource to the BindingSource for the Staff (chaining BindingSources) we are defining Staff as the parent table.

Recall that the DataMember of a BindingSource is a List. "FK_StaffClub_Staff" is the name of a Relation that identifies this List. That is, the data in the StaffClub table (DataTable) for the Current staff-member. (You may need to check the name of this relation in the DAL.)

The two DGVs are not yet synchronized. Selecting a staff-member will trigger events in the StaffClub's BindingSource, but these won't automatically filter through to the list of clubs. We need to hook into these events:
            // hook up event handlers on the binding sources
            _bsStaffClubs.BindingComplete += (s, f) => {
                BindClubs();
            };
            _bsStaffClubs.ListChanged += (s, f) => {
                BindClubs();
            };
            BindClubs();
        }


So it boils down to BindClubs() to synchronize the DGVs:
        private void BindClubs() {
            string sFilter = string.Empty;
            // Loop through the related child rows in the join table
            // and add a filter criteria to the filter string
            foreach (DataRowView row in _bsStaffClubs.List) {
                if (sFilter != string.Empty)
                    sFilter += " OR ";
                sFilter += "ClubID = " + row["ClubID"];
            }
            // Set as the filter string on the clubs BS
            _bsClubs.Filter = (sFilter == "") ? "False" : sFilter;
            
        }


This isn't too bad. When we select a staff-member the StaffClub's List changes. We loop through this list collecting the ClubIDs (for this staff-member). Then we apply a Filter to the Clubs using all the ClubIDs that we have collected.

I added an additional check for an empty filter, using "False" so that it won't display any clubs. Without this check the BindingSource treats the empty string as meaning 'no filter' and all the clubs are listed, which is misleading/wrong.

You cannot create a new Club in the DGV, so investigate how you could remove this new row.

Andy.




My First Issue

I had an issue yesterday and I think it should be useful to discuss it, and how it was, or could be, resolved.

I wasn't able to amend a holiday record, with an error stating that a valid UPDATE statement was required. Initially I couldn't see why the Holiday table would behave any differently to the others, although perhaps it was related to the introduction of a new BindingSource for holidays on the second form.

In the DAL, right-clicking the holiday table-adapter in the DataSet Designer and choosing Properties, we can see the SQL statements and, yes, there was no UPDATE statement.

I considered writing the UPDATE statement (in the Properties window) but then I thought it would be best to copy and paste this statement for another table, to ensure that I typed it correctly. But then it occurred to me that I could rebuild all the statements by configuring the adapter (right-click, Configure in the Designer). This ran okay and rebuilt the SQL statements, but still not the UPDATE version.

An 'aha' moment! The UPDATE SQL-statement is only built for us when a table contains a primary key. As suspected, I had forgotten to specify HolidayID as the primary key. Doing so, and re-configuring the adapter, created the UPDATE statement and the problem was solved.

I mentioned previously that if I encountered problems I would be happy to rebuild the DataSet entirely from scratch - having first copied any code that I had added in the partial-class. I still consider this a viable option, but we have to keep a note of any individual changes made to the DAL. For example, we changed the stored procedure's ExecuteMode to Scalar and named the procedure and its method.

Attached image(s)

  • Attached Image

This post has been edited by andrewsw: 12 April 2014 - 07:50 AM


Is This A Good Question/Topic? 0
  • +

Replies To: Database To Typed-DataSet, With BindingSource II

#2 thava  Icon User is offline

  • D.I.C Lover
  • member icon

Reputation: 180
  • View blog
  • Posts: 1,606
  • Joined: 17-April 07

Posted 11 April 2014 - 01:53 AM

Good one, the last instruction is superb.
Just a Suggestion:
Keep it in mind the update statement generated by the adapter some time not work as you expected, when the back end data is updated by someone else in the system because, the following update query is generated by the Dataset Table adapter for the currency table in adventureworks Database, actually primary key is currency code
UPDATE Sales.Currency
SET         CurrencyCode = @CurrencyCode, Name = @Name, ModifiedDate = @ModifiedDate
WHERE (CurrencyCode = @Original_CurrencyCode) AND (Name = @Original_Name) AND (ModifiedDate = @Original_ModifiedDate)


might be the concurrency problem will solve here, but last win Strategy is gone
Writing our own logic will always a better than relying on some default tools
Was This Post Helpful? 0
  • +
  • -

#3 andrewsw  Icon User is online

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3252
  • View blog
  • Posts: 10,903
  • Joined: 12-December 12

Posted 11 April 2014 - 08:24 AM

View Postthava, on 11 April 2014 - 08:53 AM, said:

Writing our own logic will always a better than relying on some default tools

Good point, and I agree.

Generally I avoid trusting in wizards. Building a Data Access Layer is such a big task though that I want to work with the DataSet Designer. To do this successfully I believe we need to make as much use of the designer, and its wizards, as possible. Once we've got everything up and running then we can continue to test it thoroughly and, if necessary, modify it.

However, I also want to be in a position to re-create the entire layer if necessary. Creating back-ups is part of this process, but I also want to document all the steps, and changes, as I progress.

[Technically, I suppose it must be possible to extract all the code at some point, and forget about the designer. Unless the designer is proven to be fundamentally flawed then this should remain a last resort.]
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1