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

Top 5 Contributors of the Month
Melody Anderson
Eminent IT

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

 Subscribe to Code Snippets

SqlDataReader to Retrieve Multiple Rows

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


SqlDataReader to Retrieve Multiple Rows

The SqlDataReader approach to retrieve multiple rows is illustrated in the following code fragment.

using System.IO;
using System.Data;
using System.Data.SqlClient;
public SqlDataReader RetrieveRowsWithDataReader()
  SqlConnection conn = new SqlConnection(
         "server=(local);Integrated Security=SSPI;database=northwind");
  SqlCommand cmd = new SqlCommand("DATRetrieveProducts", conn );
  cmd.CommandType = CommandType.StoredProcedure;
    // Generate the reader. CommandBehavior.CloseConnection causes the
    // the connection to be closed when the reader object is closed
    return( cmd.ExecuteReader( CommandBehavior.CloseConnection ) );
// Display the product list using the console
private void DisplayProducts()
  SqlDataReader reader = RetrieveRowsWithDataReader();
    while (reader.Read())
      Console.WriteLine("{0} {1} {2}", 
                        reader.GetString(1) );
    reader.Close(); // Also closes the connection due to the
                    // CommandBehavior enum used when generating the reader

To retrieve rows with a SqlDataReader

  1. Create a SqlCommand object used to execute the stored procedure and associate it with a SqlConnection object.
  2. Open the connection.
  3. Generate a SqlDataReader object by calling the ExecuteReader method of a SqlCommand object.
  4. To read the data from the stream, call the Read method of the SqlDataReader object to retrieve rows and use the typed accessor methods (such as the GetInt32 and GetString methods) to retrieve the column values.
  5. When you finish with the reader, call its Close method.


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