Visual Basic .NET, Access, and System Data Types
As an Access developer, you know the importance of typing data properly. The idea behind using data types is to specify a structure for interpreting a value. Users of your applications can perform different operations on the number 1 than they can on the string 1 . Although classic Visual Basic obscured this kind of distinction with the Variant data type, good programming practice dictates that you should not use the Variant data type unless no alternative exists. Regardless, Visual Basic .NET no longer supports the Variant data type. Instead, it offers a new Object data type that is even broader in scope than the Variant data type. Just as you should avoid using the Variant data type in Visual Basic when a more specific type will serve an application, you should avoid using the Object data type in Visual Basic .NET when a more specific type will do.
The Visual Basic .NET data types correspond to data types for the .NET Framework s common language runtime (CLR). The CLR data types belong to the System namespace. This correspondence between Visual Basic .NET and CLR data types is part of the reason languages are interoperable in the .NET Framework. Chapter 2 highlighted some differences between classic Visual Basic and Visual Basic .NET data types. These differences are most important when you are trying to migrate a classic Visual Basic application to Visual Basic .NET. My recommendation is that you avoid such migrations. Use Visual Basic .NET for your new applications so that they can benefit from all its new features. Leave your old applications in classic Visual Basic so that you can devote your programming resources to new challenges instead of retrofitting new techniques to previously solved tasks .
Visual Basic .NET Data Type Summary
Table 3-1 lists the Visual Basic .NET data types along with their matching CLR System names . The third column in the table denotes the memory requirement for each data type, and the fourth column displays the range of values a data type can represent. I would normally refer you to a page in the Visual Studio .NET documentation for content such as this, but data types are so critical to Access database programming that I included portions of this information here. The unedited version of this material is available under the Data Type Summary topic of the documentation.
| Visual Basic .NET Data Type | CLR DataType | Size in Bytes | Value Range |
|---|---|---|---|
| Byte | System.Byte | 1 | 0 through 255 (unsigned) |
| Short | System.Int16 | 2 | -32,768 through 32,767 |
| Integer | System.Int32 | 4 | -2,147,483,648 through 2,147,483,647 |
| Long (long integer) | System.Int64 | 8 | -9,223,372,036,854,775,808 through 9,223,372,036,854,775,807 |
| Decimal | System.Decimal | 1 | 0 through |
| Single (single- precision floating-point number) | System.Single | 4 | -3.4028235E+38 through -1.401298E-45 for negative values; 1.401298E-45 through 3.4028235E+38 for positive values |
| Double | System.Double | 8 | -1.79769313486231570E+308 through |
| Char | System.Char | 2 | 0 through 65,535 (unsigned) |
| String | System.String (class) | Depends on implementing platform | 0 to approximately 2 billion Unicode |
| Date | System.DateTime | 8 | 0:00:00 on January 1, 0001 through 11:59:59 P.M. on December 31, 9999 |
| Boolean | System.Boolean | 2 | True or False |
| Object | System.Object (class) | 4 | Any type can be stored in a variable of type Object . |
The Byte through Long data types represent discrete whole numbers (values with no numbers to the right of the decimal point). When you are programming loops , use the Integer data type whenever possible because it speeds performance. Recall from Chapter 2 that the Integer data type in Visual Basic .NET refers to a 4-byte value instead of a 2-byte value, as it does in classic Visual Basic.
The Decimal data type represents precise values in the same way that Byte , Short , and Integer data types do. However, Decimal permits digits after the decimal point, such as the Single and Double data types. Decimal is a good candidate for replacing the Currency data type found in classic Visual Basic but missing from Visual Basic .NET. The Decimal data type is a full-fledged data type in Visual Basic .NET and not a subdata type of Variant . Visual Basic .NET enables you to declare a variable as a Decimal data type. (Classic Visual Basic required you to specify a Variant data type and allowed the return of the Decimal data type value with the CDec function.)
The Char data type, which complements the String data type, is a new one for classic Visual Basic developers. Both the Char and String data types represent displayable or printable Unicode characters, but the Char data type specifies a fixed-length string of exactly one character. With a maximum capacity of 2 ^ 31 ”about 2 billion ”characters, the String data type accommodates more characters than most applications will typically need. (It s OK to smile.) Unicode character representations use 2 bytes to represent each character. The full Unicode set of values for characters ”which range from 0 through 65,535 ” is particularly convenient for representing worldwide character sets, and Unicode codes include ASCII character codes as a subset.
A variable declared as a Boolean data type can have one of two states. Assign True and False to set the variable for each state. As with classic Visual Basic, True corresponds to a numeric value of -1, and False converts to 0. However, it is safest in assignment statements to use the logical values for which the Boolean data was designed.
The Date data type represents the date and time values in an 8-byte format. The range of date values extends from January 1, 0001 A.D. through December 31, 9999 A.D. As with classic Visual Basic, Visual Basic .NET represents days with integers to the left of the decimal point and fractions of a day with the value to the right of the decimal point. Visual Basic .NET offers a rich array of functions that return dates and times, perform arithmetic with Date data type values, and convert between Date data type and String data type representations for date values. These functions offer most developers a more efficient means of processing date values than direct manipulation of the numeric value underlying a date.
The Object data type is the root of all other types in Visual Basic .NET as well as in the whole .NET Framework. A variable with an Object data type can hold any other data type value. With the exception of String and Object , all other data types are value types. The memory for a value type contains the value. The size of the memory allocation for a value type depends on its size in bytes, as shown in Table 3-1. The Object data type is a reference data type. In this case, a variable declared as an Object data type does not contain a value. Instead, such a variable points at, or references, an object. The object can be very large or very small, but the variable with the Object data type will always take only 4 bytes. Of course, the object pointed at will require additional memory. Setting one variable equal to another when both are declared as Object data types does not create a copy of the object ”it creates only a copy of the pointer in the original variable.
It is sometimes useful to know the default value for data types. For example, knowing the default value can streamline your code. You do not have to set a variable equal to its default value if that is the value you want it to have initially. The default value for all numeric data types such as Byte and Single is 0. Dates have a default value of the first date and time, namely January 1, 0001 at 12:00:00 A.M. Boolean data types are False by default. The Char data type s default value is Chr(0) , which is the nonprintable ASCII code for Null . If you declare a String variable without explicitly assigning an initial string value to the memory variable, the variable equals a zero-length string ( ). In the case of Object data types, the default value is Null .
Visual Basic .NET Data Types vs. Access Data Types
Now that you know the Visual Basic .NET data types, you have solved half the puzzle of understanding how to use Visual Basic .NET to process data in Access databases. The other half of the puzzle is understanding the relationship between Visual Basic .NET and Access data types for tables. Tables are ultimately where you will collect data from Access for your Visual Basic .NET applications. (Queries, another potential data source, simply select and aggregate data from Access tables.)
Table 3-2 enumerates the 15 kinds of data types that you can create from an Access Design view for a table. Some Access data types such as Text and DateTime are stand-alone data types that you specify fully in the top grid in the Design view when you are creating or editing a table. Other important Access data types such as Byte , Integer , and Long Integer are actually subtypes of the Access Number data type. When specifying one of these other data types for a column in a table, you choose Number as the data type in the top grid within the Design view, and then you select the subtype as a Field Size setting in the lower portion of the Design view. For this reason, Table 3-2 uses two columns to denote the Access data types ”one for the data type and a second for the Number subtype specified in the Field Size setting.
| Access Data Type | Number Subtype | Corresponding Visual Basic .NET Data Type |
|---|---|---|
| Text | Not applicable | String |
| Memo | Not applicable | String |
| Number | Byte | Byte |
| Number | Integer | Short |
| Number | Long Integer | Integer |
| Number | Single | Single |
| Number | Double | Double |
| Number | Replication ID | Not supported ( System.Guid ) |
| Number | Decimal | Decimal |
| Datetime | Not applicable | Date |
| Currency | Not applicable | Not supported ( System.Decimal ) |
| Autonumber | Not applicable | Integer |
| Yes/No | Not applicable | Boolean |
| OLE Object | Not applicable | Not supported ( Stream class) |
| Hyperlink | Not applicable | String |
The third column in Table 3-2 shows the corresponding Visual Basic .NET data types for the Access table data types. Table 3-2 is constructed from two primary resources. The first resource is the Comparison of Data Types" section in the Microsoft Office documentation. You can find this page on the MSDN site at the following URL:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acdatComparisonDataTypes.asp
This Web page includes a table containing a pair of columns mapping Access 2000 and Access 2002 data types to Visual Basic 6 data types. The second resource Table 3-2 draws from is the table with information on Visual Basic .NET data types from the Visual Studio .NET documentation. (This is the same material that Table 3-1 was based on.) For example, the Access Currency data type maps to the Currency data type in Visual Basic 6, but as stated earlier, Visual Basic .NET dropped support for the Currency data type. Instead, the Access Currency data type maps to the Decimal data type in Visual Basic .NET. Visual Basic .NET also does not support the Replication ID subtype for the Number data type. A Replication ID data type value is a globally unique identifier (GUID), which is a 16-byte hexadecimal number. However, the .NET Framework will translate the Access Replication ID subtype of the Number type to the System.Guid type. The Access OLE Object data type corresponds to a binary object, such as a bitmap or a Microsoft Excel spreadsheet. No direct Visual Basic .NET translation for this Access data type exists. The .NET Framework can represent this data as a stream of bytes. Study the Stream class to learn how to read and write object instances of this class.
Table 3-2 borrows from the preceding Web page reference the convention of mapping the Access Hyperlink data type to the Visual Basic .NET String data type. Although this might be technically true in terms of the contents of the Hyperlink data type field, you cannot link a field with a Hyperlink data type in Visual Basic .NET. The Access Hyperlink data type does not follow traditional Web conventions for specifying a link. Instead, Access creates a four-part definition for Hyperlink data types. Some parts of this definition are optional. To implement the linking capability, you will need to parse the Access Hyperlink value into its parts and then transfer control according to the instructions embedded in the value.
Value Type Objects vs. Reference Type Objects
Visual Basic .NET is object oriented, and Visual Basic .NET data types are no exception. All the data types have properties and methods. In addition, you can create your own objects as either structures or classes. Your custom objects can have properties and methods ”just like the native objects available through Visual Basic .NET. The next sample demonstrates this capability while highlighting the distinction between value types, such as Integer and Single , and reference types, such as Object and String .
As explained earlier, a value data type saves its value with the memory representing an instance of the data type object. Copying an instance of a value type object copies the value of the data type instance to a new memory location. With a reference type object, the memory management for copying is different: the memory for the reference variable does not contain the object. Instead, the memory for a reference type object contains a pointer to the referenced object. The .NET Framework manages the memory for value and reference types in a list, referred to as the stack . Items in the stack have a known number of bytes determined by the type's size in bytes. The memory for items in the stack gets released immediately when an item goes out of scope ”for example, when a procedure declaring the value type closes . The objects at which reference types point reside in another area of memory known in the .NET Framework as the heap . The heap is managed by garbage collection.
You can specify a custom value type with the Structure type in Visual Basic .NET. This type essentially lets you designate a variable for a user -defined data type. The Structure type is similar in some ways to the Class type. Both let you create objects. When you create an object with the Class type, it is always a reference type. Creating your own object with the Structure type always yields a value type ”even if your user-defined variable contains reference variables that are not value types, such as strings. The same rule applies to objects instantiated with a Class type. Namely, the Class type always instantiates a reference type ”even if your definition for the class exclusively uses value types. The following sample contrasts the behavior of objects based on a Structure type with that of objects based on a Class type.
Start the construction of the sample by creating a new project based on the Empty Project template. In my sample materials for this chapter, I named the project ReferenceValueComparison. You create a project based on the Empty Project template just as you create a Microsoft Windows application, but you choose the Empty Project template. Because of the template selected, Visual Studio .NET creates a project shell with a matching folder. To make a Code window start a sample, choose Project, Add Module. Accept the default name of Module1.vb by clicking Open (unless you need to change the name before opening the module).
The code sample starts by declaring two types: NameStructure and NameClass . The NameStructure type uses the Structure keyword to define the blueprint for an object that will create value types when our application makes instances of the NameStructure structure. The NameClass type uses the Class keyword. The NameClass type will create reference types when our application makes instances of it.
Notice that the definitions for the NameStructure and NameClass types directly parallel one another, except that one is a Structure type and the other is a Class type. This distinction causes instances based on either definition to behave differently. Either object definition can create an object with two members (properties), p1 and p2 . The p1 property designates the first name of the object instance, and the p2 property value specifies the last name of the object instance. Because the NameStructure type creates value type instances, you can make different changes to the members of different instances. By contrast, the NameClass type creates reference type values. Therefore, changing a property for one instance based on the NameClass type causes the modification to appear in all instances based on the NameClass type.
The Sub main procedure is the startup object for the ReferenceValueComparison project. This is the default setting for an empty project to which you add a module. When you start the project (for example, by choosing Debug, Start), the CLR runs the project s Sub main . This procedure calls two other Sub procedures, democlass and demostructure . The democlass and demostructure Sub procedures are the same, except for two statements. The first is the Dim statement declaring the a and b instances of a name object. In the democlass Sub procedure, the instances are declared on the NameClass type so that the a and b instances point at the same name object. The demostructure Sub procedure declares a and b name instances based on the NameStructure type so that the two instances point at different value types, each containing their own values for the members in an instance. The second statement that distinguishes the Sub procedures displays the p1 property values for the a and b instances of the name object. Each procedure sets the message box title in order to identify which procedure generated the message box.
The type used for specifying a and b instances is by far the most important distinction between the democlass and demostructure procedures. Otherwise, the procedures are essentially the same. After the object declarations, the procedures create a new instance: a , with the p1 and p2 values of Rick and Dobson . Then the logic copies the a instance to a b instance. Next the logic assigns a new value to the p1 property of the a instance. The procedures close by invoking the MsgBox function to display the p1 property values for the a and b instances. Let's take a look at the code now:
Module Module1 'Declare a user-defined data type. Public Structure NameStructure Dim p1, p2 As String Sub New(ByVal Fname As String, ByVal Lname As String) Me.p1 = Fname Me.p2 = Lname End Sub End Structure 'Declare a class. Public Class NameClass Public p1, p2 As String Sub New(ByVal Fname As String, ByVal Lname As String) Me.p1 = Fname Me.p2 = Lname End Sub End Class Sub main() 'Demo the operation of the class. democlass() 'Demo the operation of the structure. demostructure() End Sub Sub democlass() 'Declare two instances of the class. Dim a, b As NameClass 'Assign values to a instance with New method. a = New NameClass("Rick", "Dobson") 'Set b instance equal to a instance. b = a 'Reset p1 member of a instance. a.p1 = "Virginia" 'Display p1 member of a and b instances. Dim str1 As String str1 = "a.p1 = " & a.p1 & _ "; b.p1 = " & b.p1 MsgBox(str1, MsgBoxStyle.Information, _ "From democlass") End Sub Sub demostructure() 'Declare two instances of the structure. Dim a, b As NameStructure 'Assign values to a instance with New method. a = New NameStructure("Rick", "Dobson") 'Set b instance equal to a instance. b = a 'Reset p1 member of a instance. a.p1 = "Virginia" 'Display p1 member of a and b instances. Dim str1 As String str1 = "a.p1 = " & a.p1 & _ "; b.p1 = " & b.p1 MsgBox(str1, MsgBoxStyle.Information, _ "From demostructure") End Sub End Module Figure 3-1 shows the two messages generated by the Sub main calls of the democlass and demostructure procedures. In the From Democlass message box, a change to the p1 property for the a instance maps to the b instance. Virginia is the p1 property for both the a and b instances. The From Demostructure message box maintains separate property values for the a and b instances; therefore, a modification of the a instance's p1 value does not revise the b instance. In the From Demostructure message box, the p1 property for b is still Rick , although the p1 member for the a instance is Virginia .
Figure 3-1: The message boxes generated when you run the ReferenceValueComparison project
EAN: 2147483647
Pages: 111