Alternative Data Access Techniques

for RuBoard

The final (and much shorter) section of today's lesson focuses on some alternative methods you can use to access data from SQL Server. These technique supplement accessing data using the cached and streamed models available in ADO.NET.

Note

Although the information presented in this section isn't about ADO.NET per se, I think it's important to at least be aware of alternatives when you're designing and implementing your applications. As is often said, "When all you have is a hammer , everything looks like a nail."

Server-Side Cursors

Developers familiar with SQL Server will know that in ADO it was possible to execute queries in which the data remained on the server and rows were pulled down as needed. Although this technique results in more roundtrips to the server, which are typically to be avoided, there might be times when you need to see data as it's changed by other users, or perform positioned updates as you scroll through the result set locking the rows as they're traversed.

SQL Server Goes .NET

Microsoft made a design decision with ADO.NET not to support server cursors directly in order to focus on the disconnected and streamed models available through the DataSet and data reader. However, Microsoft is working on a server-side model that will first be released on the Web and incorporated into SqlClient and then, later in 2003, incorporated into the next release of SQL Server code named Yukon. This server model will likely expose a System.Data.SqlServer namespace, a SqlResultSet class, and an ExecuteResultSet method on the SqlCommand object, among others, that will enable you to access server cursors.

Although having access to server cursors from the client will be nice, the real productivity will be gained by embedding the CLR into SQL Server. When this happens, you'll be able to write stored procedures using VB and C# code rather than Transact-SQL. Along with a more productive language, you'll get the benefits of IntelliSense and integrated debugging. You'll also see performance improvements for complex procedures because the managed code will be compiled and not interpreted.

We'll discuss these new and exciting features more fully on Day 21, "Futures and Wrap Up."

One of the means SQL Server uses to do just these sorts of operations is server cursors. To create a server cursor, you use a specific Transact-SQL statement, as shown in Listing 13.4. This example shows how you might traverse all the rows in the Titles table and perform positioned updates if the row meets certain criteria.

Listing 13.4 A server cursor. This cursor scrolls through the Titles table and performs a positioned update on a row if it is in the Sams Teach Yourself series.

 DECLARE @ISBN nchar(10) DECLARE @Title nvarchar(100) DECLARE Titles_Cursor CURSOR DYNAMIC SCROLL_LOCKS FOR SELECT ISBN, Title FROM Titles FOR UPDATE OPEN Titles_Cursor FETCH NEXT FROM Titles_Cursor INTO @ISBN, @Title IF @Title LIKE 'Teach Yourself%'  UPDATE Titles SET Publisher = 'Sams'  WHERE CURRENT OF Titles_Cursor WHILE @@FETCH_STATUS = 0 BEGIN     FETCH NEXT FROM Titles_Cursor INTO @ISBN, @Title     IF @Title LIKE 'Teach Yourself%'       UPDATE Titles SET Publisher = 'Sams'       WHERE CURRENT OF Titles_Cursor END CLOSE Titles_Cursor DEALLOCATE Titles_Cursor 

Although you can attempt to wrap each of these statements in a SqlCommand object and execute them using the ExecuteNonQuery method, a better approach is to wrap the cursor in a stored procedure and then simply call it with a SqlCommand .

SQLXML

On Day 10, you learned how the SqlClient provider exposes the ExecuteXmlReader method to execute and return the XML generated by a query that uses the FOR XML clause on a SELECT statement. The ability to work with SQL Server 2000 data as XML, however, isn't limited to just FOR XML . The group of technologies that integrates XML and SQL Server is, not surprisingly, referred to as SQLXML. Microsoft has created three releases of this technology and has made them available on the Web through MSDN. In a nutshell , the major functionalities include

• XML Views .

  

  XML Views (also referred to as mapping schemas ) are simply XSD documents that map an XML schema to a relational schema using attributes. By defining a schema and annotating it (much as you would when creating a strongly typed DataSet ), the SQLXML query processor can write SQL statements to access data. The Views can then be queried using XPath or XQuery syntax. This allows complete abstraction from the database schema because it's mapped to the XSD. An example of a simple XML View is shown in Listing 13.5.

