Branching and Looping

Branching and looping statements help you control the flow of the active statement within your applications. It is difficult to write even a simple application without using a branching statement, such as If Then or If Then Else . Several code samples shown earlier in this chapter demonstrated these two types of branching statements. This section takes a more focused look at branching and looping statements by using samples that demonstrate how to use the COM Interop feature in the .NET Framework. With COM Interop capability, you can set a reference in a Visual Basic .NET procedure to type libraries, such as those used in the References collection with classic Visual Basic.

With End With Statements

Perhaps the most basic statement for controlling the flow within a code block is With End With . Nothing about using this statement in Visual Basic .NET has changed from its use in classic Visual Basic. This type of statement delimits a code block. Because the With End With statement is inline code, a single pass through the code block is unconditional. A common use for a With End With statement is to assign values to multiple properties for a single object instance. The main benefit of this statement is that within its code block, you do not have to name the object instance for each statement assigning a property value. Instead, you can represent the object instance by using a period (.) inside the With End With statement.

Branching Statements

The If Then and If Then Else statements support branching. Another branching statement type is the Select Case statement. As with the With End With statement, no changes in the syntax for these statements have occurred in their .NET incarnations. The basic If Then statement allows you to execute a block of code based on the value of an expression that represents a condition. The expression should evaluate to True or False . The If Then Else statement extends this capability by letting you conditionally execute either of two code blocks. By adding ElseIf clauses after the Then clause, you can specify the conditional execution of more than two code blocks. Another option for conditionally executing multiple code blocks is to use the Select Case statement. The Select Case statement applies to situations in which you want to execute code blocks dependent on the value of a variable or expression. Although you can check for different values in each Case clause within a Select Case statement, each Case clause must apply to the same variable or expression. The If Then Else statement can accommodate different expressions in each ElseIf clause.

When using branching statements, it is good programming practice to have a catchall clause for variable or expression values that do not match any other clause that you specify. Use the Else clause as the last clause in an If Then Else statement with multiple ElseIf clauses to catch a condition not represented by the expression for any If or ElseIf clause. With the Select Case statement, use the Case Else clause as the last clause to catch variable or expression values not specified in preceding Case clauses. The use of the Else and Case Else clauses is optional.

A new type of branching statement is the Try Catch Finally statement. This statement introduces a new style of processing for run-time errors that relieves the need for the On Error GoTo statement that has been available to programmers since the inception of classic Visual Basic. Chapter 4 includes a section that drills down on the syntax for the Try Catch Finally statement as well as its new style of error processing based on exceptions. This new statement type is a radical innovation because it eliminates the need for GoTo statements for error processing in your applications. Use of the GoTo statement introduces the possibility of code that is hard to follow and debug. (Developers sometimes refer to code with GoTo statements as spaghetti code .) Visual Basic .NET relentlessly aims to avoid spaghetti code by dropping the GoSub , On GoSub , and On GoTo statements.

Looping Statements

Looping statements allow you to iterate through a code block multiple times. The condition for looping through the code block depends on the type of looping statement. The looping statement types are While End While , Do Loop , For Next , and For Each Next . As with the branching statements, the basic syntax and operation of these Visual Basic .NET statements mirror their counterparts in classic Visual Basic. The sole syntax change was made to the former While Wend statement, which has been updated to While End While .

Both the Do Loop and While End While statements permit looping as long as a condition is True . In addition, the Do Loop statement allows a code block to execute until a condition becomes True . You can pass control to the first statement following either type of looping statement by using either an Exit While or Exit Do statement.

The For Next statement allows you to pass through code for a fixed number of iterations based on your arguments for the statement. You can specify a value that increases or decreases by a fixed numeric increment up to or down to a limit. When the value passes the limit, control transfers to the first statement after the For Next loop.

The For Each Next statement has a statement name similar to the For Next statement, but its operation differs significantly. The For Each Next statement lets you pass through the elements in a set, such as an array or a collection. The key point is that the loop passes through the individual elements ” not the indexes for the elements ”in a set. When you are working with the members of a set, the For Each Next syntax will often be simpler than the For Next syntax. However, when you are not iterating over the members of a set, it is helpful to have the For Next statement available.

