Introduction
Stored procedures are T-SQL statements that are stored in the SQL Server. They are executed by calling their names, as in our case, from ASP.NET pages. They add more efficiency and compactness to the code. Imagine what it’d cost in terms of server performance if we were to send with every request to the SQL Server, a set of 5-10 T-SQL statements, which the SQL Server would have to parse and compile and then execute. This will be performance heavy. Now, compare this with the performance of a pre-compiled set of high performance code that is kept on the SQL Server and executed by just calling its name. This gives tremendous performance advantages, plus also allows one to keep lengthy bits of code compactly and in a more organized way, on the SQL Server.
In this tutorial, we will learn how to create stored procedures for four of the most common SQL tasks that every ASP.NET and database developer has to go through. And that is creating, retrieving (displaying), updating and deleting records in a table in the database. For the purpose of demonstration, we’ll first create a table, and then add four stored procedures to the database, one for each of the SQL tasks we talked of above. Let us now proceed to the code.
Creating the Table
We’ll create a table with the name of ‘Names’ in our SQL Server database. Open SQL Server Query Analyzer and copy/paste following code in it. Then press F5 key to execute/create the table.
CREATE TABLE [dbo].[Names] ( [ID] [int] IDENTITY (1, 1) NOT NULL , [FirstName] [varchar] (50) NOT NULL , [LastName] [varchar] (50) NOT NULL ) ON [PRIMARY] GO
Executing the above SQL code will create a table ‘Names’ with following structure in your SQL Server database:
Explanation
We basically create a very simple table with just 3 columns. The first column with the name of ‘ID’ is an identity column, meaning it will increment automatically. It is also our primary key. The second and third columns with the names of ‘FirstName’ and ‘LastName’ are for storing first and last names that we’ll add later on, using ASP.NET pages.
Creating the Stored Procedures
Let us now create the four stored procedures to create, retrieve, update and delete records in the ‘Names’ table.
i. Stored Procedure for Creating a New Name
This stored procedure encapsulates the INSERT SQL statement to add a new record to the ‘Names’ table. Copy following code and paste in it your SQL Server Query Analyzer window and then press the F5 key to execute/create this stored procedure:
CREATE PROC dbo.AddName @FirstName varchar(50), @LastName varchar(50), @ID int OUT AS INSERT INTO Names(FirstName, LastName) VALUES (@FirstName, @LastName) SET @ID = @@IDENTITY GO
Explanation
After the CREATE PROC statement, comes the name of our stored procedure. We append dbo. before it to tell SQL Server that this stored procedure belongs to ‘dbo’ user account. We then tell SQL Server that this stored procedure will be accepting three arguments of the type given above. The third argument has the keyword OUT appended to it, which tells SQL Server that we’ll be using this argument to send data back to the calling ASP.NET page. Finally, after the INSERT statement, we retrieve the primary key of the newly added record using SQL Server’s @@IDENTITY variable. We assign it’s value to the out-going ‘ID’ parameter and we are done.
Tip: Unless you really know what you are doing, always append ‘dbo.’ before the name of all the objects (tables, stored procedures) you create in SQL Server.
In a nutshell, this stored procedure will accept first and last names. It will insert them in the ‘Names’ table and return the newly generated identity back to the ASP.NET page.
ii. Stored Procedure for Retrieving All Names from the ‘Names’ Table
This stored procedure will return all the records from the ‘Names’ table. Copy following code and paste in it your SQL Server Query Analyzer window and then press the F5 key to execute/create this stored procedure:
CREATE PROC dbo.GetAllNames AS SELECT [ID], FirstName, LastName FROM Names ORDER BY [ID] ASC GO
Explanation
This stored procedure simply runs a SELECT statement to return the ID, first name and last name of all the names added to the ‘Names’ table. Executing this stored procedure, which does not accept any argument, will display a list of all names in the ‘Names’ table.
iii. Stored Procedure for Updating a Given Name
This stored procedure will update the first and last names of a name in the ‘Names’ table using its ‘ID’. Copy following code and paste in it your SQL Server Query Analyzer window and then press the F5 key to execute/create this stored procedure:
CREATE PROC dbo.UpdateName @ID int, @FirstName varchar(50), @LastName varchar(50) AS UPDATE Names SET FirstName = @FirstName, LastName = @LastName WHERE [ID] = @ID GO
Explanation
It takes three arguments to update the record using an UPDATE SQL statement. The code is straightforward.
iv. Stored Procedure for Removing a Name From ‘Names’ Table
This stored procedure will remove a given name using its ‘ID’ from the ‘Names’ table. Copy following code and paste in it your SQL Server Query Analyzer window and then press the F5 key to execute/create this stored procedure:
CREATE PROC dbo.RemoveName @ID int AS DELETE Names WHERE [ID] = @ID GO
Explanation
Another very simple stored procedure. Here we use a single-line DELETE SQL statement to remove a record from the ‘Names’ table using the argument.
Assuming that you were successful in creating the ‘Names’ table and four stored procedures in your SQL Server database, we’ll move forward to create 4 ASP.NET pages similar to the 4 stored procedures.
Creating the ASP.NET front-end
Guys, let us now create the ASP.NET front-end.
i. Web.config
Since our ASP.NET will be connecting to the database, add the following connection string in your web.config file between the <connectionStrings></connectionStrings> tags:
<add name="NamesDB" connectionString="server=.;database=;user id=;password=" providerName="System.Data.SqlClient"/>
Add the correct server, database, username and password to be able to connect to your SQL Server database.
ii. Create.aspx
Create a new ASP.NET page and copy/paste following code in it:
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
public void AddName(object obj, EventArgs args)
{
string connStr = ConfigurationManager.ConnectionStrings["NamesDB"]
.ConnectionString;
using (SqlConnection con = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand("AddName", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("FirstName", SqlDbType.VarChar, 50).Value =
FirstName.Text;
cmd.Parameters.Add("LastName", SqlDbType.VarChar, 50).Value =
LastName.Text;
cmd.Parameters.Add("ID", SqlDbType.Int);
cmd.Parameters["ID"].Direction = ParameterDirection.Output;
con.Open();
cmd.ExecuteNonQuery();
Result.Text = "New record created. (" +
cmd.Parameters["ID"].Value + ")";
}
}
</script>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Add a New Name</title>
<style>
body { font-family: Verdana; }
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<span style="font-size: larger">Add a New Name</span><br /><br />
First Name: <asp:TextBox ID="FirstName" runat="server" /><br />
Last Name: <asp:TextBox ID="LastName" runat="server" /><br /><br />
<asp:Button ID="SubmitButton" Text="Add Name"
OnClick="AddName" runat="server" />
</div>
<br />
<asp:Label ID="Result" runat="server" />
</form>
</body>
</html>
Explanation
This ASP.NET displays a form to the user. The user then enters the desired first and last names, and presses the submit button, the ASP.NET pages connects to the SQL Server and executes the ‘AddName’ stored procedure we create above to add a new record in the ‘Names’ table.
string connStr = ConfigurationManager.ConnectionStrings["NamesDB"] .ConnectionString;
We first retrieve the connection string in a local variable.
using (SqlConnection con = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand("AddName", con))
{
...
}
Then we create connection and command objects for this connection. The command object contains the name of the stored procedure we want to execute.
cmd.CommandType = CommandType.StoredProcedure;
We set the CommandType property of command object to be of type CommandType.StoredProcedure.
cmd.Parameters.Add("FirstName", SqlDbType.VarChar, 50).Value =
FirstName.Text;
cmd.Parameters.Add("LastName", SqlDbType.VarChar, 50).Value =
LastName.Text;
cmd.Parameters.Add("ID", SqlDbType.Int);
cmd.Parameters["ID"].Direction = ParameterDirection.Output;
Add three parameters to this stored procedure and setting the last parameter to receive the data set by the stored procedure, which in our case will be the ‘ID’ of the newly created record.
con.Open(); cmd.ExecuteNonQuery();
We then open the connection and execute the stored procedure.
Result.Text = "New record created. (" +
cmd.Parameters["ID"].Value + ")";
Finally, we display the retrieved id to the user. Pretty simple stuff.
iii. Retrieve.aspx
Create a new ASP.NET page and copy/paste following code in it:
<%@ Page Language="C#" %>
<script runat="server">
public void Page_Load(object obj, EventArgs args)
{
GridView1.DataSource = SqlDataSource1;
GridView1.DataBind();
}
</script>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Retrieve All Names</title>
<style>
body { font-family: Verdana; }
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:SqlDataSource ID="SqlDataSource1" SelectCommand="GetAllNames"
SelectCommandType="StoredProcedure" ProviderName="System.Data.SqlClient"
ConnectionString="<%$ ConnectionStrings:NamesDB %>"
runat="server"></asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server">
</asp:GridView>
</div>
</form>
</body>
</html>
Explanation
We use <asp:SqlDataSource> and <asp:GridView> server tags to display the result of executing the ‘GetAllNames’ stored procedure to the user.
iv. Update.aspx
Create a new ASP.NET page and copy/paste following code in it:
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
public void UpdateName(object obj, EventArgs args)
{
string connStr = ConfigurationManager.ConnectionStrings["NamesDB"]
.ConnectionString;
using (SqlConnection con = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand("UpdateName", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("ID", SqlDbType.Int).Value = ID.Text;
cmd.Parameters.Add("FirstName", SqlDbType.VarChar, 50).Value =
FirstName.Text;
cmd.Parameters.Add("LastName", SqlDbType.VarChar, 50).Value =
LastName.Text;
con.Open();
cmd.ExecuteNonQuery();
Result.Text = "Record updated";
}
}
</script>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Update a Name</title>
<style>
body { font-family: Verdana; }
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<span style="font-size: larger">Update an Existing Name</span><br /><br />
ID: <asp:TextBox ID="ID" runat="server" /><br />
First Name: <asp:TextBox ID="FirstName" runat="server" /><br />
Last Name: <asp:TextBox ID="LastName" runat="server" /><br /><br />
<asp:Button ID="SubmitButton" Text="Update Name"
OnClick="UpdateName" runat="server" />
</div>
<br />
<asp:Label ID="Result" runat="server" />
</form>
</body>
</html>
Explanation
Presents a form, asking for a given record’s ‘ID’ whose first and last names you want to change. You supply all the arguments and click the submit button, this will execute the ‘UpdateName’ stored procedure and the first and last names corresponding to that ‘ID’ will be updated.
iv. Delete.aspx
Create a new ASP.NET page and copy/paste following code in it:
<%@ Page Language="C#" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
public void DeleteName(object obj, EventArgs args)
{
string connStr = ConfigurationManager.ConnectionStrings["NamesDB"]
.ConnectionString;
using (SqlConnection con = new SqlConnection(connStr))
using (SqlCommand cmd = new SqlCommand("RemoveName", con))
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("ID", SqlDbType.Int).Value = ID.Text;
con.Open();
cmd.ExecuteNonQuery();
Result.Text = "Record deleted";
}
}
</script>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Delete a Name</title>
<style>
body { font-family: Verdana; }
</style>
</head>
<body>
<form id="form1" runat="server">
<div>
<span style="font-size: larger">Delete an Existing Name</span><br /><br />
ID: <asp:TextBox ID="ID" runat="server" /><br /><br />
<asp:Button ID="SubmitButton" Text="Delete Name"
OnClick="DeleteName" runat="server" />
</div>
<br />
<asp:Label ID="Result" runat="server" />
</form>
</body>
</html>
Explanation
Presents a form with a single input field. You enter the value of the ‘ID’ of the record you want deleted. Pressing the submit button executes the ‘RemoveName’ stored procedure which deletes the given record.
v. index.html
This page will allow you to link to all four ASP.NET pages from a single page. This way you can go to one ASP.NET page to add a new record, and come back to go to another ASP.NET page to view it. Create a new HTML page and copy/paste following code in it:
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN"
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head>
<title>Index</title>
<style>
body { font-family: Verdana; }
</style>
</head>
<body>
<div>Basic Stored Procedure Operations</div>
<ul>
<li><a href="create.aspx">Create</a></li>
<li><a href="retrieve.aspx">Retrieve</a></li>
<li><a href="update.aspx">Update</a></li>
<li><a href="delete.aspx">Delete</a></li>
</ul>
</body>
</html>
Conclusion
In this tutorial we learned about stored procedures. We learned how to create 4 basic types of stored procedures, and viewed their operations on a sample table. We also created 4 ASP.NET pages and learned how to assign parameters to stored procedures from ASP.NET pages, receive data (via OUT statement) from the stored procedure, and execute stored procedures from ASP.NET pages.
Guys, this is it. Go play with the code. Create the drop stored procedures as many times as you want. You can download all the code discussed in this tutorial from a link below. Have fun!
