Retrieving Database Records

You can use a SELECT statement to retrieve records from the database via a SELECT command. To execute a SELECT statement you must:

• Create and open a database connection.

• Create an EDBCommand object that represents the SELECT statement.

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

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

An EDBDataReader object represents a forward-only and read-only stream of database records, presented one record at a time. To view a subsequent record in the stream, you must call the Read() method of the EDBDataReader object.

The example that follows:

1. Imports the Advanced Server namespace: EnterpriseDB.EDBClient

2. Initializes an EDBCommand object with a SELECT statement.

3. Opens a connection to the database.

4. Executes the EDBCommand by calling the ExecuteReader method of the EDBCommand object.

The results of the SQL statement are retrieved into an EDBDataReader object.

5. Loops through the contents of the EDBDataReader object to display the records returned by the query within a WHILE loop.

The Read() method advances to the next record (if a record exists) and returns true if a record exists, or false to indicate that the EDBDataReader has reached the end of the result set.

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

<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>

To exercise the sample code, save the code in your default web root directory in a file named:

selectEmployees.aspx

To invoke the program, open a web-browser, and browse to:

http://localhost/selectEmployees.aspx

Retrieving a Single Database Record

To retrieve a single result from a query, use the ExecuteScalar() method of the EDBCommand object. The ExecuteScalar() method returns the first value of the first column of the first row of the DataSet generated by the specified query.

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

<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 cmd = new EDBCommand("SELECT MAX(sal) FROM emp",conn);

       cmd.CommandType = CommandType.Text;

       int maxSal = Convert.ToInt32(cmd.ExecuteScalar());

       Response.Write("Emp Number: " + maxSal);

      }
      catch(Exception exp)
  {
    Response.Write(exp.ToString());
      }
      finally
  {
    conn.Close();
      }
}
</script>

Save the sample code in a file in a web root directory named:

selectscalar.aspx

To invoke the sample code, open a web-browser, and browse to:

http://localhost/selectScalar.aspx

Please note that the sample includes an explicit conversion of the value returned by the ExecuteScalar() method. The ExecuteScalar() method returns an object; to view the object, you must convert it into an integer value by using the Convert.ToInt32 method.