Tutorials and Code

ActionScript (5)
ASP (7)
ASP.NET (28)
Assembly (1)
C (6)
C# (132)
C++ (33)
CSS (6)
General (4)
HTML (6)
Java (28)
JavaScript (38)
MySQL (2)
Objective C (1)
Pascal (1)
PHP (61)
Python (3)
Ruby (18)
Silverlight (1)
SQL (7)
VB.NET (5)
Visual Basic (3)

Syndication

My Account

Community Forums

Webmaster Tools

Tech Jobs

Geekpedia » ASP.NET Tutorials » Creating and using stored procedures

Creating and using stored procedures

The tutorial shows you how to create stored procedures, after which you'll see how to use stored procedures in an ASP .NET web application.

On Thursday, September 2nd 2004 at 05:13 AM

By Andrew Pociu (View Profile)

(Rated 4.6 with 31 votes)

Contextual Ads

More ASP.NET Resources

The main reasons for using stored procedures to execute queries on a database, is the improved performance. Enough theory

Creating a stored procedure

In this tutorial I'm going to use a database that was created in the tutorial named 'Connecting to a SQL database from ASP .NET I' and that was accessed in the tutorial named 'Connecting to a SQL database from ASP .NET II'. But there's no need to use this specific database, you can directly experiment on any other database.

Also I should mention that in this tutorial I'm going to use the Web Data Administrator utility which you can download here.

The database is named MyDB. It has a table named MyLinks which has two char columns named Title and URL.

To create a stored procedure, first we have to select the database, so open the MyDB database page at Web Data Administrator (most often the coresponding URL is http://localhost/webadmin/tables.aspx?database=MyDB). In the menu on the left we can see the Stored Procedures link:

Click it and you can now see the stored procedures for this database... there are no stored procedures for this database so you won't see any, just a message that says 'There are no stored procedures to display.'. On the right corner of the page you'll see the Create new stored procedure which you're going to click. The name for the procedure shall be InsertLink. After clicking Create the Edit Stored Procedure page is shown. In the text box where you are supposed to enter the query replace the line created by Web Data Administrator by pasting the following:

CREATE PROCEDURE [dbo].[InsertLink]

(

@Title char(64),

@URL char(128)

)

AS

Insert MyLinks(Title, URL)

Values (@Title, @URL)

@Title and @URL are variables of type char with the length of 64 characters and 128 characters, respectively.

After AS is the actual query that you would use if you wouldn't work with stored procedures. And as you can see, the query inserts inside the columns Title and URL the values of the variabless @Title and @URL.

That's it, now let's insert some values using the stored procedure.

Executing stored procedures

Open Visual Studio .NET and create a new ASP .NET Web Application project (I called mine StoredProcedures).

On WebForm1.aspx create two TextBoxes named txtTitle and txtURL. Also add a button which can keep his original name, Button1.

NOTE

I'm not going to show you (again) how to connect to a database from an ASP .NET application because I suppose that if you need to use stored procedures you already connected your ASP .NET application to the database. Anyway, if you didn't yet established the connection and don't know how, use this tutorial and then this one, then come back here.

Now double click it and we're now located at the click event of the button. Inside Button1_Click use the following code:

// Create new SQL command named sqlInsert

System.Data.SqlClient.SqlCommand sqlInsert = new System.Data.SqlClient.SqlCommand("InsertLink", sqlConnection1);

// Set the command type property to StoredProcedure

sqlInsert.CommandType = CommandType.StoredProcedure;

// @Title should have the value inside txtTitle

sqlInsert.Parameters.Add("@Title", txtTitle.Text);

// @URL should have the value inside txtURL

sqlInsert.Parameters.Add("@URL", txtURL.Text);

// Open the connection

sqlConnection1.Open();

// Execute the query

sqlInsert.ExecuteNonQuery();

// Close the connection

sqlConnection1.Close();

The first line creates a new SQL command named sqlInsert which uses as the query text InsertLink, which is actually the name of the stored procedure.

Line 3 and 4 of code assigns to @Title the string inside txtTitle and to @URL the string inside txtURL.

Now if you run the application, complete the two text boxes and submit...

...you can then check the result using 'SELECT * FROM MyLinks' query inside WEB Data Administrator:

Visual Studio .NET has great support for stored procedures. You can create stored procedures and use them using simple operation, like drag and drop.

But this tutorial has its reasons... a programmer must know what happens behind the scenes even if he's going to use the easiest way most of the time.

Digg It!

Del.icio.us Reddit

StumbleIt

Newsvine

Furl

BlinkList

Rate this tutorial

Current Comments

by mdProgrammer on Wednesday, October 27th 2004 at 10:05 AM

Nice tutorial. One small (but important) detail. You declared the SQL command as sqlInsert, but you're calling it sqlPRInsert instead. I fixed the typo in my code and it worked. Also, here's a VB.NET version:

' Create new SQL command named sqlInsert
Dim sqlInsert As SqlClient.SqlCommand
sqlInsert = New SqlClient.SqlCommand("InsertLink", SqlConnection1)
' Set the command type property to StoredProcedure
sqlInsert.CommandType = CommandType.StoredProcedure
'@Title should have the value inside txtTitle
sqlInsert.Parameters.Add("@Title", txtTitle.Text)
'@URL should have the value inside txtTitle
sqlInsert.Parameters.Add("@URL", txtURL.Text)
' Open the connection
SqlConnection1.Open()
' Execute the query
sqlInsert.ExecuteNonQuery()
' Close the connection
SqlConnection1.Close()

by Andrei Pociu on Wednesday, October 27th 2004 at 10:14 AM

Thank you for pointing that out, I fixed the typos.
Also thank you for the VB .NET version :).

