Download Project  (Full Project)

Step 1:

In this tutorial I will show how to Call Store Procedure using Entity Framework. First open SQL server management studio. Then create two tables and create stored procedure with join the table. Then pass the perimeter of stored procedure for get the value. Then exsiccate the SQL command. Given bellow the SQL code:

USE [TestDB]
GO
/****** Object:  StoredProcedure [dbo].[GetDataByIdName]    Script Date: 3/22/2019 5:05:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- ============================================= GetDataByIdName 0,null
CREATE PROCEDURE [dbo].[GetDataByIdName]
	@id int,
	@name nvarchar(max)=null
AS
BEGIN
	SELECT P.*,C.catName from [dbo].[Post] P
	JOIN [dbo].[Category] C ON P.catId=C.catId
	--where p.PostId=case when @id<>0 then @id else p.PostId end
	--and p.PostName=case when @name is not null then @name else p.PostName end
END

GO
/****** Object:  Table [dbo].[Category]    Script Date: 3/22/2019 5:05:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Category](
	[catId] [int] IDENTITY(1,1) NOT NULL,
	[catName] [nvarchar](50) NULL,
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[catId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
/****** Object:  Table [dbo].[Post]    Script Date: 3/22/2019 5:05:29 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Post](
	[PostId] [int] IDENTITY(1,1) NOT NULL,
	[PostWeight] [int] NULL,
	[PostName] [varchar](max) NULL,
	[catId] [int] NULL,
 CONSTRAINT [PK_Post] PRIMARY KEY CLUSTERED 
(
	[PostId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[Category] ON 

GO
INSERT [dbo].[Category] ([catId], [catName]) VALUES (1, N'Plan')
GO
INSERT [dbo].[Category] ([catId], [catName]) VALUES (2, N'Development')
GO
INSERT [dbo].[Category] ([catId], [catName]) VALUES (3, N'End')
GO
INSERT [dbo].[Category] ([catId], [catName]) VALUES (4, N'Processing')
GO
SET IDENTITY_INSERT [dbo].[Category] OFF
GO
SET IDENTITY_INSERT [dbo].[Post] ON 

GO
INSERT [dbo].[Post] ([PostId], [PostWeight], [PostName], [catId]) VALUES (4, 1, N'reza', 1)
GO
INSERT [dbo].[Post] ([PostId], [PostWeight], [PostName], [catId]) VALUES (5, 5, N'rezsa', 2)
GO
INSERT [dbo].[Post] ([PostId], [PostWeight], [PostName], [catId]) VALUES (6, 1, N'hello', 3)
GO
INSERT [dbo].[Post] ([PostId], [PostWeight], [PostName], [catId]) VALUES (7, 1, N'hello2', 4)
GO
INSERT [dbo].[Post] ([PostId], [PostWeight], [PostName], [catId]) VALUES (8, 3, N'myTask', 2)
GO
INSERT [dbo].[Post] ([PostId], [PostWeight], [PostName], [catId]) VALUES (9, 8, N'yellow', 2)
GO
INSERT [dbo].[Post] ([PostId], [PostWeight], [PostName], [catId]) VALUES (10, 2, N'red', 3)
GO
INSERT [dbo].[Post] ([PostId], [PostWeight], [PostName], [catId]) VALUES (11, 2, N'<p>gfhh</p>', 1)
GO
SET IDENTITY_INSERT [dbo].[Post] OFF
GO
ALTER TABLE [dbo].[Post]  WITH CHECK ADD  CONSTRAINT [FK_Post_Category] FOREIGN KEY([catId])
REFERENCES [dbo].[Category] ([catId])
GO
ALTER TABLE [dbo].[Post] CHECK CONSTRAINT [FK_Post_Category]
GO

Step 2:

Now open visual studio and create asp.net web application and select MVC. Now install ADO.NET entity framework  under model folder  also connect  table. Now call the stored procedure from the controller. Given bellow the controller code:

using SP_TUT.Models;
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace SP_TUT.Controllers
{
    public class HomeController : Controller
    {
        TestDBEntities context = new TestDBEntities();
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult About()
        {
            ViewBag.Message = "Your application description page.";

            return View();
        }

        public ActionResult Contact()
        {
            ViewBag.Message = "Your contact page.";

            return View();
        }
        public ActionResult List()
        {
            string name = null;
            int? id = null;
            SqlParameter[] param = new SqlParameter[]{
                new SqlParameter("@id",id??(object)DBNull.Value),
                new SqlParameter("@name",name??(object)DBNull.Value)
            };
            var data = context.Database.SqlQuery<PostDetail>("GetDataByIdName @id,@name", param).ToList();
            return View(data);
        }
    }
}

Step 3:

Now create a view and pass the data list by the return key word. View is reserve the list by the model and shows the view by the rezor syntax. Given bellow the view code list.cshtml:

@model IEnumerable<SP_TUT.Models.PostDetail>

@{
    ViewBag.Title = "List";
}

<h2>List</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.PostWeight)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.PostName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.catId)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.catName)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.PostWeight)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.PostName)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.catId)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.catName)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.PostId }) |
            @Html.ActionLink("Details", "Details", new { id=item.PostId }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.PostId })
        </td>
    </tr>
}

</table>

Step 4:

Model is represent the data for view and also this model is use for save or store the data from database given bellow the model code:

using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;
using System.Web;

namespace SP_TUT.Models
{
    public class PostDetail
    {
        public int PostId { get; set; }
        public Nullable<int> PostWeight { get; set; }
        public string PostName { get; set; }
        public Nullable<int> catId { get; set; }
        [NotMapped]
        public string catName { get; set; }
    }
}

Step 5:

Now build and run the project.