Tutorial RSS

• Home
• Tutorials
• What's New
• Newsletter
• Resources
Search
Quick Search

Navigator: Home - Advanced - Using Stored Procedures in ASP.NET and C#

Using Stored Procedures in ASP.NET and C#

This tutorial will show you how to use Stored Procedures over in-line SQL
Statements to increase security of your web application, in C#.

We chose Server Intellect for its dedicated servers, for our web hosting. They have
managed to handle virtually everything for us, from start to finish. And their customer
service is stellar.

Looking for more ASP.NET Database Tutorials? Click Here!

Stored Procedures are used to organize the SQL logic apart from the application logic,
and also act as a security measure helping protect against SQL Injection attacks. In this
tutorial, you will learn how to use Stored Procedures over inline SQL Statements. We will
be creating a simple web form that will use a class to access a SQL database

The first thing that we will do is create our database using the Server Explorer built in to
Visual Studio. Once we have created our project in Visual Studio, right-click the
App_Data folder in Solution Explorer and choose Add New Item.. SQL Server Database.
This will open up the Server Explorer Window. Right-click on the Tables folder and
choose Add New Table, which will open up the designer view. We will go ahead and add
an ID column, Name column and also an Age column. We will make the types bigint,
varchar(25), and int respectively. Also make the ID column the Primary Key and the
Identity Specification in the Column Properties.
Now we can save the table and give it a name.

Once saved, we can right-click the table name and choose Show Table Data. This will
allow us to add new records to the database. Go ahead and add a few records so that we
can display the data on our web form, and once we have done that, we can close the
table.

Next, we will add a Stored Procedure. Right-click the Stored Procedures folder in Server
Explorer and choose Add New Stored Procedure. We will have something like this:

CREATE PROCEDURE dbo.StoredProcedure1

/*
(
@parameter1 int = 5,
@parameter2 datatype OUTPUT
)
*/
AS
/* SET NOCOUNT ON */
RETURN

This is the default code for a Stored Procedure. We want this Stored Procedure to get all
data from the table we created, so we can change the default code to the following:

CREATE PROCEDURE dbo.GetAllData

AS
SELECT * FROM Table1

When we save this Stored Procedure, the keyword CREATE will change to ALTER. Here,
we are simply calling a SQL statement to get all columns from a particular table. Once
saved, we can close it.

The next thing we want to do is to create our connection to the database. A quick way to
do this is to drag the SqlDataSource Control from the toolbox onto your ASPX page, and
then in design view, click the Smart Tag of the control and choose Configure Data
Source. From the dropdown, choose your Database name and click Next. Make sure the
save connection string option is checked and then click Next. choose the asterisk (*)
option to select all columns from the database, then click Next, then Finish. Now if we
open up Web.config we shall see that the Connection String has been added. You can
now delete the SqlDataSource if you wish, as we will not be using it for this example -
we used it simply to build our Connection String, which will look something like this:

<connectionStrings>
<add name="ConnectionString" connectionString="Data
Source=.\SQLEXPRESS;AttachDbFilename=|
DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>

We moved our web sites to Server Intellect and have found them to be incredibly
professional. Their setup is very easy and we were up and running in no time.

We will use this connection string in our class to connect to the database. We store it in
the Web.config for security as well as ease of access. And if it ever changes, we are only
required to change it in the one place - no matter how many methods connect to the
database, because they will all reference this same one string.
To create our class, right-click the project folder in Solution Explorer, then goto Add
ASP.NET Folder > App_Code. Then right-click on the App_Code folder and choose Add
New Item.. Class. Give it the name StoreProcedures, or something descriptive. This class
we will wrap in a namespace, so we will need to reference this on our code-behind. We
do this by wrapping the class in namespace DBtutorials { }.
We will also be adding a method to this class to get all the data from the database, which
will use the Stored Procedure we just created. Our class will look something like this:
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using System.Data.SqlClient;
using System.Web.Configuration;

/// <summary>
/// Summary description for StoredProcedures
/// </summary>
namespace DBtutorials
{
public class StoredProcedures
{
public StoredProcedures()
{
}

/// <summary>
/// Gets all columns from Table1
/// </summary>
/// <returns>DataTable of all columns in Table1</returns>
public static DataTable GetAllData()
{
DataTable allData = new DataTable();
SqlConnection connection = new
SqlConnection(WebConfigurationManager.ConnectionStrings["ConnectionString"].To
String());
try
{
SqlCommand cmd = new SqlCommand("GetAllData", connection);
cmd.CommandType = CommandType.StoredProcedure;

connection.Open();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(allData);
connection.Close();
}
catch
{
connection.Close();
}
return allData;
}
}
}
Notice we first reference two namespaces we use in the method:
System.Web.Configuration, and System.Data.SqlClient
We create a method which will return a DataTable, and also use the
WebConfigurationManager to get our ConnectionString from the Web.config. We put the
database logic in a try {} catch {} so that we can be sure the connection closes upon
any errors encountered, and that no database error messages reach the end user.
Finally, we reference the Stored Procedure we created earlier by using the SqlCommand
method, and also set the CommandType.

Our next step is to create a Repeater control on our web form to display the database
data:

<form id="form1" runat="server">

<asp:Repeater ID="Repeater1" runat="server">
<ItemTemplate>
ID: <%# Eval("ID") %>
<br />
Name: <%# Eval("Name") %>
<br />
Age: <%# Eval("Age") %>
<br /><br />
</ItemTemplate>
</asp:Repeater>
</form>

We will bind the DataTable to this Repeater so we reference each column name we want
to display.
Now all that is needed is for us to reference this method from our code-behind.

Open up Default.aspx.cs by right-clicking Default.aspx in the Solution Explorer and

clicking View Code. The first thing we want to do is add the reference to our DBtutorials
namespace. Next, we will call the method that we just created in Page_Load, and bind
the DataTable it returns to the Repeater's DataSource.

using System;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;

using DBtutorials;

public partial class _Default : System.Web.UI.Page

{
protected void Page_Load(object sender, EventArgs e)
{
Repeater1.DataSource = StoredProcedures.GetAllData();
Repeater1.DataBind();
}
}

Now when we run our application, we should be greeted with a list of data from our
database.

Server Intellect assists companies of all sizes with their hosting needs by offering fully
configured server solutions coupled with proactive server management services.
Server Intellect specializes in providing complete internet-ready server solutions
backed by their expert 24/365 proactive support team.

Looking for more ASP.NET Database Tutorials? Click Here!

Download Project Source - Enter your Email to be emailed a link

to download the Full Source Project used in this Tutorial!

100% SPAM FREE! We will never sell or rent your email address!
brought to you by ServerIntellect
• Home
• Tutorials
• What's New
• Newsletter
• More Resources
• Suggestions