11.3. ADO.NET Connected Model

As described earlier, the connected model is based on establishing a connection to a database and then using commands to update, delete, or read data on the connected source. The distinguishing characteristic of this model is that commands are issued directly to the data source over a live connection which remains open until the operations are complete. Whether working with a connected or disconnected model, the first step in accessing a data source is to create a connection object to serve as a communications pathway between the application and database.

Connection Classes

There are multiple connection classes in ADO.NET each specific to a data provider. These include SqlConnection, OracleConnection, OleDBConnection, and OdbcConnection. Although each may include custom features, ADO.NET compatibility requires that a connector class implement the IDbConnection interface. Table 11-1 summarizes the members defined by this interface.

Table 11-1. Members of the IDbConnection Interface

Category	Name	Description
Property	ConnectionString	Gets or sets the string used to connect to a data source.
Property	ConnectionTimeout	The number of seconds to wait while trying to establish a connection to a data source before timing out.
Property	Database	Name of the database associated with the current connection.
Property	State	Current state of the connection. Returns a ConnectionState enumeration name: Broken, Closed, Connecting, Executing, Fetching, or Open.
Method	Open Close	Opens a connection. Rolls back any pending operations and closes the connection returning it to a connection pool, if one is used.
Method	BeginTransaction	Initiates a database transaction.
Method	ChangeDatabase	Changes the current database for an open connection. The new database name is passed as string to the method.
Method	CreateCommand	Creates a command object to be associated with connection.

Core Note

Even though connection classes implement the IDbConnection interface, they do not necessarily have to provide meaningful functionality. For example, the OracleConnection class does not support the ConnectionTimeOut, Database, or ChangeDatabase members.

Connection String

The connection string specifies the data source and necessary information required to access the data source, such as password and ID. In addition to this basic information, the string can include values for fields specific to a data provider. For example, a SQL Server connection string can include values for Connection Timeout and Packet Size (size of network packet).

Table 11-2 offers a representative list of commonly used connection strings.

Table 11-2. Connection String Examples

Connection Type	Description and Use
SqlConnection Using SQL Server authentication.	"server=MYSERVER; uid=filmsadmin; pwd=bogart; database=films;" Or "Data Source=MYSERVER;User ID=filmsadmin; password=bogart;Initial Catalog=films;"
SqlConnection Using Windows authentication.	"server=MYSERVER; database=films; Trusted_Connection=yes"
OleDbConnection Connects to a Microsoft Access database.	"Provider=Microsoft.Jet.OLEDB.4.0; Data Source=c:\\movies.mdb;" For Internet applications, you may not be able to specify a physical path. Use MapPath to convert a virtual path into a physical path: string path= Server.MapPath("/data/movies.mdb"); Data Source="+path+";"
ODBC (DSN)	"DSN=movies;".

The connection string is used to create the connection object. This is typically done by passing the string to the constructor of the connection object.

 string cn= "Data Source=MYSERVER;Initial Catalog=films;             User Id=filmsadmin;Password=bogart;"; SqlConnection conn = new SqlConnection(cn); conn.Open();   // Open connection 

A connection string can also be built using a safer, object-oriented manner using one of the ConnectionStringBuilder classes supplied by a managed data provider.^[2] As this code demonstrates, the values comprising the connection string are assigned to properties of this class. Internally, the object constructs a string from these properties and exposes it as a ConnectionString property.

^[2] SqlClient, Oracle, OleDB, and ODBC implementations are available with ADO.NET 2.0.

 SqlConnectionStringBuilder sqlBldr = new       SqlConnectionStringBuilder(); scb.DataSource    = "MYSERVER"; // Or scp["Data Source"] = "MYSERVER"; sqlBldr.Password  = "bogart"; sqlBldr.UserID    = "filmsadmin"; sqlBldr.InitialCatalog = "films"; SqlConnection conn = new SqlConnection(sqlBldr.ConnectionString); conn.Open(); 

The ConnectionStringBuilder object is also useful for applications that input the connection string from a configuration file or other source. Setting the ConnectionString property to the connection string value exposes members that control the behavior of the connection. Table 11-3 lists selected properties of the SqlConnectionStringBuilder class.

Table 11-3. Selected Properties of the SqlConnectionStringBuilder Class

Method	Description
AsynchronousProcessing	Boolean value that indicates whether asynchronous process is permitted on the connection. The command object is responsible for making asynchronous requests.
ConnectionTimeout	Corresponds to the ConnectionTimeout property of the Connection object.
DataSource	Name or address of the SQL Server to connect to.
MaxPoolSize MinPoolSize	Sets or returns the maximum and minimum number of connections in the connection pool for a specific connection string.
Password	Password for accessing SQL Server account.
Pooling	A boolean value that indicates whether connection pooling is used.
UserID	User ID required to access a SQL Server account.

