DOT NET RULES

I have not been talking too much about databases or database design in this blog. There are some posts but this is not a blog about databases, database design or RDBMS. A brilliant blog for SQL Server databases is SQL Authority .Here you can find hundreds of articles and scripts for people that use SQL Server as their main RDBMS.

In this post I am going  to talk about stored procedures in conjunction with an SQL Server database.

I always try to write posts based on my day to day experience with people who attend the seminars I happen to be teaching. Some people are confused on why we need them, what they are and what they actually do.

As you know with Visual Studio 2008 and the new framework version (3.5 SP1 included) we have many different models-ways-methodologies that we can access a relational database, or the relational world, as I like to call it.

We have LINQ for example which we can use against any data source.  With LINQ we can express efficient query behavior in a programming language of our choice,  transform the query results into whatever format we desire, and manipulate the results.
LINQ to SQL is an object relational mapping  implementation that allows us to model a relational database using .NET classes.  We can query the database using LINQ, as well as update-insert-delete data from it.
LINQ to SQL designer provides an easy way to model and visualize a database as a LINQ to SQL object model.

So many people thought, “fantastic, I do not have to know anything about the relational world, database design,T-SQL and database objects.”

Well that is a huge mistake. For me anyone who is using the .NET platform to create data-driven applications must understand database design and how to manipulate the main database objects like views,triggers,stored procedures,tables,indexes.

You need that because many data driven applications out there do not use LINQ or Entity framework and there is a big chance that even if you create your application with LINQ you will need to utilise stored procedures as LINQ to SQL fully supports transactions, views, and stored procedures.

So you cannot be agnostic regarding the relational world. Having said that, please do learn all the new things that ship with the latest versions of .net framework and refer to data access.

• I will use SQL Server 2008 Standard edition to write,manipulate and execute some stored procedures. You can use SQL Server 2005 or any of the free SQL Server Express editions
• I will use the AdventureWorksLT database.SQL Server 2005-2008 does not install sample databases by default due to security reasons. We must download them and install them separately. You can download this free database from the codeplex website. Codeplex is the project hosting site for Microsoft SQL Server Samples and Community Projects.The down-loadable file, will  be a .msi file that you need to run and after successfully doing that, the .mdf and .ldf files will be placed somewhere on your hard disk. Then you can attach them to your local instance of your SQL Server from SQL Server Management Studio.
• For those that have Sql Server 2005 installed they need to go and download the file ,”AdventureWorksLT.msi”, from here
• For those that have Sql Server 2008 installed they need to go and download the file ,”SQL2008.AdventureWorks_All_Databases.x86.msi”, from here

I do not know if you are familiar with the sample databases that are available for us to download and use for free.The AdventureWorks database supports standard on-line transaction processing scenarios for a fictitious bicycle manufacturer (Adventure Works Cycles). Scenarios include Manufacturing, Sales, Purchasing, Product Management, Contact Management, and Human Resources.AdventureWorksLT (the database we are going to be using)  is a highly simplified and somewhat denormalized version of the Adventure Works OLTP database focused exclusively on a simple sales scenario. For more information for this database click here.

Please before you read more on this post, familiarise yourself  with this database.Have a look at the tables and the relations between the tables. It is not difficult at all.

1) Launch SQL Server Management Studio

2) Connect to the Local Instance of your SQL Server

3) Make the AdventureWorksLT database as the current database

4) Open a new query window and type the following simple T-SQL code that follows. Hit the execute button

select * from SalesLT.Customer where CustomerID=21

That will return a row of data from the Customers table. Have a look at the results pane, to see for yourself.

If I want to execute the above T-SQL code many times, I should create a stored procedure. So we can take the code above and create a stored procedure. Type the following

CREATE PROCEDURE uspGetCustomers
AS
select * from SalesLT.Customer where CustomerID=21

Hit the execute button. Congratulations, you have created your first stored procedure.

If you click on the Programmability ->Stored Procedures you will see the new database object, you have just created.

5) If you want to execute your new stored procedure, you simply type in the query window

exec uspGetCustomers

and hit the execute button. In the results pane you will get one row of data.

6) If you do not want the message that shows the count of the number of rows affected, in the Messages pane (1 row(s) affected), by the stored procedure , you must alter the stored procedure. How you do that?

7) Type the following

ALTER PROCEDURE uspGetCustomers
AS
SET NOCOUNT ON
select * from SalesLT.Customer where CustomerID=21

Hit the execute button. Then type

exec uspGetCustomers

You will notice that there are no messages created this time.

8) One can delete the stored procedure. The T-SQL to do that is

drop procedure uspGetCustomers

do not hit the execute button unless you want it actually deleted!!!

So far we have created,executed,altered  and deleted a stored procedure.

