Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
C# Class
The stored procedure is called using ADO.NET from a C# class (see Listing 3). This class is representative of code that would form part of a data access layer in a typical N-tier application architecture (refer to Figure 1). The data access layer acts as a wrapper for ADO.NET classes, along with the required database connection and authentication information.
){kind=link}
Listing 3—Part of a DAL class for querying the data store.
using System;
using System.Data;
using System.Data.SqlClient;
using System.Xml;
namespace DAL {
// Connection string stored as a constant for brevity
private const String CN_STR = "Data Source=localhost;" +
"user id=Recipes_User;" +
"password=recipes_user;" +
"Initial Catalog=recipes";
//Constructor
public CData() {
}
/* --- GetXmlDS -------------------------- */
/* Accepts a string SQL query argument */
/* (e.g. "EXEC usp_GetByKeyword 'Seafood'" */
/* and returns a DataSet object. */
/* --------------------------------------- */
public DataSet GetXmlDS(string sSQL) {
string sCN;
SqlConnection oCN;
SqlCommand oCMD;
System.Xml.XmlTextReader oXR;
DataSet oDS = new DataSet();
try {
sCN = CN_STR;
oCN = new SqlConnection(sCN);
oCMD = new SqlCommand(sSQL, oCN);
oCMD.Connection.Open();
oXR = (XmlTextReader)oCMD.ExecuteXmlReader();
oDS.ReadXml(oXR, XmlReadMode.Auto);
oCN.Close();
}
catch (Exception e) {
// Exception handling omitted for brevity
}
return oDS;
}
}
The code in Listing 3 demonstrates the use of ADO.NET, along with the XmlTextReader class to retrieve the results from SQL Server as an XML document. This class exposes a single public method: GetXmlDS. This method accepts a string argument, representing a SQL query, and returns a DataSet object containing the results.
XML plays a key role in distributed applications based on the .NET Framework. Hopefully this article will provide a jump-start for using XML in your .NET application designs. A future article will expand on this topic, demonstrating how to validate an XML document against a schema using ADO.NET.