Tutorials – ADO.Net

Stored procedures are precompiled SQL queries that are stored in a database and can be executed with a single call. They offer several advantages, including improved performance, security, and maintainability. In this chapter, we will explore the use of stored procedures in ADO.NET, how to execute them, and the benefits they bring to database-driven applications.

Introduction to Stored Procedures

A stored procedure is a collection of SQL statements that are precompiled and stored in a database. These procedures can take parameters, execute queries, perform data manipulation, and return results. Using stored procedures in ADO.NET offers several key advantages:

1. Performance: Stored procedures are precompiled, which means that the database engine can optimize their execution plans, resulting in faster query execution.
2. Security: Stored procedures can be granted permissions independently of the underlying tables. This allows for fine-grained access control and helps prevent SQL injection attacks.
3. Maintainability: By centralizing database logic in stored procedures, you can easily update, modify, and version control the database operations without changing the application code.
4. Reduced Network Traffic: Executing stored procedures reduces the amount of data transferred between the application and the database server, which can improve network performance.

Executing Stored Procedures with ADO.NET

ADO.NET provides various ways to execute stored procedures within your applications. Here’s an overview of the main methods for working with stored procedures:

• Using SqlCommand:
  The SqlCommand class is a fundamental component of ADO.NET for working with SQL queries and stored procedures. To execute a stored procedure, create a SqlCommand and set its CommandType to StoredProcedure. You can then specify the stored procedure name and parameters, if required. Here’s an example:

using (SqlConnection connection = new SqlConnection("YourConnectionString"))
{
    connection.Open();
    using (SqlCommand command = new SqlCommand("StoredProcedureName", connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        // Add parameters if needed
        command.Parameters.AddWithValue("@ParameterName", parameterValue);
        // Execute the stored procedure
        command.ExecuteNonQuery();
    }
}

• Using SqlDataAdapter:
  The SqlDataAdapter class can be used to fill a DataSet or DataTable with the results of a stored procedure. This method is particularly useful when your stored procedure returns a result set. Here’s an example:

using (SqlConnection connection = new SqlConnection("YourConnectionString"))
{
    connection.Open();
    using (SqlDataAdapter adapter = new SqlDataAdapter("StoredProcedureName", connection))
    {
        adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
        // Add parameters if needed
        adapter.SelectCommand.Parameters.AddWithValue("@ParameterName", parameterValue);
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
    }
} 

• Using Entity Framework:
  If you’re using Entity Framework (EF) to work with databases, EF allows you to execute stored procedures. You can call a stored procedure using the Database.SqlQuery method. Here’s an example:

using (YourDbContext context = new YourDbContext())
{
    var result = context.Database.SqlQuery<YourEntityType>("StoredProcedureName @ParameterName", parameterValue);
}

• Using ORM Tools:
  Object-Relational Mapping (ORM) tools like Entity Framework, NHibernate, or Dapper provide higher-level abstractions for executing stored procedures, making it even easier to work with stored procedures in a database-agnostic way.

Working with Parameters

Stored procedures often require input parameters to perform their tasks. ADO.NET allows you to add parameters to your SqlCommand objects to pass data to the stored procedure. Here’s how to work with parameters:

using (SqlConnection connection = new SqlConnection("YourConnectionString"))
{
    connection.Open();
    using (SqlCommand command = new SqlCommand("StoredProcedureName", connection))
    {
        command.CommandType = CommandType.StoredProcedure;
        
        // Adding input parameters
        command.Parameters.AddWithValue("@ParameterName", parameterValue);
        // Adding output parameters
        SqlParameter outputParameter = new SqlParameter("@OutputParameterName", SqlDbType.VarChar, 50);
        outputParameter.Direction = ParameterDirection.Output;
        command.Parameters.Add(outputParameter);
        // Execute the stored procedure
        command.ExecuteNonQuery();
        // Access the value of an output parameter
        string outputValue = outputParameter.Value.ToString();
    }
}

In the code above, we’ve added both input and output parameters to the SqlCommand object. Input parameters are used to pass values to the stored procedure, while output parameters are used to retrieve values returned by the stored procedure.

Handling Output and Result Sets

Stored procedures can return both output values and result sets. You can handle these results using ADO.NET:

• Handling Output Parameters: As shown in the previous section, you can add output parameters to your SqlCommand and access their values after executing the stored procedure.
• Handling Result Sets: If a stored procedure returns a result set, you can use a SqlDataAdapter or SqlDataReader to retrieve the data. The result set can be consumed as a DataTable or processed row by row using a SqlDataReader.

Here’s an example of retrieving a result set using a SqlDataAdapter:

using (SqlConnection connection = new SqlConnection("YourConnectionString"))
{
    connection.Open();
    using (SqlDataAdapter adapter = new SqlDataAdapter("StoredProcedureName", connection))
    {
        adapter.SelectCommand.CommandType = CommandType.StoredProcedure;
        // Add parameters if needed
        adapter.SelectCommand.Parameters.AddWithValue("@ParameterName", parameterValue);
        DataTable dataTable = new DataTable();
        adapter.Fill(dataTable);
        // Process the DataTable
        foreach (DataRow row in dataTable.Rows)
        {
            // Access row data using row["ColumnName"]
        }
    }
}

Benefits and Best Practices

Stored procedures offer a range of benefits and best practices for database development:

1. Performance Optimization: Stored procedures are precompiled and optimized, resulting in improved query execution speed.
2. Security: Stored procedures help prevent SQL injection attacks and allow for fine-grained access control by defining permissions at the procedure level.
3. Modular and Maintainable: Centralizing database logic in stored procedures makes it easier to update, modify, and version control the database operations without changing the application code.
4. Reduced Network Traffic: Stored procedures can reduce the amount of data transferred between the application and the database server, which can improve network performance.
5. Parameterized Queries: Always use parameterized queries to avoid SQL injection vulnerabilities.
6. Use Output Parameters: Output parameters are useful for retrieving values calculated or returned by the stored procedure.
7. Optimize Result Set Retrieval: When working with result sets, consider the data access pattern (e.g., read-only or updatable) and use SqlDataAdapter or SqlDataReader accordingly.
8. Exception Handling: Implement robust error handling to capture and log exceptions that may occur during stored procedure execution.

Conclusion

Stored procedures are a powerful tool for improving the performance, security, and maintainability of database-driven applications. By using stored procedures in ADO.NET, you can benefit from optimized query execution, enhanced security, and modular database logic. This chapter has covered the basics of using stored procedures in ADO.NET, including how to execute them, work with parameters, handle output values, and retrieve result sets. In the subsequent chapters, we will explore advanced topics in ADO.NET, including data relationships, data constraints, and data synchronization.