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"].ToString()); 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> |
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!
|