The System.Data Namespace

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梚t'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梩he 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