[ Team LiB ] Previous Section Next Section

11.1 Introduction to ADO.NET

The .NET data access layer is called ADO.NET and consists of two major ways of dealing with data. The first way, and the easiest for developers familiar with SQL, is implemented in terms of the IDataReader interface. The second way is the DataSet.

Out of the box, .NET Framework Version 1.0 provides implementations of IDataReader in System.Data.SqlClient.SqlDataReader (for SQL Server data sources) and System.Data.OleDb.OleDbDataReader (for OLE data sources). .NET 1.1 adds the System.Data.Odbc and System.Data.OracleClient namespaces for access to ODBC and Oracle databases, respectively.

In fact, most of the classes in the System.Data.OleDb and System.Data.SqlClient namespaces simply provide implementations of interfaces in the System.Data namespace, so I'll just refer to the interfaces by their interface names, such as IDataReader, until we get down to examples. If you want to learn more about ADO.NET, I suggest ADO.NET in a Nutshell, by Bill Hamilton and Matthew MacDonald (O'Reilly).


Before you can actually use the IDataReader to read data, you need to set up a connection to the database using the IDbConnection interface. Exactly how you do that depends on whether you're using the SqlConnection or the OleDbConnection, but each one has a ConnectionString property that you can use to specify the database you're connecting to.

Creating an IDbConnection does not actually create the physical connection to the database. In fact, you can wait until the very last minute to open the connection, which you do by calling IDbConnection.Open( ).

Once you've created the connection, you must specify what data you want to read. The IDbCommand interface represents a SQL command, and you can create an instance of it by calling IDbConnection.CreateCommand( ) or its constructor. You can create an IDbCommand before you call IDbConnection.Open( ).

Executing the IDbCommand is as simple as calling one of its execute methods. There are three:

  • IDbCommand.ExecuteNonQuery( ) is used to execute a SQL command that does not return any data, such as an insert, update, or delete statement.

  • IDbCommand.ExecuteScalar( ) is used to execute a SQL command that returns a single value, such as select count(*).

  • IDbCommand.ExecuteReader( ) is used to execute a SQL select command that returns a DataReader, which you can use to iterate over a number of rows and columns of resulting data.

The usage of the first two methods should be fairly obvious, but ExecuteReader( ) bears a little further explanation.

11.1.1 Reading Data

Angus Hardware, like most retail stores, occasionally offers its customers discounts in the form of coupons. They like to track which customers take advantage of which coupons, both as a marketing tool, and to aid in fraud detection. They've decided that the best way to manage this coupon usage data is with a relational database.

Figure 11-1 shows the portion of the coupon database schema I use in this chapter.

Figure 11-1. Coupon database schema
figs/dnxm_1101.gif

The results of any SQL select statement come in the form of a table of data. Although it may not represent any actual table in the database, it still consists of rows and columns. Take, for example, the following query:

select coupon_code, total_discount, redemption_date 
from coupon_redemptions
where redemption_date >= '11/7/2002' order by customer_id;

This query returns a table of data that looks like Table 11-1.

Table 11-1. Results of SQL select statement

coupon_code

total_discount

redemption_date

117GG

10.00

11/7/2002

167YH

10.00

11/8/2002

987UI

20.00

11/8/2002

...

  

The coupon_redemption table actually contains other columns, but because I only selected three, the result set only includes those three. In fact, you can see that these results are actually returned in order of a column that is not included in the result set, the customer_id. The result set also does not include all the rows, only the ones redeemed on or after November 7, 2002.

The IDataReader returned from ExecuteReader( ) provides a forward-only, unbuffered view of the data result set. Its Read( ) method is used to iterate through the result records in whatever order they were returned, and returns false when there are no more records to read.

The IDataReader interface also implements IDataRecord, which represents an individual data record. It provides a set of methods to read individual columns from the record. Although the IDataRecord is smart enough to do some conversions, it's up to you to know the type of each database column and what CLR types they can safely be converted to.

The SQL script used to create the database used for the examples in this chapter are on the web site for this book, along with all the sample code. If you don't have access to a SQL Server database, you can also use any OLE-compliant database by changing the classes from the ones in the System.Data.SqlClient namespace to those in the System.Data.OleDb namespace (or System.Data.Odbc or System.Data.OracleClient for an ODBC or Oracle database server if you're using Version 1.1 of the .NET Framework).

