Using T-SQL Stored Procedures with ASP.NET 2.0
(Page 1 of 4 )
If you haven't used T-SQL stored procedures before with SQL Server and in your ASP.NET 2.0 pages then you need to read this article. Today I will answer questions like the following: what is a stored procedure? How can I create a stored procedure? How can I use it from within my ASP.NET 2.0 web pages? And how can I return an output parameter value to my ASP.NET web page?
A stored procedure is a script of T-SQL code that is stored in your database under a given name. The T-SQL code for the stored procedure may contain programming logic (using T-SQL IF and CASE statements for example) and SELECT, INSERT, UPDATE or DELETE statements as well. There are many benefits to using stored procedures instead of writing pure T-SQL code. As you have seen in our previous articles on ADO.NET we have used pure T-SQL code in our ASP.NET pages and we may have to use one of those T-SQL queries many times in different pages. What would happen if you want to change the query? You would modify all the pages searching for all the string values of that T-SQL code.
If you have a stored procedure then you would not do that, as we are going to see in our code examples. You simply use the name of the stored procedure as the command text and not the query itself. Thus, if you want to modify the query you will modify the stored procedure in the database and only once. I must say that this is not the case all the time because if you modified the stored procedure you may need to modify your ADO.NET code. For example you may add, or remove, SqlParameter objects from your SqlCommand object to match the modifications you have applied on the stored procedure.
One example of a change that you can do in the stored procedure code that doesn't require a modification in the ADO.NET code may be writing something like SELECT LastName, FirstName, Title from Employees instead of SELECT * From Employees, given that the Employees table contains only those three columns. Also you might change the programming logic of your stored procedure without changing the returning result set; thus we don't need to change any lines of our ADO.NET code.
One other benefit to using stored procedures instead of using direct T-SQL queries is that you don't give your users direct access to tables; instead you give them execution permissions on selected stored procedures, thus creating another layer of security. For example, you may prevent the user from selecting data from your Employees table but you give him the permission to execute a stored procedure named GetEmployeesRecords that selects the records from the Employees table.
So using stored procedures is very important for your development. Let's see how we can create T-SQL stored procedures. I say T-SQL stored procedures because with SQL Server 2005 and C# 2.0 you can write stored procedures in .NET managed code. In other words, you can write a stored procedure as a .NET class that uses the .NET Framework Class Library. I will discuss that in another article about using .NET managed stored procedures; for now, let's look at T-SQL stored procedures.
Next: CREATE PROCEDURE T-SQL Statement >>
More ASP.NET Articles
More By Michael Youssef
