Execute Scalar: Getting Single Value from the Database

Home > Aspnet > Tutorials > Execute Scalar: Getting Single Value from the Database - ASP.NET Database Tutorial

Tell a friend

Link to us

Total Members

Members: 84657

Sitemap

Forum

Chat

Home

ASP.NET Database Tutorial Home

1 . Introduction to Asp.Net 2.0

2 . Introduction to ADO.Net

3 . Connecting Access Database with AccessDataSource and GridView Control

4 . Accessing SQL SERVER Database [Using SqlDataSource]

5 . Storing Connection String in Web.Config File

6 . Inserting Records in Database

7 . Updating Records in Database

8 . Updating Records in Database

9 . Deleting Records from Database

10 . Execute Scalar: Getting Single Value from the Database

11 . Execute Reader: Fetching Records from Database

12 . Data Adapter

13 . Data Adapter and Database Connection

14 . Performing Batch Updates: Using SqlCommandBuilder

15 . Improving Performance using Stored Procedure

16 . Filtering and Sorting Data Using DataView/DataTable

17 . Data Binding and Data Synchronization

18 . SQL Transaction and Locking

19 . SQL Joins and .Net

20 . Automatically Generation of SQL Statement

Aspnet Group Home

Aspnet Discussion (10)

Aspnet Members (2382)

Aspnet Resources

Aspnet Source Code (388)

Aspnet Articles (1)

Aspnet Blogs

Aspnet Jobs

Aspnet Components (201)

- Aspnet Ask Question

- Aspnet Questions

- Aspnet Unanswered Questions

GROUPS

.NET

Java

Other

TUTORIALS

.NET

.NET Tutorial

ASP Tutorial

ASP.NET Database Tutorial

ASP.NET Development Tips

ASP.Net Security,Internationalisation And Deployment

ASP.NET Server Controls Tips

Web Development

Software Development

Java

Java Web Component Tutorial

JSP And J2EE Design Tutorial

RESOURCES

SUBMISSIONS

COMMUNITY

SITE

Aspnet database Tutorial

Execute Scalar: Getting Single Value from the Database

<< Prev: Deleting Records from Database

Next: Execute Reader: Fetching Records from Database >>

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:

<< Prev: Deleting Records from Database	Next: Execute Reader: Fetching Records from Database >>
Aspnet Database Tutorial Home
Give feedback and win a prize.