Visual Basic, Visual Studio, ADO.NET, and SQL Server
The DataAdapter is the bridge between the database and disconnected objects in the ADO.NET object model. The DataSet holds the data used by the DataAdapter. Here's code using DataAdapter and DataSet objects that will do the same thing as the previous code. (The connectionString and commandWrapper objects are exactly the same as before. A previous article in this series shows the code.)
This code gives you exactly the same result. It might not look like much of an improvement at this point, but the use of DataAdapter and DataSet objects fits much better with the rest of ADO.NET and offers much more power and flexibility when the code gets more complex. Microsoft assures us that they're more efficient too. For one thing, you can use the DataSet object much like an in-memory database with the data retrieved from the SQL Server database. You might also notice that the code doesn't open and close the connection explicitly either. Because the connection should be open as little as possible, the DataAdapter does it for you.
When you use a DataAdapter, the keywords you use to access the database are easier to understand. When you use the SqlCommand object directly (it's instantiated in the commandWrapper variable in this code), you will normally use one of three methods to access the database:
These names have never made sense to me. But with SqlDataAdapter object, you will use one of four properties (not methods) instead:
These methods are a lot easier for me to understand and remember. In the code above, you can see SelectCommand being used:
A DataAdapter normally uses two methods to work with the database:
Fill is used with the SelectCommand property as shown above. When you use the DeleteCommand, InsertCommand, or UpdateCommand property, you use the Update method.
Just as a clarification, you may also see the term data provider. A data provider is just the namespace that contains the classes we have been talking about. .NET has five of them "in the box" and third parties (such as Oracle and IBM) provide them too. The ones that come with .NET are:
That's why you have to include the statement ...
... to run the code in these articles.
All of these providers include the four "core" objects:
My original code used a DataReader object (Using result As SqlDataReader = commandWrapper.ExecuteReader()) while the revised code used a DataAdapter (dataAdapter.SelectCommand = commandWrapper). In addition to everything discussed so far, there are other differences between these two objects. A DataReader provides read-only, forward-only access to the database (You can't "back up" and access a previous record.) while a DataAdapter can access a record randomly and update. For this reason, a DataReader is more efficient.
And there are still more advantages to using these new and improved ADO.NET objects. The next article in this series, Coding the VB.NET Many-to-Many Database App uses them while at the same time fulfilling the promise made to show how to code this important database relation!
Dim dataAdapter As SqlDataAdapter = New SqlDataAdapterDim dataSet As DataSet = New DataSetdataAdapter.SelectCommand = commandWrapperdataAdapter.Fill(dataSet)Dim theRecipeName As String = ""For Each dr As DataRow In dataSet.Tables(0).Rows...<processing code>...NextThis code gives you exactly the same result. It might not look like much of an improvement at this point, but the use of DataAdapter and DataSet objects fits much better with the rest of ADO.NET and offers much more power and flexibility when the code gets more complex. Microsoft assures us that they're more efficient too. For one thing, you can use the DataSet object much like an in-memory database with the data retrieved from the SQL Server database. You might also notice that the code doesn't open and close the connection explicitly either. Because the connection should be open as little as possible, the DataAdapter does it for you.
When you use a DataAdapter, the keywords you use to access the database are easier to understand. When you use the SqlCommand object directly (it's instantiated in the commandWrapper variable in this code), you will normally use one of three methods to access the database:
- ExecuteReader
- ExecuteScaler
- ExecuteNonQuery
These names have never made sense to me. But with SqlDataAdapter object, you will use one of four properties (not methods) instead:
- SelectCommand
- DeleteCommand
- InsertCommand
- UpdateCommand
These methods are a lot easier for me to understand and remember. In the code above, you can see SelectCommand being used:
dataAdapter.SelectCommand = commandWrapperA DataAdapter normally uses two methods to work with the database:
- Fill
- Update
Fill is used with the SelectCommand property as shown above. When you use the DeleteCommand, InsertCommand, or UpdateCommand property, you use the Update method.
Just as a clarification, you may also see the term data provider. A data provider is just the namespace that contains the classes we have been talking about. .NET has five of them "in the box" and third parties (such as Oracle and IBM) provide them too. The ones that come with .NET are:
- SQL Server - System.Data.SqlClient (This is the one we have been using!)
- OLE DB - System.Data.OleDb
- ODBC - System.Data.Odbc
- Oracle - System.Data.OracleClient
- EntityClient - System.Data.EntityClient
That's why you have to include the statement ...
Imports System.Data.SqlClient... to run the code in these articles.
All of these providers include the four "core" objects:
- Connection
- Command
- DataReader
- DataAdapter
My original code used a DataReader object (Using result As SqlDataReader = commandWrapper.ExecuteReader()) while the revised code used a DataAdapter (dataAdapter.SelectCommand = commandWrapper). In addition to everything discussed so far, there are other differences between these two objects. A DataReader provides read-only, forward-only access to the database (You can't "back up" and access a previous record.) while a DataAdapter can access a record randomly and update. For this reason, a DataReader is more efficient.
And there are still more advantages to using these new and improved ADO.NET objects. The next article in this series, Coding the VB.NET Many-to-Many Database App uses them while at the same time fulfilling the promise made to show how to code this important database relation!
Source...