6. Database Update Lab (OPTIONAL)

 

Overview:  In this lab you're going to modify the database application you build in Lab #5.  In addition to reading information about workshop attendees from an Access database and displaying that information, this application will update changes made to the attendee's email and/or institution.  Note that this lab requires an active connection to the internet.

Part 1:  Updating the Database

1.       Typically changes to the database are made as you go rather than batched up until the application is closed by the user as was the case with the text file.  In order to update the database, the following changes will have to be made to your program:

·         Add write functions to DataAccess which update the database.

·         Add setter functions to Attendee which call methods to update the database.

·         In the GUI, call the setter functions when specific events occur.

2.       In the DataAccess class, add a function that looks like this:

 

      public static void UpdateStringField(string fieldname, long aid, string newValue)   {           

OleDbConnection  dbConn = null;

 

            string  sql; 

            int     result;

            sql = String.Format("Update Attendees Set {0}='{1}' where AID={2};", fieldname, newValue, aid);

 

            dbConn = new OleDbConnection(sConnection);

     

            OleDbCommand  dbCmd;

            dbCmd = new OleDbCommand();

            dbCmd.CommandText = sql;

            dbCmd.Connection = dbConn;

 

            dbConn.Open();

            result = dbCmd.ExecuteNonQuery();

 

            dbConn.Close();

      }

 

3.       It would be appropriate to add try, catch and finally blocks as shown in the examples above, but your program will work without them if you leave them out.

Part 2:  Changes to the Attendee class

1.       The Attendee class requires a major overhaul at this point.  We've been taking the lazy approach to changing attributes by simply making them public and assigning values to them.  Now, when we want to not only change the local variable stored in the object but also the database copy, we must take a more sophisticated approach.

2.       Start by renaming all of the attributes to m_originalname.  Change the access modifier for all attributes to private.

3.       Add getters for all attributes.  A common getter looks something like this:

public string LastName        {

      get { return this.m_LastName; }

}

4.      Add setter methods for each class attribute that is to be changed and automatically updated in the database.  Here is one example:

                        public string Institution     {

                  get { return this.m_Institution;  }

                  set               {

                        this.m_Institution = value;

                        DataAccess.UpdateStringField("Institution", this.Institution, value);

                  }

            }

Part 3:  Changes to the GUI

1.       Which events should cause changes?  Take a look at the TextChanged events for both Institution and Email (double click on the control to get to the backend code for the TextChanged event).  This code was previously written when you were working with text files – and it still works with the database backend.  So no changes are required in the GUI – the changes were made in the Attendee class and the DataAccess class.  Run and test your program.

2.       Note that this is not very efficient given how the presentation tier is probably working --- it's accessing these properties on *every* Change event.  That's an awful lot of potential DB updates, one char at a time!  In this case the GUI should probably be changed to update the underlying attendee object when the focus leaves the text box, instead of on every change.  Change the GUI to work this way, and test. 

3.       Continuing...  as the focus leaves the text box, note that you should only be updating the DB if in fact the user has modified the text.  Use the text box's Modified property to determine whether you should update the database or not.  When you update the DB, print a message to the debug window of VS so you know when you are calling the DB.  Now run and test, and make sure you only update the DB if in fact the text is updated.  Now, try this:  run, select an attendee, update a text box, tab off (this should update DB), now tab a few more times until you return to the text box, and then tab off it again without changing the text --- is the DB updated again?  Probably; you need to reset the Modified property back to false after you update the database.  Run and test again.

4.       Now test the following:  run your app, change an attendee's institution or email address, and then click the X in the top-right corner of the form to close the window and shutdown the app.  Was the database updated?  Probably not...  This is the danger to using the Enter / Leave focus events, you don't always get them (another case is when forms have default / cancel buttons triggered by Enter / ESC respectively).  So you have to handle these as special cases, or go back to using the Change event...

Part 4:  Deleting

1.       Add a delete button to your GUI which causes an Attendee to be deleted from your database.