How to retrieve data from SQL Server using Ado.net

Ado|server| Data This example illustrates how to use the SqlDataReader class to read data from SQL Server. This class provides a way to read a data-only stream from a data source. If you want to use a database that has an OLE DB interface or a previous version of SQL Server 7.0, see Retrieving Data from OLE DB. SqlDataReader is created by calling the ExecuteReader method of SqlCommand, rather than by using the constructor directly. When SqlDataReader is in use, the associated SqlConnection will be busy providing services for SqlDataReader. In this case, you cannot perform any other action on the SqlConnection in addition to shutting down SqlConnection. This situation persists until the SqlDataReader Close method is invoked. SqlDataReader provides a way to read a data-only stream from a SQL Server data source. For more interactive operations, such as scrolling, filtering, positioning, remoting, and so on, use a dataset. This example creates a SqlConnection to the Northwind database. Then use the SqlCommand ExecuteReader method to perform a SqlCommand that selects items from the Employee (Employee) table. The results of this command are passed to SqlDataReader. C #: SqlDataReader mydatareader = null; SqlConnection mysqlconnection = new SqlConnection ("server= (local) \vsdotnet;" Trusted_connection=yes;database=northwind "); SqlCommand Mysqlcommand = new SqlCommand ("Select EmployeeID, LastName, FirstName, Title, ReportsTo from Employees", MYSQLC onnection); ... mysqlconnection.open (); MyDataReader = Mysqlcommand.executereader (commandbehavior.closeconnection);This example uses the SqlDataReader read method to read all the data and then writes the data elements to the console. C #: 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"); Finally, the example closes SqlDataReader before closing SqlConnection. C #://Always call Close when done reading. Mydatareader.close (); Close the connection is done with it. Mysqlconnection.close (); Summary 1, SqlDataReader is used to read only incoming data record streams from SQL Server. 2, please remember to close the SqlDataReader first, and then close the SqlConnection. 3. Only one SqlDataReader can be opened on SqlConnection at a time. If SqlDataReader is in use, the associated SqlConnection will be busy providing services to SqlDataReader. In this case, you cannot perform any other action on the SqlConnection in addition to shutting down SqlConnection.