Visual Basic, Visual Studio, ADO.NET, and SQL Server
Up to this point in this series designed to show beginners how to use VB.NET with SQL Server databases, we have concentrated on the most basic steps that you can use. We haven't spent much time looking at the bigger technology picture and - trust me - it's a big picture. The goal of this article is to bring that bigger picture into focus for you. If you reached this page from a search and you would like to start at the beginning, go to this link: A First Database Centered Application for Beginners.
In that first article, the shortest possible route to using a SQL Server database was used. In fact, almost no VB.NET code was required because the app used automation available by binding directly to the powerful DataGridView object. The illustration below shows objects in the "component designer tray" in Visual Studio that are installed automatically for a simple two table application. This is all pre-written code in .NET and this is the reason why very little code needed to be in the program.
--------
Click Here to display the illustration
--------
After this initial example, we switched over to a very different way of doing things so you could see the steps involved and how the code actually worked. The general process used in all of the articles after the first one was something like this:
--------
Click Here to display the illustration
--------
I have mentioned that production database code in the real world usually doesn't work this way, however. In the real world, you usually use automation like the objects created for you in the first DataGridView based example.
But the DataGridView wizard isn't the only way to create them. Usually, you will define a Data Source using the Visual Studio Add New Data Source selection under the Data tab and then use that to access the database. But, just as before, we're going to do it in code first to make sure the concept is clear.
ADO.NET
Although these articles haven't discussed it, the technology we're using to access SQL Server is all part of ADO.NET. This is a huge part of .NET that Microsoft defines this way:
"ADO.NET provides consistent access to data sources such as SQL Server and XML, and to data sources exposed through OLE DB and ODBC. Data-sharing consumer applications can use ADO.NET to connect to these data sources and retrieve, handle, and update the data that they contain."
This should convince you that we have just been using a small corner of ADO.NET and there is a lot more there.
The concept of "disconnected" versus "connected" database processing is a key idea that you have understand to program databases with VB.NET and ADO.NET. The previous version of ADO.NET - which was just called "ADO" - was a "connected" model. It's not used very much anymore.
To make this as clear as possible, let's compare the two ideas.
If you're a beginning programmer, you have probably never seen one of the old style "sort and merge" programs that were used in nearly all of the world's programs in the era of mainframe computers. They were the most extreme examples of being "connected" because they could tie up the data for hours when they ran. The concept was simple:
The "Connected" Data Model
The disconnected model has a critical difference:
The "Disconnected" Data Model
In other words, in the disconnected model, processing the data is independent of accessing it. In most applications, the data store is opened and closed again ("disconnected") as quickly as possible. In today's multiprocessing networked world, we usually can't tie up a data source by having it open in just one program any longer than absolutely necessary. That's a critical requirement for ASP.NET web applications.
Here's some critical code from a previous article to illustrate the difference:
The database connection was opened and then the ExecuteReader method got all of the data selected by the SQL statement and put it in the result object. After everything was complete, was closed again.
The DataAdapter and DataSet Objects
Another difference between my code and most real-world code is that it was necessary to write all of the code to process the data. Here's a shortened version of the previous code:
ADO.NET provides two objects to help do this job called the DataAdapter and the DataSet. On the next page, we show how they change the way the code is written.
In that first article, the shortest possible route to using a SQL Server database was used. In fact, almost no VB.NET code was required because the app used automation available by binding directly to the powerful DataGridView object. The illustration below shows objects in the "component designer tray" in Visual Studio that are installed automatically for a simple two table application. This is all pre-written code in .NET and this is the reason why very little code needed to be in the program.
--------
Click Here to display the illustration
--------
After this initial example, we switched over to a very different way of doing things so you could see the steps involved and how the code actually worked. The general process used in all of the articles after the first one was something like this:
--------
Click Here to display the illustration
--------
I have mentioned that production database code in the real world usually doesn't work this way, however. In the real world, you usually use automation like the objects created for you in the first DataGridView based example.
But the DataGridView wizard isn't the only way to create them. Usually, you will define a Data Source using the Visual Studio Add New Data Source selection under the Data tab and then use that to access the database. But, just as before, we're going to do it in code first to make sure the concept is clear.
ADO.NET
Although these articles haven't discussed it, the technology we're using to access SQL Server is all part of ADO.NET. This is a huge part of .NET that Microsoft defines this way:
"ADO.NET provides consistent access to data sources such as SQL Server and XML, and to data sources exposed through OLE DB and ODBC. Data-sharing consumer applications can use ADO.NET to connect to these data sources and retrieve, handle, and update the data that they contain."
This should convince you that we have just been using a small corner of ADO.NET and there is a lot more there.
The concept of "disconnected" versus "connected" database processing is a key idea that you have understand to program databases with VB.NET and ADO.NET. The previous version of ADO.NET - which was just called "ADO" - was a "connected" model. It's not used very much anymore.
To make this as clear as possible, let's compare the two ideas.
If you're a beginning programmer, you have probably never seen one of the old style "sort and merge" programs that were used in nearly all of the world's programs in the era of mainframe computers. They were the most extreme examples of being "connected" because they could tie up the data for hours when they ran. The concept was simple:
The "Connected" Data Model
- Open the data store
- Access and process data
- Close the data store
The disconnected model has a critical difference:
The "Disconnected" Data Model
- Open the data store
- Access data
- Close the data store
- Process the data
In other words, in the disconnected model, processing the data is independent of accessing it. In most applications, the data store is opened and closed again ("disconnected") as quickly as possible. In today's multiprocessing networked world, we usually can't tie up a data source by having it open in just one program any longer than absolutely necessary. That's a critical requirement for ASP.NET web applications.
Here's some critical code from a previous article to illustrate the difference:
linkToDB.Open()...Using result As SqlDataReader = commandWrapper.ExecuteReader()...<processing code>...linkToDB.Close()The database connection was opened and then the ExecuteReader method got all of the data selected by the SQL statement and put it in the result object. After everything was complete, was closed again.
The DataAdapter and DataSet Objects
Another difference between my code and most real-world code is that it was necessary to write all of the code to process the data. Here's a shortened version of the previous code:
If result.HasRows = True ThenDo While result.Read...<processing code>...LoopEnd IfADO.NET provides two objects to help do this job called the DataAdapter and the DataSet. On the next page, we show how they change the way the code is written.
