ADO.NET in a Nutshell | O'Reilly Media

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

ADO.NET is a new programming model built upon the .NET Framework, sharing a common type system, design patterns and naming conventions. The stated goals of ADO.NET are to:

Provide a disconnected (offline) data architecture in addition to supporting connected operation
Integrate tightly with XML
Interact with a variety of data sources through a common data representation
Optimize data source access

ADO.NET is designed to provide consistent access to data sources. This is accomplished through ADO.NET data providers that provide methods for connecting to data sources as well as retrieving, manipulating, and updating data in both connected and disconnected environments.

An ADO.NET data provider connects to a data source such as SQL Server, Oracle, or an OLE DB data source, and provides a way to execute commands against that data source in a consistent manner that is independent of the data source and data source-specific functionality. However, aside from a core set of similar capabilities, there is no guarantee that identical functionality will be available in each data provider. This is due to differences between data sources (for example, SQL Server provides many more capabilities than Access) and provider implementations (for example, both Microsoft and Oracle offer ADO.NET providers for Oracle's data server with slight implementation differences).

A complete .NET data provider includes the following classes:

Connection: Connects to the data source.
Command

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

An ADO.NET data provider connects to a data source such as SQL Server, Oracle, or an OLE DB data source, and provides a way to execute commands against that data source in a consistent manner that is independent of the data source and data source-specific functionality. However, aside from a core set of similar capabilities, there is no guarantee that identical functionality will be available in each data provider. This is due to differences between data sources (for example, SQL Server provides many more capabilities than Access) and provider implementations (for example, both Microsoft and Oracle offer ADO.NET providers for Oracle's data server with slight implementation differences).

A complete .NET data provider includes the following classes:

Connection: Connects to the data source.
Command: Executes commands against the data source.
DataReader: A forward-only, read-only connected result set.
ParameterCollection: Stores all parameters related to a Command and the mappings of both table and column names to the DataSet columns.
Parameter: Defines parameters for parameterized SQL statements and stored procedures.
Transaction

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As mentioned earlier, ADO.NET supports two different programming environments: connected and disconnected.

The connected environment provides forward-only, read-only access to data in the data source and the ability to execute commands against the data source. The connected classes provide a common way to work with connected data regardless of the underlying data source. They include Connection, Command, DataReader, Transaction, ParameterCollection, and Parameter classes.

The disconnected environment allows data retrieved from the data source to be manipulated and later reconciled with the data source. The disconnected classes provide a common way to work with disconnected data regardless of the underlying data source. They include the DataSet, DataTable, DataColumn, DataRow, Constraint, DataRelationship, and DataView classes.

Finally, ADO.NET introduces the connected DataAdapter class to bridge the data source and disconnected classes by way of the connected classes. The DataAdapter is an abstraction of the connected classes that simplifies filling the disconnected DataSet or DataTable classes with data from the data source and updating the data source to reflect any changes made to the disconnected data. Figure 1-1 shows the relationship between the connected and disconnected classes in ADO.NET.

Figure 1-1: The connected and disconnected ADO.NET classes

The following classes are used by ADO.NET to communicate directly with the data source:

Connection: Maintains information required to connect to the data source through a connection string. The connection string contains information such as the name of the data source and its location, and authorization credentials and settings. The

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The .NET Framework 1.0 ships with the Microsoft SQL Server .NET data provider and OLE DB .NET data provider. The .NET Framework 1.1 also includes both the Oracle and ODBC .NET data providers. A .NET data provider connects to the data source and executes commands, either to retrieve results or to modify the data in the data source. It is possible to create a .NET data provider for practically any data source: Exchange servers, XML documents, and SQL databases other than those .NET supports out of the box. Figure 2-1 shows the relationship between the ADO.NET data providers that ship with .NET, the data sources that they access, and the disconnected ADO.NET classes.

Figure 2-1: ADO.NET data providers

The most commonly used .NET data providers are described in the following sections.

The SQL Server .NET data provider ships with the .NET Framework. It uses the Tabular Data Stream (TDS) protocol to send requests to and receive responses from the SQL Server. This provider delivers very high performance because TDS is a fast protocol that can access Microsoft SQL Server directly without an OLE DB or ODBC layer and without COM interop. The SQL Server .NET data provider can be used with Microsoft SQL Server 7.0 or later. To access earlier versions of Microsoft SQL Server, the OLE DB .NET data provider with the SQL Server OLE DB provider (SQLOLEDB) should be used. The SQL Server .NET data provider classes are located in the System.Data.SqlClient namespace.

The OLE DB .NET data provider ships with the .NET Framework. It communicates with a data source using a data source-specific OLE DB provider through COM interop. The OLE DB provider, in turn, communicates directly with the data source using native OLE DB calls.

The OLE DB .NET data provider supports OLE DB interfaces later than Version 2.5. As a result, some OLE DB providers, including those for Microsoft Exchange Server and Internet Publishing, aren't supported. Also, the OLE DB .NET data provider can't be used with the OLE DB provider for ODBC (MSDASQL). To access ODBC data, use the ODBC .NET data provider discussed later in this chapter.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The most commonly used .NET data providers are described in the following sections.

The SQL Server .NET data provider ships with the .NET Framework. It uses the Tabular Data Stream (TDS) protocol to send requests to and receive responses from the SQL Server. This provider delivers very high performance because TDS is a fast protocol that can access Microsoft SQL Server directly without an OLE DB or ODBC layer and without COM interop. The SQL Server .NET data provider can be used with Microsoft SQL Server 7.0 or later. To access earlier versions of Microsoft SQL Server, the OLE DB .NET data provider with the SQL Server OLE DB provider (SQLOLEDB) should be used. The SQL Server .NET data provider classes are located in the System.Data.SqlClient namespace.

The OLE DB .NET data provider ships with the .NET Framework. It communicates with a data source using a data source-specific OLE DB provider through COM interop. The OLE DB provider, in turn, communicates directly with the data source using native OLE DB calls.

The OLE DB .NET data provider supports OLE DB interfaces later than Version 2.5. As a result, some OLE DB providers, including those for Microsoft Exchange Server and Internet Publishing, aren't supported. Also, the OLE DB .NET data provider can't be used with the OLE DB provider for ODBC (MSDASQL). To access ODBC data, use the ODBC .NET data provider discussed later in this chapter.

The OLE DB.NET data provider classes are located in the System.Data.OleDb namespace.

The ODBC .NET data provider is installed as an add-in component to the .NET Framework Version 1.0 and ships with the .NET Framework Version 1.1. The provider communicates with the data source using native ODBC drivers through COM interop.

The following ODBC drivers are guaranteed compatible with the ODBC .NET data provider:

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Microsoft SQL Server .NET data provider is recommended for all applications using Microsoft SQL Server 7.0 or later, or Microsoft Data Engine (MSDE) applications.

The Microsoft OLE DB .NET data provider is recommended for applications using Microsoft SQL Server 6.5 and earlier with the OLE DB provider for SQL Server (SQLOLEDB). It is also recommended for applications using Microsoft Access databases. Additionally, it can access any data source that has an OLE DB provider that supports OLE DB interface greater than Version 2.5. However, if a data source-specific provider is available, that is likely to be a preferred solution.

The ODBC .NET data provider can be used for any data source that is accessible through ODBC but doesn't have either a .NET data provider, an OLE DB provider, or a vendor-supplied .NET data provider.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

ADO.NET provides a set of interfaces that allow you to build a custom .NET data provider. While most DBMSs can be accessed through a specific .NET data provider or through the OLE DB .NET data provider, some reasons to implement a custom data provider include:

To access proprietary data sources that have neither a specific .NET data provider nor an OLE DB provider that can be accessed through the .NET OLE DB data provider.
To expose specific functionality of the data source that is accessed through a general-purpose provider. For example, a database that is accessed through the OLE DB .NET data provider might have functionality that isn't available through that provider. A custom data provider can be written to expose the database-specific functionality.
To provide application-specific data access architecture to improve performance, simplify programming, and improve maintainability.

An alternative to writing a custom data provider for a proprietary data source is to write an OLE DB provider for the data source and use the OLE DB .NET data provider to access the data through that OLE DB provider. This approach might make sense in situations when broad access to a full set of database features is required. Once the OLE DB provider is written, the data source can also be accessed not only with the OLE DB .NET data provider but by any application or tool that supports OLE DB provider data access.

A custom .NET data provider must at least support the DataSet through the IDataAdapter interface, and possibly the IDataParameter interface for parameterized queries. Such a minimal data provider allows a DataSet to be loaded with data from the data source, the modification of data within the DataSet, and the reconciliation of the changed DataSet with the data source. A minimal provider can support clients that deal primarily with a disconnected data, thereby functioning as a bridge between the

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Before you can perform any task with an ADO.NET data source, you need to open a connection. In ADO.NET, this means creating and using a Connection object. Connection objects are one of the simplest components in ADO.NET, but they encapsulate a fair bit of lower-level functionality, including user authentication information, a connection pooling mechanism, and a network connection (assuming the data source is located on a separate computer).

In this chapter, we'll examine the basics of the ADO.NET Connection object and the connection string settings you can configure. We'll also consider some finer points, including connection pooling—a key to highly scalable database applications—and connection events.

The Connection object, like all provider-specific ADO.NET objects, comes in more than one version. You use the version that's tailored for your specific data source. Here are two examples:

System.Data.SqlClient.SqlConnection allows you to connect to a SQL Server database (Version 7.0 or later).
System.Data.OleDb.OleDbConnection allows you to connect to almost any data source with an associated OLE DB provider.

Every Connection object that accesses relational databases implements the common System.Data.IDbConnection interface. By looking at the IDbConnection interface, you'll quickly see the small set of properties and methods that every Connection object is guaranteed to support (see Tables 3-1 and 3-2). The most important of these are the Close( ) and Open( ) methods, and the ConnectionString property, which specifies a variety of options about the data source and how to connect to it. All IDbConnection properties are read-only, except ConnectionString.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Connection object, like all provider-specific ADO.NET objects, comes in more than one version. You use the version that's tailored for your specific data source. Here are two examples:

System.Data.SqlClient.SqlConnection allows you to connect to a SQL Server database (Version 7.0 or later).
System.Data.OleDb.OleDbConnection allows you to connect to almost any data source with an associated OLE DB provider.

Every Connection object that accesses relational databases implements the common System.Data.IDbConnection interface. By looking at the IDbConnection interface, you'll quickly see the small set of properties and methods that every Connection object is guaranteed to support (see Tables 3-1 and 3-2). The most important of these are the Close( ) and Open( ) methods, and the ConnectionString property, which specifies a variety of options about the data source and how to connect to it. All IDbConnection properties are read-only, except ConnectionString.

Table 3-1: IDbConnection properties
Member	Description
ConnectionString	A string with name-value pairs of connection settings. These settings often include information such as the user to log in and the location of the database server. This is the only writeable property.
ConnectionTimeout	The time to wait for a connection to open before failing with a provider-specific exception (such as

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When creating a connection, you must specify several pieces of required information. Typically, this includes the type of authentication or user to authenticate, the location of the database server, and the name of the database. In addition, OLE DB connection strings specify an OLE DB provider, and ODBC connection strings specify an ODBC driver. To specify this information, use the ConnectionString property.

The ConnectionString contains a series of name/value settings delimited by semicolons (;). The order of these settings is unimportant, as is the capitalization. Taken together, they specify the information needed to create a connection. Table 3-3 describes some settings you can use. Parameters that are used for connection pooling are omitted; they are discussed later in this chapter.

Connection strings are data source-specific, although they tend to have broad similarities. Most parameters in Table 3-2 are supported by the SQL Server, OLE DB, and Oracle providers, although some exceptions apply. Consult the documentation for your particular database product or your OLE DB or ODBC driver for more information.

Table 3-3: Basic connection string parameters
Parameter	Description
AttachDBFilename / Initial File Name	Used only if you want to connect to an attachable database file (for example, an `.mdf` file that isn't registered with the database system). Normally, you use the `Initial Catalog` parameter instead.
Connect Timeout / Connection Timeout	The length of time (in seconds) to wait for a connection to the server before terminating the attempt and generating an error. Defaults to 15 seconds, and 0 seconds represents an infinite wait.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

You've now seen all the ingredients you need to create and use a connection. You simply create the Connection object required for your data source, apply the appropriate connection string settings, and open the connection. In Example 3-1, a connection is created to a SQL Server database on the local computer using integrated authentication. The code opens the connection, tests its state, and closes it.

Example 3-1. Opening and testing a connection

// ConnectionTest.cs - Opens and verifies a connection

using System;
using System.Data.SqlClient;

public class ConnectionTest
{
    public static void Main() 
    {
        SqlConnection con = new SqlConnection("Data Source=localhost;" +
               "Initial Catalog=Northwind;Integrated Security=SSPI");

        con.Open();
        Console.WriteLine("Connection is " + con.State.ToString());
    
        con.Close();
        Console.WriteLine("Connection is " + con.State.ToString());
    }
}

The output clearly indicates whether the connection test is successful:

Connection is Open
Connection is Closed

Connection objects expose only two events. The InfoMessage event can retrieve warnings and other messages from a data source. Generally, you use this event if you wish to receive specific information messages that don't correspond to errors (in SQL Server, these are messages with a severity of 10 or less). You can't use this event to be informed about errors, which simply causes the Connection object to throw an exception.

The message information is wrapped into a provider-specific EventArgs object, such as OleDbInfoMessageEventArgs or SqlInfoMessageEventArgs.

You can examine this object for the error number and message text, as well as the additional provider-specific information. For example, SQL Server provides information about the database, stored procedure, and line number where the message originated.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Connection pooling recycles a set of open connections to save time—a key requirement in enterprise-level database applications. Without connection pooling, your application might be able to support a large throughput but will provide poor scalability. For example, you might find that your system can easily handle 10 simultaneous clients performing 1,000 transactions/minute but falters with 1,000 clients performing 10 transactions/minute, even though the overall transaction throughput is the same.

The problem is that acquiring a database connection automatically imposes some overhead. This is because the process of establishing a connection requires several lower-level operations, including a verification of security credentials. If your system is characterized by a large number of clients that frequently connect, perform a single operation, and then disconnect, the overhead required to create connections can become a crippling bottleneck. This is typical in a large stateless distributed application, such as an ASP.NET web site or web service.

To counteract this effect, most database access frameworks support connection pooling. Connection pooling works by retaining a pool of available connections. When a client requests a connection, it's served directly from the available pool, rather than recreated. In a large system with a quick turnover of database connections, this automatic reuse can save CPU cycles and network bandwidth.

ADO.NET doesn't include a connection-pooling mechanism. However, most ADO.NET providers do implement some form of connection pooling. In the case of the SQL Server and Oracle providers, this connection pooling mechanism is written entirely in managed code. In the case of the OLE DB and ODBC providers, this connection pooling depends on a lower level and has a few limitations. Most providers enable connection pooling automatically. However, you may be able to use connection string parameters to configure pool size settings.

All forms of connection pooling work by examining the connection string. A connection is reused only if the connection string matches exactly. Most ADO.NET providers use a case-sensitive full-text matching algorithm. This means that even if you have the same connection string parameters, but they are in a different order, the connections isn't reused. (The ODP .NET provider from Oracle is one exception.) To ensure that your connections can be reused, store the connection string in a single location (such as a configuration file) and don't enter it directly in your code.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Every interaction between a client and a data source, whether it is to retrieve information, delete a record, or commit a change, is governed by a Command object. In this chapter, we introduce the Command object in detail. You'll learn how to define a command and use it to execute nonquery commands such as direct record updates, insertions, and deletions. You'll also learn how to use parameterized commands and commands that access stored procedures.

The Command object is the heart of data processing with ADO.NET. Typically, the Command object wraps a SQL statement or a call to a stored procedure. For example, you might use a Command object to execute a SQL UPDATE, DELETE, INSERT, or SELECT statement. However, ADO.NET providers that don't represent databases may use their own nomenclature. The only rule is that the Command.CommandText property, which defines the command, must be a string.

As with the Connection object, the Command object is specific to the data provider. Two examples are:

System.Data.SqlClient.SqlCommand executes commands against SQL Server Version 7.0 or later.
System.Data.OleDb.OleDbCommand executes commands against an OLE DB data provider.

Each Command object implements the System.Data.IDbCommand interface. That means it is guaranteed to support the members shown in Tables 4-1 and 4-2. At a minimum, you must set the CommandText and a reference to a valid Connection before using a Command. In addition, you must modify the CommandType default value if you wish to invoke a stored procedure.

Table 4-1: IDbCommand properties
Member

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

The Command object is the heart of data processing with ADO.NET. Typically, the Command object wraps a SQL statement or a call to a stored procedure. For example, you might use a Command object to execute a SQL UPDATE, DELETE, INSERT, or SELECT statement. However, ADO.NET providers that don't represent databases may use their own nomenclature. The only rule is that the Command.CommandText property, which defines the command, must be a string.

As with the Connection object, the Command object is specific to the data provider. Two examples are:

System.Data.SqlClient.SqlCommand executes commands against SQL Server Version 7.0 or later.
System.Data.OleDb.OleDbCommand executes commands against an OLE DB data provider.

Each Command object implements the System.Data.IDbCommand interface. That means it is guaranteed to support the members shown in Tables 4-1 and 4-2. At a minimum, you must set the CommandText and a reference to a valid Connection before using a Command. In addition, you must modify the CommandType default value if you wish to invoke a stored procedure.

Table 4-1: IDbCommand properties
Member	Description
CommandText	Contains the SQL statement, stored procedure name, or table name. For an unusual provider (one that doesn't work with a database), this can contain something entirely different and proprietary; the only requirement is that is must be formatted as a string.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

When creating a Command object, you have the choice of several constructors. The most useful accepts a CommandText value and a Connection. Here's an example with the SqlCommand class:

SqlConnection con = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand(commandText, con);

For standard providers, there are three ways to execute a command: ExecuteNonQuery( ) , ExecuteReader( ), and ExecuteScalar( ). You choose one of these methods, depending on the type of command you are executing. For example, ExecuteReader( ) returns a DataReader and provides read-only access to query results. We examine the DataReader in Chapter 5.

Some providers include additional members. For example, the ADO.NET SQL Server provider includes an ExecuteXmlReader( ) method that retrieves data as an XML document. We'll examine this specialized version in Chapter 17, which considers ADO.NET's support for XML.

The SQL language includes several nonquery commands. The best known include UPDATE, DELETE, and INSERT. You can also use other commands to create, alter, or drop tables, constraints, relations, and so on. To execute any of these commands, just set the CommandText property with the full SQL statement, open a connection, and invoke the ExecuteNonQuery( ) method. The next sections consider examples that update, delete, and insert records.

Section 4.2.1.1: Updating a record

The UPDATE statement, at its simplest, uses the following syntax:

UPDATE table SET update_expression WHERE search_condition

The UPDATE expression can thus modify a single record, or it can apply a change to an entire batch of records in a single table. Example 4-1 puts the UPDATE statement to work with a simple command that modifies a single field in a single category record in the Northwind database.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In the examples shown so far, the SQL command text and the data values have been embedded in a single string. This approach is easy, and convenient for writing data access code. However, it also has significant drawbacks that make it unsuitable for a production-level application. These include inflexibility, poor performance, and potential security problems when using user-supplied values.

To overcome these problems, you need to use another feature of the Command object: parameters. Command parameters are conceptually the same as method parameters in an ordinary piece of .NET code. The most common type of parameter is an input parameter , which carries information from your application to the data source. You can use an input parameter when calling a stored procedure or when coding a parameterized query. In addition, you can use output parameters , which return information from the data source to your code, or bidirectional parameters , which transmit values in both directions. Output and bidirectional parameters are used only when you are making stored procedure calls.

Every Command object has an associated collection of Parameter objects (referenced by its Parameters property). The Parameter object is a provider-specific object, which means a SqlCommand uses a SqlParameter, an OleDbCommand uses an OleDbParameter, and so on.

In order to create a Parameter object, you must specify a parameter name, and the exact data type for the information it will contain. For the managed OLE DB provider, you specify data types using the System.Data.OleDb.OleDbType enumeration. For the SQL Server data provider, you use the System.Data.SqlDbType enumeration. If the data type is a variable-length field such as a string or binary field, you also need to indicate the field length.

For example, the following code snippet shows how to create a SqlParameter object named @MyParam

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Parameterized commands are executed in the same way as normal commands. They simply use placeholders to separate literal values from the query itself. For example, consider the following dynamically constructed command (used in Example 4-1):

UPDATE Categories SET CategoryName='Beverages'
  WHERE CategoryID=1

As a parameterized command with the SQL Server provider, it takes this form:

UPDATE Categories SET CategoryName=@CategoryName
  WHERE CategoryID=@CategoryID

You then add two Parameter objects to the Command, with the names @CategoryName and @CategoryID. Now set the values for both these Parameter objects to Beverages and 1, respectively, and invoke the command. Example 4-3 shows a full example that rewrites Example 4-1 to use a parameterized command.

Example 4-3. Updating a record with a parameterized command (SQL Server)

// ParameterizedUpdateSQL.cs - Updates a single Category record

using System;
using System.Data;
using System.Data.SqlClient;

public class UpdateRecord
{
    public static void Main() 
    {
        string connectionString = "Data Source=localhost;" +
                      "Initial Catalog=Northwind;Integrated Security=SSPI";
        string SQL = "UPDATE Categories SET CategoryName=@CategoryName " +
                     "WHERE CategoryID=@CategoryID";

        // Create ADO.NET objects.
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(SQL, con);

        SqlParameter param;
        param = cmd.Parameters.Add("@CategoryName", SqlDbType.NVarChar, 15);
        param.Value = "Beverages";

        param = cmd.Parameters.Add("@CategoryID", SqlDbType.Int);
        param.Value = 1;

        // Execute the command.
        con.Open();
        int rowsAffected = cmd.ExecuteNonQuery();
        con.Close();

        // Display the result of the operation.
        Console.WriteLine(rowsAffected.ToString() + " row(s) affected");
    }
}

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Stored procedures—SQL scripts stored in the database—are a key ingredient in any successful large-scale database applications. One advantage of stored procedures is improved performance. Stored procedures typically execute faster than ordinary SQL statements because the database can create, optimize, and cache a data access plan in advance. Stored procedures also have a number of other potential benefits. They:

Improve security. A client can be granted permissions to execute a stored procedure to add or modify a record in a specify way, without having full permissions on the underlying tables.
Are easy to maintain, because they are stored separately from the application code. Thus, you can modify a stored procedure without recompiling and redistributing the .NET application that uses it.
Add an extra layer of indirection, potentially allowing some database details to change without breaking your code. For example, a stored procedure can remap field names to match the expectations of the client program.
Reduce network traffic, because SQL statements can be executed in batches.

Of course, stored procedures aren't perfect. Most of their drawbacks are in the form of programming annoyances:

Using stored procedures in a program often involves importing additional database-specific details (such as parameter data types) into your code. You can control this problem by creating a dedicated component that encapsulates all your data access code.
Stored procedures are created entirely in the SQL language (with variations depending on the database vendor) and use script-like commands that are generally more awkward than a full-blown object-oriented language such as C# or VB .NET, particularly with respect to error handling and code reuse. Microsoft promises that the next version of SQL Server (code-named Yukon) will allow stored procedures to be written using .NET languages like C#.

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Most data-access code focuses on Data Manipulation Language (DML) commands. These instructions change, delete, or retrieve information about the values in tables. Typically, a database administrator creates the tables as part of a separate process, using a dedicated tool. This isn't always the case. Sometimes you want to create or modify table structure directly from your code. To do this, you need Data Definition Language (DDL) commands. SQL defines many basic commands various database vendors use to implement and sometimes extend. These include old standbys such as DROP TABLE and CREATE DATABASE.

ADO, the previous generation of data-access technology, had a sibling called ADOX that provided an object-oriented wrapper for the DDL commands. ADO.NET doesn't have any such niceties. However, you can still modify table structure or create new tables programmatically; you just have to do it the hard way, by constructing a Command and executing it with the ExecuteNonQuery( ) method. Example 4-9 shows a trivial example that uses the CREATE TABLE statement to create a single table with two columns. In this case, the table is created in the Northwind database because that's the initial database selected when the connection is opened.

Example 4-9. Inserting a new table programmatically

// DDL.cs - Inserts a new table

using System;
using System.Data.SqlClient;

public class UpdateRecord
{
    public static void Main() 
    {
        string connectionString = "Data Source=localhost;" +
                      "Initial Catalog=Northwind;Integrated Security=SSPI";
        string SQL = "CREATE TABLE Users ("+
                      "UserName nvarchar(20), Password nvarchar(20) )";


        // Create ADO.NET objects.
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(SQL, con);

        // Execute the command.
        con.Open();
        int rowsAffected = cmd.ExecuteNonQuery();
        con.Close();

        // Display the result of the operation.
        Console.WriteLine(rowsAffected.ToString() + " row(s) affected");
    }
}

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

In the previous chapter, you learned to execute simple nonquery commands to update the data source and retrieve calculated values. You can also use queries to fetch a set of rows from a data source in a single operation. In ADO.NET, there are two ways to use query commands: with the disconnected DataSet object, as discussed in later chapters, and with the DataReader, which is the focus of this chapter.

The DataReader is little more than a thin wrapper over a cursor that retrieves query results in a read-only, forward-only stream of information. The DataReader won't let you perform updates, see the results of live updates, or move back and forth through a result set as a server-side cursor does in traditional ADO programming. However, what you sacrifice in flexibility, you gain in performance. Because this cursor consumes few server resources and requires relatively little locking, the DataReader is always a performance-optimal way to retrieve data.

In this chapter, you'll learn how to use a DataReader to retrieve data and schema information, how to handle specialized data types such as binary large objects (BLOBs), and how to write code that can access any type of data source with the DataReader.

As with all connection-specific objects, there is a DataReader for every data provider. Here are two examples:

System.Data.SqlClient.SqlDataReader provides forward-only, read-only access to a SQL Server database (Version 7.0 or later).
System.Data.OleDb.OleDbDataReader provides forward-only, read-only access to a data source exposed through an OLE DB provider.

Every DataReader object implements the System.Data.IDataReader and the System.Data.IDataRecord interfaces. The IDataReader interface provides the core methods shown in Table 5-1, such as Read( ) , which retrieves a single row from the stream. The

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

As with all connection-specific objects, there is a DataReader for every data provider. Here are two examples:

System.Data.SqlClient.SqlDataReader provides forward-only, read-only access to a SQL Server database (Version 7.0 or later).
System.Data.OleDb.OleDbDataReader provides forward-only, read-only access to a data source exposed through an OLE DB provider.

Every DataReader object implements the System.Data.IDataReader and the System.Data.IDataRecord interfaces. The IDataReader interface provides the core methods shown in Table 5-1, such as Read( ) , which retrieves a single row from the stream. The IDataRecord interface provides the indexer for the DataReader and allows you to access the column values for the current row by column name or ordinal number.

Table 5-1: IDataReader methods
Member	Description
Close( )	Closes the `DataReader` but not the underlying `Connection`. This allows you to use the `Connection` for another task.
GetSchemaTable( )	Retrieves a `DataTable` object with information about the schema for the current result set.
NextResult( )	When executing a `Command` that returns multiple result sets, you must use `NextResult( )` to move from one result set to another. This method returns `true`

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

To retrieve records with a Command and DataReader, you need to use the SELECT statement, which identifies the table and rows you want to retrieve, the filter and ordering clauses, and any table joins:

SELECT columns FROM tables WHERE search_condition
       ORDER BY order_expression ASC | DESC

When writing a SELECT statement with a large table, you may want to limit the number of returned results to prevent your application from slowing down dramatically as the database grows. Typically, you accomplish this by adding a WHERE clause that limits the results.

Example 5-1 shows a sample Windows application that fills a list box with the results of a query. The designer code is omitted.

Example 5-1. Using a fast-forward DataReader

// DataReaderFillForm.cs - Fills a ListBox

using System;
using System.Windows.Forms;
using System.Data.SqlClient;

public class DataReaderTest : Form
{
    private ListBox lstNames;
    private string connectionString = "Data Source=localhost;" +
        "Initial Catalog=Northwind;Integrated Security=SSPI";

  public DataReaderTest()
  {
    lstNames = new ListBox();
    lstNames.Dock = DockStyle.Fill;
    Controls.Add(lstNames);
    Load += new EventHandler(DataReaderTest_Load);
  }

  public static void Main()
  {
    DataReaderTest t = new DataReaderTest();
    Application.Run(t);
  }
    private void DataReaderTest_Load(object sender, System.EventArgs e)
    {
        string SQL = "SELECT ContactName FROM Customers";

        // Create ADO.NET objects.
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(SQL, con);
        SqlDataReader r = null;

        // Execute the command.
        try
        {
            con.Open();
            r = cmd.ExecuteReader();

            // Iterate over the results.
            while (r.Read())
            {
                lstNames.Items.Add(r["ContactName"]);
            }
        }
        catch (Exception err)
        {
            MessageBox.Show(err.ToString());
        }
        finally
        {
            if (r != null) r.Close();
            con.Close();
        }
    }

}

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Using a command to execute a stored procedure query isn't much different from using one to execute a stored procedure that wraps a nonquery command such as INSERT, UPDATE, or DELETE.

The Northwind database includes a small set of stored procedure queries. One example is the CustOrderHist procedure, which returns the total number of products a given customer has ordered, grouped by product name.

Here's the SQL code to create the CustOrderHist stored procedure. It defines one parameter (shown in the first line), called @CustomerID:

CREATE PROCEDURE CustOrderHist @CustomerID nchar(5)
       AS
       SELECT ProductName, Total=SUM(Quantity)
       FROM Products P, [Order Details] OD, Orders O, Customers C
       WHERE C.CustomerID = @CustomerID AND
             C.CustomerID = O.CustomerID AND
             O.OrderID = OD.OrderID AND OD.ProductID = P.ProductID
       GROUP BY ProductName

GO

Example 5-5 executes this stored procedure for the customer "ALFKI" and displays the results in a console window.

Example 5-5. Using a stored procedure query

// TotalOrders.cs - Runs the CustOrderHist stored procedure.

using System;
using System.Data;
using System.Data.SqlClient;

public class TotalOrders
{
    public static void Main() 
    {
        string connectionString = "Data Source=localhost;" +
                   "Initial Catalog=Northwind;Integrated Security=SSPI";
        string procedure = "CustOrderHist";

        // Create ADO.NET objects.
        SqlConnection con = new SqlConnection(connectionString);
        SqlCommand cmd = new SqlCommand(procedure, con);
        SqlDataReader r;

        // Configure command and add parameters.
        cmd.CommandType = CommandType.StoredProcedure;
        SqlParameter param;
        param = cmd.Parameters.Add("@CustomerID", SqlDbType.NChar, 5);
        param.Value = "ALFKI";

        // Execute the command.
        con.Open();
        r = cmd.ExecuteReader();
        while (r.Read())
        {
            Console.WriteLine(r["Total"].ToString() + " of " + 
                              r["ProductName"].ToString());
        }

        con.Close();
    }
}

Additional content appearing in this section has been removed.
Purchase this book now or read it online at Safari to get the whole thing!

Content preview·Buy PDF of this chapter|Buy reprint rights for this chapter

Schema information is information about the structure of your data. It includes everything from column data types to table relations.

Schema information becomes extremely important when dealing with the ADO.NET DataSet, as you'll learn in the following chapters. However, even if you aren't using the DataSet, you may want to retrieve some sort of schema information from a data source. With ADO.NET, you have two choices: you can use the DataReader.GetSchemaTable( ) method to retrieve schema information about a specific query, or you can explicitly request a schema table from the data source.

As long as a DataReader is open, you can invoke its GetSchemaTable( ) method to return a DataTable object with the schema information for the result set. This DataTable will contain one row for each column in the result set. Each row will contain a series of fields with column information, including the data type, column name, and so on.

Example 5-6 shows code to retrieve schema information for a simple query.

Example 5-6. Retrieving the schema information for a query

// GetSchema.cs - Retrieves a schema table for a query

using System;
using System.Data;
using System.Data.SqlClient;

public class GetSchema
{
    public static void Main() 
    {
        string connectionString = "Data Source=localhost;" +
                     "Initial Catalog=Northwind;Integrated Security=SSPI";
        string SQL = "SELECT * FROM CUSTOMERS";

        // Create ADO.NET objects.

Table of Contents

Section 4.2.1.1: Updating a record