In my previous tutorials, I’d explained about ExecuteReader, ExecuteNonQuery and Difference between ExecuteReader, ExecuteNonQuery, ExecuteScalar and other more cracking tutorials on Asp.net here.
Now here in this tutorial, I’ll explain Ado.net ExecuteScalar in detail with example code.
The SqlCommand ExecuteScalar in Ado.net returns first column’s first cell value of the first row from the resultset returned by the sql query or stored procedure, meaning return only single value and additional columns or rows are ignored.
ExecuteScalar will return null reference if resultset is empty, and can returns a maximum of 2033 characters.
It’s better to use when we need to retrieve resultset as aggregate functions like SUM(), COUNT() etc.
To explain further about execute scalar example, we need to create database table to read data and bind retrieved result to asp.net gridview, so simply execute following script to sql query editor to create database table and then add few records manually or download from below complete example with script.
Here is the script to create “SubjectDetails” table:
CREATE TABLE [dbo].[SubjectDetails]
(
[SubjectId] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
[SubjectName] [nvarchar](100) NULL
)
Ado.net SqlCommand ExecuteScalar Example in .NET – [.aspx]
Following is the complete HTML Markup for your .aspx page:
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Ado.net - ExecuteScalar Example in C# .NET</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<table>
<tr>
<td colspan="3">
<h4>SqlCommand ExecuteScalar in ado.net example</h4>
</td>
</tr>
<tr>
<td colspan="3">
<asp:GridView ID="gvSubjectDetails" runat="server" AutoGenerateColumns="false"
DataKeyNames="SubjectID">
<HeaderStyle Font-Bold="true" BackColor="#ff6600" BorderColor="#f5f5f5"
ForeColor="White" Height="30" />
<Columns>
<asp:BoundField DataField="SubjectID" HeaderText="Subject Id"
ItemStyle-Width="150px" />
<asp:BoundField DataField="SubjectName" HeaderText="Subject Name"
ItemStyle-Width="200px" />
</Columns>
</asp:GridView>
</td>
</tr>
<tr><td colspan="3"> </td></tr>
<tr>
<td colspan="3">
<asp:Button ID="btnGetValue" runat="server" Text="Get Scalar Value"
OnClick="btnGetValue_Click" />
</td>
</tr>
<tr><td colspan="3"> </td></tr>
<tr>
<td>
<b>ExecuteScalar Value:</b>
</td>
<td colspan="2">
<asp:Label ID="lblScalarValue" runat="server"></asp:Label>
</td>
</tr>
</table>
</div>
</form>
</body>
</html>
Now check the below sample code snippet that show you a simple executescalar example:
Ado.net SqlCommand ExecuteScalar Example – [C#]
//specify your connection string here..
string strConn = "Data Source=datasource;Integrated Security=true;Initial Catalog=yourDB";
protected void Page_Load(object sender, EventArgs e) {
if (!IsPostBack) {
BindGridviewFileData();
}
}
//button click event to get the scalar value
protected void btnGetValue_Click(object sender, EventArgs e) {
try {
using (SqlConnection sqlConn = new SqlConnection(strConn)) {
using (SqlCommand sqlCmd = new SqlCommand()) {
sqlCmd.CommandText = "SELECT * FROM SubjectDetails";
sqlCmd.Connection = sqlConn;
sqlConn.Open();
//here execute scalar will get first row's first column value
lblScalarValue.Text = Convert.ToString(sqlCmd.ExecuteScalar());
if (string.IsNullOrEmpty(lblScalarValue.Text)) {
lblScalarValue.Text = "No record found!";
}
sqlConn.Close();
}
}
} catch { }
}
//bind subject details to gridview
private void BindGridviewFileData() {
try {
using (SqlConnection sqlConn = new SqlConnection(strConn)) {
using (SqlCommand sqlCmd = new SqlCommand()) {
sqlCmd.CommandText = "SELECT * FROM SubjectDetails";
sqlCmd.Connection = sqlConn;
sqlConn.Open();
gvSubjectDetails.DataSource = sqlCmd.ExecuteReader();
gvSubjectDetails.DataBind();
sqlConn.Close();
}
}
} catch { }
}
Ado.net SqlCommand ExecuteScalar Example – [Vb.net]
'specify your connection string here..'
Dim strConn As String = "Data Source=datasource;Integrated Security=true;Initial Catalog=yourDB"
Protected Sub Page_Load(ByVal sender As Object, ByVal e As EventArgs) Handles Me.Load
If Not IsPostBack Then
BindGridviewFileData()
End If
End Sub
'button click event to get the scalar value'
Protected Sub btnGetValue_Click(ByVal sender As Object, ByVal e As EventArgs)
Using sqlConn As New SqlConnection(strConn)
Using sqlCmd As New SqlCommand()
sqlCmd.CommandText = "SELECT * FROM SubjectDetails"
sqlCmd.Connection = sqlConn
sqlConn.Open()
'here execute scalar will get first column's first cell value'
lblScalarValue.Text = Convert.ToString(sqlCmd.ExecuteScalar())
If String.IsNullOrEmpty(lblScalarValue.Text) Then
lblScalarValue.Text = "No record found!"
End If
sqlConn.Close()
End Using
End Using
End Sub
'bind subject details to gridview'
Private Sub BindGridviewFileData()
Try
Using sqlConn As New SqlConnection(strConn)
Using sqlCmd As New SqlCommand()
sqlCmd.CommandText = "SELECT * FROM SubjectDetails"
sqlCmd.Connection = sqlConn
sqlConn.Open()
gvSubjectDetails.DataSource = sqlCmd.ExecuteReader()
gvSubjectDetails.DataBind()
sqlConn.Close()
End Using
End Using
Catch
End Try
End Sub
Example Result
Download Example
Git Repo
That’s it, this is simplest way to understand ado.net sqlcommand executescalar in .net using c# or vb.net.
Let me know if you’ve any questions or doubts about this tutorial by writing down your queries in comment below. I would be happy to provide you my feedback as soon as possible.
Happy coding!
