Table of Contents


Introduction

This article presents basics of Stored Procedures to perform common CRUD operations in a Windows Form solution written in Visual Studio, C# with Microsoft SQL-Server database. 

Definition of Stored Procedures

A stored procedure is a prepared SQL code that you can save, so the code can be reused repeatedly which are stored in a database. Basic usage, common SQL statements with or without parameters while other uses include data-validation or access-control mechanisms. Stored procedures may return result sets (a DataTable for instance), i.e., the results of a SELECT statement. Such result sets can be processed using cursors, by other stored procedures, by associating a result-set locator, or by applications. Stored procedures may also contain declared variables for processing data and cursors that allow it to loop through multiple rows in a table. Stored-procedure flow-control statements typically include IF, WHILE, LOOP, REPEAT, and CASE statements, and more. Stored procedures can receive variables, return results or modify variables and return them, depending on how and where the variable is declared.

Stored procedures have been viewed as the de facto standard for applications to access and manipulate database information using codified methods, or “procedures.” This is largely due to what they offer developers: the opportunity to couple the set-based power of SQL with the iterative and conditional processing control of code development. Instead of writing inline SQL and then attempting to manipulate the data from within the code.

Advantages of Using Stored Procedures

The following are not all inclusive reasons in regards to advantages of using Stored Procedures.

These advantages are subjective to developers, database administrators, business and/or security requirements.  
  • Maintainability: Because scripts are in a single location updates and tracking of dependencies based on schema changes become easier.
  • Testing: Can be tested independent of an application.
  • Isolation of Business Rules: Having Stored Procedures in one location means that there’s no confusion of having business rules spread over potentially disparate code files in the application.
  • Speed/Optimization: Stored Procedures are cached on the server. The first execution may take longer while later calls will be shorter. This means the query is optimized along with proper indexing server side.
  • Security: 
    • Limit direct access to tables via defined roles in the database
    • Securing just the data and the code that accesses it is easier than applying that security within the application code itself

Disadvantages of Using Stored Procedures

  • Someone must keep track of changes between database environments, make a test changes in development environment and promote, then test in both test and production environments.
  • Testing: 
    • Stored Procedures tend to be utilized on more than one application. This means there are chances all affected applications do not get tested which may lead to unexpected results.
    • Any data errors in handling Stored Procedures are not generated until runtime
  • Security: When done for the wrong reasons can lead to longer time to work with a specific Stored Procedure where time will vary dependent on the process to gain access to a Stored Procedure followed by time to work on the SQL.
  • Stored procedure code is not as robust as application code, particularly in the area of looping (not to mention that iterative constructs, like cursors, are slow and processor intensive)

Alternatives to Stored Procedures

Create Stored Procedure basic steps

The following Microsoft documentation explains how to create and modify stored procedures.

Stored Procedures can be created and/or modified in Visual Studio by viewing "Server Explorer", creating a connection to a database followed by expanding the database node, selecting "Stored Procedures", right click and select "New Stored Procedure" which opens a code windows as shown below which is a generic template.

CREATE PROCEDURE [dbo].[Procedure]
    @param1 int = 0,
    @param2 int
AS
    SELECT @param1, @param2
RETURN 0

Once done writing the Stored Procedure click the "Update" link in the upper left corner of the code editor window. To run the Stored Procedure, right click on the "Stored Procedure" node, select refresh. Right click on the Stored Procedure and click "Execute".

In this case the Stored Procedure definition is.
USE [CustomerDatabase]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
 
CREATE PROCEDURE [dbo].[SelectAllCustomers]
AS
BEGIN
    SET NOCOUNT ON;
SELECT Cust.Identifier ,
       Cust.CompanyName ,
       Cust.ContactName ,
       Cust.ContactTypeIdentifier ,
       CT.ContactType AS ContactTitle
FROM   Customer AS Cust
       INNER JOIN ContactTypes AS CT ON Cust.ContactTypeIdentifier = CT.Identifier;
END
GO