Both types of For loops provide support for common looping features. For example, you can invoke an Exit For statement to exit a loop before reaching its end. You can nest For loops within one another. It is common to have an inner loop pass through the columns on a row while an outer loop designates a succession of rows.

COM Interop

COM Interop is not a branching or looping topic, but we will discuss this feature briefly here because subsequent looping samples will apply it. The COM Interop feature in the .NET Framework permits the invoking of a COM object from Visual Basic .NET. Because almost all recent Microsoft software preceding the release of the .NET Framework is based on COM objects, you likely will need the COM Interop feature as you migrate from traditional Microsoft applications to .NET applications. For example, ADO is available as several COM objects for Access. Therefore, invoking the COM Interop feature with one or more of these COM objects enables you to tap your ADO programming skills as you first migrate to Visual Basic .NET.

The COM Interop feature essentially puts a wrapper around a COM object so that you can use it from within Visual Basic .NET. The process of invoking the COM Interop feature is similar to setting a reference to a type library in classic Visual Basic. In fact, you add a reference to your Visual Basic .NET project that points to the type library for the COM object that you want to use. After setting a reference for a type library, you can optionally use an Imports statement to create a shortcut for the reference. (Chapter 2 initially presented the Imports statement.)

Begin the demonstration of the COM Interop feature by starting a new Windows application. (The project in the sample materials for this chapter has the name MetaDataLooping.) After the project opens, add a reference to a COM object by choosing Project, Add Reference. In the Add Reference dialog box, select the COM tab. This reveals the list of all registered COM objects on a workstation. This example will add two references ”one for the ADO object library that supports data access (including making a connection), and a second reference for the ADO Extensions for DDL and Security ( ADOX ) object library that permits the examination of metadata for an Access database file (.mdb).

From the COM tab of the Add Reference dialog box, scroll down to the name of the version of ADO that you are using. Figure 3-2 shows the 2.5 version of the library selected. This version is common in many environments running Access 2002. You need some version of this library to make a connection to an Access database file with ADO. In addition, the looping samples with the COM Interop feature from the MetaDataLooping project work with metadata. Therefore, the MetaDataLooping project can benefit from another reference to the ADOX library. Figure 3-2 shows the Add Reference dialog box just before the selection of the Microsoft ADO Ext. 2.5 For DDL And Security 2.5. Clicking Select will add the second reference name to the Selected Components list box. Then clicking OK will commit the selected items.

Figure 3-2: Open the Add Reference dialog box to add references to a project for COM objects.

After closing the Add Reference dialog box, names for the libraries ( ADODB and ADOX ) will be added to the References folder in the Solution Explorer for the project. Access developers experienced with ADO will recognize ADODB and ADOX as the names used to reference the ADO data access and the ADO data definition language and security libraries. Therefore, after you make the connection, you should feel right at home programming ADO inside a Visual Basic .NET project. The balance of this chapter contains samples illustrating various approaches for using ADO within Visual Basic .NET.

Note  

This discussion assumes you are familiar with ADO programming. Readers without ADO experience might want to simply skim the samples to get a feel for the looping syntax, which is basically the same as for classic Visual Basic. ADO has been available for Access developers since the release of Access 2000. For in-depth ADO coverage that targets Access developers, see my book Programming Microsoft Access Version 2002 (Microsoft Press, 2002). In addition, the Microsoft Data Access Objects (DAO) 3.51 Object Library and the Microsoft DAO 3.6 Object Library offer COM references that you can add to your projects.

Iterating Through Tables in a Database

The first sample using looping and the COM Interop feature in the Northwind database displays the names of user -defined tables in a message box ”for example, Shippers and Categories. The approach to this task relies on the ADODB and ADOX references added to the MetaDataLooping project shown earlier in this section. Before starting the sample, add a button to Form1 in the Windows application; its name will be Button1 unless you override the default assignment to the button s Name property. You can drag the button from the Toolbox. Use the Properties window to change the button s Text property from Button1 to List Tables.

After adding the button to the form in Design view, show the code behind the form by right-clicking a blank area on the form and choosing View Code from the shortcut menu. Just below the Windows Form Designer Generated Code region, enter the following Visual Basic .NET code. (The completed sample is available in the materials posted for this chapter in the MetaDataLooping project in the folder with the same name.)

