Data Access in Visual Basic .NET

In FoxPro, we saw how we could open a DBF and refer to its fields in the ControlSource property of controls on a form. This may be called a connected recordset.

Disconnected Data Access

In Visual Basic .NET, all recordsets are disconnected. You must create a cursor, bring the data into the cursor, and show it to the user . After any changes are made, you construct a command to send the data back to the source from whence it came. This is the case regardless of the data source. There are no discounts for local tables in Visual Basic .NET.

Visual Basic .NET uses datasets in the same way that we use cursors in FoxPro. You create a dataset, then call the Fill method of the associated DataAdapter to pour the data into the dataset. After any changes, you call a CommandBuilder object to create an UPDATE , INSERT , or DELETE command to be sent to the driver that actually updates the data source. It's the only way it works.

Connections

Connections are strings that tell the driver how to connect to a data source. Data sources can be OLEDB or ODBC, or they can be managed providers. Managed means "written in .NET," and for this reason they are more efficient (faster) than their OLEDB counterparts. ODBC drivers are just dreadful and are usually used only if there's nothing else available. There's a pretty good third-party market in ODBC drivers, and if you must use a data source supported by ODBC, you should look into them. Microsoft gives away several ODBC drivers for free, and as you might expect, their price is a good indication of their value.

A connection string to the Northwind database on SQL Server accessed from FoxPro was shown earlier in Listing 6.4. It's the same for Visual Basic .NET:

 Driver={SQL Server};Server=VAIO\VAIO;Database=Northwind;UID=sa;PWD=; 

However, you can also register a SQL Server database as an ODBC data source by running ODBCAD32.EXE from the command line or by clicking on the ODBC Data Sources icon in the Control Panel. The resulting dialog lets you select the driver, specify the database, and optionally provide a user ID and password. If the user ID and password aren't supplied in the connection definition, the user will have to specify them every time a connection is made. Experiment with this a few times and talk to whoever is paying for it before you decide how to proceed. It makes a difference.

In the Visual Studio IDE, there is a Server Explorer. You can open it by selecting View, Server Explorer from the menu, or by pressing Ctrl+Alt+S, whether a project or solution is open or not. The Server Explorer is shown in Figure 6.8.

Figure 6.8. The Server Explorer.

You can define connections in advance. Because a project will often deal with only two databases, the test version and the production version, this simplifies the process of switching between one and the other.

To create a new connection, right-click on Data Connections and select Add Connection. You'll see the Data Link Properties dialog. This is exactly the same dialog that is produced if you create an empty text file with the extension UDL, as we saw earlier.

Page 1 of the dialog is the Provider page. There are about 20 that have been installed on my laptop by various product installations that I've done. If you buy and install drivers from third parties, there will be a few more. Notice that there is a Microsoft OLE DB Visual FoxPro driver, so if you wondered whether Visual Basic .NET will support your FoxPro tables, now you know. Note that the FoxPro driver option supports either a DBC or a Free Tables Directory. Experiment with both and verify that your choice does what you want it to do.

If you select a provider that has password protection, you'll be asked to supply a password. For most applications, a single user ID and password combination for all users is perfectly adequate. You can go to the Enterprise Manager in the Start, Programs, SQL Server drop-down menu to add a user.

You can also add categories of users and give different rights to each, although the granularity of SQL Server database access may not be adequate for your requirements if you want to control access down to the pageframe or field level. However, if your purpose is to keep users from updating records that they're only supposed to read, producing an error message when they click Save is a pretty blunt instrument compared to simply disabling the Edit button to begin with if they don't have editing rights. Users appreciate subtlety.

Notice on the third page of the Data Link Properties dialog that you can limit access to read-only, read-write, read-write share, read-write exclusive, or write-only. Again, these are probably more than you need in most cases. The default Share Deny None is probably just what you need. It supports optimistic buffering, which means that you're responsible for ensuring that one user's changes don't overwrite another's.

After you save a connection, you can drag and drop it onto a form, and it will be used to connect to the data source. It's used either directly by a command object or by a data adapter.

Data Adapters

A DataAdapter is a mechanism that opens a connection and executes a SQL SELECT statement. It also automatically creates UPDATE , INSERT , and DELETE statements if a unique key is specified in the SELECT statement. That's why it's so terribly important in the SQL world to have a single unique key. Many FoxPro developers have developed the nasty habit of using compound keys, for example, InvoiceNum + LineNum for a detail record of an invoice. That's a spectacularly bad idea even in FoxPro; in SQL Server, it's unthinkable. So before you try to use a DataAdapter to connect to your data, add a unique integer key or a GUID to each of your data tables. I'll wait right here.

Good, you're back. Let's continue.

Using a DataAdapter with a FoxPro Table

