ADO.NET: Retrieve Data from SQL Server

The SqlDataReader is created by calling the ExecuteReader method of the SqlCommand, not through direct use of the constructor. While the SqlDataReader is in use, the associated SqlConnection is busy serving the SqlDataReader. While in this state, no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called.

VB sqldtreader.aspx [Run Sample] | [View Source]

SqlDataReader provides a means of reading a forward-only stream of data records from a SQL Server data source. For more interactive operations such as scrolling, filtering, navigating, and remoting, use the DataSet.

The example creates a SqlConnection to the Northwind database. The SqlCommand selecting items from the employee table is then executed using the SqlCommand ExecuteReader method. The results of this command are passed to the SqlDataReader.

    Dim myDataReader as SqlDataReader
    Dim mySqlConnection as SqlConnection
    Dim mySqlCommand as SqlCommand
    

    mySqlConnection = new SqlConnection("server=(local)\NetSDK;Integrated Security=SSPI;database=northwind")
    mySqlCommand = new SqlCommand("SELECT EmployeeID, LastName, FirstName, Title, ReportsTo FROM Employees", mySqlConnection)
    ...
    mySqlConnection.Open()
    myDataReader = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
    

    do while (myDataReader.Read())
      Console.Write(myDataReader.GetInt32(0).ToString() + Chr(9))
      Console.Write(myDataReader.GetString(2) + " " + myDataReader.GetString(1) + Chr(9))
      Console.Write(myDataReader.GetString(3) + Chr(9))
      if (myDataReader.IsDBNull(4)) then
        Console.Write("N/A" + Chr(10))
      else
        Console.Write(myDataReader.GetInt32(4).ToString() + Chr(10))
      end if
    loop
     

Finally, the example closes the SqlDataReader, then the SqlConnection.

    ' Always call Close when done reading.
    myDataReader.Close()
    
    ' Close the connection when done with it.
    mySqlConnection.Close()
    

Summary

1. A SqlDataReader is for reading a forward-only stream of data records from SQL Server.
2. Remember to close the SqlDataReader and then the SqlConnection.
3. There can be only one SqlDataReader open at a time on the SqlConnection. If the SqlDataReader is in use, the associated SqlConnection is busy serving the SqlDataReader and while in this state, no other operations can be performed on the SqlConnection other than closing it.