Post
Ask Question

Executing Commands with ADO.NET

Introduction

ADO.NET is a powerful framework for accessing and manipulating databases in .NET applications. One of the core components of ADO.NET is the SqlCommand class, which allows you to execute SQL queries and stored procedures against an SQL Server database. In this article, we'll explore how to use SqlCommand to execute SQL queries and stored procedures, the importance of using parameters to prevent SQL injection, and how to handle command execution results.

Using SqlCommand to Execute SQL queries and stored procedures

The SqlCommand class is used to execute SQL statements and stored procedures against a SQL Server database. It provides several methods for executing commands, such as ExecuteNonQuery, ExecuteScalar, and ExecuteReader.

Executing SQL Queries

To execute a simple SQL query, follow these steps.

  1. Create a Connection: Establish a connection to the database using SqlConnection.
  2. Create a Command: Create an instance of SqlCommand with the SQL query and the connection.
  3. Execute the Command: Use the appropriate method to execute the command.

Here’s an example of executing a SELECT query.

using System;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString = "YourConnectionStringHere";
        string query = "SELECT FirstName, LastName FROM Employees";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);
            connection.Open();
            using (SqlDataReader reader = command.ExecuteReader())
            {
                while (reader.Read())
                {
                    Console.WriteLine($"{reader["FirstName"]} {reader["LastName"]}");
                }
            }
        }
    }
}

Executing Stored Procedures

To execute a stored procedure, you need to set the CommandType property of SqlCommand to CommandType.StoredProcedure:

using System;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString = "YourConnectionStringHere";
        string storedProcedureName = "GetEmployeeDetails";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(storedProcedureName, connection);
            command.CommandType = System.Data.CommandType.StoredProcedure;           
            connection.Open();           
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine($"{reader["FirstName"]} {reader["LastName"]}");
            }
            reader.Close();
        }
    }
}

Parameters and Parameterized queries to Prevent SQL injection

SQL injection is a common security vulnerability that occurs when an attacker is able to manipulate a SQL query by injecting malicious input. To prevent SQL injection, always use parameterized queries.

Using Parameters in SqlCommand

You can add parameters to a SqlCommand using the Parameters collection. Here’s an example.

using System;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString = "YourConnectionStringHere";
        string query = "SELECT FirstName, LastName FROM Employees WHERE EmployeeID = @EmployeeID";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@EmployeeID", 1);
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine($"{reader["FirstName"]} {reader["LastName"]}");
            }
            reader.Close();
        }
    }
}

Handling command execution results

Depending on the type of command, you will use different methods to handle the results.

ExecuteNonQuery

Use ExecuteNonQuery for commands that do not return any data, such as INSERT, UPDATE, and DELETE statements. It returns the number of rows affected.

using System;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString = "YourConnectionStringHere";
        string query = "UPDATE Employees SET LastName = @LastName WHERE EmployeeID = @EmployeeID";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);
            command.Parameters.AddWithValue("@LastName", "Smith");
            command.Parameters.AddWithValue("@EmployeeID", 1);
            connection.Open();
            int rowsAffected = command.ExecuteNonQuery();
            Console.WriteLine($"{rowsAffected} row(s) updated.");
        }
    }
}

ExecuteScalar

Use ExecuteScalar to retrieve a single value, such as an aggregate value.

using System;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString = "YourConnectionStringHere";
        string query = "SELECT COUNT(*) FROM Employees";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);
            connection.Open();
            int employeeCount = (int)command.ExecuteScalar();
            Console.WriteLine($"Total employees: {employeeCount}");
        }
    }
}

ExecuteReader

Use ExecuteReader for commands that return multiple rows of data. It returns a SqlDataReader that you can use to read the results.

using System;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString = "YourConnectionStringHere";
        string query = "SELECT FirstName, LastName FROM Employees";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            SqlCommand command = new SqlCommand(query, connection);
            connection.Open();
            SqlDataReader reader = command.ExecuteReader();
            while (reader.Read())
            {
                Console.WriteLine($"{reader["FirstName"]} {reader["LastName"]}");
            }
            reader.Close();
        }
    }
}

Summary

The SqlCommand class in ADO.NET provides a robust way to execute SQL queries and stored procedures. By using parameters and parameterized queries, you can prevent SQL injection and ensure the security of your application. Handling command execution results appropriately allows you to manage the data returned from your queries effectively. With these techniques, you can leverage the full power of ADO.NET to interact with your SQL Server databases.


Similar Articles