.NET Stored Procedures
Introduction
Stored procedures are precompiled collections of SQL statements stored in a database. They function similarly to methods or functions in programming languages, accepting parameters, performing operations, and returning results. In .NET applications, working with stored procedures provides numerous benefits over using direct SQL queries:
- Performance: Stored procedures are compiled once and cached by the database engine, making subsequent executions faster.
- Security: They help prevent SQL injection attacks by limiting direct access to database tables.
- Maintainability: Database logic can be modified without changing application code.
- Network Efficiency: Only the procedure call and parameters are sent over the network, not entire SQL statements.
This guide will walk you through how to work with stored procedures in .NET applications, from creating them in your database to executing them with parameters and handling their results.
Creating Stored Procedures
Before we can use stored procedures in our .NET application, we need to create them in the database. Here's an example of creating a basic stored procedure in SQL Server:
sql
CREATE PROCEDURE GetCustomersByCity
@City NVARCHAR(50)
AS
BEGIN
SET NOCOUNT ON;
SELECT CustomerID, FirstName, LastName, Email
FROM Customers
WHERE City = @City
ORDER BY LastName, FirstName;
END
This stored procedure accepts a @City parameter and returns customers from that city.
Executing Stored Procedures in .NET
Basic Execution
Let's start with a simple example of executing a stored procedure using ADO.NET:
csharp
using System;
using System.Data;
using System.Data.SqlClient;
public void GetCustomersFromCity(string city)
{
string connectionString = "Server=myServerAddress;Database=myDataBase;User Id=myUsername;Password=myPassword;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
// Create a command for the stored procedure
SqlCommand command = new SqlCommand("GetCustomersByCity", connection);
command.CommandType = CommandType.StoredProcedure;
// Add parameters to the command
command.Parameters.AddWithValue("@City", city);
try
{
connection.Open();
// Execute and process the results
using (SqlDataReader reader = command.ExecuteReader())
{
Console.WriteLine($"Customers in {city}:");
Console.WriteLine("------------------------------");
while (reader.Read())
{
string customerId = reader["CustomerID"].ToString();
string firstName = reader["FirstName"].ToString();
string lastName = reader["LastName"].ToString();
string email = reader["Email"].ToString();
Console.WriteLine($"{customerId}: {firstName} {lastName} - {email}");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
}
}
}
Output Example:
Customers in Seattle:
------------------------------
CUST001: John Smith - john.smith@email.com
CUST023: Sarah Johnson - sarah.j@company.com
CUST045: Michael Williams - m.williams@example.net
Using Parameters with Different Data Types
Different parameter types may require specific handling. Here's how to work with various parameter types:
csharp
public void AddNewOrder(int customerId, DateTime orderDate, decimal totalAmount)
{
string connectionString = "your_connection_string_here";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("InsertNewOrder", connection);
command.CommandType = CommandType.StoredProcedure;
// Add parameters with specific types
command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerId;
command.Parameters.Add("@OrderDate", SqlDbType.DateTime).Value = orderDate;
command.Parameters.Add("@TotalAmount", SqlDbType.Decimal).Value = totalAmount;
// Add an output parameter to get the new OrderID
SqlParameter orderIdParam = command.Parameters.Add("@OrderID", SqlDbType.Int);
orderIdParam.Direction = ParameterDirection.Output;
try
{
connection.Open();
command.ExecuteNonQuery();
// Retrieve the output parameter value
int newOrderId = (int)orderIdParam.Value;
Console.WriteLine($"New order created with ID: {newOrderId}");
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
}
}
}
Working with Different Parameter Types
Stored procedures can use different types of parameters:
- Input Parameters: Send data to the stored procedure (most common)
- Output Parameters: Return values from the stored procedure
- Input/Output Parameters: Both send and receive data
- Return Value: An integer value returned by the stored procedure
Here's a comprehensive example showcasing all types:
csharp
public void CalculateOrderStatistics(int customerId)
{
string connectionString = "your_connection_string_here";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("GetCustomerOrderStats", connection);
command.CommandType = CommandType.StoredProcedure;
// Input parameter
command.Parameters.Add("@CustomerID", SqlDbType.Int).Value = customerId;
// Output parameters
SqlParameter totalOrdersParam = command.Parameters.Add("@TotalOrders", SqlDbType.Int);
totalOrdersParam.Direction = ParameterDirection.Output;
SqlParameter avgOrderValueParam = command.Parameters.Add("@AvgOrderValue", SqlDbType.Decimal);
avgOrderValueParam.Direction = ParameterDirection.Output;
// Input/Output parameter
SqlParameter lastOrderDateParam = command.Parameters.Add("@LastOrderDate", SqlDbType.DateTime);
lastOrderDateParam.Direction = ParameterDirection.InputOutput;
lastOrderDateParam.Value = DateTime.Now; // Default value
// Return value parameter
SqlParameter returnValue = command.Parameters.Add("@ReturnVal", SqlDbType.Int);
returnValue.Direction = ParameterDirection.ReturnValue;
try
{
connection.Open();
command.ExecuteNonQuery();
// Read the output values
int totalOrders = (int)totalOrdersParam.Value;
decimal avgOrderValue = (decimal)avgOrderValueParam.Value;
DateTime lastOrderDate = (DateTime)lastOrderDateParam.Value;
int returnCode = (int)returnValue.Value;
Console.WriteLine($"Customer {customerId} Order Statistics:");
Console.WriteLine($"Total Orders: {totalOrders}");
Console.WriteLine($"Average Order Value: ${avgOrderValue:F2}");
Console.WriteLine($"Last Order Date: {lastOrderDate.ToShortDateString()}");
Console.WriteLine($"Return Code: {returnCode} (0 = Success)");
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
}
}
}
Corresponding SQL Stored Procedure:
sql
CREATE PROCEDURE GetCustomerOrderStats
@CustomerID INT,
@TotalOrders INT OUTPUT,
@AvgOrderValue DECIMAL(10, 2) OUTPUT,
@LastOrderDate DATETIME OUTPUT
AS
BEGIN
SET NOCOUNT ON;
-- Get total orders
SELECT @TotalOrders = COUNT(*)
FROM Orders
WHERE CustomerID = @CustomerID;
-- Get average order value
SELECT @AvgOrderValue = AVG(TotalAmount)
FROM Orders
WHERE CustomerID = @CustomerID;
-- Get last order date if newer than input
SELECT @LastOrderDate = MAX(OrderDate)
FROM Orders
WHERE CustomerID = @CustomerID
AND OrderDate > @LastOrderDate;
-- Return an integer status code
RETURN 0; -- 0 means success
END
Using DataSets and DataAdapters with Stored Procedures
For scenarios where you need to work with disconnected data or fill multiple tables at once, you can use DataSet and SqlDataAdapter:
csharp
public DataSet GetCustomerWithOrders(int customerId)
{
string connectionString = "your_connection_string_here";
DataSet customerData = new DataSet();
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand("GetCustomerWithOrders", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@CustomerID", customerId);
SqlDataAdapter adapter = new SqlDataAdapter(command);
try
{
connection.Open();
adapter.Fill(customerData);
// Rename tables for clarity
if (customerData.Tables.Count > 0)
{
customerData.Tables[0].TableName = "CustomerDetails";
if (customerData.Tables.Count > 1)
customerData.Tables[1].TableName = "Orders";
}
return customerData;
}
catch (Exception ex)
{
Console.WriteLine($"Error: {ex.Message}");
return null;
}
}
}
SQL Stored Procedure:
sql
CREATE PROCEDURE GetCustomerWithOrders
@CustomerID INT
AS
BEGIN
SET NOCOUNT ON;
-- Return customer data
SELECT * FROM Customers WHERE CustomerID = @CustomerID;
-- Return order data
SELECT * FROM Orders WHERE CustomerID = @CustomerID ORDER BY OrderDate DESC;
END
Using Stored Procedures with Entity Framework Core
Entity Framework Core also supports stored procedures through the FromSqlRaw method:
csharp
public List<Customer> GetCustomersByCity(string city)
{
using (var context = new MyDbContext())
{
// Call a stored procedure that returns entities
return context.Customers
.FromSqlRaw("EXEC GetCustomersByCity @City",
new SqlParameter("@City", city))
.ToList();
}
}
public void CreateCustomer(string firstName, string lastName, string email, string city)
{
using (var context = new MyDbContext())
{
// Use DbContext.Database.ExecuteSqlRaw for procedures that don't return entities
context.Database.ExecuteSqlRaw(
"EXEC CreateNewCustomer @FirstName, @LastName, @Email, @City",
new SqlParameter("@FirstName", firstName),
new SqlParameter("@LastName", lastName),
new SqlParameter("@Email", email),
new SqlParameter("@City", city));
}
}
Real-World Example: Product Inventory System
Let's build a more complex example of a product inventory system that uses stored procedures for all database operations:
csharp
public class InventorySystem
{
private readonly string _connectionString;
public InventorySystem(string connectionString)
{
_connectionString = connectionString;
}
public bool ProcessProductSale(int productId, int quantity, int locationId)
{
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlCommand command = new SqlCommand("ProcessProductSale", connection);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.AddWithValue("@ProductID", productId);
command.Parameters.AddWithValue("@Quantity", quantity);
command.Parameters.AddWithValue("@LocationID", locationId);
// Output parameters
SqlParameter successParam = command.Parameters.Add("@Success", SqlDbType.Bit);
successParam.Direction = ParameterDirection.Output;
SqlParameter messageParam = command.Parameters.Add("@Message", SqlDbType.NVarChar, 500);
messageParam.Direction = ParameterDirection.Output;
try
{
connection.Open();
command.ExecuteNonQuery();
bool success = (bool)successParam.Value;
string message = messageParam.Value.ToString();
if (!success)
{
Console.WriteLine($"Error: {message}");
}
return success;
}
catch (Exception ex)
{
Console.WriteLine($"Exception: {ex.Message}");
return false;
}
}
}
public DataTable GetInventoryReport()
{
DataTable inventoryData = new DataTable();
using (SqlConnection connection = new SqlConnection(_connectionString))
{
SqlCommand command = new SqlCommand("GetInventoryReport", connection);
command.CommandType = CommandType.StoredProcedure;
try
{
connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(command);
adapter.Fill(inventoryData);
return inventoryData;
}
catch (Exception ex)
{
Console.WriteLine($"Exception: {ex.Message}");
return null;
}
}
}
}
Sample SQL for the ProcessProductSale procedure:
sql
CREATE PROCEDURE ProcessProductSale
@ProductID INT,
@Quantity INT,
@LocationID INT,
@Success BIT OUTPUT,
@Message NVARCHAR(500) OUTPUT
AS
BEGIN
SET NOCOUNT ON;
SET @Success = 0;
BEGIN TRY
BEGIN TRANSACTION;
-- Check if enough inventory exists
DECLARE @CurrentStock INT;
SELECT @CurrentStock = StockQuantity
FROM ProductInventory
WHERE ProductID = @ProductID AND LocationID = @LocationID;
IF @CurrentStock IS NULL
BEGIN
SET @Message = 'Product not found at this location.';
ROLLBACK;
RETURN;
END
IF @CurrentStock < @Quantity
BEGIN
SET @Message = 'Insufficient inventory. Available: ' + CAST(@CurrentStock AS NVARCHAR(10));
ROLLBACK;
RETURN;
END
-- Update inventory
UPDATE ProductInventory
SET StockQuantity = StockQuantity - @Quantity
WHERE ProductID = @ProductID AND LocationID = @LocationID;
-- Record sale transaction
INSERT INTO SalesTransactions (ProductID, Quantity, LocationID, SaleDate)
VALUES (@ProductID, @Quantity, @LocationID, GETDATE());
COMMIT;
SET @Success = 1;
SET @Message = 'Sale processed successfully.';
END TRY
BEGIN CATCH
ROLLBACK;
SET @Message = ERROR_MESSAGE();
END CATCH
END
Best Practices for Working with Stored Procedures
- Use Parameters Properly: Always use parameters to prevent SQL injection, never concatenate strings.
- Handle NULL Values: Make sure your code properly handles NULL values returned from stored procedures.
- Manage Connections: Use
usingstatements to properly dispose of connections and commands. - Error Handling: Implement proper try-catch blocks to handle exceptions.
- Right Tool for the Job: Choose the appropriate approach (DataReader for forward-only reading, DataSets for disconnected data, etc.) based on your needs.
- Watch for Performance: Be mindful of connection pooling and don't create/close connections repeatedly.
- Consider Transactions: Use transactions when multiple operations need to succeed or fail together.
- Parameter Direction: Correctly specify the direction for each parameter (Input, Output, InputOutput, ReturnValue).
Debugging Stored Procedure Execution
Here's how to capture information for debugging stored procedure issues:
csharp
public void ExecuteStoredProcWithDebugInfo(string procName, Dictionary<string, object> parameters)
{
string connectionString = "your_connection_string_here";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = new SqlCommand(procName, connection);
command.CommandType = CommandType.StoredProcedure;
// Add parameters
foreach (var param in parameters)
{
command.Parameters.AddWithValue(param.Key, param.Value ?? DBNull.Value);
}
try
{
connection.Open();
// Debug info - log the command being executed
Console.WriteLine("=== STORED PROCEDURE DEBUG INFO ===");
Console.WriteLine($"Procedure: {procName}");
Console.WriteLine("Parameters:");
foreach (SqlParameter param in command.Parameters)
{
Console.WriteLine($" {param.ParameterName} = {param.Value} ({param.SqlDbType})");
}
// Execute the command
using (SqlDataReader reader = command.ExecuteReader())
{
// Check if we got results
if (!reader.HasRows)
{
Console.WriteLine("No rows returned.");
}
else
{
// Process results
int rowCount = 0;
while (reader.Read())
{
rowCount++;
if (rowCount <= 5) // Show first 5 rows only
{
Console.WriteLine($"Row {rowCount}:");
for (int i = 0; i < reader.FieldCount; i++)
{
Console.WriteLine($" {reader.GetName(i)} = {reader[i]}");
}
}
}
Console.WriteLine($"Total rows: {rowCount}");
}
}
}
catch (Exception ex)
{
Console.WriteLine($"ERROR: {ex.Message}");
Console.WriteLine($"Stack Trace: {ex.StackTrace}");
}
}
}
Summary
Stored procedures are a powerful way to interact with databases from .NET applications. They offer improved performance, better security, and greater maintainability compared to embedding SQL directly in your application code. In this guide, we've covered:
- Creating and calling basic stored procedures
- Working with different types of parameters
- Using DataSets and DataAdapters with stored procedures
- Integrating stored procedures with Entity Framework Core
- Building a real-world inventory system using stored procedures
- Best practices for working with stored procedures in .NET
By leveraging stored procedures effectively, you can create more robust, secure, and maintainable database-driven applications.
Additional Resources
- Microsoft Documentation: ADO.NET Stored Procedures
- Entity Framework Core and Stored Procedures
- SQL Server Stored Procedure Best Practices
Exercises
- Create a stored procedure that retrieves product details by category and price range, then write a .NET method to call it with parameters.
- Implement a stored procedure with output parameters to process a customer order, updating inventory and returning success/failure information.
- Create a logging system using stored procedures that accepts different data types and handles errors gracefully.
- Build a reporting function that uses DataSets to populate multiple related tables from a single stored procedure call.
- Convert a series of direct SQL queries in an application to use stored procedures instead, measuring the performance difference.
If you spot any mistakes on this website, please let me know at feedback@compilenrun.com. I’d greatly appreciate your feedback! :)