19.8. Retrieving a Single Database Records

There are a lot of situations where we only need to retrieve a single record from the database. This is primarily the case when dealing with aggregate functions supported by EnterpriseDB like COUNT, MIN, MAX etc.

If you need to retrieve a single result from a query you call the ExecuteScalar() method of the EDBCommand object which returns the first value of the first column of the first row returned by a query. The ExecuteScalar() method is much more efficient than the ExecuteReader() method. So for example in order to find out which employee is the best paid, we would use something like the following query:

  SELECT MAX(sal) FROM emp; 
  

   
<% @ 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 MAX(sal) FROM emp",conn);
		cmdSelect.CommandType = CommandType.Text;			
		int maxSal = Convert.ToInt32(cmdSelect.ExecuteScalar());
				
		Response.Write("Emp Number: " + maxSal);
				
	}
	catch(Exception exp) { 
		Response.Write(exp.ToString());
	}
	finally {
		conn.Close();
	}
}
		
</script>

Save the file as "selectscalar.aspx", save it in your default web root and test it out by typing http://localhost/selectScalar.aspx in your browser window. If you have not played around with the sample tables that ship with EDB, then you should see 5000 displayed on the webpage (given nothing else goes wrong).

The code is fairly self explanatory. The only thing worth pointing out is the explicit conversion of the value returned by the ExecuteScalar() method. We need to do this since the ExecuteScalar() method returns an object you explicitly need to convert it into an integer value by using the Convert.ToInt32 method.