In this article I will explain with example, how to use MySqlCommand ExecuteScalar method in C# and VB.Net while developing ASP.Net, Windows and Console applications.
Download and Install the MySQL Connector
You will need to download and install the
MySQL Connector in order to connect to the
MySQL database in ASP.Net.
The ExecuteScalar method
ExecuteScalar is a handy function when you want to just need one Cell value i.e. one column and one row.
ExecuteScalaris basically used for operations where there is nothing returned from the SQL Query or
Stored Procedure. Preferred use will be operations where one Cell value i.e. one column and one row is returned.
Namespaces
You will need to import the following namespaces.
C#
using System.Data;
using System.Data.MySqlClient;
using System.Configuration;
VB.Net
Imports System.Data
Imports System.Data.MySqlClient
Imports System.Configuration
What happens when I use ExecuteScalar for SELECT statement with multiple columns and multiple rows?
Initially, the connection string is fetched from the App.Config or Web.Config file and object of MySqlConnection class is created using it.
Then, an object of MySqlCommand class is created and the SELECT query is passed to it as parameter.
The value of the name is added as parameter to MySqlCommand object.
Finally, the connection is opened and the ExecuteScalar function is executed and the value of the Country is returned.
C#
string name = "Mudassar Khan";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand("SELECT Country FROM Persons WHERE Name=@Name", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", name);
con.Open();
object o = cmd.ExecuteScalar();
if (o != null)
{
string country = o.ToString();
}
con.Close();
}
}
VB.Net
Dim name As String = "Mudassar Khan"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As MySqlConnection = New MySqlConnection(constr)
Using cmd As MySqlCommand = New MySqlCommand("SELECT Country FROM Persons WHERE Name=@Name", con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@Name", name)
con.Open()
Dim o As Object = cmd.ExecuteScalar()
If o IsNot Nothing Then
Dim country As String = o.ToString()
End If
con.Close()
End Using
End Using
The screenshot below displays the value of the column returned.
![Understanding MySqlCommand 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";
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand("SELECT Name, Country FROM Persons WHERE Name=@Name", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", name);
con.Open();
object o = cmd.ExecuteScalar();
if (o != null)
{
string country = o.ToString();
}
con.Close();
}
}
VB.Net
Dim name As String = "Mudassar Khan"
Using con As MySqlConnection = New MySqlConnection(constr)
Using cmd As MySqlCommand = New MySqlCommand("SELECT Name, Country FROM Persons WHERE Name=@Name", con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@Name", name)
con.Open()
Dim o As Object = cmd.ExecuteScalar()
If o IsNot Nothing Then
Dim country As String = o.ToString()
End If
con.Close()
End Using
End Using
The screenshot below displays the value of the first cell i.e. first row and first column being returned.
![Understanding MySqlCommand 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 ExecuteNonQuery function.
string name = "Mudassar Khan";
string country = "Canada";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand("UPDATE Persons SET Country = @Country WHERE Name = @Name", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@Country", country);
con.Open();
object o = cmd.ExecuteScalar();
con.Close();
}
}
VB.Net
Dim name As String = "Mudassar Khan"
Dim country As String = "Canada"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As MySqlConnection = New MySqlConnection(constr)
Using cmd As MySqlCommand = New MySqlCommand("UPDATE Persons SET Country = @Country WHERE Name = @Name", con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@Name", name)
cmd.Parameters.AddWithValue("@Country", country)
con.Open()
Dim o As Object = cmd.ExecuteScalar()
con.Close()
End Using
End Using
The screenshot below displays the returned value being returned as NULL since there’s nothing returned from the UPDATE query.
![Understanding MySqlCommand 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 LAST_INSERT_ID.
int personId;
string name = "Mudassar Khan";
string country = "India";
string query = "INSERT INTO Persons(Name, Country) VALUES(@Name, @Country);";
query += " SELECT SCOPE_IDENTITY();";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand(query))
{
cmd.Parameters.AddWithValue("@Name", txtName.Text);
cmd.Parameters.AddWithValue("@Country", ddlCountries.SelectedItem.Text);
cmd.Connection = con;
con.Open();
personId = Convert.ToInt32(cmd.ExecuteScalar());
con.Close();
}
}
VB.Net
Dim personId As Integer
Dim name As String = "Mudassar Khan"
Dim country As String = "India"
Dim query As String = "INSERT INTO Persons(Name, Country) VALUES(@Name, @Country);"
query += "SELECT LAST_INSERT_ID();"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As MySqlConnection = New MySqlConnection(constr)
Using cmd As MySqlCommand = New MySqlCommand(query)
cmd.Parameters.AddWithValue("@Name", name)
cmd.Parameters.AddWithValue("@Country", scountry)
cmd.Connection = con
con.Open()
personId = Convert.ToInt32(cmd.ExecuteScalar())
con.Close()
End Using
End Using
![Understanding MySqlCommand ExecuteScalar in C# and VB.Net]()
Thus concluding it, we must use ExecuteScalar to fetch a single cell (Scalar) value.