After clicking "Execute" the following window appears which is how the Stored Procedure is called (same as SQL-Server Management Studio).
USE [CustomerDatabase]
GO
DECLARE @return_value Int
EXEC    @return_value = [dbo].[SelectAllCustomers]
SELECT  @return_value as 'Return Value'
GO

Query results are displayed the code window.

Creating Stored Procedure tip

Create the query in a new query window rather than a query window specific to creating a Stored Procedure and test the SQL prior to creating the Stored Procedure. Chances are that for common SQL statements (SELECT, UPDATE, INSERT) work in a regular query they will work in the Stored Procedure. If something does not work properly look to any parameters both for input and output.

Parameters are either IN or OUT:
Indicates that the parameter is an output parameter. Use OUTPUT parameters to return values to the caller of the procedure. text, ntext, and image parameters cannot be used as OUTPUT parameters, unless the procedure is a CLR procedure. An output parameter can be a cursor placeholder, unless the procedure is a CLR procedure. A table-value data type cannot be specified as an OUTPUT parameter of a procedure.

Example, in the following Stored Procedures there are three IN parameters which need to be passed from the .NET application to be used in the INSERT statement while the last parameter Identity is returned from the Stored Procedure.


Code to run the above Stored Procedure (included in source code for this article).

Note for those familiar with writing conventional non-stored procedure queries the parameters are done no different. The main difference here is the SqlCommand, SqlCommand.CommandType which is set to CommandType.StoredProcedure.

public int AddCustomer(string companyName, string contactName, int contactTypeIdentifier)
{
    mHasException = false;
    try
    {
        using (var cn = new SqlConnection {ConnectionString = ConnectionString})
        {
 
            using (var cmd = new SqlCommand
            {
                Connection = cn,
                CommandType = CommandType.StoredProcedure
            })
            {
 
                cmd.CommandText = "dbo.NewCustomer";
 
                cmd.Parameters.Add(new SqlParameter
                {
                    ParameterName = "@CompanyName",
                    SqlDbType = SqlDbType.NVarChar
                });
                cmd.Parameters.Add(new SqlParameter
                {
                    ParameterName = "@ContactName",
                    SqlDbType = SqlDbType.NVarChar
                });
                cmd.Parameters.Add(new SqlParameter
                {
                    ParameterName = "@ContactTypeIdentifier",
                    SqlDbType = SqlDbType.Int
                });
                cmd.Parameters.Add(new SqlParameter
                {
                    ParameterName = "@Identity",
                    SqlDbType = SqlDbType.Int,
                    Direction = ParameterDirection.Output
                });
 
                cmd.Parameters["@CompanyName"].Value = companyName;
                cmd.Parameters["@ContactName"].Value = contactName;
                cmd.Parameters["@ContactTypeIdentifier"].Value = contactTypeIdentifier;
 
                cn.Open();
 
                cmd.ExecuteScalar();
 
                return Convert.ToInt32(cmd.Parameters["@Identity"].Value);
 
            }
        }
    }
    catch (Exception ex)
    {
        mHasException = true;
        mLastException = ex;
 
        return -1;
 
    }
}

Conventional queries to Stored Procedures

To move from existing code without stored procedures, first validate queries function correctly and for those queries which use string concatenation or string interpolation to fold in values e.g. $"SELECT FirstName, LastName, Phone FROM Contacts WHERE CountryCode = {CountryCode}" need to be changed to use parameters e.g.

"SELECT FirstName, LastName, Phone FROM Contacts WHERE CountryCode = @CountryCode"

For basics on parameters for SqlCommand.Parameters see the following Microsoft documentation.

Once the existing code base has been tested the next step is to add CommandType property set to CommandType.StoredProcedure for the SqlCommand e.g.


Handling runtime exceptions

When there is a chance of failure within a stored procedure, write client C# code in a try/catch statement which when an exception is raised the application does not crash. There may be times when the exception is not caused by an issue from incorrect data sent to received, instead there may be times when a business rule is violated, in these cases  RAISEERROR can be used to throw an exception.