Create a connection to the Customers table. If you didn't download the code, just copy the Customers table that comes with the FoxPro Northwind samples to a directory where you can play with the data without doing any permanent damage. Be sure to use COPY TO Customers WITH CDX because without an index, the DataAdapter doesn't have a clue, as you'll see. Create a connection to the directory as a Free Table directory in the Solution Explorer.

Next , open the Visual Basic .NET IDE and select File, New Project from the menu. Choose Visual Basic, Windows Application. Make sure that the project is going where you want it to go, and note that the project name is also the name of a new directory that Visual Studio is going to create for you. Visual Studio will create a project with five standard .NET namespace references needed to build a forms project: System , System.Data , System.Drawing , System.Windows.Forms , and System.XML . Remember that last one. It's there because forms need data, and XML spells data in .NET. It also adds an AssemblyInfo.vb file (like a project information file) and Form1.vb .

That first form is usually your Main form, which will contain your logo and your application's main menu. However, you can also build an application consisting of just a single form, and that's what we'll do here. Use F4 to open the Properties window and change the Text property to "My customers" .

Next, open the Solution Explorer and add the connection you just created by dragging and dropping it on the form. Visual Basic .NET will ask you if you want to add a user ID and password. For now, ignore it.

Next, open the Toolbox with Ctrl+Alt+X and add an OleDBDataAdapter from the Data toolbox category. The resulting wizard asks what connection to use, and defaults to the one we had added previously Next, the wizard needs to know from which fields, and from which tables you want to generate data (see Figure 6.9).

Figure 6.9. Adding a DataAdapter ”specifying a connection.

The wizard will offer to use SQL statements, create new stored procedures, or use existing stored procedures. You'd be surprised how many shops don't build the screens until some database guy has approved the SELECT , INSERT , UPDATE , and DELETE code and written procedures for them in advance. Select Use SQL Statements and click the Next button.

RANT

<rant>The advantage in stored procedures is that after you've built them, migrating to another vendor's SQL is harder. Uh, sorry, that's the advantage for the vendor . The advantage for us is ”uh, sorry, there isn't any.</rant>

In Figure 6.10, the DataAdapter Configuration Wizard is looking for a SELECT statement that returns the records you want.

Figure 6.10. Enter a SELECT statement for the DataAdapter .

If you want all customers from California, type this:

 SELECT * FROM Customers WHERE Region = 'CA' 

SQL can use the SELECT statement and the unique index values to generate the code for the INSERT , UPDATE , and DELETE commands. However, if you only want a SELECT statement, click on the Advanced Options button and uncheck the Generate Insert, Update and Delete Statements check box. Also, if you don't want the code-generated SQL statement to verify that no fields have been changed since the SELECT , uncheck the Use Optimistic Concurrency check box. It improves performance, and in many cases it's not necessary. You be the judge of the probability that two people will try to change the same record on two workstations at the same exact instant. It's usually infinitesimal .

If you want to add a parameter to a query, write the SELECT statement as follows :

 SELECT * FROM Customers WHERE (CustomerID=?) 

This will cause the IDE to build a parameterized query. To execute it, you'll need to supply a value for the parameter in code before executing the Fill command:

 OledbDataAdapter1.SelectCommand.Parameters(0).Value = 1 

If this looks a lot like a local view in FoxPro, it should. When you open a view in FoxPro, you're executing a SELECT command. When you close the view, the view itself uses the SELECT command and the unique index key to build and execute INSERT , UPDATE , and DELETE commands as needed to update the source table. That's exactly how the DataAdapter works. You just get to see all of the details.

Right-click on the DataAdapter and select Generate Dataset. Select New, and provide the name dsCustomers . This builds an XSD file describing the schema and a .vb file containing property procedures for all fields. We'll look at this in more detail a few pages ahead, under the heading "Datasets." For now, we'll just build one and use it.

Finally, add a grid to the form. Open the Toolbox using Ctrl+Alt+X, click on the Windows Forms controls section heading, and drag a DataGrid to the form. Open the Properties sheet, click on DataSource, and from the pull-down list select dsCustomers1.Customers . You can also use the Properties window to specify the dataset name dsCustomers1 as the DataSource , and the table name Customers as the DataMember , for the data grid.

