| [ Team LiB ] |
|
11.1 Introduction to ADO.NETThe .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.
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:
The usage of the first two methods should be fairly obvious, but ExecuteReader( ) bears a little further explanation. 11.1.1 Reading DataAngus 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 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.
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.
Let's build a program that reads redeemed coupons from a SQL Server database and prints them to the console.
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");
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( );
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));
}
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 couponsusing 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 DataLike 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 databaseusing 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 CommandIn 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 parametersusing 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 ] |
|
