Using SQL Stored Procedures with VB.NET

CodeGuru content and product recommendations are editorially independent. We may make money when you click on links to our partners. Learn More.

Introduction

A month or so ago, I wrote a few articles introducing you to the world of databases and VB. If you have followed them, you have seen that it is not particularly difficult to use databases properly from VB. Today’s article is no exception. Today I will talk about using stored procedures built into SQL Server, with your VB.NET programs.

What are Stored Procedures?

Well, technically, here is a great definition; but, a stored procedure is simply a means of keeping your database layer apart from your actual program’s code. You store whatever SQL commands you’d like to execute in a stored procedure that you can use externally. Another benefit of a stored procedure is that it eliminates the risk of SQL Injection, as all the parameters used will be populated dynamically.

What Will You Learn Today?

Today you will learn the following:

• Creating a database table with a stored procedure
• Adding data to the created table via a stored procedure
• Editing data via stored procedures
• Deleting data from tables with a stored procedure
• Deleting a table via a stored procedure
• Using all of the above from VB.NET

Let us start!

Creating the Stored Procedures

Before we can create a stored procedure to do all of the above mentioned tasks, we obviously need a database. I may be captain obvious here, but open SQL Server and create a new database named Test. If you do not want to create a database, leave it at the default SQL Server database, which is Master.

Create Table Stored Procedure

Create a new SQL Query and type the following:

CREATE PROCEDURE CreateTable

AS

BEGIN

CREATE TABLE tblSPExample(
StudentID int IDENTITY(1,1),
Name varchar(50) NOT NULL,
Surname varchar(50) NOT NULL,
Age int NOT NULL,
PRIMARY KEY (StudentID) )

END 

This creates a procedure in SQL named CreateTable. Then it tells the procedure what to do. This is a standard SQL statement creating a table with the fields, their data types and each field’s constraints.

Insert Into Stored Procedure

Create a new SQL Query and type the following:

CREATE PROCEDURE InsertDataIntoTable
@Name varchar(50),
@Surname varchar(50),
@Age int

AS

BEGIN

INSERT INTO tblSPExample(Name, Surname, Age)

VALUES ( @Name, @Surname, @Age)


END 

Before you create the Insert statement, you must specify the parameters that this query will expect. These parameters will be populated during runtime of your application.

Update  Stored Procedure

Create a new SQL Query and type the following:

CREATE PROCEDURE UpdateDataInsideTable
@Name varchar(50),
@Surname varchar(50),
@Age int,
@StudentID int

AS

BEGIN

UPDATE tblSPExample

SET Name =  @Name, Surname = @Surname, Age = @Age

WHERE StudentID = @StudentID


END 

Delete  Stored Procedure

Create a new SQL Query and type the following:

CREATE PROCEDURE DeleteDataFromTable

@StudentID int

AS

BEGIN

DELETE FROM tblSPExample

WHERE StudentID = @StudentID


END 

Drop Table Stored Procedure

Create a new SQL Query and type the following:

CREATE PROCEDURE DropTable

AS

BEGIN

DROP TABLE tblSPExample

END

Our VB.NET Project

Now we can move on to create the VB.NET project. Name it anything you like and design the form to resemble Figure 1.

Figure 1 – Our Design

Coding

In all honesty, most of the code I will demonstrate today will be more or less the same. I live by the principle: Keep it simple stupid. This has brought me very far, even if I am not the sharpest tool in the shed.

As always, let me start with the Imports statement(s) needed for today’s example. Add this line on top of your class definition:

Imports System.Data.SqlClient 'Import SQL Capabilities

Now, create the modular variables:

    Private strConn As String = "Data Source=HANNES;Initial Catalog=Master;Integrated Security=True"
    Private sqlCon As SqlConnection

The first object, strConn specifies our database connection string. It is broken down into three parts:

1. The database server, which in this case is called Hannes
2. The database name which is Master
3. Security settings

The next object, sqlCon will be used as a SQLConnection object, which assists in connecting to the physical data store specified earlier.

Now, let’s get the party started!

This next sub you will be creating is used just to load the data from the database and display it to the user. There is no use of a stored procedure as that would have been overkill. Add this sub:

   Private Sub LoadData()

        Dim strQuery As String

        strQuery = "SELECT * FROM tblSPExample"

        sqlCon = New SqlConnection(strConn)

        Using (sqlCon)

            Dim sqlComm As SqlCommand = New SqlCommand(strQuery, sqlCon)

            sqlCon.Open()

            Dim sqlReader As SqlDataReader = sqlComm.ExecuteReader()

            If sqlReader.HasRows Then

                While (sqlReader.Read())

                    txtName.Text = sqlReader.GetString(1)
                    txtSurname.Text = sqlReader.GetString(2)
                    txtAge.Text = sqlReader.GetValue(3)

                End While


            End If

            sqlReader.Close()

        End Using

    End Sub

