A Base Windows Form for displaying, editing and automatically updating Many to Many relationships – Part 2

• Download source files - 494 Kb

Introduction

In this part I have added the routines to update the database on exit, and because I felt very generous, I have added in a routine to import data from an Excel datasheet.

I've only added the UpdateDatabase routine at this point and called it from the form closing routine. Later we shall use it when we make changes and add and delete stuff.

The routine does work if you edit a record and exit. It will update the database. However note that the change isn't reflected through to the other tab. So for instance if I change Ms Smith to Mrs Taylor then it only works in the tab in which you make the change. If you go to the other tab it wouldn't been changed. This is because one relation (AB say) does not update the other (BA) - this needs fixing, which we shall do later.

You can get it to add records as well, but you have to sort out the correct ID numbers, otherwise it falls apart. This also needs fixing. Deleting records works after a fashion as well, but we need a little more control over what it is doing. All this to be sorted out in Part 3.

The routines to update the Access database are based upon the DataAdapter.Update call. In order to use this you have to set up all the SQL statements and carefully define the parameters. To be honest I have forgotten exactly how all this works. I remember it took me many many hours of tinkering before I got it working properly and you have to be careful to specify everything exactly right.

private void UpdateDatabase()
{
    string updateSQLA = "Update A set [AID]=?, [AName]=? where [AID]=?";
    string insertSQLA = "Insert into A ([AID], [AName]) values (?,?)";
    string deleteSQLA = "Delete * from A where [AID]=?";
    //    
    //the @ is to make sure that it is treated as is, 
    //no confusion with C# keywords
    daA.UpdateCommand = new OleDbCommand(updateSQLA, conn);
    daA.UpdateCommand.Parameters.Add("@AID", OleDbType.Integer, 10, "AID");
    daA.UpdateCommand.Parameters.Add("@AName", 
                      OleDbType.VarWChar, 50, "AName");
    
    OleDbParameter AID1 = daA.UpdateCommand.Parameters.Add("@AID", 
                                    OleDbType.Integer, 10, "AID");
    AID1.SourceVersion = DataRowVersion.Original;
    
    daA.InsertCommand = new OleDbCommand(insertSQLA, conn);
    daA.InsertCommand.Parameters.Add("@AID", OleDbType.Integer, 10, "AID");
    daA.InsertCommand.Parameters.Add("@AName", OleDbType.VarWChar, 50, "AName");
    
    daA.DeleteCommand = new OleDbCommand(deleteSQLA, conn);
    OleDbParameter AID2 = daA.DeleteCommand.Parameters.Add("@AID", 
                                    OleDbType.Integer, 10, "AID");
    AID2.SourceVersion = DataRowVersion.Original;


    string updateSQLB = "Update B set [BID]=?, [BName]=? where [BID]=?";
    string insertSQLB = "Insert into B ([BID], [BName]) values (?,?)";
    string deleteSQLB = "Delete * from B where [BID]=?";
                //
    daB.UpdateCommand = new OleDbCommand(updateSQLB, conn);
    daB.UpdateCommand.Parameters.Add("@BID", OleDbType.Integer, 10, "BID");
    daB.UpdateCommand.Parameters.Add("@BName", 
                      OleDbType.VarWChar, 50, "BName");
    
    OleDbParameter BID1 = daB.UpdateCommand.Parameters.Add("@BID", 
                                    OleDbType.Integer, 10, "BID");
    BID1.SourceVersion = DataRowVersion.Original;
    
    daB.InsertCommand = new OleDbCommand(insertSQLB, conn);
    daB.InsertCommand.Parameters.Add("@BID", OleDbType.Integer, 10, "BID");
    daB.InsertCommand.Parameters.Add("@BName", 
             OleDbType.VarWChar, 50, "BName");
    
    daB.DeleteCommand = new OleDbCommand(deleteSQLB, conn);
    OleDbParameter BID2 = daB.DeleteCommand.Parameters.Add("@BID", 
                                    OleDbType.Integer, 10, "BID");
    BID2.SourceVersion = DataRowVersion.Original;


    //now need to update ParticipantGroup table in real database
    //it doesn't matter which I use, AB or BA because 
    //the info should! be the same really? and probably isn't
    //Let's try AB
    
    string updateSQLAB = "Update AB set [ABID]=?, [AID]=?," + 
                        " [BID]=? where [AID]=? and [BID]=?";
    string insertSQLAB = "Insert into AB ([ABID], [AID]," + 
                                  " [BID]) values (?,?,?)";
    
    daAB.UpdateCommand = new OleDbCommand(updateSQLAB, conn);
    daAB.UpdateCommand.Parameters.Add("@ABID", 
               OleDbType.Integer, 10, "ABID");
    daAB.UpdateCommand.Parameters.Add("@AID", 
               OleDbType.Integer, 10, "AID");
    daAB.UpdateCommand.Parameters.Add("@BID", 
               OleDbType.Integer, 10, "BID");

    OleDbParameter ABID1 = daAB.UpdateCommand.Parameters.Add("@AID", 
                                      OleDbType.Integer, 10, "AID");
    ABID1.SourceVersion = DataRowVersion.Original;
    OleDbParameter ABID2 = daAB.UpdateCommand.Parameters.Add("@BID", 
                                      OleDbType.Integer, 10, "BID");
    ABID2.SourceVersion = DataRowVersion.Original;
    
    daAB.InsertCommand = new OleDbCommand(insertSQLAB, conn);
    daAB.InsertCommand.Parameters.Add("@ABID", 
               OleDbType.Integer, 10, "ABID");
    daAB.InsertCommand.Parameters.Add("@AID", 
               OleDbType.Integer, 10, "AID");
    daAB.InsertCommand.Parameters.Add("@BID", 
               OleDbType.Integer, 10, "BID");
    
    //For testing
    //DataRow[] dr = ds.Tables["AB"].Select(null,null, 
    //                      DataViewRowState.Deleted);
    //foreach (DataRow d in dr) MessageBox.Show(d["BID", 
    //  DataRowVersion.Original].ToString()+"|"+d["AID", 
    //  DataRowVersion.Original].ToString()+"|"+d["ABID",
    //  DataRowVersion.Original].ToString());
    
    string deleteSQLAB = "Delete * from AB where [AID]=? and [BID]=?";
    daAB.DeleteCommand = new OleDbCommand(deleteSQLAB, conn);
    OleDbParameter ABID3 = daAB.DeleteCommand.Parameters.Add("@AID", 
                                      OleDbType.Integer, 10, "AID");
    ABID3.SourceVersion = DataRowVersion.Original;
    OleDbParameter ABID4 = daAB.DeleteCommand.Parameters.Add("@BID", 
                                      OleDbType.Integer, 10, "BID");
    ABID4.SourceVersion = DataRowVersion.Original;


    int g = 0, h = 0, i = 0;
    try //AB first (important)
    {
        i = daAB.Update(ds, "AB");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    try
    {
        g = daA.Update(ds, "A");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    try
    {
        h = daB.Update(ds, "B");
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.ToString());
    }
    
    //for testing
    MessageBox.Show(g.ToString()+" A and "+h.ToString()+ 
         " B and "+ i.ToString()+" AB records updated");
}

Import data from an Excel spreadsheet

In order for you to test the system with more data I've included the routine I use to import pupils and classes into my real system at school. Of course I've adapted it for this base program. You might think I've gotten the A and the B the wrong way round, but think of it as A is the teacher (or class) and B is the pupil. Then I think it makes sense. If you don't like it that way, just turn it around.

To access this, call it from the File menu and grab the file called AoB.xls in the debug folder of the app.

To use the Excel stuff you have to add a reference to the Microsoft.Excel stuff in the COM tab. I had a few problems with doing this under Beta 2.0 and so I fiddled it a bit by using the interop DLLs from my main suite of programs under 1.1 and adding a reference to them. I think you also need to add using System.Reflection. I'm hoping that if you just download the Zip it should all work, but if any of you are using the routines in your own apps, there might be assembly issues arising such as an error saying Excel not recognised etc. Try making sure that the interop DLLs are being referenced properly. Again this is an area I don't really understand, but eventually hacked it to work.

The import routine starts off with asking if you want to save the existing data. If you say yes, it will save it to a spreadsheet, which will display and you have to manually save. You could also write the stuff out to an XML file, but I haven't implemented this yet (in this app anyway; if you want ideas, then email me and I'll dig out the code from another app).

