1. Introduction to SqlCommand

To fetch data from the database, we need DB connection. In ADO.Net, based on the Database Provider, there are distinct connection classes. For example, to hook up to an Oracle Database we need to use OracleConnection and in the same way we need SqlConnection to hook up to the SQL Server database. The Command Object is also database specific. For Example, SqlCommand object can talk with SQL Server DB through SqlConnection object and OracleCommand object can talks to the Oracle DB through OracleConnection. These command objects know what needs to be executed in the database. In this article, we will study how to use SqlCommand and its methods ExecuteScalar, ExecuteReader and ExecuteNonQuery.

2. The SqlConnection Object

The SqlConnection object is to open a connection to the SQL Server DB. This object knows in which box the database is running, name of the DB to connect and the needed user name plus password to enter the database. The Open() method of this SqlConnection object opens the DB connection, granting further interaction possible. After talking with the DB, we can use the Close() method of the SqlConnection to close the DB connection.

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

3. Retrieve Data Using SqlCommand

The SqlCommand carries the SQL statement that needs to be run on the DB. It takes the command in the CommandText property and Ado.Net will use this property when the it calls one of its execute method. The below list shows the three useful methods of SqlCommand object:

1. ExecuteScalar
2. ExecuteNonQuery
3. ExecuteReader

The ExecuteScalar method gets a single value from the database. For example, using this method we can fetch Sum of Sales made by a product, Total number of records in the staff table, ID Keys by supplying filter conditions, etc. As this method works faster, we no need to go for Reader methods just to pull a single scalar value.

ExecuteNonQuery is to do data change on the database. Simply, the ExecuteNonQuery is for dealing with the DML SQLs. The return value of the ExecuteNonQuery is an integer SQL value which shows the number of rows affected by the SQL.

We will use ExecuteReader method when we need to bring rows and columns of data using the SQL select statements. As the data fetched is a table of data, ExecuteReader returns SqlDataReader. We should iterate through this object to get the needed values.

The below block diagram shows how SqlConnection and SqlCommand interact together:

In the above diagram, we can see that SqlCommand needs a DB connection to know where the DB lives to run the command on it. The ExecuteReader method returns SqlDataReader, which needs to be looped to read the results from it. In our example, we will use ExecuteScalar, ExecuteNonQuery & ExecuteReader methods and this will help in figuring out when to use those.

4. About The Example

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

When the form opens to the user, a total number of book titles are shown in the form’s top. To do this, we will 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 we will use to show all the Book Titles along with the price and other details. To do this, we will use ExecuteReader method. To change the title type, we use the set of text boxes in the dialog’s bottom. We will use the ExecuteNonQuery method of the SqlCommand when the user clicks the update button.

5. Create The Windows Form

First, we start a Microsoft Visual C# Windows Application. One can design the form as shown in the previous section. After designing the form, we place the below ‘using statements’ in the code window for the form:

Next we declare

SqlConnection

object as private member of the form. The code is below:

6. Open Connection to SQL Server Database

In this example we will open connection to the Pubs database using the ‘ Ado.net SqlConnection’. We can store connection string as app settings. Connection String is a string form of data, which tells where the DB is, and how to connect with it. The below video creates a connection string and having that as app setting:

---

Video 1: Connection String as Application Setting

---

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

Explanation

1. We retrieve the Connection String from the application settings and set that in a string variable con_string.
2. Next, we create the SqlConnection object and store it in the private member pubs_db_connection.
3. Finally, we call Open() function on the SqlConnection object to open the connection to the SQL Server Pubs DB.

The code is given below:

7. ExecuteScalar Method

ExecuteScalar method of the SqlCommand object is useful to fetch a single value from the database. In our example, we must get the total number of records in the Titles table of the Pubs DB. We use the ExecuteScalar method as the total number of records is a single scalar value. Below is the code:

Explanation

1. First, we open a connection to the database using Open() method of the SqlConnection object.
2. Next we create the SqlCommand object and set its CommandText property with the SQL Statement. Note, the count(*) aggregate function will return the total number of titles. We also set the Connection property of the command object with the opened SQL connection.

3. Once the command object is ready, ExecuteScalar  method is called. This method returns the Object, and we convert it to string and then assign it to the label. In the example you are seeing this as 19.

8. ExecuteReader Method

ExecuteReader method gets table of data. In our example, we need to pull the book titles and show that in the multi-line text box. In the future articles we will learn how to use the grid-based control. We use the ExecuteReader method in the Refresh button’s click event handler. Now have a look at the below picture:

Explanation

1. First, we form a SqlCommand Object. Then we set SQL Select Query to this command object via its CommandText property. The Select Query pulls data from the Titles table of the Pubs database. We also give this command object a SqlConnection object which knows where (SQL Server Pubs DB) to run the select query.
2. After SqlCommand object is ready, we call ExecuteReader method on it. This method pulls all the four columns and packs that in the SqlDataReader. The call to ExecuteReader method returns SqlDataReader and in our case we saved that object in data_reader reference. Then it is iterated in the while loop to read all the records packed in it.
3. In the while loop, we read the four column values and show it in the console output window. Here, the column name is passed as an array index to fetch the column value. You can also use position-based indexing.
4. After fetching all the values from the reader, We close the SqlDataReader by calling close() method.

The complete event handler is shown below:

9. ExecuteNonQuery Method

The

<strong>ExecuteNonQuery</strong> 

 is to execute the DML commands on the SQL Server DB. This method returns the number of rows affected to the caller. Have a look at the code screenshot below:

Explanation

1. As usual, we form the command object using the SQL query. This time the SQL is an update formed to change the title type by reading the user-supplied value. You can see that appending the text box read value to the SQL statement forms the update for the DB.
2. Once the command object is ready with the valid update statement and the connection object, we call the ExecuteNonQuery method on it. This method returns the number of rows affected by the query and in our case, we save it in the rows_affected variable to show it in the message box.

---

Video 2: Running the Example

---

The complete code is below:

Source Code: Download SqlCommand Example From Google Drive

• MFC Rebar Control – Enable Floating Toolbar
• Updating Table Data Via SQL Views

Categories: Ado.Net

Tags: ExecuteReader(), ExecuteScalar, ExeuteNonQuery, SqlCommand, SqlConnection, SqlDataReader

This site uses Akismet to reduce spam. Learn how your comment data is processed.