A data provider is a collection of classes used to access a data source. ADO.NET provides various sets of classes for working with each data source. For example, the classes for interacting with Microsoft SQL Server are organized in the System.Data.SqlClient namespace, the classes for interacting with Oracle are organized in the System.Data.OracleClient namspace, the classes for interacting with OLEDB data sources are organized in the System.Data.OleDb namespace, and the classes for interacting with Open Database Connectivity (ODBC) are organized in the System.Data.Odbc namespace. | The exam focuses on the use of the Microsoft SQL Server database. However, you should expect common functionality among data provider classes because they all implement the common interfaces defined in the System.Data namespace. For example, the SqlConnection class in the System.Data.SqlClient namespace provides the functionality for connecting to a SQL Server database ”the same functionality that is provided by the OracleConnection class in the System.Data.OracleClient namespace. | The SqlConnection Class SqlConnection represents a single persistent connection to a SQL Server data source. Table 7.1 shows the most important members of the SqlConnection class. Table 7.1. Important Members of the SqlConnection Class Member | Type | Description | BeginTransaction() | Method | Starts a new transaction on this SqlConnection object | Close() | Method | Returns the SqlConnection object to the connection pool | ConnectionString | Property | Specifies the server to be used by this SqlConnection object | CreateCommand() | Method | Returns a new SqlCommand object that executes via this SqlConnection object | Open() | Method | Opens the connection to the database | The following code segment shows how to create a SqlConnection object: SqlConnection cnn = new SqlConnection(); cnn.ConnectionString = @"Data Source=(local)\NetSDK;" + "Initial Catalog=Northwind;Integrated Security=SSPI"; cnn.Open(); You should know how to construct a SQL Server connection string for use with the SqlConnection object. There are three parts to the string. First is the data source, which is the name of the server to which you want to connect. (local)\NetSDK is the name for the SQL Server instance installed with the .NET Framework QuickStart samples. Second is the initial catalog, which is the name of the database on the server to use. Third is authentication information. This can be either Integrated Security=SSPI for use with Windows authentication or User ID= username ;Password= password (this can be abbreviated as UID= username ;PWD= password ) for use with SQL Server authentication. Other optional parameters exist, but these three are the most important. | Establishing database connection is one of the slowest database operations. Therefore, ADO.NET supports automatic connection pooling to increase performance. When you call the Close() method of a SqlConnection object, the connection is returned to a connection pool. Connections in a pool are not immediately destroyed by ADO.NET. Instead, they're available for reuse if another part of an application requests a SqlConnection object that has the exact same connection string as a previously closed SqlConnection object. | The SqlCommand and SqlParameter Classes The SqlCommand class represents something that can be executed, such as an ad hoc query string or a stored procedure name. The SqlParameter class, on the other hand, represents a single parameter to a stored procedure. Table 7.2 details the most important members of the SqlCommand class. Table 7.2. Important Members of the SqlCommand Class Member | Type | Description | CommandText | Property | Specifies the statement to be executed by the SqlCommand object | CommandType | Property | Indicates which type of command this SqlCommand object represents | Connection | Property | Represents the SqlConnection object through which this SqlCommand object executes | CreateParameter() | Method | Creates a new SqlParameter object for this SqlCommand object | ExecuteNonQuery() | Method | Executes a SqlCommand object that does not return a result set | ExecuteReader() | Method | Executes a SqlCommand object and places the results in a SqlDataReader object | ExecuteScalar() | Method | Executes a SqlCommand object and returns the first column of the first row of the result set | ExecuteXmlReader() | Method | Executes a SqlCommand object and places the results in an XmlReader object | Parameters | Property | Contains a collection of SqlParameter objects for this SqlCommand object | The SqlDataReader Class The SqlDataReader class is designed to be the fastest possible way to retrieve a result set from a database. SqlDataReader objects can be constructed by calling the ExecuteReader() method of a SqlCommand object. The result set contained in a SqlDataReader object is forward-only and read-only. That is, you can only read the rows in the result set sequentially from start to finish, and you can't modify any of the data: // Connect to the database SqlConnection cnn = new SqlConnection(); cnn.ConnectionString = @"Data Source=(local)\NetSDK;" + "Initial Catalog=Northwind;Integrated Security=SSPI"; // Create a new ad hoc query to retrieve customer names SqlCommand cmd = cnn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = "SELECT CompanyName FROM Customers ORDER BY CompanyName"; // Dump the data to the user interface cnn.Open(); SqlDataReader dr = cmd.ExecuteReader(); while (dr.Read()) lbCustomers.Items.Add(dr.GetString(0)); // Clean up dr.Close(); cnn.Close(); | The SqlDataReader object makes exclusive use of its SqlConnection object as long as it is open. You can't execute any other SqlCommand objects on that connection as long as the SqlDataReader object is open. Therefore, you should always call the SqlDataReader.Close() method as soon as you're finished retrieving data. | | You can improve the code's performance even more by using a stored procedure instead of an ad hoc query to retrieve the data. | Table 7.3 shows the most important members of the SqlDataReader class. There's no need to memorize all the members (and the others that aren't shown in this table), but you should understand the patterns they represent. Table 7.3. Important Members of the SqlDataReader Class Member | Type | Description | Close() | Method | Closes the SqlDataReader object | GetBoolean() | Method | Gets a Boolean value from the specified column | GetByte() | Method | Gets a byte value from the specified column | GetChar() | Method | Gets a character value from the specified column | GetDateTime() | Method | Gets a date/time value from the specified column | GetDecimal() | Method | Gets a decimal value from the specified column | GetDouble() | Method | Gets a double value from the specified column | GetFloat() | Method | Gets a float value from the specified column | GetGuid() | Method | Gets a global unique identifier (GUID) value from the specified column | GetInt16() | Method | Gets a 16-bit integer value from the specified column | GetInt32() | Method | Gets a 32-bit integer value from the specified column | GetInt64() | Method | Gets a 64-bit integer value from the specified column | GetName() | Method | Gets the column name for the specified zero-based column ordinal | GetOrdinal() | Method | Gets the column ordinal for the specified column name | GetString() | Method | Gets a string value from the specified column | GetValue() | Method | Gets a value from the specified column | GetValues() | Method | Gets an entire row of data and places it in an array of objects | IsDbNull() | Method | Indicates whether a specified column contains a null value | Read() | Method | Loads the next row of data into the SqlDataReader object | The SqlDataAdapter Class The SqlDataAdapter class provides a bridge between the data provider and the dataset. You can think of the SqlDataAdapter object as a two-way pipeline between the data in its native storage format and the data in a more abstract representation (the DataSet object) that's designed for manipulation in an application. Table 7.4 details the most important members of the SqlDataAdapter class. Table 7.4. Important Members of the SqlDataAdapter Class Member | Type | Description | DeleteCommand | Property | Specifies the SqlCommand object used to delete rows from the data source | Fill() | Method | Transfers data from the data source to a DataSet object | InsertCommand | Property | Specifies the SqlCommand object used to insert rows into the data source | SelectCommand | Property | Specifies the SqlCommand object used to retrieve rows from the data source | Update() | Method | Transfers data from a DataSet object to the data source | UpdateCommand | Property | Specifies the SqlCommand object used to update rows in the data source | |