by gene on Tuesday, February 15th 2005 at 03:17 PM

This is great. Clear. Concise. and best of all, it works!

How about another tutorial building on this that performs an insert and then returns an identity value we can use?

by Andrei Pociu on Tuesday, February 15th 2005 at 03:22 PM

Thanks.

I will consider that, currently I don't have enough time, but I will soon.

by Hariharan on Friday, August 26th 2005 at 04:45 AM

It's worth for beginners who interested in SP's

by Manoj Gowda on Tuesday, January 3rd 2006 at 11:51 PM

Thank you for your instructions

by Henry on Sunday, June 11th 2006 at 06:52 AM

/* To check if record is been added to database i also used exception just in case something goes wront you\'ll be able to trace it and fixe it */

SqlConnection con = new SqlConnection(
\"data source=.\\\\SQL2000;\" + \"Initial Catalog=SampleDB;\" + \"Integrated Security=SSPI\");
SqlCommand cmd = new SqlCommand(\"SampleDB\", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(\"@Title\", txtTitle.Text);
cmd.Parameters.Add(\"@Url\", txtUrl.Text);
int added;
try
{
con.Open();
added = cmd.ExecuteNonQuery();
lblAdded.Text = added.ToString() + \" Been added\";
}
catch(Exception err)
{
//exception message goes right here!
lblAdded.Text = \"Couldn\'t perform your request \" + err;
}
finally
{
con.Close();
}

by srikanth on Wednesday, January 9th 2008 at 09:46 AM

it is pretty much good, but how to get values from sqlserver the user .

i have created a stored procedure with select statement in sql server and
same code as of urs in asp.net page but how to get the values to be displayed on ui of the user (say in one more textbox)

by ramy on Wednesday, March 12th 2008 at 02:53 AM

It is really good stuff to learn for freshers. I appreciate your effors.

by nil on Sunday, March 23rd 2008 at 12:20 AM

It is easy to understand.Nice explanation

by Heitor on Sunday, June 1st 2008 at 09:20 PM

Thanks dude!