Using Data Adapters to Retrieve and Manipulate Data

for RuBoard

In database programming, it's common to retrieve data and then perform other actions on the data (such as inserting new data, updating existing data, and deleting data). Often an application will need to take different actions based on the results of one or more queries or stored procedure calls.

In ADO.old, this can be accomplished through the use of a cursor. A cursor-based approach is expensive, however, because it implies a persistent connection to the data source. ADO.old does support a batch-update model whereby the developer can retrieve data, perform updates, and then reconnect to the data source and commit the updates in one operation (known as a batch).

The .NET vision of scalable, disconnected data access demands a different approach, however. The .NET DataSet requires a sessionless, cursorless approach. To provide access to retrieving, inserting, updating, and deleting records in a single object without the use of cursors or persistent connections, ADO.NET provides the data adapter object.

Data adapters

• Are similar to command objects, except they can contain four separate commands (one each for select, insert, update, and delete).

• Can be used to create always-disconnected DataSet objects. DataSets can be serialized as XML.

• Decouple data-manipulation code from the data itself, making the data easier to remote and the data manipulation code easier to maintain and reuse.

Each ADO.NET provider has its own data adapter object. In the SQL Server provider, the data adapter is named SqlDataAdapter; not surprisingly, in the OLE DB provider, the data adapter class is called OleDbAdapter.

The implementation details of the data adapter may vary slightly from one provider to the next , but the basic purpose of the data adapter is the same across all providers: Data adapters provide a connectionless method to engage in rich interaction with data sources. By rich interaction, we are talking about operations that go beyond the simple requests and display of data. Data readers are described in the "About the SqlDataReader Object" section of this chapter.

Although each ADO.NET provider has its own data adapter, DataSet objects created by different adapters are the same; DataSets are totally interoperable across providers. This is an important aspect of ADO.NET's interoperability story; it provides a standard way to express relational or hierarchical data that can be manipulated in any language, on any platform.

The ultimate objective of a Web database application is to present data to the user and permit users to manipulate data in interesting ways in the browser. The next few sections will introduce data adapters and demonstrate the various things you can do with them in ASP.NET applications. To demonstrate the power and flexibility of the data adapter, we'll first need to take a detour and discuss the principles of building a database-driven user interface in ASP.NET.

Displaying Query Data in the Browser

Earlier code listings in this chapter gave several examples of displaying data in a Web browser using calls to Response.Write. This is basically the same way you send output data to the browser in ASP.old. However, with ADO.NET and ASP.NET Web Forms controls, you have new options that provide better structure and maintainability, as well as more powerful features.

One of these features is data binding. Data binding refers to the process of automatically mapping the fields in a database to the user interface. Performing data binding automatically is handy because it relieves the developer from having to write a large amount of tedious code associated with retrieving and displaying data.

The concept of data binding got a bad rap among Visual Basic developers for a number of valid reasons. Data binding promised the capability to create a rich database user interface with a minimum of coding. Simplicity was the objective.

But this simplicity came at a price. Thick-client data-bound applications typically ran slowly, consumed a persistent database connection resource whether they were doing any work or not, and were difficult for programmers to code against because much of the underlying data-access functionality was encapsulated in the form of an object (the Data control) that exposed a painfully limited set of properties, methods , and events associated with data operations. If you were interested in building a certain type of data browser application and performance wasn't an issue, data binding worked well. But if you needed to build anything more sophisticated than a simple data browser, binding to a Data control was problematic at best.

Data binding in ASP.NET is different from the data-control-centric, connection-based vision of thick-client VB.old. The problems involving a "no-code" solution aren't a problem in ASP.NET, because ASP.NET data binding doesn't use black-box abstractions like the VB.old Data control.

The next few sections discuss the objects and techniques involved in data binding in ASP.NET and give you some examples describing how to put data binding to work in your applications.

Creating a DataSet Object Using a Data Adapter

