19.7. Retrieving Database Records

You can retrieve records from the database via a SELECT command. In order to execute a SELECT command you need to do the following:

  1. Create and open a database connection

  2. Create a EDBCommand object representing the Select statement to execute

  3. Execute the command with the ExecuteReader() method of the EDBCommand object returning a EDBDataReader

  4. Loop through the EDBDataReader displaying the results or binding the EDBDataReader to some control.

A EDBDataReader represents a forward-only and read-only stream of database records where we can only get one record at a time. Each time we want to read the next record we must call the read() method of the EDBDataReader object.

The following example demonstrates the use of a EDBDataReader in conjunction with a SQL Select statement

Example 19-3. Example - Retrieving all records from the dept table

   
<% @ Page Language="C#" %>
<% @Import Namespace="EnterpriseDB.EDBClient" %>
<% @Import Namespace="System.Data" %>

<script language="C#" runat="server">

private void Page_Load(object sender, System.EventArgs e)
{
	string strConnectionString = ConfigurationSettings.AppSettings["DB_CONN_STRING"];
	EDBConnection conn = new EDBConnection(strConnectionString);	
			
	try {
		conn.Open();
		EDBCommand cmdSelect = new EDBCommand("SELECT * FROM dept",conn);
		cmdSelect.CommandType = CommandType.Text;					
		EDBDataReader drDept = cmdSelect.ExecuteReader();
				
		while(drDept.Read()) {
			Response.Write("Department Number: " + drDept["deptno"]);
			Response.Write("\tDepartment Name: " + drDept["dname"]);
			Response.Write("\tDepartment Location: " + drDept["loc"]);
			Response.Write("<br>");
		}
				
	}
	
	catch(Exception exp) { 
		Response.Write(exp.ToString());
	}
	finally {
		conn.Close();
	}
}		
</script>

   

Save the file as "selectEmployees.aspx" and save it in your default web root, and access it by typing http://localhost/selectEmployees.aspx in your browser window. Given that your connection information is correct and you have installed the sample EDB database you should see the following upon the successful execution of this query.

19.7.1. Retrieving Database Records - Explanation

We start off our example by importing the necessary namespace for using ADO.NET with classes for EnterpriseDB. Next, an EDBCommand object is initialized with an SQL string representing our SELECT query. In our case this query retrieves all the records from the dept table. Then after opening a connection to the database we execute the command by calling the ExecuteReader method of the EDBCommand object. The results of the SQL statement are retrieved in an EDBDataReader object.

Once we have this EDBDataReader object we loop through its contents to display the records returned by the query within a while loop. The Read() method serves two main purposes. First of all it returns true or false indicating whether a record exists or not. Then secondly, the Read() method advances the EDBDataReader to the next record if a record exists.

It is the responsibility of the EDBDataReader to automatically convert the value(s) returned by the database to their proper .NET data type counterparts.