This should look familiar if you have read my previous article concerning databases. If you haven’t read any of them yet, here is an introduction to get you started. Anyways, it is quite simple really. This is what happened in the LoadData sub:

1. I created a string object to host the ultimate query string.
2. I stored the query inside the object. This query selects all the data from the tblSPExample table. This table you will create later via the Stored Procedure you created later.
3. I open the connection to the database.
4. Created a command object. This object will execute the query, to return the desired data to the DataReader.
5. If the DataReader can identify data, it will return each row into the associated textboxes.
6. I close all the connections to the database.

Create the CreateTable sub now:

    Private Sub CreateTable()

        sqlCon = New SqlConnection(strConn)


        Using (sqlCon)

            Dim sqlComm As New SqlCommand

            sqlComm.Connection = sqlCon

            sqlComm.CommandText = "CreateTable"
            sqlComm.CommandType = CommandType.StoredProcedure


            sqlCon.Open()

            sqlComm.ExecuteNonQuery()

        End Using

    End Sub

It’s the same principle here, except that we modify the Command object a little to specify the stored procedure we will be using; then, we execute it as a NonQuery. NonQuery means that there is no data being returned, just a simple action the SQL code should take.

Now that you have a table, you can insert data into it. Create the InsertNewRecord sub now:

    Private Sub InsertNewRecord()

        sqlCon = New SqlConnection(strConn)

        Using (sqlCon)

            Dim sqlComm As New SqlCommand()

            sqlComm.Connection = sqlCon

            sqlComm.CommandText = "InsertDataIntoTable"
            sqlComm.CommandType = CommandType.StoredProcedure

            sqlComm.Parameters.AddWithValue("FirstName", txtName.Text)
            sqlComm.Parameters.AddWithValue("Surname", txtSurname.Text)
            sqlComm.Parameters.AddWithValue("Age", Integer.Parse(txtAge.Text))

            sqlCon.Open()

            sqlComm.ExecuteNonQuery()

        End Using


        LoadData()

    End Sub

Again, still following the same principle – see, I told you it is easy! There are a couple of new statements in there. The statements in the middle add parameters to the command object. These parameters were specified inside the Insert Stored Procedure. By using parameters it easier to manage the data being sent into the SQL Command object. Here, you also gave each parameter a value, which is the associated textbox.

Now add the following two subs. One sub is to edit the information inside a database table; the other is to delete certain information:

    Private Sub UpdateRecord()

        sqlCon = New SqlConnection(strConn)

        Using (sqlCon)

            Dim sqlComm As New SqlCommand

            sqlComm.Connection = sqlCon


            sqlComm.CommandText = "UpdateDataInsideTable"
            sqlComm.CommandType = CommandType.StoredProcedure

            sqlComm.Parameters.AddWithValue("Name", txtName.Text)
            sqlComm.Parameters.AddWithValue("Surname", txtSurname.Text)
            sqlComm.Parameters.AddWithValue("Age", Integer.Parse(txtAge.Text))

            sqlCon.Open()

            sqlComm.ExecuteNonQuery()

        End Using

        LoadData()

    End Sub

    Private Sub DeleteRecord()

        sqlCon = New SqlConnection(strConn)


        Using (sqlCon)

            Dim sqlComm As New SqlCommand

            sqlComm.Connection = sqlCon

            sqlComm.CommandText = "DeleteDataFromTable"
            sqlComm.CommandType = CommandType.StoredProcedure

            sqlComm.Parameters.AddWithValue("StudentID", Integer.Parse(txtSearch.Text))


            sqlCon.Open()

            sqlComm.ExecuteNonQuery()

        End Using

        LoadData()

    End Sub

The next sub is to drop a desired database table from a database:

    Private Sub DropTable()

        sqlCon = New SqlConnection(strConn)


        Using (sqlCon)

            Dim sqlComm As New SqlCommand

            sqlComm.Connection = sqlCon

            sqlComm.CommandText = "DropTable"
            sqlComm.CommandType = CommandType.StoredProcedure


            sqlCon.Open()

            sqlComm.ExecuteNonQuery()

        End Using

    End Sub

Very simple indeed, agree?

Conclusion

As you can see, if you have the basic fundamentals of database design and VB.NET, you can create very powerful yet small programs to extract and manipulate data. Obviously, this is just a small example; but it can be used in any major application. I hope you have enjoyed this little article. Until next time, cheers!