More Complex Example: Disconnected Operation

The previous example is very simple, therefore it only scratches the surface. It doesn’t show your own code reading data from a DataSet object; it doesn’t show marshaling data across machine boundaries with XML; and it doesn’t show disconnected operations, such as making changes to data and posting them back. So I’ve written a different sample, whose operation is shown in Figure 6-6, to demonstrate these features. Instead of using a browser to display data, I wrote a rich client using Windows Forms (see Chapter 5). The client uses an XML Web service (see Chapter 4) to fetch a data set from the server machine. The client allows a user to edit the results of the query and post the changes back to the underlying database through the XML Web service.

Figure 6-6: Operation of the DataSet sample program.

On the server, I’ve written a simple XML Web service that exposes the methods GetAuthors and UpdateAuthors. The first method’s code is shown in Listing 6-2, and it’s really quite simple.

A sample showing disconnected operations starts here.

Listing 6-2: The GetAuthors method.

<WebMethod()> Public Function GetAuthors() As Data.DataSet ’ Create Connection object Dim Connection As SqlConnection Connection = New _ SqlConnection("server=(local);uid=sa;pwd=‘‘;database=pubs") ’ Create DataAdapter object Dim Adapter As SqlDataAdapter Adapter = New _ SqlDataAdapter("select * from Authors", Connection) ’ Create empty DataSet object Dim DS As Data.DataSet DS = New Data.DataSet() ’ Fill DataSet object with data Adapter.Fill(DS, "Authors") ’ Return DataSet object to caller Return DS End Function 

When the client calls GetAuthors, the method creates a Connection object and a DataAdapter object and uses these to create a DataSet object, as shown in the previous example. I could easily have added additional parameters for the client to pass to the XML Web service that the service could use in performing the query—say, authors whose first name is “John”—but I didn’t want to complicate the example. The difference between this example and the previous one is that, instead of displaying the data set on a Web page for a human user, the XML Web service returns the DataSet object to the client program that calls it. This causes the DataSet object to be serialized into XML and transmitted over the wire to the client. You can see the DataSet object layout in XML by using the XML Web service’s built-in test capability, as shown in Figure 6-7.

The XML Web service method simply returns a DataSet object, which causes it to be transmitted in XML.

Figure 6-7: The XML layout for the DataSet object.

You can run the XML Web service through the sample client app. When the user clicks the Fill button, the app fetches the data set containing all the authors from the XML Web service, as shown in Figure 6-8. The code is shown in Listing 6-3.

Figure 6-8: Our sample client application.

Listing 6-3: Code from the application.

Dim MyDataSet As DataSet Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles Button1.Click ListBox1.Items.Clear() ’ Create proxy object for accessing Web service Dim Server As New localhost.Service1() ’ Fetch DataSet object from proxy MyDataSet = Server.GetAuthors ’ Populate initial list box with author’s names Dim ThisAuthorRow As DataRow For Each ThisAuthorRow In MyDataSet.Tables("Authors").Rows ’ Create my object that holds the author’s name and ’ the author’s data row Dim ThisGuy As New MyOwnListItem(ThisAuthorRow("au_lname") + _  ", " + ThisAuthorRow("au_fname"), ThisAuthorRow) ListBox1.Items.Add(ThisGuy) Next End Sub 

The client creates an object of the XML Web service class and calls the GetAuthors method, which returns the DataSet object as I’ve just described. When the client assigns this return value to a variable, it takes the XML stream sent by the server and rehydrates it into a functioning DataSet object. The DataSet object has been transmitted using XML and HTTP, which clearly illustrates the fact that it can be sent to any type of client, even a non-Microsoft system.

The client automatically rehydrates the XML into a DataSet object.

Once I’ve gotten the DataSet object from server to client, I want to access it on the client side. I fetch the table in the DataSet object that I know contains the records of authors by using the DataSet object’s Tables collection, passing the name of the table that I want to access, in this case Authors. This call returns an object of class System.Data.DataTable. This table contains the author records that I want, each represented by an object called System.Data.DataRow. I step through each record sequentially by accessing the collection called Rows in the DataTable object.

