Ajax toolkit
Ajax tutorials
|
|
6.2. SQLXML 4.0SQL Server 2005 introduces SQLXML 4.0, which provides functionality found in previous releases and adds support for new SQL Server 2005 XML and web service functionality. SQLXML 4.0 is installed automatically with SQL Server 2005. You can also install SQLXML on clients' computers from the installation program sqlxml4.msiyou will also need to install MSXML 6.0 from the installation program msxml6.msi. These installation programs are on the SQL Server 2005 installation disk in the Tools\Setup subdirectory. SQLXML 4.0 provides client-side XML functionality for writing applications that access XML data from SQL Server, process it, and send updated data back to the server. SQLXML bridges relational data to XML data in SQL Server. SQLXML is used to query relational data using T-SQL and return XML results, query relational data with XPath, and update relational data using XML. In addition to facilitating working with relational data as XML, SQLXML lets you execute XML template queriesqueries embedded in a dynamic XML documentand server-side XPath queries in SQL Server 2005. The SQL Server .NET data provider, without SQLXML, does not provide this capability. SQLXML 4.0 supports both the SQL Native Client and SQLOLEDB providers. The SQL Native Client provider is recommended, because it supports new SQL Server 2005 features such as the xml data type. 6.2.1. SQLXML Managed ClassesSQLXML managed classes expose SQLXML 4.0 functionality within .NET applications. The SQLXML managed classes provide methods to execute commands (SqlXmlCommand), create parameters for commands (SqlXmlParameter), and interact with the DataSet class (SqlXmlAdapter). These classes are described in the following subsections. 6.2.1.1. SqlXmlCommandThe SqlXmlCommand class executes a T-SQL command, stored procedure, XPath command, XML template file, UpdateGram, or DiffGram against a database. (UpdateGrams and DiffGrams are discussed later in this chapter.) The constructor for the SqlXmlCommand object is:
SqlXmlCommand(string connectionString)
where connectionString is an OLE DB connection string identifying the provider, server, database, and login information. For example:
Provider=SQLNCLI;Server=(local);database=AdventureWorks;Integrated Security=SSPI
You should normally set the Provider property of the connection to SQLNCLI, because the SQL Native Client data provider supports new SQL Server 2005 features such as the xml data type. The SqlXmlCommand class has the public methods described in Table 6-3.
The SqlXmlCommand class has the public properties described in Table 6-4.
6.2.1.2. SqlXmlParameterSqlXmlCommand objects support parameterized command text and stored procedure queries. Call the CreateParameter( ) method of the SqlXmlCommand class to create a parameter. The SqlXmlParameter class has the public properties described in Table 6-5.
6.2.1.3. SqlXmlAdapterThe SqlXmlAdapter class is similar to the DataAdapter class. It provides a mechanism to load XML data into a DataSet and subsequently update the database with changes made offline to the data in the DataSet. The SqlXmlAdapter has the following public constructors:
SqlXmlAdapter(SqlXmlCommand command)
SqlXmlAdapter(string commandText, SqlXmlCommandType commandType,
string connectionString)
SqlXmlAdapter(Stream commandStream, SqlXmlCommandType commandType,
string connectionString)
The SqlXmlAdapter has the public methods described in Table 6-6.
6.2.2. Using SQLXML Managed ClassesThis section contains examples showing how to use the SQLXML managed classes to retrieve, process, output, and update data.
6.2.2.1. Executing a queryThe following example reads the top two employees from the HumanResources.Employee table in AdventureWorks into a Stream object:
using System;
using System.IO;
using Microsoft.Data.SqlXml;
class Program
{
static void Main(string[] args)
{
SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +
"database=AdventureWorks;Integrated Security=SSPI");
cmd.CommandText = "SELECT TOP 2 * FROM HumanResources.Employee " +
"FOR XML AUTO";
Stream stream = cmd.ExecuteStream( );
StreamReader sr = new StreamReader(stream);
string s = sr.ReadToEnd( );
Console.WriteLine(s);
Console.WriteLine(Environment.NewLine + "Press any key to continue.");
Console.ReadKey( );
}
}
The example uses the StreamReader class to read the Stream into a string that is then output to the console. Results follow:
<HumanResources.Employee EmployeeID="1" NationalIDNumber="14417807"
ContactID="1209" LoginID="adventure-works\guy1" ManagerID="16"
Title="Production Technician- WC60" BirthDate="1972-05-15T00:00:00"
MaritalStatus="M" Gender="M" HireDate="1996-07-31T00:00:00"
SalariedFlag="0" VacationHours="21" SickLeaveHours="30"
CurrentFlag="1" rowguid="AAE1D04A-C237-4974-B4D5-935247737718"
ModifiedDate="2004-07-31T00:00:00"/>
<HumanResources.Employee EmployeeID="2" NationalIDNumber="253022876"
ContactID="1030" LoginID="adventure-works\kevin0" ManagerID="6"
Title="Marketing Assistant" BirthDate="1977-06-03T00:00:00"
MaritalStatus="S" Gender="M" HireDate="1997-02-26T00:00:00"
SalariedFlag="0" VacationHours="42" SickLeaveHours="41"
CurrentFlag="1" rowguid="1B480240-95C0-410F-A717-EB29943C8886"
ModifiedDate="2004-07-31T00:00:00"/>
6.2.2.2. Using parameters in a queryThe following example uses a parameterized query to read the data for an employee:
using System;
using System.IO;
using Microsoft.Data.SqlXml;
class Program
{
static void Main(string[] args)
{
SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +
"database=AdventureWorks;Integrated Security=SSPI");
cmd.CommandText = "SELECT * FROM HumanResources.Employee " +
"WHERE EmployeeID=? FOR XML AUTO";
// create the parameter to read data for EmployeeID = 5
SqlXmlParameter parm = cmd.CreateParameter( );
parm.Value = "5";
Stream stream = cmd.ExecuteStream( );
StreamReader sr = new StreamReader(stream);
string s = sr.ReadToEnd( );
Console.WriteLine(s);
Console.WriteLine(Environment.NewLine + "Press any key to continue.");
Console.ReadKey( );
}
}
The results follow:
<HumanResources.Employee EmployeeID="5" NationalIDNumber="480168528"
ContactID="1009" LoginID="adventure-works\thierry0" ManagerID="263"
Title="Tool Designer" BirthDate="1949-08-29T00:00:00" MaritalStatus="M"
Gender="M" HireDate="1998-01-11T00:00:00" SalariedFlag="0"
VacationHours="9" SickLeaveHours="24" CurrentFlag="1"
rowguid="1D955171-E773-4FAD-8382-40FD898D5D4D"
ModifiedDate="2004-07-31T00:00:00"/>
6.2.2.3. Handling an exceptionThe following example shows how to handle a SqlXmlException exception. This example modifies the example in the preceding sectionthe parameter expected in the query string is no longer created and, as a result, the code raises an exception when executed.
using System;
using System.IO;
using Microsoft.Data.SqlXml;
class Program
{
static void Main(string[] args)
{
SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +
"database=AdventureWorks;Integrated Security=SSPI");
cmd.CommandText = "SELECT * FROM HumanResources.Employee " +
"WHERE EmployeeID=? FOR XML AUTO";
try
{
stream = cmd.ExecuteStream( );
StreamReader sr = new StreamReader(stream);
string s = sr.ReadToEnd( );
Console.WriteLine(s);
}
catch (SqlXmlException ex)
{
Console.WriteLine(ex.Message);
}
Console.WriteLine(Environment.NewLine + "Press any key to continue.");
Console.ReadKey( );
}
}
Results are shown in Figure 6-1. Figure 6-1. Results for exception handling example 6.2.2.4. Retrieving an XmlReader objectThe following example reads the data for the first two employees from the HumanResources.Employee table in AdventureWorks into an XmlTextReader object:
using System;
using Microsoft.Data.SqlXml;
using System.Xml;
class Program
{
static void Main(string[] args)
{
SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +
"database=AdventureWorks;Integrated Security=SSPI");
cmd.CommandText = "SELECT TOP 2 * FROM HumanResources.Employee " +
"FOR XML
AUTO";
XmlReader xr = cmd.ExecuteXmlReader( );
using (XmlTextWriter xtw = new XmlTextWriter(Console.Out))
{
xtw.WriteNode(xr, false);
xtw.Flush( );
}
Console.WriteLine(Environment.NewLine + "Press any key to continue.");
Console.ReadKey( );
}
}
The example uses an XmlTextWriter object to output the results in the XmlReader object to the console. Results follow:
<HumanResources.Employee EmployeeID="1" NationalIDNumber="14417807"
ContactID="1209" LoginID="adventure-works\guy1" ManagerID="16"
Title="Production Technician- WC60" BirthDate="1972-05-15T00:00:00"
MaritalStatus="M" Gender="M" HireDate="1996-07-31T00:00:00" SalariedFlag="0"
VacationHours="21" SickLeaveHours="30" CurrentFlag="1"
rowguid="AAE1D04A-C237-4974-B4D5-935247737718"
ModifiedDate="2004-07-31T00:00:00" />
<HumanResources.Employee EmployeeID="2" NationalIDNumber="253022876"
ContactID="1030" LoginID="adventure-works\kevin0" ManagerID="6"
Title="Marketing Assistant" BirthDate="1977-06-03T00:00:00"
MaritalStatus="S" Gender="M" HireDate="1997-02-26T00:00:00" SalariedFlag="0"
VacationHours="42" SickLeaveHours="41" CurrentFlag="1"
rowguid="1B480240-95C0-410F-A717-EB29943C8886"
ModifiedDate="2004-07-31T00:00:00" />
6.2.2.5. Processing an XML result set on the clientThe following example returns a result set and uses the ClientSideXml property of the SqlXmlCommand object to process the results at the client side. It produces an XML document formatted using the FOR XML NESTED mode.
using System;
using System.IO;
using Microsoft.Data.SqlXml;
class Program
{
static void Main(string[] args)
{
Stream stream;
SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +
"database=AdventureWorks;Integrated Security=SSPI");
cmd.CommandText = "SELECT TOP 2 * FROM HumanResources.Employee " +
"FOR XML NESTED";
cmd.ClientSideXml = true;
stream = cmd.ExecuteStream( );
StreamReader sr = new StreamReader(stream);
string s = sr.ReadToEnd( );
Console.WriteLine(s);
Console.WriteLine(Environment.NewLine + "Press any key to continue.");
Console.ReadKey( );
}
}
You can also specify a stored procedure that returns a regular non-XML result set. Results follow:
<Employee EmployeeID="1" NationalIDNumber="14417807" ContactID="1209"
LoginID="adventure-works\guy1" ManagerID="16"
Title="Production Technician - WC60" BirthDate="1972-05-15T00:00:00"
MaritalStatus="M" Gender="M" HireDate="1996-07-31T00:00:00"
SalariedFlag="0" VacationHours="21" SickLeaveHours="30" CurrentFlag="1"
rowguid="AAE1D04A-C237-4974-B4D5-935247737718"
ModifiedDate="2004-07-31T00:00:00"/>
<Employee EmployeeID="2" NationalIDNumber="253022876" ContactID="1030"
LoginID="adventure-works\kevin0" ManagerID="6"
Title="Marketing Assistant" BirthDate="1977-06-03T00:00:00"
MaritalStatus="S" Gender="M" HireDate="1997-02-26T00:00:00"
SalariedFlag="0" VacationHours="42" SickLeaveHours="41" CurrentFlag="1"
rowguid="1B480240-95C0-410F-A717-EB29943C8886"
ModifiedDate="2004-07-31T00:00:00"/>
6.2.2.6. Filling a DataSetThe following example uses an XmlDataAdapter object to fill a DataSet object:
using System;
using Microsoft.Data.SqlXml;
using System.Data;
class Program
{
static void Main(string[] args)
{
SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +
"database=AdventureWorks;Integrated Security=SSPI");
cmd.CommandText = "SELECT TOP 2 * FROM HumanResources.Employee " +
"FOR XML AUTO";
DataSet ds = new DataSet( );
SqlXmlAdapter da = new SqlXmlAdapter(cmd);
da.Fill(ds);
foreach (DataRow row in ds.Tables[0].Rows)
Console.WriteLine("{0} {1}", row["EmployeeID"], row["Title"]);
Console.WriteLine(Environment.NewLine + "Press any key to continue.");
Console.ReadKey( );
}
}
Results are shown in Figure 6-2. Figure 6-2. Results for filling a DataSet example 6.2.2.7. Applying an XSLT transformation to the XML result setThe following example applies an XSLT transformation to the XML result set containing the top 10 employees from the HumanResources.Employee table in AdventureWorks. The XSL file named C:\PSS2005\Employee.xsl follows:
<?xml version='1.0' encoding='UTF-8'?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
<xsl:output method="html"/>
<xsl:template match='*'>
<xsl:apply-templates />
</xsl:template>
<xsl:template match='HumanResources.Employee'>
<TR>
<TD><xsl:value-of select='@EmployeeID'/></TD>
<TD><xsl:value-of select='@Title'/></TD>
</TR>
</xsl:template>
<xsl:template match='/'>
<HTML>
<HEAD/>
<BODY>
<TABLE>
<TR>
<TH>Employee ID</TH>
<TH>Title</TH>
</TR>
<xsl:apply-templates select='ROOT'/>
</TABLE>
</BODY>
</HTML>
</xsl:template>
</xsl:stylesheet>
The code that generates the HTML file by applying the XSLT file Employee.xsl (stored in the C:\PSS2005 directory) follows:
using System;
using System.IO;
using Microsoft.Data.SqlXml;
class Program
{
static void Main(string[] args)
{
SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +
"database=AdventureWorks;Integrated Security=SSPI");
cmd.CommandText = "SELECT TOP 10 * FROM HumanResources.Employee " +
"FOR XML AUTO";
cmd.CommandType
= SqlXmlCommandType.Sql;
cmd.RootTag
= "ROOT";
cmd.XslPath
= @"C:\PSS2005\Employee.xsl";
Stream s = cmd.ExecuteStream( );
byte[] b = new byte[s.Length];
s.Read(b, 0, b.Length);
FileStream fs = new FileStream(@"C:\PSS2005\Employee.html",
FileMode.OpenOrCreate);
fs.Write(b, 0, b.Length);
fs.Flush( );
}
}
The CommandType property of the SqmXmlCommand object is set to SqlXmlCommandType.Sql from the values described in Table 6-4. This specifies that the SQL command in the CommandText property is processed. The ExecuteStream( ) method of the SqlXmlCommand object returns the results of executing the commandin this case a SQL queryas a Stream object. The RootTag property of the SqlXmlCommand object specifies the root element for the XML generated by the queryin this case ROOT, which matches the xsl:apply-templates select element in the XSLT transformation file. The XslPath property specifies the full name of the XSLT transformation file to applythe path to the file can be absolute or relative. An excerpt from the resulting HTML file, named C:\PSS2005\Employee.html, follows:
<HTML>
<HEAD>
<META http-equiv="Content-Type" content="text/html; charset=utf-8">
</HEAD>
<BODY>
<TABLE>
<TR>
<TH>Employee ID</TH>
<TH>Title</TH>
</TR>
<TR>
<TD>1</TD>
<TD>Production Technician - WC60</TD>
</TR>
<TR>
<TD>2</TD>
<TD>Marketing Assistant</TD>
</TR>
<TR>
...
<TR>
<TD>10</TD>
<TD>Production Technician - WC10</TD>
</TR>
</TABLE>
</BODY>
</HTML>
Figure 6-3 shows the file displayed in Internet Explorer. 6.2.3. Annotated Mapping SchemasAn XML schema defines the structure of an XML document and any constraints on the data in the document. In an XML schema, the <xs:schema> element encloses the schema. The <xs:schema> element also contains attributes that define the namespace that the schema is in, as well as namespaces used in the schema. A valid XSD schema is derived from the XML schema namespace at http://www.w3.org/2001/XMLSchema and must have the <xs:schema> element defined as follows:
<xs:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
Annotations are attributes used to map XML data to database tables and columns in a relational database, and to specify relationships between and constraints on multiple tables within an XSD schema. These mapping schemas let you do the following:
Annotations are defined in the urn:schemas-microsoft-com:mapping-schema namespace. Adding this namespace to the <xs:schema> element is the easiest way to specify the namespace:
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
You can specify any prefix (instead of sql) for the namespace. You can easily create an XSD schema using XML Schema Designer in Visual Studio 2005. For example, to create a schema for the HumanResources.Employee table in AdventureWorks, follow these steps:
You can add annotations to this XSD schema and remove schema information that you do not need. The following example shows the same schema with sql:relation and sql:field mappings added and unnecessary information removed. Note the addition of the highlighted mapping-schema namespace in the xs:schema element.
<xs:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xs:element name="Employee" sql:relation="HumanResources.Employee">
<xs:complexType>
<xs:sequence>
<xs:element name="EmployeeID" type="xs:int" sql:field="EmployeeID" />
<xs:element name="NationalIDNumber" type="xs:string"
sql:field="NationalIDNumber" />
<xs:element name="ContactID" type="xs:int" sql:field="ContactID" />
<xs:element name="LoginID" type="xs:string" sql:field="LoginID" />
<xs:element name="ManagerID" type="xs:int" sql:field="ManagerID" />
<xs:element name="Title" type="xs:string" sql:field="Title" />
<xs:element name="BirthDate" type="xs:date" sql:field="BirthDate" />
<xs:element name="MaritalStatus" type="xs:string"
sql:field="MaritalStatus" />
<xs:element name="Gender" type="xs:string" sql:field="Gender" />
<xs:element name="HireDate" type="xs:date" sql:field="HireDate" />
<xs:element name="SalariedFlag" type="xs:boolean"
sql:field="SalariedFlag" />
<xs:element name="VacationHours" type="xs:int"
sql:field="VacationHours" />
<xs:element name="SickLeaveHours" type="xs:int"
sql:field="SickLeaveHours" />
<xs:element name="CurrentFlag" type="xs:boolean"
sql:field="CurrentFlag" />
<xs:element name="rowguid" type="xs:string" sql:field="rowguid" />
<xs:element name="ModifiedDate" type="xs:date"
sql:field="ModifiedDate" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
If the sql:field attribute is not specified for a field element or attribute, that element is automatically mapped to a column that has the same name, if one exists. The annotations in the example are actually unnecessary, because field elements and columns all have the same name. The case-sensitivity of the table and field names defined by the sql:relation and sql:field attributes is determined by whether SQL Server is using case-sensitive collation. Table 6-7 describes the most common mapping schema annotations.
Annotations are used to define the relational hierarchy of the data based on the relationships of the underlying tables. Specify a <sql:relationship> element for each relationship in the <xs:appinfo> element within the <xs:annotation> element in the XSD schema. The following code snippet defines a parent-child relationship between the Sales.Customer and Sales.SalesOrderHeader tables in AdventureWorks:
<xsd:annotation>
<xsd:appinfo>
<sql:relationship name="Customer-SalesOrderHeader"
parent="Sales.Customer"
parent-key="CustomerID"
child="Sales.SalesOrderHeader"
child-key="CustomerID" />
</xsd:appinfo>
</xsd:annotation>
Add the <xsd:annotation> element immediately following the <xs:schema> element. Table 6-8 describes annotation attributes used to define an XSD relationship.
6.2.4. XML Template QueriesAn XML template query is an XML document with one or more T-SQL or XPath queries inside. An XML template query lets you query an XML document using T-SQL or XPath. The syntax is:
<rootName xmlns:sql="urn:schemas-microsoft-com:xml-sql" [ sql:xsl="stylesheet" ] >
[ <sql:header>
[ <sql:param name="paramName">paramValue</sql:param> [ ... n ] ]
</sql:header> ]
<sql:query client-side-xml="n">>
tsqlQuery [ ... n ]
</sql:query> [ ... n ]
<sql:xpath-query mapping-schema="annotatedSchemaFile">
xpathQuery
</sql:xpath-query>
</rootname>
where:
The following example uses an XML template file containing a T-SQL query to return an XML document containing data about the top two employees in the HumanResources.Employee table in AdventureWorks. The XML template containing the T-SQL query follows:
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<sql:query>
SELECT TOP 2 * FROM HumanResources.Employee FOR XML AUTO
</sql:query>
</ROOT>
The code that executes the query in the XML template file (stored in the C:\PSS2005 directory) follows:
using System;
using System.IO;
using Microsoft.Data.SqlXml;
class Program
{
static void Main(string[] args)
{
SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +
"database=AdventureWorks;Integrated Security=SSPI");
cmd.CommandType = SqlXmlCommandType.TemplateFile;
cmd.CommandText = @"C:\PSS2005\TopTwoEmployeesTemplate.xml";
Stream stream = cmd.ExecuteStream( );
StreamReader sr = new StreamReader(stream);
string s = sr.ReadToEnd( );
Console.WriteLine(s);
Console.WriteLine(Environment.NewLine + "Press any key to continue.");
Console.ReadKey( );
}
}
The results follow:
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql">
<HumanResources.Employee EmployeeID="1" NationalIDNumber="14417807"
ContactID="1209" LoginID="adventure-works\guy1" ManagerID="16"
Title="Production Technician - WC60" BirthDate="1972-05-15T00:00:00"
MaritalStatus="M" Gender="M" HireDate="1996-07-31T00:00:00" SalariedFlag="0"
VacationHours="21" SickLeaveHours="30" CurrentFlag="1"
rowguid="AAE1D04A-C237-4974-B4D5-935247737718"
ModifiedDate="2004-07-31T00:00:00"/>
<HumanResources.Employee EmployeeID="2" NationalIDNumber="253022876"
ContactID="1030" LoginID="adventure-works\kevin0" ManagerID="6"
Title="Marketing Assistant" BirthDate="1977-06-03T00:00:00"
MaritalStatus="S" Gender="M" HireDate="1997-02-26T00:00:00" SalariedFlag="0"
VacationHours="42" SickLeaveHours="41" CurrentFlag="1"
rowguid="1B480240-95C0-410F-A717-EB29943C8886"
ModifiedDate="2004-07-31T00:00:00"/>
</ROOT>
The CommandType property of the SqmXmlCommand object is set to SqlXmlCommandType.TemplateFile from the values described in Table 6-4. This specifies that the template file at the location specified by the CommandText property is executed. The ExecuteStream( ) method of the SqlXmlCommand object returns the results of executing the commandin this case a query in an XML template fileas a Stream object. The next example shows how to execute an XPath query against a mapping schema for the HumanResources.Employee table in the AdventureWorks database. The mapping schema follows:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="Employee" sql:relation="HumanResources.Employee">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="EmployeeID" type="xsd:int" sql:field="EmployeeID" />
<xsd:element name="NationalIDNumber" type="xsd:string"
sql:field="NationalIDNumber" />
<xsd:element name="ContactID" type="xsd:int" sql:field="ContactID" />
<xsd:element name="LoginID" type="xsd:string" sql:field="LoginID" />
<xsd:element name="ManagerID" type="xsd:int" sql:field="ManagerID" />
<xsd:element name="Title" type="xsd:string" sql:field="Title" />
<xsd:element name="BirthDate" type="xsd:date" sql:field="BirthDate" />
<xsd:element name="MaritalStatus" type="xsd:string"
sql:field="MaritalStatus" />
<xsd:element name="Gender" type="xsd:string" sql:field="Gender" />
<xsd:element name="HireDate" type="xsd:date" sql:field="HireDate" />
<xsd:element name="SalariedFlag" type="xsd:boolean"
sql:field="SalariedFlag" />
<xsd:element name="VacationHours" type="xsd:int"
sql:field="VacationHours" />
<xsd:element name="SickLeaveHours" type="xsd:int"
sql:field="SickLeaveHours" />
<xsd:element name="CurrentFlag" type="xsd:boolean"
sql:field="CurrentFlag" />
<xsd:element name="rowguid" type="xsd:string" sql:field="rowguid" />
<xsd:element name="ModifiedDate" type="xsd:date"
sql:field="ModifiedDate" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The code that returns an XML document containing data for the employee with EmployeeID = 2 uses an XPath query and the preceding mapping file (stored in the C:\PSS2005 directory):
using System;
using System.IO;
using Microsoft.Data.SqlXml;
class Program
{
static void Main(string[] args)
{
SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +
"database=AdventureWorks;Integrated Security=SSPI");
cmd.CommandText = @"Employee[EmployeeID=2]";
cmd.CommandType = SqlXmlCommandType.XPath;
cmd.RootTag = "ROOT";
cmd.SchemaPath = @"C:\PSS2005\Employee.xsd";
Stream stream = cmd.ExecuteStream( );
StreamReader sr = new StreamReader(stream);
string s = sr.ReadToEnd( );
Console.WriteLine(s);
Console.WriteLine(Environment.NewLine + "Press any key to continue.");
Console.ReadKey( );
}
}
The results follow:
<?xml version="1.0" encoding="utf-8" ?>
<ROOT>
<Employee>
<EmployeeID>2</EmployeeID>
<NationalIDNumber>253022876</NationalIDNumber>
<ContactID>1030</ContactID>
<LoginID>adventure-works\kevin0</LoginID>
<ManagerID>6</ManagerID>
<Title>Marketing Assistant</Title>
<BirthDate>1977-06-03</BirthDate>
<MaritalStatus>S</MaritalStatus>
<Gender>M</Gender>
<HireDate>1997-02-26</HireDate>
<SalariedFlag>0</SalariedFlag>
<VacationHours>42</VacationHours>
<SickLeaveHours>41</SickLeaveHours>
<CurrentFlag>1</CurrentFlag>
<rowguid>1B480240-95C0-410F-A717-EB29943C8886</rowguid>
<ModifiedDate>2004-07-31</ModifiedDate>
</Employee>
</ROOT>
The CommandType property of the SqmXmlCommand object is set to SqlXmlCommandType.Xpath from the values described in Table 6-4. This specifies that the XPath command in the CommandText property is executed. The ExecuteStream( ) method of the SqlXmlCommand object returns the results of executing the commandin this case an XPath queryas a Stream object. 6.2.5. UpdateGramsAn UpdateGram is an XML template used to insert, update, or delete data in the database. An UpdateGram uses mapping information provided in the annotated XML (XSD or XDR) schema. The UpdateGram format is:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync [ mapping-schema= "AnnotatedSchemaFile.xml" ] >
<updg:before>
...
</updg:before>
<updg:after>
...
</updg:after>
</updg:sync>
</ROOT>
where the following elements are defined in the urn:schemas-microsoft-com:xml-updategram namespace:
An UpdateGram uses the operations described in Table 6-9. The UpdateGram mapping to the database can be implicit or explicitly specified using an XSD or XDR schema. Implicit mapping maps each element in the <before> and <after> elements to a table, and each attribute of the <before> and <after> elements to a column in the table. Explicit mapping uses the annotated schema file specified by the mapping-schema attribute of the <sync> element to map elements and attributes in the <before> and <after> elements to tables and columns in the database. The path of the mapping schema file is specified relative to the location of the UpdateGram. The examples in this section use a table named UpdateGramTable. Create the table using the following T-SQL statement:
USE ProgrammingSqlServer2005
CREATE TABLE UpdateGramTable
(
ID int,
Name varchar(50)
)
The following code executes an UpdateGram. To process the three UpdateGrams that follow the code, replace the full filename of the UpdateGram file passed into the StreamReader constructor with the name of each of the three UpdateGram files in the order in which they appear.
using System;
using System.IO;
using Microsoft.Data.SqlXml;
class Program
{
static void Main(string[] args)
{
StreamReader sr = new StreamReader(@"C:\PSS2005\UpdateGram.xml");
string s = sr.ReadToEnd( );
sr.Close( );
SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +
"database=ProgrammingSqlServer2005;Integrated Security=SSPI");
cmd.CommandType = SqlXmlCommandType.UpdateGram;
cmd.CommandText = s;
cmd.ExecuteNonQuery( );
Console.WriteLine("Press any key to continue.");
Console.ReadKey( );
}
}
The CommandType property of the SqmXmlCommand object is set to SqlXmlCommandType.UpdateGram from the values described in Table 6-4. This specifies that the UpdateGram in the CommandText property is processed. The ExecuteNonQuery( ) method of the SqlXmlCommand object executes the commandin this case, processes the UpdateGramand returns nothing. The following UpdateGram inserts three records into the table UpdateGramTable:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
<updg:before>
</updg:before>
<updg:after>
<UpdateGramTable ID="1" Name="Record 1"/>
<UpdateGramTable ID="2" Name="Record 2"/>
<UpdateGramTable ID="3" Name="Record 3"/>
</updg:after>
</updg:sync>
</ROOT>
The following UpdateGram deletes the record with ID = 2 from the table UpdateGramTable:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
<updg:before>
<UpdateGramTable ID="2" Name="Record 2"/>
</updg:before>
<updg:after>
</updg:after>
</updg:sync>
</ROOT>
The following UpdateGram updates the record with ID = 3 in the table UpdateGramTable:
<ROOT xmlns:updg="urn:schemas-microsoft-com:xml-updategram">
<updg:sync >
<updg:before>
<UpdateGramTable ID="3" Name="Record 3"/>
</updg:before>
<updg:after>
<UpdateGramTable ID="3" Name="Updated Record 3"/>
</updg:after>
</updg:sync>
</ROOT>
6.2.6. DiffGramsA DiffGram is an XML document format introduced with the DataSet in Visual Studio .NET 1.0 and used to synchronize offline changes made to data with a database server or other persistent store using a DataAdapter. The DiffGram format is:
<?xml version="1.0"?>
<diffgr:diffgram
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1"
xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<DataInstance>
...
</DataInstance>
[<diffgr:before>
...
</diffgr:before>]
</diffgr:diffgram>
where:
The DiffGram uses the following XML annotations that are defined in the urn:schemas-microsoft-com:xml-diffgram-v1 namespace:
A DiffGram identifies whether records are unchanged, deleted, updated, or inserted according to the rules described in Table 6-10.
Examples of using a DiffGram from SQLXML 4.0 managed classes to modify data in the database follow. The examples use a table named DiffGramTable. Create the table using the following T-SQL statement:
USE ProgrammingSqlServer2005
CREATE TABLE DiffGramTable
(
ID int,
Name varchar(50)
)
The examples in this section also use an XSD annotated mapping schema named DiffGramTable.xsd for the table DiffGramTable:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema"
xmlns:sql="urn:schemas-microsoft-com:mapping-schema">
<xsd:element name="DiffGramTable" sql:relation="DiffGramTable">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="ID" type="xsd:int" sql:field="ID" />
<xsd:element name="Name" type="xsd:string" sql:field="Name" />
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
The following code executes a DiffGram. To process the three DiffGrams that follow the code, replace the full filename of the DiffGram file passed into the highlighted StreamReader constructor with the name of each of the three DiffGram files in the order in which they appear.
using System;
using System.IO;
using Microsoft.Data.SqlXml;
class Program
{
static void Main(string[] args)
{
StreamReader sr = new StreamReader(@"C:\PSS2005\DiffGram.xml");
string s = sr.ReadToEnd( );
sr.Close( );
SqlXmlCommand cmd = new SqlXmlCommand("Provider=SQLNCLI;Server=(local);" +
"database=ProgrammingSqlServer2005;Integrated Security=SSPI");
cmd.CommandType = SqlXmlCommandType.DiffGram;
cmd.CommandText = s;
cmd.ExecuteNonQuery( );
Console.WriteLine("Press any key to continue.");
Console.ReadKey( );
}
}
The CommandType property of the SqmXmlCommand object is set to SqlXmlCommandType.DiffGram from the values described in Table 6-4. This specifies that the DiffGram in the CommandText property is processed. The ExecuteNonQuery( ) method of the SqlXmlCommand object executes the commandin this case processes the DiffGramand returns nothing. The following DiffGram inserts three records into the table DiffGramTable. Note that the hasChanges attribute is set to "inserted".
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"
sql:mapping-schema="c:\PSS2005\DiffGramTable.xsd">
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<DocumentElement>
<DiffGramTable diffgr:id="DiffGramTable1" msdata:rowOrder="0"
diffgr:hasChanges="inserted">
<ID>1</ID>
<Name>Record 1</Name>
</DiffGramTable>
<DiffGramTable diffgr:id="DiffGramTable2" msdata:rowOrder="1"
diffgr:hasChanges="inserted">
<ID>2</ID>
<Name>Record 2</Name>
</DiffGramTable>
<DiffGramTable diffgr:id="DiffGramTable3" msdata:rowOrder="2"
diffgr:hasChanges="inserted">
<ID>3</ID>
<Name>Record 3</Name>
</DiffGramTable>
</DocumentElement>
</diffgr:diffgram>
</ROOT>
The following DiffGram deletes the record with ID = 2 from the table DiffGramTable. In this case, hasChanges is not used.
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"
sql:mapping-schema="c:\PSS2005\DiffGramTable.xsd">
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<DocumentElement>
</DocumentElement>
<diffgr:before>
<DiffGramTable diffgr:id="DiffGramTable2" msdata:rowOrder="1">
<ID>2</ID>
<Name>Record 2</Name>
</DiffGramTable>
</diffgr:before>
</diffgr:diffgram>
</ROOT>
The following UpdateGram updates the record with ID = 3 in the table UpdateGramTable. Here, hasChanges is set to ="modified".
<ROOT xmlns:sql="urn:schemas-microsoft-com:xml-sql"
sql:mapping-schema="c:\PSS2005\DiffGramTable.xsd">
<diffgr:diffgram xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
xmlns:diffgr="urn:schemas-microsoft-com:xml-diffgram-v1">
<DocumentElement>
<DiffGramTable diffgr:id="DiffGramTable2" msdata:rowOrder="1"
diffgr:hasChanges="modified">
<ID>3</ID>
<Name>Updated Record 3</Name>
</DiffGramTable>
</DocumentElement>
<diffgr:before>
<DiffGramTable diffgr:id="DiffGramTable2" msdata:rowOrder="1">
<ID>3</ID>
<Name>Record 3</Name>
</DiffGramTable>
</diffgr:before>
</diffgr:diffgram>
</ROOT>
|
|
|
Ajax toolkit
Ajax tutorials
New Item from the context menu to open the Add New Item dialog box.