ExecuteNonQuery: Get number of rows affected during Insert, Update and Delete in C# and VB.Net

In this article I will explain with example how to get number of rows affected during Insert, Update or Delete operations with ADO.Net SqlCommand ExecuteNonQuery method in C# and VB.Net.

This article explains how to fetch the number of rows affected returned from the ExecuteNonQuery method in C# and VB.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.SqlClient;

using System.Configuration;

 

VB.Net

Imports System.Data

Imports System.Data.SqlClient

Imports System.Configuration

Get number of rows affected (inserted) during Insert operation.

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

 

Get number of rows affected (updated) during Update operation.

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.

Get number of rows affected (deleted) during Delete operation.

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.