Search - Articles
DevASP.NET for ASP.NET, VB.NET, XML and C# (C-Sharp) Developers Tuesday, March 03, 2009
Dev Articles
Search Directory
ASP.NET
VB.Net
C-Sharp
SQL Server
 

ExecuteReader, ExecuteScalar and ExecuteNonQuery methods in ASP.NET

Author: Richard Wand

This article will explain ExecuteReader, ExecuteScalar and ExecuteNonQuery methods with example.

 

When we manipulate database data in ADO.NET code, ExecuteReader, ExecuteScalar and ExecuteNonQuery are three basic methods extensively used to run queries.  These methods are available in SqlCommand,  OledbCommand and DbCommand classes under System.Data.SqlClient, System.Data.OleDb and System.Data.Common namespaces respectively. I will explain purpose of these three methods with examples.
 
ExecuteScalar() Method

ExecuteScalar() method is used to retrieve a single value from database. It executes the defined query and returns the value in the first column of the first row in the selected result set and ignores all other columns and rows in the result set. It is use to get aggregate value from database, for example count or total of rows. So it works with non action queries that use aggregate functions. ExecuteScalar() method is a faster way when we compare it to other ways to retrieve single value from database. It returns a value as object and we have to cast it to appropriate type.
 
Here is the example of ExecuteScalar() method. Use System.Data.SqlClient namespace for this example.
 
C#
 
public string GetCustomerName()
{
    string query = "SELECT CustName FROM Customers WHERE CustID = 2";
    string connString = "Data Source=local;Initial Catalog=SampleDB;Integrated Security=True";
    SqlConnection conn = new SqlConnection(connString);
    SqlCommand cmd = new SqlCommand(query, conn);
    string custName = "";
    try
    {
        conn.Open();
        custName = cmd.ExecuteScalar().ToString();
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }
    finally
    {
        conn.Close();
    }
    return custName;
}
 
VB.NET
 
Public Function GetCustomerName() As String
    Dim query As String = "SELECT CustName FROM Customers WHERE CustID = 2"
    Dim connString As String = "Data Source=local;Initial Catalog=SampleDB;Integrated Security=True"
    Dim conn As New SqlConnection(connString)
    Dim cmd As New SqlCommand(query, conn)
    Dim custName As String = ""
    Try
        conn.Open()
        custName = cmd.ExecuteScalar().ToString()
    Catch ex As Exception
        Response.Write(ex.Message)
    Finally
        conn.Close()
    End Try
    Return custName
End Function
 
ExecuteScalar() method is used here to retrieve a single value that is customer name for a specific customer ID. It returns object value so we have to cast it in appropriate type “string” to assign it to variable.
 
ExecuteNonQuery() Method
 
ExecuteNonQuery() method is used to manipulate data in database and is used for statements without results such as CREATE, INSERT, UPDATE and DELETE commands. It does not return any data but it returns number of rows affected. If NO COUNT property is ON then it will not return number of rows affected. It will not give access to result set generated by the statement. The return value of number of rows affected is of type integer and you can get it in an integer variable. It will tell you how many rows have been affected in result of your statement. ExecuteNonQuery() method is a flexible method and we can use it input and output parameters.
 
Here is the example of ExecuteNonQuery() method. Use System.Data.SqlClient namespace for this example.
 
C#
 
public int DeleteCustomer()
{
    string query = "DELETE FROM Customers WHERE CustID = 5";
    string connString = "Data Source=local;Initial Catalog=SampleDB;Integrated Security=True";
    SqlConnection conn = new SqlConnection(connString);
    SqlCommand cmd = new SqlCommand(query, conn);
 
    int rowsAffected = 0;
 
    try
    {
        conn.Open();
        rowsAffected = cmd.ExecuteNonQuery(); ;
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }
    finally
    {
        conn.Close();
    }
    return rowsAffected;
}
 
VB.NET
 
Public Function DeleteCustomer() As Integer
   Dim query As String = "DELETE FROM Customers WHERE CustID = 5"
   Dim connString As String = "Data Source=local;Initial Catalog=SampleDB;Integrated Security=True"
   Dim conn As New SqlConnection(connString)
   Dim cmd As New SqlCommand(query, conn)
 
   Dim rowsAffected As Integer = 0
 
   Try
       conn.Open()
       rowsAffected = cmd.ExecuteNonQuery()
   Catch ex As Exception
       Response.Write(ex.Message)
   Finally
       conn.Close()
   End Try
   Return rowsAffected
End Function
 
ExecuteNonQuery() method is used here to delete a record for specific customer from database table. This method returns number of rows affected as integer.
 
ExecuteReader() Method
 
ExecuteReader() method is used with SELECT command. It returns set of rows by executing query or stored procedure mentioned in the command object. When we use ExecuteReader() method, It is necessary that query returns value. It can return one or more result sets as a result of query. The returned data has the DataReader return type. ExecuteReader() method is a read-only and forward-only way of retrieving data. It means we cannot edit data in result set. ExecuteReader() is also a connected way of data retrieval. It uses SELECT statement to read through the table from first to last record in a connected way. Do not use ExecuteReader() method when you know that the result of the query is exactly one record. The better is to use ExecuteScalar() in this situation.
 
Here is the example of ExecuteReader() method. Use System.Data.SqlClient namespace for this example.
 
C#
 
public void GetCustomerDetail()
{
    string query = "SELECT * FROM Customers WHERE CustID = 2";
    string connString = "Data Source=local;Initial Catalog=SampleDB;Integrated Security=True";
    SqlConnection conn = new SqlConnection(connString);
    SqlCommand cmd = new SqlCommand(query, conn);
 
    string name = "";
    string city = "";
    string state = "";
 
    try
    {
        conn.Open();
        SqlDataReader reader = cmd.ExecuteReader();
        while (reader.Read())
        {
            name = reader["CustName"].ToString();
            city = reader["City"].ToString();
            state = reader["state"].ToString();
        }
    }
    catch (Exception ex)
    {
        Response.Write(ex.Message);
    }
    finally
    {
        conn.Close();
    }
}
 
VB.NET
 
Public Sub GetCustomerDetail()
    Dim query As String = "SELECT * FROM Customers WHERE CustID = 2"
    Dim connString As String = "Data Source=local;Initial Catalog=SampleDB;Integrated Security=True"
    Dim conn As New SqlConnection(connString)
    Dim cmd As New SqlCommand(query, conn)
 
    Dim name As String = ""
    Dim city As String = ""
    Dim state As String = ""
 
    Try
        conn.Open()
        Dim reader As SqlDataReader = cmd.ExecuteReader()
        While reader.Read()
            name = reader("CustName").ToString()
            city = reader("City").ToString()
            state = reader("state").ToString()
        End While
    Catch ex As Exception
        Response.Write(ex.Message)
    Finally
        conn.Close()
    End Try
End Sub
 
ExecuteReader() method is used here to retrieve all detail about a specific customer. It can return any number of records. We have to call the Read() method to read data from result set.

 

   
Add Article Comment:
Name :
Email Address :
   
Comments :
 
   
<< How to auto generate serial number for GridView rows in ASP.NET

Disclaimer - Privacy
© 2002-2017 DevASP.net