Updating the Database with a Data Set

The two sample applications shown so far have been read-only: Each found the records matching a criteria (part of the employee name ) and displayed them. Many applications offer their users the capability to update the database as well as display records. Data sets and data grids are well suited for this task.

Modifying the Data Layer

Add another function to the Employee class in the data layer called Update . This is the declaration to add to the class definition in Employee.h:

 void Update(DataSet* newrecords); 

The code for this new method belongs in Employee.cpp. It looks like this:

 void Employee::Update(DataSet* newrecords) {     OleDbDataAdapter* adapter = new OleDbDataAdapter("SELECT * FROM Employees",                                                      ConnStr);     OleDbCommandBuilder* cb = new OleDbCommandBuilder(adapter);     adapter->Update(newrecords, "Employees"); } 

The adapter used for the update needs all four commands: select , insert , update , and delete . A data set keeps track of all the changes made to it (for example by a user working with a data grid). When the adapter saves the changes back to the database, it works with the data set to

• Insert into the database (using the insert command) all the records marked in the data set as new.

• Update all the records marked as changed.

• Delete all the records marked as deleted.

To create an adapter with all four commands, this code first creates an adapter with a select command, very similar to the select command used in the Lookup() method. It then creates an associated OleDbCommandBuilder object which will build the three other commands automatically. With the commands in place, getting all the user's changes into the database is simply a matter of calling the Update() method of the adapter. The second parameter, "Employees" , is the name of the table within the data setthis corresponds to the name given to the table when it was filled in the Lookup() method.

Modifying the User Interface

Add a button to Form1 , next to the Lookup button, and change both the text and the name to Update . Double-click the button to add and edit the handler for the click event.

The next step is to change the DataSet instance, ds , used in the Lookup click handler from a temporary local variable to a member of the Form1 class. Add these lines before the function definitions in Form1.h:

 private:     DataSet* ds; 

Change the line in the Lookup click handler that fills the data set so it no longer declares the variable:

 ds = emp->Lookup(employeeName->Text); 

Enter this code for the Update click handler:

 private: System::Void Update_Click(System::Object *  sender,                                    System::EventArgs *  e) { EmployeeData::Employee* emp = new EmployeeData::Employee(); emp->Update(ds); ds->Clear(); employeeName->Text = "" ; } 

This code creates an instance of the data layer object, uses it to update the data set back into the data source, and then clears the data set and the text box. The reason it's so simple is only partly because the data layer is doing the workafter all, the Update() method in Employee is pretty short, too. It's really the power of the adapter and the data set working together that makes this application so quick to put together.

Build and run the application (don't forget to make EmployeeUI the startup project again) and try changing someone's pay rate or name. To delete a record, click in the left margin to select the whole record and press Del. To add a record, type values in the blank row at the bottom of the data grid, and then move the cursor to another row when you're finished.

Make sure you type the right kind of data for each field (for example, a number in the Pay Rate field), because this simple application has no error checking.

When you've made some changes, click Update, and then search again, using a criteria that should find the changed records (or would find deleted ones if they weren't deleted), and make sure you see your changes. You can even close the application and open the database in Access to confirm your changes went through.