Working with Stored Procedures in ADO.NET

Stored procedures offer several advantages when working with databases, including improved performance, enhanced security, and simplified application logic. ADO.NET provides robust support for executing stored procedures and handling their results.

Benefits of Stored Procedures

Performance: Stored procedures are pre-compiled and optimized by the database, leading to faster execution.
Security: Granting execute permissions on stored procedures can limit direct access to tables, enhancing data security.
Reduced Network Traffic: Instead of sending multiple SQL statements, only the procedure name and parameters are sent over the network.
Maintainability: Business logic can be encapsulated within stored procedures, making it easier to update and manage.
Code Reusability: Stored procedures can be called from multiple applications or parts of an application.

Executing Stored Procedures

To execute a stored procedure, you typically use the SqlCommand object and set its CommandType property to CommandType.StoredProcedure.

Executing a Stored Procedure with No Parameters

This example demonstrates how to execute a stored procedure that returns a result set.


using System;
using System.Data;
using System.Data.SqlClient;

public class StoredProcedureExample
{
    private string connectionString = "Server=myServerName;Database=myDatabaseName;Integrated Security=SSPI;";

    public void ExecuteSimpleStoredProcedure()
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand("usp_GetAllCustomers", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.HasRows)
                    {
                        while (reader.Read())
                        {
                            Console.WriteLine($"ID: {reader["CustomerID"]}, Name: {reader["CustomerName"]}");
                        }
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error executing stored procedure: {ex.Message}");
                }
            }
        }
    }
}

Executing a Stored Procedure with Input Parameters

Stored procedures often require parameters to filter data or provide input.


using System;
using System.Data;
using System.Data.SqlClient;

public class StoredProcedureExample
{
    private string connectionString = "Server=myServerName;Database=myDatabaseName;Integrated Security=SSPI;";

    public void ExecuteStoredProcedureWithParameters(int customerId)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand("usp_GetCustomerById", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                // Add input parameter
                command.Parameters.AddWithValue("@CustomerID", customerId);

                try
                {
                    connection.Open();
                    SqlDataReader reader = command.ExecuteReader();

                    if (reader.Read())
                    {
                        Console.WriteLine($"Customer Found - ID: {reader["CustomerID"]}, Name: {reader["CustomerName"]}");
                    }
                    else
                    {
                        Console.WriteLine($"Customer with ID {customerId} not found.");
                    }
                    reader.Close();
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error executing stored procedure: {ex.Message}");
                }
            }
        }
    }
}

Executing a Stored Procedure with Output Parameters

Stored procedures can return values through output parameters.


using System;
using System.Data;
using System.Data.SqlClient;

public class StoredProcedureExample
{
    private string connectionString = "Server=myServerName;Database=myDatabaseName;Integrated Security=SSPI;";

    public void ExecuteStoredProcedureWithOutputParameter()
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand("usp_GetCustomerCount", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                // Add output parameter
                SqlParameter outputParameter = new SqlParameter("@CustomerCount", SqlDbType.Int);
                outputParameter.Direction = ParameterDirection.Output;
                command.Parameters.Add(outputParameter);

                try
                {
                    connection.Open();
                    command.ExecuteNonQuery(); // ExecuteNonQuery is suitable for procedures that don't return a result set directly

                    int customerCount = (int)command.Parameters["@CustomerCount"].Value;
                    Console.WriteLine($"Total customers: {customerCount}");
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error executing stored procedure: {ex.Message}");
                }
            }
        }
    }
}

Note on Parameter Types

While AddWithValue is convenient, it can sometimes lead to issues with parameter type inference. For better control and to avoid potential `SqlDbType` issues, it's often recommended to explicitly define the parameter type and size:


command.Parameters.Add("@ParameterName", SqlDbType.NVarChar, 50).Value = "Some Value";

Handling Return Values

Stored procedures can also return an integer value, typically used to indicate success or failure status. This is accessed using the Command.ExecuteNonQuery() method's return value or by explicitly setting the return parameter.

Using Return Value from Stored Procedure

Assume a stored procedure like usp_AddCustomer which returns 1 on success, 0 on failure.