Core Note

For demonstration purposes, the connection strings in these examples are shown as cleartext within the application's code. In reality, a connection string should be stored outside of an application's assembly. For Web applications, the Web.Config file is often a reasonable choice. As described in Chapter 17, "The ASP.NET Application Environment," .NET includes a special configuration section to hold connection strings and supports techniques to encrypt the configuration information.

Desktop applications that access a central database can store the information on the client's machine (in the registry or a configuration file) or have it downloaded as part of an application's startup. The latter approach provides better scalability and security, particularly if the server returns a connection object rather than the string.

Connection Pooling

Creating a connection is a time-consuming process in some cases taking longer than the subsequent commands take to execute. To eliminate this overhead, ADO.NET creates a pool of identical connections for each unique connection string request it receives. This enables future requests with that connection string to be satisfied from the pool, rather than by reconnecting to the server and performing the overhead to validate the connection.

There are several rules governing connection pooling that you should be aware of:

• Connection pooling is turned on by default. It can be disabled for a SqlConnection by including "Pooling=false" in the connection string; an OleDbConnection requires "OLE DB Services=-4".

• Each connection pool is associated with a distinct connection string. When a connection is requested, the pool handler compares the connection string with those of existing pools. If it matches, a connection is allocated from the pool.

• If all connections in a pool are in use when a request is made, the request is queued until a connection becomes free. Connections are freed when the Close or Dispose method on a connection is called.

• The connection pool is closed when all connections in it are released by their owners and have timed out.

Under SQL Server, you control the behavior of connection pooling by including key-value pairs in the connection string. These keywords can be used to set minimum and maximum numbers of connections in the pool, and to specify whether a connection is reset when it is taken from the pool. Of particular note is the Lifetime keyword that specifies how long a connection may live until it is destroyed. This value is checked when a connection is returned to the pool. If the connection has been open longer than its Lifetime value, it is destroyed.

This code fragment demonstrates the use of these keywords for SqlClient:

 cnString = "Server=MYSERVER;Trusted_Connection=yes;       database=films;"          +       "connection reset=false;" +       "connection Lifetime=60;" +  // Seconds       "min pool size=1;"        +       "max pool size=50";           // Default=100 SqlConnection conn = new SqlConnection(cnString); 

The Command Object

After a connection object is created, the next step in accessing a database for the connected model is to create a command object that submits a query or action command to a data source. Command classes are made available by data providers and must implement the IDbCommand interface.

Creating a Command Object

You can use one of its several constructors to create a command object directly, or use the ProviderFactory approach mentioned in Section 11.1.

This segment demonstrates how to create a command object and explicitly set its properties:

 SqlConnection conn = new SqlConnection(connstr); Conn.open(); string sql = "insert into movies(movie_Title,movie_Year,         movie_Director) values(@title,@yr,@bestpicture)"; SqlCommand cmd = new SqlCommand(); // Assign connection object and sql query to command object cmd.Connection = conn; cmd.commandText = sql; // Fill in parameter values in query // This is recommended over concatenation in a query string cmd.Parameters.AddWithValue ("@title", "Schindler's List"); cmd.Parameters.AddWithValue ("@yr", "1993"); cmd.Parameters.AddWithValue ("@bestpic", "Y"); 

In situations where multiple data providers may be used, a provider factory provides a more flexible approach. The factory is created by passing its constructor a string containing the data provider. The factory's CreateCommand method is used to return a command object.

 string provider = "System.Data.SqlClient"; DBProviderFactory factory =    DbProviderFactories.GetFactory(provider); DbCommand cmd   = factory.CreateCommand(); cmd.CommandText = sql;     // Query or command cmd.Connection  = conn;    // Connection object 

Note that DbCommand is an abstract class that implements the IDbCommand interface. It assumes the role of a generic command object. This can eliminate the need to cast the returned command object to a specific provider's command object such as SqlCommand. However, casting is required if you need to access custom features of a provider's command class for example, only SqlCommand has an ExecuteXmlReader method.

Executing a Command

The SQL command assigned to the CommandText property is executed using one of the four command methods in Table 11-4.

Table 11-4. Command Executexxx Methods

Method	Description
ExecuteNonQuery	Executes an action query and returns the number of rows affected: cmd.CommandText = "DELETE movies WHERE movie_ID=220"; int ct = cmd.ExecuteNonQuery();
ExecuteReader	Executes a query and returns a DataReader object that provides access to the query's resultset. This method accepts an optional CommandBehavior object that can improve execution efficiency. cmd.CommandText="SELECT * FROM movies WHERE movie_year > '1945'; SqlDataReader rdr= cmd.ExecuteReader();
ExecuteScalar	Executes a query and returns the value of the first column in the first row of the resultset as a scalar value. cmd.CommandText="SELECT COUNT(movie_title) FROM movies"; int movieCt = (int) cmd.ExecuteScalar();
ExecuteXmlReader	Available for SQL Server data provider only. Returns an XmlReader object that is used to access the resultset. XmlReader is discussed in Chapter 10, "Working with XML in .NET."

