In this "Data Access" column, I will discuss topics relating to the Microsoft .NET data access stack, known as ADO.NET. This installment starts with an overview of the different pieces of the stack and finishes with an interesting development in the world of SQL Server, .NET, and XML Web services.

The ADO.NET data access APIs consist of three types of classes:
1.   Classes for executing database commands in connected mode: This is accomplished by a series of data providers that run in managed code as much as possible. Each of these data providers implements approximately the same set of interfaces and exposes an analogous class hierarchy.
2.   Disconnected data access classes: A series of classes in the System.Data namespace that consists of a complex in-memory collection class (called the DataSet), which approximates relational database semantics, and supporting classes such as the DataTable, DataRow, and DataRelation.
3.   Classes for XML integration: DataSets, with help from a few additional classes in the Sys-tem.Xml namespace (although they "live" in the System.Data.dll), integrate the XML and relational data access worlds. It's also possible to allow for data interchange with heterogeneous platforms, such as Unix systems and IBM mainframes, using XML Web services.

An ADO.NET Crash Course
We'll start with a crash course in ADO.NET consisting of some simple programs.

Executing Database Commands in Connected Mode
Data providers include a SQL Server­specific provider (the SqlClient data provider) and a data provider, known as the OleDb data provider, which is a bridge for OLE DB data sources. Just before .NET shipped in February, Microsoft released a bridge provider for ODBC data sources for download from the Web. So, if you use SQL Server or have an OLE DB provider or ODBC driver for your database, you can use the .NET data access classes directly with your data.