Listing 13.5 An XML View. This document maps the Customers and Orders table to an XSD schema.

 <xsd:schema  xmlns:xsd="http://www.w3.org/2001/XMLSchema"       xmlns:msdata="urn:schemas-microsoft-com:mapping-schema">   <xsd:element name="Customer" msdata:relation="Customers">     <xsd:complexType>       <xsd:sequence>         <xsd:element name="Order" msdata:relation="Orders">           <xsd:annotation><xsd:appinfo>               <msdata:relationship                 parent="Customers" parent-key="CustomerID"                    child="Orders" child-key="CustomerID" />           </xsd:appinfo></xsd:annotation>           <xsd:complexType>              <xsd:attribute name="OrderDate" type="xsd:dateTime"/>           </xsd:complexType>          </xsd:element>       </xsd:sequence>       <xsd:attribute name="CustomerID" />       <xsd:attribute name="StateProv" type="xsd:string" />     </xsd:complexType>   </xsd:element> </xsd:schema> 

Note that in Listing 13.5, the schema looks remarkably like the DataSet schema shown on Day 7, "XML and the DataSet." This is the case because both are XSD documents and use annotations from the msdata namespace. In this case, the schema defines the relationship between the Customers and Orders tables and exposes the CustomerID , StateProv , and OrderDate columns . The SQLXML query processor can then use this information to formulate a SELECT statement to join these tables. Note that if the names of the XML elements defined in the schema are the same as the column names in SQL Server, no further annotations are required.

• HTTP Access via a URL . Using IIS, you can set up a virtual directory through which you can query data in SQL Server. You can specify your query directly within the query string, through an XML View, or using an XML Template. An XML Template is simply an XML document that encapsulates the SQL statement, XPath or XQuery syntax. A sample XML Template is shown in Listing 13.6.

Listing 13.6 An XML template. This document defines a parameterized XPath query that accesses the XML View in Listing 13.5.

 <root xmlns:sql="urn:schemas-microsoft-com:xml-sql"       sql:xsl="  path to XSLT file  " >  <sql:header>     <sql:param name="state">KS</sql:param>  </sql:header>  <sql:xpath-query mapping-schema="customers.xsd">     /Customers[@StateProv=$state]  </sql:xpath-query> </root> 

You'll notice in Listing 13.6 that the document contains the definition of a parameter that can be passed to it, or in this case, hardcoded in the header element. The Template then contains an XPath query that references the XML View and that selects all the customers whose StateProv matches the parameter. When this template is processed , the appropriate WHERE clause will be appended to the SELECT statement that is produced.

• XML UpdateGrams .

  

  An UpdateGram is an XML document that records the before and after state of rows correlated with an XML View. The SQLXML query processor then writes INSERT , UPDATE , and DELETE statements when the document is processed. UpdateGrams can be used to send XML directly to the server rather than it having to go through stored procedures.

• XML Bulkload . This feature enables you to bulk load XML data directly into SQL Server using an XML View. This is useful when, for example, you're receiving data from a trading partner that's already in an XML document. Using the XML View, you can create annotations to map the data into your SQL Server database. As you might expect, the performance of XML Bulkload is about 75 percent that of the traditional technique.

• .NET Access through managed classes . In addition to accessing SQLXML features via HTTP, a set of managed classes are available. This allows you to work with XML Views and templates directly in your .NET code. A simple example of using these classes is shown in Listing 13.7.

Listing 13.7 SQLXML managed classes. This short example executes an XPath query against the XML View in Listing 13.5.

 Dim cmd As New SqlXmlCommand(ConnStr) cmd.CommandType = SqlXmlCommandType.XPath cmd.CommandText = "/Customer[@StateProv='KS']" cmd.SchemaPath = "customers.xml" cmd.RootTag = "ROOT" Dim r As XmlReader r = cmd.ExecuteXmlReader(); '  Loop through the data ' OR Fill a DataSet Dim ds As New DataSet() Dim ad As New SqlXmlAdapter(cmd) ad.Fill(ds) ' Do some work here ad.Update(ds) 

In Listing 13.7, several of the classes that SQLXML exposes are used, including SqlXmlCommand and SqlXmlAdapter . These classes are analogous to the SqlCommand and SqlDataAdapter classes in the SqlClient provider but are used to execute commands that are processed by the SQLXML query processor. In this case, the listing shows how you might execute an XPath query against the XML View in Listing 13.5 and then return the data either through an XmlReader or in a DataSet .

• SOAP for SQL Server . In the latest Web release (3.0), SOAP support was added to enable you to expose stored procedures or XML Templates as methods in an XML Web Service using SOAP. There's a graphical tool that you can use to pick which procedures and templates to expose.

for RuBoard