This is necessary because a dataset can contain several tables, and we just want one for the grid. (If you like, select dsCustomers1 and you'll see a little plus sign, which you have to expand manually to pick the table and display the table in the grid. Grids know how to work with datasets, even if they have multiple tables or hierarchical datasets.)

But we're not done. We have to write some code ”one line of code. Double-click anywhere on the form except the grid, and the code window will open with a first and last line of a Form_Load event wired to the Load event of the form via a Handles clause. Type in the following line of code:

 Me.OleDbDataAdapter1.Fill(DsCustomers1) 

You don't need the "Me.", but it brings up IntelliSense and saves you some typing. Use the Tab key to select the current suggestion and move to the end of the selected text; or, use a period to do the same thing and add a period at the end. The Fill command of the DataAdapter was built when we constructed the SELECT command, and the dsCustomers1 dataset was constructed from the DataAdapter 's SELECT command, so they're guaranteed to be conformable.

Press F5 to run the application. That's one line of code. In FoxPro, it also takes one line of code ”a USE statement in the LOAD event of the form. So far, it's a close race.

NOTE

I've had a few problems with the FoxPro OLE DB driver in Visual Basic .NET. During the writing of this chapter, the DataAdapter Configuration Wizard began inserting double quotes around my table name, so that not even the SELECT command would work. And many times it failed to generate UPDATE and DELETE commands for unspecified reasons. I had no such problem with the SQL Server or Access drivers.

Generated Code for the DataAdapter and Dataset

You've probably already done this, but open up the code for the form. You'll see a little box that contains the text

 Windows Form Designer Generated Code 

Click on the plus sign to the left of it, and scroll through the code. You'll see a block of code declaring a series of objects named SQLDataAdapter1 , SQLSelectCommand1 , SQLInsertCommand1 , SQLUpdateCommand1 , SQLDeleteCommand1 , and SQLConnection1 . Each of these is defined in the generated code, including the command that SQL Server will need to do its magic. Each field is generated as a parameter, which is filled in when the function is called. Listing 6.10 is an example. The generated code produces long lines, so they look pretty bad on the screen and on the page. Thankfully, we seldom look at this code.

Listing 6.10. Generated SQLUpdateCommand Code

 Me.SqlUpdateCommand1.CommandText = "UPDATE Customers SET CustomerID = @Customer ID, CompanyName = @CompanyName, Contac" & _ "tName = @ContactName, ContactTitle = @ContactTitle, Address = @Address, City =  @" & _ "City, Region = @Region, PostalCode = @PostalCode, Country = @Country, Phone = @P" & _ "hone, Fax = @Fax WHERE (CustomerID = @Original_CustomerID) AND (Address = @Ori gi" & _ "nal_Address OR @Original_Address IS NULL AND Address IS NULL) AND (City = @Ori gi" & _ "nal_City OR @Original_City IS NULL AND City IS NULL) AND (CompanyName = @Origin a" & _ "l_CompanyName) AND (ContactName = @Original_ContactName OR @Original_ContactNa me" & _ " IS NULL AND ContactName IS NULL) AND (ContactTitle = @Original_ContactTitle O R " & _ "@Original_ContactTitle IS NULL AND ContactTitle IS NULL) AND (Country = @Origi na" & _ "l_Country OR @Original_Country IS NULL AND Country IS NULL) AND (Fax = @Origin al" & _ "_Fax OR @Original_Fax IS NULL AND Fax IS NULL) AND (Phone = @Original_Phone OR  @" & _ "Original_Phone IS NULL AND Phone IS NULL) AND (PostalCode = @Original_PostalCo de" & _ " OR @Original_PostalCode IS NULL AND PostalCode IS NULL) AND (Region = @Origin al" & _ "_Region OR @Original_Region IS NULL AND Region IS NULL); SELECT CustomerID, Co mp" & _ "anyName, ContactName, ContactTitle, Address, City, Region, PostalCode, Country , " & _ "Phone, Fax FROM Customers WHERE (CustomerID = @CustomerID)" 

That's one line of code . I really didn't need to see this. In Visual FoxPro, we're blissfully unaware of what's needed to make things happen; Visual Basic .NET shows you everything .

There's also a DSCustomers1 object based on DSCustomers . What is DSCustomers ? It's a generated typed dataset, located in another generated code file, as you'll see shortly.

Datasets

A dataset is a cursor. Want to see how it works? Open a new Visual Basic blank project, and then open the Solution Explorer using Ctrl+Alt+L, right-click on the project name, and select Add New Item. Select Dataset from the available selections (others include XML file and XML Schema and others). When the designer opens, use Ctrl+Alt+S to open the Server Explorer, and then pick any connection, expand it to reveal its tables, expand Tables, and drag any table name to the designer surface. You can view the result either as XML or as a table.

Using the DataAdapter to Build a Dataset

To build a dataset, click on the Data tab in the Toolbox and drag an OLEDBDataAdapter (or a SQLDataAdapter ) to a form surface. There are small differences, but essentially the SQLDataAdapter works only with SQL Server, whereas the OLEDBDataAdapter works with SQL Server plus many other data sources that have OLEDB drivers. The SQLDataAdapter has less overhead, and is therefore faster and more efficient.

The DataAdapter Configuration wizard asks you to supply a SQL SELECT statement to determine where to get the data. After it's configured, you can use it to add a dataset to your project. This named dataset will be used as a cursor. The DataAdapter will fill it with data using the Fill method and present it to your form; later, when you save data, the changed rows can be sent back through the DataAdapter to the data source to update it.

When the DataAdapter has been configured, right-click on it and select Generate Dataset from the context menu. Change the dataset name to the prefix "ds" plus the name of the table and click OK. The resulting wizard will ask you for a SELECT statement, and will build the container in which the data will be stored in an XML format with the extension .xsd . XSD means "I'm a dataset." It's an empty cursor. Actually, because a dataset can hold multiple tables, it's more like "one or more cursors." But we'll keep it simple for now.

Relationship Between XML, Datasets, and XML Schemas

XML is not a dataset, and a dataset is not a schema. And even though a dataset can start with a schema, the first half- dozen lines of a dataset and an XML schema file created from the same SQL table are quite different. And XML is generic, while a dataset is a particular structure in XML.

Here's how it works: XML is a technology for storing things. A dataset is one or more tables stored in XML format, with (optionally) one or more schemas. A schema describes a data table in an XML file, either inline or in a separate XML file. A dataset can contain multiple schemas and multiple tables. I hope that proves easy to remember. Each piece of the puzzle has its purpose.

Typed Datasets

Typed datasets in Visual Basic .NET are defined by

• An XML schema that describes the tables, columns , keys, and relationships in the dataset

• A class file (written as a Visual Basic class) that inherits from System.Data.DataSet and implements properties and methods that provide access to a dataset with the specified schema

Use Ctrl+Alt+L to open the Solution Explorer, and then click on the Show All Files icon at the top of the window. Notice that the dataset you just created has a plus sign, indicating that there's more below it. Click on the plus sign to expand the tree, and you'll see a file with the same name and a .vb extension. Double-click on it to open the file. It's a Visual Basic class that describes the tables in the dataset.

Exploring the Typed Dataset

Use the View, Class View menu option to open the Class View window. Select the DsCustomers.vb class that was generated automatically by the Generate Dataset Wizard from the DataAdapter object, and you're in for a shock . There are dozens and dozens of properties, events, and methods in the generated code.

Most of them are prototypes ; that is, you can add code wherever you want to. For example, for each field there's a SetFaxNull subroutine. The Customers table has a Fax column. In the database, Nulls are permitted in this column. VB hates nulls. In fact, it will crash if you return one. So, the typed dataset has provided a routine where you can write code to specify what you want to return as a value if you add a record and don't specify a value for the Fax column. The generated routine inserts DBNull , but you might want to insert "N/A". The SetFaxNull subroutine is where you would do so.

Go a little further and you begin to see how this works. The IDE has generated a class definition called a CustomersDataTable , which inherits from the .NET DataTable class. That means that it comes with all of the properties, events, and methods of the DataTable class, which you probably should learn about. The declaration is followed by a declaration of a private variable called column&ColumnName (to use FoxPro syntax ” columnFax would be created for the Fax column in the table, for example) as DataColumn for each of the columns in your original data table. These are called fields, and for the first time, the name makes sense in this context.

A property procedure called Count is added, which returns the value of Me.Rows.Count . Then follow a raft of property procedures named &Field.Column (again using FoxPro syntax ”an example would be FaxColumn ), which is supplied by returning the value stored in Classname.ColumnFax , the class's corresponding field name. Visual Basic .NET creates one field (that is, one local variable) for each column, then uses these fields to store the values that are actually available to the user as properties via property procedures named with the word "Column" as a suffix for the property procedures. Confused yet?

It gets more complicated. The last declaration in this class definition is tableCustomers , which is actually an object derived from CustomersDataTable . What is returned is the tableCustomers object, not the CustomersDataTable object. When you refer to dsCustomers , you're actually referring to an object based on the tableCustomers object, which is in turn based on the CustomersDataTable class, which is based on the DataTable class.

When I drive to the store to buy a loaf of bread, I'm exploding gasoline thousands of times per minute. I know this is true, and in fact it would be pretty dramatic to be down inside the engine watching all of it happen. But I just want a loaf of bread, and I don't care about the exploding gasoline.

I feel exactly, precisely the same way about all of this stuff. The less I know about it, the better. All you need to know is that if you create a dataset, you can bind the Text property of your onscreen controls to Dataset.FieldName and it will work.

The Data Form Wizard

When you right-click on the Windows form project and select Add, Add New Item, one of the options is Data Form Wizard, as shown in Figure 6.11.

Figure 6.11. Double-click to Select the Data Form Wizard project.

When you select Data Form Wizard, the screen shown in Figure 6.12 appears. Click on the Next button to proceed.

Figure 6.12. The Data Form Wizard.

If I've already created a typed dataset, I can use it in the form that the Data Form Wizard is building, as shown in Figure 6.13. Typed datasets belong to the project, not to a form, and can be shared by several forms.

Figure 6.13. Selecting an existing typed dataset.

The wizard will include a Load and (optionally) an Update button if you check the check box (see Figure 6.14, about halfway down the dialog form).

Figure 6.14. Setting options for the Data Form Wizard.

Finally, the customer table and all of its fields are selected by default. Click the Next button to go to the next step (see Figure 6.15).

Figure 6.15. Selecting the table and all fields.

The next screen (see Figure 6.16) is the one that I was waiting for. Do I want to present data to my users as a grid (no) or as a record (yes). I don't even know why they offer the first option, although I suppose it's easy to code. But editing records in a grid is almost never a desirable technique. So, pick Single Record in Individual Controls and click Finish. In about three seconds, you've got screen! Forms like the one shown in Figure 6.16 are where I spend most of my day, and this one was written for me in a few seconds. All I have to do is go in and tweak it.

Figure 6.16. Specifying the screen type in the Data Form Wizard.

Before we can run this, there's one thing we have to do. I started this project with another form, then added this one. When you press F5 to compile and run a project, you have to tell the project which is the startup form ”like FoxPro's SET MAIN selection on the Project menu popup. To do this, click on the solution, then on the project, to make sure it's selected . Next, right-click on the project and select Properties from the context menu. You should see the screen shown in Figure 6.17. Pick the form that you want to use as the startup for the executable.

Figure 6.17. Setting the Startup form.

Press F5 to run the application. As shown in Figure 6.18, you can indeed move from one record to another, make changes and save them, and add and delete records.

Figure 6.18. The generated data form in action.

However, when I click the Load button, nothing happens! It turns out that because I told the wizard to use my existing dataset, it left me with the responsibility of loading the dataset. No big deal, right?

The Generated Code

The code for this generated screen is 360 lines long. It returns all of the records in the table from SQL, then uses the VB equivalent of SKIP to move backward and forward through the tables. It also assumes that my users can just start typing in any field whenever they want, and that they'll know that they need to "update" before they can go to another record ”their wrist will be slapped with an error message if they don't. So it has little to commend it as a database application. But it works, and it only took a few seconds.

Here's the code generated by the Data Form Wizard:

 Private Sub btnCancel_Click( _  ByVal sender As System.Object, ByVal e As System.EventArgs) _  Handles btnCancel.Click   Me.BindingContext(objdsCustomers, "Customers").CancelCurrentEdit()   Me.objdsCustomers_PositionChanged() End Sub Private Sub btnDelete_Click( _  ByVal sender As System.Object, ByVal e As System.EventArgs) _   Handles btnDelete.Click   If (Me.BindingContext(objdsCustomers, "Customers").Count > 0) Then     Me.BindingContext(objdsCustomers, "Customers").RemoveAt( _     Me.BindingContext(objdsCustomers, "Customers").Position)     Me.objdsCustomers_PositionChanged()   End If End Sub Private Sub btnAdd_Click( _  ByVal sender As System.Object, ByVal e As System.EventArgs) _  Handles btnAdd.Click Try 'Clear out the current edits   Me.BindingContext(objdsCustomers, "Customers").EndCurrentEdit()   Me.BindingContext(objdsCustomers, "Customers").AddNew()   Catch eEndEdit As System.Exception    System.Windows.Forms.MessageBox.Show(eEndEdit.Message)    End Try   Me.objdsCustomers_PositionChanged() End Sub Private Sub btnNavFirst_Click( _  ByVal sender As System.Object, ByVal e As System.EventArgs) _  Handles btnNavFirst.Click   Me.BindingContext(objdsCustomers, "Customers").Position = 0   Me.objdsCustomers_PositionChanged() End Sub Private Sub btnLast_Click( +  ByVal sender As System.Object, ByVal e As System.EventArgs) _  Handles btnLast.Click   Me.BindingContext(objdsCustomers, "Customers").Position = _  (Me.objdsCustomers.Tables("Customers").Rows.Count - 1)   Me.objdsCustomers_PositionChanged() End Sub Private Sub btnNavPrev_Click( +  ByVal sender As System.Object, ByVal e As System.EventArgs) _  Handles btnNavPrev.Click   Me.BindingContext(objdsCustomers, "Customers").Position = _  (Me.BindingContext(objdsCustomers, "Customers").Position - 1)   Me.objdsCustomers_PositionChanged() End Sub Private Sub btnNavNext_Click( _  ByVal sender As System.Object, ByVal e As System.EventArgs) _  Handles btnNavNext.Click   Me.BindingContext(objdsCustomers, "Customers").Position = _  (Me.BindingContext(objdsCustomers, "Customers").Position + 1)   Me.objdsCustomers_PositionChanged() End Sub Private Sub objdsCustomers_PositionChanged()   Me.lblNavLocation.Text = (((Me.BindingContext(objdsCustomers,   "Customers").Position + 1).ToString _   + " of  ") + Me.BindingContext(objdsCustomers, "Customers").Count.ToString) End Sub Private Sub btnCancelAll_Click( _  ByVal sender As System.Object, ByVal e As System.EventArgs) _  Handles btnCancelAll.Click   Me.objdsCustomers.RejectChanges() End Sub 

This is interesting code. There's something called a BindingContext that apparently belongs to the form (I didn't instantiate it, so it must be a part of Windows.Forms.Form ). BindingContext takes two parameters, a dataset and a tablename, and contains a count property (like RECCOUNT() ) and a position (like RECNO() ). To skip to the next record, all you have to type is

 Me.BindingContext(objdsCustomers, "Customers").Position = _ (Me.BindingContext(objdsCustomers, "Customers").Position + 1) 