The sample code begins by declaring a module-level variable ( cat1 ) for an ADOX Catalog object. The declaration is at the module level because several procedures will reference it. This sample code references the declaration from two procedures, but a subsequent piece of sample code references it from a third procedure. (See the "Iterating Through Columns in a Table" discussion.) A Catalog object stores the metadata for a connection to an Access database. The Form1_Load event procedure instantiates an ADODB Connection object named cnn1 and uses cnn1 to open a connection to the Northwind database. Notice that the connection string is identical to the ADO connection string. That s because we are using ADO. The point is that you are using coding techniques that developers have had available to them since the release of Access 2000. The Form1_Load event procedure concludes by assigning the Connection object named cnn1 to the ActiveConnection property of the Catalog object named cat1 . Therefore, cat1 can expose metadata about the Northwind database.

Note  

The following sample code specifies the user ID and password. However, they are optional when no security exists on a database file (as is true for the sample Northwind database). Chapter 13, "Securing .NET Applications with Access Database," drills down on security topics.

The Button1_Click event procedure puts cat1 to work by listing the user- defined tables in the Northwind database within a message box. The procedure begins by declaring a string variable and assigning a value to it for the header within the message box. This single string will eventually populate the whole message box. Next the procedure uses a For Each Next loop to pass through the tables within the Northwind database. Besides user-defined tables, several types of table objects exist within a database, such as the Customers and Orders tables. However, all user-defined tables have a Type property of TABLE. This sample code takes advantage of the Type property by filtering with an If Then statement for tables with a Type property of TABLE. When the If Then statement discovers a table with a Type property of TABLE, the code block within the If Then statement appends the table s name to the str1 variable initially declared at the start of the event procedure.

After passing through all the tables in the Northwind database, the sample displays the value of str1 in a message box by invoking the Show method for the MessageBox class. As you saw in Chapter 2, you can invoke the Show method without first invoking an instance of the MessageBox class. The message box should show the tables appearing in the Database container window with Tables selected in the Objects bar. The default filtering (not showing Hidden and System objects) should also be selected.

 'Declare a reference to an ADOX Catalog object. Dim cat1 As New ADOX.Catalog() Private Sub Form1_Load(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles MyBase.Load 'Declare a new ADO Connection object. Dim cnn1 As New ADODB.Connection() 'Open an ADO Connection object. cnn1.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\Program Files\" & _ "Microsoft Office\Office10\Samples\Northwind.mdb;", _ "Admin", "") 'Assign the connection to the catalog. cat1.ActiveConnection = cnn1 End Sub Private Sub Button1_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button1.Click 'Declare a string to head the table names. Dim str1 As String = _ "The user-defined table names are:" & StrDup(2, vbCr) 'Loop through the tables in the catalog defined in 'the Form1_Load event procedure. Dim tbl1 As ADOX.Table For Each tbl1 In cat1.Tables If tbl1.Type = "TABLE" Then str1 = str1 & tbl1.Name & vbCr End If Next 'Display the results in a message box. MessageBox.Show(str1, "From Chapter 3", _ MessageBoxButtons.OK) End Sub 

Iterating Through Columns in a Table

Among the most common kinds of metadata are the data types for the columns in a table. You can report the data types for the columns in a table by designating a particular table in a catalog and then iterating through the elements of the Columns collection in that table. By displaying the Name and Type property for each column, you can derive the data type for each column. The next piece of sample code demonstrates this approach for the Categories table, which has just four columns, each one containing a different data type.

Start the sample by adding a second button to Form1 in the MetaDataLooping project. The button s default name will be Button2 . Change its Text property to List Data Types . Drag the right edge of the button to show the whole Text property setting.

This sample code illustrates one approach to printing the column names and their data type codes in the Debug panel of the Output window. After the declarations for the Button2_Click event procedure, the sample points the tbl1 variable to the Categories table in the Northwind database. Then a For Each Next statement loops through the columns in the Categories table. The code block within the loop assigns a value to a string ( str1 ) with the column s Name and Type properties for each successive column. Next the sample invokes the WriteLine method for the Debug class to print the string to the Output window.

 Private Sub Button2_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button2.Click 'Declare objects for procedure. Dim tbl1 As ADOX.Table Dim col1 As ADOX.Column Dim str1 As String 'Loop through columns in the Categories table from the 'Northwind database, and write results to the Output window. tbl1 = cat1.Tables("Categories") For Each col1 In tbl1.Columns str1 = col1.Name & "; " & col1.Type & vbCr Debug.WriteLine(str1) Next End Sub 

