Section 20.5. ADO.NET Object Model

20.5. ADO.NET Object Model

The ADO.NET object model provides an API for accessing database systems programmatically. ADO.NET was created for the .NET framework to replace Microsoft's ActiveX Data Objects™ (ADO) technology. As will be discussed in the next section, the IDE features visual programming tools that simplify the process of using a database in your projects. While you may not need to work directly with many ADO.NET objects to develop simple applications, basic knowledge of how the ADO.NET object model works is important for understanding data access in Visual Basic.

Namespaces System.Data, System.Data.OleDb and System.Data.SqlClient

Namespace System.Data is the root namespace for the ADO.NET API. The other important ADO.NET namespaces, System.Data.OleDb and System.Data.SqlClient, contain classes that enable programs to connect with and manipulate data sources locations that contain data, such as a database or an XML file. Namespace System.Data.OleDb contains classes that are designed to work with any data source, whereas System.Data.SqlClient contains classes that are optimized to work with Microsoft SQL Server databases. The chapter examples manipulate SQL Server 2005 Express databases, so we use the classes of namespace System.Data.SqlClient. SQL Server 2005 Express is provided with Visual Basic 2005 Express. It can also be downloaded from msdn.microsoft.com/vstudio/express/sql/default.aspx.

An object of class SqlConnection (namespace System.Data.SqlClient) represents a connection to a data sourcespecifically a SQL Server database. A SqlConnection object keeps track of the location of the data source and any settings that specify how the data source is to be accessed. A connection is either active (i.e., open and permitting data to be sent to and retrieved from the data source) or closed.

An object of class SqlCommand (namespace System.Data.SqlClient ) represents a SQL command that a DBMS can execute on a database. A program can use SqlCommand objects to manipulate a data source through a SqlConnection. The program must open the connection to the data source before executing one or more SqlCommands and close the connection once no further access to the data source is required. A connection that remains active for some length of time to permit multiple data operations is known as a persistent connection.

Class DataTable (namespace System.Data) represents a table of data. A DataTable contains a collection of DataRows that represent the table's data. A DataTable also has a collection of DataColumns that describe the columns in a table. DataRow and DataColumn are both located in namespace System.Data. An object of class System.Data. DataSet, which consists of a set of DataTables and the relationships among them, represents a cache of datadata that a program stores temporarily in local memory. The structure of a DataSet mimics the structure of a relational database.

ADO.NET's Disconnected Model

An advantage of using class DataSet is that it is disconnected the program does not need a persistent connection to the data source to work with data in a DataSet. Instead, the program connects to the data source to populate the DataSet (i.e., fill the DataSet's DataTables with data), but disconnects from the data source immediately after retrieving the desired data. The program then accesses and potentially manipulates the data stored in the DataSet. The program operates on this local cache of data, rather than the original data in the data source. If the program makes changes to the data in the DataSet that need to be permanently saved in the data source, the program reconnects to the data source to perform an update then disconnects promptly. Thus the program does not require any active, persistent connection to the data source.

An object of class SqlDataAdapter (namespace System.Data.SqlClient ) connects to a SQL Server data source and executes SQL statements to both populate a DataSet and update the data source based on the current contents of a DataSet. A SqlDataAdapter maintains a SqlConnection object that it opens and closes as needed to perform these operations using SqlCommands. We demonstrate populating DataSets and updating data sources later in this chapter.