Tuesday, January 28, 2014

Ado.Net - Difference between ExecuteReader, ExecuteNonQuery, ExecuteScalar

1. Introduction

To retrieve data from the database, we need database connection. In ADO.Net, based on the Database provider, there are different connection classes. For example, to connect to an Oracle Database we need to use OracleConnection and in the same way we need SqlConnection to connect to the SQL Server database.

The Command Object is also database specific. For Example, SqlCommand object is capable of interacting with SQL Server database through SqlConnection object and OracleCommand object is capable of interacting with Oracle database through OracleConnection. These command objects knows what needs to be executed in the underlying database. In this article, I will explain how do we use SqlCommand and SqlConnection.



2. SqlConnection Object

The SqlConnection object is useful to open a connection to the SQL Server database. This object knows where the database exists that the machine in which the database is running, name of the database to connect and the required database credentials to enter into the database. The Open method of this SqlConnection object opens the database connection allowing further database interaction possible. Once all opened SqlConnection object should closed after the use using the close method call. This is applicable SqlCommand that we will see the coming section.

In our article we will use this SqlConnection object to open connection to the pubs database by making use of connection string.



3. SqlCommand Object

The SqlCommand carries the SQL statement that needs to be executed on the database. SqlCommand carries the command in the CommandText property and this property will be used when the SqlCommand calls any of its execute methods. The three important methods exposed by the SqlCommand object is shown below:

  1. ExecuteScalar
  2. ExecuteNonQuery
  3. ExecuteReader

ExecuteScalar method is useful to return a single value from the database. For example, using this method we can retrieve sum of sales made by a particular product, Total number of records in the employee table, unique id by supplying filtering conditions etc. As this method performs faster we no need to go for Reader method just to retrieve a single scalar value.

ExecuteNonQuery is useful to perform data manipulation on the database. Simply, the ExecuteNonQuery is for executing the DML statements. The return value of the ExecuteNonQuery is an integral value that represents number of rows affected by the Operation.

ExecuteReader method will be used when we need to retrieve rows and columns of data using the SQL select statements. As the data retrieved is a table of data, ExecuteReader returns SqlDataReader. We should iterate through this object to get the required values.

The below diagram shows how SqlConnection and SqlCommand interact together:



In the above diagram, you can see that SqlCommand needs a connection to know where exactly the database exists in order to execute the command on it. The ExecuteReader method returns SqlDataReader, which needs to be iterated to read the results from it. In our example, we are going to use ExecuteScalar, ExecuteNonQuery & ExecuteReader methods and this will help in understanding the difference between them.



4. About the Example

The sample application that we are going to develop is shown below:


When the form displays to the user, total number of book titles is displayed in the top of the form. To do this we are going to use the ExecuteScalar method and this will use the count (*) aggregate function on the Titles table. The middle of the form is having a multi-line text box which is used to display the all the Book Titles along with the price and other important details. To achieve this, we are going to use ExecuteReader method. To change the title type we use the set of text boxes in the bottom of the dialog. We will use the ExecuteNonQuery method of the SqlCommand when the update button is clicked.



5. Create the application

1) Start a Microsoft Visual C# Windows Application. Design the form as shown above. Once this is done place the below using statements in the code window for the form:

//Sample 01: SQL Database Support
using System.Data;
using System.Data.SqlClient;

2) Declare SqlConnection object as private member of the form. The code is shown below:

//Sample 02: Declaration for Sql Database
private SqlConnection pubs_db_connection = null;

6. Open Database Connection

In this example we will open connection to the Pubs database using the connection. We can store connection string as application settings. Connection String is a string representation of information, which tells where the database is, and how to connect with it. The below video shows creating a connection string and having that as application setting:

Video 1: Connection String as application setting



Once the connection string is ready as part of the application setting, that can be referred at anytime in the code window across the application. Have a look at the below code picture:


Explanation


  1. Connection string created as application setting (ConStrPubs) in the previous video is retrieved and stored as String.
  2. SqlConnection object is created and stored in the private member of the form.
  3. Open function is called on the SqlConnection object to open the connection to the SQL Server Pubs database.

The code is given below:

//Sample 03: Open Database Connection
String con_string = Properties.Settings.Default.ConStrPubs;
pubs_db_connection = new SqlConnection(con_string);
pubs_db_connection.Open();

7. ExecuteScalar Method

