The System.Data Namespace This book will take advantage of three namespaces that allow you to work with data within the application in this book: System.Data, System.Data.OleDb, and System.Data.SqlClient. (Microsoft and other vendors will add support for additional data namespaces in the future.) The System.Data namespace contains classes that manage data within memory. System.Data.OleDb and System.Data.SqlClient provide classes that can connect to data sources, read and write data, and fill System.Data objects with data. The SqlClient and OleDb Namespaces Microsoft has provided two namespaces that we'll use for managing data throughout this book. The System.Data.OleDb namespace allows you to work with data using "unmanaged" OLE DB providers (unmanaged means that the namespace uses code written outside of the .NET CLR). The System.Data.SqlClient namespace allows you to work with data in Microsoft SQL Server 7.0 or higher, using the SQL Server native API (with no OLE DB provider required). The classes and their members are almost identical in name and functionality. Table 4.1 provides a list of the most commonly used classes from the System.Data.OleDb and System.Data.SqlClient namespaces. To make things simpler, we've removed the namespace-specific prefixes from each object. Add the appropriate prefix ("OleDb" or "Sql") to each of these class names. Table 4.1. ADO.NET Classes | Class | Description | | Connection | This class is responsible for making the actual connection to the database. It has methods for starting, committing, and rolling back transactions as well. | | Command | This class allows you to submit SQL statements to a data source or to call stored procedures. It can either perform action queries (INSERT, UPDATE, and DELETE) or execute SELECT statements to return a DataReader object. | | DataReader | This class provides forward-only, read-only access to data. If you're an ADO developer, you're familiar with this type of data access it's similar to the default ADO Recordset (often called a fire hose cursor). | | DataAdapter | This class encapsulates a connection and one or more commands (SELECT, UPDATE, DELETE, and INSERT). It can retrieve and update data and uses its SelectCommand property to fill a DataSet or DataTable object with data. | The System.Data Classes The System.Data class provides a number of classes, but you'll only use a few of them when developing your applications. The classes described here all work with data in memory rather than interacting with any particular data source. You can typically use these in-memory data stores to both view and update data. Table 4.2 provides a list of the most commonly used System.Data classes. Table 4.2. System.Data Classes | Class | Description | | DataSet | This data cache is much like an in-memory database. It can contain one or more DataTable objects. You can store the data as well as the schema information for the tables. You may also set relationships between DataTable objects within a DataSet object. | | DataTable | A DataTable provides a collection of DataRow objects. Each DataRow object is, in turn, made up of a collection of DataColumn objects. A DataTable is a single table or a single view of data. A DataTable can be populated via a DataAdapter object. | | DataView | A DataView object is used to sort or filter the data within a DataTable. You can also use a DataView for searching data as well. | Making database access part of the underlying .NET Framework means that any language targeting the .NET platform will have access to the same set of data classes. In addition, because the data-access classes are part of the .NET Framework, if or when the framework is ported to alternate platforms such as Linux, the same data classes should be available in the new platforms as well. This set of data classes in .NET is known collectively as ADO.NET. ADO.NET is different from previous Microsoft database access technologies in several ways. It's more highly focused on disconnected data access, which makes it easier for Web developers to work with data. ADO.NET separates objects that talk to data sources from objects that just hold data. The data classes also "speak" fluent XML. This makes it easier to transmit data from one application to another, regardless of operating system or transport mechanism. A Data-Handling Example The following example reads data from the Products table in the Northwind sample database. It uses a number of the ADO.NET classes, including OleDbDataAdapter and DataSet. The procedure shown in this section, XMLGet, first creates a connection string and a SQL string. It passes these two strings to the constructor of the OleDbDataAdapter object. The code then calls the OleDbDataAdapter object's Fill method in order to fill the DataSet object with data. NOTE You don't need to type System.Data.OleDb.OleDbDataAdapter here because VB .NET automatically includes an Imports statement that imports the System.Data namespace.
The Fill method is responsible for creating an implicit OleDbConnection object using the supplied connection string. Next, the Fill method creates an OleDbCommand object and uses the SELECT statement to read the data from the data source. The Fill method reads the data and fills one table within the DataSet object. The code closes the collection, leaving you with just the filled DataSet object. The DataSet object's GetXml method returns its entire set of data as an XML stream. Your code can handle the XML in any way it requires the following code snippet places the XML into a text box on the sample page: Private Sub XMLGet() Dim strConn As String Dim strSQL As String Dim da As OleDb.OleDbDataAdapter Dim ds As DataSet strConn = "Provider=sqloledb;Data Source=(local);Initial Catalog=Northwind;User ID=sa" strSQL = "SELECT ProductID, ProductName FROM Products" da = New OleDb.OleDbDataAdapter(strSQL, strConn) ds = New DataSet() da.Fill(ds) txtXML.Text = ds.GetXml() End Sub If you know that you are only going to be using SQL Server, you might choose to use the SqlClient namespace. The classes in this namespace are specific for SQL Server and will give you a little better performance when working with SQL Server. The following code snippet provides the same functionality, using the SqlClient namespace classes: Private Sub XMLGetSqlClient() Dim strConn As String Dim strSQL As String Dim da As SqlClient.SqlDataAdapter Dim ds As DataSet strConn = "Server=(local);Database=Northwind;" _ "User ID=sa" strSQL = "SELECT ProductID, ProductName FROM Products" da = New SqlClient.SqlDataAdapter(strSQL, strConn) ds = New DataSet() da.Fill(ds) txtXML.Text = ds.GetXml() End Sub | 
