Insert Update Delete example in C# and VB.NET Using ExecuteNonQuery method

ExecuteNonQuery is a method from the SQlCommand Class in the System.Data.SqlClient namespace. It executes a T-SQL query and returns the number of rows affected. Below are examples of how to run an Insert, Delete, and Update statements using the ExecuteNonQuery method in both C# and VB.NET.

Required namespaces
C#

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

VB.NET

Imports System.Data
Imports System.Data.SqlClient

Insert

The insert function below will return the integer 1, as one row will be inserted to the database:

C#

 protected int InsertEmployee()

        {
            string _name = "Mike";
            string _departmenmt = "Engineering";
            double _salary = 2000;
            string connetionString = "Data Source=TutorialsPanel-PC\SQLEXPRESS; Initial Catalog=TutorialsPanel;Integrated Security=True;";
         
            using (SqlConnection conn = new SqlConnection(connetionString))
            {
                using (SqlCommand cmd = new SqlCommand("INSERT INTO Employees (Name, Department, Salary) VALUES (@Name, @Department, @Salary)", conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@Name", _name);
                    cmd.Parameters.AddWithValue("@Department", _departmenmt);
                    cmd.Parameters.AddWithValue("@Salary", _salary);
                    conn.Open();
                    int i = cmd.ExecuteNonQuery();
                    conn.Close();
                    return i;
                }
            }
        }

VB.NET

Protected Function InsertEmployee() As Integer

	Dim _name As String = "Mike"
	Dim _departmenmt As String = "Engineering"
	Dim _salary As Double = 2000
	Dim connetionString As String = "Data Source=TutorialsPanel-PC\SQLEXPRESS; Initial Catalog=TutorialsPanel;Integrated Security=True;"

	Using conn As New SqlConnection(connetionString)
		Using cmd As New SqlCommand("INSERT INTO Employees (Name, Department, Salary) VALUES (@Name, @Department, @Salary)", conn)
			cmd.CommandType = CommandType.Text
			cmd.Parameters.AddWithValue("@Name", _name)
			cmd.Parameters.AddWithValue("@City", _departmenmt)
			cmd.Parameters.AddWithValue("@City", _salary)
			conn.Open()
			Dim i As Integer = cmd.ExecuteNonQuery()
			conn.Close()
			Return i
		End Using
	End Using
End Function

Delete

The delete function below will return the affected row, which is 1.

C#

        protected int DeleteEmployee()
        {
            string _name = "Mike";
            string connetionString = "Data Source=TutorialsPanel-PC\SQLEXPRESS; Initial Catalog=TutorialsPanel;Integrated Security=True;";
            using (SqlConnection conn = new SqlConnection(connetionString))
            {
                using (SqlCommand cmd = new SqlCommand("DELETE FROM Persons WHERE Name = @Name", conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@Name", _name);
                    conn.Open();
                    int i = cmd.ExecuteNonQuery();
                    conn.Close();
                    return i;
                }
            }
        }

VB.NET

Protected Function DeleteEmployee() As Integer
	Dim _name As String = "Mike"
	Dim connetionString As String = "Data Source=TutorialsPanel-PC\SQLEXPRESS; Initial Catalog=TutorialsPanel;Integrated Security=True;"
	Using conn As New SqlConnection(connetionString)
		Using cmd As New SqlCommand("DELETE FROM Employees WHERE Name = @Name", conn)
			cmd.CommandType = CommandType.Text
			cmd.Parameters.AddWithValue("@Name", _name)
			conn.Open()
			Dim i As Integer = cmd.ExecuteNonQuery()
			conn.Close()
			Return i
		End Using
	End Using
End Function

Update

C#

 protected int UpdateEmployee()
        {
            string _name = "Mike";
            string _departmenmt = "IT";
            double _salary = 2500;
            string connetionString = "Data Source=TutorialsPanel-PC\SQLEXPRESS; Initial Catalog=TutorialsPanel;Integrated Security=True;";
            using (SqlConnection conn = new SqlConnection(connetionString))
            {
                using (SqlCommand cmd = new SqlCommand("UPDATE Employees SET Department = @Department, Salary = @Salary WHERE Name = @Name", conn))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@Name", _name);
                    cmd.Parameters.AddWithValue("@Department", _departmenmt);
                    cmd.Parameters.AddWithValue("@Salary", _salary);
                    conn.Open();
                    int i = cmd.ExecuteNonQuery();
                    conn.Close();
                    return i;
                }
            }

        }

VB.NET

Protected Function UpdateEmployee() As Integer
	Dim _name As String = "Mike"
	Dim _departmenmt As String = "IT"
	Dim _salary As Double = 2500
	Dim connetionString As String = "Data Source=TutorialsPanel-PC\SQLEXPRESS; Initial Catalog=TutorialsPanel;Integrated Security=True;"
	Using conn As New SqlConnection(connetionString)
		Using cmd As New SqlCommand("UPDATE Employees SET Department = @Department, Salary = @Salary WHERE Name = @Name", conn)
			cmd.CommandType = CommandType.Text
			cmd.Parameters.AddWithValue("@Name", _name)
			cmd.Parameters.AddWithValue("@Department", _departmenmt)
			cmd.Parameters.AddWithValue("@Salary", _salary)
			conn.Open()
			Dim i As Integer = cmd.ExecuteNonQuery()
			conn.Close()
			Return i
		End Using
	End Using

End Function

Related Articles: