AoB: A Base Windows Form for displaying, editing and automatically updating Many-to-Many relationships – Part 3

• Download source - 507 Kb

Introduction

In this part I explain how to deal with concurrency issues.

Although this form is capable of some editing and data control it isn't very controlled and can easily allow concurrency violations where data is not properly linked or saved.

Firstly it is important to get values for the IDs that can be used, as these are used to link A and B data.

We have CurrentMaxAID and CurrentMaxBID and CurrentMaxABID. We need to find the maximum value that exists, not simply count them - some may have been deleted.

Int32 CurrentMaxAID = 0;
Int32 CurrentMaxBID = 0;
Int32 CurrentMaxABID = 0;
object ob;

public void LoadDatabase()
{

    ...

    //Get current maximum value of AID
    CurrentMaxAID = 0;
    ob = ds.Tables["A"].Compute("Max(AID)", "");
    if (ob.ToString() != "")
        CurrentMaxAID = Int32.Parse(ob.ToString());
    CurrentMaxAID++;

    //Get current maximum value of BID
    CurrentMaxBID = 0;
    ob = ds.Tables["B"].Compute("Max(BID)", "");
    if (ob.ToString() != "") CurrentMaxBID = Int32.Parse(ob.ToString());
    CurrentMaxBID++;

    //Get current maximum value of ABID  //could use BA
    CurrentMaxABID = 0;
    ob = ds.Tables["AB"].Compute("Max(ABID)", "");
    if (ob.ToString() != "") CurrentMaxABID = Int32.Parse(ob.ToString());
    CurrentMaxABID++;

    DatabaseLoaded = true;
}

Now if a user goes into the "add new row" of the A DataGridView we need to update the AID field. We can use the "User Added Row" event to add in the next CurrentMaxAID number.

private void dgvA_UserAddedRow(object sender, DataGridViewRowEventArgs e)
{
  dgvA.CurrentRow.Cells[0].Value = CurrentMaxAID.ToString();
  CurrentMaxAID++;
}

Similarly for the B grid.

Note that the number doesn't get actually added until the first character is typed into the AName field.

This, to me, seems a big improvement in design over the original DataGrid where I used a column changed event which obviously fired on each column change:

//set up some event handlers for when editing(adding) data 
//adds a unique AID number to new rows
DS.Tables["A"].ColumnChanged += new 
  DataColumnChangeEventHandler(this.ATableDataColumnChanged);

private void ATableDataColumnChanged(object sender, 
             System.Data.DataColumnChangeEventArgs e) 
{
    //This code adds a (hopefully) unique AID to the A table.
    if ((e.Row["AID"].ToString() == "") || 
               (int.Parse(e.Row["AID"].ToString()) == 0))
        e.Row["AID"] = CurrentMaxAID++;
}

There is now a design decision to be made. It might seem obvious to add similar code for adding B records in the AB DataGridView. Surely all we need to do is add the existing (new) AID, the next ABID and the next BID and the BName. However although this works for the AB table, the B table does not get updated. Code needs to be added to update the B table with the BID and BName.

An alternative view to take is that perhaps only existing B entries should be allowed to be added to the new A row. Therefore new B entries should be created first and then selected. This is the route I took with my initial design. I will stop the user adding entries directly to the AB side of the tab and only allow the user to select from existing BName entries. This will allow me to also demonstrate a few new techniques. (I have actually written the code for the first idea and it is described below.)

I am not saying my decision is the best, it may not be, I am saying it is the decision I made originally and since I am converting an existing application to v2.0, I think I'll keep it this way for now.

I will use a context menu on the A DataGridView to bring up a list of all the existing B rows. Then the user can select which B rows are to be added, either as a range, or individually, and then add them by right clicking again and selecting "Add these selected rows to A".

