DotNetStories
Contents tagged with Stored procedures
-
Display Data using a Stored Procedure in an ASP.Net MVC 5.0 application with Entity Framework 6.0
In this post I will be looking into EF 6.0 using the Code First Workflow and its support for stored procedures and more particularly how to display data using a stored procedure. I have posted something similar in this post https://weblogs.asp.net/dotnetstories/using-stored-procedures-in-an-asp-net-mvc-5-0-application-using-entity-framework-6-0 but this post described in detail how to insert, update and delete data in an ASP.Net MVC 6.0 application using stored procedures but not how to display data using a stored procedure.
-
Using stored procedures with Entity Framework in an ASP.Net application
This is going to be the third post of a series of posts regarding ASP.Net and the Entity Framework and how we can use Entity Framework to access our datastore. You can find the first one here and the second one here.
-
Retrieving data using stored procedures with LINQ to SQL in an ASP.Net application
In this post I would like to present a step by step example on how to use stored procedures with LINQ to SQL.
-
Stored procedures with Linq to Sql in an ASP.Net application
First we need to create the 3 stored procedures that will insert,update and delete records from the Authors table
This is not a post on how to create stored procedures, so i am just going to paste here the complete stored procs.
DeleteAuthor
USE [pubs]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[DeleteAuthor]
@AuthorID nvarchar(20)
AS
BEGIN
DELETE FROM authors
WHERE au_id = @AuthorID
END
GO
UpdateAuthor
USE [pubs]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[UpdateAuthor]
@authorID varchar(11),
@lname nvarchar(50),
@fname nvarchar(50),
@phone char(12),
@address nvarchar(40),
@city nvarchar(40),
@state char(2),
@zip char(5),
@contract bit
AS
BEGIN
UPDATE authors
SET
au_lname=@lname,
au_fname=@fname,
phone=@phone,
address=@address,
city=@city,
state=@state,
zip=@zip,
contract=@contract
WHERE au_id = @authorID
END
GO
InsertAuthor
USE [pubs]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[InsertAuthor]
@id varchar(11),
@lName nvarchar(50),
@fname nvarchar(50),
@phone char(12),
@address nvarchar(40),
@city nvarchar(40),
@state char(2),
@zip char(5),
@contract bit
AS
BEGIN
INSERT INTO pubs.dbo.authors(
au_id,
au_lname,
au_fname,
phone,
address,
city,
state,
zip,
contract)
VALUES (
@id,
@lname,
@fname,
@phone,
@address,
@city,
@state,
@zip,
@contract)
END
GO
1) Launch Visual Studio 2008
2) Create an ASP.net web application. Use C# a your language of development
3) Name your project – solution as you want.
4) Open the Server Explorer and connect to the Pubs database.
5) Add a new item in your project, a Linq to SQL classes, a .dbml file. name it authors.dbml
6) Drag and drop from the Server explorer window the authors table into the designer area (on the .dbml file)
7) Right-Click on the designer area and show the “Show methods Pane”
8) Drag and drop the stored procedures from the Server explorer to the designer area
9) Select the author entity from the deisgner area and select the Properties window. In theDefault methods we need to assign the correct stored procs and not to leave the default option which is “use Runtime”. So please assign the different methods to their respective stored procs. Have a look at the picture below
10) We will use these stored procs that are methods now as far as LINQ is concerned to update,insert and delete records from the database.
11) Add a button in the Default.aspx file. Name it “Update”.Double click on this button. In the event handling routine type: