Example 19 SqlDataReader.aspx

<%@ Import Namespace="System.Data.SqlClient" %> <% Dim conNorthwind As SqlConnection Dim cmdSelectEmployees As SqlCommand Dim dtrEmployees As SqlDataReader conNorthwind = New SqlConnection( "Server=localhost;uid=sa;pwd=secret;database=NorthWind" ) conNorthwind.Open() cmdSelectEmployees = New SqlCommand( "Select ename From Employees", conNorthwind ) dtrEmployees = cmdSelectEmployees.ExecuteReader() While dtrEmployees.Read() Response.Write(“<li>" ) Response.Write(dtrEmployees( "ename" ) ) End While dtrEmployees.Close() conNorthwind.Close() %>

The output is shown below:

The above first line imports the necessary namespace to use the ADO.NET classes for SQL Server.

Next, a connection is created and opened for the database located on the local server named Northwind.

After the database connection is opened, a SqlCommand object is initialized with SQL string that contains a SQL Select statement. This statement retrieves all the records from an employee’s table.

Next, the command is executed by calling the ExecuteReader () method of the SqlCommand class. This method returns a SqlDataReader class that represents the results by executing the SQL Select statement.

When you have a SqlDataReader, you need to loop through its contents to display all the records returned by the query. This is accomplished with a While. .. End while loop. All the records returned by the Select statement are displayed with the following block of code:

While dtrEmployees. Read () Response.Write (“<li>“) Response.Write (dtrEmployees (“ename”)) End While

The Read () method of the SqlDataReader class does two things whenever it is called. Firstly, the method returns the value true if another record exists, and it returns false otherwise.

Secondly, the method advances the DataReader to the next record if the next record exists. By combining these functions, the Read () method enables to quickly loop through the contents of a DataReader.

The above example works only with Microsoft SQL Server. To use other data­bases, you must provide the appropriate provider-specific namespaces such as System.Data.OleDb or System.Data.OracleClient.

Example 20 shows how would you execute a query against a Microsoft Access database. The page retrieves and displays records from an Access table named Employees.

Example 20 OleDbDatabaseReader.aspx

<%@ Import Namespace="System.Data.OleDb" %> <% Dim conEmployees As OleDbConnection Dim cmdSelectEmployees As OleDbCommand Dim dtrEmployees As OleDbDataReader conEmployees = New OleDbConnection( "PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA Source=c:\Employees.mdb" ) conEmployees.Open() cmdSelectEmployees = New OleDbCommand( "Select ename From Employees", conEmployees ) dtrEmployees = cmdSelectEmployees.ExecuteReader() While dtrEmployees.Read() Response.Write (“<li>“) Response.Write (dtrEmployees (“ename”)) End While dtrEmployees. Close () conEmployees. Close () %>

ExpertRating ASP.NET Certification - Get Certified Now!!

.