An Introduction to ADO.NET

only for RuBoard

Many developers are excited about the changes to ASP and the increased support for XML, but have reservations about learning a new data access strategy. It seems that every new version of Visual Basic brings another data-access strategy. The release of VB 3.0 brought forth Data Access Objects (DAO), Visual Basic 4.0 created a new release of DAO and introduced developers to Remote Data Objects (RDO), and Visual Basic 5.0 included a new release of RDO. Around the release of Visual Basic 5.0, Microsoft introduced its Universal Data Access strategy and its key component, ActiveX Data Objects (ADO). Since Visual Basic 5.0's release, developers have seen ADO versions 1.5, 2.0, 2.1, 2.5, 2.6, and recently released version 2.7. Besides ADO releases, developers have also seen interfaces for working with the Data Definition Language (DDL) in the form of ADO Extensions for DDL and Security (ADOX) for versions 2.1 and 2.5. With so many revisions (and updates to existing code), it's understandable that many developers dread yet another data access strategy.

Why so many changes? More importantly, what makes ADO.NET a compelling technology to migrate code to or adopt for new development? The simple answer is evolution: Because the way that developers work with data has changed over the years , so have the interfaces that developers use to gain access to the underlying data. DAO was built to facilitate forms-based development with local access databases where a constant, open connection to the database is feasible . RDO was built to provide fast access for forms-based applications to remote SQL server databases. ADO was built to provide access to disparate data sources with the added design goal of working in a disconnected server environment. As web development gained maturity, lessons learned have yielded a new programming model that best serves the disconnected environment.

ADO Versus ADO.NET

One of the reasons that ADO has been so successful is its ability to work with different data sources and still expose the same programming model without concern of the specific features of the given provider. ADO.NET builds on the past success of ADO as a tool for working with disparate data sources. The term ADO is an acronym for ActiveX Data Objects. ActiveX is the marketing name that replaced Object Linking and Embedding (OLE), and the name ActiveX was surpassed by Component Object Model (COM) and Distributed COM (DCOM). As you can recall from Chapter 1, .NET is not built on COM and does not rely on COM, so the term ADO.NET is actually somewhat of a misnomer. As you'll see throughout this chapter, using XML as the underlying support mechanism in ADO.NET is a key factor in the expansion of the ADO vision.

OLE DB and Managed Providers

OLE DB is a Microsoft specification that provides an open standard for accessing disparate data sources. It is a set of COM interfaces that provide universal access to data, whether the data source is a relational database, a mainframe, or an Excel spreadsheet. Microsoft eased the developer's burden by providing a set of wrapper classes and helper methods in the form of ADO. Using ADO, the developer is able to focus on the business problem rather than the low-lying APIs.

ADO is traditionally associated with databases. Its primary function is to provide database support with an easy-to-understand, common API that can be applied to a variety of databases, such as SQL Server, Access, Sybase, or Oracle. It can also be applied to other nonrelational data stores, such as ADSI or Excel. It achieves this by abstracting exposed APIs from the underlying implementation through the concept of providers. Data retrieved from a database is received in streams, and working with these streams is simplified through the use of the Recordset object. How the streams are read and updated depends on from where the data came. For example, working with data in an Excel spreadsheet is different than working with data in an Oracle database. The abstraction that ties the two data sources together is known as a data provider .

OLE DB Providers

The notion of universal data access was first introduced through the Open Database Connectivity (ODBC) specification, where different data providers could access ODBC data sources through a standard API. The problem with ODBC is that it brings all implementors down to the lowest -common denominator by requiring a minimum level of support. Requiring all implementors to adhere to certain behaviors doesn't easily enable the individual data providers to extend their support base.

ADO supports ODBC as a lowest-common denominator approach, but also introduces the concept of using existing data providers and creating custom data providers using OLE DB data providers. OLE DB data providers enable the use of the common ADO programming model while accessing vendor-specific implementations and features.

Figure 8.1 shows the additional ODBC layer that accesses data stores using ODBC and ensures ODBC compliance, whereas Figure 8.2 shows that using a specific provider for data access reduces the number of layers needed and enables direct access to vendor-specific features.

Figure 8.1. An additional ODBC layer is needed to access data stores using ODBC connectivity.
graphics/08fig01.gif
Figure 8.2. Direct provider access reduces overhead while allowing implementors to extend functionality.
graphics/08fig02.gif

A data provider owns its own data and exposes it in tabular form to your application[1]. Many different data providers are available that provide optimizations for each type of data store. In ADO, the Provider property of the Connection object or using the Provider argument in a connection string specified which data provider was to be used. Examples of provider arguments for ADO are SQLOLEDB (Microsoft OLE DB Provider for SQL server), MSDAORA (Microsoft OLE DB Provider for Oracle), and MSDASQL (Microsoft OLE DB Provider for ODBC).

Using an OLE DB provider directly instead of going through the additional ODBC layer provides several benefits, such as the following:

  • Bypassing the additional ODBC layer increases performance by reducing the amount of executed code

  • Accessing vendor-specific implementations increases flexibility and potentially performance

  • Creating custom providers to custom data stores reduces duplication of effort and code continuity

Managed Providers in .NET

Data access in .NET builds on the concept of abstracting data sources, data providers, and the interfaces used to interact with the underlying data by introducing managed providers. A managed provider is used to connect to a database, execute commands, and retrieve results. It's designed to create a minimal layer between the data source and your code, which increases performance without sacrificing functionality[2]. Currently three choices of managed providers are available, which are listed in Table 8.1.

Table 8.1. Managed Data Providers in ADO.NET

Data Provider

Description

SQL Server .NET Data Provider

Recommended for middle-tier applications using Microsoft SQL server 7.0 or later or single- tier applications using MSDE or SQL Server 7.0 or later.

OLEDB .NET Data Provider

Recommended for middle-tier applications using Microsoft SQL server 6.5 or earlier, or any OLEDB provider that supports the OLEDB interfaces. Does not support OLEDB Provider for ODBC.

ODBC .NET Data Provider

Provides access to ODBC data sources. This provider is not part of the .NET Framework SDK, and is obtained through a separate download at http://msdn.microsoft.com/downloads.

The main difference between providers is depicted in Figure 8.3.

Figure 8.3. The difference between providers in the .NET Framework.
graphics/08fig03.gif
 ms-help://MS.VSCC/MS.MSDNVS/cpguide/html/cpconadonetproviders.htm. 

OLEDB providers are built using the COM programming model, so they rely on the IUnknown interface's AddRef and Release methods. Again, .NET is not built on COM, so a new programming model is needed for data interaction that takes advantage of managed code in .NET, which takes advantage of the Common Language Run-time's garbage collection mechanism. Therefore, a layer of abstraction is needed to take care of the COM reference counting necessary for OLEDB providers.

The four core types of objects used in a managed provider are Connection , Command , DataReader , and DataAdapter . Table 8.2 explains these object types.

Table 8.2. Core Objects in ADO.NET Data Providers

Type

SQL Server .NET Managed Provider Implementation

OLEDB .NET Managed Provider Implementation

Description

Connection

SqlConnection

OleDbConnection

Similar to the ADO Connection object. Provides connectivity to the specified data store.

Command

SqlCommand

OleDbCommand

Similar to the ADO Command object. Issues commands to the database.

DataReader

SqlDataReader

OleDbDataReader

Reads a forward-only stream of rows from the database.

DataAdapter

SqlDataAdapter

OleDbDataAdapter

Fills a DataSet and updates a database.

It is also worth mentioning that it is possible to create your own managed provider. No example is provided here, but refer you to the SDK documentation. Figure 8.4 depicts some of the interfaces used to create your own provider. This is helpful in understanding how the different implementations use interfaces to create their individual implementations while retaining a common API.

Figure 8.4. Interfaces to implement to create your provider.
graphics/08fig04.gif

Because providers use common interfaces, it is possible to work with interfaces to create a provider- agnostic implementation, as shown in the following code:

 IDbConnection connection =  Activator.CreateInstance("System.Data","System.Data.SqlClient.SqlConnection")  ; 

This chapter focuses on using the SQL server .NET Managed Provider classes.

Namespaces in .NET

Currently, five different namespaces comprise ADO.NET. The namespace that nearly all ADO.NET implementations utilize is System.Data . This namespace contains the base classes and interfaces used to retrieve data in a tabular format. Table 8.3 describes the different namespaces that comprise ADO.NET.

Table 8.3. Namespaces in ADO.NET

.NET Namespace

Description

System.Data

Contains the base classes and interfaces for ADO.NET, such as DataSet , DataView , and DataTable .

System.Data.Common

Contains classes shared by the .NET data providers, such as DataAdapter and DbDataAdapter .

System.Data.OLEDB

Contains classes that comprise the OLEDB .NET Data Provider, such as OleDbConnection and OleDbCommand .

System.Data.SqlClient

Contains classes that comprise the OLEDB .NET Data Provider, such as SqlConnection and SqlCommand .

System.Data.SqlTypes

Provides the classes for native data types within SQL server.

OLEDB .NET Data Provider

The SQL server .NET Data Provider is implemented through the classes in the System.Data.OLEDB namespace. As mentioned in Table 8.1, this provider is used for connectivity to OLEDB data sources other than SQL Server 7.0 or later. It contains classes similar to ADO. The code in Listing 8.1 is similar to code you have likely done in the past with ADO. For example, look at the following class that provides data connectivity to an OLEDB data source.

Listing 8.1 The OLEDBSample Class
 using System;  using System.Data.OleDb;  using System.Collections;  namespace ADOSamples  {      /// <summary>       /// Summary description for OLEDB.       /// </summary>       public class OLEDBSample       {           private string m_connectionString = "";            public OLEDBSample(string connectionString)            {                m_connectionString = connectionString;            }            public void ExecuteCommand(string commandString, System.Data.CommandType graphics/ccc.gif commandType)           {                string errorString = string.Empty;  OleDbCommand command = new OleDbCommand(commandString);   command.CommandType = commandType;  try                 {                    _execute(command);                 }                 catch(System.Exception e)                 {                      errorString = e.ToString();                 }                 finally                 {                      command.Dispose();                       if (errorString != string.Empty)                           throw (new System.Data.DataException(errorString));                 }            }            private void _execute(OleDbCommand command)            {                string errorString = string.Empty;                 OleDbConnection connection = null;                 try                 {  connection = new OleDbConnection(m_connectionString);   command.CommandTimeout = 30;   command.Connection = connection;   command.Connection.Open();   command.ExecuteNonQuery();  }                 catch(System.Exception  e)                 {                     errorString = e.ToString();                 }                 finally                 {                     if(connection != null)                      {                          if (connection.State !=  System.Data.ConnectionState.Closed)   {   connection.Close();   }   connection.Dispose();   }   if (errorString != string.Empty)   throw(new System.Data.DataException(errorString));  }            }       }  } 

The preceding code provides the ability to issue a command to the database that does not return a rowset . Common instances of commands that do not return a rowset are INSERT , UPDATE , and DELETE statements. The method to concentrate on in this example is the private _ execute() method of our OLEDBSample class. This is where you actually create a database connection, set the length of time the query should be able to execute in, and execute the query. Notice that you explicitly close your database connection and call the Dispose() method. The rationale for explicitly closing and disposing of database resources is discussed in the section, "Disconnected Designs."

To call this method, simply pass a connection string to the class' constructor and call the ExecuteCommand method, as shown here:

 Try              Dim connectionString As String  connectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Program graphics/ccc.gif Files\Microsoft Office\Office\Samples\Northwind.mdb;Persist Security Info=False"  Dim con As ADOSamples.OLEDBSample = New  ADOSamples.OLEDBSample(connectionString)              con.ExecuteCommand("INSERT INTO CUSTOMERS(CustomerID,CompanyName) VALUES graphics/ccc.gif ('VBDNA','Kirk Allen Evans')", CommandType.Text)          Catch errOops As System.Exception              Console.WriteLine(errOops.ToString())          End Try 

This code inserts a new row in the Customers table in the Northwind sample database for Access 2000 using the Jet OLEDB 4.0 data provider.

SQL Server .NET Data Provider

The SQL server .NET Data Provider is implemented through the classes in the System.Data.SqlClient namespace, and is used for connectivity to SQL Server 7.0 or later. Listing 8.2 shows that the code changes between the two implementations in are minimal.

Listing 8.2 The SqlClientExample Code
 Imports System  Imports System.Data.SqlClient  Imports System.Collections  Namespace ADOSamples      Public Class SqlClientExample          Private m_connectionString As String = ""          Public Sub New(ByVal connectionString)               m_connectionString = connectionString          End Sub          Public Sub ExecuteCommand(ByVal commandString As String, ByVal commandType As graphics/ccc.gif System.Data.CommandType)              Dim errorString As String = String.Empty              Dim command As SqlCommand = New SqlCommand(commandString)              command.CommandType = commandType              Try                  _execute(command)              Catch e As System.Exception                  errorString = e.ToString()              Finally                  command.Dispose()                  If errorString <> String.Empty Then                      Throw (New System.Data.DataException(errorString))                  End If              End Try          End Sub          Private Sub _execute(ByVal command As SqlCommand)          Dim errorString As String = String.Empty          Dim connection As SqlConnection = Nothing          Try  connection = New SqlConnection(m_connectionString)   command.CommandTimeout = 30   command.Connection = connection   command.Connection.Open()   command.ExecuteNonQuery()  Catch e As System.Exception              errorString = e.ToString()          Finally              If Not connection Is Nothing Then  If connection.State <> System.Data.ConnectionState.Closed Then   connection.Close()   End If  connection.Dispose()               End If               If errorString <> String.Empty Then                       Throw (New System.Data.DataException(errorString))                   End If              End Try          End Sub      End Class  End Namespace 

C# was used in the previous OLEDB example and Visual Basic in this SqlClient example, but if you look at the two versions, they are nearly identical. The only real change to the code is to search for OleDb and replace it with SqlClient .

The code to call the class does change slightly, however. The difference is in the connection string used:

 <%@Import Namespace="ADOSamples"%>  <script language="vb" runat="server">       Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) graphics/ccc.gif Handles MyBase.Load            Try                 Dim connectionString As String                 connectionString = "Integrated Security=SSPI;Persist Security graphics/ccc.gif Info=False;Initial Catalog=Northwind;Data Source=at1lt-3165-03"                 Dim con As ADOSamples.SqlClientExample = New graphics/ccc.gif ADOSamples.SqlClientExample(connectionString)                 con.ExecuteCommand("INSERT INTO CUSTOMERS(CustomerID,CompanyName) VALUES graphics/ccc.gif ('VBDNA','Kirk Allen Evans')", CommandType.Text)                 Response.Write ("Successfully inserted the record into the database!")            Catch errOops As System.Exception                 Response.Write (errOops.ToString())            End Try       End Sub  </script> 

