Calling a stored procedure from ASP.Net
By Brad Kingsley
May 3, 2005
I guess I'm from the "old school" because I learned coding without a fancy GUI and
I still have a tendency to lean that direction when possible. The latest version
of Visual Studio is a great tool, but I still like to understand what's going on
rather than just dragging and dropping controls onto a page. Perhaps with some more
time I'll give up on this way of thinking, but for now I still like to hand-code
at times.
With ASP.Net 2.0 available now, I wanted to create an ASP.Net page that would pass
parameters to a SQL Server stored procedure to insert data into a database. I've
read that this can be done now with controls and almost no understanding of coding,
but I wanted to do it manually. It surprised me that I couldn't find a decent online
sample from Google, which motivated me to put together this short article.
I am using Visual Studio 2005 to create my test page. I set up a page with nothing
more than a submit button and then started adding code to the submit function in
the associated .vb code file.
The first thing I had to do was add "Imports System.Data.SqlClient" to the very
top of the file. Without that the data objects weren't being recognized.
After that imports statement is added, the rest isn't too complex.
I created a variable to hold my connection string represented by <myconnectionstring></myconnectionstring>
below (put your real connection string here). I then created a SQL connection based
on the connection string, and also created a SQL Command based on that SQL Connection.
When creating the SQL Command you need to pass the name of the stored procedure
("Insert_Test" in this case) and the name of the connection object.
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 Command Type to Data.CommandType.StoredProcedure
(VS's intellisense makes it really easy to find what you need if you don't remember
exactly what you need to type).
Next I added a parameter to the SQL Command object providing the name of the parameter
and also the data type and size. After the parameter is added the value needs to
be specified. Until you specify the value, no data is actually set, so be sure to
perform this step. In this sample I'm only sending one parameter value but I could
have just as easily sent 20 if needed.
Lastly I open the connection, execute the stored procedure (automatically passing
the parameters defined), and close the connection.
Here is the entire subroutine with the code I used:
Protected Sub Btn1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Btn1.Click
Dim sConnStr As String = {MyConnectionString}
Dim cnBKTest As New SqlConnection(sConnStr)
Dim cmdTest As New SqlCommand("Insert_Test", cnBKTest)
cmdTest.CommandType = Data.CommandType.StoredProcedure
cmdTest.Parameters.Add(New SqlParameter("@TestParam", _
Data.SqlDbType.VarChar, 10))
cmdTest.Parameters("@TestParam").Value = "Testing"
cnBKTest.Open()
cmdTest.ExecuteNonQuery()
cnBKTest.Close()
End Sub
That's it! It really isn't very hard,
but since I couldn't find an online sample I wound up fussing with this for about
an hour. Hopefully this will give someone else a good start and save them from wasting
time scratching their heads like I did.
~Brad
Brad Kingsley is founder and president of ORCS Web, Inc. - a company that provides managed
complex hosting for clients who develop and deploy their applications on Microsoft
Windows platforms.