Introduction

Executing SQL directly in C# is common for backend services, maintenance tools, and migration utilities. The most reliable path is ADO.NET with parameterized commands, explicit connection scope, and predictable transaction boundaries. Direct SQL can be fast and clear, but only when safety and observability are treated as first-class requirements.

Core Sections

Open connections late and close them early

A short connection lifetime reduces lock contention and resource pressure. In C#, using and await using make this straightforward.

1using Microsoft.Data.SqlClient;
2
3var connectionString = "Server=localhost;Database=appdb;User Id=app;Password=secret;TrustServerCertificate=True";
4
5await using var conn = new SqlConnection(connectionString);
6await conn.OpenAsync();
7
8Console.WriteLine(conn.State); // Open

Avoid holding one global open connection for an entire process. Connection pooling already handles reuse efficiently, and short scopes are easier to reason about under load.

Read rows with parameterized commands

Parameterized queries protect against SQL injection and preserve type information. They also improve query plan stability compared with dynamic string concatenation.

1using Microsoft.Data.SqlClient;
2
3await using var conn = new SqlConnection(connectionString);
4await conn.OpenAsync();
5
6const string sql = @"
7SELECT Id, Email, CreatedAt
8FROM Users
9WHERE IsActive = @isActive AND CreatedAt >= @cutoff";
10
11await using var cmd = new SqlCommand(sql, conn);
12cmd.Parameters.AddWithValue("@isActive", true);
13cmd.Parameters.AddWithValue("@cutoff", DateTime.UtcNow.AddDays(-30));
14
15await using var reader = await cmd.ExecuteReaderAsync();
16while (await reader.ReadAsync())
17{
18    var id = reader.GetInt32(0);
19    var email = reader.GetString(1);
20    var createdAt = reader.GetDateTime(2);
21    Console.WriteLine($"{id} | {email} | {createdAt:O}");
22}

When you only need a single value, use ExecuteScalarAsync instead of a full reader loop. It keeps intent clear and reduces boilerplate.

Execute write operations in explicit transactions

For inserts, updates, and deletes that must succeed together, wrap operations in a transaction. Commit only after every statement succeeds.

1await using var conn = new SqlConnection(connectionString);
2await conn.OpenAsync();
3
4await using var tx = await conn.BeginTransactionAsync();
5try
6{
7    await using var debit = new SqlCommand(
8        "UPDATE Accounts SET Balance = Balance - @amount WHERE Id = @id",
9        conn,
10        (SqlTransaction)tx
11    );
12    debit.Parameters.AddWithValue("@amount", 100m);
13    debit.Parameters.AddWithValue("@id", 10);
14
15    await using var credit = new SqlCommand(
16        "UPDATE Accounts SET Balance = Balance + @amount WHERE Id = @id",
17        conn,
18        (SqlTransaction)tx
19    );
20    credit.Parameters.AddWithValue("@amount", 100m);
21    credit.Parameters.AddWithValue("@id", 20);
22
23    var rows1 = await debit.ExecuteNonQueryAsync();
24    var rows2 = await credit.ExecuteNonQueryAsync();
25
26    if (rows1 != 1 || rows2 != 1)
27    {
28        throw new InvalidOperationException("Unexpected row count during transfer.");
29    }
30
31    await tx.CommitAsync();
32}
33catch
34{
35    await tx.RollbackAsync();
36    throw;
37}

This pattern prevents partial writes when a command fails midway through a workflow.

Add basic operational safeguards

Set command timeouts for queries that can degrade under load. Log query identity and duration, but avoid logging raw sensitive parameter values. Keep SQL text in well-named constants or files so code review can track changes.

If your service runs in parallel, confirm transaction isolation behavior against real workloads. Some bugs appear only under contention, not during local single-user tests.

For recurring operational queries, store SQL in version control and review it like application code. This prevents silent drift between environments and keeps risky statements visible in pull requests. If a query is intended for diagnostics only, label it clearly and limit execution permissions. Clear intent plus controlled access is usually the difference between safe troubleshooting and an accidental production write.

Common Pitfalls

• Concatenating user input into SQL strings instead of using parameters.
• Keeping connections open across unrelated business operations.
• Omitting transactions for multi-step write workflows.
• Ignoring command timeout settings in high-latency environments.
• Logging raw secrets or personal data while debugging query issues.

Summary

• Use ADO.NET commands with parameters for direct SQL execution.
• Scope connections tightly and rely on pooling for reuse.
• Wrap related writes in explicit transactions with rollback.
• Choose ExecuteReader, ExecuteScalar, or ExecuteNonQuery based on intent.
• Add timeout and logging discipline to keep production behavior observable.