Notice the lack of a Provider argument in the connection string. This argument isn't necessary when working with the class in the SqlClient namespace because the provider is implied through the use of the SQL-server specific implementation classes.

Now that you have seen how to make a basic connection and issue a simple text command, it's time to look at how to use parameterized queries with ADO.NET.

Parameterized Queries and Stored Procedures in .NET

Just as in ADO, you can specify the type of command being issued through the Command object's CommandType property. The CommandType in ADO.NET accepts a member of the CommandType enumeration. Table 8.4 lists the members of the CommandType enumeration.

Table 8.4. Members of the CommandType Enumeration

Member Name

Description

Text

Executes a text command. An example is SELECT * FROM AUTHORS . This is the default setting.

StoredProcedure

Executes a stored procedure. This is used for SQL Server, Sybase, and Oracle stored procedures, as well as saved queries in Access.

TableDirect

Accesses a table directly. When an Execute method is called, all columns and rows of the table are returned. Not currently supported. To work with tables directly, see the DataAdapter class and the SourceTable argument description.

In the previous examples, you simply issued a non-parameterized text command. In the May 2000 issue of Visual Basic Programmer's Journal , I wrote an article called "Minimize Cache Thrash" (also available in MSDN) that explains why the use of stored procedures and parameterized queries can be essential for a database- intensive application.

Using parameters in ADO.NET is just as simple as it was in ADO; in fact, it looks similar, with a few exceptions.

The syntax for representing parameters in the SQL command text has changed since ADO classic. I have seen posts in newsgroups where users wanted to know how to reference a parameter by its name within the SQL command text. Until ADO.NET, parameters were represented as question marks ( ? ). In ADO.NET, parameters are represented by using their parameter name preceded by an at ( @ ) symbol.

The API for ADO.NET is familiar, but also has some slight variations. You can either instantiate an ADO.NET Parameter object, or you can use the CreateParameter method of the Command object to return an unpopulated Parameter object. After the object is created and available, use Command.Parameters.Add to add the parameter.

For practice, create a stored procedure in SQL Server. Open Query Analyzer (located in the SQL Server programs group on your Start menu, if the client tools for SQL Server are installed) and connect to the Northwind database. Enter the instructions in Listing 8.3 into the code pane and press the F5 button.

Listing 8.3 Creating a Procedure Called AddCustomer in the Northwinds Database
 create procedure AddCustomer(@CustomerID char(5), @CompanyName varchar(40))  as  insert into Customers(CustomerID, CompanyName) VALUES (@CustomerID,@CompanyName)  if (@@ERROR <> 0) goto errorhandler  cleanup:       return(0)  errorhandler:       return(1) 

These steps created a stored procedure in the Northwind database called AddCustomer . To execute this stored procedure, you need two input parameters. Optionally , you can also use a return value parameter to inspect the return value, as shown in Listing 8.4.

Listing 8.4 Familiar Code for Database Access
 <%@Page Language="vb" Debug="true"%>  <%@Import Namespace="System.Data.SqlClient"%>  <%@Import Namespace="System.Data"%>  <%@Import Namespace="System.Data.Common"%>  <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">  <HTML>       <HEAD>            <title>WebForm3</title>            <script language="vb" runat="server" >      Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)          Dim customerID As String = String.Empty          Dim companyName As String = String.Empty          'Retrieve the Customer ID from the form element "CustomerID"          customerID = TextBox1.Text          companyName = TextBox2.Text          Dim command As SqlCommand = New SqlCommand("AddCustomer")          command.CommandType = System.Data.CommandType.StoredProcedure          Dim tempParam As SqlParameter          'Create the input parameter for the CustomerID parameter          tempParam = New SqlParameter("@CustomerID", SqlDbType.Char)          tempParam.Direction = ParameterDirection.Input          tempParam.Size = customerID.Length          tempParam.Value = customerID          'Add the parameter directly to the collection          command.Parameters.Add(tempParam)          'Create the parameter using the Add method and return the parameter          tempParam = command.Parameters.Add("@CompanyName", SqlDbType.VarChar, graphics/ccc.gif companyName.Length)          'Access the parameter object directly          tempParam.Direction = ParameterDirection.Input          tempParam.Value = companyName           'Create the input parameter for the return value           tempParam = command.Parameters.Add(New SqlParameter("@RetVal", SqlDbType.Int))           tempParam.Direction = ParameterDirection.ReturnValue           Dim connection As SqlConnection = New SqlConnection("Initial graphics/ccc.gif Catalog=Northwind;User ID=sa;Password=;Data Source=at1lt-3165-03")           command.Connection = connection           command.Connection.Open()           command.ExecuteNonQuery()           Dim retVal As Int32 = 0           retVal = CType(command.Parameters("@RetVal").Value, Int32)           If (0 <> retVal) Then               Response.Write("The insert failed")           Else               Response.Write("The insert succeeded.")           End If           command.Dispose()           connection.Close()           connection.Dispose()       End Sub             </script>       </HEAD>       <body>            <form id="Form1" method="post" runat="server">                 <asp:Label id="Label1" runat="server" Width="152px" graphics/ccc.gif Height="16px">CustomerID:</asp:Label>                 <asp:TextBox id="TextBox1" runat="server" Width="200px" Height="21px"></ graphics/ccc.gif asp:TextBox>                 <p/>                      <asp:Label id="Label2" runat="server" Width="152px" graphics/ccc.gif Height="16px">Company Name:</asp:Label>                      <asp:TextBox id="TextBox2" runat="server" Width="200px"></ graphics/ccc.gif asp:TextBox>                 <p/>                      <asp:Button OnClick="Button1_Click" id="Button1" runat="server" graphics/ccc.gif Text="Button"></asp:Button>            </form>            </P>       </body>  </HTML> 

