Master the SqlCommand in ADO.NET!

Table of Contents

1. Introduction
2. Purpose of SQL Command Object
3. Creating an Instance of SQL Command Class
4. When and How to Use ExecuteReader Method
5. When and How to Use ExecuteScalar Method
6. When and How to Use ExecuteNonQuery Method
7. Example: Using ExecuteReader Method
8. Example: Using ExecuteScalar Method
9. Example: Using ExecuteNonQuery Method
10. Summary

Introduction

Welcome to Part 4 of the Radio Dotnet video series by Prism Technologies. In this session, we will be focusing on the SQL Command object and its various methods. Specifically, we will learn about the purpose of the SQL Command object, how to Create an instance of it, and when and how to use the ExecuteReader, ExecuteScalar, and ExecuteNonQuery methods. By the end of this session, You will have a clear understanding of how to use the SQL Command object to execute SQL statements or stored procedures on a SQL Server database.

Purpose of SQL Command Object

The SQL Command object is used to prepare an SQL statement or a stored procedure that we want to execute on a SQL Server database. It allows us to Interact with the database by executing queries, retrieving data, and performing various operations such as inserts, updates, and deletes. In this session, we will focus on how to use the SQL Command object to prepare an SQL statement and execute it on a database.

Creating an Instance of SQL Command Class

To create an instance of the SQL Command class, we first need to establish a connection to the database using the SQL Connection object. Once the connection is established, we can create an instance of the SQL Command class and pass in the command text and the SQL Connection object as parameters. The command text specifies the SQL statement or stored procedure that we want to execute, while the SQL Connection object determines the database and server on which the command will be executed.

It is good practice to use the 'using' statement when working with the SQL Command object, as it automatically closes the connection for us after the command has been executed.

When and How to Use ExecuteReader Method

The ExecuteReader method of the SQL Command object is used when the SQL statement or stored procedure being executed returns multiple rows of data. This method returns a data reader, which is an object that allows us to read the returned data and perform operations on it.

To use the ExecuteReader method, we first need to open the connection using the Open method of the SQL Connection object. Once the connection is open, we can call the ExecuteReader method on the SQL Command object to execute the command. The returned data reader can then be bound to a data control, such as a GridView, to display the results.

Example: Using ExecuteReader Method

Let's demonstrate the usage of the ExecuteReader method with an example. Imagine we have a SQL Server database with a table called 'TBL Product', which contains columns for product ID, name, unit price, quantity present, and quantity available. Our goal is to display all this data in a GridView control on a web form.

To achieve this, we first need to establish a connection to the database using the SQL Connection object. Next, we create an instance of the SQL Command class and pass in the command text, which is the SQL statement for selecting all columns from the 'TBL Product' table. We then call the ExecuteReader method on the SQL Command object to execute the command and return a data reader. Finally, we Bind the data reader to the GridView control on the web form to display the results.

It is worth noting that, before executing the command, we should always check if the connection is open and handle any potential exceptions that may occur during the execution.

When and How to Use ExecuteScalar Method

The ExecuteScalar method of the SQL Command object is used when the SQL statement or stored procedure being executed returns a single value. This method is typically used when we need to retrieve a scalar value, such as the total number of rows in a table or the average salary of an employee.

To use the ExecuteScalar method, we follow a similar process as before - first opening the connection using the Open method of the SQL Connection object, then calling the ExecuteScalar method on the SQL Command object to execute the command. The returned value is of Type 'object', so we need to cast or convert it to the appropriate type Based on our expectations.

Example: Using ExecuteScalar Method

Let's illustrate the usage of the ExecuteScalar method with an example. Suppose we have a SQL query that returns the total number of rows in a table. The query would look something like this: 'SELECT COUNT(*) FROM TableName'. By executing this query using the ExecuteScalar method, we will get a single scalar value back, indicating the total number of rows in the table.

To implement this, we first establish a connection to the database, create an instance of the SQL Command class, and pass in the command text of our query. We then call the ExecuteScalar method on the SQL Command object to execute the command and retrieve the scalar value. Finally, we convert the scalar value to the appropriate type (e.g., using int.Parse) and display it to the user.

When and How to Use ExecuteNonQuery Method

The ExecuteNonQuery method of the SQL Command object is used when the SQL statement or stored procedure being executed performs an insert, update, or delete operation. This method does not return any data, but rather returns the number of rows affected by the operation.

To use the ExecuteNonQuery method, we follow a similar process as before - first opening the connection using the Open method of the SQL Connection object, then calling the ExecuteNonQuery method on the SQL Command object to execute the command. The returned integer value represents the number of rows affected by the operation.

Example: Using ExecuteNonQuery Method

Let's demonstrate the usage of the ExecuteNonQuery method with examples of insert, update, and delete operations. In each case, we will create a SQL command for the respective operation and execute it using the ExecuteNonQuery method. We will capture the number of rows affected by each operation and display it as output.

For the insert operation, we will specify the values for the columns in the 'TBL Product' table that need to be inserted and execute the command. The ExecuteNonQuery method will return the number of rows inserted.

For the update operation, we will specify the column and value to be updated, as well as the condition for the update (e.g., product ID). The ExecuteNonQuery method will return the number of rows updated.

For the delete operation, we will specify the condition for the delete (e.g., product ID) and execute the command. The ExecuteNonQuery method will return the number of rows deleted.

By executing these examples using the respective SQL commands and the ExecuteNonQuery method, we will see the number of rows affected for each operation.

Summary

In this session, we learned about the SQL Command object and its important methods: ExecuteReader, ExecuteScalar, and ExecuteNonQuery. We explored when and how to use each method, using examples that demonstrate their usage in different scenarios. The ExecuteReader method is used for retrieving multiple rows of data, the ExecuteScalar method is used for retrieving a single scalar value, and the ExecuteNonQuery method is used for performing insert, update, or delete operations. By understanding the purpose and proper usage of the SQL Command object and its methods, we can efficiently interact with a SQL Server database and retrieve or manipulate data as needed.