.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> ADO.NET >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

XmlReader to Retrieve Multiple Rows

Posted By:Shashi Ray       Posted Date: November 30, 2009    Points: 10    Category: ADO.NET    URL: http://www.dotnetspark.com  
 

 

XmlReader to Retrieve Multiple Rows

You can use the SqlCommand object to generate an XmlReader object, which provides forward-only, stream-based access to XML data. The command (usually a stored procedure) must generate an XML-based result set, which for SQL Server 2000 usually consists of a SELECT statement with a valid FOR XML clause. The following code fragment illustrates this approach:

public void RetrieveAndDisplayRowsWithXmlReader()
{
  using( SqlConnection conn = new SqlConnection(connectionString) )
  {;
    SqlCommand cmd = new SqlCommand("DATRetrieveProductsXML", conn );
    cmd.CommandType = CommandType.StoredProcedure;
try
  {
      conn.Open();
    XmlTextReader xreader = (XmlTextReader)cmd.ExecuteXmlReader();
    while ( xreader.Read() )
    {
      if ( xreader.Name == "PRODUCTS" ) 
      {
        string strOutput = xreader.GetAttribute("ProductID");
        strOutput += " ";
        strOutput += xreader.GetAttribute("ProductName");
        Console.WriteLine( strOutput );
      }
    }
    xreader.Close();  // XmlTextReader does not support IDisposable so it can't be
                      // used within a using keyword 
  }
}
  

The preceding code uses the following stored procedure:

CREATE PROCEDURE DATRetrieveProductsXML
AS
SELECT * FROM PRODUCTS 
FOR XML AUTO
GO
  

To retrieve XML data with an XmlReader

  1. Create a SqlCommand object to invoke a stored procedure that generates an XML result set (for example, using the FOR XML clause on the SELECT statement). Associate the SqlCommand object with a connection.
  2. Call the ExecuteXmlReader method of the SqlCommand object and assign the results to a forward-only XmlTextReader object. This is the fastest type of XmlReader object that you should use when you do not require any XML-based validation of the returned data.
  3. Read the data by using the Read method of the XmlTextReader object.


     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend