Retrieving database records v6.0.2.1

Suggest edits

You can use a SELECT statement to retrieve records from the database using 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 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 EDB Postgres 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.

Loop through the contents of the EDBDataReader object to display the records returned by the query in a WHILE loop.

The Read() method advances to the next record (if a record exists) and returns true if a record exists. It returns false if 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 = ConfigurationManager.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. Then, to invoke the program, enter the following URL into a browser: 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 = ConfigurationManager.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 named selectscalar.aspx in a web root directory.

To invoke the sample code, enter the following in a browser: http://localhost/selectScalar.aspx

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 to an integer value by using the Convert.ToInt32 method.