The following stored procedure shows throwing a mocked exception.
ALTER PROCEDURE [dbo].[usp_ThrowDummyException]
    @ErrorMessage VARCHAR(2000) OUTPUT ,
    @ErrorSeverity INT OUTPUT ,
    @ErrorState INT OUTPUT
AS
BEGIN
 
 
    BEGIN TRY
 
        RAISERROR('your message here', 16, 1);
 
    END TRY
    BEGIN CATCH
        SET @ErrorMessage = ERROR_MESSAGE();
        SET @ErrorSeverity = ERROR_SEVERITY();
        SET @ErrorState = ERROR_STATE();
        RAISERROR(@ErrorMessage, @ErrorSeverity, @ErrorState);
    END CATCH;
END;

Calling a stored procedure, note in the catch of the try/catch statement parameters set in the above catch are read.
public void ReturnErrorInformation()
{
    using (var cn = new SqlConnection {ConnectionString = ConnectionString})
    {
        using (var cmd = new SqlCommand
        {
            Connection = cn,
            CommandType = CommandType.StoredProcedure
        })
        {
 
            cmd.CommandText = "dbo.[usp_ThrowDummyException]";
 
            cmd.Parameters.Add(new SqlParameter
            {
                ParameterName = "@ErrorMessage",
                SqlDbType = SqlDbType.NVarChar,
                Direction = ParameterDirection.Output
            }).Value = "";
 
            cmd.Parameters.Add(new SqlParameter
            {
                ParameterName = "@ErrorSeverity",
                SqlDbType = SqlDbType.Int,
                Direction = ParameterDirection.Output
            });
 
            cmd.Parameters.Add(new SqlParameter
            {
                ParameterName = "@ErrorState",
                SqlDbType = SqlDbType.Int,
                Direction = ParameterDirection.Output
            });
 
            try
            {
                cn.Open();
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                Console.WriteLine($"[{ex.Message}]");
                Console.WriteLine(cmd.Parameters["@ErrorSeverity"].Value);
                Console.WriteLine(cmd.Parameters["@ErrorState"].Value);
            }
        }
    }
}

Code for interacting with databases

Code which interacts with a database should not reside in a Window Form, instead this code should reside in a class.
  • Step 1 is to remove all code which directly interacts with a database from all forms.
  • Step 2 is to decide should there be separate classes for each table needed in the database. 
  • Step 3 is to decide if conventional containers such as DataSet and DataTable are the choice to interact with data or to use classes where each class represents a table in the database.
Example, in the code samples provided all code which interacts directly with the database resides in a class project with the following class to read, insert, delete and modify data.

In the main form an instance of the class is created.
private readonly BackendOperations _dataOperations = new BackendOperations();

A BindingSource component is also used.
private readonly BindingSource _bsCustomers = new BindingSource();

Data is presented using the following code.

private void MainForm_Shown(object sender, EventArgs e)
{
    LoadCustomers();
}
private void LoadCustomers()
{
 
    var customerDataTable = _dataOperations.RetrieveAllCustomerRecords();
    var contactList = _dataOperations.RetrieveContactTitles();
 
    if (_dataOperations.IsSuccessFul)
    {
 
        _bsCustomers.DataSource = customerDataTable;
        _bsCustomers.Sort = "CompanyName";
 
        DataGridView1.DataSource = _bsCustomers;
        DataGridView1.ExpandColumns();
 
        _bsCustomers.MoveFirst();
 
        ContactTypeComboBox.DataSource = contactList;
 
    }
    else
    {
        MessageBox.Show($"Failed to load data\n{_dataOperations.LastExceptionMessage}");
    }
}

Note there are no code that directly makes calls to the database, that code resides in the data class in a class project. The same goes for edit, delete and add methods.

How can Stored Procedures be viewed?

Open a new query window in Visual Studio from Server Explorer selected by first selecting the database then insert the following query and run the query.
SELECT   name
FROM     sys.procedures
WHERE    name NOT LIKE 'sp_%'
ORDER BY name;