The ExecuteReader method is the most important of these methods. It returns a DataReader object that exposes the rows returned by the query. The behavior of this method can be modified by using its overload that accepts a CommandBehavior type parameter. As an example, the following statement specifies that a single row of data is to be returned:

 rdr = cmd.ExecuteReader(sql, CommandBehavior.SingleResult); 

Some data providers take advantage of this parameter to optimize query execution. The list of values for the CommandBehavior enumeration includes the following:

• SingleRow. Indicates that the query should return one row. Default behavior is to return multiple resultsets.

• SingleResult. The query is expected to return a single scalar value.

• KeyInfo. Returns column and primary key information. It is used with a data reader's GetSchema method to fetch column schema information.

• SchemaOnly. Used to retrieve column names for the resultset. Example:

   dr=cmd.ExecuteReader(CommandBehavior.SchemaOnly); string col1= dr.GetName(0);  // First column name 

  
  

• SequentialAccess. Permits data in the returned row to be accessed sequentially by column. This is used with large binary (BLOB) or text fields.

• CloseConnection. Close connection when reader is closed.

Executing Stored Procedures with the Command Object

A stored procedure is a set of SQL code stored in a database that can be executed as a script. It's a powerful feature that enables logic to be encapsulated, shared, and reused among applications. ADO.NET supports the execution of stored procedures for OleDb , SqlClient, ODBC, and OracleClient data providers.

Executing a stored procedure is quite simple: set the SqlCommand.CommandText property to the name of the procedure; set the CommandType property to the enumeration CommandType.StoredProcedure; and then call the ExecuteNonQuery method.

 cmd.CommandText = "SP_AddMovie";  // Stored procedure name cmd.CommandType = CommandType.StoredProcedure; cmd.ExecuteNonQuery(); 

When a stored procedure contains input or output parameters, they must be added to the command object's Parameters collection before the procedure is executed. To demonstrate, let's execute the stored procedure shown in Listing 11-3. This procedure allows records to be fetched from the movies table as pages containing 10 rows of data. Input to the procedure is the desired page; the output parameter is the total number of pages available. This code fragment illustrates how parameters are set and how the procedure is invoked to return the first page:

 SqlCommand cmd  = new SqlCommand(); cmd.CommandText = "SPMOVIES_LIST";  // Stored procedure name cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(@PageRequest", SqlDbType.Int); cmd.Parameters.Add(@TotalPages", SqlDbType.Int); cmd.Parameters[0].Direction= ParameterDirection.Input; cmd.Parameters[0].Value= 1;        // Retrieve first page cmd.Parameters[1].Direction=ParameterDirection.Output; cmd.CommandTimeout=10;  // Give command 10 seconds to execute SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()){ //   do something with results } rdr.Close();  // Must close before reading parameters int totpages=  cmd.Parameters[1].Value; 

This example uses the SqlClient data provider. With a couple of changes, OleDb can be used just as easily. The primary difference is in the way they handle parameters. SqlClient requires that the parameter names match the names in the stored procedure; OleDb passes parameters based on position, so the name is irrelevant. If the procedure sends back a return code, OleDB must designate the first parameter in the list to handle it. SqlClient simply adds a parameter the name is unimportant that has its direction set to ReturnValue.

Listing 11-3. Stored SQL Server Procedure to Return a Page of Records

 CREATE PROCEDURE SPMOVIES_LIST    @PageRequest int,    @TotalPages int output AS    /*       Procedure to return a resultset of movies ordered       by title.       Resultset contains 10 movies for the specified page.    */    SET NOCOUNT ON    select @TotalPages = CEILING(COUNT(*)/10) from movies    if @PageRequest = 1 or @PageRequest <1    begin       select top 10 * from movies order by movie_Title       set @PageRequest = 1       return 0    end    begin       if @PageRequest > @TotalPages          set @PageRequest = @TotalPages       declare @RowCount int       set @RowCount = (@PageRequest * 10)       exec ('SELECT * FROM       (SELECT TOP 10 a.* FROM       (SELECT TOP ' + @RowCount + ' * FROM movies ORDER BY           movie_Title) a        ORDER BY movie_Title desc) b        ORDER BY Movie_Title')        return 0    end 

Using Parameterized Commands Without Stored Procedures

