Executing SQL Queries Explained

Executing SQL queries in C# involves using ADO.NET to interact with databases. This guide will walk you through the key concepts and steps to execute SQL queries effectively.

1. Key Concepts

Understanding the following key concepts is essential for executing SQL queries in C#:

• Connection Object: Establishes a connection to the database.
• Command Object: Executes SQL statements or stored procedures.
• DataReader Object: Reads data from the database in a forward-only manner.
• DataSet Object: Represents a cache of data retrieved from the database.
• DataAdapter Object: Fills a DataSet with data from the database and updates the database with changes made to the DataSet.

2. Establishing a Connection

Before executing any SQL query, you need to establish a connection to the database. The connection string specifies the database server, authentication, and other parameters.

Example

using System.Data.SqlClient;

string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

3. Executing a SQL Query

Once the connection is established, you can execute SQL queries using the Command object. The Command object requires a connection and the SQL query or stored procedure name.

Example: Executing a SELECT Query

string query = "SELECT * FROM Customers";
SqlCommand command = new SqlCommand(query, connection);
SqlDataReader reader = command.ExecuteReader();

while (reader.Read())
{
    Console.WriteLine(reader["CustomerName"]);
}
reader.Close();

Example: Executing an INSERT Query

string insertQuery = "INSERT INTO Customers (CustomerName) VALUES ('John Doe')";
SqlCommand insertCommand = new SqlCommand(insertQuery, connection);
int rowsAffected = insertCommand.ExecuteNonQuery();
Console.WriteLine($"{rowsAffected} row(s) inserted.");

4. Using DataReader

The DataReader object provides a way to read a forward-only stream of data rows from the database. It is efficient for reading large amounts of data because it only loads data as needed.

Example

while (reader.Read())
{
    Console.WriteLine(reader["CustomerName"]);
}
reader.Close();

5. Using DataSet and DataAdapter

The DataSet object represents a cache of data retrieved from the database. The DataAdapter object acts as a bridge between the DataSet and the database, filling the DataSet with data and updating the database with changes made to the DataSet.

Example

SqlDataAdapter adapter = new SqlDataAdapter(query, connection);
DataSet dataSet = new DataSet();
adapter.Fill(dataSet, "Customers");

DataTable table = dataSet.Tables["Customers"];
foreach (DataRow row in table.Rows)
{
    Console.WriteLine(row["CustomerName"]);
}

6. Executing Stored Procedures

Stored procedures are precompiled SQL statements that can be executed with parameters. They are often used for complex queries or to encapsulate business logic.

Example

string storedProcedure = "GetCustomerDetails";
SqlCommand command = new SqlCommand(storedProcedure, connection);
command.CommandType = CommandType.StoredProcedure;

SqlParameter param = new SqlParameter("@CustomerID", SqlDbType.Int);
param.Value = 1;
command.Parameters.Add(param);

SqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
    Console.WriteLine(reader["CustomerName"]);
}
reader.Close();

7. Handling Transactions

Transactions allow you to group multiple database operations into a single transaction. This ensures that either all operations are completed successfully, or none are.

Example

SqlTransaction transaction = connection.BeginTransaction();
try
{
    SqlCommand command = new SqlCommand("INSERT INTO Customers (CustomerName) VALUES ('John Doe')", connection, transaction);
    command.ExecuteNonQuery();
    transaction.Commit();
}
catch (Exception)
{
    transaction.Rollback();
}