Jitesh Byahut 11/22/2014 9283

ExecuteNonQuery In ADO.Net

ExecuteNonQuery execute command specified and returns the number of affected rows. Its return type is integer. It's very useful to execute insert, update and delete queries. Its returns total number of rows that are affected by specified query. It does not return any row or column values.

ExecuteNonQuery is method of Command class. For using this you have to specify below namespaces so that you can connect sql server.

// Namespace

using System.Data;

using System.Data.SqlClient;

After specify namespaces, create SqlConnection object, specify connection string and open the connection. Create SqlCommand object and set connection string, command type and command text. After that use ExecuteNonQuery method to execute the query and after this close the connection. Make sure you open the connection before using ExecuteNonQuery.

Below example demonstrate that steps to use of ExecuteNonQuery:

// Create SqlConnection object

SqlConnection con = new SqlConnection();

// Specify connection string

con.ConnectionString = "Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;";

// Open Connection

con.Open();

// Create SqlCommand object

SqlCommand cmd = new SqlCommand();

// Specify command connection

cmd.Connection = con;       

// Set commant type means query, stored procedure or table direct

cmd.CommandType = CommandType.Text;

// Sql query specify

cmd.CommandText = "Insert into tblTest(Name,Points) Values('Jitesh', 100)";

// Execute command

cmd.ExecuteNonQuery();

// Close connection

con.Close();

Get return value from ExecuteNonQuery

ExecuteNonQuery returns integer value that indicate that total row affected by specified command. So you can check return integer value that your command fully executed. For example in below example we are executing 5 rows update command and we can check that how many rows are executed using the return value.

SqlConnection con = new SqlConnection("Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;");

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

con.Open();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "update tblTest set Points=80 where Id in (2,4,6,7,9)";

int retval = cmd.ExecuteNonQuery();

con.Close();

ExecuteNonQuery for OleDb

In above examples we seen sql server database command execution, now we are using ExecuteNonQuery for OleDb database command.

For OleDb connection, you have to specify below namespaces:

// Namespace

using System.Data;

using System.Data.OleDb;

After specify namespaces, all the steps are same as sql command object, just difference is to use OleDb command object instead of Sql.

 

OleDbConnection con = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\dataaccess.mdb;Persist Security Info=False");

OleDbCommand cmd = new OleDbCommand();

cmd.Connection = con;

con.Open();

cmd.CommandType = CommandType.Text;

cmd.CommandText = "Insert into tblTest(Name,Points) Values('Jitesh', 100)";

cmd.ExecuteNonQuery();

con.Close();

ExecuteNonQuery for Stored Procedure

You can also use ExecuteNonQuery for executing stored procedure. For stored procedure execution, you have to specify command type and pass stored procedure name in command text. If you want to pass parameter then use Parameters.AddWithValue that holds parameter name and that value.

SqlConnection con = new SqlConnection("Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;");

SqlCommand cmd = new SqlCommand();

cmd.Connection = con;

con.Open();

cmd.CommandType = CommandType.StoredProcedure;

cmd.CommandText = "sp_test";

cmd.Parameters.AddWithValue("@Name", "Jitesh");

cmd.Parameters.AddWithValue("@Ponits", 100);

cmd.ExecuteNonQuery();

con.Close();

Standard ExecuteNonQuery Function

If you are using ADO.Net frequently in your project then create a class for ADO.Net that contains all function that executing commands. Below is example of that class:

using System.Data;

using System.Data.SqlClient;

 

public class SqlDb

{

    SqlConnection con = new 

               SqlConnection("Data Source=JITESH-PC; Database=db_Test; User Id=sa; Password=12345;");

 

    public int ExecuteNonQuery(string sqlQuery)

    {       

        con.Open();

        SqlCommand cmd = new SqlCommand(sqlQuery, con);

        int retval = cmd.ExecuteNonQuery();

        con.Close();

        return retval;

    }

}

Once you created above class, then it's very easy to execute sql query from anywhere in your application. Best things about this is to no need of write to much of codes in page behind. Below example demonstrate that, how to use above class function:

SqlDb objDb = new SqlDb();

objDb.ExecuteNonQuery("update tblTest set Points=80 where Id in (2,4,6,7,9)");



Please give your feedback for improving this page