First I will turn off the ability to add new rows to the AB DataGridView using the designer (properties). AllowUserToAddRows is set to false. [Of course don't do this if you use the additional code described below.]

Now I need to add a context menu to the A DataGridView. Create a context menu, add an item "Add rows from B", add an item "Add these selected rows to A" and a "Cancel" option. Double click on these to create the event handler skeletons. On the A DataGridView, assign the context menu to the contextmenustrip property. I assign the same context menu to the AtoB DataGrid as well.

A little visual clue I added to my app to help me see what was going on is to change the backcolor of the caption for each DataGrid that is currently active. This is particularly useful if deleting, to see which gridview you are actually deleting from. When displaying the list of B rows to select, I change the caption to yet another color so I can see what is going on there. This is totally non-standard, but I like it, so there it is. The only problem is that the DataGridView doesn't now have a caption - why? What was wrong with it? Surely it's not that hard to implement backward compatibility. Anyway I use some labels to achieve the same effect. There is some code that makes use of the mouse down event to organise the colours.

private void dgMouseDown(object sender, 
           System.Windows.Forms.MouseEventArgs e)
{
    DataGridView myGrid = (DataGridView)sender;

    //If caption not coral, which indicates that the grid 
    //is displaying a list of all A or B for selectionand assignment
    //set all caption header to LightSteelBlue, then set active 
    //one to Goldenrod - because need to know from 
    //what grid what you are deleting!!!
    lbA.BackColor = Color.LightSteelBlue;
    lbB.BackColor = Color.LightSteelBlue;
    if (lbAtoB.BackColor != Color.Coral)
        lbAtoB.BackColor = Color.LightSteelBlue;
    if (lbBtoA.BackColor != Color.Coral)
        lbBtoA.BackColor = Color.LightSteelBlue;
    //which grid am I on and highlight it
    if (myGrid.Name.Trim() == "dgvA")
        lbA.BackColor = Color.Goldenrod;
    if (myGrid.Name.Trim() == "dgvB")
        lbB.BackColor = Color.Goldenrod;
    if (myGrid.Name.Trim() == "dgvAtoB")
        if (lbAtoB.BackColor != Color.Coral)
            lbAtoB.BackColor = Color.Goldenrod;
    if (myGrid.Name.Trim() == "dgvBtoA")
        if (lbBtoA.BackColor != Color.Coral)
            lbBtoA.BackColor = Color.Goldenrod;
}

If you need any more information such as where on the DataGridView you have clicked on, use the hit test info structure in the dgMouseDown class:

System.Windows.Forms.DataGridView.HitTestInfo hti;
hti = myGrid.HitTest(e.X, e.Y);
MessageBox.Show(hti.Type.ToString());

In the context menu event classes, I add some code to hide the options that don't make sense in the particular context and organise my caption colours. I assign the B table from the dataset to the AtoB DataGridView. This seems to work well, although I have a little uncertainty in the back of my mind as to what is going on with the unused columns such as ABID etc. I'm assuming that the DataGridView and column classes know what to do - but if there are any strange bugs this might be a place to look. An alternative would be to properly assign columns to the grid and then reassign the old ones back later, or use a DataGridView that lies on top of, or underneath, the AtoB one in the designer and make it active and visible as needed. I have used this technique before and it works well, although sorting out any problems in the designer view is a little more awkward - use the drop down list to select the desired grid to be worked on in the properties fly-out window.

[Talking about that, is there a bug in the designer? If I click on the DataGridView in the designer and then click on the properties flyaway tab, I don't always get the list of properties or events. I have to click on the DataGridView again? (Visual C# Express beta 2 version.)]

If I have right clicked on a DataGridView and selected the option to "Select from B", then the caption above the right hand grid will be coral, and a list of all Bs will be displayed. I can now select which Bs I want to assign to A by clicking on the row header. I can use the shift key to make a range selection, and/or the control key to select individual multiple rows. A problem with selecting and scrolling in the old DataGrid appears to have been fixed. (There is a fix if you need it!)

Once rows are selected use the context menu and select the "Add these selected rows to A" menu option (or Cancel!).

In the event for this, we cycle through the rows for the AtoB DataGridView and see which are selected. I use the AID and BID from the two grids to see if an existing AB record exists. I don't want duplicates. To do this I used a DataView and filter and count all the records. If the count is 0, I add the new one to the underlying table. The dataset seems to take care of the rest - if the right flags are set up, such as ds.EnforceConstraints = true and the nr.SetParentRow(nr); - I think? It's so long ago that I got it all working...

private void addthesetoAToolStripMenuItem_Click(object sender, EventArgs e)
{
    DataView dv;
    DataRow nr;
    
    //process selected rows
    foreach (DataGridViewRow row in dgvAtoB.Rows)
    {
        if (row.Selected)
        {
            //need to see if the AB row already exists. Dataview seems easiest?
            dv = ds.Tables["AB"].DefaultView;
            dv.RowFilter = "AID='" + 
               dgvA.CurrentRow.Cells["AID"].Value.ToString() 
               + "' and BID='" + 
               row.Cells["BID"].Value.ToString() + "'";
            if (dv.Count == 0)
            //no duplicates (I hope) 
            {
                //Update the AB table now
                nr = ds.Tables["AB"].NewRow();
                nr["ABID"] = CurrentMaxABID++;
                nr["AID"] = dgvA.CurrentRow.Cells["AID"].Value;
                nr["BID"] = row.Cells["BID"].Value;
                nr["BName"] = row.Cells["BName"].Value;
                nr.SetParentRow(nr);
                ds.Tables["AB"].Rows.Add(nr);
            }
        }
    }

There might be a better way now to see if the AB record exists, please let me know. Certainly in the original version I seemed to have had to use the binding manager base to get at the table supplying the DataGrid. I can't remember why this was so, but there must have been some hitch somewhere because who would use the binding manager base unless they had to!!! Probably because of the relation being assigned to the grid I suspect.

I do the same for the other side of the tab as well.

Now that I've rewritten the code I can't see any problem now with amending it to allow the addition of a participant directly. It will use the same essential routine as above. The only difference is getting the next maximum BID, which is easy - CurrMaxBID.

The only problem is that I should only update the A table when I have finished editing in the row. So I need to know when I have left the add new data row.

There are a lot of events going on. The one that seems most useful is the DataGridView.UserAddedRow event. The documentation says this occurs when a user has finished adding a row to a DataGridView. It then goes to show how this can be used to update a running total. I don't know how it can do this as it is fired on the first keystroke for entry into a cell in a blank row. It doesn't fire when all the text entry has been finished.

The order of [some] events is a user clicks into the add new row (asterisked). There are RowLeave, RowValidating, RowValidated and RowEnter events. The icon changes to a right triangle icon. The user presses a key on the keyboard. There is a RowAdded event followed by the UserRowAdded event. The icon changes to an edit pencil. The user can now keep on typing. When the focus leaves the row then RowLeave, RowValidating, RowvValidated, and RowEnter events fire. The icon changes back to a right triangle icon and the Add new row changes to an asterisk (unless still on it).

I'm sure that there are myriads of other events firing as well, e.g., end cell edit? But where is the documentation for all this??? Microsoft never seems to write example code that we actually want to use; it always seems so esoteric!

In order to make some progress I decided to set a flag when I know I have added a new row, then use the row validated event to update the database. Again I look forward to hearing the definitive answer on this :)

The code for adding a row to the AtoB grid is now:

private void dgvAtoB_UserAddedRow(object sender, DataGridViewRowEventArgs e)
{
    RowAddedToAtoB = true;
    //use this in the validated event to update database, 
    //needs to go here otherwise things out of sync 
    //when cell edit routine invoked???.

    dgvAtoB.CurrentRow.Cells["ABID"].Value = 
            CurrentMaxABID.ToString();
    CurrentMaxABID++;
    dgvAtoB.CurrentRow.Cells["BID"].Value = 
            CurrentMaxBID.ToString();
    CurrentMaxBID++;
    dgvAtoB.CurrentRow.Cells["AID"].Value = 
            dgvA.CurrentRow.Cells["AID"].Value;
}

private void dgvAtoB_RowValidated(object sender, 
                    DataGridViewCellEventArgs e)
{
    DataRow nr;

    if (RowAddedToAtoB)
    {
        nr = ds.Tables["B"].NewRow();
        nr["BID"] = dgvAtoB.CurrentRow.Cells["BID"].Value;
        nr["BName"] = dgvAtoB.CurrentRow.Cells["BName"].Value.ToString();
        ds.Tables["B"].Rows.Add(nr);
        //now need to make sure that the BA relation gets updated
        nr = null;
        nr = ds.Tables["BA"].NewRow();
        nr["ABID"] = dgvAtoB.CurrentRow.Cells["ABID"].Value;
        nr["BID"] = dgvAtoB.CurrentRow.Cells["BID"].Value;
        nr["AID"] = dgvAtoB.CurrentRow.Cells["AID"].Value;
        nr["AName"] = dgvA.CurrentRow.Cells["AName"].Value.ToString();
        ds.Tables["BA"].Rows.Add(nr);

        RowAddedToAtoB = false;
    }
}

(Note that adding a user row calls other events such as the CellValueChanged routine, there seems to be some timing issues related to these calls so this RowAddedToAtoB flag has to be set first, not at the end!!! There is some weird asynchronous eventing going on here - I need to investigate!)

Note that this method does not check for an existing B record (or an A record if on the other side). Therefore it is possible to get say two or more "Phil"s being added. Depending on your needs this could be a good thing or a bad thing. [So I still think the second design decision is best.]

A major issue that has to be faced is now becoming clear. If I add some B entries to an A entry this will update the AtoB relation, but it will not update the BtoA relation. This is a major pain. This means that unless steps are taken, the two sides of the relationship will not match up in the views. Even worse is that since I only update the real AB datatable from the AtoB side, any changes in the dataset on the BtoA side will not update the DataTable. I can't simply just add the extra update code because both sides may be hopelessly out of sync.

What I have to do is to make sure that if I make a change to the AtoB side I feed it through the BtoA side as well. This includes edits, adding records and deletions. Similarly for working on the other side of the relationship. This is a major pain and involves adding almost duplicate code and taking care of the additional edit and delete events.

It would have been so much easier if there was an automatic option in the dataset for updating the two relations together.

The code is similar to the code above - see the source for full details.

As well as this, we need to check that a change to either the AName or BName updates the other entries in the AB or BA lists, so that everything is synchronised. To do this I use the CellValueChanged event.

This takes care of the change to an AName (on the first tab).

private void dgvA_CellValueChanged(object sender, 
                     DataGridViewCellEventArgs e)
{
    //need to propogate any changes through to table BA
    //find the record(s) first, then change it(them).
    
    string s;
    s = "AID = '" + dgvA.CurrentRow.Cells["AID"].Value.ToString() + "'";
    DataRow[] dr = ds.Tables["BA"].Select(s, null, 
                    DataViewRowState.CurrentRows);
    foreach (DataRow r in dr)
    {
        if (e.ColumnIndex == 1)
            r["AName"] = dgvA.CurrentRow.Cells["AName"].Value.ToString();
    }
}

This next event takes care of the change of a BName in the right hand grid (on the first tab). However this event is also fired if a new row is added. I set a flag in the UserAddedRow routines, so if this is set I return from the routine.

private void dgvAtoB_CellValueChanged(object sender, DataGridViewCellEventArgs e)
{
    //need to propogate any changes through to table B
    //find the record(s) first, then change it(them).

    string s;
    DataRow[] dr;

    if (RowAddedToAtoB) return;
    //I'm adding a row so don't do anything

    s = "BID = '" + 
        dgvAtoB.CurrentRow.Cells["BID"].Value.ToString() 
        + "'";
    dr = ds.Tables["B"].Select(s, null, 
         DataViewRowState.CurrentRows);
    foreach (DataRow r in dr)
    {
        if (e.ColumnIndex == 3)
            r["BName"] = 
              dgvAtoB.CurrentRow.Cells["BName"].Value.ToString();
    }
    //Now propogate through to "all" in AB table
    dr = ds.Tables["AB"].Select(s, null, 
          DataViewRowState.CurrentRows);
    foreach (DataRow r in dr)
    {
        if (e.ColumnIndex == 3)
            r["BName"] = 
              dgvAtoB.CurrentRow.Cells["BName"].Value.ToString();
    }
}

After sorting these bits out, I had a bug in the update routine. Eventually I tracked it down to the parameters I has used and you will need to change this if you have already used the code:

In the database Update routine, find the line here:

daAB.UpdateCommand.Parameters.Add("@BID", OleDbType.Integer, 10, "BID");

Then in these line you will note that ABID1 reads daA.UpdateCommand...

It should of course read ABID1.dAB.UpdateCommand... (This was really hard to track down...)

So this bit of code should read:

//OleDbParameter ABID1 = 
  daAB.UpdateCommand.Parameters.Add("@ABID", 
  OleDbType.Integer, 10, "ABID");
//ABID1.SourceVersion = DataRowVersion.Original;
OleDbParameter ABID2 = 
  daAB.UpdateCommand.Parameters.Add("@AID", 
  OleDbType.Integer, 10, "AID");
ABID2.SourceVersion = DataRowVersion.Original;
OleDbParameter ABID3 = 
  daAB.UpdateCommand.Parameters.Add("@BID", 
  OleDbType.Integer, 10, "BID");
ABID3.SourceVersion = DataRowVersion.Original;

The final issue concerns the deletion of a row. The row that is to be deleted is highlighted and then the delete key is pressed. Before the row can be deleted the user should be asked if they are sure and then the row should be checked as to what its effect will be on any related records. If it is related then you shouldn't allow deletion until the relationship is deleted. Of course this needs to be done before the actual deletion and so use the row deleting event, not the row deleted event. Using e.Cancel will stop the deletion and return to the state the row was in before the delete key was pressed.

Here is the routine:

private void dgvA_UserDeletingRow(object sender, 
             DataGridViewRowCancelEventArgs e)
{
    //this routine cannot cope with multiple selections??? 
    //The routine is looking at the current row 
    //indicated by the right arrow icon

    //Check to see if any relations before allowing delete. 
    //Note could have done a selection and delete
    if (MessageBox.Show("Do you want to delete this row?", 
        "", MessageBoxButtons.YesNo) == DialogResult.No)
    {
        e.Cancel = true;
        return;
    }
    //need to see if the AB row already exists. 
    //Dataview seems easiest?, is there a performance hit?
    
    DataView dv = ds.Tables["AB"].DefaultView;
    dv.RowFilter = "AID='" + dgvA.CurrentRow.Cells["AID"].Value.ToString() + "'";
    if (dv.Count != 0)
    {
        MessageBox.Show("Can't Delete this row as there" + 
           " are B entries depending on it - delete those first!");
        e.Cancel = true; 
    }
}

If the row being deleted is in the AB part then I need to delete the corresponding entries in the BA relation as well.

private void dgvAtoB_UserDeletingRow(object sender, 
                  DataGridViewRowCancelEventArgs e)
{
    //Check to see if the user want to delete this row.
    if (MessageBox.Show("Do you want to delete this row?", 
        "", MessageBoxButtons.YesNo) == DialogResult.No)
    {
        e.Cancel = true;
        return;
    }
    //Delete the row and delete any corresponding entries in BA
    DataRow[] dr = ds.Tables["BA"].Select("ABID = '" + 
                   dgvAtoB.CurrentRow.Cells["ABID"].Value + 
                   "'", null, DataViewRowState.CurrentRows);
    foreach (DataRow r in dr) r.Delete();
}

You will note from the comment that this routine cannot cope with multiple rows being selected for deletion. It only seems to look at the row that is highlighted and has the right arrow selected icon displayed. Then it falls over!

I think I need to intercept the delete key and then check which grid I am on and then cycle through the rows. Time for more investigation.

Does anyone have an alternative approach? In my .NET 1.1 version I inherited and overrode the DataGrid class.

So that's it. Hopefully you will get some value from this routine and be able to make use of it or some of the code snippets. Thanks to everyone who helped me solve different issues and provided code to help me.

P.S.: if you write a killer app using this routine, remember me...

(Note: There seem to be some issues with the Excel import stuff. When I wrote the routine originally I used Office 2000. Now that my new machine has Office 2002, I seem to have a few issues with the COM stuff. To solve it I have basically referenced the original DLLs as supplied in the Zip. Again if anyone has a definitive answer to that...)