In this article I will explain with an example, how to use ADO.Net SqlCommand ExecuteNonQuery method in C# and VB.Net while developing ASP.Net, Windows and Console applications.
This article covers the usage, significance and syntax of the ADO.Net SqlCommand ExecuteNonQuery method in C# and VB.Net languages.
Database
I have made use of the following table Persons with the schema as follows.
![Using ADO.Net ExecuteNonQuery method in ASP.Net with examples in C# and VB.Net]()
Note: You can download the database table SQL by clicking the download link below.
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.SqlClient;
using System.Configuration;
VB.Net
Imports System.Data
Imports System.Data.SqlClient
Imports System.Configuration
INSERT
Initially, the connection string is fetched from the Web.Config file and a object of SqlConnection class is created using it.
Then an object of SqlCommand class is created and the INSERT query is passed to it as parameter.
The values of the name and country are added as parameter to SqlCommand object.
Finally, the connection is opened and the ExecuteNonQuery function is executed.
C#
string name = "Mudassar Khan";
string city = "Pune";
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("INSERT INTO Persons (Name, City) VALUES (@Name, @City)", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@City", city);
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();
}
}
VB.Net
Dim name As String = "Mudassar Khan"
Dim city As String = "Pune"
Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand("INSERT INTO Persons (Name, City) VALUES (@Name, @City)", con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@Name", name)
cmd.Parameters.AddWithValue("@City", city)
con.Open()
Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
![Using ADO.Net ExecuteNonQuery method in ASP.Net with examples in C# and VB.Net]()
UPDATE
Initially, the connection string is fetched from the App.Config file and a object of SqlConnection class is created using it.
Then an object of SqlCommand class is created and the UPDATE query is passed to it as parameter.
The values of the name and country are added as parameter to SqlCommand object.
Finally, the connection is opened and the ExecuteNonQuery function is executed.
C#
string name = "Mudassar Khan";
string city = "Pune";
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("UPDATE Persons SET City = @City WHERE Name = @Name", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@City", city);
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();
}
}
VB.Net
Dim name As String = "Mudassar Khan"
Dim city As String = "Pune"
Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand("UPDATE Persons SET City = @City WHERE Name = @Name", con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@Name", name)
cmd.Parameters.AddWithValue("@City", city)
con.Open()
Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
The screenshot below displays the rows affected.
![Using ADO.Net ExecuteNonQuery method in ASP.Net with examples in C# and VB.Net]()
DELETE
Initially, the connection string is fetched from the App.Config file and a object of SqlConnection class is created using it.
Then an object of SqlCommand class is created and the DELETE query is passed to it as parameter.
The values of the name and country are added as parameter to SqlCommand object.
Finally, the connection is opened and the ExecuteNonQuery function is executed.
C#
string name = "Mudassar Khan";
string constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("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 constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand("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.
![Using ADO.Net ExecuteNonQuery method in ASP.Net with examples 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 constring = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
using (SqlConnection con = new SqlConnection(constring))
{
using (SqlCommand cmd = new SqlCommand("SELECT * FROM Persons", con))
{
cmd.CommandType = CommandType.Text;
cmd.Parameters.AddWithValue("@Name", name);
cmd.Parameters.AddWithValue("@City", city);
con.Open();
int rowsAffected = cmd.ExecuteNonQuery();
con.Close();
}
}
VB.Net
Dim constring As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
Using con As New SqlConnection(constring)
Using cmd As New SqlCommand("SELECT * FROM Persons", con)
cmd.CommandType = CommandType.Text
cmd.Parameters.AddWithValue("@Name", name)
cmd.Parameters.AddWithValue("@City", city)
con.Open()
Dim rowsAffected As Integer = cmd.ExecuteNonQuery()
con.Close()
End Using
End Using
The screenshot below displays the rows affected returned -1.
![Using ADO.Net ExecuteNonQuery method in ASP.Net with examples in C# and VB.Net]()
Thus concluding it, we must use ExecuteNonQuery for INSERT, UPDATE and DELETE operations only.
