Disconnected ADO.NET

Let's switch gears and now look at disconnected ADO.NET. Disconnected data access is a key feature of ADO.NET. Basically, it means that most of the time when you're accessing a database, you aren't getting the data from the database at all. Instead, you're accessing a synchronized, in-memory copy of the data that was moved earlier to your client computer. Don't worry about all the technical issues surrounding this; just be glad that it works because it provides three major benefits:

• Less congestion on the database server because users are spending less time connected to it

• Faster access to the data because the data is already on the client

• Capability to work across disconnection networks such as the Internet

It also offers one benefit (associated with disconnected access) that is less obvious: Data doesn't have to be stored in a database-like format. Realizing this, Microsoft decided to implement ADO.NET using a strong typed XML format. The benefit is that having data in XML format enables data to be transmitted using standard HTTP. This causes a further benefit: Firewall problems disappear. An HTTP response with the body of XML flows freely through a firewall, unlike the pre-ADO.NET technology's system-level COM marshalling requests. If the previous bonus is Greek (or geek) to you, don't fret. In fact, be glad you have no idea what I was talking about.

The Core Classes

If you spend a lot of time working with ADO.NET, you may have an opportunity to work with almost all of ADO.NET's classes. For the purposes of this book, however, I've trimmed these classes down to the following:

• DataAdaptor

• DataSet

• DataTableCollection

• DataTable

• DataRow

• DataColumn

• DataRelationCollection

• DataRelation

• Constraint

All of these classes interact with each other in some way. Figure 12-14 shows the flow of the interaction. Essentially, the DataAdaptor connects the data store to the DataSet. The DataSet stores the data in a Tables property containing a DataTablesCollection made up of one or more DataTables. Each DataTable is made up of DataRows and DataColumns. All of the DataTables store their relationships in a Relations property containing a DataRelationCollection made up of DataRelations. Finally, each DataTable can be affected by Constraints. Simple, isn't it?

Figure 12-14: The disconnected ADO.NET class interaction

DataAdaptor

The DataAdaptor is the bridge between a data source (database) and the DataSet. Its purpose is to extract data out of the data source and place it in the DataSet. Then it updates, if required, the data source with the changes made in the DataSet.

It should be relatively easy to get comfortable with the SqlDataAdaptor, OleDbDataAdaptor, OdbcDataAdaptor, or OracleDataAdaptor, as they use (just like connected database access) a connection class to connect to the data source and a command class to add, update, and select data out of the data source.

The basic idea behind using the DataAdaptor is to provide SQL commands to the following four properties to handle sending and receiving data between the DataSet and the data store:

• SelectCommand

• InsertCommand

• UpdateCommand

• DeleteCommand

If you plan to only read data from the database, then only the SelectCommand property needs to be provided.

With these properties provided, it is a simple matter to call the DataAdaptor class's Fill() method to select data from the data store to the DataSet and to call the Update() method to insert, update, and/or delete data from the DataSet to the data store.

DataSet Class

The DataSet is the major controlling class for disconnected ADO.NET. A DataSet is a memory cache used to store all data retrieved from a data source, in most cases a database or XML file. The data source is connected to the DataSet using a DataAdaptor.

A DataSet consists of one or more data tables in a DataTableCollection class, which in turn is made up of data rows and data columns. Relationships between the tables are maintained via a DataRelationsCollection class. The DataSet also stores the format information about the data.

A DataSet is data source-independent. All it understands is XML. In fact, all data sent or received by the DataSet is in the form of an XML document. The DataSet has methods for reading and writing XML, and these are covered in Chapter 13.

A DataSet also provides transactional access to its data. To commit all changes made to the DataSet from the time it was created or since the last time it was committed, call the DataSet class's AcceptChanges() method. If you want to roll back changes since the DataSet was corrected or since it was last committed, call the RejectChanges() method. What actually happens is a cascading effect where the AcceptChanges() and RejectChanges() methods execute their table's versions of the method, which in turn calls the table's rows' version. Thus, it is also possible to commit or roll back at the table and row levels.

DataTableCollection Class