That's the equivalent of FoxPro's

 SKIP 

It looks as if the BindingContext is also responsible for deleting records. Here's the command:

 Me.BindingContext(objdsCustomers, "Customers").RemoveAt( _ Me.BindingContext(objdsCustomers, "Customers").Position) 

and here's the FoxPro equivalent:

 DELETE 

How about adding? I found it. It's this:

 Me.BindingContext(objdsCustomers, "Customers").AddNew() 

That's not much harder than FoxPro's equivalent:

 APPEND BLANK 

I'm starting to like Visual Basic .NET. But I still like FoxPro better.

Loading the Dataset

But it still isn't loading my table from SQL into my dataset. And it needs to be a table named Customers, because datasets can have multiple tables, and by default the first one is named Table. It took me about 30 seconds to write the following code:

 Private Sub btnLoad_Click( _  ByVal sender As System.Object, ByVal e As System.EventArgs) _  Handles btnLoad.Click   Dim c As New SqlClient.SqlConnection   c.ConnectionString = "Server=VAIO\VAIO;Database=Northwind;UID=sa;PWD=;"   c.Open()   Dim da As New SqlClient.SqlDataAdapter("SELECT * FROM CUSTOMERS", c)   da.Fill(Me.objdsCustomers, "Customers") End Sub 

