Visual Studio Support and Typed DataSet Objects

Visual Studio Support and Typed DataSet Objects

In the two previous examples, I’ve written my own code for creating the various objects that I’ve needed for my data operations, such as the Connection and the Adapter. I’ve also had to write code for setting their properties, such as the connection string parameters in the Connection object and the query string parameters in the Adapter object. As any developer who’s struggled with connection strings knows, this can become painful. I don’t usually have to write code that creates buttons on a form or sets their properties; instead, Visual Studio provides an editor that generates that code for me and saves me lots of time. I’d really like some of that support for writing my database operation code, and Visual Studio gives it to me.

Visual Studio provides good editor support for writing data applications.

Besides the Connection and Adapter objects, I’d also like some help with DataSets. Working with a DataSet object as shown in the previous example is useful, but it’s still somewhat unwieldy because you have to plug in strings to specify the names of tables and columns. Some programming cases require this flexibility, such as a generic data browsing tool that allows a human user to type in any sort of query that occurs to him. But the majority of data access programs perform the same operations on the same data sources over and over and over again—think of the concert ticket application, for example. In cases like these, it doesn’t make sense to require the programmer to pass a string name to identify a table or column. The programmer has to look up the name in a manual so that she knows which one to use and then make sure she types it in correctly every time—that she hasn’t transposed a key and typed “Auhtors,” for example. Mistakes like this are easy to make because raw DataSets don’t have development-time support to make sure that you type in the correct string. They are also difficult to debug because your eye isn’t good at picking out close misspellings. It may not sound like a terrible problem, but if we could prevent it, we’d save some programmer time, some testing time, and probably some service calls. If you don’t want those savings, send yours to me, OK?

Working with standard DataSet objects could use some development time support.

What we’d really like is a DataSet object that’s tailored to the particular data that we expect to receive from a specific operation. It would have table and column names already wired into it as hard-coded variables. It would allow IntelliSense to show these names during programming so that we wouldn’t have to reach for the paper manual. We wouldn’t have to worry about misspellings because the compiler would catch us if we somehow ignored IntelliSense and got a name wrong. And we’d like good development tool support for generating them.

We want a dedicated DataSet class tailored to the results of a specific data operation.

It turns out that all of our wishes have been granted by .NET and Visual Studio. Now that we’ve seen the nuts and bolts of ADO.NET, I’ll show you the tools that make it easier. I’ve written a sample program that demonstrates it.

Visual Studio .NET supports developers writing data applications by providing its Server Explorer, shown in Figure 6-10. Server Explorer shows the various elements on a server for which it can generate .NET wrapper class objects, such as message queues and performance counters. The most interesting part of Server Explorer for our purposes is that it allows us to see the contents of our local SQL Server installation down to the table level. (It will actually go down to the individual column level, but I’m not using that for this example.)

A database programming example demonstrating intelligent tool support starts here.

Figure 6-10: Server Explorer within the Visual Studio .NET environment.

When I click on a table (in this case, authors) and drag it onto my design surface, Visual Studio generates SqlConnection and SqlDataAdapter objects and sets their parameters to the proper values for accessing the selected table. For example, the SqlDataAdapter is set to use the created connection, and has appropriate SQL commands added, as shown in Figure 6-11. As with all objects generated by Visual Studio, the code for creating them is placed in the InitializeComponent method of the container, in this case my XML Web service. I haven’t gained any run-time performance advantage by setting up my connection and adapter objects this way. In fact, I’ve probably lost a little because Visual Studio generates the Insert and Delete commands that this example doesn’t use. But it saves a whole lot of developer time and prevents errors, which means it’s usually a good trade-off. And I could manually remove the unneeded pieces if I really cared.

Server Explorer in Visual Studio will automatically generate the correct SqlConnection and SqlDataAdapter objects for accessing a database.

Figure 6-11: SQLDataAdapter object properties.

Tips from the Trenches

My clients report that the overhead of creating the unneeded pieces within these objects is not very high. It doesn’t perform any external communication with the database, it’s just allocating local memory and setting its values. Especially when used in conjunction with an XML Web Service as shown, there’s so much other stuff happening that this overhead gets lost in the noise. However, if you find that it’s taking more time than you can afford, you will probably still find it handy to use the designer to generate the code and then remove or modify the pieces that you don’t care about in any particular method.

