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
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();
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();
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();
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)");