To view a specific stored procedure
DECLARE @StoredProcedureName AS NVARCHAR(50) = 'dbo.CustomerInsertOrUpdate';
SELECT definition
FROM   sys.sql_modules
WHERE  object_id = OBJECT_ID(@StoredProcedureName);

To view parameters for a specific stored procedure.
DECLARE @StoredProcedureName AS NVARCHAR(50) = 'dbo.CustomerInsertOrUpdate';
SELECT name,
       system_type_id,
       max_length,
       [precision],
       scale
FROM sys.parameters
WHERE object_id = OBJECT_ID(@StoredProcedureName);

To view all stored procedures and parameters.
DECLARE @ProcName NVARCHAR(50);
DECLARE @IteratorProcedureName NVARCHAR(50);
DECLARE IteratorName CURSOR FAST_FORWARD READ_ONLY FOR
    SELECT   name
    FROM     sys.procedures
    WHERE    name NOT LIKE 'sp_%'
    ORDER BY name;
OPEN IteratorName;
 
FETCH NEXT FROM IteratorName
INTO @IteratorProcedureName;
WHILE @@FETCH_STATUS = 0
    BEGIN
        SELECT definition
        FROM   sys.sql_modules
        WHERE  object_id = OBJECT_ID(@IteratorProcedureName);
        FETCH NEXT FROM IteratorName
        INTO @IteratorProcedureName;
 
    END;
 
CLOSE IteratorName;
DEALLOCATE IteratorName;

An issue is the results are not easy to read, below is a C# project which provides the ability to view stored procedures and their parameters. Double click on a stored procedure to view both details and parameters.



The above project uses conventional SQL to obtain results. This can also be done with SMO (SQL-Server Management Objects).

The following method accepts a server, a database and in this case a file name as the results will be scripted to a file.

using System.Collections.Generic;
using System.Data;
using Microsoft.SqlServer.Management.Sdk.Sfc;
using Microsoft.SqlServer.Management.Smo;
 
namespace ScriptingLibrary
{
    public class StoredProcedureScripter
    {
        /// <summary>
        /// Script stored procedures from a specific database residing in a specific
        /// SQL-Server instance
        /// </summary>
        /// <param name="pServerName">Name of SQL-Server</param>
        /// <param name="pCatalogName">Catalog to traverse Stored Procedures on</param>
        /// <param name="pFileName">File name and path to write Stored Procedures too</param>
        /// <remarks>
        /// Exception handling intentionally left out. At least there should be a try/catch
        /// around this method from the caller of this method.
        /// </remarks>
        public void Execute(string pServerName, string pCatalogName, string pFileName)
        {
            Server server = new Server(pServerName);
            Database database = server.Databases[pCatalogName];
 
            var sqlSmoObjectList = new List<SqlSmoObject>();
            DataTable dataTable = database.EnumObjects(DatabaseObjectTypes.StoredProcedure);
 
            foreach (DataRow row in dataTable.Rows)
            {
                var currentSchema = (string)row["Schema"];
 
                if (currentSchema == "sys" || currentSchema == "INFORMATION_SCHEMA")
                {
                    continue;
                }
 
                var sp = (StoredProcedure)server.GetSmoObject(new Urn((string)row["Urn"]));
 
                if (!sp.IsSystemObject)
                {
                    sqlSmoObjectList.Add(sp);
                }
 
            }
 
            var scriptWriter = new Scripter
            {
                Server = server, Options =
                {
                    IncludeHeaders = true,
                    SchemaQualify = true,
                    ToFileOnly = true,
                    FileName = pFileName
                }
            };
 
            scriptWriter.Script(sqlSmoObjectList.ToArray());
 
        }
    }
}

The SMO code is within a compressed file in the main solution. This was done so not to interfere with compiling and running the main code samples.

Included code sample