You can use a DataSet in conjunction with a data adapter object to retrieve data from a database in a manner similar to the DataReader example in Listing 11.4. Although you might not use this code to display data in this way in a real application, dumping the contents of a query into a DataSet and then into the browser is a useful stepping stone on the way to data binding with Web Forms controls (which we'll discuss next).

Listing 11.10 shows a very simple example of a database select query using the SqlDataAdapter and a DataSet object.

Listing 11.10 Using the SqlDataAdapter and the DataSet Object to Display Query Results

 <% @Page language='c#' debug='true' %> <% @Import namespace='System.Data' %> <% @Import namespace='System.Data.SqlClient' %> <SCRIPT runat='server'>   void Page_Load(Object Sender,EventArgs e)   {     SqlConnection cn;     SqlDataAdapter da;     DataSet ds;     String strSQL;     strSQL = "SELECT TOP 10 au_fname, au_lname FROM authors";     cn = new SqlConnection("server=localhost;uid=sa;pwd=;database=pubs;");     da = new SqlDataAdapter(strSQL, cn);     // ** Fill DataSet     ds = new DataSet();     da.Fill(ds, "Authors");     // ** Display data     foreach(DataRow Author in ds.Tables["Authors"].Rows)     {       Response.Write(Author["au_fname"].ToString() + " " +                      Author["au_lname"].ToString() + "<BR>");     }   } </SCRIPT> 

You can see in this example, the SqlDataAdapter object is created from a SqlConnection object; this is similar to the way we created the basic SqlConnection object earlier. To build a DataSet object from the SqlDataAdapter object, we declare and instantiate the DataSet and then pass the DataSet to the Fill method of the SqlDataAdapter. The connection to the database is implicitly opened and closed when you fill a DataSet in this way.

Executing the fill method executes the SELECT query; at this point, we can start accessing the data through the Rows collection contained by the (one and only) table contained by the DataSet.

This example is similar to some of the data reader examples from earlier in this chapter, but with more complexity and more code. Fear not; this is just the first example and doesn't scratch the surface of what the DataSet can accomplish.

One of the big differences you can see between the behavior of the DataSet and the data reader object is that the DataSet has a Tables collection that contains DataTable objects. The capability to contain multiple tables in a single object ( potentially containing relationships defined by one or more DataRelation objects) is one of the defining characteristics of the DataSet objects. We'll take a look at the power of multiple tables in a single DataSet in Listing 11.14.

For now, it might be worthwhile to look at more efficient and structured ways to display data on the page. Outputting HTML to the browser using a loop containing calls to Response.Write works fine, but if you're interested in doing anything more complicated than displaying row-by-row data with line breaks, you will want a more powerful display technique. Fortunately, ASP.NET provides this in the form of data-bound Web Forms controls, which we'll discuss in the next section.

Binding a DataView Object to Web Forms Controls

You can display data in an ASP.NET Web Forms page by using data binding. To use data binding, you begin by executing a select command with a data adapter. This produces a DataSet object that contains a collection of DataTable objects; each DataTable contains a DataView object that can be connected to any Web Forms control capable of binding to data (including many HTML server controls).

To perform the actual data binding after the DataSet is created, you first set the Web Forms control's DataSource property to a DataView object contained by the DataSet, and then use the DataBind method of the ASP.NET Page object to initiate binding. This method, typically called in the Page object's Load event, serves to connect the user interface control(s) on the page with the DataSet object(s) you've created.

Listing 11.11 takes the code from the previous example and amends it to display its output in a Web Forms DataGrid control.

Listing 11.11 Displaying Query Data in a Web Forms DataGrid Control

 <% @Page language='c#' debug='true' %> <% @Import namespace='System.Data' %> <% @Import namespace='System.Data.SqlClient' %> <HTML> <SCRIPT runat='server'>   void Page_Load(Object Sender,EventArgs e)   {     SqlConnection cn;     SqlDataAdapter da;     DataSet ds;     String strSQL;     strSQL = "SELECT TOP 10 au_fname, au_lname FROM authors";     cn = new SqlConnection("server=localhost;uid=sa;" +             "pwd=;database=pubs;");     da = new SqlDataAdapter(strSQL, cn);_     // ** Fill DataSet     ds = new DataSet();     da.Fill(ds, "Authors");     // ** Display data     DataGrid1.DataSource = ds.Tables["Authors"].DefaultView;     DataGrid1.DataBind();   } </SCRIPT> <BODY>   <ASP:datagrid id='DataGrid1' runat='server' /> </BODY> </HTML> 

Nothing is really new here except for the changes in the "display data" section of the code. This time we simply assign the DefaultView property (a DataView object) of the Authors table (a DataTable object) to the DataSource property of the DataGrid control, which we named DataGrid1. You should be able to see that the ASP:datagrid definition in the HTML section of the code doesn't specify any property settings for the DataGrid control other than its ID, so all the defaults are in place. The result is a plain vanilla HTML table, outputted to the browser.

Binding Other Objects to Web Forms Controls

Web Forms controls can be bound to any object that implements the System.Collections.IEnumerable or System.Collections.ICollection interfaces. The DataView object supports the IEnumerable interface, which is why it can be bound to ASP.NET server controls. You can also build your own .NET classes that implement one of these interfaces if you're interested in binding custom classes to ASP.NET Web Forms controls.

Creating your own classes for data binding is beyond the scope of this book, but it is pretty easy to see how an existing class can be bound to an ASP.NET control. The ArrayList class (found in System.Collections) is a perfect candidate to use as a guinea pig to see how this works; ArrayList is simple to work with and implements both IEnumerable and ICollection.

Listing 11.12 shows an example of how to bind a DataGrid control to an ArrayList object.

Listing 11.12 Binding a DataGrid Control to an ArrayList Object

 <% @Page language='c#' debug='true' %> <% @Import namespace='System.Collections' %> <HTML> <SCRIPT runat='server'>   void Page_Load(Object Sender,EventArgs e)   {     ArrayList al = new ArrayList();     al.Add("Alaska");     al.Add("Alabama");     al.Add("California");     al.Add("Kentucky");     // ** Display data     DataGrid1.DataSource = al;     DataGrid1.DataBind();   } </SCRIPT> <BODY>   <ASP:datagrid id='DataGrid1' runat='server' /> </BODY> </HTML> 

The technique to bind the DataGrid control shown here is the same as the methods shown earlier in the chapter to bind to relational data; as far as the DataGrid control is concerned , no difference exists between different types of bindable objects. As long as the object supports IEnumerable or ICollection, it can be displayed through binding.

Note that data readers (the SqlDataReader and OleDbDataReader classes) support the IEnumerable interface and can therefore be bound to Web Forms controls as well.

Expressing a DataSet as XML

One of the advantages of retrieving data with a DataSet is that a DataSet can be accessed at the object level (through the collection of DataTable and DataRow objects contained by the DataSet) or on a raw XML level. The capability to process a DataSet as XML means that you can easily transfer a DataSet to other platforms that don't explicitly support Microsoft.NET or work with the DataSet with XML tools (whether or not they're explicitly built to support .NET).

You can use the GetXml method of the DataSet object to extract XML data from the result of a query. By setting the ContentType Page directive to text/xml, it's possible to see the XML output directly in the browser window.

As with many Web-based XML examples, Listing 11.13 works best when you use Internet Explorer 5.0 or later; Internet Explorer has support for parsing and displaying XML directly in the browser window.

Listing 11.13 shows an example of outputting a query in XML format to the browser window using this technique.

Listing 11.13 Sending Query Results to the Browser Window Using XML

 <% @Page ContentType='text/xml' language='c#' debug='true' %> <% @Import namespace='System.Data' %> <% @Import namespace='System.Data.SqlClient' %> <SCRIPT runat='server'>  void Page_Load(Object Sender,EventArgs e)   {     SqlConnection cn;     SqlDataAdapter da;     DataSet ds;     String strSQL;     strSQL = "SELECT TOP 10 au_fname, au_lname FROM authors";     cn = new SqlConnection("server=localhost;uid=sa;pwd=;database=pubs;");     da = new SqlDataAdapter(strSQL, cn);     // ** Fill DataSet     ds = new DataSet();     da.Fill(ds, "Authors");     // ** Display data     Response.Write(ds.GetXml());   } </SCRIPT> 

Instead of simply dumping XML to the browser window, you can also assign the XML output of a DataSet object to one of the XML-manipulating objects in the .NET framework. To do this, you pass an XmlDataReader object to the ReadXml method of the DataSet object. You can then manipulate the XmlDataReader object as you would any other XML document, including sending it to a file or passing it to another process.

NOTE

The XmlDataReader object is one of the objects that the .NET framework provides for reading XML. For more information on how the XmlDataReader works, see Chapter 10, "Using XML."

for RuBoard