Disconnected Data via ADO.NET and DataSets

This chapter is from the book

Extending MFC Applications with the .NET Framework

Learn More Buy

This chapter is from the book

This chapter is from the book 

Extending MFC Applications with the .NET Framework

Learn More Buy

Basic Database Operations with ADO.NET

Whether you're working with a connected or disconnected data store, the majority of database operations involve NURD work—New, Update, Read, Delete. However, as this section will illustrate, many of the sometimes very tedious database operations are made much easier with the help of the various ADO.NET classes.

Reading Data

The first "real" task that many people new to a given database access layer want to explore is that of reading data. In the previous sections, you learned how to connect to a data store and construct a DataSet object that in turn contains a collection of Table objects. That is where we'll pick up here—the objects within the Table object.

The Table class contains two integral collections that you'll use most often: one for holding all columns (DataColumnCollection) and one for holding all returned rows from a query or command (DataRowCollection). Within these collections are held the DataColumn and DataRow objects, respectively. Figure 6-1 shows the relationship between these classes.

Figure 6-1. Basic relationship between DataSet, DataTable, DataRowCollection, DataRow, DataColumnCollection, and DataColumn classes

Let's start with the DataRowCollection and DataRow classes. The DataRowCollection is simply the collection of DataRow objects returned from the query or command executed by the adapter. This class is used to perform operations on the entire set of rows, such as inserting new rows, deleting rows, and searching for rows. Each of these tasks will be covered shortly. For now, we'll just be enumerating this collection in order to get at the row objects. To see how these two classes are used to read elements from a DataSet, take a look at the following function (ListAllEmployees) that connects to the sample SQL Server database Northwind, enumerates its Employees table, and retrieves the FirstName and LastName columns for each row:

void ListAllEmployees()
{
#pragma push_macro("new")
#undef new
  try
  {
    // Assumes employeesTable has already been filled by an adapter
    DataRowCollection* rows = employeesTable->Rows;
    DataRow* row;
    String* id;
    String* firstName;
    String* lastName;

    for (int i = 0; i < rows->Count; i++)
    {
      // Get DataRow object
   row = rows->Item[i];
   
   id = row->Item[S"EmployeeID"]->ToString();
   firstName = row->Item[S"FirstName"]->ToString();
   lastName = row->Item[S"LastName"]->ToString();
   }
   }
   catch(Exception* e)
   {
   MessageBox::Show(String::Format(S"Exception : {0}", e->Message));
   }
   #pragma pop_macro("new")
   }
   

As you can see, once the ListAllEmployees function retrieves the DataRowsCollection object from the Employees DataTable object (via the Rows method), it then employs a for loop to enumerate the collection. Within the loop, the DataRowCollection::Item property is called in order to retrieve each DataRow object from the collection. Once the DataRow object has been secured, the DataRow::Item property is used to retrieve the desired column's data (by column name). As you can see, once the higher-level objects (such as DataSet and the data adapter) have been constructed, the database code resembles any other—except that here you have the power of disconnected data.

Now let's look at one way the DataColumnCollection and DataColumn classes can help in reading data. As the DataColumnCollection object contains an array of every DataColumn object for a given DataTable, you can easily determine column-level information when needed—such as when coding an agnostic client—or one that has no knowledge of the data store's schema. By interrogating the DataColumn object, you can determine many important aspects of the column's definition, including column name, data type, whether or not the column is read-only, has a default value, and so on.

Take a look now at the following method, which illustrates how the DataColumnCollection and DataColumn classes can be used to dynamically determine column information and retrieve all data from a given table (the Employees table, in this case).

void DumpEmployeeTable()
{
  try
  {
    // Assumes employeesTable has already been filled by an adapter
   // Get all column names and column types...
   String* columnName;
   String* columnType;
   for (int i = 0; i < employeesTable->Columns->Count; i++)
   
   {
   // Get column name
   columnName = employeesTable->Columns->Item[i]->ColumnName;
   // Get column data type
   columnType =
   employeesTable->Columns->Item[i]->DataType->ToString();
   // Display column information
   }
   
   // Get all rows and within each row, all column data
   DataRowCollection* rows = employeesTable->Rows;
   DataRow* row;
   for (int i = 0; i < rows->Count; i++)
   {
   row = rows->Item[i];
   for (int j = 0; j < row->ItemArray->Count; j++)
   {
   String* value = row->Item[j]->ToString();
   // Display column data for current row
   }
   }
   }
   catch(Exception* e)
   {
   MessageBox::Show(String::Format(S"Exception : {0}", e->Message));
   }
   }
   

The DumpEmployeeTable function begins by enumerating the employeeTable object's DataColumnCollection with a for loop. Within that loop, each column is retrieved with a call to the DataColumnCollection::Item property. The ColumnName and DataType properties are then used to retrieve those values.

Once the column information has been acquired, the function loops through the employeeTable object's DataRowCollection in similar fashion to what you saw in the ListAllEmployees function. The main difference here being that instead of hard-coding the desired columns, the loop contains an inner loop to enumerate each row's columns. Within the inner loop, the overloaded DataRow::Item property that takes an array index value is used.

As you can see, a few trivial tweaks and this function could be modified to dump both the complete schema information and data of any table.

Inserting and Updating Rows

Having seen a bit of the DataRowCollection class, you might imagine that adding rows to a DataTable is easy, and you would be correct. There are two distinct means of inserting new rows into a DataTable, each facilitated by an overload of the DataRowCollection::Add method:

virtual DataRow* Add(Object* valueArray[]);
   void Add(DataRow* newRow);
   

The first overload takes an Object array of values and returns a DataRow object representing the new data row. Here's an example of using this method. (Notice that in the code I'm not defining the first value of the array; I'll explain why shortly.)