An earlier example (see "Creating a Command Object" on page 511) used this statement to create a SQL command to store a movie in the Films database:

 string sql = "insert into movies(movie_Title,movie_Year,       bestpicture) values(@title,@yr,@bestpic)"; // Parameters set values to be stored cmd.Parameters.AddWithValue ("@title", "Schindler's List"); cmd.Parameters.AddWithValue ("@yr", "1993"); cmd.Parameters.AddWithValue ("@bestpic", "Y"); 

The alternative, which uses concatenation, looks like this:

 string title = "Schindler''s List";  // Two single quotes needed string yr = "1993"; string pic = "Y"; sql = "insert into movies(movie_Title,movie_Year,       bestpicture) values"; sql += "('"+title+"',"+yr+",'"+pic+"') "; 

Not only is the parameterized version more readable and less prone to syntactical error, but it also provides a significant benefit: It automatically handles the problem of placing double single quotes ('') in a SQL command. This problem occurs when attempting to store a value such as O'Quinn, which has an embedded quote that conflicts with SQL syntax. Parameters eliminate the usual approach to search each string and replace an embedded single quote with a pair of single quotes.

DataReader Object

As we have seen in several examples, a DataReader exposes the rows and columns of data returned as the result of executing a query. Row access is defined by the IDataReader interface that each DataReader must implement; column access is defined by the IDataRecord interface. We'll look at the most important members defined by these interfaces as well as some custom features added by data providers.

Accessing Rows with DataReader

A DataReader returns a single row from a resultset each time its Read method is executed. If no rows remain, the method returns false. The reader should be closed after row processing is completed in order to free system resources. You can check the DataReader.IsClosed property to determine if a reader is closed.

Although a DataReader is associated with a single command, the command may contain multiple queries that return multiple resultsets. This code fragment demonstrates how a DataReader processes the rows returned by two queries.

 string q1 = "SELECT * FROM movies WHERE movie_Year < 1940"; string q2 = "SELECT * FROM movies WHERE movie_Year > 1980"; cmd.CommandText = q1 + ";" + q2; DbDataReader rdr = cmd.ExecuteReader(); bool readNext = true; while (readNext) {    while (rdr.Read())    {       MessageBox.Show(rdr.GetString(1));    }    readNext = rdr.NextResult(); // Another resultset? } rdr.Close(); conn.Close(); 

The two things to note are the construction of the CommandString with multiple queries and the use of the Nextresult method to determine if results from another query are present.

Core Note

The DataReader has no property or method that provides the number of rows returned in its resultset. Because data is received one row at a time, the resultset could be altered by additions and deletions to the database as records are read. However, there is a HasRows property that returns TRue if the data reader contains one or more rows.

Accessing Column Values with DataReader

There are numerous ways to access data contained in the columns of the current DataReader row: as an array with column number (zero-based) or name used as an index; using the GetValue method by passing it a column number; and using one of the strongly typed Getxxx methods that include GetString, GetInt32, GetdateTime, and Getdouble. The following code segment contains an example of each technique:

 cmd.CommandText="SELECT movie_ID, movie_Title FROM movies"; rdr = cmd.ExecuteReader(); rdr.Read(); string title; // Multiple ways to access data in a column title = rdr.GetString(1); title = (string)rdr.GetSqlString(1); // SqlClient provider title = (string)rdr.GetValue(1); title = (string)rdr["movie_Title"];  // Implicit item title = (string)rdr[1];              // Implicit item 

The GetString method has the advantage of mapping the database contents to a native .NET data type. The other approaches return object types that require casting. For this reason, use of the Get methods is recommended. Note that although GetString does not require casting, it does not perform any conversion; thus, if the data is not of the type expected, an exception is thrown.

Many applications rely on a separate data access layer to provide a DataReader. In such cases, the application may require metadata to identify column names, data types, and other columnar information. Column names, which are useful for generating report headings, are readily available through the GetName method:

 // List column names for a DataReader DbDataReader rdr = GetReader();        // Get a DataReader for (int k = 0; k < rdr.FieldCount; k++) {    Console.WriteLine(rdr.GetName(k));  // Column name } rdr.Close(); 

Complete column schema information is available through the GetSchemaTable method. It returns a DataTable in which there is one row for each field (column) in the resultset. The columns in the table represent schema information. This code segment demonstrates how to access all the column information for a resultset. For brevity, only three of the 24 columns of information are shown:

 DataTable schemaTable = rdr.GetSchemaTable(); int ict = 0; foreach (DataRow r in schemaTable.Rows) {    foreach (DataColumn c in schemaTable.Columns){       Console.WriteLine(ict.ToString()+"                         "+c.ColumnName + ": "+r[c]);       ict++;    } } // Selected Output: //       0 ColumnName: movie_ID //       1 ColumnOrdinal: 0 //       12 DataType: System.Int32