Retrieving a dataset requires opening a connection, then defining a DataAdapter and giving it a SELECT command, and then finally calling the DataAdapter 's Fill method to fill the dataset and providing the correct name for the table contained in the dataset. It's harder than opening a DBF, but it's easier than creating a cursor in a FoxPro form and dumping a SQL cursor into it. The dataset was declared up at the top of the form, so it has scope throughout the form. The connection and DataAdapter do their job and fade away.

I ran the form, and it worked just fine, except that I don't want to load the entire table from SQL or across the Internet into a table in my form. But this will do for now.

Data Binding

In FoxPro, if you open the Data Environment, click on a cursor name, and drag the word Fields onto the surface of the form, you get all of the fields in the table, using your classes, with a label to the left of them with a name for the field to the right of the label. The controls that are used are your own subclassed controls from your own VCX file, based on your settings on the Field Mappings page of the Tools, Options dialog. The labels are the text stored in the corresponding records in the database container, if you used one. The ControlSource property of each control is automatically filled in, and when you open the form and navigate through your data, a simple THISFORM.Refresh shows you that the record pointer has moved.

In Visual Basic .NET, if you create a dataset from a DataAdapter , then drag the dataset onto the form, you get bupkis ”nothing. No controls are placed on the form.

Open up the form generated by the Data Form Wizard, right-click on any of the text boxes, and look at the DataBindings property. You'll have to click on the plus sign to expand the property and see its Text property.