The simplest starter program for reading a resultset (the results of a SQL SELECT statement) containing two columns from a SQL Server table (in C#) would look like Listing 1.

The program uses the SqlClient data provider's Connection class (which includes a constructor that takes a database connection string) to open a connection to SQL Server. The Command class encapsulates SQL statement functionality and includes a constructor that takes a SQL command string. We use this to execute the SQL; the results are returned in a DataReader class. This class has methods that allow us to move forward through the rows in the resultset, along with methods that allow us to access the data in each row by using zero-based column ordinals.

We'll discuss using data providers with different data sources in more detail in future columns. In addition to Microsoft's data providers, third parties can build data providers. For example, DataDirect Technologies has shipped native providers for Oracle and Sybase. You can also build your own. I built a simple data provider that's available for download at http://staff.develop.com/bobb.

Disconnected Data and the DataSet Class
The DataSet class is a specialized collection class that includes a collection that follows the relational data model. If you're used to dealing with tables, columns, rows, and relationships, you'll be right at home with DataSet. DataSets can be used as an in-memory cache or a data source for ASP.NET Web Forms controls, or they can be directly bound and manipulated by Windows Forms controls. DataSets interact with data providers through a data provider's DataAdapter class.

Listing 2 shows a simple program that reads data into a DataSet, updates the DataSet, and flushes the updates back to the database. Note that you don't have to open the connection directly when you're using a DataAdapter because the DataAdapter.Fill method opens the connection, reads the rows, and then closes the connection automatically.

Although using a DataSet is similar in concept to using the disconnected ADO Recordset class, a DataSet can contain multiple tables and the XML integration is infinitely more flexible. It's instructive to look at the DataSet as an ADO Recordset on steroids.

You can use DataSets with data from any data provider, synthesize tables and data using the APIs directly, or fill the DataSet using XML. Using DataSets alone or in conjunction with the XmlDataDocument class (a hybrid of DataSet and XmlDocument) allows you to transparently mix and switch between XML and relational data. Here's an example that writes a DataSet as XML:

DataSet ds = new DataSet();
SqlDataAdapter da = new
SqlDataAdapter(
"select au_id, au_lname from
authors",
"server=myserver;uid=myuser;
pwd=mypwd;database=pubs");

da.Fill(ds, "authors");
ds.WriteXml("c:\\authors.xml");
ds.WriteXmlSchema("c:\\authors.xsd"
);

I'll look at some of the intricacies of the DataSet and related classes in future columns.

SQL Server, XML, and Web Services
Now that we've covered the basics of the ADO.NET data access stack, let's turn to another feature that helps integrate Microsoft's SQL Server database, XML documents, .NET programming, and XML Web services. This extension to SQL Server, known as SQLXML, is available on the Web for download.

The SQLXML Configuration Tool
The SQLXML 3.0 version added the capability to expose data from SQL Server and perform database maintenance through Web services. This means that database operations can be accomplished from any platform that supports Web services, without needing SQL Server client libraries, OLE DB providers, or ODBC drivers to be installed on each user's workstation. This is implemented through an IIS ISAPI (Internet Services API) application that supports sending and receiving messages using SOAP (Simple Object Access Protocol). The SOAP protocol (and Web services in general) is designed to provide interoperability between heterogeneous platforms. You don't need to use them when you have a direct connection to SQL Server and can use SQL Server's native TDS (Tabular Data Stream) protocol to communicate. However, if you have a Web service that passes DataSets around as XML, there's no automatic way to provide .NET DataSets to VB6 clients. VB6 could in this case be considered a heterogeneous platform as well.

SQLXML 3.0 makes exposing results from a stored procedure, user-defined function, or SQLXML template query (a SQL or XPath query bracketed by XML) as easy as configuring it in the SQLXML IIS configuration utility. No server-side code need be written. You even have a few choices of how to return your SQL results: as a .NET DataSet, an array of .NET DataSets, or an array of XmlElement. The XmlElement is a .NET class in the managed XML stack that can represent an XML document or a document fragment. When you're returning arrays, you represent this in your .NET client program as an array of type Object, because your stored procedure may return errors of type SqlMessage if errors occur in execution. I'll explain more about the array of objects when we write the client.

You also have a choice of whether your SQL resultsets are generated in "XML RAW" format or "XML NESTED" format. XML RAW format returns each row in the resultset as an XML element named row; columns are returned in attribute normal form (i.e., each column is an attribute of the row element). XML NESTED format returns hierarchically nested XML elements named after the tables used in the SQL SELECT statement. There may be multiple levels of hierarchy, depending on the SQL statement. You also have the option of configuring your Web service methods to return a SOAP Fault element instead of a SqlMessage element if errors occur.

To configure the SQLXML IIS utility to return SOAP, you must configure a virtual name for the SOAP Web service after mapping a SQLXML virtual directory to a SQL Server and a specific database. You use the Virtual Names tab to accomplish this. You can add one or more SOAP virtual names per virtual directory if you want. Although the SOAP virtual name can be anything, a common convention is to name it "soap" (see Figure 1).

Once you've configured the SOAP virtual name, you add access to your stored procedures, user-defined functions, or templates using the configuration button. The XML output format options mentioned in the previous paragraph can be specified on a per-method basis. The SQLXML configuration tool even generates the WSDL (Web Service Description Language) that's used to describe your Web service parameters to the outside world automatically when you save the configuration. The endpoint that exposes this WSDL document is a combination of the virtual directory and the SOAP virtual name with "?WSDL" appended ­ so for example, if the name of the virtual directory is "pubs" and the SOAP virtual name is "soap," the WSDL may be obtained at http://webserver/pubs/soap?WSDL.

Figure 2 shows how to configure a template. In this case, the stored procedure byroyalty is mapped to a Web service endpoint, named AuthorsByRoyaltyAsDataSets, using the raw row formatting style. This Web service returns errors as SqlMessage types rather than as native SOAP errors.

Writing the Client
Now that we've "written" the Web service ­ by virtue of having a stored procedure, UDF (user-defined function), or SQLXML template already in place (wasn't that easy?) ­ let's write the client. Writing a .NET client is fairly automatic (as it should be), no matter how we've chosen to return the results. In any VS.NET client project, click on references in the Project Explorer pane and choose "Add Web Reference." Point the Add Web Reference dialog at the WSDL file, and click "Add Reference." A Web service proxy class is automatically generated. This proxy class will contain methods that can be used to invoke your newly created Web service. As with all VS.NET-generated proxies, both synchronous and asynchronous execution are supported. If you don't have VS.NET, the same results can be obtained by using the wsdl.exe command-line utility. Listing 3, which can be found at www.sys-con.com/dotnet/sourcec.cfm is an example of using a .NET proxy class to fill an array of DataSets.

I mentioned earlier that I would explain what "array of objects" means as a Web services return value. This is best illustrated by inspecting the WSDL generated by the configuration tool. The types section of the WSDL file contains schemas for four generic types:
1.   SqlRowSet: Produced when you choose to expose the results as a DataSet, which consists of an XML schema describing the resultset, followed by a resultset in the XML DiffGram format, represented as an xsd:any type. This production corresponds to a generic result (the xsd:any type) preceded by an inline schema describing the result.
2.   SqlXml: Produced when you choose array of objects in the configuration tool. It consists of an xsd:sequence element followed by an xsd:any element. Since the xsd:any element is completely generic, the output is loosely typed and depends entirely on the stored procedure's outputs.
3.   SqlResultCode: Consists of a single integer representing the Result parameter from a SQL stored procedure or UDF.
4.   SqlMessage: Contains multiple, well-defined elements consisting of the information returned when SQL Server returns an error.

These types are defined in three different XSD schemas exposed inside the WSDL document. A list of all the schemas for all of the types is beyond the scope of this article, but as an example, here's the schema for the SqlXml and SqlRowSet complex types. Note that the actual DataSet or XmlElement result is represented as XSD type xsd:any:

<!-- returns a DataSet -->
<xsd:complexType name="SqlRowSet">
<xsd:sequence>
<xsd:element ref="xsd:schema"/>
<xsd:any/>
</xsd:sequence>
<xsd:attribute
ref="sqltypes:IsNested"/>
</xsd:complexType>

<!-- returns an XmlElement -->
<xsd:complexType name="SqlXml"
mixed="true">
<xsd:sequence>
<xsd:any />
</xsd:sequence>
</xsd:complexType>

If we return an array of DataSet or XmlElement it may also contain the SqlMessage complex type if errors occur, so the entire series of results, known as a SqlResultStream type, appears to the client as an array of .NET type Object. In that case, the correct type of any part of the SqlResultStream can be determined by using Object.GetType as shown in the sample program or by prior knowledge of the result by the programmer.

Web services aficionados may be concerned with the loose typing implied by the xsd:any types in the SqlRowSet and SqlXml complex types. It's necessary for specific reasons. First, the DataSet itself is a generic container class; you can deduce the schema of a specific DataSet instance only from the inline schema in the SqlRowSet. Second, the WSDL generator in the SQLXML configuration utility can't obtain metadata from the database that describes stored procedures' resultsets. Stored procedures may obtain different results and even different numbers of results on a case-by-case basis. But how will these types be accessed by non-.NET clients that don't have definitions of the XmlElement or DataSet classes? Doesn't this amount to returning every result as generic, untyped XML and forcing the client to figure out what's in there?

Although the SQLXML configuration utility doesn't know what can be obtained from the stored procedure, UDF, or template returns, the Transact SQL programmer of these XML Web services does know. For non-.NET clients that require strong typing, you can hand-code alternative WSDL and make it available at an alternate endpoint. If your client can handle inline schema, this may be as easy as exposing a custom schema that specifies a complex type your stored procedure produces. In the case of SqlXml, you can code an alternate (strongly typed) complex type. For example, the result returned in the example above could be coded as:

<!‹- this schema contains the
specific DataSet instance
definition -->
<import namespace="http://www.develop.com/authors.xsd"
location="http://localhost/pubs/
soap/authors.xsd" />

<!-- this strongly typed complex
type references the schema -->
<!-- instead of the any tag -->
<xsd:complexType
name="AuthorsSqlRowSet">
<xsd:sequence>
<xsd:element ref="xsd:schema"/>
<au:AuthorsDataSet
xmlns:au="http://www.develop.com/authors.xsd" />
</xsd:sequence>
<xsd:attribute ref="sql
types:IsNested" />
</xsd:complexType>

This complex type should be usable by any client, not only those written in ADO.NET.

Direct Programmatic Access
We've seen how SQLXML lets us produce Web services consumable by any client with little or no coding on the server side. We wouldn't want to use these services gratuitously if consuming TDS directly is possible; using TDS instead of XML is always going to be more efficient. But for situations in which we want to make SQL Server available to non-Microsoft clients, combining SQL, XML, and SOAP may be an easy way to achieve interoperability. When using .NET clients, this gives us the ability to permit controlled access to our SQL Server without requiring either direct access or writing an ASP.NET Web service application. A Web service allows direct programmatic access.

Next Time
Since .NET has been released, a plethora of .NET data providers have also hit the market. Next time we'll discuss strategies for migrating existing code, looking at how the ADO.NET model makes it easier for provider writers to expose data source­specific features, and how this affects interoperability.

Author Bio
Bob Beauchemin has more than 22 years' experience in the computer industry as an instructor, course author, software developer, and systems administrator. He's currently a senior staff instructor at DevelopMentor, curriculum liaison for the data access curriculum, and author of courses on ADO.NET, OLE DB, ADO, SQL Server application development, and Java data access. bobb@develop.com

	


Listing 1

SqlConnection conn = new SqlConnection(
  "server=myserver;uid=myuser;pwd=mypwd;database=pubs");
SqlCommand cmd = new SqlCommand(
  "select au_id, au_lname from authors", conn);
SqlDataReader rdr;


try {
  conn.Open();
  rdr = cmd.ExecuteReader();
  while (rdr.Read())
     Console.WriteLine("author {0} has name {1}", rdr[0],
        rdr[1]);
}
catch (Exception e) {
     Console.WriteLine(e.Message);
}
finally {
     if (conn.State == ConnectionState.Open)
         conn.Close();
}



Listing 2

DataSet ds = new DataSet();
SqlDataAdapter da = new SqlDataAdapter(
   "select au_id, au_lname from authors",
   "server=myserver;uid=myuser;pwd=mypwd;database=pubs");


// this classes builds commands to update the database
SqlCommandBuilder bld = new SqlCommandBuilder(da);


// connection is closed here...
da.Fill(ds, "authors");
// connection closed here too


ds.Tables[0].Rows[0][1] = "newlastname";
da.Update(ds, "authors");

Listing 3:

static void Main(string[] args)
{
  // these are the types that can be returned
  SqlMessage sqlm;
  XmlElement elem;
  DataSet ds;

  // instantiate the proxy class
  myprog.localhost.soap s = new myprog.localhost.soap();

  object[] oa;	
  try {
    oa = s.AuthorsByRoyaltyAsDataSets(50);

    for (int i=0;i<oa.Length;i++) {
      switch (oa[i].ToString()) {
        case "System.Data.DataSet":
          ds = (DataSet)oa[i];
          Console.WriteLine(ds.GetXml());
          break;
        case "myprog.localhost.SqlMessage":
          sqlm = (SqlMessage)oa[i];
          Console.WriteLine("Error: {0} in proc {1}",
            sqlm.Message, sqlm.Procedure);
          break;
        default:
          Console.WriteLine("Unexpected type {0}:{1}",      
            oa[i].GetType().ToString(), 
            oa[i].ToString());
            break;
      }
    }
  }
  catch (Exception e) {
    Console.WriteLine("{0} {1}", 
      e.GetType().ToString(), e.Message);
    if (oa)
      Console.WriteLine("error, {0} results", oa.Length);
  }
}