This project is broken down to a class project responsible for interacting with the backend database using stored procedures while the front end works with the backend class project to work with data.

In form Shown event (better than form Load event as the Load event can sometimes swallow exceptions) data is read using a Stored Procedure to a BindingSource component where the BindingSource component becomes the Data Source of a DataGridView.

 A BindingSource allows interacting with data without ever interrogating the DataGridView rows or cells plus obtaining data not attached to the DataGridView.

  • The "reload from database" button provides a refresh of data so that validation may be done to ensure edits, additions and deletes actually occurred.
Viewing the form above gives no thought that a different way of interacting with data has been done e.g. stored procedures vs no stored procedures.

Second example

This project uses the same techniques to work with data, in this was the focus is on adding, viewing, removal and inserting images.


For reading a specific image the primary key, a string to hold the description of the image and a image is passed to the following method. This could also be done by passing in an instance of a class with properties which represent the inbound parameters.

public Success GetImage(int identifier, ref Image inBoundImage, ref string description)

The stored procedure:
ALTER PROC [dbo].[ReadImage]
    @imgId INT
AS
    SELECT ImageData ,
           Description
    FROM   ImageData
    WHERE  ImageID = @imgId;


To get only the image without the image description
public Success GetImage(int identifier, ref Image inBoundImage)

When saving images, the new image needs to be converted from Image to a byte array using the following method.
public byte[] ImageToByte(Image img)
{
    var converter = new ImageConverter();
    return (byte[])converter.ConvertTo(img, typeof(byte[]));
}

From here the operation is the same as others described above, the key here is setting the Command Type to Stored Procedure and having an OUT parameter for returning the new primary key.

Insert method:
public Success InsertImage(Image image, string description, ref int identifier)
{
    mHasException = false;
 
    using (var cn = new SqlConnection { ConnectionString = ConnectionString })
    {
        using (var cmd = new SqlCommand { Connection = cn, CommandText = "SaveImage" })
        {
            cmd.CommandType = CommandType.StoredProcedure;
 
            cmd.Parameters.Add("@img", SqlDbType.Image).Value = ImageToByte(image);
            cmd.Parameters.Add("@description", SqlDbType.Text).Value = description;
 
            cmd.Parameters.Add(new SqlParameter
            {
                ParameterName = "@new_identity",
                SqlDbType = SqlDbType.Int, Direction = ParameterDirection.Output
            });
 
            try
            {
                cn.Open();
                identifier = Convert.ToInt32(cmd.ExecuteScalar());
                return Success.Okay;
            }
            catch (Exception ex)
            {
                mHasException = true;
                mLastException = ex;
                return Success.OhSnap;
            }
        }
    }
}

Stored procedure
ALTER PROC [dbo].[SaveImage]
    @img IMAGE ,
    @description NVARCHAR(MAX),
    @new_identity INT OUTPUT
AS
    BEGIN
        INSERT  INTO dbo.ImageData ( ImageData, [Description])
            VALUES  ( @img, @description );
        SELECT  @new_identity = SCOPE_IDENTITY();
        SELECT  @new_identity AS id;
        RETURN;
    END;

Third code example

Stored procedures can contain various paths such as accepts more than one incoming parameters work againsts these parameters to allow different results dependent on the logic written into a stored procedure.

In the following example passing null values will return all rows in the table while passing in the @CustomerIdentifier with an existing primary key will return one row or passing in just the company name into @CompanyName with an existing company name will return one row.

Even with this known the best path is to have a separate stored procedure for each result set needed for an application. Keeping statements and code simple go along way down the road for maintainability.

CREATE PROCEDURE [dbo].[Customer_Reader]
    @CustomerIdentifier INT = NULL,
    @CompanyName varchar(50) = NULL
AS
BEGIN
 
    SET NOCOUNT ON;
 
    SELECT Identifier ,
           CompanyName ,
           ContactName ,
           ContactTypeIdentifier ,
           GenderIdentifier FROM dbo.Customer
    WHERE
    (Identifier=@CustomerIdentifier OR @CustomerIdentifier IS NULL) AND
    (CompanyName=@CompanyName OR @CompanyName IS NULL)
 
