.NET SQL Commands
Introduction
When building applications that need to store and retrieve data, databases are essential tools in a developer's arsenal. .NET provides robust mechanisms to interact with databases using SQL (Structured Query Language) commands. This guide will walk you through how to execute SQL commands in .NET applications, from basic queries to more advanced operations.
Whether you're building a small application or an enterprise system, understanding how to effectively communicate with databases using .NET is a fundamental skill every developer should master.
Understanding ADO.NET
ADO.NET is the primary data access technology in the .NET framework. It provides a set of classes that allow you to interact with various data sources, including SQL Server, MySQL, Oracle, and more.
The key components of ADO.NET include:
- Connection: Establishes a connection to a specific data source
- Command: Contains the SQL commands to execute against the database
- DataReader: Provides a forward-only, read-only stream of data from the database
- DataSet/DataAdapter: For disconnected data access that can work offline
Setting Up Database Connections
Before executing SQL commands, you need to establish a connection to your database. Here's how to create a connection to a SQL Server database:
csharp
using System.Data.SqlClient; // For SQL Server
// Create a connection string
string connectionString = "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;";
// Initialize the connection
using (SqlConnection connection = new SqlConnection(connectionString))
{
try
{
// Open the connection
connection.Open();
Console.WriteLine("Connection opened successfully!");
// Your SQL commands will go here
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
}
// Connection is automatically closed when exiting the using block
}
For other database providers, you'll need different namespaces and connection strings:
- For MySQL:
MySql.Data.MySqlClient - For SQLite:
Microsoft.Data.Sqlite - For Oracle:
Oracle.ManagedDataAccess.Client
Basic SQL Commands in .NET
SELECT - Retrieving Data
Let's start with the most common operation: retrieving data from a database.
csharp
using System;
using System.Data.SqlClient;
public void RetrieveData()
{
string connectionString = "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
// SQL SELECT command
string sql = "SELECT Id, Name, Email FROM Customers";
// Create command
using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
// Execute and read data
using (SqlDataReader reader = command.ExecuteReader())
{
// Check if there are rows
if (reader.HasRows)
{
Console.WriteLine("Customer List:");
Console.WriteLine("ID | Name | Email");
Console.WriteLine("----------------");
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
string email = reader.GetString(2);
Console.WriteLine($"{id} | {name} | {email}");
}
}
else
{
Console.WriteLine("No customers found.");
}
}
}
}
}
Output:
Customer List:
ID | Name | Email
----------------
1 | John Doe | [email protected]
2 | Jane Smith | [email protected]
3 | Robert Johnson | [email protected]
INSERT - Adding Data
Adding new records to your database is another common operation:
csharp
public void InsertData(string name, string email)
{
string connectionString = "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
// SQL INSERT command
string sql = "INSERT INTO Customers (Name, Email) VALUES (@Name, @Email)";
using (SqlCommand command = new SqlCommand(sql, connection))
{
// Add parameters to prevent SQL injection
command.Parameters.AddWithValue("@Name", name);
command.Parameters.AddWithValue("@Email", email);
connection.Open();
// ExecuteNonQuery returns the number of rows affected
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine($"{rowsAffected} row(s) inserted successfully.");
}
}
}
UPDATE - Modifying Data
Updating existing records is done similarly to inserting:
csharp
public void UpdateCustomerEmail(int customerId, string newEmail)
{
string connectionString = "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
// SQL UPDATE command
string sql = "UPDATE Customers SET Email = @Email WHERE Id = @CustomerId";
using (SqlCommand command = new SqlCommand(sql, connection))
{
// Add parameters
command.Parameters.AddWithValue("@Email", newEmail);
command.Parameters.AddWithValue("@CustomerId", customerId);
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected > 0)
Console.WriteLine($"Customer ID {customerId}'s email updated successfully.");
else
Console.WriteLine($"No customer found with ID {customerId}.");
}
}
}
DELETE - Removing Data
Removing records from the database:
csharp
public void DeleteCustomer(int customerId)
{
string connectionString = "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
// SQL DELETE command
string sql = "DELETE FROM Customers WHERE Id = @CustomerId";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@CustomerId", customerId);
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected > 0)
Console.WriteLine($"Customer ID {customerId} deleted successfully.");
else
Console.WriteLine($"No customer found with ID {customerId}.");
}
}
}
Parameterized Queries
You may have noticed the @Name, @Email, etc. in our previous examples. These are parameters, and using them is crucial for:
- Preventing SQL injection - A serious security vulnerability
- Improving performance - The database can reuse execution plans
- Handling different data types correctly
Never concatenate strings to build SQL commands as it opens your application to SQL injection attacks:
❌ Bad practice (vulnerable to SQL injection):
csharp
// DO NOT DO THIS!
string sql = "SELECT * FROM Users WHERE Username = '" + userInput + "'";
✅ Good practice (using parameters):
csharp
string sql = "SELECT * FROM Users WHERE Username = @Username";
command.Parameters.AddWithValue("@Username", userInput);
Working with Stored Procedures
Stored procedures are precompiled SQL statements stored in the database. They can improve performance and security:
csharp
public void CallStoredProcedure(int customerId)
{
string connectionString = "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Create command for stored procedure
using (SqlCommand command = new SqlCommand("GetCustomerDetails", connection))
{
// Specify that we're calling a stored procedure
command.CommandType = System.Data.CommandType.StoredProcedure;
// Add parameters
command.Parameters.AddWithValue("@CustomerId", customerId);
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.Read())
{
string name = reader["Name"].ToString();
string email = reader["Email"].ToString();
Console.WriteLine($"Customer Details - Name: {name}, Email: {email}");
}
else
{
Console.WriteLine("Customer not found.");
}
}
}
}
}
Handling Transactions
Transactions ensure that a series of SQL commands either all succeed or all fail, maintaining data integrity:
csharp
public void TransferFunds(int fromAccountId, int toAccountId, decimal amount)
{
string connectionString = "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// Create a transaction
using (SqlTransaction transaction = connection.BeginTransaction())
{
try
{
// Command 1: Withdraw from account
using (SqlCommand withdrawCommand = new SqlCommand(
"UPDATE Accounts SET Balance = Balance - @Amount WHERE Id = @AccountId", connection, transaction))
{
withdrawCommand.Parameters.AddWithValue("@Amount", amount);
withdrawCommand.Parameters.AddWithValue("@AccountId", fromAccountId);
int withdrawResult = withdrawCommand.ExecuteNonQuery();
if (withdrawResult != 1)
throw new Exception("Failed to withdraw funds.");
}
// Command 2: Deposit to account
using (SqlCommand depositCommand = new SqlCommand(
"UPDATE Accounts SET Balance = Balance + @Amount WHERE Id = @AccountId", connection, transaction))
{
depositCommand.Parameters.AddWithValue("@Amount", amount);
depositCommand.Parameters.AddWithValue("@AccountId", toAccountId);
int depositResult = depositCommand.ExecuteNonQuery();
if (depositResult != 1)
throw new Exception("Failed to deposit funds.");
}
// If we get here, both operations were successful
transaction.Commit();
Console.WriteLine($"Successfully transferred ${amount} from account {fromAccountId} to {toAccountId}");
}
catch (Exception ex)
{
// If anything goes wrong, roll back changes
transaction.Rollback();
Console.WriteLine($"Transaction failed: {ex.Message}");
}
}
}
}
Executing Scalar Commands
Sometimes you need just a single value from the database (e.g., COUNT, SUM, MAX). The ExecuteScalar method is perfect for this:
csharp
public decimal GetAccountBalance(int accountId)
{
string connectionString = "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
string sql = "SELECT Balance FROM Accounts WHERE Id = @AccountId";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@AccountId", accountId);
connection.Open();
// ExecuteScalar returns the first column of the first row
object result = command.ExecuteScalar();
if (result != null && result != DBNull.Value)
return Convert.ToDecimal(result);
else
throw new Exception("Account not found");
}
}
}
Real-World Example: Customer Management System
Let's put everything together with a more complete example of a simple customer management system:
csharp
using System;
using System.Data.SqlClient;
public class CustomerManager
{
private readonly string _connectionString;
public CustomerManager(string connectionString)
{
_connectionString = connectionString;
}
public void CreateCustomer(string name, string email, string phone)
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
string sql = @"
INSERT INTO Customers (Name, Email, Phone, CreatedDate)
VALUES (@Name, @Email, @Phone, @CreatedDate);
SELECT SCOPE_IDENTITY();"; // Returns the newly created ID
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@Name", name);
command.Parameters.AddWithValue("@Email", email);
command.Parameters.AddWithValue("@Phone", phone);
command.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
connection.Open();
// Get the new customer's ID
int newCustomerId = Convert.ToInt32(command.ExecuteScalar());
Console.WriteLine($"New customer created with ID: {newCustomerId}");
}
}
}
public void SearchCustomers(string searchTerm)
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
string sql = @"
SELECT Id, Name, Email, Phone
FROM Customers
WHERE Name LIKE @SearchTerm OR Email LIKE @SearchTerm OR Phone LIKE @SearchTerm
ORDER BY Name";
using (SqlCommand command = new SqlCommand(sql, connection))
{
command.Parameters.AddWithValue("@SearchTerm", "%" + searchTerm + "%");
connection.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
Console.WriteLine("Search Results:");
Console.WriteLine("ID | Name | Email | Phone");
Console.WriteLine("-------------------------");
while (reader.Read())
{
int id = reader.GetInt32(0);
string name = reader.GetString(1);
string email = reader.GetString(2);
string phone = reader.GetString(3);
Console.WriteLine($"{id} | {name} | {email} | {phone}");
}
}
else
{
Console.WriteLine("No matching customers found.");
}
}
}
}
}
public void UpdateCustomer(int id, string name, string email, string phone)
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
// Start a transaction for multiple updates
connection.Open();
using (SqlTransaction transaction = connection.BeginTransaction())
{
try
{
string sql = @"
UPDATE Customers
SET Name = @Name, Email = @Email, Phone = @Phone, LastUpdated = @LastUpdated
WHERE Id = @Id";
using (SqlCommand command = new SqlCommand(sql, connection, transaction))
{
command.Parameters.AddWithValue("@Id", id);
command.Parameters.AddWithValue("@Name", name);
command.Parameters.AddWithValue("@Email", email);
command.Parameters.AddWithValue("@Phone", phone);
command.Parameters.AddWithValue("@LastUpdated", DateTime.Now);
int rowsAffected = command.ExecuteNonQuery();
if (rowsAffected == 0)
{
throw new Exception($"Customer with ID {id} not found.");
}
// Log the update in an audit table
string auditSql = @"
INSERT INTO CustomerUpdateLog (CustomerId, UpdatedBy, UpdateDate)
VALUES (@CustomerId, @UpdatedBy, @UpdateDate)";
using (SqlCommand auditCommand = new SqlCommand(auditSql, connection, transaction))
{
auditCommand.Parameters.AddWithValue("@CustomerId", id);
auditCommand.Parameters.AddWithValue("@UpdatedBy", Environment.UserName);
auditCommand.Parameters.AddWithValue("@UpdateDate", DateTime.Now);
auditCommand.ExecuteNonQuery();
}
// Commit both operations
transaction.Commit();
Console.WriteLine($"Customer {id} updated successfully.");
}
}
catch (Exception ex)
{
transaction.Rollback();
Console.WriteLine($"Error updating customer: {ex.Message}");
}
}
}
}
public int GetCustomerCount()
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
string sql = "SELECT COUNT(*) FROM Customers";
using (SqlCommand command = new SqlCommand(sql, connection))
{
connection.Open();
return (int)command.ExecuteScalar();
}
}
}
}
Example usage:
csharp
// Example of using the CustomerManager class
public static void Main()
{
string connectionString = "Server=myServerAddress;Database=myDatabase;User Id=myUsername;Password=myPassword;";
CustomerManager manager = new CustomerManager(connectionString);
// Add a new customer
manager.CreateCustomer("Maria Garcia", "[email protected]", "555-123-4567");
// Search for customers
manager.SearchCustomers("maria");
// Update a customer
manager.UpdateCustomer(1, "Maria Rodriguez", "[email protected]", "555-123-9876");
// Get total count
int totalCustomers = manager.GetCustomerCount();
Console.WriteLine($"Total customers in database: {totalCustomers}");
}
Best Practices for .NET SQL Commands
- Always use parameterized queries to prevent SQL injection attacks
- Properly dispose of connections using the
usingstatement - Use transactions when performing multiple related operations
- Handle exceptions properly and include appropriate error logging
- Don't store connection strings in code - use configuration files or secure storage
- Consider using an ORM (Object-Relational Mapper) like Entity Framework for complex applications
- Monitor performance of your SQL commands and optimize when necessary
- Use stored procedures for complex or frequently used queries
- Implement retry logic for transient errors in cloud environments
- Don't trust user input - always validate and sanitize
Common Mistakes to Avoid
- String concatenation in SQL commands: Always use parameters instead
- Keeping connections open for too long: Open connections just before use and close them immediately after
- Ignoring SQL injection risks: This is one of the most common security vulnerabilities
- Not handling NULL values properly: Always check for DBNull.Value when reading data
- Hardcoding connection strings: Use configuration files instead
Summary
In this guide, we've covered the fundamentals of executing SQL commands in .NET applications:
- Setting up database connections using ADO.NET
- Executing basic SQL commands (SELECT, INSERT, UPDATE, DELETE)
- Working with parameterized queries to prevent SQL injection
- Using stored procedures for improved security and performance
- Implementing transactions to maintain data integrity
- Building a simple real-world customer management system
Understanding these concepts provides a solid foundation for building data-driven applications with .NET. As you become more comfortable with these techniques, you might want to explore more advanced topics like Entity Framework, which provides a higher-level abstraction for database operations.
Additional Resources
- Microsoft Documentation: ADO.NET
- SQL Server Connection Strings
- OWASP SQL Injection Prevention Cheat Sheet
- Entity Framework Documentation
Practice Exercises
- Create a simple console application that connects to a database and retrieves a list of products.
- Implement a method that allows adding new products with validation.
- Create a transaction that transfers inventory from one warehouse to another, ensuring data consistency.
- Build a search feature that allows searching products by name, category, or price range using parameterized queries.
- Implement a data access layer that abstracts database operations from the rest of your application.
By completing these exercises, you'll gain practical experience with .NET SQL commands and be well on your way to building robust data-driven applications.
If you spot any mistakes on this website, please let me know at [email protected]. I’d greatly appreciate your feedback! :)