Another option to using stored procedures is using parameters in inline SQL text:

 private void Page_Load(object sender, System.EventArgs e)  {      String customerID = string.Empty;       String companyName = string.Empty;       //Retrieve the Customer ID from the form element "CustomerID"       customerID = Request.Form.Get("CustomerID");       companyName = Request.Form.Get("CompanyName");       SqlCommand command = new SqlCommand("AddCustomer");  command.CommandText="INSERT INTO CUSTOMERS (CustomerID,CompanyName) VALUES graphics/ccc.gif (@CustomerID,@CompanyName)";   command.CommandType = System.Data.CommandType.Text;  SqlParameter tempParam = null;       //Create the input parameter for the CustomerID parameter       tempParam = new SqlParameter ("@CustomerID", SqlDbType.Char);       tempParam.Direction = ParameterDirection.Input;       tempParam.Size = customerID.Length;       tempParam.Value = customerID;       //Add the parameter directly to the collection       command.Parameters.Add(tempParam);       //Create the parameter using the Add method and return the parameter       tempParam = command.Parameters.Add ("@CompanyName",SqlDbType.VarChar, graphics/ccc.gif companyName.Length);       //Access the parameter object directly       tempParam.Direction = ParameterDirection.Input;       tempParam.Value = companyName;       SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Persist graphics/ccc.gif Security Info=False;Initial Catalog=Northwind;Data Source=at1lt-3165-03");       command.Connection = connection;       command.Connection.Open();       command.ExecuteNonQuery();       command.Dispose();       connection.Close();       connection.Dispose();  } 

You are now able to use named parameters instead of using question marks for parameter placeholders. These examples are similar to ADO coding, with the notable exception of the new ExecuteNonQuery method and using constructors for objects.

Now that you have seen how to execute stored procedures and can use parameterized queries, you're ready to work with the real "meat" of ADO.NET ”returning data.

Disconnected Designs

One of the key differences between ADO and ADO.NET development is the concept of disconnected development . Disconnected development has everything to do with how you retrieve data.

The term disconnected refers to the fact that you do not maintain an open connection with the database while the data is in use. It is essentially a snapshot of the data. Using disconnected designs in web applications can be crucial to the success or failure of an application because maintaining open connections to a database can be expensive: More open connections means more memory that needs to be managed both by SQL Server and the Internet Information Services (IIS) server. By disconnecting the data, you can relinquish the database connection back to a pool of available connections where they might be reused again, which increases performance on high-volume websites .

ADO introduced developers to disconnected development by enabling them to set the ActiveConnection property of a Recordset object to Nothing . By setting the connection to Nothing , the underlying data is retained while the connection can be returned to the connection pool. The problem is that properly disconnecting a recordset requires using a client-side, static cursor with batch optimistic locking (say that five times fast!) The default behavior of ADO was not to use client-side static cursors. Instead, ADO provides read-only server-side cursors as a default. Failure to set the properties correctly yields a recordset that did not disconnect when it was instructed to do so. This was a common bug in many web applications, and its results could be catastrophic to an otherwise healthy web application.

Many ADO web developers have been developing with Visual Basic for years, and learned to access databases by using DAO. The DAO development model urged the developer to update resultsets directly and call the Update method to commit the underlying changes. Developers that were familiar with this model applied the same design patterns in ASP applications by using ADO. This action is an expensive operation in server-side applications because it keeps the connection to the database open for a longer period of time than necessary. Again, the result was applications that were not as scalable as they could be.

You've no doubt heard the term stateless repeatedly with respect to web-application development. The concept of disconnecting goes hand-in-hand with stateless design. The goal is to create and connect to resources as late as possible, perform a small unit of work, and release resources as soon as possible. By using disconnected designs, you can increase server scalability as the number of connections open at any given time is reduced.

Relational Versus Hierarchical Models

The release of ADO 2.5 introduced limited XML support for ADO, but it does not use XML natively. It supports exposing hierarchical data through the Data Shaping Service for OLEDB, persisting relational data as XML through the Recordset object's Save method, and accessing XML through the Stream object. None of these features were available in ADO until version 2.5, which shows how development with ADO has evolved since its inception.

It is possible to create XML from an ADO Recordset , but the underlying XML is riddled with namespaces and an inline XDR Schema that proves difficult for novices. As previously mentioned, XML support was an afterthought, so ADO did not use XML natively ”the native data format for ADO was Advanced Data Tablegram (ADTG).

ADO was not particularly good at delivering hierarchical data. The MSDataShape provider was a useful tool, but developing useful applications using it proved difficult in the middle-tier. Providing parameterized queries as the basis for the shaped data meant that the underlying data could not be persisted by using XML. Restricting the amount of data returned required elaborate WHERE clauses and RELATE operations in the shaped statement.

ADO is good at working with Data Manipulation Language (DML), such as INSERT or DELETE statements, but has limited support for providing information regarding its underlying schema. For example, determining which columns comprise a composite primary key is not possible using ADO without querying the database's system tables for the information. ADOX was introduced to provide this type of information, but required yet another library reference to maintain.

As you'll see, ADO.NET addresses these limitations by using XML as its underlying store. XML is returned natively from the DataSet , DataRelations define relations between DataTables contained in a DataSet , and XML Schemas define the underlying structures. Other important benefits of using XML are also notable: XML can be passed through firewalls, so security risks of passing ADO Recordsets remotely no longer exist. XML is also inherently disconnected and persistent, which gives increased asynchronous support for your applications.

The Key Components of ADO.NET

Now that you're familiar with the Connection and Command objects in ADO.NET, now it's time to address the second pair of core component types: DataReader and DataAdapter .

The DataReader Class

The DataReader class is implemented like the other reader classes found in .NET. It provides forward-only, read-only access to an underlying stream of data. The DataReader can increase scalability in your applications because only one row of data at a time is ever in memory[3].

Chapter 7, "ASP.NET Extensibility with XML," introduced you to XmlTextReader and XmlNodeReader classes. Recall that the primary design pattern for reading data was to use a While loop:

 While(reader.Read())  {       //Inspect the reader's exposed properties here  } 

DataReader classes are used in the same manner. This example selects everything from the Northwinds database and outputs it as an HTML table. Create a new Visual Basic web form called WebForm1 . Open the code view for webform1.aspx.vb and paste the code in Listing 8.5.

Listing 8.5 A Sample Application Using the SqlDataReader
 Imports System  Imports System.Data.SqlClient  Imports System.Collections  Public Class WebForm1    Inherits System.Web.UI.Page  #Region " Web Form Designer Generated Code "    'This call is required by the Web Form Designer.    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()    End Sub    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) graphics/ccc.gif Handles MyBase.Init      'CODEGEN: This method call is required by the Web Form Designer      'Do not modify it using the code editor.      InitializeComponent()    End Sub  #End Region    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) graphics/ccc.gif Handles MyBase.Load      DumpTable("SELECT * FROM Customers", "User ID=sa;password=;Persist Security graphics/ccc.gif Info=False;Initial Catalog=Northwind;Data Source=p450")    End Sub    Public Sub DumpTable(ByVal query As String, ByVal connectionString As  String)      Dim connection As SqlConnection = New SqlConnection(connectionString)      Dim command As SqlCommand = New SqlCommand(query)      command.CommandType = CommandType.Text      command.Connection = connection      command.Connection.Open()      Dim reader As SqlDataReader = command.ExecuteReader (CommandBehavior.CloseConnection)      Response.Write("<table border=""1"" cellpadding=""1"" cellspacing=""1"">")      If Not reader.IsClosed() Then        While (reader.Read())          'We now have an entire row to interrogate          Dim loopVar As Integer          Response.Write("<tr>")          For loopVar = 0 To reader.FieldCount - 1            If reader.IsDBNull(loopVar) Then              Response.Write("<td></td>")            Else              Response.Write("<td>" & reader.GetValue(loopVar) & "</td>")            End If          Next loopVar          Response.Write("</tr>")        End While      End If      Response.Write("</table>")      reader.Close()      command.Dispose()      connection.Close()      connection.Dispose()    End Sub  End Class 

Inside the While loop, you have access to a variety of properties and methods. You can also access individual columns instead of relying on their ordinal position:

 While (reader.Read())      Response.Write("<tr>")      Response.Write("<td>" & reader("CustomerID").ToString() & "</td>")      Response.Write("</tr>")  End While 
Handling Database Nulls

A key difference between working with ASP and ASP.NET is how database nulls are handled. Using Response.Write() to output a null value was not a problem in ASP. In ASP.NET, nulls are not allowed to be output. You can deal with nulls in several ways: One way is to explicitly test for a null by using the IsDbNull method of the Reader .

 Dim idx As Integer = reader.GetOrdinal("CompanyName")  While (reader.Read())      Response.Write("<tr>")      If reader.IsDBNull(idx) Then          Response.Write("<td>IsDbNull</td>")      Else          Response.Write("<td>" & reader("CompanyName").ToString() & "</td>")      End If      Response.Write("</tr>")  End While 

Another method for handling database null values is to use the GetString method of the Reader . It automatically converts the null value to an empty string:

 Dim idx As Integer = reader.GetOrdinal("CompanyName")  While (reader.Read())      Response.Write("<tr>")      Response.Write("<td>" & reader.GetString(idx) & "</td>")      Response.Write("</tr>")  End While 

The DataReader class can generate XML on the fly. In this example, an HTML table was generated but this could easily be adapted to create XML data. As you see in the next section, there are better ways to get at the underlying XML data.

The DataSet Class

By now, you're probably wondering when we will be getting to the XML material. Well, we're almost there. We're at the right class level, but we still have a bit to go to show how to read, write, and update XML using the DataSet class.

The DataSet class is one of the key parts of XML integration in ADO.NET. In fact, it is a key feature of ADO.NET as a whole. This section shows you that it is one of several objects that make working with XML and ADO.NET so easy.

Figure 8.5 shows the hierarchy of classes in the DataSet class. For brevity, the ExtendedParameters collections of the DataTable and DataColumn classes were left out. As you can see, the class hierarchy is complex. But after you work with the DataSet , you'll realize that it's intuitive.

Figure 8.5. The hierarchy of classes within the DataSet class.
graphics/08fig05.gif

So, what are you looking at? It looks like an object representation of a database. In fact, that is the best analogy for the DataSet class: an in-memory database. The DataSet class is central to ADO.NET's support of disconnected and distributed development. Unlike the ADODB.Recordset class that holds a single tabular representation of data, the DataSet class holds multiple tables from potentially different sources in memory. It is a container for a collection of DataTable classes that can manage the relations between the tables it contains.

Look at this example of filling a DataSet with data:

 Public Function GetDS(ByVal connectionString As String) As DataSet      Dim connection As SqlConnection = New SqlConnection(connectionString)      Dim command As SqlCommand = New SqlCommand()      command.CommandText = "SELECT CustomerID, CompanyName FROM Customers"      command.CommandType = CommandType.Text      command.Connection = connection      Dim adapter As SqlDataAdapter = New SqlDataAdapter()      Dim ds As DataSet = New DataSet()      adapter.SelectCommand = command      connection.Open()  adapter.Fill(ds, "Customers")  connection.Close()      connection.Dispose()      adapter.Dispose()      Return (ds)  End Function 

This example starts out by using a Connection and a Command object, which is similar to previous examples. Then an Adapter object is used to fill a DataTable , called Customers , in the DataSet class. Because the DataSet has already been filled with data, it is safe to close the open connection to the database.

A DataSet contains at least one DataTable . When the Adapter.Fill() method is called, you are specifying which DataTable inside the DataSet to fill with data.

This is an obvious source of confusion for many ADO programmers. You aren't really filling the DataSet with data ”you are filling a table contained in the DataSet with data. Furthermore, the name of the DataTable , Customers , has nothing to do with the source of the data itself: This is just a name that the programmer uses to keep track of the different tables contained.

Rewrite your GetDS function in the previous example to help clarify this point. This time, fill two tables in the DataSet and name them to show that the name of the underlying table in the DataSet has no bearing on the name of the database table ”it is simply a naming convenience:

 Public Function GetDS(ByVal connectionString As String) As DataSet       Dim connection As SqlConnection = New SqlConnection(connectionString)       Dim command As SqlCommand       Dim customerAdapter As SqlDataAdapter       Dim orderAdapter As SqlDataAdapter       Dim ds As DataSet = New DataSet()       'Create the Command object to select from the Customers database       'table and append it to the adapter.       command = New SqlCommand()       With command           .CommandText = "SELECT CustomerID, CompanyName FROM Customers"           .CommandType = CommandType.Text           .Connection = connection       End With  customerAdapter = New SqlDataAdapter()   customerAdapter.SelectCommand = command  'Create the Command object to select from the Orders database       'table and append it to the adapter.       command = New SqlCommand()       With command          .CommandText = "Orders"          .CommandType = CommandType.TableDirect          .Connection = connection      End With      orderAdapter = New SqlDataAdapter()      orderAdapter.SelectCommand = command      connection.Open()  customerAdapter.Fill(ds, "NorthwindCustomers")   orderAdapter.Fill(ds, "NorthwindOrders")  connection.Close()      connection.Dispose()  customerAdapter.Dispose()   orderAdapter.Dispose()  Return (ds)  End Function 

This revision of the GetDS uses two different Adapters ; each has its own associated SelectCommand .

DataSet Support for XML

Now for the XML material. Once filled with data, you can use the methods of the DataSet to read, expose, and manipulate XML data. Several key methods exposed by the DataSet class facilitate working with XML data. Table 8.5 lists the methods that facilitate working with XML data in the DataSet class.

Table 8.5. XML Support Methods of the DataSet Class

Method Name

Description

GetXml

Returns the XML representation of the data stored in the DataSet .

GetXmlSchema

Returns the XSD Schema for the XML representation of the data stored in the DataSet .

InferXmlSchema

Infers the XML Schema from the XML data into the DataSet .

ReadXml

Reads XML data and schema into the DataSet .

ReadXmlSchema

Reads the XML Schema into the DataSet .

WriteXml

Writes the XML Schema and data from the DataSet.

WriteXmlSchema

Writes an XML Schema representation of the structure of the DataSet.

So far, you have only retrieved tabular data from a database. Using the XML support methods of the DataSet makes reading and writing XML simple. Start by using your GetDS example method to populate a DataSet with data from a database and then output the contents as XML.

Create a new C# web application project and create a C# web form called WebForm1.aspx . Paste the code found in Listing 8.6 into the WebForm1.aspx page.

Listing 8.6 Extracting XML from the DataSet
 <%@ Page language="c#" Debug="true"%>  <%@ Import Namespace="System.Data"%>  <%@ Import Namespace="System.Data.SqlClient"%>  <%@ Import Namespace="System.Xml"%>  <%@ Import Namespace="System.Configuration"%>  <script language="C#" runat="server" >       public DataSet GetDS(String connectionString)       {         SqlConnection connection = new SqlConnection(connectionString);          SqlCommand command;          SqlDataAdapter customerAdapter;          SqlDataAdapter orderAdapter;          DataSet ds  = new DataSet();          //Create the Command object to select from the Customers database          //table and append it to the adapter.          command = new SqlCommand();          command.CommandText = "SELECT CustomerID, CompanyName FROM Customers";          command.CommandType = CommandType.Text;          command.Connection = connection;          customerAdapter = new SqlDataAdapter();          customerAdapter.SelectCommand = command;          //Create the Command object to select from the Orders database          //table and append it to the adapter.          command = new SqlCommand();          command.CommandText = "SELECT * FROM Orders";          command.CommandType = CommandType.Text;          command.Connection = connection;          orderAdapter = new SqlDataAdapter();          orderAdapter.SelectCommand = command;          connection.Open();          customerAdapter.Fill(ds, "NorthwindCustomers");          orderAdapter.Fill(ds, "NorthwindOrders");          connection.Close();          connection.Dispose();          customerAdapter.Dispose();          orderAdapter.Dispose();          return (ds);          }       private void Page_Load(object sender, System.EventArgs e)       {           String connectionString = "User ID=sa;Password=;Data Source=localhost;Initial graphics/ccc.gif Catalog=Northwind";            DataSet ds = GetDS(connectionString);            Response.ContentType = "text/xml";            ds.WriteXml(Response.OutputStream,XmlWriteMode.IgnoreSchema);            ds.Dispose();       }  </script> 

The results are structured similar to the following:

 <NewDataSet>    <NorthwindCustomers>      <CustomerID>WOLZA</CustomerID>      <CompanyName>Wolski  Zajazd</CompanyName>    </NorthwindCustomers>    <NorthwindOrders>      <OrderID>10248</OrderID>      <CustomerID>VINET</CustomerID>      <EmployeeID>5</EmployeeID>      <OrderDate>1996-07-04T00:00:00.0000000-04:00</OrderDate>      <RequiredDate>1996-08-01T00:00:00.0000000-04:00</RequiredDate>      <ShippedDate>1996-07-16T00:00:00.0000000-04:00</ShippedDate>      <ShipVia>3</ShipVia>      <Freight>32.38</Freight>      <ShipName>Vins et alcools Chevalier</ShipName>      <ShipAddress>59 rue de l'Abbaye</ShipAddress>      <ShipCity>Reims</ShipCity>      <ShipPostalCode>51100</ShipPostalCode>      <ShipCountry>France</ShipCountry>    </NorthwindOrders>  </NewDataSet> 

You can specify how each column is output from the DataSet by using the DataColumn object's ColumnMapping property to indicate if the column should be output as an attribute, element, text, or not at all (hidden):

 ds.Tables["NorthwindCustomers"].Columns["CustomerID"].ColumnMapping = graphics/ccc.gif MappingType.Attribute;  ds.Tables["NorthwindCustomers"].Columns["CompanyName"].ColumnMapping = graphics/ccc.gif MappingType.Attribute; 

This changes the XML output of the NorthwindCustomers data table to output the following:

 <NewDataSet>    <NorthwindCustomers CustomerID="WOLZA" CompanyName="Wolski  Zajazd"/>  <NewDataSet> 

You can also specify your XML Schema and table mappings. This is discussed in depth later in this section.

Another means of working with XML and the DataSet object is through the ReadXml method. This method reads an XML representation of the data and optional inline schema to populate all the tables in the DataSet .

Reuse the sample.xml file from Chapter 7:

 <?xml version="1.0" encoding="utf-8" ?>  <Customers xmlns="urn:foo:bar">       <Customer id="ALFKI">            <CompanyName>Alfreds Futterkiste</CompanyName>            <Contact>                 <FirstName>Maria</FirstName>                 <LastName>Anders</LastName>                 <Title>Sales Representative</Title>            </Contact>       </Customer>       <Customer id="LOWES">            <CompanyName>Lowe&apos;s</CompanyName>            <Contact>                 <FirstName>Keth</FirstName>                 <LastName>Bunn</LastName>                 <Title>Assistant Manager</Title>            </Contact>       </Customer>       <Customer id="VELVE">            <CompanyName>The Velvet Room</CompanyName>            <Contact>                 <FirstName>Rodney</FirstName>                 <LastName>Wade</LastName>                 <Title>Bar Manager</Title>            </Contact>       </Customer>       <Customer id="GABST">            <CompanyName>Georgia&apos;s Best Pressure and Stain</CompanyName>            <Contact>                 <FirstName>Steve</FirstName>                 <LastName>Durling</LastName>                 <Title>Owner</Title>            </Contact>       </Customer>       <Customer id="VBDNA">            <CompanyName>Kirk Allen Evans Consulting, Inc.</CompanyName>            <Contact>                 <FirstName>Kirk Allen</FirstName>                 <LastName>Evans</LastName>                 <Title>Owner</Title>            </Contact>       </Customer>  </Customers> 

By using this XML file, you can load the XML data into a DataSet . Note that you do not load the XML into a specific table. Rather, the XML represents all the data contained in the different DataTable objects contained in the DataSet :

 <%@ Page language="c#" %>  <%@ Import Namespace="System.Data"%>  <%@ Import Namespace="System.Xml"%>      <link rel="stylesheet"  type="text/css" href="ADOSamples.css">  <script language="C#" runat="server">            private void Page_Load(object sender, System.EventArgs e)            {                System.Xml.XmlTextReader reader = new graphics/ccc.gif XmlTextReader(Server.MapPath("sample.xml"));                 DataSet ds = new DataSet("Northwind Data");  ds.ReadXml(reader,XmlReadMode.InferSchema);  reader.Close();                 DumpTable(ds);                 ds.Dispose();            }            public void DumpTable(DataSet ds)            {                foreach(DataTable table in ds.Tables)                 {                     Response.Write("<h1>" + table.TableName + "</h1>");                      Response.Write("<table border=\"1\" cellpadding=\"2\" graphics/ccc.gif cellspacing=\"2\">");                      Response.Write("<tr>");                      foreach(DataColumn column in table.Columns)                      {                          Response.Write("<th>" + column.ColumnName + "</th>");                      }                      Response.Write("</tr>");                      foreach(DataRow row in table.Rows)                      {                          Response.Write("<tr>");                           foreach(DataColumn column in table.Columns)                           {                               Response.Write("<td>" + row[column].ToString() + "</td>");                           }                           Response.Write("</tr>");                      }                      Response.Write("</table>");                 }            }  </script> 

The output shown in Figure 8.6 shows that two data tables were created with the names Customer and Contact .

Figure 8.6. The Customer and Contact data tables are created by using an XML document as the data source.
graphics/08fig06.gif

The previous example told ADO.NET to guess at the schema based on the XML's contents by specifying the XmlReadMode.InferSchema parameter of the ReadXml() method. You can inspect the schema created by ADO.NET by using the WriteXml method and specifying the write mode as WriteSchema , as shown here:

 <%@ Page language="c#" %>  <%@ Import Namespace="System.Data"%>  <%@ Import Namespace="System.Xml"%>  <script language="C#" runat="server">            private void Page_Load(object sender, System.EventArgs e)            {                System.Xml.XmlTextReader reader = new  XmlTextReader(Server.MapPath("sample.xml"));                 DataSet ds = new DataSet("Northwind Data");                 ds.ReadXml(reader,XmlReadMode.InferSchema);                 reader.Close();                 Response.ContentType = "text/xml";  ds.WriteXml(Response.OutputStream,XmlWriteMode.WriteSchema);  ds.Dispose();            }  </script> 

Figure 8.7 shows the output schema.

Figure 8.7. The output schema from a DataSet .
graphics/08fig07.gif

The problem with inferring a schema is that the inference depends on the data. This is not always a desirable side effect, so you want to specify your own schema prior to loading the XML data. By specifying the XML Schema, you can control what the DataSet looks like. Table 8.6 shows how the elements in XSD Schemas are mapped to DataSet objects.

Table 8.6. Mappings of Schemas to DataSet Objects

XML Schema Element

DataSet Mapping

ComplexType

DataTable

Nested complexTypes

DataTables with DataRelations

key or unique

UniqueConstraint

KeyRef

ForeignKeyConstraint

Take a look at how to read a schema into a DataSet . The example begins in Listing 8.7 by creating another version of the GetDS method. This version loads a schema into a DataSet from a file by using DataSet.ReadXmlSchema and then loads the DataSet with data by using the LoadXml method.

Listing 8.7 The GetDS Method Reading into a DataSet
 public DataSet GetDS(String connectionString, String schemaFilePath, String xmlFilePath)           {                DataSet ds = new DataSet();  ds.ReadXmlSchema(schemaFilePath);   ds.ReadXml(xmlFilePath, XmlReadMode.IgnoreSchema);   ds.AcceptChanges();  try                 {  ds.EnforceConstraints = true;  }                 catch(Exception e)                 {                     Response.Write("<h1>The input file failed validation.</h1>");                      foreach(DataTable table in ds.Tables)                      {  DataRow [] errorRows = table.GetErrors();  if (errorRows != null)                           {                               Response.ContentType = "text/html";                                Response.Clear();                                Response.Write("<h3>Error in " + table.TableName + "</ graphics/ccc.gif h3>");                                foreach(DataRow row in errorRows)                                {  Response.Write ("<i>" + row.RowError + "</i><br/>");   DataColumn [] errorColumns = row.GetColumnsInError();  if (errorColumns.Length > 0)                                     {                                        Response.Write ("The following columns are in graphics/ccc.gif error:");                                         Response.Write("<ul>");                                         foreach(DataColumn column in errorColumns)                                         {                                              Response.Write("<li>" + column.ColumnName + graphics/ccc.gif "</li>");                                         }                                         Response.Write("</ul>");                                     }                               }                          }                     }                     ds.Dispose();                     throw (new System.Xml.Schema.XmlSchemaException("One or more tables in graphics/ccc.gif the DataSet failed validation.",e));                }                return(ds);           } 

This example loads an XML Schema into the DataSet , loads the XML data into the DataSet , and applies the changes to the DataSet by using the AcceptChanges() method. When the data is loaded into the DataSet , you specified the XmlReadMode.IgnoreSchema parameter value. This value tells the processor to ignore the layout of the XML data being read. You can use this setting so that the schema populated using the ReadXmlSchema method is not overwritten. The EnforceConstraints property is then set to true , which tells the DataSet to validate the XML data against the underlying schema.

After data is contained in a DataSet , you can also output its contents as XML by using the WriteXml method . While you output the XML, you might want to control how it is structured. For example, you might read the XML as all elements, and easily output the XML as all attributes. To accomplish this task without using ADO.NET requires either a small but unintuitive XSLT transformation (as described in Chapter 3) or a looping algorithm, but ADO.NET makes this task very simple.

Look at an example of how to load XML data and an XML Schema file to populate a DataSet . By specifying the schema, you can specify what the schema should look like rather than have it inferred based on the XML data that's being read. The XML Schema is shown in Listing 8.8.

Listing 8.8 The sample1.xsd Schema
 <?xml version="1.0"?>  <xs:schema id="Customers" targetNamespace="http://tempuri.org/sample1.xsd" graphics/ccc.gif xmlns:mstns="http://tempuri.org/sample1.xsd" xmlns="http://tempuri.org/sample1.xsd" graphics/ccc.gif xmlns:xs="http://www.w3.org/2001/XMLSchema" graphics/ccc.gif xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" graphics/ccc.gif elementFormDefault="qualified"&gt;      <xs:complexType name="contactType">            <xs:sequence>            <xs:element name="FirstName" type="xs:string" minOccurs="0" />            <xs:element name="LastName" type="xs:string" minOccurs="0" />            <xs:element name="Title" type="xs:string" minOccurs="0" />            </xs:sequence>            </xs:complexType>    <xs:element name="Customers" msdata:IsDataSet="true" msdata:EnforceConstraints="False">      <xs:complexType>        <xs:choice maxOccurs="unbounded">          <xs:element name="Customer">            <xs:complexType>              <xs:sequence>                <xs:element name="CompanyName" type="xs:string" minOccurs="0" graphics/ccc.gif msdata:Ordinal="0" />                <xs:element name="Contact" minOccurs="0" maxOccurs="unbounded" graphics/ccc.gif type="contactType">                </xs:element>              </xs:sequence>              <xs:attribute name="id" form="unqualified" type="xs:string" />            </xs:complexType>          </xs:element>        </xs:choice>      </xs:complexType>    </xs:element>  </xs:schema> 

The data to load into the DataSet comes from an XML file, sample.xml . This XML file is valid against the schema depicted in Listing 8.8 and uses an element-dominant form. The XML file sample.xml is shown in Listing 8.9.

Listing 8.9 sample.xml
 <?xml version="1.0" encoding="utf-8"?>  <Customers xmlns="http://tempuri.org/sample1.xsd">      <Customer id="ALFKI">          <CompanyName>Alfreds Futterkiste</CompanyName>          <Contact>              <FirstName>Maria</FirstName>              <LastName>Anders</LastName>              <Title>Sales Representative</Title>          </Contact>      </Customer>      <Customer id="LOWES">          <CompanyName>Lowe's</CompanyName>          <Contact>              <FirstName>Keth</FirstName>              <LastName>Bunn</LastName>              <Title>Assistant Manager</Title>          </Contact>      </Customer>      <Customer id="VELVE">          <CompanyName>The Velvet Room</CompanyName>          <Contact>              <FirstName>Rodney</FirstName>              <LastName>Wade</LastName>              <Title>Bar Manager</Title>          </Contact>      </Customer>      <Customer id="GABST">          <CompanyName>Georgia's Best Pressure and Stain</CompanyName>          <Contact>              <FirstName>Steve</FirstName>              <LastName>Durling</LastName>              <Title>Owner</Title>          </Contact>      </Customer>      <Customer id="VBDNA">          <CompanyName>Kirk Allen Evans Consulting, Inc.</CompanyName>          <Contact>              <FirstName>Kirk Allen</FirstName>              <LastName>Evans</LastName>              <Title>Owner</Title>          </Contact>      </Customer>  </Customers> 

With a simple routine using a DataSet , you can load the XML using the schema from Listing 8.8 and the XML from Listing 8.9 to alter its structure to an attribute-dominant form. Notice also that you are reusing the GetDS method from Listing 8.5. To run this example, create a new Visual Basic .NET web application called ADOVB and create a new web form called Listing8_10.aspx . Remove all HTML code from the .aspx page, leaving only the Page directive:

 <%@ Page Language="vb" AutoEventWireup="false" Codebehind="Listing8_10.aspx.vb" graphics/ccc.gif Inherits="ADOVB.Listing8_10"%> 

Open the code-behind file, Listing8_10.aspx.vb , and enter the code from Listing 8.10 into it.

Listing 8.10 A Working Example of Transforming an XML Document From Element-Centric Form to Attribute-Centric Form
 Imports System.Data  Imports System.Data.SqlClient  Public Class Listing8_10      Inherits System.Web.UI.Page  #Region " Web Form Designer Generated Code "      'This call is required by the Web Form Designer.      <System.Diagnostics.DebuggerStepThrough()> Private Sub  InitializeComponent()      End Sub      Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) graphics/ccc.gif Handles MyBase.Init          'CODEGEN: This method call is required by the Web Form Designer          'Do not modify it using the code editor.          InitializeComponent()      End Sub  #End Region      Public Function GetDS(ByVal schemaFilePath As String, ByVal xmlFilePath As String) As graphics/ccc.gif DataSet          Dim ds As DataSet = New DataSet()          ds.ReadXmlSchema(schemaFilePath)          ds.ReadXml(xmlFilePath, XmlReadMode.IgnoreSchema)          ds.AcceptChanges()          Try              ds.EnforceConstraints = True          Catch e As System.Exception              Response.Write("<h1>The input file failed validation.</h1>")              Dim table As DataTable              For Each table In ds.Tables                  Dim errorRows() As DataRow = table.GetErrors()                  If errorRows Is Nothing Then                      Response.ContentType = "text/html"                      Response.Clear()                      Response.Write("<h3>Error in " + table.TableName + "</h3>")                      Dim row As DataRow                      For Each row In errorRows                          Response.Write("<i>" + row.RowError + "</i><br/>")                          Dim errorColumns() As DataColumn = row.GetColumnsInError()                          If errorColumns.Length > 0 Then                              Response.Write("The following columns are in error:")                              Response.Write("<ul>")                              Dim column As DataColumn                              For Each column In errorColumns                                  Response.Write("<li>" + column.ColumnName + "</li>")                              Next column                              Response.Write("</ul>")                          End If                      Next row                  End If              Next table              ds.Dispose()              Throw (New System.Xml.Schema.XmlSchemaException("One or more tables in the graphics/ccc.gif DataSet failed validation.", e))          End Try          ds.Dispose()          Return (ds)      End Function      Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) graphics/ccc.gif Handles MyBase.Load          'Put user code to initialize the page here          Dim ds As DataSet = GetDS(Server.MapPath("sample1.xsd"), graphics/ccc.gif Server.MapPath("sample.xml"))          Dim table As DataTable          For Each table In ds.Tables              Dim column As DataColumn              For Each column In table.Columns                  column.ColumnMapping = MappingType.Attribute          Next      Next      Response.ContentType = "text/xml"      ds.WriteXml(Response.OutputStream)      table.Dispose()      ds.Dispose()      End Sub  End Class 

Run the application. When the Page_Load event fires, each column in each table in the DataSet is changed from element-dominant form to attribute-dominant form by changing the ColumnMapping to MappingType.Attribute . The results are as follows :

 <Customers xmlns="http://tempuri.org/sample1.xsd">    <Customer CompanyName="Alfreds Futterkiste" id="ALFKI" Customer_Id="0">      <Contact FirstName="Maria" LastName="Anders" Title="Sales Representative" graphics/ccc.gif Customer_Id="0" />    </Customer>    <Customer CompanyName="Lowe's" id="LOWES" Customer_Id="1">      <Contact FirstName="Keth" LastName="Bunn" Title="Assistant Manager" Customer_Id="1" / graphics/ccc.gif >    </Customer>    <Customer CompanyName="The Velvet Room" id="VELVE" Customer_Id="2">      <Contact FirstName="Rodney" LastName="Wade" Title="Bar Manager" Customer_Id="2" />    </Customer>    <Customer CompanyName="Georgia's Best Pressure and Stain" id="GABST" Customer_Id="3">      <Contact FirstName="Steve" LastName="Durling" Title="Owner" Customer_Id="3" />    </Customer>    <Customer CompanyName="Kirk Allen Evans Consulting, Inc." id="VBDNA" Customer_Id="4">      <Contact FirstName="Kirk Allen" LastName="Evans" Title="Owner" Customer_Id="4" />    </Customer>  </Customers> 

With a small amount of code, you can easily change the structure of the XML representation of a DataSet simply by changing the ColumnMapping property of the DataColumn . This code also introduces the concept of multiple DataTables within a DataSet and demonstrates looping through the DataColumns of a table.

As you can see, support for XML and XML Schemas plays a large role in ADO.NET. You are able to validate data against a specified XML Schema, infer a schema based on the data in the DataSet , and extract the XML Schema and XML data from a populated DataSet .

You have seen that a DataSet contains one or more DataTable objects, and you've gotten a glimpse at how using XML and XSD Schemas can facilitate loading a DataSet object's underlying DataTables . So, what is a DataTable object? Read on to find out.

The DataTable Class

The DataTable class is the closest representation of an ADODB.Recordset class in ADO.NET. A better analogy is a table in a database. A DataTable provides structure to its data through the use of DataRow and DataColumn objects, enforces data integrity through a collection of Constraints , and manages how it is related to other DataTable objects through its ParentRelations and ChildRelations collections.

When the DataSet is populated using the Adapter.Fill() method, you can optionally specify the name of the DataTable to fill with data. If the DataTable does not already exist, it is created and appended to the collection of tables.

The ADODB.Recordset class does not have a mechanism to return an entire row of data, but instead returns individual Field objects. The model for the ADO.NET DataSet accesses entire DataRows , which are indexed using DataColumn objects to access individual column values.

DataRows

A DataRow represents a single row of data contained in a DataTable . DataRow objects can be added to a DataSet by using several different methods. Look at an example.

You will use an application setting variable from the web.config file to store the database connection string. Application settings are discussed in detail in Chapter 7, "ASP.NET Extensibility With XML."

Creating an Application Setting

To create an application setting, add an appSettings element as a child of the configuration element in the web.config file:

 <configuration>     <appSettings>        <add key="connectionString" value="User graphics/ccc.gif ID=sa;Password=;Initial  Catalog=Northwind;Data Source=localhost" />     </appSettings>     .     .     .  </configuration> 

After the web.config file is altered to include your connection string, you can create a new version of the GetDS method from Listing 8.7 to read from the database instead of from an XML file. Listing 8.11 shows several methods for adding rows of data to a DataSet . Add a new web form to your ADOVB application, called Listing8_11.aspx . Add a DataGrid control to the web form called DataGrid1 , and add the code from Listing 8.11 in the code-behind file, listing8 _ 11.aspx.vb :

Listing 8.11 Adding Rows of Data to a DataSet
 Imports System.Data  Imports System.Data.SqlClient  Imports System.Configuration  Public Class Listing8_11      Inherits System.Web.UI.Page      Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid  #Region " Web Form Designer Generated Code "      'This call is required by the Web Form Designer.      <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()      End Sub      Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) graphics/ccc.gif Handles MyBase.Init          'CODEGEN: This method call is required by the Web Form Designer          'Do not modify it using the code editor.          InitializeComponent()      End Sub  #End Region      Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) graphics/ccc.gif Handles MyBase.Load          'Put user code to initialize the page here          Dim connectionString As String = graphics/ccc.gif ConfigurationSettings.AppSettings.Item("connectionString")          Dim ds As DataSet = GetDS(connectionString)          Dim row As DataRow          'Directly manipulate the DataRow object          row = ds.Tables("NorthwindCustomers").NewRow()          row.Item("CustomerID") = "HLZNR"          row.Item("CompanyName") = "Inside XML"          ds.Tables("NorthwindCustomers").Rows.Add(row)          'Add a row using an array of values          Dim values() As String = Array.CreateInstance(GetType(String), 2)          values.SetValue("WORLY", 0)          values.SetValue("Inside ASP.NET", 1)          row = ds.Tables("NorthwindCustomers").Rows.Add(values)  values.SetValue("EVANS", 0)   values.SetValue("XML and ASP.NET", 1)   row = ds.Tables("NorthwindCustomers").LoadDataRow(values, True)  Dim dv As DataView = New DataView(ds.Tables("NorthwindCustomers"))          dv.RowStateFilter = DataViewRowState.Added         DataGrid1.DataSource = dv         DataGrid1.DataBind()         ds.Dispose()     End Sub     Public Function GetDS(ByVal connectionString As String) As DataSet         Dim connection As SqlConnection = New SqlConnection(connectionString)         Dim command As SqlCommand         Dim customerAdapter As SqlDataAdapter         Dim orderAdapter As SqlDataAdapter         Dim ds As DataSet = New DataSet()         'Create the Command object to select from the Customers database         'table and append it to the adapter.         command = New SqlCommand()         With command             .CommandText = "SELECT CustomerID, CompanyName FROM Customers"             .CommandType = CommandType.Text             .Connection = connection         End With         customerAdapter = New SqlDataAdapter()         customerAdapter.SelectCommand = command         'Create the Command object to select from the Orders database         'table and append it to the adapter.         command = New SqlCommand()         With command              .CommandText = "Select * from Orders"              .CommandType = CommandType.Text              .Connection = connection          End With          orderAdapter = New SqlDataAdapter()          orderAdapter.SelectCommand = command          connection.Open()          customerAdapter.Fill(ds, "NorthwindCustomers")          orderAdapter.Fill(ds, "NorthwindOrders")          ds.Relations.Add("CustomerOrders", graphics/ccc.gif ds.Tables("NorthwindCustomers").Columns("CustomerID"), graphics/ccc.gif ds.Tables("NorthwindOrders").Columns("CustomerID"))          connection.Close()          connection.Dispose()          customerAdapter.Dispose()          orderAdapter.Dispose()          Return (ds)      End Function  End Class 

In Listing 8.11, you can see three different ways to add a row of data to a DataTable . First, the DataTable.NewRow() method was used to create a new DataRow and set the values for the specified columns. Next, you create a string array and add a DataRow to the DataTable by using the array to specify the values being added, positionally. Finally, you use the DataTable.LoadDataRow() method to search for a particular DataRow based on the values specified. If the DataRow is found, it is updated. If the DataRow is not found, it is created with the specified values.

Interestingly enough, when the LoadDataRow method is called, a row is added to the DataSet , but its RowState is unchanged. RowStates are explained in the section, "Versioning in the DataSet." The output of the code is shown in Figure 8.8.

Figure 8.8. The output from Listing 8.11 shows the two newly added rows.
graphics/08fig08.gif

The ADODB.Recordset object does not have the capability to return an entire row of data. Instead, it exposes methods such as MoveNext() and MoveLast() . The DataRows collection in ADO.NET can be iterated over, which is another improvement over ADODB:

 Dim connectionString As String = AppSettings.Item("connectionString")         Dim ds As DataSet = GetDS(connectionString)         Dim row As DataRow         Response.Write("<table>")  For Each row In ds.Tables("NorthwindCustomers").Rows  Response.Write("<tr>")             Response.Write("<td>" & row.Item("CustomerID") & "</td>")             Response.Write("<td>" & row.Item("CompanyName") & "</td>")             Response.Write("</tr>")  Next  Response.Write("</table>") 

A useful feature of the ADODB.Recordset and its predecessors was the ability to locate data based on conditions. For example, DAO supplied the Seek() method, and ADO supplied a Filter property. ADO.NET achieves the same means through a Select() method, as shown here:

 Dim connectionString As String = AppSettings.Item("connectionString")          Dim ds As DataSet = GetDS(connectionString)          Dim row As DataRow  Dim foundRows() As DataRow = ds.Tables("NorthwindCustomers").Select("CustomerID LIKE graphics/ccc.gif 'TEST%'")   For Each row In foundRows  Response.Write("<tr>")              Response.Write("<td>" & row.Item("CustomerID") & "</td>")              Response.Write("<td>" & row.Item("CompanyName") & "</td>")              Response.Write("</tr>")  Next  

This example retrieved an array of DataRow objects that matched the Select criteria. Notice the use of wildcards in the Select criteria. Unlike ADO, ADO.NET supports complex expressions in the Select criteria.

DataColumns

Unlike the DataRow object, a DataColumn object does not contain data. A DataColumn object only contains schema information for a column in the DataTable . Table 8.7 lists the properties of the DataColumn class.

Table 8.7. Properties of the DataColumn Class

Member Name

Description

AllowDBNull

Boolean that indicates if the column is nullable.

AutoIncrement

Boolean that indicates if this column should be incremented for each new row added.

AutoIncrementSeed

Gets or sets the starting value for a field with a true value for its AutoIncrement property.

AutoIncrementStep

Gets or sets the increment step for a field with a true value for its AutoIncrement property.

Caption

Gets or sets the caption for the column when used in controls that support displaying DataSet captions.

ColumnMapping

Gets or sets the MappingType of the column. Specifies how the column will appear when written as XML ( Element , Attribute , Hidden , or SimpleContent (text)).

ColumnName

The name for the column.

Container

Gets the container for the component.

DataType

Gets or sets the base .NET Framework data type of data stored in the column. Read-only after a DataRow having the specified index column contains data.

DefaultValue

Gets or sets the default value when creating a new DataRow .

DesignMode

Gets a value that indicates whether the component is currently in design mode.

Expression

Gets or sets the expression used to filter rows, calculate values in a column, or create an aggregate column.

ExtendedProperties

Gets a collection of custom user information. Each property must be of type String to emit the property as XML.

MaxLength

Gets or sets the maximum length of a text column.

Namespace

Gets or sets the namespace for the XML representation of the column.

Ordinal

Gets the position of the column in the DataColumnCollection .

Prefix

Gets or sets the namespace prefix for the XML representation of the column.

ReadOnly

Gets or sets a Boolean that indicates if the column is readonly once an associated DataRow is populated with data.

Site

Gets or sets the site of the component.

Table

Gets or sets the DataTable to which the column belongs.

Unique

Gets or sets a Boolean that indicates if the values in the column must be unique. Adds a UniqueConstraint object to the DataTable.Constraints collection.

Working with DataColumn objects is similar to working with DataRow objects. The columns can be iterated over, and are indexed on ordinal position and column name:

 Dim connectionString As String = AppSettings.Item("connectionString")     Dim ds As DataSet = GetDS(connectionString)  Dim table As DataTable     Dim column As DataColumn     table = ds.Tables("NorthwindCustomers")     Response.Write("<h1>Columns of " & table.TableName & "</h1>")     Response.Write("<table>")     Response.Write("<tr>")     Response.Write("<th>ColumnName</th>")     Response.Write("<th>Nullable</th>")     Response.Write("<th>Data Type</th>")     Response.Write("<th>Unique?</th>")     Response.Write("<th>ColumnName</th>")     Response.Write("<th>MaxLength</th>")     Response.Write("</tr>")     For Each column In table.Columns         Response.Write("<tr>")         Response.Write("<td>" & column.ColumnName & "</td>")         Response.Write("<td>" & column.AllowDBNull & "</td>")         Response.Write("<td>" & column.DataType.ToString() & "</td>")         Response.Write("<td>" & column.Unique & "</td>")         Response.Write("<td>" & column.MaxLength & "</td>")         Response.Write("</tr>")     Next     Response.Write("</table>") 

You can use the DataRows and DataColumns collections to iterate through the entire DataSet . You can create a new version of the DumpTable method that was introduced at the beginning of this chapter. This new version emits an HTML table for each DataTable in the DataSet :

 Public Sub DumpTable(ByVal ds As DataSet)          Dim table As DataTable          Dim column As DataColumn          Dim row As DataRow          For Each table In ds.Tables              Response.Write("<h1>" & table.TableName & "</h1>")              Response.Write("<table>")              For Each row In table.Rows                  Response.Write("<tr>")                  For Each column In table.Columns                      Response.Write("<td>")                      Response.Write(row.Item(column.Ordinal))                      Response.Write("</td>")                  Next                  Response.Write("</tr>")              Next              Response.Write("</table>")          Next      End Sub 
Versioning in the DataSet

Each row has an associated state. These states are as follows:

  • Added ” The row has been added to a DataRowCollection , and AcceptChanges has not been called.

  • Deleted ” The row was deleted by using the Delete method of the DataRow .

  • Detached ” The row has been created but is not part of any DataRowCollection . A DataRow is in this state immediately after it is created and before it is added to a collection, or if it has been removed from a collection.

  • Modified ” The row has been modified and AcceptChanges has not been called.

  • Unchanged ” The row has not changed since AcceptChanges was last called.

In addition to having a state, each row can also have a version. To check if a row has a version, use the HasVersion method of the DataRow object. The DataSet object does not commit changes to the underlying data until the AcceptChanges method is called. This means that changes to a row can be undone and the values in the row can be returned to their original value.

To do this, the DataRow class maintains versions of its data. This is similar to the ADODB.Field object's Value , OriginalValue , and UnderlyingValue properties.

The valid versions are as follows:

  • Current The row contains current values.

  • Default The row contains its default values.

  • Original The row contains its original values.

  • Proposed The row contains proposed values.

The DataRow 's version is affected by using the BeginEdit , CancelEdit , EndEdit , and AcceptChanges methods of the DataRow . Table 8.8 describes what happens when data in a data row is edited.

Table 8.8. Effects on DataRowVersion

Called Method Name

Effect

DataRow.BeginEdit

Changing a value of a column in the row makes the Current and Proposed values available.

DataRow.CancelEdit

The Proposed value is deleted.

DataRow.EndEdit

The Proposed value becomes the Current value.

DataRow.AcceptChanges

The Proposed value becomes the Current value; the Original value remains.

DataTable.AcceptChanges

Original value is set to the Current value.

DataRow.RejectChanges

Version becomes Current . Proposed value is discarded.

DataViews

The data in a DataTable can be queried by using a Select method that supports SQL syntax. The array of DataRow objects returned from this method are static; that is, if the underlying DataTable changes, the array of DataRow objects returned from the Select method do not reflect the changes made. Requerying, rebuilding, and displaying these changes can be a time-consuming process. This problem is addressed by using the DataView object.

Simliar to views in a database, a DataView object represents a particular view of the underlying DataTable object. Changes made to the DataTable include changes to content, membership, and ordering[4]. Unlike a view in a database, DataView s cannot span multiple tables, exclude columns in the source table, or include columns not present in the source table.

Creating a DataView

To create a DataView , use the overloaded constructor to create a reference to a DataView object:

 Dim connectionString as String = AppSettings.Item("connectionString")  Dim ds as DataSet = GetDS(connectionString)  Dim table as DataTable = ds.Tables("NorthwindCustomers")  Dim view as DataView  view = New DataView(table, "CustomerID = 'ALFKI')", "CompanyName", graphics/ccc.gif DataViewRowState.CurrentRows) 

This version of the constructor accepts the DataTable to create a view over, the filter condition for the rows, the column to sort on, and the DataViewRowState , respectively.

The DataViewRowState parameter indicates what rows are to be included in the view. Table 8.9 lists the different enumeration members and their descriptions.

Table 8.9. Members of the DataViewRowState Enumeration

Member Name

Description

CurrentRows

The current row version of all Unchanged , Added , and Modified rows. This is the default.

Added

The current row version of all Added rows.

Deleted

The original row version of all Deleted rows.

ModifiedCurrent

The current row version of all Modified rows.

ModifiedOriginal

The original row version of all Modified rows.

None

No rows.

OriginalRows

The original row version of all Unchanged , Modified , and Deleted rows.

Unchanged

The current row version of all Unchanged rows.

By using DataViews , you can create separate views of the same table. You use DataViews in the next section, "Binding to Controls."

Binding to Controls

DataViews are typically used for binding to controls. In .NET, controls that support the IBindingList interface can bind to a control such as the <asp:DataGrid> control. Neither the DataSet nor the DataTable classes support this interface, so neither can be directly bound to a control. The DataTable class, however, exposes a method called DefaultView that returns a DataView . The DataView supports IBindingList , so it can be bound to a control.

You can bind a DataView to a control in several ways. In Listing 8.12, you create a DataView using the same table for three different views: added rows, changed rows, and unchanged rows. Note that all three use the same underlying table. The only difference is the RowStateFilter that uses one of the values from Table 8.9 to indicate which version of the rows should be shown in each data grid. Create a new web form called Listing8_12.aspx . Replace the Page directive and paste the code form Listing 8.12 into Listing8_12.aspx .

Listing 8.12 Working with Versioning in the DataSet
 <%@ Page Language="vb" %>  <%@Import Namespace="System.Xml"%>  <%@Import Namespace="System.Data"%>  <%@Import Namespace="System.Data.SqlClient"%>  <%@Import Namespace="System.Configuration"%>  <script language="visualbasic" runat="server">  Public Function GetDS(ByVal connectionString As String) As DataSet          Dim connection As SqlConnection = New SqlConnection(connectionString)          Dim command As SqlCommand          Dim customerAdapter As SqlDataAdapter          Dim orderAdapter As SqlDataAdapter          Dim ds As DataSet = New DataSet()          'Create the Command object to select from the Customers database          'table and append it to the adapter.          command = New SqlCommand()          With command              .CommandText = "SELECT CustomerID, CompanyName FROM Customers"              .CommandType = CommandType.Text              .Connection = connection          End With          customerAdapter = New SqlDataAdapter()          customerAdapter.SelectCommand = command          'Create the Command object to select from the Orders database          'table and append it to the adapter.          command = New SqlCommand()          With command              .CommandText = "Select * from Orders"              .CommandType = CommandType.Text              .Connection = connection          End With          orderAdapter = New SqlDataAdapter()          orderAdapter.SelectCommand = command          connection.Open()          customerAdapter.Fill(ds, "NorthwindCustomers")          orderAdapter.Fill(ds, "NorthwindOrders")          ds.Relations.Add("CustomerOrders", graphics/ccc.gif ds.Tables("NorthwindCustomers").Columns("CustomerID"), graphics/ccc.gif ds.Tables("NorthwindOrders").Columns("CustomerID"))          connection.Close()          connection.Dispose()          customerAdapter.Dispose()          orderAdapter.Dispose()          Return (ds)      End Function       Private Function GetRow(ByVal table as DataTable, ByVal customerID as string, ByVal graphics/ccc.gif companyName as String) as DataRow            dim row as DataRow = table.NewRow()            row("CustomerID") = customerID          row("CompanyName") = companyName          return(row)       End Function       Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) graphics/ccc.gif Handles MyBase.Load           Dim connectionString As String = graphics/ccc.gif ConfigurationSettings.AppSettings.Item("connectionString")           Dim ds As DataSet = GetDS(connectionString)           Dim table As DataTable = ds.Tables("NorthwindCustomers")           'Add some new rows           table.Rows.Add(GetRow(table,"STLTH","Charles Townsend"))           table.Rows.Add(GetRow(table,"JAMES","James Townsend"))           table.Rows.Add(GetRow(table,"MRRYK","Linda Evans"))           table.Rows.Add(GetRow(table,"RTIRD","Bob Evans"))           'Change some rows           Dim foundRows() As DataRow = table.Select("CustomerID in ('ALFKI','BLAUS', graphics/ccc.gif 'OCEAN','BOLID','BONAP')")            Dim row as DataRow          For Each row In foundRows                 select case row("CustomerID")                      case "ALFKI"                           row("CompanyName") = "Michelle Schultz"                      case "BLAUS"                           row("CompanyName") = "John Schultz"                      case "OCEAN"                           row("CompanyName") = "Madison Schultz"                      case "BOLID"                           row("CompanyName") = "Janice Hickey"                      case "BONAP"                           row("CompanyName") = "George Hickey"                 end select          Next          'Bind the 3 grids to the same DataTable, using different views          Dim view As DataView          view = New DataView(table)          view.Sort = "CompanyName"          view.RowStateFilter = DataViewRowState.Added          AddedRows.DataSource = view          AddedRows.DataBind()          ChangedRows.DataSource = New DataView(table, String.Empty, "CustomerID", graphics/ccc.gif DataViewRowState.ModifiedCurrent)          ChangedRows.DataBind()          OriginalRows.DataSource = New DataView(table, String.Empty, "CustomerID", graphics/ccc.gif DataViewRowState.Unchanged)          OriginalRows.DataBind()      End Sub  </script>  <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">  <HTML>       <HEAD>            <title>DataViewExample</title>            <meta name="GENERATOR" content="Microsoft Visual Studio.NET 7.0">            <meta name="CODE_LANGUAGE" content="Visual Basic 7.0">            <meta name="vs_defaultClientScript" content="JavaScript">            <meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ graphics/ccc.gif ie5">       </HEAD>       <body MS_POSITIONING="GridLayout">            <form id="Form1" method="post" runat="server">                 <h3>Added Rows</h3>                 <asp:DataGrid id="AddedRows" runat="server" Font-Names="Verdana,Tahoma, graphics/ccc.gif Arial" Width="500" BackColor="silver" BorderColor="#000000" EnableViewState="False">                      <HeaderStyle BackColor="black" ForeColor="white" Font-Bold="True" graphics/ccc.gif Font-Names="Verdana,Tahoma,Arial" Font-Size="10pt" />                 </asp:DataGrid>                 <h3>Changed Rows</h3>                 <asp:DataGrid   id="ChangedRows" runat="server" Font-Names="Verdana,Tahoma, graphics/ccc.gif Arial" Width="500" BackColor="silver"BorderColor="#000000" EnableViewState="False">                      <HeaderStyle BackColor="black" ForeColor="white" Font-Bold="True" graphics/ccc.gif Font-Names="Verdana,Tahoma,Arial" Font-Size="10pt" />                 </asp:DataGrid>                 <h3>Original Rows</h3>                 <asp:DataGrid   id="OriginalRows" runat="server" Font-Names="Verdana, graphics/ccc.gif Tahoma,Arial" Width="500" BackColor="silver" BorderColor="#000000" graphics/ccc.gif EnableViewState="False">                      <HeaderStyle BackColor="black" ForeColor="white" Font-Bold="True" graphics/ccc.gif Font-Names="Verdana,Tahoma,Arial" Font-Size="10pt" />                 </asp:DataGrid>                 <br/>            </form>       </body>  </HTML> 
The DataAdapter Class

The different managed providers supply a Command object that supports the IDbCommand interface. The base IDbCommand interface by itself doesn't really provide you with a means of working with XML natively, so working with XML using an OLEDB Provider and the OleDbCommand object requires you to create your own XML. It is great for giving you access to stored procedures and issuing SQL UPDATE commands. But, the Command object suffers from limitations that are solved by using the DataAdapter class.

The DataAdapter class provides the ability to control what happens behind the scenes. It is also used as a bridge to pass data back and forth using a DataSet object and a database connection.

As previously stated, ADO supports the notion of disconnected recordsets and batch updates. When batch updates occur, the data updated in the database depends on from where the data was retrieved. For example, you select data from two different tables using a SQL JOIN . After you update several values in different rows, you issue an UpdateBatch statement. Which table gets updated, the table to the left of the JOIN , or both tables? More often than not, you receive an error similar to "Insufficient key column information," which can be a difficult bug to correct in your application. Suppose that you retrieved the data from a complex stored procedure or view. Properly updating the underlying data becomes fallible and risky.

The DataAdapter class solves this issue by allowing you to define your own operations to respond to individual rows being manipulated. Suppose that you select using inline text, but want to call your own stored procedures when the underlying data is changed. This is impossible in ADO, but easy in ADO.NET. In addition to the previous AddCustomer stored procedure from Listing 8.3, you create several new stored procedures for use with the Northwinds database in SQL Server. Open Query Analyzer, connect to the Northwinds database, and enter the following code into the code pane:

 create procedure UpdateCustomerCompanyName(@CustomerID char(5), @CompanyName varchar(40))  as  update Customers set CompanyName = @CompanyName where CustomerID = @CustomerID  if (@@ERROR <> 0) goto errorhandler  cleanup:       return(0)  errorhandler:       return(1)  GO  create procedure DeleteCustomer(@CustomerID char(5))  as  delete from  Customers where CustomerID = @CustomerID  if (@@ERROR <> 0) goto errorhandler  cleanup:       return(0)  errorhandler:       return(1)  GO 

Click the F5 button, and the stored procedures are created.

Next, create a new .aspx page called Listing8_13.aspx and enter the code from Listing 8.13 into the .aspx page.

Listing 8.13 By Using the SqlDataAdapter , You Can Control Update Operations for a DataSet
 <%@ Import Namespace="System.Data"%>  <%@ Import Namespace="System.Data.SqlClient"%>  <%@ Import Namespace="System.Configuration"%>  <script language="vb" runat="server">  Public Function GetDS(ByVal connectionString As String) As DataSet          Dim connection As SqlConnection = New SqlConnection(connectionString)          Dim command As SqlCommand          Dim customerAdapter As SqlDataAdapter          Dim orderAdapter As SqlDataAdapter          Dim ds As DataSet = New DataSet()          'Create the Command object to select from the Customers database          'table and append it to the adapter.          command = New SqlCommand()          With command              .CommandText = "SELECT CustomerID, CompanyName FROM Customers"              .CommandType = CommandType.Text              .Connection = connection          End With          customerAdapter = New SqlDataAdapter()          customerAdapter.SelectCommand = command          'Create the Command object to select from the Orders database          'table and append it to the adapter.          command = New SqlCommand()          With command              .CommandText = "SELECT * FROM Orders"              .CommandType = CommandType.Text              .Connection = connection          End With          orderAdapter = New SqlDataAdapter()          orderAdapter.SelectCommand = command          connection.Open()          customerAdapter.Fill(ds, "NorthwindCustomers")          orderAdapter.Fill(ds, "NorthwindOrders")          connection.Close()          connection.Dispose()          customerAdapter.Dispose()          orderAdapter.Dispose()          Return (ds)      End Function      Public Sub UpdateDS(ByVal connectionString As String, ByVal ds As DataSet)          Dim adapter As SqlDataAdapter = New SqlDataAdapter()          Dim connection As SqlConnection = New SqlConnection(connectionString)          Dim parm As SqlParameter          With adapter              .InsertCommand = New SqlCommand("AddCustomer", connection)              .InsertCommand.CommandType = CommandType.StoredProcedure              parm = .InsertCommand.Parameters.Add("@CustomerID", SqlDbType.Char, 5)              parm.SourceColumn = "CustomerID"              parm = .InsertCommand.Parameters.Add("@CompanyName", SqlDbType.VarChar, 40)              parm.SourceColumn = "CompanyName"              .UpdateCommand = New SqlCommand("UpdateCustomerCompanyName", connection)              .UpdateCommand.CommandType = CommandType.StoredProcedure              parm = .UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.Char, 5)              parm.SourceColumn = "CustomerID"              parm = .UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.VarChar, 40)              parm.SourceColumn = "CompanyName"              .DeleteCommand = New SqlCommand("DeleteCustomer", connection)              .DeleteCommand.CommandType = CommandType.StoredProcedure              parm = .DeleteCommand.Parameters.Add("@CustomerID", SqlDbType.Char, 5)              parm.SourceColumn = "CustomerID"              connection.Open()              .Update(ds, "NorthwindCustomers")          End With      End Sub      Public Sub DumpTable(ByVal ds As DataSet)          Dim table As DataTable          For Each table In ds.Tables              Response.Write("<h1>" + table.TableName + "</h1>")              Response.Write("<table border=""1"" cellpadding=""2"" cellspacing=""2"">")              Response.Write("<tr>")              Dim column As DataColumn              For Each column In table.Columns                  Response.Write("<th>" + column.ColumnName + "</th>")              Next column              Response.Write("</tr>")              Dim row As DataRow  An Introduction to ADO.NET 419  For Each row In table.Rows                  Response.Write("<tr>")                  For Each column In table.Columns                      Response.Write("<td>" + row(column).ToString() + "</td>")                  Next column                  Response.Write("</tr>")              Next row              Response.Write("</table>")          Next table      End Sub      Private Sub Page_Load(ByVal sender As System.Object, ByVal e As  System.EventArgs)          'Get the connection string from web.config          Dim connectionString As String =   ConfigurationSettings.AppSettings.Item("connectionString")          'Get a populated DataSet object          Dim ds As DataSet = GetDS(connectionString)          Dim table As DataTable = ds.Tables("NorthwindCustomers")          Dim row As DataRow          'Use an array to add a single row to the table.          Dim values() As String = Array.CreateInstance(GetType(String), 2)          values.SetValue("VBDNA", 0)          values.SetValue("Kirk Allen Evans", 1)          row = table.Rows.Add(values)          'Use the NewRow method to add a new row to the table          row = ds.Tables("NorthwindCustomers").NewRow()          row.Item("CustomerID") = "CARSN"          row.Item("CompanyName") = "Carson Allen Evans"          'The LoadDataRow method will update the row if it is found,          'otherwise it will create a row with the values          values.SetValue("DEANA", 0)          values.SetValue("Deanna Evans", 1)          row = table.LoadDataRow(values, True)          Dim foundRows() As DataRow = table.Select("CustomerID LIKE 'TEST%'")          For Each row In foundRows              row.Delete()          Next          UpdateDS(connectionString, ds)          'Get a new copy of the underlying data          ds = GetDS(connectionString)          DumpTable(ds)      End Sub  </script> 

