Page 1 of 1

Creating SQL Server Stored Procedures and Using them in VB.NET Rate Topic: ****- 1 Votes

#1 Psyguy   User is offline

  • D.I.C Regular
  • member icon

Reputation: 84
  • View blog
  • Posts: 365
  • Joined: 12-January 11

Posted 16 November 2012 - 11:36 AM

A while back, I wrote a simple blog on how to call a SQL Server stored procedure from VB.NET. I was asked by a reader to generate a more detailed "Hello World" style stored procedure so they could more easily identify exactly what was going on. In this tutorial, you will find just such a stored procedure and the associated VB.NET code.

The first thing I went about doing was creating the stored procedure. SSMS (SQL Server Management Studio) is a fairly easy tool to figure out, but in case you aren't familiar with it, here is the steps to create the stored procedure:

Step 1. Open Up the Query Window with the stored procedure standard code already generated

Attached Image

Attached Image

Step 2. Alter the template query so the stored procedure does what you want it to do. I have created a sample table (see design below) and a stored procedure which takes a customer number and returns the full name of the customer assigned to that number.

Attached Image

Attached Image

Attached Image

Step 3. Execute the code and verify that it is now in the list of stored procedures.

Attached Image

Now that the stored procedure exists, we can start to use it in our programs. In the sample program I created to demonstrate this, i have a label to display the full name and a button which will go through each of the customer numbers when clicked. Obviously, we can get much more abstracted than this, but it will do for demonstrative purposes.

Under the click event of the button, we have the code which will loop through the predetermined customer numbers and return to 0 once the end is reached. The GetCustomerName method is where we will call our stored procedure.

Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        If i < 9 Then
            i += 1
            i = 0
        End If

        Label1.Text = GetCustomerName(i)
End Sub

Private Function GetCustomerName(ByVal custNum As Integer) As String
            Dim ret As String = String.Empty
            Dim conn As SqlClient.SqlConnection = New SqlClient.SqlConnection("User ID = myUserName" &
                                                                                          "; Password = myPassword" &
                                                                                          "; Data Source = myServer" &
                                                                                          "; Initial Catalog = myDatabase")
            Using cmd As SqlClient.SqlCommand = New SqlClient.SqlCommand("TutorialSP", conn)
                cmd.CommandType = CommandType.StoredProcedure
                cmd.Parameters.Add("@customerNumber", SqlDbType.Int).Value = custNum
                ret = cmd.ExecuteScalar()
            End Using

            GetCustomerName = ret
        Catch ex As SqlClient.SqlException
            GetCustomerName = "<SQL Exception>"
        End Try
End Function

This small amount of code will result in the following simple app
Attached Image

The great thing about these calls to stored procedures is that you can do very complex calculations or table creations on the server side, then just pull the results back. You can return the results of a scalar query, a non query (insert/update), or a table and have as many parameters as you want. You have to be mindful of how long the query can possibly take to execute, however, so I would suggest that if you are going to do something that is going to take a while (or could) then execute it on a separate thread.

Another benefit of using stored procedures is that you can change the definition of certain properties without having to recompile and release a new version of your application. For instance, you may have a stored procedure which returns some calculation. You later learn that there is a much simpler way to calculate that same thing. Now all you have to do is go in and change the stored procedure and it filters down to each application which uses that calculation.

There are, of course, drawbacks to using stored procedures, but those are outside the scope of this tutorial/blog.

As always, i hope some found this post helpful. If anything is incorrect, you would like to know something more, or you found it helpful, please leave a comment. Cheers!

Is This A Good Question/Topic? 4
  • +

Page 1 of 1