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

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:

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:

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:

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:

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:

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.

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

Download Source Files