The DataAdapter exposes properties for performing SQL SELECT , INSERT , UPDATE , and DELETE operations. These are shown in the UpdateDS method in Listing 8.13.

The Command object was used in previous examples throughout this chapter, so this object is nothing new. Separate Command objects perform the data manipulation and are specified by using the respective InsertCommand , UpdateCommand , and DeleteCommand properties. But here is what is really cool:You can use the SourceColumn property of the Parameter object to specify the column in the DataSet to read the value from. You can then call the Updat e method of the DataAdapter, specifying the DataSet and its DataTable that you want to modify, and call a single Update method. Any inserted rows will call the InsertCommand to perform the insertion, any deleted rows will call the DeleteCommand to perform the deletion, and updated rows will call the UpdateCommand to perform the update. It's all done in one shot. (Pretty cool, huh?)

To test this, insert some fake data into the Northwinds database. Open Query Analyzer again, connect to the Northwinds database, and enter the following code into the code pane:

 INSERT INTO Customers (CustomerID, CompanyName) VALUES ('TEST1','Testing 1')  INSERT INTO Customers (CustomerID, CompanyName) VALUES ('TEST2','Testing 2')  INSERT INTO Customers (CustomerID, CompanyName) VALUES ('TEST3','Testing 3')  INSERT INTO Customers (CustomerID, CompanyName) VALUES ('TEST4','Testing 4')  INSERT INTO Customers (CustomerID, CompanyName) VALUES ('TEST5','Testing 5')  INSERT INTO Customers (CustomerID, CompanyName) VALUES ('TEST6','Testing 6') 