END

Below are three methods, the first passes null for all three parameters which will return all records.  The second method passes only a primary key which will return one record or if the primary key does not exists, no records while the third method passes the company name, if the name exists, one record is returned while if the company name does not exists no records are returned. The second and third method need not return a DataTable but instead may uses a DataReader to return data e.g. use a class with properties matching the fields in the SELECT statement.

/// <summary>
/// Get all customers
/// </summary>
/// <returns></returns>
public DataTable GetAllCustomersRecords()
{
    mHasException = false;
    var dt = new DataTable();
 
    try
    {
        using (var cn = new SqlConnection { ConnectionString = ConnectionString })
        {
            using (var cmd = new SqlCommand
            {
                Connection = cn,
                CommandType = CommandType.StoredProcedure
            })
            {
 
                cmd.CommandText = "dbo.[Customer_Reader]";
 
                cmd.Parameters.AddWithValue("@CustomerIdentifier", null);
                cmd.Parameters.AddWithValue("@CompanyName", null);
 
                cn.Open();
 
                dt.Load(cmd.ExecuteReader());
 
            }
        }
 
    }
    catch (Exception e)
    {
        mHasException = true;
        mLastException = e;
    }
 
    return dt;
}
/// <summary>
/// Get a single customer by primary key
/// </summary>
/// <param name="identifier"></param>
/// <returns></returns>
public DataTable GetAllCustomerRecordsByIdentifier(int identifier)
{
    mHasException = false;
    var dt = new DataTable();
 
    try
    {
        using (var cn = new SqlConnection { ConnectionString = ConnectionString })
        {
            using (var cmd = new SqlCommand
            {
                Connection = cn,
                CommandType = CommandType.StoredProcedure
            })
            {
 
                cmd.CommandText = "dbo.[Customer_Reader]";
 
                cmd.Parameters.AddWithValue("@CustomerIdentifier", identifier);
                cmd.Parameters.AddWithValue("@CompanyName", null);
 
                cn.Open();
 
                dt.Load(cmd.ExecuteReader());
 
            }
        }
 
    }
    catch (Exception e)
    {
        mHasException = true;
        mLastException = e;
    }
 
    return dt;
}
/// <summary>
/// Get customer by customer name
/// </summary>
/// <param name="companyName"></param>
/// <returns></returns>
public DataTable GetAllCustomerRecordsByCompanyName(string companyName)
{
    mHasException = false;
    var dt = new DataTable();
 
    try
    {
        using (var cn = new SqlConnection { ConnectionString = ConnectionString })
        {
            using (var cmd = new SqlCommand
            {
                Connection = cn,
                CommandType = CommandType.StoredProcedure
            })
            {
 
                cmd.CommandText = "dbo.[Customer_Reader]";
 
                cmd.Parameters.AddWithValue("@CustomerIdentifier", null);
                cmd.Parameters.AddWithValue("@CompanyName", companyName);
 
                cn.Open();
 
                dt.Load(cmd.ExecuteReader());
 
            }
        }
 
    }
    catch (Exception e)
    {
        mHasException = true;
        mLastException = e;
    }
 
    return dt;
}

Summary

This article has provided basics to get started working with Stored Procedures in a Windows Form project. Neither the full potential of stored procedures were covered nor searching for data from the database nor conventional filtering of the data residing in the form project which was done intentionally so those reading this article would focus on the absolute basics.

Recommend reading through the see also section below for more details for working with Stored Procedures.

Resources

See also

SQL: Protect Your Data against SQL Injection
SSMS: Generate Scripts for Stored Procedures Without Dynamic SQL
T-SQL: Script to Find the Names of Stored Procedures that Use Dynamic SQL
SQL Server: Stored procedure to add row-level auditing to table
Entity Framework FAQ: Sprocs and Functions
SQL Server Performance Survival Guide

Source code