ADO - Stored Procedure

A stored procedure in SQL is a precompiled set of SQL statements that are stored in the database and can be executed repeatedly. It is a named collection of SQL statements that can accept input parameters, perform operations, and return results. Stored procedures are commonly used to encapsulate complex logic and improve performance by reducing network traffic.

Example to understand ADO.NET using Stored Procedure in C#:

SQL Server Stored Procedure without Parameters:

Let's consider a simple stored procedure in SQL Server that selects all records from a table:

CREATE PROCEDURE GetEmployees
AS
BEGIN
    SELECT * FROM Employees;
END

Example to understand how to call a stored procedure in C# without parameters:

string connectionString = "YourConnectionString";
string storedProcedureName = "GetEmployees";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(storedProcedureName, connection);
    command.CommandType = CommandType.StoredProcedure;
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        // Process each row
        // Example: int id = (int)reader["ID"];
        //          string name = (string)reader["Name"];
        //          ...
    }
    reader.Close();
}

In the above example:

First, we define the connection string to the SQL Server database.

Then, we create a new SqlCommand object with the stored procedure name and connection.

We set the CommandType property of the command to CommandType.StoredProcedure.

After opening the connection, we execute the stored procedure using the ExecuteReader() method of the command.

We can then process the results by iterating through the SqlDataReader object.

SQL Server Stored Procedure with Input Parameters:

Let's consider a stored procedure that accepts an employee ID as input and returns the details of the employee:

CREATE PROCEDURE GetEmployeeDetails
    @EmployeeID int
AS
BEGIN
    SELECT * FROM Employees WHERE ID = @EmployeeID;
END

Example to understand how to call a stored procedure with an input parameter in C#:

string connectionString = "YourConnectionString";
string storedProcedureName = "GetEmployeeDetails";
int employeeID = 123;
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(storedProcedureName, connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@EmployeeID", employeeID);
    connection.Open();
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        // Process each row
    }
    reader.Close();
}

In the above example, we add a parameter to the SqlCommand object using the Parameters.AddWithValue() method. The parameter is assigned the employee ID value.

SQL Server Stored Procedure with Both Input and Output Parameters:

Let's consider a stored procedure that accepts an employee ID as input and returns the employee name as an output parameter:

CREATE PROCEDURE GetEmployeeName
    @EmployeeID int,
    @EmployeeName varchar(50) OUTPUT
AS
BEGIN
    SELECT @EmployeeName = Name FROM Employees WHERE ID = @EmployeeID;
END

Example to understand calling a stored procedure with both input and output parameters in C#:

string connectionString = "YourConnectionString";
string storedProcedureName = "GetEmployeeName";
int employeeID = 123;
string employeeName = "";
using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = new SqlCommand(storedProcedureName, connection);
    command.CommandType = CommandType.StoredProcedure;
    command.Parameters.AddWithValue("@EmployeeID", employeeID);
    command.Parameters.Add("@EmployeeName", SqlDbType.VarChar, 50).Direction = ParameterDirection.Output;
    connection.Open();
    command.ExecuteNonQuery();
    employeeName = command.Parameters["@EmployeeName"].Value.ToString();
}

In this example, we add an output parameter to the SqlCommand object using the Parameters.Add() method. We specify the parameter name, data type, and direction as ParameterDirection.Output. After executing the command, we can retrieve the output parameter value from the Value property of the parameter.

By using stored procedures in ADO.NET, you can encapsulate your database logic, improve performance, and pass parameters to perform specific operations on the data.