Deborah's Developer MindScape






         Tips and Techniques for Web and .NET developers.

July 8, 2009

DAL: Using Stored Procedures

Filed under: C#,VB.NET @ 12:21 pm

A stored procedure is a set of structured query language (SQL) statements that provide a particular operation on the data (or structure) of a database and are stored within the database.

For example, a retrieval stored procedure contains a Select statement to select a set of data from one or more tables in a database and return the results.

CREATE PROCEDURE dbo.CustomerRetrieveAll
AS
    SELECT     
      CustomerId, 
      LastName, 
      FirstName
    FROM        
      Customer
    ORDER BY 
      LastName + ‘, ‘ + FirstName

An insert stored procedure contains an Insert statement to insert a new row in a table in the database and often a Select statement to return the Id of the new row.

CREATE PROCEDURE dbo.CustomerInsert
      @CustomerID      int output,
      @LastName        varchar(50),
      @FirstName       varchar(50)
AS
      INSERT INTO Customer
                 (LastName,
                 FirstName, 
                 LastUpdateDate)
      VALUES    
                (@LastName,
                @FirstName,
                GETDATE())
      /* Return the Id of the inserted row */
      SELECT  @CustomerID = @@Identity

An update stored procedure contains an Update statement to update a particular row in a table in the database.

CREATE PROCEDURE dbo.CustomerUpdate
      @CustomerID      int,
      @LastName        varchar(50),
      @FirstName       varchar(50)
AS

      UPDATE    Customer
        SET
            LastName = @LastName,
            FirstName = @FirstName,
            LastUpdateDate = GETDATE()
        WHERE
            CustomerID = @CustomerID

The benefits of stored procedures include:

  • Performance: Since they are stored in the database, they often execute more quickly then passing a SQL statement from your code. SQL Server compiles each stored procedure and then reuses the execution plan.
  • Reuse: A stored procedure can be reused across any number of applications that need to access the data.
  • Security: There can be security gains because permissions can be set on stored procedures separate from underlying table permissions.
  • Encapsulation: If the database structure is changed, the stored procedures can be adjusted to handle the change without having to modify any existing code. For example, a many-columned table is split into two related tables. This change could be handled in the stored procedures without affecting any code.

In a few cases, SQL statements in your code may be easier to use than stored procedures:

  • If your code needs to support multiple types of databases (SQL Server, Access, Oracle, MySQL), then using SQL statements provides a more general solution. Otherwise you would have to recreate the stored procedures (queries in Access) for each type of database. Though the process of building stored procedures for each type of database could be automated to some extent, it often may be easier to use SQL statements in this case.
  • If you have a end-user search feature that needs to provide extensive flexibility and search through large amounts of data, building SQL statements in your code might be easier than building a stored procedure.

In most cases, however, you will find that stored procedures help you better manage the data for your application.

So now you have decided to use stored procedures in your VB or C# application, how do you work with them? If you are using Visual Studio Professional or above, there are some helpful tools available.

Database Project

Visual Studio Professional Edition and above provide a Database project for managing your stored procedures and other database scripts. This feature has gotten a little confusing because there is now a Database Project under the “Other Project Types” node and a “Database Projects” node.

The Database Project under the “Other Project Types” is the simplest to use and is described here. Feel free to try out the project types under the “Data Projects” node at your leisure.

1. Right-click on the Solution and select Add | New Project.

image

2. Open the Other Project Types node and select Database. Enter a name for the Database Project and click OK.

image

3. A Database Project is defined for a specific database that you use during your development. If the database you want is listed, select it. Otherwise, select Add New Reference and define a connection to your database.

4. The Database Project is then added to your solution.

image

You can then manage your database scripts from this project. And if you have a source code control product in place, your database scripts will be managed by source control just like your other source files.

If you have existing tables or stored procedures, you can use the Server Explorer to automatically generate the scripts in the Database Project.

1. Right-click on a table or stored procedure and select Generate Create Script to Project.

image

2. The table or stored procedure script is generated and added to your Database project.

Enjoy!

2 Comments

  1.   Jack Dolby — July 9, 2009 @ 8:18 am    Reply

    Deborah, that was great! Exactly what I needed.

    You have a talent for putting descriptions into UNDERSTANDABLE language.

    Much appreciated.

    jack

  2.   Antonio.B — September 20, 2009 @ 7:18 am    Reply

    Very well-written article and very understandable. Also helped me with a problem I’ve had for some time, thanks!

RSS feed for comments on this post. TrackBack URI

Leave a comment

© 2019 Deborah's Developer MindScape   Provided by WPMU DEV -The WordPress Experts   Hosted by Microsoft MVPs