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;
Example 25-4. Retrieving a Single Database Record
<% @ 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 EnterpriseDB, 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.