SQL Stored Procedures (in ASP.NET)

This page discusses calling SQL Stored Procdures from ASP.NET 2.0 in VB.NET.

Before proceeding it may help you to read my article on SQL Stored Procedures (in ASP) if you haven't already done so.

Open up Visual Studio and create a new ASP.NET Web Form.
Switch to the code-behind view, and add the following line at THE VERY TOP of the source listing:

Imports System.Data.SqlClient

This imports all the necessary references to the data objects used in the example. It also makes the intellisense work!

Then within the body of the source code declare a variable to hold the connection string, represented in braces below (where you will need to put your real connection string).

Dim sConnStr As String = {MyConnectionString}

TIP: It a good idea to declare all your data connection strings in the Web.config file. Then you can reference them as needed, reducing site maintenance.

Next create a SQL connection based on the connection string, and open the connection:

Dim cnBKTest As New SqlConnection(sConnStr)
cnBKTest.open()

Now create a SQL Command based on the SQL connection. When creating the SQL command, you need to pass the name of the stored procedure ("Read_AveragePrice" in this case) and the name of the connection object:

Dim cmdTest As New SqlCommand("Read_AveragePrice", cnBKTest)

The next thing you need to do is tell the command that it is going to be calling a stored procedure. You do this by setting the CommandType to Data.CommandType.StoredProcedure (Visual Studio's intellisense makes it really easy to find what you need if you don't remember exactly what you need to type).

cmdTest.CommandType = Data.CommandType.StoredProcedure

Then declare a DataReader object and execute the Command object, populating the DataRead:

Dim reader as SqlDataReader = myCommand.ExecuteReader()

Now call the Read() method on the DataReader object to read the first record:

'Read in the first record and grab the first column
Dim avgPrice as Decimal
If reader.Read() Then
avgPrice = Convert.ToDouble(reader("AveragePrice"))
End If

Finally close the connection

cnBKTest.Close()

Passing Parameters

To pass parameters to the stored Procedure, the name, datatype and size need to be passed to the SQL command object:

cmdTest.Parameters.Add(New SqlParameter("@TestParam", Data.SqlDbType.VarChar, 10))

After the parameter is added, the value needs to be specified:

cmdTest.Parameters("@TestParam").Value = "Testing"

Calling procedures without return values

When you wish to run an update query and do not require any information to be passed back use:

cmdTest.ExecuteNonQuery()