|
|
< Day Day Up > |
|
11.3. ADO.NET Connected ModelAs 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梬hich 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 ClassesThere are multiple connection classes in ADO.NET梕ach 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.
Core Note
Connection StringThe 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.
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.
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.
Core Note
Connection PoolingCreating a connection is a time-consuming process梚n 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:
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 ObjectAfter a connection object is created, the next step in accessing a database梖or the connected model梚s 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 ObjectYou 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梖or example, only SqlCommand has an ExecuteXmlReader method. Executing a CommandThe SQL command assigned to the CommandText property is executed using one of the four command methods in Table 11-4.
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:
Executing Stored Procedures with the Command ObjectA 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梩he name is unimportant梩hat 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 ProceduresAn 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 ObjectAs 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 DataReaderA 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
Accessing Column Values with DataReaderThere 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
|
|
|
< Day Day Up > |
|