Press the F5 button, and the data is inserted into the database. Finally, run your sample project by opening listing8_13.aspx in your browser.

The end result of this example is that all the data in all the tables for the DataSet is displayed as individual HTML tables, which shows you that three rows were successfully added to the database and the six test rows were deleted.

DataRelations

DataSets can represent data as a hierarchical set of relational views and an XML view. This is achieved through the DataRelation object, as follows:

 ds.Relations.Add("CustomerOrders",  ds.Tables("NorthwindCustomers").Columns("CustomerID"),  ds.Tables("NorthwindOrders").Columns("CustomerID")) 

Those familiar with the MSDataShape provider can see the vast improvement in this method. This allows you to relate the data in the parent table with the data in the child table. We can see this in use in the sections, "Binding to Controls," and " XmlDataDocument ."

The SqlDataCommand Class Revisited

The SqlDataCommand class provides several methods in addition to the DataSet class for working with XML. These methods are contained in the SqlDataCommand class and are not part of the base IDbDataCommand interface because returning XML from a database is a SQL Server-specific implementation not found in most data stores. Listing 8.14 uses the ExecuteXmlReader method of the SqlDataCommand object to return XML data from SQL Server.

Listing 8.14 Using the ExecuteXmlReader Method
 Imports System.Xml  Imports System.Data.SqlClient  Public Class WebForm1    Inherits System.Web.UI.Page  #Region " Web Form Designer Generated Code "    'This call is required by the Web Form Designer.    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()    End Sub    Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) graphics/ccc.gif Handles MyBase.Init      'CODEGEN: This method call is required by the Web Form Designer      'Do not modify it using the code editor.      InitializeComponent()    End Sub  #End Region    Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) graphics/ccc.gif Handles MyBase.Load      Call GetXMLFromDb("Server=p450;Database=Northwind;User ID=sa;Password=;")    End Sub    Public Sub GetXMLFromDb(ByVal connectionString)      Dim connection As SqlConnection = New SqlConnection(connectionString)      Dim command As SqlCommand = New SqlCommand("SELECT TOP 5 CustomerID, CompanyName FROM graphics/ccc.gif CUSTOMERS FOR XML AUTO")      command.CommandType = CommandType.Text      command.Connection = connection      connection.Open()      'Fill the XmlTextReader with the XML returned from the database      Dim reader As System.Xml.XmlTextReader = command.ExecuteXmlReader()      Response.ContentType = "text/xml"      Dim writer As System.Xml.XmlWriter = New graphics/ccc.gif System.Xml.XmlTextWriter(Response.OutputStream, System.Text.Encoding.UTF8)      writer.WriteStartDocument(True)      writer.WriteStartElement("DATA")      While reader.Read()        Select Case reader.NodeType          Case XmlNodeType.Element            writer.WriteStartElement(reader.LocalName)            If reader.HasAttributes() Then              writer.WriteAttributes(reader, True)            End If            If reader.IsEmptyElement Then              writer.WriteEndElement()            End If          Case XmlNodeType.EndElement            writer.WriteEndElement()        End Select      End While      writer.WriteEndElement()      writer.WriteEndDocument()      writer.Close()      reader.Close()      connection.Close()      connection.Dispose()      command.Dispose()    End Sub  End Class 