A DataTableCollection is a standard collection class made up of one or more DataTables. Like any other collection class, it has functions such as Add, Remove, and Clear. Usually, you will not use any of this functionality. Instead, you will use it to get access to a DataTable stored in the collection.

The method of choice for doing this will probably be to access the DataTableCollection indexed property Item, using the name of the table that you want to access as the index:

 DataTable *dt = dSet->Tables->Item["Authors"]; 

It is also possible to access the same table using the overloaded array property version of Item:

 DataTable *dt = dSet->Tables->Item[0]; 

With this method, you need to know which index is associated with which table. When you use the indexed property, it is a little more obvious.

DataTable Class

Put simply, a DataTable is one table of data stored in memory. A DataTable also contains constraints, which help ensure the integrity of the data it is storing.

It should be noted that a DataTable can be made up of zero or more DataRows, because it is possible to have an empty table. Even if the table is empty, the Columns property will still contain a collection of the headers that make up the table.

Many properties and methods are available in the DataTable, but in most cases you will simply use it to get access to the rows of the table. Two of the most common methods are enumerating through the Rows collection:

 IEnumerator *Enum = dt->Rows->GetEnumerator(); while(Enum->MoveNext()) {     DataRow *row = dynamic_cast<DataRow*>(Enum->Current);     //...Do stuff to row } 

and selecting an array of DataRows using the Select() method:

 DataRow *row[] =     dt->Select(String::Format(S"AuthorID={o}", __box(CurrentAuthorID))); 

Another method that you will probably come across is NewRow(), which creates a new DataRow, which will later be added to the DataTable Rows collection:

 DataRow *row = dt->NewRow(); //...Build row dt->Rows->Add(row); 

DataRow Class

The DataRow is where the data is actually stored. You will frequently access the data from the DataRow as indexed property Item, using the name of the column that you want to access as the index.

 row->Item[S"LastName"] = tbLastName->Text; 

It is also possible to access the same column using the overloaded array property version of Item:

 row->Item[o] = tbLastName->Text; 

With this method, you need to know which index is associated with which column. When you use the indexed property, it is a little more obvious.

DataColumn Class

You use the DataColumn class to define the columns in a DataTable. Each DataColumn has a data type that determines the kind of data it can hold. A DataColumn also has properties similar to a database, such as AllowNull and Unique. If the DataColumn autoincrements, then the AutoIncrement property is set. (Now, that makes more sense than Identity.)

DataRelationCollection Class

A DataRelationCollection is a standard collection class made up of one or more DataRelations. Like any other collection class, it has functions such as Add, Remove, and Clear. Usually, as with the DataTableCollection class, you will not use any of this functionality. Instead, you will simply use it to get access to the DataRelations it stores.

DataRelation Class

A DataRelation is used to relate two DataTables together. It does this by matching DataColumns between two tables. You can almost think of it as the ADO.NET equivalent of the foreign-key relationship in a relational database (like you previously set).

One important thing you have to keep in mind is that the DataColumns must be the same data type. Remember that ADO.NET has strong data types, and when comparing different data types, one data type must be converted to the other. This conversion is not done automatically.

Constraint Classes

The Constraint classes make it possible to add a set of constraints on a particular column in your DataTable. Two types of constraints are currently supported by ADO.NET:

• ForeignKeyConstraint disallows a row to be entered unless there is a matching row in another (parent) table.

• UniqueConstraint makes sure that a column is unique within a DataTable.

Creating a Table Manually in Code

Normally, database designers build the databases that you use, but the DataColumn, DataRelation, and Constraint classes allow you as a developer to build a DataTable dynamically. The following snippet of code shows how to create the Authors DataTable manually:

 //Create an empty DataTable DataTable *Authors = new DataTable(S"Authors2"); // Add all the columns Authors->Columns->Add(new DataColumn(S"AuthorID",                                          Type::GetType(S"System.Int32"))); Authors->Columns->Add(new DataColumn(S"LastName",                                          Type::GetType(S"System.String"))); Authors->Columns->Add(new DataColumn(S"FirstName",                                          Type::GetType(S"System.String"))); // Add autoincrement to AuthorID Authors->Columns->Item[S"AuthorID"]->AutoIncrement = true; // Make AuthorID unique Authors->Constraints->Add(     new UniqueConstraint(S"PK_AuthorID", Authors->Columns->Item[S"AuthorID"])); // Make AuthorID the Primary key DataColumn *key[] = new DataColumn*[1]; key[0] = Authors->Columns->Item[S"AuthorID"]; Authors->PrimaryKey = key; // Create a relation between AuthorID in Authors and Content tables dSet->Relations->Add(S"StoryLink",        Authors2->Columns->Item["AuthorID"],        dSet->Tables->Item[S"Content"]->Columns->Item["AuthorID"]); // add table to DataSet dSet->Tables->Add(Authors); 

Developing with Disconnected ADO.NET

In the final example of this chapter, you're going to build a small Win Form application to maintain the Authors DataTable that you've been working with throughout the chapter. The example uses disconnected data source access with full select, insert, update, and delete capabilities that can be either committed or rolled back.

A good portion of the code (which you can find in the Downloads section of the Apress Web site, http://www.apress.com) is related to Win Forms and isn't included here. What you'll see in the example is the code that wasn't autogenerated by Visual Studio .NET. Figure 12-15 shows the final result of the example, from which you can build your own Win Form.

Figure 12-15: The Author Maintenance tool

Building the DataAdaptor

The first thing that you need to do is build the application's SqlDataAdaptor. Then you'll use the SqlDataAdaptor to place data in the DataSet. Eight major steps (three of which are optional) are involved in building a SqlDataAdaptor and populating and maintaining a DataSet:

1. Create a SqlConnection.

2. Create a SqlDataAdaptor.

3. Implement a SelectCommand property.

4. Implement an InsertCommand property (optional).

5. Implement an UpdateCommand property (optional).

6. Implement a DeleteCommand property (optional).

7. Create a DataSet.

8. Populate (fill) the DataSet.

You build a SqlConnection for a disconnected database in the same way as you build a connected database:

 SqlConnection *connect = new SqlConnection(); #ifdef SQLAuth     // SQL Server authentication     connect->ConnectionString =         S"User ID=sa; Password=;"         S"Data Source=(local); Initial Catalog=DCV_DB;"; #else     // Windows Integrated Security     connect->ConnectionString =         S"Persist Security Info=False; Integrated Security=SSPI;"         S"Data Source=(local); Initial Catalog=DCV_DB;"; #endif 

Creating the SqlDataAdapter is a simple constructor call. You probably want to also add the primary key information. This ensures that incoming records that match existing records are updated instead of appended:

 dAdapt = new SqlDataAdapter(); dAdapt->MissingSchemaAction = MissingSchemaAction::AddWithKey; 

The SelectCommand is the SQL command that will be used to populate the DataSet. It can be as complex or as simple as you like. The implementation of the SelectCommand requires a standard SqlCommand like the one you created earlier with connected access. Notice that the constructor takes the SQL command and the data source connection:

 dAdapt->SelectCommand =     new SqlCommand(S"SELECT AuthorID, LastName, FirstName"                     S"FROM Authors", connect); 

The InsertCommand is the SQL command that will be executed to insert added DataSet rows back into the data source. The implementation of this property is a little tricky, as it requires parameters to be passed to the command. The Add() method to the Parameters property is similar to what you have seen previously, except it has one additional parameter and the size parameter is mandatory, even if it is obvious, as in the case of Int. The additional property is the name of the column that the data will be extracted from:

 // Implement Insert command dAdapt->InsertCommand =     new SqlCommand(S"INSERT INTO Authors (LastName, FirstName) "                      S"VALUES (@LastName, @FirstName)", connect); // Add parameters dAdapt->InsertCommand->Parameters->Add(S"@LastName", SqlDbType::VarChar, 50,                                            S"LastName"); dAdapt->InsertCommand->Parameters->Add(S"@FirstName", SqlDbType::VarChar, 50,                                            S"FirstName"); 

The UpdateCommand is the SQL command that will be executed to update rows in the data source that have been modified within the DataSet. The code does not contain anything new:

 dAdapt->UpdateCommand =     new SqlCommand(S"UPDATE Authors SET "                      S"LastName = @LastName, FirstName = @FirstName, "                      S"WHERE AtithorID = @AuthorID = @AuthorID", connect); dAdapt->UpdateCommand->Parameters->Add(S"@LastName", SqlDbType::VarChar, 50,                                            S"LastName"); dAdapt->UpdateCommand->Parameters->Add(S"@FirstName", SqlDbType::VarChar, 50,                                            S"FirstName"); dAdapt->UpdateCommand->Parameters->Add(S"@AuthorID", SqlDbType::Int, 4,                                            S"AuthorID"); 

In the preceding WHERE clause I use the key AuthorID, which is an autogenerated column that can't be changed, to find the row to update. This simplifies things because if the key used to find the row to update can be changed during the update process, then when it's changed the WHERE clause won't be able to find the right row due to the changed key not matching the original key in the database.

So, are you stuck with only being able to use unchangeable keys? Fortunately, the answer is no. When changed, DataRows store their original values so that they can be accessed for this exact reason (they can be used for rolling back changes as well). Let's pretend you can update AuthorID. Here is the code that needs to be changed:

 dAdapt->UpdateCommand =     new SqlCommand(S"UPDATE Authors SET "                     S"LastName = @LastName, FirstName = @FirstName, "                     S"AuthorID = @AuthorID "                     S"WHERE AuthorID = @OldAuthorID", connect); //...all the parameters plus dAdapt->UpdateCommand->Parameters->Add(S"@OldAuthorID", SqlDbType::Int, 4,              S"AuthorID")->SourceVersion = DataRowVersion::Original; 

The DeleteCommand is the SQL command that will be executed when a DataRow is removed from the DataSet, which needs to be deleted now from the data source. Nothing new to explore here in the code:

 dAdapt->DeleteCommand =     new SqlCommand(S"DELETE FROM Authors "                     S"WHERE AuthorID = @AuthorID", connect); dAdapt->DeleteCommand->Parameters->Add(S"@AuthorID", SqlDbType::Int, 4,                                            S"AuthorID"); 

You create a DataSet with a simple constructor. To fill the DataSet, you call the SqlDataAdapter class's Fill() method. The Fill() method takes two parameters: a pointer to the DataSet and the name of the data source table that you will be filling the DataSet with:

 dSet = new DataSet(); dAdapt->Fill(dSet, S"Authors"); 

Selecting Rows

You have many ways of selecting records from the DataSet. A common way of getting all the rows from a table is to use the DataRow collection found in the Rows property of the table and then enumerate through the collection. You populate the list box doing exactly that:

 DataTable *dt = dSet->Tables->Item["Authors"]; if (dt == 0)     throw new Exception(S"No Authors Table"); IEnumerator *Enum = dt->Rows->GetEnumerator(); while(Enum->MoveNext()) {     DataRow *row = dynamic_cast<DataRow*>(Enum->Current);     lbAuthors->Items->Add(ListBoxItem(row)); } 

As you can see in the ListBoxItem() method, to grab the columns, you use the Item indexed property of the DataRow:

 String *ListBoxItem(DataRow *row) {     return String::Format(S"{0} {1} {2}",         row->Item[S"AuthorID"],         row->Item[S"FirstName"],         row->Item[S"LastName"]); } 

A way of getting a specific set of DataRows from a DataTable is by using the DataTable's Select() method. The method takes as a parameter a filter of the primary key:

 DataRow *row[] =     dt->Select(String::Format(S"AuthorID={0}", __box(CurrentAuthorID))); 

You will see this code implemented later in updating and deleting rows.

Inserting Rows

Inserting a new row or, in this case, a new author is done by updating the text boxes with the information about the author and then clicking the Add button.

A good portion of the following code consists of validating, updating the list box, and cleaning up for text boxes. The actual ADO.NET-related code simply creates a new row, updates the columns with the information in the list boxes, and adds the row to the DataTable.

Notice that the actual insertion of the row into the data source with the Update() method is not found in this method. The reason for this is that I want to be able to commit or roll back all changes at one time using the Commit and Rollback buttons. Thus, the Update() method only occurs in the Commit button event. When the Update() method finally gets called, the UpdateCommand (which was coded previously) will get executed:

 void bnAdd_Click(Object *sender, System::EventArgs *e) {     // Make sure the text boxes are populated     if (tbFirstName->Text->Trim()->Length == 0 ||         tbLastName->Text->Trim()->Length == 0)         return;     // Create a new row in the DataTable     DataTable *dt = dSet->Tables->Item["Authors"];     DataRow *row = dt->NewRow();     // Update the columns with the new author information     row->Item[S"FirstName"] = tbFirstName->Text;     row->Item[S"LastName"] = tbLastName->Text;     // Add the row to the Rows collection     dt->Rows->Add(row);     // Add the new row to the list box     lbAuthors->Items->Add(ListBoxItem(row));     // blank out the text boxes     tbFirstName->Text = S"";     tbLastName->Text = S""; } 

Updating Rows

Updating an author row is handled when you select a row out of the list box, update the text boxes, and finally click the Update button.

The ADO.NET-related code to update the author requires that you first select the row to be updated using the DataTable class's Select() method. Once you have the row, you update the author information in the row columns. Like when you inserted a row, the Update() method does not get called until the Commit button is clicked, but when the Update() method finally gets called, the UpdateCommand ends up being executed:

 void bnUpdate_Click(Object *sender, System::EventArgs *e) {     // make sure we have a selected author from the listbox     if (CurrentAuthorID < 0)         return;     // Select the author using its AuthorID     DataTable *dt = dSet->Tables->Item["Authors"];     DataRow *row[] =         dt->Select(String::Format(S"AuthorID={0}", __box(CurrentAuthorID)));     // Since we know that AuthorID is unique only one row will be returned     // Update the row with the text box information     row[0]->Item[S"FirstName"] = tbFirstName->Text;     row[0]->Item[S"LastName"] = tbLastName->Text;     // Update listbox     lbAuthors->Items->Insert(lbAuthors->SelectedIndex, ListBoxItem(row[0]));     lbAuthors->Items->RemoveAt(lbAuthors->SelectedIndex); } 

Deleting Rows

Deletion of an author DataRow happens when you click a row in the list box and then click the Delete button.

The code to handle deleting a row is a little tricky, as it requires the use of transactional access to the DataSet. First, you need to select the row. Then you call its Delete() method. Deleting a record in the DataSet does not actually occur until the change is accepted. At this point only, a flag is set in the DataRow.

Also, like inserting and updating, the actual updating of the database does not occur until the Update() method is called when the Commit button is clicked. Ultimately, when the Update() method is called, the DeleteCommand (built previously) will be executed:

 void bnDelete_Click(Object *sender, System::EventArgs *e) {     // make sure we have a selected author from the listbox     if (CurrentAuthorID < 0)         return;     // Select the author using its AuthorID     DataTable *dt = dSet->Tables->Item["Authors"];     DataRow *row[] =         dt->Select(String::Format(S"AuthorID={0}", __box(CurrentAuthorID)));     // Since we know that AuthorID is unique only one row will be returned     // Delete the row     row[0]->Delete();     // all went well, delete the row from list box     lbAuthors->Items->RemoveAt(lbAuthors->SelectedIndex); } 

Committing and Rolling Back Changed Rows

You commit all author DataRows changed when you click the Commit button.

Because a DataSet is disconnected from the database, anything that you do to it will not get reflected in the actual database until you force an update using the Update() method. Because this is the case, it is really a simple matter to either commit or roll back any changes that you have made to the DataSet.

To commit the changes to the database, simply call the Update() method, which will walk through the DataSet and update any changed records in its corresponding database record. Depending on the type of change, the appropriate SQL command (insert, update, or delete) will be executed. To commit the changes to the DataSet, you need to call the AcceptChanges() method, which will cause the DataSet to accept all changes that were made to it:

 dAdapt->Update(dSet, S"Authors"); dSet->AcceptChanges(); 

To roll back any changes, simply don't call the Update() method, and call the RejectChanges() method to delete all changes in the DataSet that you have made since you last committed:

 dSet->RejectChanges();