C# and SQL Server Stored Procedures

A stored procedure contains prepared SQL code that is stored directly in an SQL Server database and can be called when needed. This allows for the SQL to be reused as many times as needed and also makes maintenance of the code much easier, because it only has to be updated in one place. The other advantage of stored procedures is that they help protect against SQL injection attacks, where an attacker tries to gain unauthorised access to data.

To continue with the example of a person table, instead of embedding say an SQL ‘Select’ statement directly in the C# code, it can be placed in a stored procedure on the database, as shown below.

CREATE PROCEDURE SelectAllPeople 
AS
BEGIN

    SELECT id, firstname, lastname, dob 
    FROM dbo.person 
    ORDER BY lastname, firstname, dob

END

This stored procedure can then be executed within C# and the data processed as before.

// Database connection variable.
SqlConnection connect = new SqlConnection(
    "Server=MSSQLSERVERDEMO; Database=Demo;" +
    "User Id=DemoUN; Password=DemoPW");

try
{

    // Connect to database.
    connect.Open();

}
catch (Exception e)
{

    // Confirm unsuccessful connection and stop program execution.
    Console.WriteLine("Database connection unsuccessful.");
    System.Environment.Exit(1);

}

try
{

    // Stored procedure incorporated into SQL command.
    SqlCommand sqlProcedure = new SqlCommand("dbo.SelectAllPeople", connect);

    // Set the command type to stored procedure.
    sqlProcedure.CommandType = System.Data.CommandType.StoredProcedure;

    // Execute stored procedure and place data in a reader object.
    SqlDataReader reader = sqlProcedure.ExecuteReader();

    // Display person information in the console.
    while (reader.Read())
    {

        Console.WriteLine("{0}: {1}, {2} ({3})",
                            reader[0], reader[2], reader[1],
                            reader.GetDateTime(3).ToShortDateString());

    }

}
catch (Exception e)
{

    // Confirm error retrieving person information and exit.
    Console.WriteLine("Error retrieving person information.");
    System.Environment.Exit(1);

}
finally
{

    // Close the database connection.
    connect.Close();

}

The resulting output is the same as when the SQL was embedded directly within the C#.

3: Bloggs, Fred (07/05/1975)
2: Jones, George (15/12/1997)
1: Smith, Bob (20/01/1980)
4: White, Alan (20/03/1989)

It is also possible to include parameters within a stored procedure, such as the ones shown in the following example, to incorporate a date range for the date of birth to limit the people returned.

CREATE PROCEDURE SelectPeopleByDobRange @LowerDob date, @UpperDob date
AS
BEGIN
	
    SELECT id, firstname, lastname, dob 
    FROM dbo.person 
    WHERE dob BETWEEN @LowerDob AND @UpperDob
    ORDER BY lastname, firstname, dob

END

This stored procedure can again be executed from within C# code.

// Database connection variable.
SqlConnection connect = new SqlConnection(
    "Server=MSSQLSERVERDEMO; Database=Demo;" +
    "User Id=DemoUN; Password=DemoPW");

try
{

    // Connect to database.
    connect.Open();

}
catch (Exception e)
{

    // Confirm unsuccessful connection and stop program execution.
    Console.WriteLine("Database connection unsuccessful.");
    System.Environment.Exit(1);

}

try
{

    // Query parameters.
    DateTime dobLower = new DateTime(1980, 1, 1);
    DateTime dobUpper = new DateTime(1989, 12, 31);

    // Stored procedure incorporated into SQL command.
    SqlCommand sqlProcedure = new SqlCommand("dbo.SelectPeopleByDobRange", connect);

    // Set the command type to stored procedure.
    sqlProcedure.CommandType = System.Data.CommandType.StoredProcedure;

    // Bind the parameters to the query.
    sqlProcedure.Parameters.AddWithValue("@LowerDob", dobLower);
    sqlProcedure.Parameters.AddWithValue("@UpperDob", dobUpper);

    // Execute stored procedure and place data in a reader object.
    SqlDataReader reader = sqlProcedure.ExecuteReader();

    // Display person information in the console.
    while (reader.Read())
    {

        Console.WriteLine("{0}: {1}, {2} ({3})",
                            reader[0], reader[2], reader[1],
                            reader.GetDateTime(3).ToShortDateString());

    }

}
catch (Exception e)
{

    // Confirm error retrieving person information and exit.
    Console.WriteLine("Error retrieving person information.");
    System.Environment.Exit(1);

}
finally
{

    // Close the database connection.
    connect.Close();

}

The resulting output is a subset of records from those returned previously.

1: Smith, Bob (20/01/1980)
4: White, Alan (20/03/1989)

Stored procedures can also be used for inserting, updating and deleting data.

More

Further Resources