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 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. Similarly, the OracleConnection class in the System.Data.OracleClient namespace provides the functionality for connecting to an Oracle database. |
The SqlConnection Class SqlConnection represents a single persistent connection to a SQL Server data source. Table 2.1 shows the important members of the SqlConnection class. Table 2.1. Important Members of the SqlConnection ClassMember | 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 connection information 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. The string has three parts. First is the data source, which is the name of the server to which you want to connect. (local)\NetSDK is the name of 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 a 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 SqlCommand object. Table 2.2 details the most important members of the SqlCommand class. Table 2.2. Important Members of the SqlCommand ClassMember | 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 read the rows in the result set only sequentially from start to finish, and you can't modify any of the data. Follow these steps to use SqlDataReader in a program: Create the folder C:\EC70320. Open Internet Information Services from the Administrative Tools section of the Windows Control Panel. Select the Default Web Site node and then select Action, New, Virtual Directory. In the Virtual Directory Creation Wizard, set the alias as EC70320 and the directory as C:\EC70320. Select the default access permissions (to allow Read and Run Scripts) and click Next to complete the wizard. Open Visual Studio .NET and create a new blank solution named C02 at the location C:\EC70320. Add a new Visual C# ASP.NET Web Application project at the following location: http://localhost/EC70320/C02/Example2_1. Place a Button control with the ID of btnGetCustomers and a ListBox control with the ID of lbCustomers on the form. Enter the following using statement to the form's code: using System.Data.SqlClient; Enter this code to run when the user clicks the Button control: private void btnGetCustomers_Click(object sender, System.EventArgs e) { // 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(); } Run the project and click the button. The code connects to SQL Server and fills the ListBox control with a list of customers from the Northwind database. | 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 2.3 shows the most important members of the SqlDataReader class. Table 2.3. Important Members of the SqlDataReader ClassMember | 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 object. 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 2.4 details the most important members of the SqlDataAdapter class. Table 2.4. Important Members of the SqlDataAdapter ClassMember | 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 | |