This chapter is from the book
This chapter is from the book
This chapter is from the book
SQLXML provides managed code classes that allow you to retrieve XML data from SQL Server (you can translate the data to XML on the server or at the client). These classes have analogues in the .NET Framework itself but are more geared toward SQLXML and exposing its unique functionality in managed code applications. The SQLXML classes reside in an assembly named Microsoft.Data.SqlXml, and, as with any managed code assembly, they can be accessed from apps written in any CLR-compliant language, including C#, VB.NET, Delphi.NET, and others.
The SqlXmlCommand, SqlXmlParameter, and SqlXmlAdapter classes are the key managed code classes in the SqlXml assembly. As I've mentioned, these are similar to their similarly named counterparts in the .NET Framework. SqlXmlCommand is used to execute T-SQL commands or SQL Server procedural objects and optionally return their results as XML. SqlXmlParameter is used to set up parameterized queries. SqlXmlAdapter is used to process the results from a SqlXmlCommand execution. If the underlying data source supports modification, changes can be made at the client and posted back to the server using diffgrams, specialized updategram-like templates used by the .NET Framework to encapsulate data modifications.
The best way to understand how these classes interoperate in a real application is to build one. The C# example code in the next example demonstrates how to use each of the main SQLXML managed classes to execute a stored procedure and process its result set. Let's begin with the source code for the stored procedure (Listing 18.85).
Listing 18.85
USE Northwind
GO
DROP PROC ListCustomers
GO
CREATE PROC ListCustomers @CustomerID nvarchar(10)='%'
AS
PRINT '@CustomerID = ' +@CustomerID
SELECT *
FROM Customers
WHERE CustomerID LIKE @CustomerID
RAISERROR('%d Customers', 1,1, @@ROWCOUNT)
GO
EXEC ListCustomers N'ALFKI'
This stored proc takes a single parameter, a customer ID mask, and lists all the rows from the Northwind Customers table that match it. Listing 18.86 shows the C# code that uses SQLXML managed classes to execute the stored proc. (You can find this code in the CH18\managed_classes subfolder on the CD accompanying this book.)
Listing 18.86
using System;
using Microsoft.Data.SqlXml;
using System.IO;
using System.Xml;
class CmdExample
{
static string strConn = "Provider=SQLOLEDB;Data Source='(local)';
database=Northwind; Integrated Security=SSPI";
public static int CmdExampleWriteXML()
{
XmlReader Reader;
SqlXmlParameter Param;
XmlTextWriter TxtWriter;
//Create a new SqlXmlCommand instance
SqlXmlCommand Cmd = new SqlXmlCommand(strConn);
//Set it up to call our stored proc
Cmd.CommandText = "EXEC ListCustomersXML ?";
//Create a parameter and give it a value
Param = Cmd.CreateParameter();
Param.Value = "ALFKI";
//Execute the proc
Reader = Cmd.ExecuteXmlReader();
//Create a new XmlTextWriter instance
//to write to the console
TxtWriter = new XmlTextWriter(Console.Out);
//Move to the root element
Reader.MoveToContent();
//Write the document to the console
TxtWriter.WriteNode(Reader, false);
//Flush the writer and close the reader
TxtWriter.Flush();
Reader.Close();
return 0;
}
public static int Main(String[] args)
{
CmdExampleWriteXML();
return 0;
}
}
Note the reference to the Microsoft.Data.SqlXml assembly. You will have to add a reference to this assembly in the Visual Studio .NET IDE (or on the csc.exe command line) in order to compile and link this code.
Let's walk through how this code works. We begin by instantiating a new SqlXmlCommand and passing it our connection string. We then set its CommandText property to call a stored procedure with a replaceable parameter. Next, we create a SqlXmlParameter instance and assign its Value property in order to supply a value for the stored procedure's parameter.
Once the SqlXmlCommand object is properly set up, we call its ExecuteXmlReader method. This returns an XmlReader instance that we can use to process the stored proc's results. We then create an XmlTextWriter object so that we can write out the XML returned by the SqlXmlCommand object. We follow up by moving to the start of the document itself (via the MoveToContent call), then write the entire document to the console via the TxtWriter.WriteNode call. We then conclude by flushing the XmlTextWriter object and closing the XmlReader object that was originally returned by the call to SqlXmlCommand.ExecuteXmlReader.
If you've done much programming with the .NET Framework's ADO.NET and XML classes, this code probably looks very familiar to you. All three SQLXML managed classes have counterparts in the .NET Framework itself. The metaphors are the same. They return compatible types with the base .NET Framework classes where it makes sense and can be used interchangeably with them. Their purpose is to extend the ADO.NET classes to include functionality that's specific to SQLXML, not replace them or offer an alternative to them.