Working with Stored Procedures in ADO.NET

Stored procedures are a powerful feature in database management systems that allow you to encapsulate complex logic and operations on the server side. When using ADO.NET in a .NET application, stored procedures can be leveraged to interact with the database efficiently. In this article, we’ll explore how to create and execute stored procedures using ADO.NET, discuss the benefits of using stored procedures, and cover how to handle input and output parameters.

Creating and Executing stored procedures using ADO.NET

Creating a Stored Procedure

To start, let’s define a simple stored procedure in SQL Server. Suppose we have a table named Employees and we want to create a stored procedure to insert a new employee record.

CREATE PROCEDURE AddEmployee
    @Name NVARCHAR(50),
    @Position NVARCHAR(50),
    @Salary DECIMAL(18, 2)
AS
BEGIN
    INSERT INTO Employees (Name, Position, Salary)
    VALUES (@Name, @Position, @Salary);
END

This procedure takes three parameters (@Name, @Position, and @Salary) and inserts a new record into the Employees table.

Executing stored procedures with ADO.NET

To execute this stored procedure from a .NET application using ADO.NET, follow these steps.

Setup the Connection: Create a connection to the database using SqlConnection.
Create a Command: Use SqlCommand to define the stored procedure and set its type to StoredProcedure.
Add Parameters: Define and add parameters to the command object.
Execute the Command: Use the ExecuteNonQuery method to execute the stored procedure.

Here’s a code example demonstrating these steps.

using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string_here";        
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand("AddEmployee", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                // Add parameters
                command.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar, 50)).Value = "John Doe";
                command.Parameters.Add(new SqlParameter("@Position", SqlDbType.NVarChar, 50)).Value = "Software Developer";
                command.Parameters.Add(new SqlParameter("@Salary", SqlDbType.Decimal)).Value = 75000.00m;
                // Execute the stored procedure
                command.ExecuteNonQuery();
            }
        }
    }
}

Benefits of Using Stored Procedures

Performance: Stored procedures are precompiled and optimized, which often results in better performance compared to executing individual SQL statements from the application.
Reduced Network Traffic: Since stored procedures execute on the server side, only the call to execute the procedure and the parameters need to be sent over the network, reducing the amount of data transferred.
Code Reusability: Stored procedures can be reused across multiple applications or different parts of the same application, promoting consistency and reducing code duplication.
Security: By using stored procedures, you can encapsulate the database logic and control access more effectively. Users can be granted permission to execute the stored procedure without direct access to the underlying tables.
Maintainability: Changes to database logic can be made in one place (the stored procedure) rather than updating multiple application codebases.

Handling Input and Output Parameters

Stored procedures can also handle output parameters and return values. Here’s how you can handle them in ADO.NET.

Example of a Stored Procedure with Output Parameters.

Let’s enhance the previous example by modifying the stored procedure to return the ID of the newly inserted employee.

CREATE PROCEDURE AddEmployee
    @Name NVARCHAR(50),
    @Position NVARCHAR(50),
    @Salary DECIMAL(18, 2),
    @EmployeeID INT OUTPUT
AS
BEGIN
    INSERT INTO Employees (Name, Position, Salary)
    VALUES (@Name, @Position, @Salary);

    -- Retrieve the ID of the newly inserted record
    SET @EmployeeID = SCOPE_IDENTITY();
END

Executing a Stored Procedure with Output Parameters.

To handle output parameters in ADO.NET, you can use the SqlParameter class. Here’s an updated version of the previous code that includes output parameter handling.

using System;
using System.Data;
using System.Data.SqlClient;
class Program
{
    static void Main()
    {
        string connectionString = "your_connection_string_here";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (SqlCommand command = new SqlCommand("AddEmployee", connection))
            {
                command.CommandType = CommandType.StoredProcedure;

                // Add input parameters
                command.Parameters.Add(new SqlParameter("@Name", SqlDbType.NVarChar, 50)).Value = "John Doe";
                command.Parameters.Add(new SqlParameter("@Position", SqlDbType.NVarChar, 50)).Value = "Software Developer";
                command.Parameters.Add(new SqlParameter("@Salary", SqlDbType.Decimal)).Value = 75000.00m;

                // Add output parameter
                SqlParameter outputParam = new SqlParameter("@EmployeeID", SqlDbType.Int);
                outputParam.Direction = ParameterDirection.Output;
                command.Parameters.Add(outputParam);
                // Execute the stored procedure
                command.ExecuteNonQuery();
                // Retrieve and display the output parameter value
                int employeeID = (int)command.Parameters["@EmployeeID"].Value;
                Console.WriteLine($"New Employee ID: {employeeID}");
            }
        }
    }
}

In this example, after executing the stored procedure, you can retrieve the output parameter’s value to get the ID of the newly inserted employee.

Summary

Working with stored procedures in ADO.NET can enhance the performance, security, and maintainability of your database interactions. By encapsulating SQL logic on the server side and using ADO.NET to execute these procedures, you can streamline your application’s data access layer. Understanding how to handle both input and output parameters effectively ensures that you can fully leverage the power of stored procedures in your .NET applications.