try
{
  // Assumes employeesTable DataTable has already been constructed
   // Allocate enough elements for all the columns
   Object* values[] = new Object*[employeesTable->Columns->Count];
   // Populate the array
   // Intentionally skipping first element of array
   values[1] = S"Tom";
   values[2] = S"Archer";
   // ...
   
   // Add the row to the DataTable object
   DataRow* newRow = employeesTable->Rows->Add(values);
   // Must call DataAdapter::Update when ready to commit changes
   // to disconnected data source
   // adapter->Update(dataset, S"AllEmployees");
   //
   
   MessageBox::Show(S"Record added successfully");
   }
   catch(Exception* e)
   {
   MessageBox::Show(String::Format(S"Exception : {0}", e->Message));
   }
   

As you can see, this code snippet first allocates an array of Object types using the Managed C++ syntax for allocating an array of reference types, as discussed in Chapter 1. The DataTable::Count property is used to ensure that the proper number of elements is allocated (although in this example I only output a couple of values for example purposes). From there, the code populates the array and calls the Add method, with the new DataRow object being returned.

However, there a couple of key issues to cover here. First, note the comment regarding the data adapter object's Update method. Calling the Update method will obviously cause the data adapter to connect to the underlying data store in order to reconcile changes in the adapter's specified DataTable. Therefore, where you place this logic will be application-specific. As an example, let's say you have a distributed application where you want to keep connections to the remote data store to a minimum. Instead of calling Update on every data change, you could place a UI element on the application (such a Commit Changes menu item) that calls the Update method and causes all updates, inserts, and deletes to be reconciled en masse.

The second issue to take note of is that of skipping the first element of the array. Typically as C++ programmers we would cringe to see someone allocate an array, not initialize the first element, and then pass that array to another function for further processing. However, in this case I know that the first element will not be used in the insert of the new record. This can be verified by inspecting the CommandBuilder object's InsertCommand (via the GetInsertCommand method). As shown here, note that the EmployeeID column is not specified in the SQL INSERT command.

"INSERT INTO Employees( LastName , FirstName , Title ,
TitleOfCourtesy , BirthDate , HireDate , Address , City , Region ,
PostalCode , Country , HomePhone , Extension , Photo , Notes ,
ReportsTo , PhotoPath ) VALUES ( @p1 , @p2 , @p3 , @p4 , @p5 , @p6 ,
@p7 , @p8 , @p9 , @p10 , @p11 , @p12 , @p13 , @p14 , @p15 , @p16 , @p17)"

However, that does bring up an interesting issue that often plagues users of tables with a primary key column that is defined as auto-increment. Specifically, the problem is determining how to insert a new record and then retrieve its auto-incremented primary key. For example, you'd want to do this if you had related tables where the primary key for one table was to be used as part of the key for another table. Also, you might need the primary key in your code in order to programmatically keep track of the records, or you might even need to return that value to the user. I'll cover a common technique for handling this situation in the section entitled "Disconnected Data and Auto-Increment Primary Keys."

Now, let's look at a code snippet that inserts new records into a DataTable object by first creating and populating a DataRow object. As a DataRow can be populated using either the DataRow:: Item property (one element at a time) or by using the ItemArray property (where an array of Object types can be specified), I'll illustrate both techniques.

try
{
  // Assumes employeesTable DataTable has already been constructed
   // Construct a DataRow object using an Object array
   DataRow* newRow1 = employeesTable->NewRow();
   Object* values[] = new Object*[employeesTable->Columns->Count];
   values[1] = S"Tom";
   values[2] = S"Archer";
   newRow1->ItemArray = values;
   employeesTable->Rows->Add(newRow1);
   // Construct a DataRow object one element at a time
   DataRow* newRow2 = employeesTable->NewRow();
   newRow2->Item[S"FirstName"] = S"Nishant";
   newRow2->Item[S"LastName"] = S"Sivakumar";
   employeesTable->Rows->Add(newRow2);
   MessageBox::Show(S"Records added successfully");
   }
   catch(Exception* e)
   {
   MessageBox::Show(String::Format(S"Exception : {0}", e->Message));
   }
   

With three row-insertion techniques to choose from, it's logical at this point to question which one is best. My personal recommendation is to use the NewRow method and then fill the returned DataRow object via the overloaded Item that allows you to specify the column name. This way, you eliminate common errors associated with assigning a value to an incorrect column. In addition, explicitly naming the column to which a value is being assigned results in code that is more readable—and therefore, more maintainable.

Deleting and Removing Rows

There are actually three distinct ways to delete (or remove) a row depending upon your needs. Here are the three methods and a brief explanation of their differences, specifically with regard to when you would use one over the other:

• DataRowCollection::Remove

• DataRowCollection::RemoveAt

• DataRow::Delete

First off, the only difference between the Remove and RemoveAt methods is that the former takes a DataRow object as its only parameter, and the latter takes an index value, or ordinal, as its sole parameter. I would encourage you to be very careful when using the RemoveAt method, since adding rows to and removing rows from the DataRowCollection will have an impact on the ordinal value of the current rows in the collection. Therefore, unless you are very sure of the index of the row you wish to delete, I recommend using one of the search methods (see "Searching, Sorting, and Filtering Data") to acquire the desired DataRow object and then passing that object to the Remove method.

// Assumes employeesTable DataTable has already been constructed
// and that you have acquired the desired row to delete
employeesTable->Rows->Remove(row);
   // Assumes employeesTable DataTable has already been constructed
   // and removes the first row in the table's row collection
   employeesTable->Rows->RemoveAt(0);
   