TIP

Just to clarify, the Text property is the equivalent of the Value property of a text box, combo box or check box, or the caption property of a label.

I've opened the drop-down for the Text property so that you can see how it has been populated , as shown in Figure 6.19.

Figure 6.19. Populating the Text property of DataBindings .

Now you know that you can define a dataset, then drop controls on the form and fill in the Text DataBindings property with individual fields from the dataset, and it will work.

However, if you open up the code, you'll find this:

 Me.editCustomerID.DataBindings.Add(New System.Windows.Forms.Binding("Text", _ Me.objdsCustomers, "Customers.CustomerID")) 

It seems that data binding is actually the result of a command that we can write ourselves . The syntax in pseudocode is this:

 Control.DataBindings.Add ( Binding("Text", ds, "table.field")) 

where ds is our dataset, Control is an object reference to each control on the form, and "table.field" is a string containing the table and field names as part of a typed dataset. It's possible to refer to them in some other way, but this is the easiest .

That's one of the reasons why we'll want to use typed datasets. If I can loop through all of the controls on the form and bind them to my dataset using names from a typed dataset, I don't have to go through dozens of controls on dozens of forms clicking and selecting the field name for each one. This is a good thing .

What Else Can You Do with Typed Datasets?

Typed datasets are the tip of the iceberg. At a minimum, a typed dataset contains one property procedure for each column in the corresponding schema, so that, for example, an expression like Customers.Name is meaningful. Otherwise, unlike FoxPro, it has no intrinsic meaning in Visual Studio 1.1.