Figure 3-3 shows an excerpt from the Output window for the preceding sample code. (The figure clips the right edge of selected lines.) You have to close Form1 and end the running application to return to the IDE so that you can view the contents of the Output window. If the Output window is not already in view, choose View, Other Windows, Output. The window starts by listing various assemblies loaded to support the project. Then the window displays the four column names in the Categories table along with the DataTypeEnum value for the data type of each column. The Type property does not return the member name of the DataTypeEnum for a column s type; instead, it returns the numeric code for the column s data type. You can look up these DataTypeEnum values in the Object Browser in a code window for your favorite version of Access or classic Visual Basic. The DateTypeEnum is a class in both the ADODB and ADOX libraries. Next we will discuss a Function procedure that automates the return of the DateTypeEnum member name corresponding to a data type code.

Figure 3-3: The WriteLine method for the Debug class can write strings to the Output window.

Translating Access Data Types

The preceding sample represented the DataTypeEnum values for the columns in the Categories table of the Northwind database. As mentioned, having the DataTypeEnum names would be more helpful. Decoding a number to a corresponding name is a task that lends itself well to using a Select Case statement inside a Function procedure. The procedure can accept a number as an input parameter and return a string. The input parameter number can correspond to the DataTypeEnum value, and the return string can be its matching DataTypeEnum name. This section will demonstrate the construction of a sample to implement this task. This sample will also contrast the performance of the ADO features through the COM Interop feature with the native Visual Basic .NET procedures for translating Access data types.

The output for the preceding sample shows a DataTypeEnum value of 3 for the CategoryID column in the Categories table. (See Figure 3-3.) Whether you look up the DataTypeEnum name for the value 3 manually or programmatically, the result will be adInteger . Because adInteger does not correspond to an Access data type, some Access developers might wonder what data type they are actually using (regardless of whether they see the DataTypeEnum value or name). The following sample tackles this issue by constructing a table named DataTypes with all 15 Access data types shown in Table 3-2. The column name for each table corresponds to a recognizable Access data type name. Figure 3-4 shows the Design view from Access for the DataTypes table. The Description column specifies the Field Size setting for the Number data types that have subtypes .

Figure 3-4: The DataTypes table in the dbDataTypes Access database file has all 15 Access data types.

I constructed the DataTypes table in the dbDataTypes database file, and I saved the database file in the bin subdirectory within the MetaDataLooping folder. Therefore, you get the database with its table automatically when you download the MetaDataLooping folder with the sample materials for this chapter. Storing an Access database in the folder for a solution is a convenient way of deploying an Access database file with a Visual Basic .NET solution. Just copy the solution s folder to another computer, and the database file appears in a known location. If you use the same name for the folder on both computers, you will not need to manually edit the code for the location of the database file. If copying the folder to another machine is not feasible , you can create a simple setup procedure that copies the name of the new directory for the folder containing a solution (and its associated database).

To start implementing this sample, add another button to Form1 in the MetaDataLooping project. Set the button s Text property to List Data Type Names . The button s default Name property is Button3 . Then insert the following code for an event procedure for the button and a Function procedure that translates the DataTypeEnum values to names.

The Button3_Click event procedure actually translates the Access data types for the columns of the DataTypes table into both ADO and .NET Framework data types. The preceding sample code demonstrates how to programmatically create an ADO connection to a database. However, this section's sample needs an ADO.NET Connection object pointing at the dbDataTypes database and an OleDbDataAdapter referencing the DataTypes table in the database. In addition, you need a DataSet object, which the sample refers to as DsDataTypes1 , to hold a local version of the contents in the DataTypes table within the dbDataTypes database file. See the Jump Start sample at the end of Chapter 2 for a model of how to construct these objects. Figure 3-5 shows the Design view for Form1 after the addition of the OleDbConnection1 , OleDbDataAdapter1 , and the DsDataTypes1 objects. I started the process of creating the objects by dragging the DataTypes table from Server Explorer to Form1 . The Properties window shows that DsDataTypes1 is a DataSet object that points at the DsDataTypes dataset. (See the DataSetName property setting.)

