Imagine you're building an e-commerce checkout flow. When customers complete a purchase, you need to validate inventory, reserve stock, calculate shipping, apply discounts, and record the transaction—all within a single database transaction. A stored procedure handles this multi-step logic atomically, and ADO.NET gives you the tools to call it safely.
Stored procedures encapsulate database operations, enforce security through permissions, and improve performance through query plan caching. ADO.NET's SqlCommand class provides a structured way to execute these procedures with input parameters, output values, and return codes. You'll avoid SQL injection risks while maintaining full control over parameter types and directions.
By the end of this article, you'll know how to execute stored procedures with various parameter types, retrieve output values and return codes, handle result sets, and integrate async patterns for scalable applications. You'll also see working examples that you can adapt to your own projects.
The most straightforward scenario is calling a stored procedure with input parameters and no return data. You set CommandType to StoredProcedure, add parameters with SqlParameter, and execute with ExecuteNonQuery. This approach works for INSERT, UPDATE, DELETE operations, and procedures that don't return result sets.
Here's how to call a stored procedure that updates customer information. The procedure takes a customer ID and new email address, performs validation on the server side, and updates the record.
using System.Data;
using System.Data.SqlClient;
public class CustomerRepository
{
private readonly string _connectionString;
public CustomerRepository(string connectionString)
{
_connectionString = connectionString;
}
public void UpdateCustomerEmail(int customerId, string newEmail)
{
using var connection = new SqlConnection(_connectionString);
using var command = new SqlCommand("sp_UpdateCustomerEmail", connection);
command.CommandType = CommandType.StoredProcedure;
// Add input parameters
command.Parameters.Add(new SqlParameter("@CustomerId", SqlDbType.Int)
{
Value = customerId
});
command.Parameters.Add(new SqlParameter("@NewEmail", SqlDbType.NVarChar, 255)
{
Value = newEmail
});
connection.Open();
int rowsAffected = command.ExecuteNonQuery();
Console.WriteLine($"Rows affected: {rowsAffected}");
}
}The CommandType.StoredProcedure setting tells ADO.NET to treat the command text as a procedure name, not a SQL query. Parameters are strongly typed with SqlDbType, preventing type mismatches and enabling better query plan optimization. ExecuteNonQuery returns the number of affected rows, which you can use to verify the operation succeeded.
Many stored procedures return data through output parameters or return codes. Output parameters pass values back to the caller after execution, while return values provide a status code (typically 0 for success). You configure these by setting the Direction property on SqlParameter.
This example calls a procedure that creates a new order and returns the generated order ID through an output parameter. The procedure also returns a status code indicating success or failure.
using System.Data;
using System.Data.SqlClient;
public class OrderService
{
private readonly string _connectionString;
public OrderService(string connectionString)
{
_connectionString = connectionString;
}
public (int orderId, int statusCode) CreateOrder(
int customerId, decimal totalAmount)
{
using var connection = new SqlConnection(_connectionString);
using var command = new SqlCommand("sp_CreateOrder", connection);
command.CommandType = CommandType.StoredProcedure;
// Input parameters
command.Parameters.Add("@CustomerId", SqlDbType.Int).Value = customerId;
command.Parameters.Add("@TotalAmount", SqlDbType.Decimal).Value =
totalAmount;
// Output parameter for new order ID
var orderIdParam = new SqlParameter("@OrderId", SqlDbType.Int)
{
Direction = ParameterDirection.Output
};
command.Parameters.Add(orderIdParam);
// Return value parameter
var returnParam = new SqlParameter("@ReturnValue", SqlDbType.Int)
{
Direction = ParameterDirection.ReturnValue
};
command.Parameters.Add(returnParam);
connection.Open();
command.ExecuteNonQuery();
int orderId = (int)orderIdParam.Value;
int statusCode = (int)returnParam.Value;
return (orderId, statusCode);
}
}Output parameters remain unset until ExecuteNonQuery completes. The Direction.Output setting tells ADO.NET to expect a value from the server. Return values use Direction.ReturnValue and conventionally appear as the first parameter in T-SQL procedures, though you add them last in ADO.NET code. Always check for DBNull before casting output values if the procedure might return NULL.
Stored procedures that execute SELECT statements return result sets. You read these with ExecuteReader, which provides a forward-only cursor over the data. This approach is memory-efficient since it streams rows instead of loading everything into memory at once.
Here's a procedure that searches for products by category and returns matching records. We use SqlDataReader to process each row and map it to a Product object.
using System.Data;
using System.Data.SqlClient;
public record Product(int Id, string Name, decimal Price, int Stock);
public class ProductRepository
{
private readonly string _connectionString;
public ProductRepository(string connectionString)
{
_connectionString = connectionString;
}
public List<Product> GetProductsByCategory(int categoryId, int maxPrice)
{
var products = new List<Product>();
using var connection = new SqlConnection(_connectionString);
using var command = new SqlCommand("sp_GetProductsByCategory", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@CategoryId", SqlDbType.Int).Value = categoryId;
command.Parameters.Add("@MaxPrice", SqlDbType.Decimal).Value = maxPrice;
connection.Open();
using var reader = command.ExecuteReader();
while (reader.Read())
{
var product = new Product(
Id: reader.GetInt32(reader.GetOrdinal("ProductId")),
Name: reader.GetString(reader.GetOrdinal("ProductName")),
Price: reader.GetDecimal(reader.GetOrdinal("Price")),
Stock: reader.GetInt32(reader.GetOrdinal("StockQuantity"))
);
products.Add(product);
}
return products;
}
}The GetOrdinal method retrieves the column index by name, which you then use with the typed Get methods like GetInt32 and GetString. This approach is more resilient to column order changes than accessing by index. For nullable columns, check reader.IsDBNull before calling the typed getter to avoid exceptions.
Some stored procedures return multiple result sets in a single call. For example, a reporting procedure might return summary statistics in the first set and detailed records in the second. You navigate between result sets using the NextResult method on SqlDataReader.
using System.Data;
using System.Data.SqlClient;
public record OrderSummary(int TotalOrders, decimal TotalRevenue);
public record OrderDetail(int OrderId, DateTime OrderDate, decimal Amount);
public class ReportService
{
private readonly string _connectionString;
public ReportService(string connectionString)
{
_connectionString = connectionString;
}
public (OrderSummary summary, List<OrderDetail> details)
GetMonthlyReport(int year, int month)
{
OrderSummary? summary = null;
var details = new List<OrderDetail>();
using var connection = new SqlConnection(_connectionString);
using var command = new SqlCommand("sp_GetMonthlyReport", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@Year", SqlDbType.Int).Value = year;
command.Parameters.Add("@Month", SqlDbType.Int).Value = month;
connection.Open();
using var reader = command.ExecuteReader();
// First result set: summary
if (reader.Read())
{
summary = new OrderSummary(
TotalOrders: reader.GetInt32(0),
TotalRevenue: reader.GetDecimal(1)
);
}
// Move to second result set: details
if (reader.NextResult())
{
while (reader.Read())
{
details.Add(new OrderDetail(
OrderId: reader.GetInt32(0),
OrderDate: reader.GetDateTime(1),
Amount: reader.GetDecimal(2)
));
}
}
return (summary ?? new OrderSummary(0, 0m), details);
}
}The NextResult method advances to the next result set and returns false when no more sets remain. This pattern lets you process heterogeneous data from a single database round trip, reducing latency compared to multiple separate calls. The reader maintains its forward-only semantics within each result set.
In web applications and services, blocking threads on database I/O hurts scalability. ADO.NET provides async methods that free threads while waiting for the database to respond. This is essential for ASP.NET Core applications where thread pool starvation can degrade performance under load.
using System.Data;
using System.Data.SqlClient;
public class AsyncProductService
{
private readonly string _connectionString;
public AsyncProductService(string connectionString)
{
_connectionString = connectionString;
}
public async Task<List<Product>> SearchProductsAsync(
string searchTerm, CancellationToken cancellationToken = default)
{
var products = new List<Product>();
await using var connection = new SqlConnection(_connectionString);
await using var command = new SqlCommand("sp_SearchProducts", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@SearchTerm", SqlDbType.NVarChar, 100).Value =
searchTerm;
await connection.OpenAsync(cancellationToken);
await using var reader = await command.ExecuteReaderAsync(
cancellationToken);
while (await reader.ReadAsync(cancellationToken))
{
products.Add(new Product(
Id: reader.GetInt32(0),
Name: reader.GetString(1),
Price: reader.GetDecimal(2),
Stock: reader.GetInt32(3)
));
}
return products;
}
public async Task<int> UpdateProductPriceAsync(
int productId, decimal newPrice, CancellationToken cancellationToken)
{
await using var connection = new SqlConnection(_connectionString);
await using var command = new SqlCommand("sp_UpdatePrice", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add("@ProductId", SqlDbType.Int).Value = productId;
command.Parameters.Add("@NewPrice", SqlDbType.Decimal).Value = newPrice;
await connection.OpenAsync(cancellationToken);
return await command.ExecuteNonQueryAsync(cancellationToken);
}
}The async methods—OpenAsync, ExecuteReaderAsync, ReadAsync, and ExecuteNonQueryAsync—all accept a CancellationToken. This enables request cancellation when users navigate away or when timeouts occur. Always await these methods and avoid mixing sync and async calls on the same connection, which can cause deadlocks.
When integrating ADO.NET stored procedure calls into production applications, always use connection pooling by keeping connection strings consistent. The SqlConnection class pools connections automatically, so creating and disposing connections frequently is fine and actually recommended over trying to manage connection lifetime manually.
For error handling, wrap database calls in try-catch blocks that specifically catch SqlException. Inspect the Number property to handle specific errors like deadlocks (1205) or duplicate keys (2627) differently from general failures. Log the entire exception including stack trace and parameter values (sanitized) for troubleshooting.
Consider implementing retry logic with exponential backoff for transient failures. Libraries like Polly make this straightforward. Detect transient errors by checking SqlException.Number against known transient codes like timeout (−2) or connection failures.
For configuration, store connection strings in appsettings.json or Azure Key Vault, never in code. Use IConfiguration in ASP.NET Core to inject connection strings into repositories. Enable sensitive data logging only in development to avoid leaking credentials in production logs.
Build a console application that demonstrates calling a stored procedure with parameters and output values. This sample creates a mock procedure in memory using string formatting for demonstration purposes.
dotnet new console -n AdoNetDemocd AdoNetDemodotnet run<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net8.0</TargetFramework>
<Nullable>enable</Nullable>
<ImplicitUsings>enable</ImplicitUsings>
</PropertyGroup>
<ItemGroup>
<PackageReference Include="System.Data.SqlClient" Version="4.8.6" />
</ItemGroup>
</Project>using System.Data;
using System.Data.SqlClient;
// Simulated demonstration of stored procedure calls
Console.WriteLine("=== ADO.NET Stored Procedure Demo ===\n");
// Simulate calling a procedure with input parameters
Console.WriteLine("1. Calling sp_UpdateCustomerEmail");
Console.WriteLine(" Input: CustomerId=123, Email='new@example.com'");
Console.WriteLine(" Result: 1 row affected\n");
// Simulate calling a procedure with output parameters
Console.WriteLine("2. Calling sp_CreateOrder");
Console.WriteLine(" Input: CustomerId=456, Amount=99.99");
Console.WriteLine(" Output: OrderId=789");
Console.WriteLine(" ReturnValue: 0 (success)\n");
// Simulate reading a result set
Console.WriteLine("3. Calling sp_GetProductsByCategory");
Console.WriteLine(" Input: CategoryId=5, MaxPrice=50.00");
Console.WriteLine(" Results:");
Console.WriteLine(" - Product #1: Widget (Price: $19.99, Stock: 100)");
Console.WriteLine(" - Product #2: Gadget (Price: $29.99, Stock: 50)");
Console.WriteLine(" - Product #3: Tool (Price: $39.99, Stock: 75)\n");
Console.WriteLine("Key patterns demonstrated:");
Console.WriteLine("- CommandType.StoredProcedure for type safety");
Console.WriteLine("- SqlParameter for input/output values");
Console.WriteLine("- ExecuteNonQuery for commands without results");
Console.WriteLine("- ExecuteReader for result sets");
Console.WriteLine("- Proper disposal with using statements");=== ADO.NET Stored Procedure Demo ===
1. Calling sp_UpdateCustomerEmail
Input: CustomerId=123, Email='new@example.com'
Result: 1 row affected
2. Calling sp_CreateOrder
Input: CustomerId=456, Amount=99.99
Output: OrderId=789
ReturnValue: 0 (success)
3. Calling sp_GetProductsByCategory
Input: CategoryId=5, MaxPrice=50.00
Results:
- Product #1: Widget (Price: $19.99, Stock: 100)
- Product #2: Gadget (Price: $29.99, Stock: 50)
- Product #3: Tool (Price: $39.99, Stock: 75)
Key patterns demonstrated:
- CommandType.StoredProcedure for type safety
- SqlParameter for input/output values
- ExecuteNonQuery for commands without results
- ExecuteReader for result sets
- Proper disposal with using statementsAlways use CommandType.StoredProcedure with parameterized SqlCommand. Building SQL strings opens you to injection attacks and loses query plan caching benefits. The typed approach also validates parameter names against the procedure at runtime, catching mismatches early.
Use DBNull.Value when passing NULL to SQL. C# null converts incorrectly. Set parameter.Value = myValue ?? (object)DBNull.Value to handle nullable types safely. For output parameters, check IsDBNull before reading values to avoid cast exceptions.
Output parameters remain unset until you fully consume the result set. Call reader.Close() or use ExecuteNonQuery before accessing output values. Also, specify Size for string output parameters to avoid truncation. Set Direction to Output or InputOutput explicitly.
Reusing SqlCommand works but requires clearing parameters between calls and ensuring thread safety. It's easier and safer to create a new command per operation. Connection pooling handles the expensive resource, so command reuse offers minimal benefit while adding complexity.
Yes, use ExecuteNonQueryAsync, ExecuteScalarAsync, and ExecuteReaderAsync for async operations. Pass a CancellationToken to enable query cancellation. This prevents thread blocking during I/O and improves scalability in web applications. Always await database calls in ASP.NET Core.