A Whole New Data Technology
You can do a lot with Visual Basic by itself, but ultimately, you need data to build most systems. VB.NET is designed to work with a new way to access data: ADO.NET.
Chapter 19 of Microsoft Visual Basic Step by Step is a great intro, but again, it presents a view of ADO.NET through a Microsoft filter. There are a number of things that it doesn't say. We're going to say those things here in this lesson.
The name "ADO.NET" was invented by the Microsoft marketing department and you should not conclude that it's "just like ADO". Like VB.NET, ADO.NET is a completely new product with only a surface similarity to earlier versions. That's why it doesn't really matter that ADO once stood for "ActiveX Data Objects". It doesn't stand for that anymore.
The simplest definition of ADO.NET is that it is Microsoft's new .NET object library for data access. In one way, this is just the most recent of Microsoft's access methods (previous products were DAO, RDO and ADO). But in a more fundamental way, this is a new revolution in software. Prepare to bend your mind.
Microsoft's data technologies have been among their most successful products and a big part of the reason why is wrapped up in the word "standard". ADO.NET can be used with all ODBC data sources, XML, Oracle, SQL Server ... the list goes on and on.
The bottom line is that Microsoft has positioned ADO.NET as the universal data model:
Or, at least as close to that ideal as they can come.
MSDE - Moving From Jet to SQL Server
One Microsoft data technology that is not mentioned in the book - and should have been - is called MSDE - the most recent version is currently being called MSDE 2000 but the Microsoft marketing gnomes are starting to refer to it only by it's full name: Microsoft SQL Server 2000 Desktop Engine.
For a long time, Microsoft had two quite different ways to access databases that you had to choose between:
If your application was targeted at a small number of desktop PC's, you normally used Jet (that is, Access). But programmers discovered that Jet just didn't "scale up" enough for corporate systems and lacked sophisticated features. Developers found out the hard way that when their systems started to get really popular, they would have to make a lot of code changes to migrate their Jet applications to Microsoft's industrial strength database, SQL Server.
Enter MSDE - Microsoft SQL Server 2000 Desktop Engine
From Microsoft's point of view, this was terrible!!! Some organizations decided that if they were going to have to recode the application anyway, they might as well switch to Oracle or IBM's DB2. So Microsoft released MSDE. It was designed to be compatible with SQL Server ... and they made it free and redistributable so they could convince as many developers as possible to use it! That's a better deal than you can get with Access and Jet!
Playing into Microsoft's hands is not necessarily a bad thing. MSDE is a great data technology and you can't beat the price.
MSDE, for example, has client-server architecture. Database operations occur on the database server, not the client (the database engine must be installed on a server, of course).
And both MSDE and SQL Server conform to Transact-SQL (T-SQL). Jet doesn't. Jet has its own query language which is pretty nice for small scale applications, but you have to recode when you need to move up to SQL Server. (Transact-SQL is a superset of ANSI-SQL and it lets you program the server with custom business logic - almost an absolute requirement for true corporate databases.)
A big plus to Jet, however, is that MSDE doesn't include a user interface. (Access is the user interface for Jet.) You can, however, create what is called an Access Data Project with Access 2003 (which creates an .adp file). This is designed to provide a smooth transition to SQL Server.
DataBase Management Tools
Visual Studio .NET also has some of the same types of tools that are in Access for working with databases. We'll look more at some of those in the next chapter.
Or, you can install a third part database manager such as ...
In professional programming, however, you seldom are actually required to design a database from scratch.
Usually you have to access a database that already exists. Designing and modifying databases is usually done by specialists (DBA's - Data Base Administrators).
Beyond the Book: The Rest of ADO.NET
Please read chapter 19 and complete the example before reading this lesson. The book shows one way to use ADO.NET. Emphasis: "one way".
How often does .NET only have one way to do things?
There are, in fact, a lot of objects in the ADO.NET namespace. They're roughly split into two groups:
The content components essentially hold actual data and include these classes:
The managed-provider components interact with the database for data retrievals and updates. These components include:
These names are "generic" because unique managed-provider components are used with different databases). There are two that are currently automatically included with ADO.NET: SqlClient is used with Microsoft's SQL Server database and OleDb for everything else.
For example, SqlClient contains SqlDataReader and OleDb contains OleDbDataReader . And more recently, Microsoft has supplied one just for Oracle. But there are a lot of providers available from third party software companies:
Even the open source Mono Project has a whole catalog of ADO.NET providers.
DataReader - A Code Example
The method our book uses is shown in an illustration on page 490. But using DataSet as pictured isn't the only way to do it. Another way to look at it might be like the left side path in the illustration above. To give you an alternative, let's take a detailed look at another way to use OleDBConnection, the DataReader.
The OleDbDataReader is a more streamlined way to access data. OleDbDataReader is a read-only, forward-only way to get data from a database.
This makes it more limited, but faster.
Although we won't take advantage of this here, DataReader can also improve performance by retrieving data as soon as it is available, rather than waiting for the entire result of the query to be returned in a DataSet. Microsoft notes that, "Results are returned as the query executes, and are stored in the network buffer on the client until you request them." Things like this can be critical in using large databases.
In this example application, we don't use automated dialogs to build the application. We do it in code! For our example, the database is then stored in a series of arrays. This allows random access to the data after the connection is closed to the database and eliminates the "forward-only" limitation of OleDbDataReader.
Although we could be using MSDE, this example is also coded using Access like the one in the book. Our very simple Access database was constructed containing data about recent About Visual Basic Spotlight articles.
The database in Access
Here's what our database looks like in Access. The entire solution can be downloaded, but first, I'll go through each line of code and explain how it works.
The application starts with an Imports statement for the OleDb namespace.
~~~~~~~~~~~~~~~~~~~~~~~~~
'Imports OleDb namespace
Imports System.Data.OleDb
~~~~~~~~~~~~~~~~~~~~~~~~~
Then, before any subroutine code, a series of DIM statements are added to make the scope of the variables global over the subroutines.
This has nothing to do with ADO.NET but it makes the program work.
~~~~~~~~~~~~~~~~~~~~~~~~~
Dim I As Integer = 0
Dim dteAboutVBDate(10000) As Date
Dim strAboutVBDesc(10000) As String
Dim blnAboutVB6(10000) As Boolean
Dim blnAboutVBNet(10000) As Boolean
Dim urlAboutVBURL(10000) As String
~~~~~~~~~~~~~~~~~~~~~~~~~
The program reads the database and initializes a ListBox in the Form Load subroutine.
The first statement in that subroutine is the declaration of the Connection String. This is a set of parameters that allows ADO.NET to match the unique characteristics of your database.
For the Access database in this example, the Connection String is pretty simple. (Note that {path} must be replaced by the actual path on your computer.)
~~~~~~~~~~~~~~~~~~~~~~~~~
Dim strAboutVBConn As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "User ID=Admin;" _
& "Data Source={path}\"AboutVB.mdb"
~~~~~~~~~~~~~~~~~~~~~~~~~
Page 2 of the program
The next statements declare objects necessary for using OleDb: the Command object and the DataReader object.
~~~~~~~~~~~~~~~~~~~~~~~~~
Dim ocmdAboutVBCommand As OleDbCommand
Dim odtrAboutVBDataReader As OleDbDataReader
~~~~~~~~~~~~~~~~~~~~~~~~~
The Imports statement coded earlier makes it possible to avoid qualifying these names in the DIM statement. Without the Imports statement,
OleDbCommand
would be coded as
System.Data.OleDb.OleDbCommand.
The next tasks are to instantiate the Command and Connection objects, Open the Connection and execute the DataReader. But before we do that, another refinement will be added.
Since the section of code that actually reads the database is critical, the entire section will be in a Try block. If an error occurs, the message will be displayed in the form using code in the Catch block. A more complete and professional system would probably have more error checking, but it would be variations of this same technique.
Here's the ADO.NET code down to the execution of the DataReader. The CommandText is set to a very simple SQL statement that reads everything in an example "Articles" table from the Access database.
~~~~~~~~~~~~~~~~~~~~~~~~~
ocmdAboutVBCommand = New OleDbCommand()
With ocmdAboutVBCommand
.Connection = New OleDbConnection(strAboutVBConn)
.Connection.Open()
.CommandText = "Select * From Articles"
odtrAboutVBDataReader = .ExecuteReader()
End With
~~~~~~~~~~~~~~~~~~~~~~~~~
After the DataReader has been executed, the results of the query are available using the Read method of the DataReader. Each time the Read method is called, a new row of the query results are provided. To transfer all of the results into our arrays, the Read method is called until it returns False. Perfect for a Do-While loop.
One of the advantages of the .NET Foundation is the wealth of built in functions available in the objects.
For example, since we're going for efficiency in this example, we will use the BeginUpdate and EndUpdate methods of the ListBox control. These methods allow the ListBox to be conveniently updated without being repainted on the screen each time a change is made.
ADO.NET features another performance option. Let's look at just the artDate field of the database to see how it works. In the old ADO, you would probably access the actual columns in the old ADO RecordSet (RecordSet isn't in ADO.NET) with a statement similar to this new ADO.NET statement where the column is referenced by name.
~~~~~~~~~~~~~~~~~~~~~~~~~
dteAboutVBDate(I) = odtrAboutVBDataReader.Item("artDate")
~~~~~~~~~~~~~~~~~~~~~~~~~
DataReader provides a series of methods to access column values in their native data types (GetDateTime, GetDouble, GetGuid, GetInt32, and so on). These typed accessor methods perform better by returning a value as a specific .NET Framework type and don't require additional type conversion. (If you want to see the whole list, Microsoft provides it here.)
Here's the same statement using a typed accessor method. (The value 2 indicates that the field is in the second zero based ordinal reference of the column. Or, in other words, it's the third field.)
~~~~~~~~~~~~~~~~~~~~~~~~~
dteAboutVBDate(I) = odtrAboutVBDataReader.GetDateTime(2)
~~~~~~~~~~~~~~~~~~~~~~~~~
The downloadable source code has statements for both methods so you can try both on your own (one set is commented out).
Finally, the DataReader and the Connection are both closed.
~~~~~~~~~~~~~~~~~~~~~~~~~
odtrAboutVBDataReader.Close() ocmdAboutVBCommand.Connection.Close()
~~~~~~~~~~~~~~~~~~~~~~~~~
.NET is pretty forgiving if you forget (and you see lots of examples - even in books! - where a "close" isn't done) but Microsoft "officially" recommends it:
While a DataReader is open, the Connection is in use exclusively by that DataReader. You will not be able to execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.
The rest of the Form Load subroutine is simple housekeeping. The arrays are resized to minimize memory use and the top item in the ListBox is selected. Once this is done, an event subroutine to change the Text properties of the Label components is simple and straightforward.
Download the entire source.
You can do a lot with Visual Basic by itself, but ultimately, you need data to build most systems. VB.NET is designed to work with a new way to access data: ADO.NET.
Chapter 19 of Microsoft Visual Basic Step by Step is a great intro, but again, it presents a view of ADO.NET through a Microsoft filter. There are a number of things that it doesn't say. We're going to say those things here in this lesson.
The name "ADO.NET" was invented by the Microsoft marketing department and you should not conclude that it's "just like ADO". Like VB.NET, ADO.NET is a completely new product with only a surface similarity to earlier versions. That's why it doesn't really matter that ADO once stood for "ActiveX Data Objects". It doesn't stand for that anymore.
The simplest definition of ADO.NET is that it is Microsoft's new .NET object library for data access. In one way, this is just the most recent of Microsoft's access methods (previous products were DAO, RDO and ADO). But in a more fundamental way, this is a new revolution in software. Prepare to bend your mind.
Microsoft's data technologies have been among their most successful products and a big part of the reason why is wrapped up in the word "standard". ADO.NET can be used with all ODBC data sources, XML, Oracle, SQL Server ... the list goes on and on.
The bottom line is that Microsoft has positioned ADO.NET as the universal data model:
- All programming languages
- All data sources
Or, at least as close to that ideal as they can come.
MSDE - Moving From Jet to SQL Server
One Microsoft data technology that is not mentioned in the book - and should have been - is called MSDE - the most recent version is currently being called MSDE 2000 but the Microsoft marketing gnomes are starting to refer to it only by it's full name: Microsoft SQL Server 2000 Desktop Engine.
For a long time, Microsoft had two quite different ways to access databases that you had to choose between:
- Jet (used in Access)
- SQL Server
If your application was targeted at a small number of desktop PC's, you normally used Jet (that is, Access). But programmers discovered that Jet just didn't "scale up" enough for corporate systems and lacked sophisticated features. Developers found out the hard way that when their systems started to get really popular, they would have to make a lot of code changes to migrate their Jet applications to Microsoft's industrial strength database, SQL Server.
Enter MSDE - Microsoft SQL Server 2000 Desktop Engine
From Microsoft's point of view, this was terrible!!! Some organizations decided that if they were going to have to recode the application anyway, they might as well switch to Oracle or IBM's DB2. So Microsoft released MSDE. It was designed to be compatible with SQL Server ... and they made it free and redistributable so they could convince as many developers as possible to use it! That's a better deal than you can get with Access and Jet!
Playing into Microsoft's hands is not necessarily a bad thing. MSDE is a great data technology and you can't beat the price.
MSDE, for example, has client-server architecture. Database operations occur on the database server, not the client (the database engine must be installed on a server, of course).
And both MSDE and SQL Server conform to Transact-SQL (T-SQL). Jet doesn't. Jet has its own query language which is pretty nice for small scale applications, but you have to recode when you need to move up to SQL Server. (Transact-SQL is a superset of ANSI-SQL and it lets you program the server with custom business logic - almost an absolute requirement for true corporate databases.)
A big plus to Jet, however, is that MSDE doesn't include a user interface. (Access is the user interface for Jet.) You can, however, create what is called an Access Data Project with Access 2003 (which creates an .adp file). This is designed to provide a smooth transition to SQL Server.
DataBase Management Tools
Visual Studio .NET also has some of the same types of tools that are in Access for working with databases. We'll look more at some of those in the next chapter.
Or, you can install a third part database manager such as ...
In professional programming, however, you seldom are actually required to design a database from scratch.
Usually you have to access a database that already exists. Designing and modifying databases is usually done by specialists (DBA's - Data Base Administrators).
Beyond the Book: The Rest of ADO.NET
Please read chapter 19 and complete the example before reading this lesson. The book shows one way to use ADO.NET. Emphasis: "one way".
How often does .NET only have one way to do things?
There are, in fact, a lot of objects in the ADO.NET namespace. They're roughly split into two groups:
- content components
- managed-provider components
The content components essentially hold actual data and include these classes:
- DataSet
- DataTable
- DataView
- DataRow
- DataColumn
- DataRelation
The managed-provider components interact with the database for data retrievals and updates. These components include:
- connection
- command
- data adapter
- data reader
These names are "generic" because unique managed-provider components are used with different databases). There are two that are currently automatically included with ADO.NET: SqlClient is used with Microsoft's SQL Server database and OleDb for everything else.
For example, SqlClient contains SqlDataReader and OleDb contains OleDbDataReader . And more recently, Microsoft has supplied one just for Oracle. But there are a lot of providers available from third party software companies:
- DataDirect Connect for .NET
100% Managed .NET Data Providers for Oracle ? DB2 ? Microsoft SQL Server ? Sybase - UniDirect .NET Data Provider
"Universal access to most major database servers such as Microsoft SQL Server, Microsoft Access, Oracle, DB2, MySQL, PostgreSQL and others"
Even the open source Mono Project has a whole catalog of ADO.NET providers.
DataReader - A Code Example
The method our book uses is shown in an illustration on page 490. But using DataSet as pictured isn't the only way to do it. Another way to look at it might be like the left side path in the illustration above. To give you an alternative, let's take a detailed look at another way to use OleDBConnection, the DataReader.
The OleDbDataReader is a more streamlined way to access data. OleDbDataReader is a read-only, forward-only way to get data from a database.
This makes it more limited, but faster.
Although we won't take advantage of this here, DataReader can also improve performance by retrieving data as soon as it is available, rather than waiting for the entire result of the query to be returned in a DataSet. Microsoft notes that, "Results are returned as the query executes, and are stored in the network buffer on the client until you request them." Things like this can be critical in using large databases.
In this example application, we don't use automated dialogs to build the application. We do it in code! For our example, the database is then stored in a series of arrays. This allows random access to the data after the connection is closed to the database and eliminates the "forward-only" limitation of OleDbDataReader.
Although we could be using MSDE, this example is also coded using Access like the one in the book. Our very simple Access database was constructed containing data about recent About Visual Basic Spotlight articles.
The database in Access
Here's what our database looks like in Access. The entire solution can be downloaded, but first, I'll go through each line of code and explain how it works.
The application starts with an Imports statement for the OleDb namespace.
~~~~~~~~~~~~~~~~~~~~~~~~~
'Imports OleDb namespace
Imports System.Data.OleDb
~~~~~~~~~~~~~~~~~~~~~~~~~
Then, before any subroutine code, a series of DIM statements are added to make the scope of the variables global over the subroutines.
This has nothing to do with ADO.NET but it makes the program work.
~~~~~~~~~~~~~~~~~~~~~~~~~
Dim I As Integer = 0
Dim dteAboutVBDate(10000) As Date
Dim strAboutVBDesc(10000) As String
Dim blnAboutVB6(10000) As Boolean
Dim blnAboutVBNet(10000) As Boolean
Dim urlAboutVBURL(10000) As String
~~~~~~~~~~~~~~~~~~~~~~~~~
The program reads the database and initializes a ListBox in the Form Load subroutine.
The first statement in that subroutine is the declaration of the Connection String. This is a set of parameters that allows ADO.NET to match the unique characteristics of your database.
For the Access database in this example, the Connection String is pretty simple. (Note that {path} must be replaced by the actual path on your computer.)
~~~~~~~~~~~~~~~~~~~~~~~~~
Dim strAboutVBConn As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" _
& "User ID=Admin;" _
& "Data Source={path}\"AboutVB.mdb"
~~~~~~~~~~~~~~~~~~~~~~~~~
Page 2 of the program
The next statements declare objects necessary for using OleDb: the Command object and the DataReader object.
~~~~~~~~~~~~~~~~~~~~~~~~~
Dim ocmdAboutVBCommand As OleDbCommand
Dim odtrAboutVBDataReader As OleDbDataReader
~~~~~~~~~~~~~~~~~~~~~~~~~
The Imports statement coded earlier makes it possible to avoid qualifying these names in the DIM statement. Without the Imports statement,
OleDbCommand
would be coded as
System.Data.OleDb.OleDbCommand.
The next tasks are to instantiate the Command and Connection objects, Open the Connection and execute the DataReader. But before we do that, another refinement will be added.
Since the section of code that actually reads the database is critical, the entire section will be in a Try block. If an error occurs, the message will be displayed in the form using code in the Catch block. A more complete and professional system would probably have more error checking, but it would be variations of this same technique.
Here's the ADO.NET code down to the execution of the DataReader. The CommandText is set to a very simple SQL statement that reads everything in an example "Articles" table from the Access database.
~~~~~~~~~~~~~~~~~~~~~~~~~
ocmdAboutVBCommand = New OleDbCommand()
With ocmdAboutVBCommand
.Connection = New OleDbConnection(strAboutVBConn)
.Connection.Open()
.CommandText = "Select * From Articles"
odtrAboutVBDataReader = .ExecuteReader()
End With
~~~~~~~~~~~~~~~~~~~~~~~~~
After the DataReader has been executed, the results of the query are available using the Read method of the DataReader. Each time the Read method is called, a new row of the query results are provided. To transfer all of the results into our arrays, the Read method is called until it returns False. Perfect for a Do-While loop.
One of the advantages of the .NET Foundation is the wealth of built in functions available in the objects.
For example, since we're going for efficiency in this example, we will use the BeginUpdate and EndUpdate methods of the ListBox control. These methods allow the ListBox to be conveniently updated without being repainted on the screen each time a change is made.
ADO.NET features another performance option. Let's look at just the artDate field of the database to see how it works. In the old ADO, you would probably access the actual columns in the old ADO RecordSet (RecordSet isn't in ADO.NET) with a statement similar to this new ADO.NET statement where the column is referenced by name.
~~~~~~~~~~~~~~~~~~~~~~~~~
dteAboutVBDate(I) = odtrAboutVBDataReader.Item("artDate")
~~~~~~~~~~~~~~~~~~~~~~~~~
DataReader provides a series of methods to access column values in their native data types (GetDateTime, GetDouble, GetGuid, GetInt32, and so on). These typed accessor methods perform better by returning a value as a specific .NET Framework type and don't require additional type conversion. (If you want to see the whole list, Microsoft provides it here.)
Here's the same statement using a typed accessor method. (The value 2 indicates that the field is in the second zero based ordinal reference of the column. Or, in other words, it's the third field.)
~~~~~~~~~~~~~~~~~~~~~~~~~
dteAboutVBDate(I) = odtrAboutVBDataReader.GetDateTime(2)
~~~~~~~~~~~~~~~~~~~~~~~~~
The downloadable source code has statements for both methods so you can try both on your own (one set is commented out).
Finally, the DataReader and the Connection are both closed.
~~~~~~~~~~~~~~~~~~~~~~~~~
odtrAboutVBDataReader.Close() ocmdAboutVBCommand.Connection.Close()
~~~~~~~~~~~~~~~~~~~~~~~~~
.NET is pretty forgiving if you forget (and you see lots of examples - even in books! - where a "close" isn't done) but Microsoft "officially" recommends it:
While a DataReader is open, the Connection is in use exclusively by that DataReader. You will not be able to execute any commands for the Connection, including creating another DataReader, until the original DataReader is closed.
The rest of the Form Load subroutine is simple housekeeping. The arrays are resized to minimize memory use and the top item in the ListBox is selected. Once this is done, an event subroutine to change the Text properties of the Label components is simple and straightforward.
Download the entire source.
SHARE