Many developers have taken the notion of typed datasets far, far further. By adding events that other objects can respond to, it's possible (for example) to detect when data changes, look for "save" and "cancel" command buttons on the form and enable them, without writing any screen-specific code. The DataSet object itself does this. And many very sophisticated extensions are possible. IdeaBlade from ObjectWare, for example, can save data either back to the server, or to a local XML cache for subsequent synchronization with the server whenever a connection can be established. This is done using methods built into a generated DataSet object. There's no limit to what they can be designed to do except our imaginations.

Tables

Datasets contain a tables collection. When you return a dataset, if you want to show it in a grid, you have to specify both the DataSource (the dataset) and DataMember (the table). Otherwise, it shows you a little plus sign, which is your cue that you have to drill down and specify which table in the dataset ”even though you and I know there's only one. Computers aren't really all that bright.

Technically, you can do this:

 Dim oTable as DataTable For each oTable in MyDataset.Tables     Debug.WriteLine oTable.TableName End For 

Of course, the Table object itself has properties, events, and methods. Some of them will be useful. So expect to see code like this:

 Dim oTable As New DataTable = MyDataSet.Tables(0) 

followed by calls to methods on the oTable object. If you ever get used to referring to table number zero as the first table, you've outdone me. It just creeps me out.

Rows

By the same token, tables have rows. So we can also write this:

 For Each oTable In objdsCustomers.Tables     Dim oRow As DataRow     For Each oRow In oTable.Rows         Dim I As Integer         For I = 0 To oRow.ItemArray.Length - 1             Debug.WriteLine(oRow.ItemArray(I))         Next         Debug.WriteLine(oTable.TableName)     Next Next 

Row objects give you access to their columns via overloading. You can assign a value to the first column in a row, a column named CustomerID, using either the column name or its number (starting with zero, of course):

 Row("CustomerID") = 1234 

or

 Row(0) = 1234 

Columns

Each row contains a columns collection. The following code prints all of the rows in a table:

 Private Sub PrintValues(ByVal myTable As DataTable)     Dim myRow As DataRow     Dim myColumn As DataColumn     For Each myRow in myTable.Rows        For Each myColumn In myTable.Columns           Console.WriteLine(myRow(myColumn))        Next     Next  End Sub 

XML Web Services

XML Web services in .NET are as easy as they are in Visual FoxPro 8, and a heck of a lot easier than they are in earlier versions of Fox. Create a new project, and pick ASP.NET Web Services from the Visual Basic New Project dialog, as shown in Figure 6.20.

Figure 6.20. Creating a Visual Basic New Project.

Notice that the location of the project is http://localhost/Chapter6WebService . That means that the location is actually C:\InetPub\WWWRoot\Chapter6WebService . IIS is only allowed to see virtual directories that have been expressly added. The only exception is that it can also see any subdirectory under the C:\InetPub\WWWRoot\ directory that's not hidden from it. So by placing Web service projects there, Visual Studio .NET publishes them in the most logical place. And, because I've probably created 300 Web service projects on my computer since the early beta of .NET, I really appreciate anything that makes them easier to find. Then there's that whole "senior moment" thing.

I also changed both the external filename of Service1.asmx and the internal class name of the service to MyWebService . Changing the filename is easy; just right-click on Service1.asmx in the Solution Explorer, select Rename, and then type the new name. Then select the Web service design surface, then right-click and select Properties, and change the Service1 name to MyWebService as shown in Figure 6.21.

Figure 6.21. Naming a Visual Basic .NET XML Web service.

To write the code, double-click on the design surface, or select View, Code from the IDE menu. You'll see some commented code (probably lime green text) with placeholder code that looks like this:

 '<WebMethod()> _ 'Public Function HelloWorld() As String '   Return "Hello World" 'End Function 

The <WebMethod()> attribute prefix exposes your new Web service for testing on a Web page test bed that's automatically generated by Visual Studio. We'll need that, but everything else has got to go.

But first, we need a connection and a DataAdapter . Open the toolbox and drag a SQLDataAdapter to the design surface. As before, select the Northwind connection, pick the Customers table with a SELECT * FROM Customers statement, and click Finish to end the process.

Next, right-click on the DataAdapter and select Generate Dataset. Use dsCustomers as the name.

dsCustomers is a typed dataset. It's a type, as are integers and dates. Functions return types. So we'll return an XML string of type dsCustomers .

Now we're ready. Double-click on the form to open the code window. Replace the entire sample function with this:

 <WebMethod> Public Function GetCustomers() As dsCustomers    Dim Customers As New dsCustomers()    SqlDataAdapter1.Fill(Customers)    Return Customers End Function 