ExecuteScalar method of the SqlCommand object is useful to retrieve a single value from the database. In our example, we have to retrieve total number of records in the Titles table of the Pubs database. As total number of record is single scalar value, the Execute Scalar method is used. Below is the code and it explanation:

private void frmSqlCommand_Load(object sender, EventArgs e)
{
    //Sample 03: Open Database Connection
    String con_string = Properties.Settings.Default.ConStrPubs;
    pubs_db_connection = new SqlConnection(con_string);
    pubs_db_connection.Open();

    //Sample 04: Form the Command Object
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "Select Count(*) as Count from Titles";
    cmd.Connection = pubs_db_connection;

    //Sample 05: Execute the Command & retrive scalar value
    lblTotal.Text = System.Convert.ToString(cmd.ExecuteScalar());          
}

  1. First a connection to the database is opened.
  2. Next SqlCommand object is created and its CommandText property is set with the SQL Statement. Note that count(*) aggregate function will return total number of titles. The Connection property of the command object is set with opened SQL connection.
  3. Once the command object is ready, ExecuteScalar method is called. This method returns the Object and that is converted to string and assigned to the label. In the screen shot you are seeing this as 19.



8. ExecuteReader Method

ExecuteReader method is useful for getting rows and columns of data. In our example, we need to retrieve the book titles and display that in the multiline textbox. In the coming articles I will show using the grid based control. In the Refresh button’s click event handler, the ExecuteReader method is used. Now have look at the below picture:



Code Explanation



  1. First a Command Object is formed using the Select Query that retrieves data from the Titles table of the Pubs database. This command object is also given the connection object that knows where (SQl Server Pubs database) that select statement will be executed.
  2. Once Command is formed, ExecuteReader method is called on the command object cmd. This method queries all the four columns and packs that in the SqlDataReader. This SqlDataReader is returned to the caller and in our example we collected that object in data_reader. Then the retrieved object is iterated in the while loop to read all the records packed in it.
  3. In the iteration the four column values are read and displayed in the console output window. Here, the column name is passed as array index to retrieve the column value. You can also use position based indexing. We will see about this in the next article.
  4. After fetching all the values from the reader, the SqlDataReader is closed.

The complete event handler is shown below:

private void btnRefresh_Click(object sender, EventArgs e)
{
    //Sample 06: Create the command
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "Select Title, Type, Price,Royalty from Titles";
    cmd.Connection = pubs_db_connection;

    //Sample 07: Get the DataReader and Iterate through it
    SqlDataReader data_reader = cmd.ExecuteReader();
    while (data_reader.Read())
    {
        txtDisplay.Text += "Title :" + data_reader["Title"] +
            Environment.NewLine;
        txtDisplay.Text += "Type :" + data_reader["Type"] +
            Environment.NewLine;
        txtDisplay.Text += "Price :" + data_reader["Price"] +
            Environment.NewLine;
        txtDisplay.Text += "Royalty :" + data_reader["Royalty"] +
            Environment.NewLine;
        txtDisplay.Text += "========================================" +
            Environment.NewLine;
    }

    //Sample 08: Close the Data Reader
    data_reader.Close();
}


9. DML using ExecuteNonQuery

ExecuteNonQuery is useful to execute the Data Manipulation command on the underlying database. This methods returns the rows effected to the caller. Have a look at the code screenshot below:


Code Explanation



  1. As usual the command object is formed using the SQL Statement. This time the SQL Statement is an update statement formed to change the title type by reading the use supplied value. You can see that appending the text box read value to the Sql statement forms the update statement.
  2. Once the command object is ready with the valid update statement and the connection object, the ExecuteNonQuery method is called on it. This method returns the number rows affected by the query and in our example it is collected in the rows_affected variable to display it in the message box.

Video 2: Running Sample Application.


The complete code is shown below:

private void btnUpdate_Click(object sender, EventArgs e)
{
    //Sample 08: Create the update command
    SqlCommand cmd = new SqlCommand();
    cmd.CommandText = "Update Titles set Type = '" +
        txtTypeTo.Text + "' where Type ='" + txtTypeFrom.Text  + "'";
    cmd.Connection = pubs_db_connection;

    //Sample 09: Execute the Update Command
    int rows_affected = cmd.ExecuteNonQuery();
    MessageBox.Show(String.Format("{0} Row(s) effected", rows_affected));
}

 Source Code : Download
Like this site? Tell it to your Firend :)