Figure 3-5: The Design view for Form1 in the MetaDataLooping project with the ADO.NET connection, data adapter, and dataset objects for the Button3_Click event procedure

The code within the Button3_Click event procedure starts by making an ADO connection to the dbDataTypes database. Then the code assigns the connection to the ActiveConnection property for the cat2 Catalog object. The event procedure next loops through the columns of the DataTypes table in the dbDataTypes database. So far, this code sample follows the general design of the preceding sample. But here the approach changes. Instead of returning the raw Type property value for the columns in the table, the sample passes the Type property value as an argument to the ADODataType Function procedure, and the procedure returns the corresponding DataTypeEnum name. Therefore, the display in the Output window from the Debug.Writeline statement contains the Access column name, which is based on its Access data type name, and the matching ADO data type name. The ADODataType Function demonstrates the syntax for applying the Select Case statement to decode a number to its matching string value.

The remainder of the Button3_Click event procedure demonstrates how to translate the Access data types to their matching .NET Framework data types. First, the code fills the DataTypes DataTable in the DsDataTypes1 DataSet with the contents of the DataTypes table from the dbDataTypes database. This process implicitly converts Access data types to .NET Framework data types. Then the sample loops through the columns of the local DataTypes DataTable . Although the concept is the same as in the preceding code that uses ADO, the actual objects are different because the block of code within the click event procedure deals with ADO.NET objects instead of ADO ones. The ColumnName property contains the name of a column in the local datatable, and the DataType property returns the data type value. By applying the ToString method, the procedure converts the value for display in the Output window by using the Writeline method for the Debug class.

 Private Sub Button3_Click(ByVal sender As System.Object, _ ByVal e As System.EventArgs) Handles Button3.Click 'Declare objects. Dim cnn1 As New ADODB.Connection() Dim cat2 As New ADOX.Catalog() Dim tbl1 As ADOX.Table Dim col1 As ADOX.Column Dim str1 As String 'Open ADO connection to database. cnn1.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=C:\pawvbnet\" & _ "MetaDataLooping\bin\dbDataTypes.mdb;", _ "Admin", "") 'Assign connection to catalog and open 'a table in the catalog. cat2.ActiveConnection = cnn1 tbl1 = cat2.Tables("DataTypes") 'Iterate through the columns in the table 'and decode ADO type numbers to ADO type names. For Each col1 In tbl1.Columns str1 = col1.Name & "; " & ADODataType(col1.Type) Debug.WriteLine(str1) Next 'Fill a local dataset with a table from 'an Access database. OleDbDataAdapter1.Fill(DsDataTypes1, "DataTypes") Dim dt1 As DataTable Dim dtcol As DataColumn 'Iterate through the columns in the dataset's 'table based on the original in the Access database. For Each dtcol In DsDataTypes1.Tables("DataTypes").Columns str1 = dtcol.ColumnName & ", " & dtcol.DataType.ToString Debug.WriteLine(str1) Next End Sub Function ADODataType(ByVal intType As String) _ As String 'Decode ADO type number to ADO type name. Select Case intType Case 2 Return "adSmallint" Case 3 Return "adInteger" Case 4 Return "adSingle" Case 5 Return "adDouble" Case 6 Return "adCurrency" Case 7 Return "adDate" Case 11 Return "adBoolean" Case 17 Return "adUnsignedTinyInt" Case 72 Return "adGuid" Case 130 Return "adWChar" Case 131 Return "adNumeric" Case 202 Return "adVarWChar" Case 203 Return "adLongVarWChar" Case 205 Return "adLongVarBinary" Case Else Return "Unspecified ADO type number" End Select End Function 

Figure 3-6 displays an excerpt from the Output window from the Button3_Click event procedure. The top set of column and data type names show the DataTypes column names with their matching ADO data type names. The bottom set of column names and data types show comparable results for .NET Framework data types. (These data types appear with a System prefix.) Notice that Visual Basic .NET displays System data type names even for Access data types that it does not support directly. For example, Visual Basic .NET offers no data type for processing GUIDs. However, the procedure seamlessly inserts the name of a System data type ( System.Guid ) from the .NET Framework.

Figure 3-6: The Output window results generated by the Button3_Click event procedure from the MetaDataLooping project