Loading data as XML from DB

In most of the applications, the data will be loaded as Dataset or datareader from Sql server from the back end DB. If we are going to look at service oriented approach, the services normally return xml. Most of the time we get data from SQl and convert to xml and send it. We could get the data directly from Db itself.

From the stored procedure here is my select statement

This statement selects the data from Department table which has columns DeptID,DeptName,IsActive

SELECT
1 AS TAG, NULL AS PARENT,
DeptID AS [Department!1!DeptID],
ISNULL(DeptName, '') AS [Department!1!DeptName],
FROM Department (NOLOCK)
WHERE IsActive = 'Y'
ORDER BY DeptName
FOR XML EXPLICIT, ROOT('Department')

The first two columns Tag and PARENT are meta coulmns. for more details on these tags you can check http://msdn.microsoft.com/en-us/library/ms189068.aspx

The xml returned would be

<Department> < DeptID="1" DeptName="Administration"/> < /Department >

Loading the Xml Data using C#

SqlConnection dbConn = new SqlConnection(connectionstring);
dbCommand.Connection = dbConn;
dbConn.Open();
XmlReader dbReader;
dbReader = dbCommand.ExecuteXmlReader();
dbReader.Read();
StringBuilder str = new StringBuilder();
do
{
str.Append(dbReader.ReadOuterXml());

}
while (dbReader.ReadState != ReadState.EndOfFile);
dbConn.Close();
return str.ToString();

Above statement will return the xml data returned from DB as a string format which can be returned from service directly.

But it is always a good practice to bind the data returned from DB to a schema for the datatype check and perorm the validation. The above method returns xml but does not have any infromation on the schema of the data. So this may not be suitable in all the situations.