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

Top 5 Contributors of the Month
Ava Aiden
Imran Ghani
Thomas Shaw

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;
    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:


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:


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