How Do I...? Common Tasks QuickStart Tutorial
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.
VB sqldtreader.aspx
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)\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)
VB
The example reads through the data using the SqlDataReader Read method and
writing the data elements out to the console.
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
VB
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()
VB
Summary
- A SqlDataReader is for reading a forward-only stream of data records from SQL Server.
- Remember to close the SqlDataReader and then the SqlConnection.
- 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.
Microsoft .NET Framework SDK QuickStart Tutorials Version 2.0
Copyright � 2005 Microsoft Corporation. All rights reserved.
|