One important fact to keep in mind here is that the Remove/RemoveAt methods do not actually delete the row from the underlying data store upon calling the data adapter object's Update. Instead, the Remove/RemoveAt methods simply set the DataRow::State to DataRowState::Detached and remove the row from the row collection. In other words, calling the data adapter Update method to delete rows only works for rows that are still in the collection and marked as DataRowState::Deleted. This is accomplished via the DataRow::Delete method:

// Assumes employeesTable DataTable has already been constructed
// and that you have acquired the desired row to delete
row->Delete(); // delete row
   

It's worth reiterating that since the data is disconnected, the deletion of the row from the data store will not occur until you call data adapter's Update. Also, if you call the Delete method and then query either the DataRowCollection::Count property or enumerate the DataRowCollection object, it will appear as though the record has not been deleted. This is because when you directly access the DataRowCollection, you see all records in that collection regardless of their State value (Added, Changed, Deleted, etc.).

So how do we know if a record has been deleted so that it is not included in the enumeration of a row collection? This is done via the DataView object. I'll discuss the usage of DataView objects in more detail in the section entitled "Sorting, Searching, and Filtering Data with DataView Objects"). For now, however, I'll describe how the DataView object allows you to filter data rows based on their DataRowState property. In fact, every DataSet has a "built-in" default data view that's accessible via the DataTable::DefaultView property. The default view's RowStateFilter is set to DataViewRowState::CurrentRows so that the view includes unchanged, new, and modified rows, but excludes deleted rows. You can see this in the following code snippet and comments:

// Assumes employeesTable DataTable has already been constructed
// and that you have acquired the desired row to delete
row->Delete(); // delete row
   // Will be the same as the number before the delete because
   // no view is being applied
   int dataRowCount = employeesTable->Rows->Count;
   // Will be the one less than the count before the delete
   // as the default view is set to DataViewRowState::CurrentRows
   int defaultViewCount = employeesTable->DefaultView->Count;
   

So the question of which approach you should use really comes down to what capabilities your application will provide to the user. For example, if the application will allow the user to undo delete operations, then you should use the Delete method to delete rows and not use the Remove/RemoveAt method, as it would permanently remove the row from the collection. You could then use two DataView objects to view your data—one using a filter that ignores rows marked as deleted, and one using a filter that only includes deleted rows. I'll go into this in more detail in the section entitled "Sorting, Searching, and Filtering Data with DataView Objects."

If, on the other hand, you have no need to allow for the revocation of delete operations, then you can either remove or delete the row(s). Just make sure that you understand that removing a row from the rows collection means that no matter what filter you set for your view, you will not be able to view the row. Finally, if you are going to delete and remove the row, you must call the Delete method first.

Disconnected Data and Auto-Increment Primary Keys

There might be times when you work with a table that contains a primary key column defined as auto-increment (also known as auto-number, or IDENTITY). This can be especially problematic for disconnected data scenarios where the application needs to determine the auto-increment value once the record has been inserted into the data store. Here are two examples of such scenarios:

• An application where the record's primary key is used to programmatically keep track of records. For example, a maintenance application might display records in a list and need to retrieve the user-selected record from the data row collection. A newly created record could be inserted into the list with a special value indicating that it was a new record. However, once the user chooses to update the data store, the application-supplied primary key would be incorrect, and the application would need a means of retrieving the new row's actual auto-incremented primary key from the data store.

• An application that uses multiple tables that have a parent-child relationship—such as order header and order detail tables. This is especially problematic if the parent table has an auto-incremented primary key that must be used in inserting rows into the child table.

In both cases, the technique used to determine the auto-incremented value of a newly inserted row is the same. In fact, the first scenario is the subject of the next section's demo application. The second scenario is a bit more complex and brings into play a special ADO.NET class called a DataRelation, which enables an application to define a programmatic relation between two tables—a parent and child—such that the data can be navigated in a much more intuitive manner. I'll get into using the DataRelation class in the next chapter.

To understand how you can retrieve the auto-incremented primary key value from the data store, it's important to understand the complete order of execution that occurs when a row is updated against a data store.

• The DataRow object's values are moved to the adapter's parameter members. These members govern the SQL statement executed against the data store.

• The data adapter's OnRowUpdating method fires the RowUpdating event.

• The command is executed against the data store.

• The DataRow object is updated accordingly if the FirstReturnedRecord property is set or if any output parameters are specified.

• The data adapter's OnRowUpdated method fires the RowUpdated event.

• The DataRow::AcceptChanges method is called to commit the changes to the row.

As you can see, I've made two lines of interest here bold, where an event is fired both before and after the command is executed against the data store. As we're looking for a value returned as a result of executing a command, we're only interested in the second event; therefore, we need only handle the data adapter's RowUpdated event.

In .NET you handle an event by registering, for that event, a method that conforms to a delegate. A delegate is a signature for a method and is used in cases where one piece of code (a server) needs to define the signature for a method that will be passed to it from another piece of code (a client). Typical uses of delegates include callback scenarios or asynchronous event-handling situations. As a result, the event-handling method must be a managed method.

A common way of writing event-handling methods in mixed-mode applications is to define a managed class that specifies (and implements) the method that's called when the event is fired. Here are the steps involved in defining such a class to handle the RowUpdated event and retrieve the auto-incremented primary key for a newly inserted row.

1. Create a managed class that will register and implement the delegates that will handle the desired data adapter events.

   __gc class SqlEventHandler
   {
   };
   

2. Implement a class constructor that takes a data adapter as a parameter and register (with that adapter) the method to be called for the desired events. Since we're handling the RowUpdated event, that means the code needs to call the data adapter's add_RowUpdated method and pass it an SqlRowUpdatedEventHandler object. The SqlRowUpdatedEventHandler constructor takes an instance of an object that will handle the event and the method name to call when the event is fired.

   ...
   public:
     SqlEventHandler(SqlDataAdapter* adapter)
     {
       adapter->add_RowUpdated(
         new SqlRowUpdatedEventHandler(this,
           SqlEventHandler::OnUpdated));
     }
   

