ASP.NET 4.0 and the Entity Framework 4 - Part 3 - Execute Stored Procedures Using the Entity Framework 4
page 2 of 9
by Vince Varallo
Feedback
Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 38079/ 79

Step 1: Create the Stored Procedures

The first step is to create the stored procedures in the database.  The first stored procedure is used to select all the records from the UserAccounts table.

CREATE PROCEDURE dbo.UserAccounts_SelectAll     
AS
      SET NOCOUNT ON
 
      SELECT Id, FirstName, LastName, AuditFields_InsertDate, 
             AuditFields_UpdateDate
        FROM UserAccounts
 
      RETURN

The next procedure will select a single record from the UserAccounts table using the Id in the where clause.

CREATE PROCEDURE dbo.UserAccounts_SelectById    
(
      @Id int     
)
AS
      SET NOCOUNT ON 
 
      SELECT Id, FirstName, LastName, AuditFields_InsertDate, 
             AuditFields_UpdateDate
        FROM UserAccounts
       WHERE Id = @Id
 
      RETURN

The third procedure inserts a record into the UserAccounts table.  This takes all the fields as parameters and returns the Id of the inserted record.

CREATE PROCEDURE dbo.UserAccounts_Insert
(
      @FirstName nvarchar(50),
      @LastName nvarchar(50),
      @AuditFields_InsertDate datetime,
      @AuditFields_UpdateDate datetime
)     
AS
      INSERT INTO UserAccounts (FirstName, LastName, AuditFields_InsertDate, 
                                AuditFields_UpdateDate)
      VALUES (@FirstName, @LastName, @AuditFields_InsertDate, 
              @AuditFields_UpdateDate)
 
      SELECT SCOPE_IDENTITY() AS Id

The fourth procedure updates a record in the UserAccounts table.

CREATE PROCEDURE dbo.UserAccounts_Update
(
      @Id int,
      @FirstName nvarchar(50),
      @LastName nvarchar(50), 
      @AuditFields_UpdateDate datetime
)     
AS
      SET NOCOUNT ON
 
      UPDATE UserAccounts
         SET FirstName = @FirstName,
             LastName = @LastName,
             AuditFields_UpdateDate = @AuditFields_UpdateDate
       WHERE Id = @Id
 
      RETURN

The fifth procedure deletes a record from the UserAccounts table.

CREATE PROCEDURE dbo.UserAccounts_Delete
(
      @Id int     
)     
AS
      SET NOCOUNT ON
 
      DELETE 
        FROM UserAccounts
       WHERE Id = @Id
 
      RETURN

You should create all of these stored procedures in the OrderSystem database before moving to step 2.


View Entire Article

User Comments

Title: Greate Writer   
Name: Alex
Date: 2012-09-04 11:54:02 AM
Comment:
Vince, is a great author. Looking forward from him a new book in asp.net 4.5 enterprise application development with all the latest features. EF, etc
Title: Stored Procedure doesn't return columns   
Name: Oscar
Date: 2011-12-28 10:18:54 AM
Comment:
I'm following your tutorial (very well done and thanks). When I reached the point to add the function import for the Select_all stored procedure I get the answer that the Stored Procedure doesn't return columns (My best translation from spanish). If I execute it from the sms client works fine.
Looked around alredy and didn't find anything. May be you could give a hint on where to look for a solution?
Title: Excellent   
Name: John
Date: 2011-05-05 4:00:03 PM
Comment:
excellent article
Title: Thanks a Ton!   
Name: Vipul
Date: 2011-03-22 12:02:05 AM
Comment:
Very helpful article indeed!
Title: Excellent   
Name: Sathiya
Date: 2011-01-09 6:04:12 AM
Comment:
Varallo,
Great Article and very impressive. Please post more article related to Framework 4.

Product Spotlight
Product Spotlight 





Community Advice: ASP | SQL | XML | Regular Expressions | Windows


©Copyright 1998-2024 ASPAlliance.com  |  Page Processed at 2024-03-29 8:56:01 AM  AspAlliance Recent Articles RSS Feed
About ASPAlliance | Newsgroups | Advertise | Authors | Email Lists | Feedback | Link To Us | Privacy | Search