You can also use MSDE, the Microsoft Data Engine, which is a freely redistributable desktop version of SQL Server with some minor restrictions. See http://www.microsoft.com/sql/techinfo/development/2000/MSDE2000.asp for more information on MSDE 2000.

Let's build a program that reads redeemed coupons from a SQL Server database and prints them to the console.

The database connect string is different for every sort of database. You should consult your database administrator for the exact parameters for your database connection.


First, create the database connection. This is a SQL Server database named "AngusHardware", and you can connect as the system administrator without a password:

SqlConnection connection = new SqlConnection(
  "Initial Catalog=AngusHardware; User ID=sa");

Of course, in the interest of system security, a real database server would always require a password for the sa user, and you would never want to connect your application to a database as the sa user in any case.


Once the connection has been created (but not actually opened), you're free to use that connection to create SQL commands. This command will perform the query I introduced earlier, returning some coupons from the database:

SqlCommand command = new SqlCommand(
  "select coupon_code, total_discount, redemption_date " +
  "from coupon_redemptions where redemption_date >= '11/7/2002' " +
  "order by customer_id", 
  connection);

Now that everything is set up, it's time to actually open the database connection:

connection.Open( );

Note that this method call can fail if the database connect string was not specified correctly, even though the SqlConnection constructor has come and gone without incident. This can make it difficult to trace connection problems, so be sure your connect string is correct before getting too involved in other program logic.


With an open connection, the SqlCommand can be executed. It's a select statement, so you can call ExecuteQuery( ) to return a SqlDataReader:

SqlDataReader reader = command.ExecuteReader( );

The SqlDataReader.Read( ) method returns a bool indicating whether a record was read from the database, so the while loop exits after the last record has been read. The code within the loop writes a line of text to the console containing the three columns selected from the database:

while (reader.Read( )) {
  Console.WriteLine("{0} {1} {2}", reader.GetString(0), 
    reader.GetDouble(1), reader.GetDateTime(2));
}

IDataRecord has methods to get nearly every type of data from a data record, as well as indexers by column number or column name. With the indexer, the statement above could have been written as the following:

Console.WriteLine("{0} {1} {2}", 
reader["coupon_code"],         
reader["discount_amount"], 
reader["date_redeemed"]);

If you use the indexer, however, you do have to cast the object to whatever type you are expecting.


Finally, it's always good to free up any resources you might have allocated:

reader.Close( );
connection.Close( );

Although it's not strictly necessary to close the SqlDataReader, because it will be closed when the underlying DbConnection is closed, it is considered good form to go ahead and close it.

Example 11-1 shows the complete program.

Example 11-1. Program to print redeemed coupons
using System;
using System.Data.SqlClient;

public class CouponPrinter {

  public static void Main(string [ ] args) {
    SqlConnection connection = new SqlConnection(
      "Initial Catalog=AngusHardware; Integrated Security=SSPI; User ID=sa");

    SqlCommand command = new SqlCommand(
      "select coupon_code, total_discount, redemption_date " +
      "from coupon_redemptions where redemption_date >= '11/7/2002' " +
      "order by customer_id", 
      connection);

    connection.Open( );

    SqlDataReader reader = command.ExecuteReader( );

    while (reader.Read( )) {
      Console.WriteLine("{0} {1} {2}", reader.GetString(0), 
        reader.GetDouble(1), reader.GetDateTime(2));
    }

    reader.Close( );
    connection.Close( );
  }
}

11.1.2 Updating Data

Like TextReader and XmlReader, DataReader provides a read-only, forward-only view of the underlying data stream. This means that updating a database requires a new IDbCommand and the ExecuteNonQuery( ) method, which I mentioned earlier.

Example 11-2 shows a program to insert a new coupon into the database.

Example 11-2. Program to insert a new coupon into a database
using System;
using System.Data.SqlClient;

public enum DiscountType {
  Percentage,
  Fixed
}

public class AddCoupon {
  public static void Main(string [ ] args) {
    SqlConnection connection = new SqlConnection(
      "Initial Catalog=AngusHardware; User ID=sa");

    SqlCommand command = new SqlCommand(
      "insert into coupons ( coupon_code, discount_amount, discount_type, expiration_date ) " +
      "values ( '077GH', 15, " + (int)DiscountType.Percentage + 
      ", '11/30/2002' )", connection);

    connection.Open( );

    command.ExecuteNonQuery( );

    connection.Close( );
  }
}

The SqlCommand.ExecuteNonQuery( ) method simply executes the SQL command without expecting any values to be returned. If you're familiar with SQL, this insert statement should need no explanation.