You look at how SQL Server generates XML data in Chapter 9. But you can see here that the SQL Server implementation of the IDbDataCommand interface extends support for returning XML data from the database.

Another function to note is the WriteAttributes method of the XmlWriter . This method writes all the attributes for the current node of an XmlReader . The output of this method is shown here:

 <?xml version="1.0" encoding="utf-8" ?>  <DATA>       <CUSTOMERS CustomerID="ALFKI" CompanyName="Alfreds Futterkiste" />       <CUSTOMERS CustomerID="ANATR" CompanyName="Ana Trujillo Emparedados yhelados" />       <CUSTOMERS CustomerID="ANTON" CompanyName="Antonio Moreno Taquera" />       <CUSTOMERS CustomerID="AROUT" CompanyName="Around the Horn" />       <CUSTOMERS CustomerID="BERGS" CompanyName="Berglunds snabbkp" />  </DATA> 
The XmlDataDocument Class

Throughout this book, you have seen many different methods for working with XML data. You have used stream-based readers and writers, in-memory DOM representations, IXPathNavigable documents, and now the ADO.NET DataSet and SqlCommand objects. As you are already aware, each of these approaches to working with XML can usually be mixed with another approach. The DataSet class is no exception.

A DataSet object can easily be represented either as XML or as a relational object view, as you saw earlier using relations.

 ds.Relations.Add("CustomerOrders",  ds.Tables("NorthwindCustomers").Columns("CustomerID"),  ds.Tables("NorthwindOrders").Columns("CustomerID")) 

