Execute Scalar: Getting Single Value from the Database

text zoom

Previous: Deleting Records from Database Execute Reader: Fetching Records from Database :Next

Execute Scalar method to retrieve a single value (for example, an aggregate value) from a database. Since the return type of ExecuteScalar is type object, we use a cast operator to convert the value to desired data type. The ExecuteScalar method belongs to the Command object in ASP.Net..

Example: Demonstrate Execute Scalar method of the Command Object

ExecuteScalar.aspx

<% @ Page Language ="C#" AutoEventWireup ="true" CodeFile ="ExecuteScalar.aspx.cs" Inherits ="_Default" %>

<! DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

</head >

<body >

<div>

<strong> Execute Scalar </strong>

<br />

<em > Total No. of Records in Each Table: </ em >< strong >< em > Press

Button in front of EachQuery <br />

<br />

</em>       < br />

<asp:Label ID ="Label1" runat ="server" Text ="SELECT count(*) FROM Categories"

Width ="262px"></asp:Label ></td>

<asp:Button ID ="btnCategories" runat ="server" OnClick ="btnCategories_Click" Text ="Count" /></td>

<asp:Label ID ="lblCategories" runat ="server"></ asp : Label ></ td >

</tr>

<asp:Label ID ="Label2" runat ="server" Text ="SELECT count(*) FROM Customers" Width ="258px"></ asp : Label ></ td >

<asp:Button ID ="btnCustomers" runat ="server" OnClick ="btnCustomers_Click" Text ="Count" /></ td >

<asp:Label ID ="lblCustomers" runat ="server"></ asp : Label ></ td >

</ tr >

<tr>

<asp:Label ID ="Label3" runat ="server" Text ="SELECT count(*) FROM Employees" Width ="257px"></ asp : Label ></ td >

<asp: Button ID ="btnEmployees" runat ="server" OnClick ="btnEmployees_Click" Text ="Count" /></ td >

<asp:Label ID ="lblEmployees" runat ="server"></ asp : Label ></ td >

</tr>

<tr>

<asp:Label ID ="Label4" runat ="server" Text ="SELECT count(*) FROM Orders" Width ="257px"></asp:Label ></ td >

<asp:Button ID ="btnOrders" runat ="server" OnClick ="btnOrders_Click" Text ="Count" /></ td >

<asp:Label ID ="lblOrders" runat ="server"></ asp : Label ></ td >

</tr>

<tr >

<asp:Label ID ="Label5" runat ="server" Text ="SELECT count(*) FROM Products" Width ="256px"></ asp : Label ></ td >

<asp:Button ID ="btnProducts" runat ="server" OnClick ="btnProducts_Click" Text ="Count" /></ td >

<asp:Label ID ="lblProducts" runat ="server"></ asp : Label ></ td >

</tr >

<tr >

<asp:Label ID ="Label6" runat ="server" Text ="SELECT count(*) FROM Region" Width ="253px"></ asp : Label ></ td >

<asp:Button ID ="btnRegion" runat ="server" OnClick ="btnRegion_Click" Text ="Count" /></ td >

</tr >

<tr >

<asp:Button ID ="btnShippers" runat ="server" OnClick ="btnShippers_Click" Text ="Count" /></ td >

</tr >

<tr >

<asp:Label ID ="Label8" runat ="server" Text ="SELECT count(*) FROM Suppliers" Width ="256px"></ asp : Label ></ td >

<asp:Label ID ="lblSuppliers" runat ="server"></ asp : Label ></ td >

</ tr >

<tr >

<asp:Label ID ="Label9" runat ="server" Text ="SELECT count(*) FROM Territories" Width ="252px"></ asp : Label ></ td >

< td style ="width: 3px">

<asp:Button ID ="btnTerritories" runat ="server" OnClick ="btnTerritories_Click" Text ="Count" /></ td >

<asp:Label ID ="lblTerritories" runat ="server"></ asp : Label ></ td >

</ tr >

</table>

</strong>

</div>

</form>

</body>

</html>

ExecuteScalar.aspx.cs

using System;

using System.Data;

using System.Configuration;

using System.Web;

using System.Web.Security;

using System.Web.UI;

using System.Web.UI.WebControls;

using System.Web.UI.WebControls.WebParts;

using System.Web.UI.HtmlControls;

using System.Data.SqlClient;

public partial class _Default : System.Web.UI. Page

{

protected void Page_Load( object sender, EventArgs e)

{

}

protected void btnCategories_Click( object sender, EventArgs e)

{ //Categories Table

string strQuery = "select Count(*) from Categories" ;

lblCategories.Text = countNumberOfRecords(strQuery).ToString() ;

}

protected void btnCustomers_Click( object sender, EventArgs e)

{ //Customers Table

string strQuery = "select Count(*) from Customers" ;

lblCustomers.Text = countNumberOfRecords(strQuery).ToString();

}

protected void btnEmployees_Click( object sender, EventArgs e)

{ //Employee Table

string strQuery = "select Count(*) from Employees" ;

lblEmployees.Text = countNumberOfRecords(strQuery).ToString();

}

protected void btnOrders_Click( object sender, EventArgs e)

{ //Orders Table

string strQuery = "select Count(*) from Orders" ;

lblOrders.Text = countNumberOfRecords(strQuery).ToString();

}

protected void btnProducts_Click( object sender, EventArgs e)

{ //Products Table

string strQuery = "select Count(*) from Products" ;

lblProducts.Text = countNumberOfRecords(strQuery).ToString();

}

protected void btnRegion_Click( object sender, EventArgs e)

{ //Region Table

string strQuery = "select Count(*) from Region" ;

lblRegion.Text = countNumberOfRecords(strQuery).ToString();

}

protected void btnShippers_Click( object sender, EventArgs e)

{ //Shipper Table

string strQuery = "select Count(*) from Shippers" ;

lblShippers.Text = countNumberOfRecords(strQuery).ToString();

}

protected void btnSuppliers_Click( object sender, EventArgs e)

{ //Suppliers Table

string strQuery = "select Count(*) from Suppliers" ;

lblSuppliers.Text = countNumberOfRecords(strQuery).ToString();

}

protected void btnTerritories_Click( object sender, EventArgs e)

{ //Territories Table

string strQuery = "select Count(*) from Territories" ;

lblTerritories.Text = countNumberOfRecords(strQuery).ToString();

}

private int countNumberOfRecords( string sqlQuery){

int _noofRecords = 0;

try

{

string strConn = "Data Source=localhost;Initial Catalog= Northwind; user Id=sa;Password = test" ;

SqlConnection conn = new SqlConnection (strConn);

SqlCommand cmd = new SqlCommand ();

cmd.Connection = conn;

cmd.CommandText = sqlQuery;

cmd.CommandType = CommandType .Text;

conn.Open();

_noofRecords = ( int )cmd.ExecuteScalar();

conn.Close();

}catch ( Exception ex) {

Response.Write(ex.Message);

}

return _noofRecords;

}

Output:

Clicking every count button in front of the query:

Share This ...

Previous: Deleting Records from Database Execute Reader: Fetching Records from Database:Next

Give feedback and win a prize.

Aspnet Discussion

...more

Aspnet Source Code

...more

Aspnet Related Tutorials

- ASP Tutorial
- ASP.NET Tutorial

...more

New Aspnet Resources

...more

>>Earn money through freelance projects!

Database Tutorial Home

Database Home

ASP.NET Resources

Community

Site

Execute Scalar: Getting Single Value from the Database

Aspnet Discussion

Aspnet Source Code

Aspnet Related Tutorials

New Aspnet Resources

Member Panel