Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery in asp .net

This asp .net tutorial describes about differences between ExecuteNonQuery, ExecuteScalar and ExecuteReader methods in asp .net with example and sample code. ExecuteNonQuery, ExecuteScalar and ExecuteReader are most commonly used methods of command object in ADO .Net. All these methods have one common feature: They all used to executes SQL statements but still they have some differences.

ExecuteNonQuery

ExecuteNonQuery is used to execute SQL Statement and it returns number of rows affected. We cannot use ExecuteNonQuery while we are expecting some data from the SQL query. We can use ExecuteNonQuery in INSERT, UPDATE AND DELETE queries where they are not returning any result.

ExecuteNonQuery Single Row effected example:

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
        string cmdText = "INSERT INTO employee (Name,Email,Age) VALUES (@Name,@Email,@Age)";
        SqlCommand cmd = new SqlCommand(cmdText, con);
        cmd.Parameters.AddWithValue("@Name", "Test");
        cmd.Parameters.AddWithValue("@Email", "Test@gmail.com");
        cmd.Parameters.AddWithValue("@Age", 30);
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        int result = cmd.ExecuteNonQuery();
        lblResult.Text = "Total rows effected: " + result.ToString();
        con.Close();
    
Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery in asp .net
In this case result is 1 because only single row is inserted into database.

ExecuteNonQuery Multiple Rows effected example:

       SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
        string cmdText = "DELETE FROM employee where iD IN(SELECT TOP 3 ID FROM employee)";
        SqlCommand cmd = new SqlCommand(cmdText, con);
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        int result = cmd.ExecuteNonQuery();
        lblResult.Text = "Total rows effected: " + result.ToString();
        con.Close();
    
Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery in asp .net
In this case result is 3 because 3 rows are effected in database.

ExecuteScalar

ExecuteScalar is used to execute SQL statement and it returns first column of the first row in the result returned by the query. All the other columns are ignored. We can use ExecuteScalar in INSERT, UPDATE AND DELETE queries but it is best used where we are expecting some result/value from the query.
Only difference between ExecuteNonQuery and ExecuteScalar scalar is that we use ExecuteNonQuery when when query is returning nothing and we use ExecuteScalar when query is returning some result.

ExecuteScalar example:

        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
        string cmdText = "INSERT INTO employee (Name,Email,Age) VALUES (@Name,@Email,@Age); SELECT SCOPE_IDENTITY();";
        SqlCommand cmd = new SqlCommand(cmdText, con);
        cmd.Parameters.AddWithValue("@Name", "Test");
        cmd.Parameters.AddWithValue("@Email", "Test@gmail.com");
        cmd.Parameters.AddWithValue("@Age", 30);
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        int result = Convert.ToInt32(cmd.ExecuteScalar());
        lblResult.Text = "Id of the newely created record is: " + result.ToString();
        con.Close();
    
Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery in asp .net
In this case result is 20 because, Primary key "Id" of the newly inserted record is 20. Here we have used "Select SCOPE_IDENTITY()" which returns primary key value of newly created record.

ExecuteDataReader

ExecuteDataReader is used to execute where we want to get some records from the database. It provides us an option to read data from database in forward-only direction. Generally ExecuteDataReader is used with SELECT queries.

ExecuteDataReader example:

         SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["myConnection"].ConnectionString);
        string cmdText = "SELECT * FROM employee";
        SqlCommand cmd = new SqlCommand(cmdText, con);
        if (con.State == ConnectionState.Closed)
        {
            con.Open();
        }
        SqlDataReader dr = cmd.ExecuteReader();
        while (dr.Read())
        {
            string Name = dr["Name"].ToString();
            string Email = dr["Email"].ToString();
            int Age = Convert.ToInt32(dr["Age"]);

        }
        con.Close();
        dr.Close();
    
Note: If you like to have more information about database table and namespaces required you can read this: Gridview insert update delete in asp net

Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery in asp .net Demo

Difference between ExecuteReader ExecuteScalar and ExecuteNonQuery in asp .net

We are nothing without our users ! You can help us offer even more high quality content. Please share our page !
Best quality Asp .Net Ajax Control Toolkit tutorials.

Give your valuable comments.

Name
Email
Comment
1 + 5 =
 
About Us | Terms of Use | Privacy Policy | Disclaimer | Contact Us Copyright © 2012-2019 CodingFusion
75 Important SQL Server queries you should know