C# SQL Commands
When building applications that interact with databases, you'll often need to execute SQL commands from your C# code. This allows your application to create, read, update, and delete data in your database systems. In this guide, we'll explore how to execute various SQL commands using C# and the ADO.NET framework.
Introduction to SQL Commands in C#
SQL (Structured Query Language) is the standard language for interacting with relational databases. When working with databases in C# applications, you'll use ADO.NET - Microsoft's data access technology that bridges your C# code and your database.
The most common types of SQL commands you'll execute are:
- SELECT - Retrieve data from the database
- INSERT - Add new records to the database
- UPDATE - Modify existing records
- DELETE - Remove records from the database
- CREATE/ALTER/DROP - Manage database structure
Let's explore how to execute each of these commands from C# code.
Setting Up Your Environment
Before we start writing SQL commands in C#, we need to set up our environment:
- Add the necessary references to your project:
csharp
using System.Data;
using System.Data.SqlClient; // For Microsoft SQL Server
// For other database types, you'd use different providers:
// using MySql.Data.MySqlClient; // For MySQL
// using System.Data.SQLite; // For SQLite
// using Npgsql; // For PostgreSQL
- Create a connection string to your database:
csharp
string connectionString = "Data Source=ServerName;Initial Catalog=DatabaseName;User ID=Username;Password=Password";
// Or for a local SQL Server Express instance:
// string connectionString = "Data Source=.\\SQLEXPRESS;Initial Catalog=DatabaseName;Integrated Security=True";
Executing SELECT Commands
SELECT commands retrieve data from your database. Here's how to execute them in C#:
Basic SELECT Example
csharp
public List<Customer> GetAllCustomers()
{
List<Customer> customers = new List<Customer>();
// Create the SQL command
string sql = "SELECT CustomerID, FirstName, LastName, Email FROM Customers";
// Establish a connection to the database
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Create the command object
using (SqlCommand command = new SqlCommand(sql, connection))
{
// Open the connection
connection.Open();
// Execute the command and get a data reader
using (SqlDataReader reader = command.ExecuteReader())
{
// Process the results
while (reader.Read())
{
Customer customer = new Customer
{
CustomerID = (int)reader["CustomerID"],
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
Email = reader["Email"].ToString()
};
customers.Add(customer);
}
}
}
}
return customers;
}
SELECT with Parameters
Using parameters helps prevent SQL injection attacks:
csharp
public Customer GetCustomerById(int customerId)
{
Customer customer = null;
string sql = "SELECT CustomerID, FirstName, LastName, Email FROM Customers WHERE CustomerID = @CustomerID";
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(sql, connection))
{
// Add parameters
command.Parameters.AddWithValue("@CustomerID", customerId);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
customer = new Customer
{
CustomerID = (int)reader["CustomerID"],
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
Email = reader["Email"].ToString()
};
}
}
}
return customer;
}
Executing INSERT Commands
INSERT commands add new records to your database:
csharp
public int AddCustomer(Customer customer)
{
string sql = @"
INSERT INTO Customers (FirstName, LastName, Email)
VALUES (@FirstName, @LastName, @Email);
SELECT SCOPE_IDENTITY()";
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(sql, connection))
{
// Add parameters
command.Parameters.AddWithValue("@FirstName", customer.FirstName);
command.Parameters.AddWithValue("@LastName", customer.LastName);
command.Parameters.AddWithValue("@Email", customer.Email);
connection.Open();
// ExecuteScalar returns the first column of the first row
// In this case, it's the newly created ID
int newId = Convert.ToInt32(command.ExecuteScalar());
return newId;
}
}
Executing UPDATE Commands
UPDATE commands modify existing records:
csharp
public bool UpdateCustomerEmail(int customerId, string newEmail)
{
string sql = "UPDATE Customers SET Email = @Email WHERE CustomerID = @CustomerID";
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(sql, connection))
{
// Add parameters
command.Parameters.AddWithValue("@Email", newEmail);
command.Parameters.AddWithValue("@CustomerID", customerId);
connection.Open();
// ExecuteNonQuery returns the number of affected rows
int rowsAffected = command.ExecuteNonQuery();
return rowsAffected > 0;
}
}
Executing DELETE Commands
DELETE commands remove records from your database:
csharp
public bool DeleteCustomer(int customerId)
{
string sql = "DELETE FROM Customers WHERE CustomerID = @CustomerID";
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand(sql, connection))
{
// Add parameters
command.Parameters.AddWithValue("@CustomerID", customerId);
connection.Open();
// ExecuteNonQuery returns the number of affected rows
int rowsAffected = command.ExecuteNonQuery();
return rowsAffected > 0;
}
}
Working with Transactions
When you need to execute multiple SQL commands as a single unit of work, use transactions:
csharp
public bool TransferFunds(int fromAccountId, int toAccountId, decimal amount)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Start a transaction
using (SqlTransaction transaction = connection.BeginTransaction())
{
try
{
// Withdraw from the first account
string withdrawSql = "UPDATE Accounts SET Balance = Balance - @Amount WHERE AccountID = @FromAccountID";
using (SqlCommand withdrawCommand = new SqlCommand(withdrawSql, connection, transaction))
{
withdrawCommand.Parameters.AddWithValue("@Amount", amount);
withdrawCommand.Parameters.AddWithValue("@FromAccountID", fromAccountId);
withdrawCommand.ExecuteNonQuery();
}
// Deposit to the second account
string depositSql = "UPDATE Accounts SET Balance = Balance + @Amount WHERE AccountID = @ToAccountID";
using (SqlCommand depositCommand = new SqlCommand(depositSql, connection, transaction))
{
depositCommand.Parameters.AddWithValue("@Amount", amount);
depositCommand.Parameters.AddWithValue("@ToAccountID", toAccountId);
depositCommand.ExecuteNonQuery();
}
// Commit the transaction
transaction.Commit();
return true;
}
catch (Exception)
{
// Roll back the transaction if something goes wrong
transaction.Rollback();
return false;
}
}
}
}
Executing Stored Procedures
Stored procedures are precompiled SQL statements stored in the database. They can be executed like this:
csharp
public List<Customer> GetCustomersByState(string state)
{
List<Customer> customers = new List<Customer>();
using (SqlConnection connection = new SqlConnection(connectionString))
using (SqlCommand command = new SqlCommand("GetCustomersByState", connection))
{
// Specify that we're calling a stored procedure
command.CommandType = CommandType.StoredProcedure;
// Add parameters
command.Parameters.AddWithValue("@State", state);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
Customer customer = new Customer
{
CustomerID = (int)reader["CustomerID"],
FirstName = reader["FirstName"].ToString(),
LastName = reader["LastName"].ToString(),
Email = reader["Email"].ToString()
};
customers.Add(customer);
}
}
}
return customers;
}
Real-World Application Example: Simple Order System
Let's see a more integrated example of how these commands can work together in a simple order processing system:
csharp
public class OrderRepository
{
private readonly string connectionString;
public OrderRepository(string connString)
{
connectionString = connString;
}
public int CreateOrder(Order order)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Use a transaction since we're making multiple related changes
using (SqlTransaction transaction = connection.BeginTransaction())
{
try
{
// Create the order
string orderSql = @"
INSERT INTO Orders (CustomerID, OrderDate, TotalAmount)
VALUES (@CustomerID, @OrderDate, @TotalAmount);
SELECT SCOPE_IDENTITY()";
int orderId;
using (SqlCommand command = new SqlCommand(orderSql, connection, transaction))
{
command.Parameters.AddWithValue("@CustomerID", order.CustomerId);
command.Parameters.AddWithValue("@OrderDate", order.OrderDate);
command.Parameters.AddWithValue("@TotalAmount", order.TotalAmount);
// Get the new order ID
orderId = Convert.ToInt32(command.ExecuteScalar());
}
// Create the order details
string detailsSql = @"
INSERT INTO OrderDetails (OrderID, ProductID, Quantity, UnitPrice)
VALUES (@OrderID, @ProductID, @Quantity, @UnitPrice)";
foreach (OrderDetail detail in order.OrderDetails)
{
using (SqlCommand command = new SqlCommand(detailsSql, connection, transaction))
{
command.Parameters.AddWithValue("@OrderID", orderId);
command.Parameters.AddWithValue("@ProductID", detail.ProductId);
command.Parameters.AddWithValue("@Quantity", detail.Quantity);
command.Parameters.AddWithValue("@UnitPrice", detail.UnitPrice);
command.ExecuteNonQuery();
}
// Update inventory
string inventorySql = @"
UPDATE Products
SET StockQuantity = StockQuantity - @Quantity
WHERE ProductID = @ProductID";
using (SqlCommand command = new SqlCommand(inventorySql, connection, transaction))
{
command.Parameters.AddWithValue("@ProductID", detail.ProductId);
command.Parameters.AddWithValue("@Quantity", detail.Quantity);
command.ExecuteNonQuery();
}
}
// Commit the transaction
transaction.Commit();
return orderId;
}
catch (Exception)
{
// Something went wrong, roll back the transaction
transaction.Rollback();
throw;
}
}
}
}
// Other methods for retrieving and managing orders
}
Best Practices for SQL Commands in C#
- Always use parameterized queries to prevent SQL injection attacks.
- Properly dispose of connections, commands, and readers using
usingstatements. - Use transactions when you need to ensure multiple operations succeed or fail as a unit.
- Handle exceptions appropriately, especially for database operations.
- Consider using stored procedures for complex operations.
- Avoid executing unnecessary queries by batching operations when possible.
Common Mistakes to Avoid
-
String concatenation in SQL queries - This can lead to SQL injection vulnerabilities:
csharp// DON'T DO THIS:
string sql = "SELECT * FROM Customers WHERE CustomerID = " + customerId;
// DO THIS INSTEAD:
string sql = "SELECT * FROM Customers WHERE CustomerID = @CustomerID";
command.Parameters.AddWithValue("@CustomerID", customerId); -
Not closing connections - Always close or dispose connections when you're done with them.
-
Executing too many individual queries - Consider using batch operations or stored procedures for efficiency.
-
Opening connections too early or leaving them open too long - Open connections right before you need them and close them as soon as you're done.
Summary
In this guide, you've learned:
- How to execute different types of SQL commands in C#
- Working with SELECT, INSERT, UPDATE, and DELETE operations
- Using parameters to prevent SQL injection
- Managing transactions for coordinated operations
- Executing stored procedures
- Following best practices for database operations
SQL commands in C# follow a consistent pattern:
- Create a connection
- Create a command
- Set parameters (if needed)
- Open the connection
- Execute the command
- Process the results
- Close the connection
By mastering these concepts, you'll be able to build robust database-driven applications using C#.
Further Resources and Exercises
Resources
Exercises
- Create a simple console application that connects to a database and displays all records from a table.
- Modify the application to allow inserting new records using user input.
- Add functionality to search for records using parameterized queries.
- Implement transaction-based functionality to transfer a value from one record to another.
- Create a small CRUD (Create, Read, Update, Delete) application for a simple database table.
By completing these exercises, you'll gain practical experience with SQL commands in C# and be better prepared to implement database functionality in your applications.
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)