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.

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.

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.