The next question is whether you want to clear the tables or not. If you don't then the system will leave the data untouched as it imports stuff. This is so you could for instance import an existing class list, with just a few additions and it will leave the existing data as is.

One major issue is with selecting a file and then importing it. As the class imports the Excel sheet, it makes decisions on what type the column will be based upon the type of data it sees. If it sees a numeric it will set up the column as a numeric, if it is a string it sets it up as a string. However if I have a class such as 7.1, this is year 7 set 1, I want it to be a string, because I might have 7.A later in the list. Because it sets it up as a number, the routine falls over at the 7.A. I didn't overcome this even though I tried many hacks. The only way I can resolve this is to ensure that the first lines of data in my Excel sheets are strings. I think it may be possible to manually create a table in the dataset and specify the types of data, but I don't know how to do this using the Excel routines?

[One little tip I've picked up with working with Access databases and strings is to remember to change single apostrophes into double apostrophes by using the mystring.Replace("'", "''"); type of operation. Dates are also a pain, especially if you are non US style.]

The routine often has to find the maximum of the ID numbers. These are the numbers I use, not the autonumber that Access generates - I don't use those as things can easily get out of sequence. I do an ExecuteScalar on the real Access database to get the maximum of the ID. E.g.: AID in table A etc. (not the ID which is autonumber generated).

I then increment this so I can make sure the stuff matches up properly. However if someone else does an update before I've finished processing and resaving, then I'm stuffed. The routines probably need some work for multi user use.

This also explains why autonumber is dangerous. It is quite easy when adding and deleting for the ID numbers in the dataset, which of course is disconnected from the real Access database, to get out of sequence with what Access will generate when you save records back. If you add three rows and delete the middle one and then save the two rows to Access, the autonumber will not know about the missing number and bang you're dead. So you'd have to manage all the numbers and re-jig everything. Imagine adding a hundred records and then deleting the first one. Think of all the extra processing you'd have to do on all the records and the related records to get everything back in sync, before you saved the dataset tables back.

There are also some strange things happening when you do a max on empty tables - it falls over (I think!). I do a count first and only if the count is greater than 0 do I do a max. It's not too much of an overhead, but it is clean.

I also do a bit of checking to see if I already have an existing AName or BName or AID-BID combination. If I do then I just ignore that record and go to the next one. This is to avoid duplication. If John belongs to Mr Moore then I only want one record of this.

Also note that I'm mucking about with the real Access database. At the end I reread this into the dataset and redisplay. Note that my column adding routines need to be done only once in a session - hence the boolean flag DoneThisOnce. If I don't have this I get repeated columns added to the DataGridView.

Also note that when you import and exit, the update database routine does not record any updates, because it's all been sorted out in the ImportFromExcel routine.