In this article I will explain with an example, how to use
ExecuteScalar method of
Dapper library in C# and VB.Net while developing ASP.Net, Windows and Console applications.
Installing Dapper package using Nuget
Database
I have made use of the following table Customers with the schema as follows.
![Understanding Dapper ExecuteScalar in C# and VB.Net]()
I have already inserted few records in the table.
![Understanding Dapper ExecuteScalar in C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
The ExecuteScalar method
ExecuteScalar is a handy function when you want to just need one Cell value i.e. one column and one row.
Preferred use will be operations, where one Cell value i.e. one column and one row is returned from the SQL Query or
Stored Procedure.
Namespaces
You will need to import the following namespaces.
C#
using Dapper;
using System.Data.SqlClient;
using System.Configuration;
VB.Net
Imports Dapper
Imports System.Data.SqlClient
Imports System.Configuration
What happens when I use ExecuteScalar for SELECT statement with multiple rows?
Initially, the connection string is fetched from the App.Config or Web.Config file and object of SqlConnection class is created using it.
Then, the SELECT query is passed to ExecuteScalar method as parameter.
The value of the name is also added as parameter to
ExecuteScalar method of
Dapper library.
Finally, the value of the Country is returned.
C#
string name = "Mudassar Khan";
string sql = "SELECT Country FROM Customers WHERE Name=@Name";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
object o = con.ExecuteScalar(sql, new { name });
if (o != null)
{
string country = o.ToString();
}
}
VB.Net
Dim name As String = "Mudassar Khan"
Dim sql As String = "SELECT Country FROM Customers WHERE Name=@Name"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Dim o As Object = con.ExecuteScalar(sql, New With { name })
If o IsNot Nothing Then
Dim country As String = o.ToString()
End If
End Using
The screenshot below displays the value of the column returned.
![Understanding Dapper ExecuteScalar in C# and VB.Net]()
What happens when I use ExecuteScalar for SELECT statement with multiple columns and multiple rows?
This is a great question and the answer is yes you can use it, but as its behavior it will return the very first cell i.e. first row and first column.
C#
string name = "Mudassar Khan";
string sql = "SELECT Name, Country FROM Customers WHERE Name=@Name";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
object o = con.ExecuteScalar(sql, new { name });
if (o != null)
{
string country = o.ToString();
}
}
VB.Net
Dim name As String = "Mudassar Khan"
Dim sql As String = "SELECT Name, Country FROM Customers WHERE Name=@Name"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Dim o As Object = con.ExecuteScalar(sql, New With { name })
If o IsNot Nothing Then
Dim country As String = o.ToString()
End If
End Using
The screenshot below displays the value of the first cell i.e. first row and first column being returned.
![Understanding Dapper ExecuteScalar in C# and VB.Net]()
Can we use ExecuteScalar for INSERT, UPDATE and DELETE Statements?
Yes you can. But since INSERT, UPDATE and DELETE Statements return no value, you will not get any value returned from the Query as well as you will not get the Rows Affected like you get in Execute function.
C#
string name = "Mudassar Khan";
string country = "India";
string sql = "UPDATE Customers SET Country = @Country WHERE Name = @Name";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
object o = con.ExecuteScalar(sql, new { name, country });
}
VB.Net
Dim name As String = "Mudassar Khan"
Dim country As String = "India"
Dim sql As String = "UPDATE Customers SET Country = @Country WHERE Name = @Name"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Dim o As Object = con.ExecuteScalar(sql, New With { name, country })
End Using
The screenshot below displays the returned value being returned as NULL since there’s nothing returned from the UPDATE query.
![Understanding Dapper ExecuteScalar in C# and VB.Net]()
Another case would be, when you have an Identity (Auto Increment) column and you need to get the value of the ID column newly added record.
If you want to return a value, then you can combine an INSERT, UPDATE and DELETE Statement with a SELECT statement and then the ExecuteScalar function will return value. For example, in the below code, the ID of the new record that was inserted is fetched using SCOPE_IDENTITY.
C#
int customerId;
string sql = "INSERT INTO Customers (Name, Country) VALUES (@Name, @Country)";
sql += " SELECT SCOPE_IDENTITY()";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constr))
{
string name = txtName.Text;
string country = ddlCountries.SelectedItem.ToString();
customerId = Convert.ToInt32(con.ExecuteScalar(sql, new { name, country }));
}
VB.Net
Dim customerId As Integer
Dim sql As String = "INSERT INTO Customers (Name, Country) VALUES (@Name, @Country)"
sql += " SELECT SCOPE_IDENTITY()"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As SqlConnection = New SqlConnection(constr)
Dim name As String = txtName.Text
Dim country As String = ddlCountries.SelectedItem.ToString()
customerId = Convert.ToInt32(con.ExecuteScalar(sql, New With { name, country }))
End Using
![Understanding Dapper ExecuteScalar in C# and VB.Net]()
Thus, concluding it, we must use ExecuteScalar to fetch a single cell (Scalar) value.