11.1.3 Building a SQL Command

In the examples so far, I've built the SQL commands as simple text. There is another way that's more flexible. Of course, more flexibility usually involves more code.

The basic concept is that an IDbCommand.Parameters property returns an IDataParameterCollection, which is a collection of IDataParameter instances. The IDataParameter interface's properties include the name of a parameter coded into the IDbCommand, and the value you wish to bind to that name. Look at the following code snippet for an example:

SqlCommand command = new SqlCommand(
  "insert into coupons ( coupon_code, discount_amount, " +
  "discount_type, expiration_date ) " +
  "values ( @coupon_code, @discount_amount, @discount_type, " +
  "@expiration_date )", connection);

command.Parameters.Add(new SqlParameter("@coupon_code", "665RQ"));
command.Parameters.Add(new SqlParameter("@discount_amount", 15));
command.Parameters.Add(new SqlParameter("@discount_type", 
  DiscountType.Percentage));
command.Parameters.Add(new SqlParameter("@expiration_date ", 
  new DateTime(2002,11,30)));

As you can see, the names of the parameters are embedded into the SQL command itself. Each parameter is then added to the IDataParameterCollection as a SqlParameter, with its name and value. The names I've used in this snippet match the names of the respective columns, with an @ prefixed; while the naming of the parameters is entirely up to you, the @ prefix is required.

You can use the Parameters property on any IDbCommand, for any select, insert, update, or delete statement. There are other properties to the IDbParameter subclasses that pertain to the specific types of databases they know about.

The major benefit of building an IDbCommand this way is that every parameter can be assigned dynamically, instead of having to hard-code the command by repeatedly appending strings. Another benefit is that type conversion is automatic, so you don't have to use the ToString( ) method or any sort of string formatting to get a value that the database will accept. Finally, most database servers actually run more efficiently when a query is built this way; the query does not need to be parsed again every time it is run again with different data values.

Example 11-3 shows how both these benefits can be exploited in a rewritten version of the AddCoupon program from Example 11-2.

Example 11-3. Program to insert a new coupon using parameters
using System;
using System.Data;
using System.Data.SqlClient;

public class AddCoupon {
  public static void Main(string [ ] args) {
    SqlConnection connection = new SqlConnection(
      "Initial Catalog=AngusHardware; User ID=sa");

    SqlCommand command = new SqlCommand(
      "insert into coupons ( coupon_code, discount_amount, " +
      "discount_type, expiration_date ) " +
      "values ( @coupon_code, @discount_amount, " +
      "@discount_type, @expiration_date )", connection);

    SqlParameter couponCode = command.Parameters.Add(
      new SqlParameter("@coupon_code", SqlDbType.Char));
    SqlParameter discountAmount = command.Parameters.Add(
      new SqlParameter("@discount_amount", SqlDbType.Decimal));
    SqlParameter discountType = command.Parameters.Add(
      new SqlParameter("@discount_type", SqlDbType.TinyInt));
    SqlParameter expirationDate = command.Parameters.Add(
      new SqlParameter("@expiration_date", SqlDbType.DateTime));
          
    connection.Open( );

    couponCode.Value = "99GGY";
    discountAmount.Value = 5d;
    discountType.Value = DiscountType.Percentage;
    expirationDate.Value = new DateTime(2002,12,31);
    command.ExecuteNonQuery( );

    command.Parameters["@coupon_code"].Value = "81BIN";
    command.Parameters["@discount_amount"].Value = 10d;
    command.Parameters["@discount_type"].Value = DiscountType.Fixed;
    command.Parameters["@expiration_date"].Value = 
      new DateTime(2003,1,31);
    command.ExecuteNonQuery( );

    connection.Close( );
  }
}

This example shows two ways to deal with the SqlParameter objects. Each of the SqlParameter objects is created and added to the SqlCommand's Parameters property, which is a SqlParameterCollection. The Add( ) method returns the newly created SqlParameter, which is then assigned to a local variable.

For the first execution of the SqlCommand, the SqlParameter instances are accessed by the local variables, and their values are assigned using the Value parameter. The SqlCommand.ExecuteNonQuery( ) method causes the SQL statement to be executed with those values.

In the second SqlCommand execution, the SqlParameter instances are accessed by name using the SqlParameterCollection's indexer (the other indexer accesses a SqlParameter by its integer index). Then, like before, its Value is set and the SqlCommand is executed with those values.

    [ Team LiB ] Previous Section Next Section