Exploring the System.SqlClient Namespace

In this chapter, we use the SQL Server database as the data source to test the code samples. Hence, we use the System.Data.SqlClient (or just SqlClient) name-space most of the time. Before going any further, let's explore the SqlClient namespace and see what it has to offer.

Table 4-1 describes the SqlClient namespace classes.

Table 4-1: Sql Data Provider Classes

CLASS	DESCRIPTION
SqlClientPermission	Makes sure the user who is accessing the data has permission to access a data source.
SqlClientPermissionAttribute	Sets permission attributes.
SqlCommand	Represents a SQL statement or stored procedure. Data can be returned in a DataReader by calling its ExecuteReader method.
SqlCommandBuilder	Provides a mechanism to generate automatic commands for a table.
SqlConnection	Represents a Connection object. This is first class you need to use to connect to a data source.
SqlDataAdapter	Set of commands that provides a link between SqlConnection and DataSet.
SqlDataReader	Reads data in a forward-only direction through SqlCommand.
SqlError	Collects error and warnings returned by the data source.
SqlErrorCollection	Collects errors generated by Sql data providers.
SqlException	Exception handling class designed for Sql data sources.
SqlInfoMessageEventArgs	Provides data for the InfoMessage event.
SqlParameter	This class represents a parameter used in SqlCommand.
SqlParameterCollection	A collection of parameters.
SqlRowUpdateEventArgs	Provides data for the RowUpdated event.
SqlRowUpdatingEventArgs	Provides data from the RowUpdating event.
SqlTransaction	A transaction that can be processed at a data source.

Like the Sql data provider, the OleDb, ODBC, and other data providers implement the same class hierarchy model. Programmatically, the only difference is the prefix for each data provider namespace and the connection string. Internally, each data provider uses a different native data access technology to access the data source. For example, the OleDb provider classes begin with OleDb, the Sql Server provider namespace classes start with Sql, and the ODBC provider classes begin with Odbc . The System.Data classes have no provider-specific prefix, of course, because the classes defined in the System.Data and System.Data.Common namespaces are used in memory and are shared by all the providers.

The way ADO.NET works is that once you've connected to a database and dumped the data into the in-memory object (known as the DataSet), you can disconnect from the database and manipulate the data until you're ready to write it back to the database. The data provider classes serve as a bridge for moving data back and forth between memory and the database. In the next section we discuss how to initially connect to a database using these providers and how to implement the data provider classes to allow data to use this efficient bridge.

In the sections that follow we describe how to connect to various databases with the data providers. Then we show you how to use the data provider classes to execute queries and stored procedures. Also, you'll learn how these powerful classes work together and how the data providers differ in certain capabilities. You'll also learn how to do transaction locking, batch transactions, and transaction rollbacks.