SQL-Server Stored Procedures for VB.NET Windows Forms
Introduction
This article presents basics of Stored Procedures to perform common CRUD operations in a Windows Form solution written in Visual Studio, VB.NET 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
- Conventional inline queries using SqlClient data provider utilizing parameters rather than string concatenation when creating SQL statements.
- Entity Framework classic
or Entity Framework Core . - Dapper ORM
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 Function AddCustomer(
companyName As String,
contactName As String,
contactTypeIdentifier As Integer) As Integer
mHasException = False
Try
Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd As New SqlCommand With {.Connection = cn}
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "dbo.InsertCustomer"
cmd.Parameters.Add(New SqlParameter With
{
.ParameterName = "@CompanyName",
.SqlDbType = SqlDbType.NVarChar
})
cmd.Parameters.Add(New SqlParameter With
{
.ParameterName = "@ContactName",
.SqlDbType = SqlDbType.NVarChar
})
cmd.Parameters.Add(New SqlParameter With
{
.ParameterName = "@ContactTypeIdentifier",
.SqlDbType = SqlDbType.Int
})
cmd.Parameters.Add(New SqlParameter With
{
.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 CInt(cmd.Parameters("@Identity").Value)
End Using
End Using
Catch ex As Exception
mHasException = True
mLastException = ex
Return -1
End Try
End Function
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.
''' <summary>
''' Mocked up sample showing how to return error
''' information from a failed operation within
''' a stored procedure.
''' </summary>
Public Sub ReturnErrorInformation()
Using cn As New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd As New SqlCommand With {.Connection = cn}
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "dbo.[usp_ThrowDummyException]"
cmd.Parameters.Add(New SqlParameter With
{
.ParameterName = "@ErrorMessage",
.SqlDbType = SqlDbType.NVarChar,
.Direction = ParameterDirection.Output
}).Value = ""
cmd.Parameters.Add(New SqlParameter With
{
.ParameterName = "@ErrorSeverity",
.SqlDbType = SqlDbType.Int,
.Direction = ParameterDirection.Output
})
cmd.Parameters.Add(New SqlParameter With
{
.ParameterName = "@ErrorState",
.SqlDbType = SqlDbType.Int,
.Direction = ParameterDirection.Output
})
Try
cn.Open()
cmd.ExecuteNonQuery()
Catch ex As Exception
Console.WriteLine($"[{ex.Message}]")
Console.WriteLine(cmd.Parameters("@ErrorSeverity").Value)
Console.WriteLine(cmd.Parameters("@ErrorState").Value)
End Try
End Using
End Using
End Sub
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 _dataOperations As New BackendOperations
A BindingSource component is also used.
Private _bsCustomers As New BindingSource
Data is presented using the following code.
Private Sub MainForm_Shown(sender As Object, e As EventArgs) Handles Me.Shown
LoadCustomers()
End Sub
Private Sub LoadCustomers()
Dim customerDataTable = _dataOperations.RetrieveAllCustomerRecords
Dim contactList = _dataOperations.RetrieveContactTitles()
If _dataOperations.IsSuccessFul Then
_bsCustomers.DataSource = customerDataTable
_bsCustomers.Sort = "CompanyName"
DataGridView1.DataSource = _bsCustomers
DataGridView1.ExpandColumns()
_bsCustomers.MoveFirst()
ContactTypeComboBox.DataSource = contactList
Else
MessageBox.Show($"Failed to load data{Environment.NewLine}" &
$"{_dataOperations.LastExceptionMessage}")
End If
End Sub
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.
Imports System.Collections.Generic
Imports System.Data
Imports Microsoft.SqlServer.Management.Sdk.Sfc
Imports 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 Sub Execute(ByVal pServerName As String, ByVal pCatalogName As String, ByVal pFileName As String)
Dim server As New Server(pServerName)
Dim database As Database = server.Databases(pCatalogName)
Dim sqlSmoObjectList = New List(Of SqlSmoObject)()
Dim dataTable As DataTable = database.EnumObjects(DatabaseObjectTypes.StoredProcedure)
For Each row As DataRow In dataTable.Rows
Dim currentSchema = DirectCast(row("Schema"), String)
If currentSchema = "sys" OrElse currentSchema = "INFORMATION_SCHEMA" Then
Continue For
End If
Dim sp = CType(server.GetSmoObject(New Urn(DirectCast(row("Urn"), String))), StoredProcedure)
If Not sp.IsSystemObject Then
sqlSmoObjectList.Add(sp)
End If
Next
Dim scriptWriter = New Scripter With { _
.Server = server, _
.Options = { _
IncludeHeaders = True, _
SchemaQualify = True, _
ToFileOnly = True, _
FileName = pFileName _
} _
}
scriptWriter.Script(sqlSmoObjectList.ToArray())
End Sub
End Class
End Namespace
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 Function GetImage(
identifier As Integer,
ByRef inBoundImage As Image,
ByRef description As String) As Success
The stored procedure:
ALTER PROC [dbo].[ReadImage]
@imgId INT
AS
SELECT ImageData ,
Description
FROM ImageData
WHERE ImageID = @imgId;
When saving images, the new image needs to be converted from Image to a byte array using the following method.
Public Function ImageToByte(image As Image) As Byte()
Dim converter = New ImageConverter()
Return DirectCast(converter.ConvertTo(image, GetType(Byte())), Byte())
End Function
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 Function InsertImage(
image As Image,
description As String,
ByRef identifier As Integer) As Success
mHasException = False
Using cn = New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd = New SqlCommand With {
.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 With {
.ParameterName = "@new_identity",
.SqlDbType = SqlDbType.Int,
.Direction = ParameterDirection.Output
})
Try
cn.Open()
identifier = Convert.ToInt32(cmd.ExecuteScalar())
Return Success.Okay
Catch ex As Exception
mHasException = True
mLastException = ex
Return Success.OhSnap
End Try
End Using
End Using
End Function
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.
Public Function GetAllCustomersRecords() As DataTable
mHasException = False
Dim dt = New DataTable()
Try
Using cn = New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd = New SqlCommand With {
.Connection = cn,
.CommandType = CommandType.StoredProcedure
}
cmd.CommandText = "dbo.[Customer_Reader]"
cmd.Parameters.AddWithValue("@CustomerIdentifier", Nothing)
cmd.Parameters.AddWithValue("@CompanyName", Nothing)
cn.Open()
dt.Load(cmd.ExecuteReader())
End Using
End Using
Catch e As Exception
mHasException = True
mLastException = e
End Try
Return dt
End Function
By primary key
Public Function GetAllCustomerRecordsByIdentifier(identifier As Integer) As DataTable
mHasException = False
Dim dt = New DataTable()
Try
Using cn = New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd = New SqlCommand With {
.Connection = cn,
.CommandType = CommandType.StoredProcedure
}
cmd.CommandText = "dbo.[Customer_Reader]"
cmd.Parameters.AddWithValue("@CustomerIdentifier", identifier)
cmd.Parameters.AddWithValue("@CompanyName", Nothing)
cn.Open()
dt.Load(cmd.ExecuteReader())
End Using
End Using
Catch e As Exception
mHasException = True
mLastException = e
End Try
Return dt
End Function
By Company name
Public Function GetAllCustomerRecordsByCompanyName(companyName As String) As DataTable
mHasException = False
Dim dt = New DataTable()
Try
Using cn = New SqlConnection With {.ConnectionString = ConnectionString}
Using cmd = New SqlCommand With {
.Connection = cn,
.CommandType = CommandType.StoredProcedure
}
cmd.CommandText = "dbo.[Customer_Reader]"
cmd.Parameters.AddWithValue("@CustomerIdentifier", Nothing)
cmd.Parameters.AddWithValue("@CompanyName", companyName)
cn.Open()
dt.Load(cmd.ExecuteReader())
End Using
End Using
Catch e As Exception
mHasException = True
mLastException = e
End Try
Return dt
End Function
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
Stored Procedure Generator
Redgate SQL-Tool-belt
SSMS Tool Pack
See also
Calling Stored Procedures Using Transact-SQL
Using XML Serialization with C# and SQL Server
T-SQL : Search for string or phrase in SQL Server database
SQL Server: Searching All Columns in a Table for a String
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
Source Visual Studio 2017 C# solution repository .
Source Visual Studio 2017 VB.NET solution repository .
Setup
- Run the following script to create tables and stored procedures before running the code
- Change server name in data class .