The DataSet object contains .NET properties representing tables and rows.

I’d like to get each author’s first name and last name, assemble them into a string, and display the string in the ListBox control. Getting the data from the DataSet object is easy. Each DataRow object contains a collection of columns that represent the fields in the database that actually contain individual values. I access a column by using its name, in this case au_lname and au_fname, as shown at the end of the code listing in Listing 6-3.

You access an individual column through its name in a DataRow object.

Since I want to enable the user to edit the DataRow later, I need to associate a DataRow with its line in the ListBox control. In Visual Basic 6.0, I’d have used the ListBox control’s ItemData property to hold an integer key identifying the row in a separate collection I’d have to somehow manage. But I can’t do that in .NET because the ItemData property has been removed. Instead, the ListBox control can hold a .NET object of any class, but it won’t hold two separate items (the string and the key) as it did before. The ListBox control displays the string returned by the object’s ToString method (described in Chapter 2). So to make this app work the way I wanted, I needed to roll my own class that contained all the information I wanted for each line of the ListBox control to hold. You’ll find that information in the class MyOwnListItem. It holds a DataRow object and a string, both of which it accepts in its constructor. The code is shown in Listing 6-4. It sounds complicated, but it really isn’t. It saves me having to manage my own collection of ListBox items, which is a net gain even if you use it in only one place. For each row, I create an object of this class, passing it the full name string I want to display in the ListBox control and the corresponding DataRow object.

ListBox controls now require a .NET object because the ItemData property has been removed.

Listing 6-4: Code from my own class MyOwnListItem.

Public Class MyOwnListItem Public m_FullName As String Public m_DataRow As Data.DataRow ’ Class constructor that accepts a name for display and a DataRow ’ to hold Public Sub New(ByVal FullName As String, _ ByVal MyDataRow As Data.DataRow) m_FullName = FullName m_DataRow = MyDataRow End Sub ’ Override System.Object.ToString. The displaying ListBox control ’ will call this method to get the string to display Public Overrides Function ToString() As String Return m_FullName End Function End Class

Now I want to edit an individual entry. When the user selects an entry from the ListBox and clicks Edit, I pop up a dialog box showing the status of that author’s contract, as shown in Figure 6-9. You can see the code in Listing 6-5.

Figure 6-9: The Edit Author’s Info dialog box.

Listing 6-5: Code allowing editing of author info.

 Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) _ Handles Button2.Click ’ Get selected author’s data row from ListBox control Dim SelectedListItem As MyOwnListItem SelectedListItem = ListBox1.Items(ListBox1.SelectedIndex) Dim AuthorsDataRow As Data.DataRow AuthorsDataRow = SelectedListItem.m_DataRow ’ Get state of author’s contract from data row Dim contract As Boolean contract = AuthorsDataRow("contract") ’ Set control in editing form according to current state ’ of author’s contract Dim EditForm As New Form2() EditForm.CheckBox1.Checked = contract ’ Set editing form’s text and show to user. ’ If user clicked OK, then change value in data row ’ and enable Update button If (EditForm.ShowDialog() = DialogResult.OK) Then AuthorsDataRow("contract") = EditForm.CheckBox1.Checked Button3.Enabled = True End If End Sub

I first fetch the DataRow object representing the user’s selection from the ListBox. (See how much easier it is than a separate collection?) I look at the DataRow object’s contract column and set the dialog box’s CheckBox control to the column’s value. If the user clicks OK, I fetch the state of the CheckBox control from the dialog box and set the value in the DataRow object’s contract column. You can see that I’m simply treating the contract column of a row like a standard variable.

You read and write columns in the DataRow object as if they were
simple variables.

When the user clicks Update, I need to send whatever changes he’s made back to the server to update the server’s master database tables. I could send the entire data set back to the server and let the server figure out which rows have changed, but this would be a waste of network bandwidth. It would be better to send only the changed rows. I can easily do this by using the method DataSet.GetChanges, which returns another DataSet object containing only the rows in the original data set to which changes have been made. I send this DataSet object back to the XML Web service using the service’s UpdateAuthors method. You can see the code for UpdateAuthors in Listing 6-6.

