166 Microsoft ADO .NET 4 Step by Step
4. Just after the “Read the next set, which contains the orders” comment, add the follow-ing code:
Do While (customerReader.Read = True) oneOrder = New OrderInfo
oneOrder.ID = CLng(customerReader!ID) oneOrder.OrderDate = CDate(customerReader!OrderDate) oneOrder.OrderTotal = CDec(customerReader!Total) AllOrders.Items.Add(oneOrder)
This code accesses the records in the second set of results, the SELECT statement for the OrderEntry table, via the NextResult method call.
5. Run the program. On the Customer Management form, select a customer from the list of customers and then click View Orders. When the View Orders form appears, it in-cludes content from both SELECT statements returned by the stored procedure.
Thischapterdiscussedparameters,whicharedatavalueobjectsthathelpensuretheaccuracy andsafetyofthedatabeingsenttoandreturnedfromexternaldatasources.Parameterized queriesusespecialSQLstatementsthatincludeplaceholdersforeachparameter.Each SqlParameter instance defines the name of the parameter, its data type, and its value.
Parameters work with either standard SQL commands or with stored procedures. When using them with stored procedures, you can create both input and output stored procedures, sup-porting two-way communications with these custom database functions.
Chapter 10 Adding Standards to Queries 167 Chapter10QuickReference
Create a parameterized query for SQL Server
Create an “out” parameter for a stored procedure
Create a SQL query string that includes @-prefixed placeholders.
Create a SqlCommand instance.
Assign the SQL query to the SqlCommand object’s CommandText property.
Create SqlParameter objects, one for each placeholder in the query, and add them to the command object’s Parameters collection.
Set the SqlCommand.Connection property.
Call one of the command object’s Execute methods.
Create a SQL query string that includes question marks (?) for placeholders.
Create an OleDbCommand instance.
Assign the SQL query to the OleDbCommand object’s CommandText property.
Create OleDbParameter objects, one for each placehold-er in the query, and add them to the command object’s Parameters collection.
Set the OleDbCommand.Connection property.
Call one of the command object’s Execute methods.
Create a SqlParameter instance, setting its fields as needed.
Set the SqlParameter.Direction property to ParameterDirection.Output.
External Data Available
After completing this chapter, you will be able to: ■■ Load external data into a DataTable or DataSet
■■ Return updated DataSet content to an external source
■■ Use SQL statements and stored procedures to manage DataSet content
The disconnected data experience provided by ADO.NET revolves around the DataSet class and its supporting objects. The last few chapters have introduced ways to access external data with ADO.NET, but none of those features took advantage of the disconnected aspects of the framework. Still, part of the promise of ADO.NET is its ability to manage external data in a disconnected and table-focused way.
This chapter introduces the DataAdapter class—the class that fulfills that core data promise. The DataAdapter bridges the simple data connectedness exhibited by the DataReader and joins it with the advanced data management features found in the DataSet. By creating a few simple objects and crafting a minimum number of SQL statements, you can safely give your DataSet the tools needed to keep it and its associated external data source in sync.
Data adapters link your external database tables and your local DataSet-managed tables by issuing SQL statements. Anytime you need to get data from the database into a DataSet, the adapter must perform a “Fill” operation, issuing a SELECT statement and moving the results into local DataTable instances. You can then update the values in those DataTable instances. When it’s time to return changes stored in the DataSet to the database, the data adapter’s “Update” operation sends the relevant INSERT, UPDATE, and DELETE statements to the da-tabase to bring the external data store into line with local changes. Figure 11-1 shows these components working on a single database table, Customer.
170 Microsoft ADO .NET 4 Step by Step
Customer ID FullName
DataAdapter SELECT DataReader
Fill INSERT Update
UPDATE Command DELETE
DataSet / DataTable Original Data
FIGURE 11-1 The data adapter in action.
As Figure 11-1 makes clear, the DataAdapter manages a lot of complex activity between the database and a DataSet or DataTable. It is no exaggeration to say that the DataAdapter is possibly the most complex part of ADO.NET, especially when you take advantage of all the flexibility it provides. All the classes introduced so far in this book—from DataSet to SqlParameter, from DataRow to DataReader—come into play when creating instances of a data adapter class.
The System.Data.SqlClient.SqlDataAdapter class exposes the SQL Server provider implemen-tation of the adapter. You can also find OLE DB and ODBC variations of the data adapter in the classes System.Data.OleDb.OleDbDataAdapter and System.Data.Odbc.OdbcDataAdapter, respectively. All these classes derive from System.Data.Common.DbDataAdapter, which in turn derives from System.Data.Common.DataAdapter.
Note Although the information in this chapter applies generally to all data adapter implemen-tations, this chapter’s code samples and examples focus specifically on the SQL Server provider version.
SqlDataAdapter provides three general support features in your application:
■■ Recordretrieval Populating a DataTable with database records represents the mini-mal functionality of the data adapter. Internally, the SqlDataAdapter uses a DataReader instance to retrieve records out of the database, so you must provide it with a SELECT statement and a connection string. Stored procedures that return data rows also work; the adapter will correctly process multiple record sets returned by the query.