3. Implement the event-handling method. Since the SqlRowUpdatedEventHandler delegate defines the signature for the method, the method you define must match it perfectly. In this case, the SqlRowUpdatedEventHandler signature states that the method must accept two parameters: an Object representing the source of the event (the data adapter object) and an SqlRowUpdatedEventArgs object, which will be passed when the event fires and the method is called.

   Now let's look at an example method. The first thing you must do is verify that the row causing the event to be fired is a row that was added (as opposed to updated). Once you've determined that the row is a newly added row, construct a command object specifying the SQL SELECT @@IDENTITY command to query the data store for the new row's auto-incremented value. Calling the command object's ExecuteScalar method executes the command. Note that ExecuteScalar returns the first column of the first row of a result set that's enough for our needs, since we're only retrieving one value. With the new auto-increment value at hand, update the row's appropriate column. In the example's case, that is the EmployeeID column. Finally, call the AcceptChanges method to commit the changes to the DataRow object.

   ...
   public:
     void OnUpdated(Object* obj, SqlRowUpdatedEventArgs* e)
     {
       if (StatementType::Insert == e->StatementType)
       {
         SqlCommand* command =
           new SqlCommand(S"SELECT @@IDENTITY",
                          e->Command->Connection);
         e->Row->Item[S"EmployeeID"] = command->ExecuteScalar());
         e->Row->AcceptChanges();
       }
     }
   

4. That's it for the event-handling side of things. Now you simply construct the new class—typically just after constructing the data adapter:

   ...
   SqlConnection* conn =
     new SqlConnection(S"Server=localhost;"
                       S"Database=Northwind;"
                       S"Integrated Security=true;");
   adapter = new SqlDataAdapter(S"SELECT * FROM Employees", conn);
   eventHandler = new SqlEventHandler(adapter, this);
               ...
               

Now the SqlEventHandler::OnUpdated gets called for each updated or inserted row anytime you call the adapter's Update method to synchronize your local in-memory changes with the data store.

Filling in Missing Schema and Primary Key Information for Untyped Datasets

When using untyped datasets, certain schema information—such as primary key information is not available. The following code snippet illustrates this point. If you want to test this code, you'll need to create the second dataset (EmployeesDataSet) using the steps listed in the section entitled "Generating a Typed DataSet").

SqlConnection* conn =
  new SqlConnection(S"Server=localhost;"
                    S"Database=Northwind;"
                    S"Integrated Security=true;");
adapter* = new SqlDataAdapter(S"SELECT * FROM Employees", conn);
conn->Open();
// Untyped DataSet
DataSet* untyped = new DataSet();
adapter->Fill(untyped, S"AllEmployees");
int count1 =
  dataset->Tables->Item["AllEmployees"]->PrimaryKey->Length;
// count1 will be 0
   // Typed DataSet
   EmployeesDataSet* typed = new EmployeesDataSet();
   adapter->Fill(typed->EmployeesDataTable);
   int count2 = typed->EmployeesDataTable->PrimaryKey->Length;
   // count2 will be 1
   conn->Close();
   

As the comments indicate, the data table will only return the correct count of primary keys if the DataSet is typed. This can cause a problem if you wish to use certain ADO.NET functionality, which relies on the data table properly representing the data store. For example, the DataRowCollection::Find method allows you to search through a row collection based on a primary key. However, attempting to call the Find method using the untyped dataset, as illustrated in the previous code snippet, results in a System::Data::MissingPrimaryKeyException because no primary key has been defined—at least as far as the data table object is concerned. There are two solutions to this problem.

The first solution is simply to tell the data adapter to gather the schema information when filling the dataset. This is accomplished via the data adapter's MissingSchemaAction property, which accepts a MissingSchemaAction enumeration value that tells the adapter what action to take when the DataSet schema doesn't match the incoming data. For example, suppose you programmatically define a DataTable object that doesn't match the incoming data in terms of the number of columns. Let's say you've defined a DataTable that has only two columns, and you want to read the Employees table data (which contains many more columns) into that DataTable. Since the schemas don't match, the data adapter has to be told how to handle that situation, and that's exactly what the MissingSchemaAction enumeration is typically used for. By specifying a value of MissingSchemaAction::Add, the adapter will add any necessary columns to complete the schema. Specifying a value of MissingSchemaAction.Ignore results in the data adapter ignoring any extra columns (and not downloading that data) and MissingSchemaAction.Error results in an exception if the schemas don't match. The default MissingSchemaAction property value is Add.

So, as you can see, the MissingSchemaAction property is mainly used to map dissimilar schemas to one another, which doesn't seem to be what we're after. However, there is one last MissingSchemaAction enumeration value—AddWithKey—that does exactly what we want. This value is similar to the Add value except that it also adds the primary key information to complete the schema. As a result, simply setting the MissingSchemaAction property to AddWithKey just before the Fill method call will result in the DataTable being properly constructed with the data source's primary key information:

SqlConnection* conn =
  new SqlConnection(S"Server=localhost;"
                    S"Database=Northwind;"
                    S"Integrated Security=true;");
adapter = new SqlDataAdapter(S"SELECT * FROM Employees", conn);
adapter->MissingSchemaAction = MissingSchemaAction::AddWithKey;
   conn->Open();
   dataset = new DataSet(); adapter->Fill(dataset, S"AllEmployees");
   // Untyped DataSet
   DataSet* untyped = new DataSet();
   adapter->Fill(untyped, S"AllEmployees");
   int count1 =
   dataset->Tables->Item["AllEmployees"]->PrimaryKey->Length;
   // count1 will now be 1
   

