In this article I will explain with examples, how to use MySqlCommand ExecuteNonQuery 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 ExecuteNonQuery method
ExecuteNonQuery is basically used for operations where there is nothing returned from the SQL Query or
Stored Procedure Preferred use will be for INSERT, UPDATE and DELETE Operations.
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
INSERT
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 INSERT query is passed to it as parameter and the values of the name and country are added as parameter to MySqlCommand object.
Finally, the connection is opened and the ExecuteNonQuery function is executed.
C#
string name = "Mudassar Khan";
string country = "India";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand("INSERT INTO Persons (Name, Country) VALUES (@Name, @Country)", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@Country", country);
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();
}
}
VB.Net
Dim name As String = "Mudassar Khan"
Dim country As String = "India"
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As MySqlConnection = New MySqlConnection(constr)
Using cmd As MySqlCommand = New MySqlCommand("INSERT INTO Persons (Name, Country) VALUES (@Name, @Country)", con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@Name", name)
cmd.Parameters.AddWithValue("@Country", country)
con.Open()
Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
![Understanding MySqlCommand ExecuteNonQuery in C# and VB.Net]()
UPDATE
Initially, the connection string is fetched from the App.Config or Web.Config file and an object of MySqlConnection class is created using it.
Then, an object of MySqlCommand class is created and the UPDATE query is passed to it as parameter and the values of the name and country are added as parameter to MySqlCommand object.
Finally, the connection is opened and the ExecuteNonQuery function is executed.
C#
string name = "Mudassar Khan";
string country = "India";
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();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();
}
}
VB.Net
Dim name As String = "Mudassar Khan"
Dim country As String = "India"
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 rowsAffected As Integer = cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
The screenshot below displays the rows affected.
![Understanding MySqlCommand ExecuteNonQuery in C# and VB.Net]()
DELETE
Initially, the connection string is fetched from the App.Config or Web.Config file and an object of MySqlConnection class is created using it.
Then, an object of MySqlCommand class is created and the DELETE query is passed to it as parameter and the values of the name and country are added as parameter to MySqlCommand object.
Finally, the connection is opened and the ExecuteNonQuery function is executed.
C#
string name = "Mudassar Khan";
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand("DELETE FROM Persons WHERE Name = @Name", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", name);
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
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("DELETE FROM Persons WHERE Name = @Name", con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@Name", name)
con.Open()
Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
The screenshot below displays the rows affected.
![Understanding MySqlCommand ExecuteNonQuery in C# and VB.Net]()
What happens when I use ExecuteNonQuery for SELECT statement?
ExecuteNonQuery will work flawlessly for SELECT SQL Query or Stored Procedure but that will simply execute the query and do nothing. Even if you use it you will not throw any error but the Rows Affected will be negative i.e. -1.
C#
string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (MySqlConnection con = new MySqlConnection(constr))
{
using (MySqlCommand cmd = new MySqlCommand("SELECT * FROM Persons", con))
{
cmd.CommandType = CommandType.Text;
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();
}
}
VB.Net
Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As MySqlConnection = New MySqlConnection(constr)
Using cmd As MySqlCommand = New MySqlCommand("SELECT * FROM Persons", con)
cmd.CommandType = CommandType.Text
con.Open()
Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
The screenshot below displays the rows affected returned -1.
![Understanding MySqlCommand ExecuteNonQuery in C# and VB.Net]()
Thus concluding it, we must use ExecuteNonQuery for INSERT, UPDATE and DELETE operations only.