The flexibility of the DataSet comes with several drawbacks. When loading the XML into a DataSet , only the data that is meaningful to the relational view is preserved. Element ordering can change, white space is not preserved, and content not mapped to the DataSet 's schema is discarded. These are rational limitations to working with XML in a relational view. If you must preserve document fidelity, consider using the XmlDataDocument class instead.

The XmlDataDocument class is flexible and is designed for working with the DataSet and its XML capabilities.

Besides preserving the source XML document's original structure, using the XmlDataDocument class provides several benefits. Because the DataSet provides hierarchy and supports XML, it is possible to use tools such as XPath and XSLT with the XML it provides.

Preserving Document Fidelity

If you want to preserve the original XML document when working with XML data using a DataSet , use the XmlDataDocument class.

Another key benefit of using the XmlDataDocument with the DataSet is that changes between each associated object can be automatically synchronized (see Figure 8.9). This means that changes in the DataSet are reflected in the XmlDataDocument instance associated with the DataSet . Because the DataSet is only concerned with data that fits within its relational view, however, nodes added to the XML document that do not fit within the DataSet 's schema are not synchronized.

Figure 8.9. The synchronization between XmlDataDocument and the DataSet .
graphics/08fig09.gif

The XmlDataDocument class is inherited from XmlDocument . Tables 7.6 and 7.7 in Chapter 7 show the properties and methods of the XmlDocument class. The XmlDataDocument class adds several properties and methods to the XmlDocument class, which are shown in Tables 8.10 and 8.11.

Table 8.10. Properties Added by XmlDataDocument

Member Name

Description

DataSet

Gets a DataSet that can access the data in the XmlDataDocument by using a relational model.

Table 8.11. Methods Added by XmlDataDocument

Member Name

Description

GetElementFromRow

Returns the XmlElement that contains an XML representation of the specified DataRow .

GetRowFromElement

The inverse of the GetElementFromRow method, returns a DataRow associated with the specified XmlElement .

Listing 8.7 introduced you to working with XML and the DataSet class and introduced a new version of the GetDS method that returns a DataSet that was populated from an XML file and an XSD schema. This example is again utilized here:

 public DataSet GetDS(String connectionString, String schemaFilePath, String xmlFilePath)  {      DataSet ds = new DataSet();       ds.ReadXmlSchema(schemaFilePath);       ds.ReadXml(xmlFilePath, XmlReadMode.IgnoreSchema);       ds.AcceptChanges();       try       {          ds.EnforceConstraints = true;       }       catch(Exception e)       {          Response.Write("<h1>The input file failed validation.</h1>");           foreach(DataTable table in ds.Tables)           {                DataRow [] errorRows = table.GetErrors();                 if (errorRows != null)                 {                     Response.ContentType = "text/html";                      Response.Clear();                      Response.Write("<h3>Error in " + table.TableName + "</h3>");                      foreach(DataRow row in errorRows)                      {                          Response.Write ("<i>" + row.RowError + "</i><br/>");                           DataColumn [] errorColumns = row.GetColumnsInError();                           if (errorColumns.Length > 0)                           {                               Response.Write ("The following columns are in error:");                                Response.Write("<ul>");                                foreach(DataColumn column in errorColumns)                                {                                    Response.Write("<li>" + column.ColumnName + "</li>");                                }                                Response.Write("</ul>");                               }                          }                    }               }               ds.Dispose();               throw (new System.Xml.Schema.XmlSchemaException("One or more tables in the graphics/ccc.gif DataSet failed validation.",e));         }         return(ds);  } 

