<%@ Page Language="C#" MasterPageFile="~/howto/howto.master" %> <%@ Register TagPrefix=Acme Namespace=Acme %> <%@ Register TagPrefix="Acme" TagName="SourceRef" Src="~/util/SrcRef.ascx"%>

ADO.NET: Retrieve Data from SQL Server



This sample illustrates how to read data from SQL Server using the SqlDataReader class. This class provides a way of reading a forward-only stream of data records from a data source. If you want to work with databases that have OLE DB interfaces or versions of SQL Server prior to SQL Server 7.0, see Retrieve Data Using OLE DB.

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.



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. SqlDataReader myDataReader = null; SqlConnection mySqlConnection = new SqlConnection("server=(local)\SQLExpress;Integrated Security=SSPI;database=northwind"); SqlCommand mySqlCommand = new SqlCommand("SELECT EmployeeID, LastName, FirstName, Title, ReportsTo FROM Employees", mySqlConnection); ... mySqlConnection.Open(); myDataReader = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection); Dim myDataReader as SqlDataReader Dim mySqlConnection as SqlConnection Dim mySqlCommand as SqlCommand mySqlConnection = new SqlConnection("server=(local)\SQLExpress;Integrated Security=SSPI;database=northwind") mySqlCommand = new SqlCommand("SELECT EmployeeID, LastName, FirstName, Title, ReportsTo FROM Employees", mySqlConnection) ... mySqlConnection.Open() myDataReader = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection) SqlDataReader * myDataReader = 0; SqlConnection * mySqlConnection = new SqlConnection(S"server=(local)\\SQLExpress;Trusted_Connection=yes;database=northwind;provider=SQLNCLI"); SqlCommand * mySqlCommand = new SqlCommand("SELECT EmployeeID, LastName, FirstName, Title, ReportsTo FROM Employees", mySqlConnection); ... mySqlConnection->Open(); myDataReader = mySqlCommand->ExecuteReader(); The example reads through the data using the SqlDataReader Read method and writing the data elements out to the console. while (myDataReader.Read()) { Console.Write(myDataReader.GetInt32(0) + "\t"); Console.Write(myDataReader.GetString(2) + " " + myDataReader.GetString(1) + "\t"); Console.Write(myDataReader.GetString(3) + "\t"); if (myDataReader.IsDBNull(4)) Console.Write("N/A\n"); else Console.Write(myDataReader.GetInt32(4) + "\n"); } 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 while (myDataReader->Read()) { Console::Write(myDataReader->GetInt32(0)); Console::Write("\t\t"); Console::Write(myDataReader->GetString(2)); Console::Write(" "); Console::Write(myDataReader->GetString(1)); Console::Write("\t"); Console::Write(myDataReader->GetString(3)); Console::Write("\t"); if (myDataReader->IsDBNull(4)) Console::Write("N/A\n"); else Console::WriteLine(myDataReader->GetInt32(4)); }

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(); ' Always call Close when done reading. myDataReader.Close() ' Close the connection when done with it. mySqlConnection.Close() // 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.