So we instantiate a dsCustomers object named Customers , use the built-in SELECT statement in the DataAdapter to fill the Customers dataset (which, as we know, is XML), and return the selected records in an XML string to the requestor .

Press F5 to run this, and the screen shown in Figure 6.22 appears.

Figure 6.22. The generated test bed fir your XML Web Service.

Visual Studio has created a test bed to test our Web service. This test bed page will list all of your Web service's functions, and will allow you to test any of them that have parameters that are primitive types and can, therefore, be entered on a Web page for use in testing. Thus any calls that require a dataset as input (such as an update routine) will be listed, but not testable.

Our new function GetCustomers is testable, so click on it. This brings up the next screen, shown in Figure 6.23. This screen appears in case we have parameters to enter. There aren't any in this case, so just click on the Invoke button, and you'll see the results in a browser window shown in Figure 6.24. I scrolled down to a customer in Venezuela, which is where I am at this instant, in a restaurant in Caracas, having cake and eating it too (I've never understood that statement ).

Figure 6.23. The Add Web Reference dialog.

Figure 6.24. The CustomersApp form running with a Web service.

Adding an Update Function

Open the Web service code again and add the following code:

 <WebMethod> Public Function UpdateCustomers( _   ByVal CustChanges As dsCustomers) _   As dsCustomers    If Not (CustChanges Is Nothing) Then       SqlDataAdapter1.Update(CustChanges)       Return CustChanges    Else       Return Nothing    End If End Function 

This function expects a diffgram, which it will call custChanges . It will use the SQLDataAdapter method Update to apply the diffgram to the source table that the DataAdapter is charged with updating, then return the diffgram to the caller. If there are no changes, it returns nothing. The reason for this is that if the Update method isn't able to process some of the records in the diffgram, it sends the ones it couldn't apply back to the source, presumably to try again later.

How to Use the Web Service

We'll need a Windows form application to test this Web service, so add a Windows Form project to the solution. Call it WinClientCustomers. On the form, add a DataGrid and two buttons named LoadData and SaveData .

To use this Web service, you need one method in your client form to get the records, and a command button to extract any changed records from the dataset and send them back. You'll need a dataset and a data source. But in this case, it's coming from a Web service. We need to write code that goes to the Internet for the data. And that's exactly where we go for the data connection as well.

Right-click on the project and select Add Web Reference from the context menu. You'll see the screen shown in Figure 6.23.

Click on the Web Services on Local Machine link. Pick your new Web service from the resulting list. A Web References item will be added to the WinClientCustomer project, right under References.

Next we need to add a dataset. What about the connection and DataAdapter components ? We don't need them. Add a dataset from the Data tab of the Toolbox, and a dialog will appear asking if you want to use the Typed Dataset with the Web Reference shown in the combo box. Click OK to accept it.

Listing 6.11 has the code for the Load event of the form:

Listing 6.11. Client Form Code to Load Data from a Web Service

 Private Sub LoadData_Click(ByVal sender As System.Object, _     ByVal e As System.EventArgs) Handles LoadData.Click     Dim ws As New WinClientCustomers.localhost.MyWebService     ws.Credentials = System.Net.CredentialCache.DefaultCredentials     DsCustomers1.Merge(ws.GetCustomers()) End Sub 

Listing 6.12 contains the code needed to save changes to the dataset.

Listing 6.12. Client Form Code to Save Changes to a Web Service

 Private Sub SaveData_Click(ByVal sender As System.Object, _     ByVal e As System.EventArgs) Handles SaveData.Click     If DsCustomers1.HasChanges() Then        Dim ws As New WinClientCustomers.localhost.MyWebService        Ws.Credentials = System.Net.CredentialCache.DefaultCredentials        Dim diffCustomers As New WinClientCustomers.localhost.dsCustomers        diffCustomers.Merge(DsCustomers1.GetChanges())        diffCustomers = ws.UpdateCustomers(diffCustomers)        DsCustomers1.Merge(diffCustomers)     End If End Sub 

The only thing remaining is to enter the DataSource for the grid. Open the drop-down and select DsCustomers1.Customers , the typed dataset. Now we're ready to give it a try.

Press F5 to run this application. This will bring up the screen shown in Figure 6.24.

Change something and click the Update button, and then close the form and run it again to see if the changes were saved. Worked on mine the first time.

You can hardly beat a Web service that only takes 14 lines of code, and a client form that acquires remote data and saves the changes back to the data source in another 15 lines of code. That's less coding that it takes in FoxPro.

To be fair, there are other issues that need to be addressed in a commercial application. Concurrency, one record per form, data binding, and a nice search screen are all things that take time and code. But with this simple foundation we can build great structures. And if this recession ever ends, we might even get paid for it.