Data Provider Classes

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.

graphics/alert_icon.gif

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.

graphics/alert_icon.gif

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(); 
graphics/alert_icon.gif

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.


graphics/alert_icon.gif

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



MCAD Developing and Implementing Web Applications with Visual C#. NET and Visual Studio. NET (Exam [... ]am 2)
MCAD Developing and Implementing Web Applications with Visual C#. NET and Visual Studio. NET (Exam [... ]am 2)
ISBN: 789729016
EAN: N/A
Year: 2005
Pages: 191

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net