Table of Contents

This article will learn how to use the ExecuteNonQuery method to Insert, update, and delete data in C#. To begin with, what is ExecuteNonQuery, and which namespace has its definitions?

C# ExecuteNonQuery Method

ExecuteNonQuery is a Method that executes a SQL statement against a connection. The namespace System.Data.SqlClient contains the ExecuteNonQuery method. After the method execution, an integer value is returned, indicating how many records were affected. For example, if you use this method to delete records, it returns how many records were deleted by executing the SQL statement.

Using the ExecuteNonQuery Method to Delete in C#

The first example of ExecuteNonQuery is its usage to delete records from a SQL database. The first thing you will need to do before using the ExecuteNonQuery is adding the namespaces below:

using System.Data;
using System.Data.SqlClient;

Let’s look at the example below:

public int DeleteBook(string isbn)
{

    string strConnection = "Data Source=CodingPanel-PC\\SQLEXPRESS; Initial Catalog=BooksDB;Integrated Security=True;";
    using (SqlConnection conn = new SqlConnection(strConnection))
    {
        // DELETE SQL command
        using (SqlCommand cmd = new SqlCommand("DELETE FROM Books WHERE ISBN =" + isbn, conn))
        {
            cmd.CommandType = CommandType.Text;

            // Pass the isbn as a parameter
            cmd.Parameters.AddWithValue("@ISBN", isbn);

            // Open the connection
            conn.Open();

            // Exucute the command using ExecuteNonQuery()
            int i = cmd.ExecuteNonQuery();

            // Close the connection
            conn.Close();

            // Return the number of deleted rows
            return i;
        }
    }
}

[adinserter block=”2″] Now call this function and pass the ISBN of the book that you would like to delete:

int i;
i = DeleteBook("987-6-54-321012-3");
if (i == 0)
{
    Console.WriteLine("Book not found");
}
else
{
    Console.WriteLine("Book has been deleted.");
}

Using the ExecuteNonQuery Method to Insert in C#

Now let’s use the ExecuteNonQuery Method to Insert data into the Books table. The function below inserts a book into a database by using the ExecuteNonQuery method. If the function runs successfully, it should return 1, as we are inserting 1 book at a time.

public int InsertBook(string isbn, string bookTitle, string author)
{

    string strConnection = "Data Source=CodingPanel-PC\\SQLEXPRESS; Initial Catalog=BooksDB;Integrated Security=True;";
    using (SqlConnection conn = new SqlConnection(strConnection))
    {
        // Insert SQL command
        using (SqlCommand cmd = new SqlCommand("INSERT INTO Books (ISBN, BookTitle, Author) VALUES (@isbn, @BookTitle, @Author)", conn))
        {
            cmd.CommandType = CommandType.Text;

            // Add parameters
            cmd.Parameters.AddWithValue("@isbn", isbn);
            cmd.Parameters.AddWithValue("@Booktitle", bookTitle);
            cmd.Parameters.AddWithValue("@Author", author);

            // Open the connection
            conn.Open();

            // Exucute the command using ExecuteNonQuery()
            int i = cmd.ExecuteNonQuery();

            // Close the connection
            conn.Close();

            // Return the number of deleted rows
            return i;
        }
    }
}

Run the function:

int i;
i = InsertBook("987-6-54-321012-3", "Learn Programming", "CodingPanel.com");
if (i == 0)
{
    Console.WriteLine("Nothing inserted");
}
else
{
    Console.WriteLine("Book has been inserted.");
}

Using the ExecuteNonQuery Method to Update in C#

The last example of this article is to update a record in the database using the ExecuteNonQuery method. The function below takes the book information and updates the Books table.
[adinserter block=”2″]

public int UpdateBook(string isbn, string bookTitle, string author)
{

    string strConnection = "Data Source=CodingPanel-PC\\SQLEXPRESS; Initial Catalog=BooksDB;Integrated Security=True;";
   
    using (SqlConnection conn = new SqlConnection(strConnection))
    {
        // Update SQL command
        using (SqlCommand cmd = new SqlCommand("UPDATE Books SET BookTitle = @BookTitle, Author=@Author WHERE ISBN =" + isbn, conn))
        {
            cmd.CommandType = CommandType.Text;

            // PAdd parameters
            cmd.Parameters.AddWithValue("@ISBN", bookTitle);
            cmd.Parameters.AddWithValue("@Author", author);

            // Open the connection
            conn.Open();

            // Exucute the command using ExecuteNonQuery()
            int i = cmd.ExecuteNonQuery();

            // Close the connection
            conn.Close();

            // Return the number of deleted rows
            return i;
        }
    }
}

Now call the method:

int i;
i = UpdateBook("987-6-54-321012-3", "Learn C# Programming", "CodingPanel.com");
if (i == 0)
{
        
    Console.WriteLine("Nothing updated");
}
else
{
    Console.WriteLine("Book has been updated.");
}

Happy coding!

Post Views: 1,059