using System;
using System.Data;
using System.Data.SqlClient;

public class StoredProcedureExample
{
    private string connectionString = "Server=myServerName;Database=myDatabaseName;Integrated Security=SSPI;";

    public int AddNewCustomer(string name, string email)
    {
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand("usp_AddCustomer", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.AddWithValue("@CustomerName", name);
                command.Parameters.AddWithValue("@Email", email);

                // Optionally, define a RETURN parameter if your SP uses it explicitly
                // SqlParameter returnParameter = new SqlParameter("@ReturnValue", SqlDbType.Int);
                // returnParameter.Direction = ParameterDirection.ReturnValue;
                // command.Parameters.Add(returnParameter);

                try
                {
                    connection.Open();
                    // ExecuteNonQuery returns the number of rows affected.
                    // The actual RETURN value from the SP needs a specific parameter or is handled by some providers.
                    // For SQL Server, it's common to use a RETURN statement and capture it if needed via a parameter.
                    // If no explicit RETURN parameter is defined, the return value of ExecuteNonQuery might not be the SP's RETURN value.
                    // The example below assumes a RETURN statement in the SP.
                    int returnValue = command.ExecuteNonQuery(); // This typically returns rows affected.

                    // If your SP has a RETURN statement, you'd typically capture it via an output parameter or a specific mechanism.
                    // For simplicity here, let's assume a scenario where we expect rows affected or a specific return code.
                    // A more robust way for SQL Server RETURN values:
                    // command.ExecuteNonQuery();
                    // return (int)command.Parameters["@ReturnValue"].Value;

                    // For this example, let's assume ExecuteNonQuery is sufficient to indicate operation success
                    // and we'll return a placeholder or rely on other mechanisms for detailed success/failure.
                    // A common pattern is to return the ID of the inserted row if applicable, or a status code.

                    // A better pattern for return codes in SQL Server would be:
                    // command.Parameters.Add("@ReturnCode", SqlDbType.Int).Direction = ParameterDirection.ReturnValue;
                    // command.ExecuteNonQuery();
                    // return (int)command.Parameters["@ReturnCode"].Value;

                    // For this simplified example, let's just indicate success if no exception occurred.
                    return 1; // Indicate success

                }
                catch (SqlException ex)
                {
                    Console.WriteLine($"Error adding customer: {ex.Message}");
                    // Handle specific SQL errors if necessary
                    return -1; // Indicate failure
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"An unexpected error occurred: {ex.Message}");
                    return -2; // Indicate other failure
                }
            }
        }
    }
}

Using DataAdapter with Stored Procedures

DataAdapter objects can also be configured to execute stored procedures for their SelectCommand, InsertCommand, UpdateCommand, and DeleteCommand properties.

DataAdapter with Stored Procedures


using System;
using System.Data;
using System.Data.SqlClient;

public class StoredProcedureDataAdapterExample
{
    private string connectionString = "Server=myServerName;Database=myDatabaseName;Integrated Security=SSPI;";

    public DataTable GetProductsByCategory(int categoryId)
    {
        DataTable dataTable = new DataTable();
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlDataAdapter adapter = new SqlDataAdapter("usp_GetProductsByCategory", connection))
            {
                adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
                adapter.SelectCommand.Parameters.AddWithValue("@CategoryID", categoryId);

                try
                {
                    connection.Open();
                    adapter.Fill(dataTable);
                }
                catch (Exception ex)
                {
                    Console.WriteLine($"Error filling DataTable: {ex.Message}");
                }
            }
        }
        return dataTable;
    }
}

Considerations

Error Handling: Implement robust error handling to catch exceptions during stored procedure execution.
Parameter Management: Be meticulous with parameter names, types, and directions to ensure correct data transfer.
SQL Injection: While stored procedures themselves help mitigate SQL injection, ensure that parameter values passed from user input are properly validated and sanitized.
Performance Tuning: Even with stored procedures, indexing and query optimization within the procedure are crucial for performance.

By leveraging stored procedures effectively with ADO.NET, you can build more efficient, secure, and maintainable data-driven applications.