One important thing to note is that when a data adapter whose MissingSchemaAction property is set to AddWithKey creates a DataColumn object for a column defined as a primary key in the data source, that DataColumn object is marked as read-only (DataColumn::ReadOnly is set to true). Obviously, if you do not need to alter the primary key value, this solution will work fine for you.

However, there are plenty of scenarios where you'll need to modify the local value for a primary key. For example, in the previous section you saw that the local value for an auto-increment primary key of a new row can't be realized until the row is inserted and that value is retrieved from the data store. The local value is then updated to properly reflect the data store's value. This can't be done if the DataColumn is set to read-only. Therefore, another mechanism must be used to indicate that a given data source column is a primary key. We can do this via the DataTable::PrimaryKey property.

SqlConnection* conn =
  new SqlConnection(S"Server=localhost;"
                    S"Database=Northwind;"
                    S"Integrated Security=true;");
adapter = new SqlDataAdapter(S"SELECT * FROM Employees", conn);
conn->Open();
dataset = new DataSet(); adapter->Fill(dataset, S"AllEmployees");
DataTableCollection* tables = dataset->Tables;
employeesTable = tables->Item[S"AllEmployees"];
DataColumn* primaryKeys[] = new DataColumn*[1];
   primaryKeys[0] = employeesTable->Columns-> Item[0];
   employeesTable->PrimaryKey = primaryKeys;
   // Untyped DataSet
   DataSet* untyped = new DataSet();
   adapter->Fill(untyped, S"AllEmployees");
   int count1 =
   dataset->Tables->Item["AllEmployees"]->PrimaryKey->Length;
   // count1 will now be 1
   

After the DataSet object is filled, a DataColumn array is allocated, and the first DataColumn object from the employees DataTable is inserted into it, as the first column is the EmployeeId primary key. The PrimaryKey property is then set to the DataColumn array, thereby accomplishing what we need.

You could also set the MissingSchemaAction property and then, after the DataSet is filled, set the desired DataColumn object's ReadOnly property to false. However, it's my opinion that if you're going to manually override what the adapter does anyway, you might as well save yourself the overhead of requesting that the schema information be retrieved during the DataSet fill and simply override the entire process.

So, in summary, I recommend using the MissingSchemaAction property if your application will not need to modify the DataColumn object corresponding to the primary key; otherwise use the PrimaryKey property.

Demo—Simple Maintenance Application

Let's test what you've learned to this point with a semi-realistic MFC SDI demo application that lists all the employee records from the sample SQL Server Northwind database's Employees table. The application will allow you to create new records as well as edit and delete existing records using the classes and techniques you've learned about up to this point. While this demo contains a few more steps than I normally prefer to include in a book demo, there are a lot of little things you have to do to make a realistic ADO.NET application, and seeing how everything fits together is paramount to understanding how to use ADO.NET in a real-world application.

1. To get started, create a new MFC project called EmployeeMaintenance—where the application type is SDI and the view class is a CListView—and update the Project properties to support Managed Extensions.

2. Open the stdafx.h file and add the following .NET support directives to the end of the file.

   #using <mscorlib.dll>
   #using <system.dll>
   #using <system.data.dll>
   #using <system.xml.dll>
   #using <system.windows.forms.dll>
   
   using namespace System;
   using namespace System::Data;
   using namespace System::Data::SqlClient;
   using namespace System::Xml;
   using namespace System::Windows::Forms;
   #undef MessageBox
   

3. In the view class's PreCreateWindow function, set the view window's style to "report" and single selection:

   BOOL CEmployeeMaintenanceView::PreCreateWindow(CREATESTRUCT& cs)
   {
     cs.style |= LVS_REPORT;
               cs.style |= LVS_SINGLESEL;
               return CListView::PreCreateWindow(cs);
               }
               

4. Add the following code to the end of the view's OnInitialUpdate function to initialize the list view.

   void CEmployeeMaintenanceView::OnInitialUpdate()
   {
         CListView::OnInitialUpdate();
   
     ...
   
     CListCtrl& lst = GetListCtrl();
               // All full row selection
               LONG lStyle =
               (LONG)lst.SendMessage(LVM_GETEXTENDEDLISTVIEWSTYLE);
               lStyle |= LVS_EX_FULLROWSELECT;
               lst.SendMessage(LVM_SETEXTENDEDLISTVIEWSTYLE,  0,
               (LPARAM)lStyle);
               // Add columns to listview lst.InsertColumn(0, _T("ID"));
               lst.InsertColumn(1, _T("First Name"));
               lst.InsertColumn(2, _T("Last Name"));
               }
               