Now that I have my connection and adapter set up nicely, it’s time to do something about my DataSet. ADO.NET provides the typed DataSet class. This is a custom class, derived from System.Data.DataSet, that provides named member variables for each specific table, row, and column. If we know at programming time which tables and columns a data set will contain, we can use utility programs to generate a typed DataSet class. This feature might not sound like a big deal, and I didn’t think it would matter much until I tried it, but now I’m hooked. As long as you know during program development which data queries you are going to want to make, and you usually will, you won’t want to program any other way. I’ve rewritten the authors client example from the previous section to use a typed DataSet object. This approach was easy to generate and made my programming somewhat easier to accomplish and somewhat harder to get wrong. The cost is a small amount of extra code, which you don’t have to write, in your application. Any time you can trade off larger code size for faster and better programming, you don’t have an economic choice.

ADO.NET supports typed DataSet classes.

You generate a typed DataSet by selecting Generate Dataset from the Data menu of Visual Studio’s main menu. (You can also generate it with the command-line utility XSD.exe, which requires an XML schema that describes your data set.) Visual Studio pops up the dialog box shown in Figure 6-12, asking for the name of the new class and the table that you want it to match. You make your selections and Visual Studio generates the code for the new class. In Class View, shown in Figure 6-13, you can see that the new class contains strongly typed classes to represent the table (authorsDataTable) and the row within the table (authorsRow).

You generate a typed DataSet class using Visual Studio’s wizards.

Figure 6-12: Generating a DataSet in Visual Studio .NET.

Figure 6-13: Class View showing strongly typed classes.

I rewrote my XML Web service to take advantage of the new objects that it contains. Listing 6-7 shows the code of my XML Web service method. It’s much simpler because the connection, adapter, and typed data set have already been created. In my GetAuthors method, I simply tell the adapter to fill the data set and then return it. In my UpdateAuthors method, I simply tell the adapter to update the database with the new information. Again, I’m not saving any CPU cycles here; the objects are still being created exactly as if I had written the code myself, but I had to write much less code. When either method returns, ASP.NET automatically calls Dispose on the XML Web service object, which automatically disposes of all its components, including the connection and adapter.

Writing the database access code is much easier with the objects that Visual Studio has created.

Listing 6-7: XML Web service sample code for getting and updating author information.

<WebMethod()> Public Function GetAuthors() As AuthorsTypedDataSet ’ Connection, adapter, and dataset objects have been ’ added by designer instead of with our own code. ’ Fill DataSet object with data Me.SqlDataAdapter1.Fill(Me.AuthorsTypedDataSet1) ’ Return DataSet object to caller Return Me.AuthorsTypedDataSet1 End Function <WebMethod()> Public Function UpdateAuthors(ByVal ChangedItemsDS _ As System.Data.DataSet) As Integer ’ Connection, adapter, and dataset objects have been ’ added by designer instead of with our own code. ’ Call Update method on adapter, which makes updates in records Return Me.SqlDataAdapter1.Update(ChangedItemsDS) End Function

The client code, shown in Listing 6-8, is very similar to the previous example. You can see that instead of saying MyDataSet.Tables (“Authors”).Rows, I say MyDataSet.authors. To fetch an individual value, I say ThisAuthorRow.au_lname instead of ThisAuthorRow(“au_lname”). These differences may not sound like much, but they remove a common source of errors (misspelling the string) and save programmer time by allowing IntelliSense support, as shown in Figure 6-14. If you still don’t think it sounds useful, try using it for an hour and then give it up. You’ll change your mind very quickly. The rest of the sample gets similarly easier. If you know your query set at development time, you do not have an economic choice.

Writing the client code is much easier with the typed data set.

Listing 6-8: Client code for typed XML Web service sample.

Dim MyDataSet As localhost.AuthorsTypedDataSet 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 localhost.AuthorsTypedDataSet.authorsRow For Each ThisAuthorRow In MyDataSet.authors ’ Create my object that holds the authors 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

Figure 6-14: IntelliSense support in Visual Studio.