The next step is to set up more versions of the DumpTable to output the results as an HTML table:

 public void DumpTable(XPathNodeIterator iterator)  {      Response.Write("<table border=\"1\"");       Response.Write("<tr>");       Response.Write("<td>Name</td>");       Response.Write("<td>BaseURI</td>");       Response.Write("<td>LocalName</td>");       Response.Write("<td>NamespaceURI</td>");       Response.Write("<td>Prefix</td>");       Response.Write("</tr>");       while(iterator.MoveNext())       {           Response.Write("<tr>");            Response.Write("<td>" + iterator.Current.Name + "</td>");            Response.Write("<td>" + iterator.Current.BaseURI  + "</td>");            Response.Write("<td>" + iterator.Current.LocalName   + "</td>");            Response.Write("<td>" + iterator.Current.NamespaceURI + "</td>");            Response.Write("<td>" + iterator.Current.Prefix     + "</td>");            Response.Write("</tr>");       }       Response.Write("</table>");  }  public void DumpTable(XmlElement element)  {      Response.Write("<table border=\"1\"");       Response.Write("<tr>");       Response.Write("<td>Name</td>");       Response.Write("<td>BaseURI</td>");       Response.Write("<td>LocalName</td>");       Response.Write("<td>NamespaceURI</td>");       Response.Write("<td>Prefix</td>");       Response.Write("</tr>");       Response.Write("<tr>");       Response.Write("<td>" + element.Name + "</td>");       Response.Write("<td>" + element.BaseURI  + "</td>");       Response.Write("<td>" + element.LocalName   + "</td>");       Response.Write("<td>" + element.NamespaceURI    + "</td>");       Response.Write("<td>" + element.Prefix     + "</td>");       Response.Write("</tr>");       Response.Write("</table>");  }  public void DumpTable(DataRow row)  {      //Outputs a single DataRow as an HTML table  Response.Write("<h4>Table:" + row.Table.TableName + "</h4>");  Response.Write("<table border=\"1\" cellpadding=\"2\" cellspacing=\"2\">");       Response.Write("<tr>");  foreach(DataColumn column in row.Table.Columns)   {   Response.Write("<th>" + column.ColumnName + "</th>");  }       Response.Write("</tr>");       Response.Write("<tr>");  foreach(DataColumn column in row.Table.Columns)   {   Response.Write("<td>" + row[column].ToString() + "</td>");  }       Response.Write("</tr>");       Response.Write("</table>");  } 

The overloaded version of the DumpTable method that accepts a DataRow uses the Table method to retrieve the DataTable that the DataRow belongs to. This enables you to retrieve the TableName of the table and output it. Each DataRow object is indexed on its columns. To retrieve the list of columns, you must resort back to the Columns collection of the Table object the current DataRow belongs to.

The following sample.xml file from Listing 8.9 and the sample1.xsd file from Listing 8.8 are used here.

Create a new web form called Listing8_15.aspx and paste the code from Listing 8.15 into the code-behind view.

Listing 8.15 Navigating a DataSet Using XmlDataDocument and XPath
 Imports System.Xml  Imports System.Xml.XPath  Imports System.Data  Imports System.Data.SqlClient  Public Class Listing8_15      Inherits System.Web.UI.Page  #Region " Web Form Designer Generated Code "      'This call is required by the Web Form Designer.      <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()      End Sub      Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) graphics/ccc.gif Handles MyBase.Init          'CODEGEN: This method call is required by the Web Form Designer          'Do not modify it using the code editor.          InitializeComponent()      End Sub  #End Region      Public Function GetDS(ByVal schemaFilePath As String, ByVal xmlFilePath As String) As graphics/ccc.gif DataSet          Dim ds As DataSet = New DataSet()          ds.ReadXmlSchema(schemaFilePath)          ds.ReadXml(xmlFilePath, XmlReadMode.IgnoreSchema)          ds.AcceptChanges()          Try              ds.EnforceConstraints = True          Catch e As System.Exception              Response.Write("<h1>The input file failed validation.</h1>")              Dim table As DataTable              For Each table In ds.Tables                  Dim errorRows() As DataRow = table.GetErrors()                  If errorRows Is Nothing Then                      Response.ContentType = "text/html"                      Response.Clear()                      Response.Write("<h3>Error in " + table.TableName + "</h3>")                      Dim row As DataRow                      For Each row In errorRows                          Response.Write("<i>" + row.RowError + "</i><br/>")                          Dim errorColumns() As DataColumn = row.GetColumnsInError()                          If errorColumns.Length > 0 Then                              Response.Write("The following columns are in error:")                              Response.Write("<ul>")                              Dim column As DataColumn                              For Each column In errorColumns                                  Response.Write("<li>" + column.ColumnName + "</li>")                              Next column                              Response.Write("</ul>")                          End If                      Next row                  End If              Next table              ds.Dispose()              Throw (New System.Xml.Schema.XmlSchemaException("One or more tables in the graphics/ccc.gif DataSet failed validation.", e))          End Try          ds.Dispose()          Return (ds)      End Function      Private Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles graphics/ccc.gif MyBase.Load          Dim connectionString As String = graphics/ccc.gif ConfigurationSettings.AppSettings.Get("connectionString")          Dim ds As DataSet          Dim writer As XmlWriter = New XmlTextWriter(Response.OutputStream, graphics/ccc.gif System.Text.Encoding.UTF8)          Try              ds = GetDS(Server.MapPath("sample1.xsd"), Server.MapPath("sample.xml"))              Response.ContentType = "text/html"              Dim doc As XmlDataDocument = New XmlDataDocument(ds)              Dim ns As XmlNamespaceManager = New XmlNamespaceManager(doc.NameTable)              ns.AddNamespace("bar", "http://tempuri.org/sample1.xsd")              'Select a Customer element              Dim query As String = "bar:Customers/bar:Customer[@id='ALFKI']"              Dim element As XmlElement = CType(doc.SelectSingleNode(query, ns), graphics/ccc.gif XmlElement)              Dim row As DataRow = doc.GetRowFromElement(element)              Response.Write("<h3>Results Using SelectSingleNode</h3>")              DumpTable(row)              Dim foundRows() As DataRow = ds.Tables("Customer").Select("id='VBDNA'")              Response.Write("<h3>Results Using GetElementFromRow</h3>")              Dim foundRow As DataRow              For Each foundRow In foundRows                  element = doc.GetElementFromRow(foundRow)                  DumpTable(element)              Next foundRow              'Select a Contact element              query = "bar:Customers/bar:Customer[@id='ALFKI']/bar:Contact"              'Use the XPathNavigator class for XPath queries from DataSet              Dim nav As XPathNavigator = doc.CreateNavigator()              Dim expression As XPathExpression = nav.Compile(query)              expression.SetContext(ns)              Dim iterator As XPathNodeIterator = CType(nav.Evaluate(expression), graphics/ccc.gif XPathNodeIterator)              Response.Write("<h3>Results Using XPathNavigator</h3>")              DumpTable(iterator)          Catch oops As Exception              Response.Write(oops.ToString())          Finally              writer.Close()              If Not ds Is Nothing Then ds.Dispose()          End Try      End Sub      Public Sub DumpTable(ByVal iterator As XPathNodeIterator)          Response.Write("<table border=""1""")          Response.Write("<tr>")          Response.Write("<td>Name</td>")          Response.Write("<td>BaseURI</td>")          Response.Write("<td>LocalName</td>")          Response.Write("<td>NamespaceURI</td>")          Response.Write("<td>Prefix</td>")          Response.Write("</tr>")          While (iterator.MoveNext())              Response.Write("<tr>")              Response.Write("<td>" + iterator.Current.Name + "</td>")              Response.Write("<td>" + iterator.Current.BaseURI + "</td>")              Response.Write("<td>" + iterator.Current.LocalName + "</td>")              Response.Write("<td>" + iterator.Current.NamespaceURI + "</td>")              Response.Write("<td>" + iterator.Current.Prefix + "</td>")              Response.Write("</tr>")          End While          Response.Write("</table>")      End Sub      Public Sub DumpTable(ByVal element As XmlElement)          Response.Write("<table border=""1""")          Response.Write("<tr>")          Response.Write("<td>Name</td>")          Response.Write("<td>BaseURI</td>")          Response.Write("<td>LocalName</td>")          Response.Write("<td>NamespaceURI</td>")          Response.Write("<td>Prefix</td>")          Response.Write("</tr>")          Response.Write("<tr>")          Response.Write("<td>" + element.Name + "</td>")          Response.Write("<td>" + element.BaseURI + "</td>")          Response.Write("<td>" + element.LocalName + "</td>")          Response.Write("<td>" + element.NamespaceURI + "</td>")          Response.Write("<td>" + element.Prefix + "</td>")          Response.Write("</tr>")          Response.Write("</table>")      End Sub      Public Sub DumpTable(ByVal row As DataRow)          'Outputs a single DataRow as an HTML table          Response.Write("<h4>Table:" + row.Table.TableName + "</h4>")          Response.Write("<table border=""1"" cellpadding=""2"" cellspacing=""2"">")          Response.Write("<tr>")          Dim column As DataColumn          For Each column In row.Table.Columns              Response.Write("<th>" + column.ColumnName + "</th>")          Next column          Response.Write("</tr>")          Response.Write("<tr>")          For Each column In row.Table.Columns              Response.Write("<td>" + row(column).ToString() + "</td>")          Next column          Response.Write("</tr>")          Response.Write("</table>")      End Sub  End Class 

The cool part of this example is in the Page_Load event handler. You are able to create an XmlDataDocument based on the DataSet and then run XPath queries against the XmlDataDocument class itself. This enables you to create robust solutions using XPath to query relational data such as tables in a mainframe where XPath is not supported.

The output from this example is shown in Figure 8.10.

Figure 8.10. The output from Listing 8.15 shows the results of querying the XmlDataDocument using XPath.
graphics/08fig10.gif

Recall from Chapter 7 that the XmlDocument class supports the IXPathNavigable interface, which exposes a CreateNavigator method that can be used with the XPathNavigator and XsltTransform classes. Because the XmlDataDocument class inherits from XmlDocument , you can (by proxy) use these classes with the XmlDataDocument class. Use the CreateNavigator method to retrieve an XPathNavigator .

The XML sample that you are using contains namespaces. Recall from Chapter 7 that you use the XmlNamespaceManager object to express the namespaces for the XML document so that you can prefix them for XPath queries. You associated the namespace URI urn:foo:bar with the prefix bar . You can now use the prefix in your XPath queries, as shown here:

  string expression = "bar:Customers/bar:Customer[@id='ALFKI']/bar:Contact";  

Use the XmlNamespaceManager to query the XmlDataDocument , retrieving an XmlNode object that you cast to XmlElement . You can now use the element to retrieve a DataRow from the DataSet , as shown here:

 XmlElement element = (XmlElement)doc.SelectSingleNode(query,ns);  DataRow row = doc.GetRowFromElement(element); 

Earlier in this chapter, you used the Select method of the DataTable object to select rows matching the selection criteria. Use this method again in the following code to retrieve rows, iterate through each DataRow , and retrieve an XmlElement that's output as HTML:

 DataRow [] foundRows = ds.Tables["Customer"].Select("id='VBDNA'");  Response.Write("<h3>Results Using GetElementFromRow</h3>");  foreach(DataRow foundRow in foundRows)  {      element = doc.GetElementFromRow(foundRow);       DumpTable(element);  } 

Just as with the SelectSingleNode example, you must associate a namespace with a prefix by using the XmlNamespaceManager class to run XPath queries against XML that uses namespaces. Do this by using the SetContext method of the XPathExpression object, as follows:

 //Select a Contact element  query = "bar:Customers/bar:Customer[@id='ALFKI']/bar:Contact";  //Use the XPathNavigator class for XPath queries from DataSet  XPathNavigator nav = doc.CreateNavigator();  XPathExpression expression = nav.Compile(query);  expression.SetContext(ns);  XPathNodeIterator iterator = (XPathNodeIterator)nav.Evaluate(expression);  Response.Write("<h3>Results Using XPathNavigator</h3>");  DumpTable(iterator); 

The return from the Evaluate method returns an Object , so you have to explicitly cast the Object as an XPathNodeIterator . After you have the iterator, you can output its contents as HTML.

Another option that the XmlDataDocument gives you is the ability to apply an XSLT transformation directly to a DataSet . Although it is possible to use this ability to output HTML from a DataSet , it would probably be more practical to bind the DataSet directly to a DataGrid control or other web control. Instead, the ability to apply an XSLT transformation to a DataSet can be incredibly useful any time you need to convert relational data (probably coming from a database) into XML. For example, you might have a business partner that needs periodic dumps of part of your relational database, but he needs the data to conform to a particular XML Schema, or he just finds XML to be the easiest format to import. Microsoft SQL Server 2000 has the ability to output relational data as XML directly, but the DataSet method has the advantage that it can work with any database at all (or any data source that can be loaded into a DataSet , database or not).

As an example, complete the following steps:

  1. Load some data from the Northwind sample database that comes with SQL Server into a DataSet , and produce a hierarchical XML file from that data.

  2. To produce a single XML file with one entry for each customer, containing all the orders for that customer, load both the Customers and the Orders tables from the Northwind database into a single DataSet , and set up a relation within the DataSet (each order is placed by a customer).

  3. Wrap the DataSet in an XmlDataDocument object, and transform that object with an XSLT stylesheet and the XML web control, streaming the resulting XML to the browser.

  4. Create a new Web Application Project in Visual Studio .NET and call the project "XSLT".

  5. Add a new web form to the project; call it DSTransform.aspx .

  6. Add a new XML web control from the toolbox to the page, and make its ID OrderOutput . The HTML view should look similar to this:

     <%@ Page language="c#" Codebehind="DSTransform.aspx.cs" AutoEventWireup="false" graphics/ccc.gif Inherits="XSLT.DSTransform" %>  <asp:Xml id="OrderOutput" runat="server"/> 

    There's not much to it, really. The remainder of the code goes into the code-behind file for this page, in the Page_Load event handler.

  7. Add a using or Imports statement for the System.Xml namespace. The Page_Load method looks like this:

     protected System.Web.UI.WebControls.Xml OrderOutput;  private void Page_Load(object sender, System.EventArgs e)  {      string connectionString = "Data Source=localhost;Initial graphics/ccc.gif Catalog=Northwind;Integrated Security=SSPI;";       SqlConnection myConnection = new SqlConnection(connectionString);       SqlDataAdapter customerAdapter = new SqlDataAdapter("SELECT * FROM Customers", graphics/ccc.gif myConnection);       SqlDataAdapter orderAdapter = new SqlDataAdapter("SELECT * FROM Orders", graphics/ccc.gif myConnection);       DataSet myDataSet = new DataSet("CustomerOrders");       try       {           myConnection.Open();            customerAdapter.Fill(myDataSet, "Customers");            orderAdapter.Fill(myDataSet, "Orders");       }       finally       {           if (myConnection.State == ConnectionState.Open)                 myConnection.Close();       }       DataRelation custOrderRelation = new DataRelation("CustomerOrderXREF",                  myDataSet.Tables["Customers"].Columns["CustomerID"],                  myDataSet.Tables["Orders"].Columns["CustomerID"]);       custOrderRelation.Nested = true;       myDataSet.Relations.Add(custOrderRelation);       XmlDataDocument xmlDoc = new XmlDataDocument(myDataSet);       OrderOutput.Document = xmlDoc;       OrderOutput.TransformSource = "CustomerOrders.xslt";       Response.Clear();       Response.ContentType = "text/xml";  } 

As you can see in this code, you begin by creating one SqlDataAdapter for each table from which you're selecting. Select all records from each table into separate tables with the same names in the DataSet that you create. Then set up a DataRelation that joins the CustomerID in the Orders table to the CustomerID in the Customers table. Then add the DataRelation to the DataSet , and create a new XmlDataDocument from the DataSet , and pass the XmlDataDocument to the Document property of the OrderOutput object (remember that OrderOutput is the XML web control that you added to the page previously). Then set the TransformSource of the OrderOutput object, and set the ContentType of the page to text/xml .

At this point, it is necessary to examine the XSLT stylesheet in Listing 8.16, which you use to do the transformation. You did not start with the stylesheet because much of it depends on the names used in creating the DataSet in the code that was just examined.

Listing 8.16 CustomerOrders.xslt
 <?xml version="1.0" encoding="UTF-8" ?>  <xsl:stylesheet version="1.0"  xmlns:xsl="http://www.w3.org/1999/XSL/Transform">       <xsl:template match="CustomerOrders">            <Customers>                 <xsl:apply-templates select="Customers"/>            </Customers>       </xsl:template>       <xsl:template match="Customers">            <Customer>                 <CustomerID><xsl:value-of select="CustomerID"/></CustomerID>                 <CompanyName><xsl:value-of select="CompanyName"/></CompanyName>                 <Contact>                      <Name><xsl:value-of select="ContactName"/></Name>                      <Title><xsl:value-of select="ContactTitle"/></Title>                      <Phone><xsl:value-of select="Phone"/></Phone>                      <Fax><xsl:value-of select="Fax"/></Fax>                      <Address>                           <Street><xsl:value-of select="Address"/></Street>                           <City><xsl:value-of select="City"/></City>                           <Region><xsl:value-of select="Region"/></Region>                           <PostalCode><xsl:value-of select="PostalCode"/></PostalCode>                           <Country><xsl:value-of select="Country"/></Country>                      </Address>                 </Contact>                 <Orders>                      <xsl:apply-templates select="Orders"/>                 </Orders>            </Customer>       </xsl:template>       <xsl:template match="Orders">            <Order>                 <OrderID><xsl:value-of select="OrderID"/></OrderID>                 <OrderDate><xsl:value-of select="OrderDate"/></OrderDate>                 <Shipping>                      <Name><xsl:value-of select="ShipName"/></Name>                      <Address><xsl:value-of select="ShipAddress"/></Address>                      <City><xsl:value-of select="ShipCity"/></City>                      <Region><xsl:value-of select="ShipRegion"/></Region>                      <PostalCode><xsl:value-of select="ShipPostalCode"/></PostalCode>                      <Country><xsl:value-of select="ShipCountry"/></Country>                 </Shipping>            </Order>       </xsl:template>  </xsl:stylesheet> 

This stylesheet consists basically of three templates. It is important to note which XML tags are being matched on for these templates, and compare those names to the code you just wrote for the code-behind of DSTransform.aspx . Because the data you intend to feed to this stylesheet does not come from an XML file, the tag names you must use are derived from the the column names in the source database and from the names that you give to the DataSet, and each of the tables it contains.

The first template matches on a tag called CustomerOrders . If you look for that name in the code-behind file, you'll find it on this line:

 DataSet myDataSet = new DataSet("CustomerOrders"); 

That tells you that the root element of the virtual XML document created from the DataSet is the name that you give to the DataSet when you create it. If you do not assign a name to the DataSet , a default name, such as NewDataSet , is assigned.

The next template matches on a tag called Customers . This is taken from the name of the Customers table in the DataSet , created on the following line:

 customerAdapter.Fill(myDataSet, "Customers"); 

Likewise, the template that matches on the Orders tag uses the name of the Orders table in the same DataSet . This stylesheet then produces an XML document with a root element called Customers . This element contains a series of Customer elements, which, in turn , contain customer details and a series of Order elements that contain details of each order that a particular customer has placed. After you compile the project and open the page in a browser, the resulting XML file looks something like Figure 8.11. The result is a database-independent and simple way of turning relational data into hierarchical XML.

Figure 8.11. Hierarchical XML can be easily produced from any relational database.
graphics/08fig11.gif
only for RuBoard


XML and ASP. NET
XML and ASP.NET
ISBN: B000H2MXOM
EAN: N/A
Year: 2005
Pages: 184

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net