A simple and short definition of a stored procedure could be:

It is a collection of  one or more T-SQL statements that are compiled together and given a name that allows you to call and reuse those sql statements by simply calling that name.

1. You can pass information into the stored procedure as an input parameter
2. You can retrieve scalar values from a stored procedure using output parameters or return values
3. A stored procedure can return result sets
4. Stored procedures can be executed from a user query using the SQL Server Management Studio
5. Stored procedures can be executed from code, like a .NET application , using ADO.Net or LINQ to SQL as data access technologies.
6. Stored procedures can call other stored procedures

Why we should use stored procedures?What are we gaining from using a stored procedure? We could simply write the T-SQL statements in our .net application, without using a stored procedure.

• With stored procedures we can implement our  business  rules in a much easier and encapsulated way. When an application wants to access our corporate data through a stored procedure we can be certain that there will be no harm done to our data.

• Stored procedures allows various applications to use the stored procedure and hence share the logic of our application.Of course you can create components-classes and keep the business rules of your application in those components.

• Stored procedures help us to secure our application. We can restrict users to update or delete data in our database. Certain privileges can be granted to different users.

• The possibility of becoming a victim of SQL injection is almost eliminated with stored procedures.

• We can increase the performance of our application with stored procedures. When we call the the stored procedure many times, the execution plan is reused without having to rebuild the query tree or recompiling the execution plan. The execution plan troubles some people so we better give a short explanation. During the execution of a stored procedure , the query tree is created. A query tree is an internal representation of an SQL statement where the single parts that it is built from are stored separately. The query tree is passed to the SQL Server optimiser that compiles and optimises it.The optimiser scans the query tree and it develops a plan that is the best way to execute the data. This is the execution plan and that is what it is executed.

• When a stored procedure is called SQL Server will check if the execution plan exists in the cache and use this, if not it will create a new execution plan. If we have many calls to the stored procedure there is no need to create again a query tree or compile the plan again. Simply the existed execution plan is reused.

We have created a very simple stored procedure in our previous example. We will demonstrate how to use a stored procedure with an input parameter which allow us to have more generic-useful stored procedures.

1) Launch SQL Server Management Studio

2) Connect to the Local Instance of your SQL Server

3) Make the AdventureWorksLT database as the current database

4) Open a new query window and type the following simple T-SQL code that follows. Hit the execute button

CREATE PROCEDURE uspGetCustomers
@CustomerID Int
AS
SELECT *
FROM SalesLT.Customer
WHERE CustomerID = @CustomerID

In this example, I allow the application-user to pass to the procedure the customerID and the respective data gets returned for that customerID.

5) If you want to execute this procedure you can type in a query window

exec uspGetCustomers 18

6) You can have a stored procedure with 2 input parameters. Type the following T-SQL in a query window and hit the execute button. In this procedure we try to get the products with  their color and categories specified as input parameters.

CREATE PROCEDURE uspGetByColorAndCategory
@Color NVARCHAR(15),
@ProductCategoryID INT
AS
SELECT ProductID, Name, ProductNumber
FROM SalesLT.Product
WHERE Color = @Color
AND ProductCategoryID = @ProductCategoryID

7) If you want to execute this stored procedure, you can type

exec uspGetByColorAndCategory ‘Black’,'18′

8) Stored procedures can return back multiple result sets. Create a new procedure with the following code.Hit execute

CREATE PROCEDURE uspGetCustomersmultiple
AS
select * from SalesLT.Customer where CustomerID=21
select * from SalesLT.Customer where CustomerID=22

9) Execute the stored procedure by typing

exec uspGetCustomersmultiple

10) We can create a stored procedure using output parameters. In this example I need to retrieve a specific color and size scalar values for a given ProductID from the product table. So you can see the two variables that I declare and will use as output parameters. In this procedure I just pass in my output variables the values of Color and Size for the given ProductID which is the input parameter. So type the following statement in a query window and execute it.

CREATE PROCEDURE uspProducts
@ProductID int,
@Color nvarchar(5) OUTPUT,
@Size nvarchar(5) OUTPUT
AS
SELECT @Color = Color, @Size = Size
FROM SalesLT.Product
WHERE ProductID = @ProductID

11) We must declare two new variables to receive the values of the two output parameters. Then we need to execute the stored procedure by passing it a productID of our choice. Finally we just output in the query window the two values which are the output parameters.Type the following statements and hit execute.

DECLARE @myColor NVARCHAR(5)
DECLARE @mySize NVARCHAR(5)
exec uspProducts 680, @myColor OUT, @mySize OUT
SELECT @myColor + ‘ – ‘ + @mySize AS ProductDetails

That is all for now on this topic. For more info on stored procedures click here