5. Now that the main UI is done, let's focus on the view-level ADO.NET objects and their construction. First, define the following ADO.NET objects in the CEmployeeMaintenanceView class that will be used throughout the application.

   class CEmployeeMaintenanceView : public CListView
   {
   ...
   protected:
               gcroot<DataSet*>dataset;
               gcroot<SqlDataAdapter*>adapter;
               gcroot<DataTable*>employeesTable;
               gcroot<SqlCommandBuilder*>commandBuilder;
               ...
               

6. Add the following code to the end of the view's OnInitialUpdate function to initialize the ADO.NET objects. As you can see, the first thing that is done is to make the connection to the sample SQL Server Northwind database. From there, the code constructs an SqlDataAdapter object (adapter) with an SQL SELECT statement that retrieves all records from the Employees table.

   Once the adapter has been constructed, an SqlCommandBuilder object (eventHandler) is instantiated, and the connection is opened. A DataSet object (dataset) is then constructed and filled with employee records via the SqlDataAdapter::Fill method, and the resulting DataTable object is named "AllEmployees." At this point, we have the data in memory, and so the connection to the data store is closed. A DataTable object (employeesTable) is then allocated and points to the "AllEmployees" DataTable created during the Fill method. (Note that declaring a DataTable object is done just for convenience, as the application could retrieve the table from the DataSet object's DataTable collection each time it needs access to the table.)

   The employeesTable primary key is defined as the table's first column via the PrimaryKey property. This is done so that the DataRowCollection::Find method can be used to locate records by their primary key. Finally, a helper function called ReadAllEmployees is called.

   void CEmployeeMaintenanceView::OnInitialUpdate()
   {
     CListView::OnInitialUpdate();
   
     ...
   
   #pragma push_macro("new")
   #undef new
   try
     {
       SqlConnection* conn =
         new SqlConnection(S"Server=localhost;"
                           S"Database=Northwind;"
                           S"Integrated Security=true;");
       adapter = new SqlDataAdapter(S"SELECT * FROM Employees",
         conn);
       commandBuilder = new SqlCommandBuilder(adapter);
       conn->Open();
       dataset = new DataSet();
       adapter->Fill(dataset, S"AllEmployees");
       conn->Close(); // No longer needed
       DataTableCollection* tables = dataset->Tables;
       employeesTable = tables->Item[S"AllEmployees"];
       // Set the table's primary key column(needed for
       // DataRowCollection::Find method) Can't use
       // DataAdapter::MissingSchemaAction because that
       // would make the EmployeeID readonly and I need to
       // set that value after inserts are realized against
       // the data store.
       DataColumn* primaryKeys[] = new DataColumn*[1];
       primaryKeys[0] = employeesTable->Columns->Item[0];
       employeesTable->PrimaryKey = primaryKeys;
       employeesTable->Columns->Item[0]->ReadOnly = false;
       ReadAllEmployees();
     }
     catch(Exception* e)
     {
       MessageBox::Show(e->Message, S".NET Exception Thrown",
                        MessageBoxButtons::OK,
                        MessageBoxIcon::Error);
     }
   #pragma pop_macro("new")
   }
   

7. Implement the following ReadAllEmployees member function. After initializing the list view, this function enumerates the employeesTable object's row collection, retrieving each record's EmployeeId, FirstName and LastName values and inserting them into the list view.

   void CEmployeeMaintenanceView::ReadAllEmployees()
   {
     try
     {
       CWaitCursor wc;
       CListCtrl& lst = GetListCtrl(); lst.DeleteAllItems();
       DataRowCollection* rows = employeesTable->Rows;
       DataRow* row;
       String* firstName;
       String* lastName;
       String* id;
       for (int i = 0; i < rows->Count; i++)
       {
         row = rows->Item[i];
         id = row->Item[S"EmployeeID"]->ToString();
         firstName = row->Item[S"FirstName"]->ToString();
         lastName = row->Item[S"LastName"]->ToString();
         int idx = lst.InsertItem(i, (CString)id);
         lst.SetItemText(idx, 1, (CString)firstName);
         lst.SetItemText(idx, 2, (CString)lastName);
       }
     }
     catch(Exception* e)
     {
       MessageBox::Show(e->Message, S".NET Exception Thrown",
                        MessageBoxButtons::OK,
                        MessageBoxIcon::Error);
     }
   }
   

8. At this point, you should be able to build and run the application and see the employee records in the list view. Now, let's add the ability to create, edit, and delete records. Begin by adding an Employees pop-up menu as shown in Figure 6-2.

   Figure 6-2. The Employee Maintenance menu for adding, editing, and deleting employee records

9. Now let's implement the ability to create new employee records. Start by adding the dialog shown in Figure 6-3.

   Figure 6-3. The dialog for adding and editing employee records

10. Add a CDialog-based class called CEmployeeDlg for the dialog resource and include its header file in the view's implementation file (EmployeeMaintenanceView.cpp).

    Table 6-1. DDX variables for the EmployeeMaintenance demo

    Control	Variable Type	Variable Name
    Employee ID	int	m_iEmployeeId
    First Name	Cstring	m_strFirstName
    Last Name	Cstring	m_strLastName

11. Returning to the resource editor, add the DDX value variables for the CEmployeeDlg as shown in Table 6-1.

12. Each employee record is uniquely identified by its EmployeeId column. However, as this column is defined as auto-increment (IDENTITY), we won't know its value for a newly created record until the data source has been updated, the database has generated the value, and that value is retrieved using a technique such as that described in the section entitled "Disconnected Data and Auto-Increment Primary Keys." However, we need a way to uniquely identify newly created records before the data store is updated. Therefore, we need to associate any newly created records with an application-generated key that will not conflict with any possible real values. One way to accomplish this is to create an application variable that starts out as a negative value and decrements with each new record. This will work because database auto-increment values start at 0 and increment. Therefore, define the following static variable (newRowId) to the top of the EmployeeMaintenanceView.cpp file.

    // Used as a temporary id for new records not yet added to the database
    static newRowId = -1;
    

13. The next thing we'll want to do is to track which records in the list view are new records. We'll do this so that when the data store is updated, it's easy to locate the affected records and update their EmployeeId value with the data store generated value. We'll use a simple MFC map collection to do this. Define the following CMapPtrToPtr member in the view class.

    class CEmployeeMaintenanceView : public CListView
    {
    ...
    protected:
                CMapPtrToPtr mapIdToLVIndex;
                

14. Now that everything's in place, add the following #define directive and event handler for the New option of the Employee menu to the CEmployeeMaintenanceView class. The function first displays the CEmployeeDlg. If the user clicks the Save (IDOK) button, the function constructs a new DataRow object and initializes it by setting the EmployeeId to the value of the newRodId and the FirstName and LastName values to those of the dialog's member variables (representing the data entered by the user).

    The data is then added to the list view. You'll notice that I opted to set the list view's employeeID column to NEW_RECORD ("NEW") instead of the newRowId value. This was simply a choice of aesthetics. However, since I need to keep track of the row's newRowId value, I stuff that value into the item's item data for later retrieval. The map is then updated where the newRowId maps to the index of the newly added item in the list view. Finally, the newRowId value is decremented for the next new record.

    #define NEW_RECORD "NEW"
    
    ...
    
    void CEmployeeMaintenanceView::OnEmployeeNew()
    {
      try
      {
        CListCtrl& lst = GetListCtrl();
        CEmployeeDlg dlg; if (IDOK == dlg.DoModal())
        {
          CWaitCursor wc;
          DataRow* newRow = employeesTable->NewRow();
          newRow->Item[S"EmployeeID"] = __box(newRowId);
          newRow->Item[S"FirstName"] =
            (String*)dlg.m_strFirstName;
          newRow->Item[S"LastName"] = (String*)dlg.m_strLastName;
          employeesTable->Rows->Add(newRow);
          int idx = lst.InsertItem(lst.GetItemCount(),
            NEW_RECORD);
          lst.SetItemData(idx, (int)newRowId);
          lst.SetItemText(idx, 1, dlg.m_strFirstName);
          lst.SetItemText(idx, 2, dlg.m_strLastName);
          mapIdToLVIndex.SetAt((void*)newRowId, (void*)idx);
          // when finished, decrement static new row counter
          newRowId—;
        }
      }
      catch(Exception* e)
      {
        MessageBox::Show(e->Message, S".NET Exception Thrown",
                         MessageBoxButtons::OK,
                         MessageBoxIcon::Error);
      }
    }
    

15. We can add records to the list view, but that's all. Therefore, let's implement the Commit Changes menu item so that we can see the data store get updated. As you can see, there's really nothing much to do here besides call the data adapter's Update method—specifying which table to update—and handle any potential exceptions.

    void CEmployeeMaintenanceView::OnEmployeeCommitChanges()
    {
      try
      {
        CWaitCursor wc;
        adapter->Update(employeesTable);
        MessageBox::Show(S"Changed committed", S"Information",
                         MessageBoxButtons::OK,
                         MessageBoxIcon::Information);
      }
      catch(Exception* e)
      {
        MessageBox::Show(e->Message, S".NET Exception Thrown",
                         MessageBoxButtons::OK,
                         MessageBoxIcon::Error);
      }
    }
    

16. At this point, building and running the code results in new records being added to the data store. However, we still have one hurdle to clear. We need to update the DataRow (and list view) with the data store generated EmployeeId. If we skip this, attempts to edit or delete the row after updating the data store will result in an exception, as our application-supplied temporary value is invalid. To start with, forward-declare the following class before the CEmployeeMaintenanceView class. We have to do this because—as you'll soon see—there's a circular reference between the SqlEventHandler class and the CEmployeeMaintenanceView class.

    __gc class SqlEventHandler;
                class CEmployeeMaintenanceView : public CListView
                {
                ...
                

17. Now, implement the SqlEventHandler class. This class will be used to handle the data adapter's RowUpdated event so that we can determine the new EmployeeId value for newly inserted records. The class's constructor takes both a pointer to an adapter object as well as a pointer to the view object. The latter is needed because the object needs to tell the view when it has handled an event. The OnUpdated method—what actually gets called when the data adapter's RowUpdated event fires—saves the previous EmployeeId value and then uses the SELECT @@IDENTITY command to retrieve the new EmployeeId value. Both values are then passed to the view's OnRowInserted function.

    __gc class SqlEventHandler
    {
    public:
      SqlEventHandler(SqlDataAdapter* adapter,
                      CEmployeeMaintenanceView* parentView)
      {
        adapter->add_RowUpdated(new
          SqlRowUpdatedEventHandler(this,
          SqlEventHandler::OnUpdated));
        this->parentView = parentView;
      }
    protected:
      CEmployeeMaintenanceView* parentView;
    public:
      void OnUpdated(Object* obj, SqlRowUpdatedEventArgs* e)
      {
        if (StatementType::Insert == e->StatementType)
        {
          SqlCommand* command =
            new SqlCommand(S"SELECT @@IDENTITY",
                           e->Command->Connection);
          int previousId =
            *dynamic_cast<__box int*>(e->Row->
              Item[S"EmployeeId"]);
          e->Row->Item[S"EmployeeID"] = command->ExecuteScalar();
          int newId =
            *dynamic_cast<__box int*>(e->Row->
              Item[S"EmployeeId"]);
          e->Row->AcceptChanges();
          parentView->OnRowInserted(previousId, newId);
        }
      }
    };
    

18. Now, implement the view's (public) OnRowInserted function as follows. Here the function is simply using the previous EmployeeId value to search the mapIdToLVIndex collection for the list view index of the just-inserted row. The list view item is then updated with the new EmployeeId value, and the entry is removed from the mapIdToLVIndex collection, as this is the only function that uses it, and it's no longer needed once the EmployeeId is updated.

    void CEmployeeMaintenanceView::OnRowInserted(int iPrevId, int iNewId)
    {
      void* iListViewIdx;
      mapIdToLVIndex.Lookup((void*)iPrevId, (void*&)iListViewIdx);
      ASSERT(-1 < (int)iListViewIdx); if (-1 < (int)iListViewIdx)
      {
        CListCtrl& lst = GetListCtrl();
        CString strNewId;
        strNewId.Format(_T("%ld"), iNewId);
        lst.SetItemText((int)iListViewIdx, 0, strNewId);
        mapIdToLVIndex.RemoveKey((void*)iPrevId);
      }
    }
    

19. Define a CEmployeeMaintenanceView member variable called eventHandler that is of type SqlEventHandler This is why we had to forward-declare the SqlEventHandler class before the view class's definition.

    class CEmployeeMaintenanceView : public CListView
    {
    ...
    public:
      gcroot<SqlEventHandler*>eventHandler;
                

20. Now that the code is in place to handle the data adapter's RowUpdated event, we need only subscribe to the event. Since the subscription to the event takes place in the SqlEventHandler object's construct, we just need to construct the SqlEventHandler at the appropriate time. The perfect place to do this is in the view's OnInitialUpdate function immediately after the data adapter object has been instantiated:

    void CEmployeeMaintenanceView::OnInitialUpdate()
    {
    ...
    adapter = new SqlDataAdapter(S"SELECT * FROM Employees",
      conn);
    eventHandler = new SqlEventHandler(adapter, this);
                

21. At this point, you can now add records to the data store! Let's finish up this demo by implementing the edit and delete functions. Start by adding the following helper function to the CEmployeeMaintenanceView class, which will return the currently selected item index of the list view.

    int CEmployeeMaintenanceView::GetSelectedItem()
    {
      int iCurrSel = -1;
      CListCtrl& lst = GetListCtrl();
    POSITION pos = lst.GetFirstSelectedItemPosition();
      if (pos)
        iCurrSel = lst.GetNextSelectedItem(pos);
      return iCurrSel;
    }
    

22. Implement the following event handler for the Edit option of the Employee menu. As you can see, the function attempts to determine the EmployeeId for the row by first looking at the first column of the list view. However, if that value is equal to "NEW", then the function retrieves the EmployeeId value from the list view item's item data. From there, the CEmployeeDlg object's member variables are initialized, and the dialog is displayed.

    If the user enters data and clicks the Save button (IDOK), the DataRow for the edited row is located by specifying the EmployeeId to the DataRowCollection::Find method. Being able to search the DataRowCollection is another reason why the application needed to maintain a temporary EmployeeId for new rows. Once the row is retrieved, it's FirstName and LastName columns are updated and so is the list view.

    void CEmployeeMaintenanceView::OnEmployeeEdit()
    {
      try
      {
        CListCtrl& lst = GetListCtrl();
    
        int currSel = GetSelectedItem();
        if (-1 < currSel)
      {
        CEmployeeDlg dlg;
    
        CString strId = lst.GetItemText(currSel, 0);
        Int32 id;
        if (0 == strId.Compare(NEW_RECORD))
          id = (int)lst.GetItemData(currSel);
        else
          id = atoi(strId);
    
         dlg.m_iEmployeeId = id;
         dlg.m_strFirstName = lst.GetItemText(currSel, 1);
         dlg.m_strLastName = lst.GetItemText(currSel, 2);
    
        if (IDOK == dlg.DoModal())
        {
          CWaitCursor wc;
          DataRow* row = employeesTable->Rows->Find(__box(id));
          if (row)
          {
            row->Item[S"FirstName"] = (String*)dlg.m_strFirstName;
            row->Item[S"LastName"] = (String*)dlg.m_strLastName;
    
            lst.SetItemText(currSel, 1, dlg.m_strFirstName);
            lst.SetItemText(currSel, 2, dlg.m_strLastName);
          }
        }
      }
      else
      {
        MessageBox::Show(S"You must first select an employee "
                 S"to perform this operation.",
                 S"Alert",
                 MessageBoxButtons::OK,
                 MessageBoxIcon::Error);
      }
    }
    catch(Exception* e)
    {
      MessageBox::Show(e->Message, S".NET Exception Thrown",
               MessageBoxButtons::OK,
               MessageBoxIcon::Error);
      }
    }
    

23. Finally, implement the following event handler for the Delete option of the Employee menu. The function starts out much like the Edit menu event handler by first retrieving the DataRow for the currently selected employee. Once that is done, the DataRow::Delete method is called, and the row is removed from the list view.

    void CEmployeeMaintenanceView::OnEmployeeDelete()
    {
      try
      {
        CListCtrl& lst = GetListCtrl();
        int currSel = GetSelectedItem(); if (-1 < currSel)
        {
          CWaitCursor wc;
          CString strId = lst.GetItemText(currSel, 0);
          Int32 id;
          if (0 == strId.Compare(NEW_RECORD))
            id = (int)lst.GetItemData(currSel);
          else
            id = atoi(strId);
          DataRow* row = employeesTable->Rows->Find(__box(id));
          if (row)
          {
            row->Delete();
            lst.DeleteItem(currSel);
          }
        }
        else
        {
          MessageBox::Show(S"You must first select an employee "
                           S"to perform this operation.",
                           S"Alert",
                           MessageBoxButtons::OK,
                           MessageBoxIcon::Error);
        }
      }
      catch(Exception* e)
      {
        MessageBox::Show(e->Message, S".NET Exception Thrown",
                         MessageBoxButtons::OK,
                         MessageBoxIcon::Error);
      }
    }
    

Finally we're done! As I mentioned in the beginning of this chapter, while disconnected data is a wonderful thing for certain scenarios, it also increases the complexity of even something as simple as a maintenance application. However, just saying (or writing) that doesn't have the same impact as actually walking through a step-by-step demo such as this EmployeeMaintenance application and seeing for yourself the various issues that arise in a disconnected setting and how to deal with them one by one. Figure 6-4 shows an example of the application being run.

Figure 6-4. The EmployeeMaintenance demo at work