You can easily select only the changed rows to be sent back to the server for updating.

Listing 6-6: Updating author information.

<WebMethod()> Public Function UpdateAuthors( _ ByVal ChangedItemsDS As System.Data.DataSet) As Integer ’ Create new Connection object Dim Connection As SqlConnection Connection = New _ SqlConnection("server=(local);uid=sa;pwd=‘‘;database=pubs") ’ Create DataAdapter object Dim Adapter As SqlDataAdapter Adapter = New SqlDataAdapter() ’ Create and set properties of Command object Dim MyUpdateCommand As New _ Data.SqlClient.SqlCommand( _  "UPDATE Authors SET contract = @contract WHERE au_id = @au_id", _ Connection) Adapter.UpdateCommand = MyUpdateCommand Adapter.UpdateCommand.Parameters.Add("@contract", SqlDbType.Bit, _ 1, "contract") Adapter.UpdateCommand.Parameters.Add("@au_id", SqlDbType.VarChar, _ 11, "au_id") ’ Send update command to database via DataAdapter object, ’ specifying the changed records DataSet object. ’ The Update method returns an integer, which we return to ’ the client Return Adapter.Update(ChangedItemsDS, "Authors") End Function 

On the server side, my XML Web service catches the incoming DataSet object containing changes that the client has made. It creates a Connection object and a DataAdapter object as before. In this case, we are going to be updating data that’s already in the database, so we also need a Command object, which represents a command that you use to tell the database to do something. The common language runtime provides two classes of Command object, System.Data.OleDb.OleDbCommand, which is the generic Command object available to any OLE DB provider, and System.Data.SqlClient.SqlCommand, which is the version specific to SQL Server. You create one of these objects as shown, passing in its constructor the SQL string that you want executed. You then plug the Command object into the DataAdapter object by assigning it to the DataAdapter object’s UpdateCommand property. This assignment tells the DataAdapter object which SQL command to run when data is updated. You’ll see that I also have to add parameter objects to tell the command which variables map to which columns. (The DataAdapter object also contains InsertCommand and DeleteCommand properties, which accept a similar Command object used during inserts and deletes, respectively, but I don’t use these properties in this example.) Finally, I call the DataAdapter object’s Update method, telling it to take the update command and run it against the database, using the DataSet object that I received from the client. This call returns the number of rows updated, which I return to the client.

The DataAdapter object uses a Command object to make changes to the underlying database.

The loosely coupled nature of ADO.NET DataSet objects requires careful thought in database design. Since you don’t know how long a client is going to keep a DataSet object, you can’t afford to keep locks on all your data to prevent conflicts; you’d tie the system into knots very quickly. Instead, you can design your database to use some form of optimistic concurrency. If I had done that, my sample XML Web service would contain code that would check before saving updates to see whether the data it is saving had been changed by someone else in the interim—for example, by checking a timestamp column. If the data had been changed, the unsaved, edited values might be bad, so the XML Web service would throw an error back to the client, and the client would somehow inform the user of this and make the user do it again. We call this type of concurrency optimistic because we’re hoping that this somewhat painful process won’t happen very often. This approach works well in systems that experience low contention rates. For higher contention systems, such as buying tickets online to the latest Harry Belafonte concert (he’s still got his stuff, by the way, even at age 76), you might use compensating transactions—remove a specific pair of tickets from the theater database when the user first asks what’s available and then perform the opposite operation to put the tickets back in the pool if the user doesn’t buy them within ten minutes.

Design of databases used by ADO.NET needs to take into account its loosely coupled nature.

Tips from the Trenches

Most developers I know don’t like optimistic concurrency, but it can be very efficient if you do it right. The key is to ruthlessly pare away the potential situations in which contention can arise, which this example doesn’t even try to do. For example, a production app might again request the data record for an author when the user opens the editing dialog box to work on that author so that the user would be looking at the latest data. The app might automatically save the record back to the database when the user clicks OK so that the changes can be reflected immediately. These strategies greatly reduce the amount of time during which two users might be messing each other up. If your contention is low, it’s the way to go.