Hi, today I am writing a blog related to the entity framework (EF) and asp.net mvc 5. Stored procedures (SP) makes life easy of coder. Writing complex queries in SQL server is easier rather than LINQ queries. changes in SQL queries have minor effects in C# code which can be understood and solve very easily.So guys lets start step by step. Please follow following steps.

Create a new database table “Login” in sql server.

Run following sql queries in your database(here my database name is ‘example’)

CREATE TABLE [dbo].[Login](

[id] [int] IDENTITY(1,1) NOT NULL,

[UserName] [varchar](50) NULL,

[Password] [nvarchar](50) NULL,

[Name] [varchar](50) NULL,

 CONSTRAINT [PK_Login] PRIMARY KEY CLUSTERED

(

[id] ASC

)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE PROC [dbo].[sp_UpdateLogin] @uname VARCHAR(50),@password NVARCHAR(50),@name VARCHAR(50)=' ',@id int

AS

UPDATE [Login] SET UserName=@uname, [Password]=@password ,Name=@name WHERE id=@id

 

CREATE proc [dbo].[sp_Delete] @id int

AS

delete FROM [Login] WHERE id=@id

 

CREATE proc [dbo].[sp_GetLogin]

as

select *from [Login]

 

CREATE PROC [dbo].[sp_getloginby_id] @id INT

AS

SELECT *FROM Login WHERE id=@id;



CREATE PROC [dbo].[sp_InsertLogin]

@uname VARCHAR(50),@password NVARCHAR(50),@name VARCHAR(50)=' '

as

INSERT INTO [Login](UserName,[Password],Name) VALUES(@uname,@password,@name)

Step1. Create New Project

Step2: Install Entity Framework using package manager console or nuget manager. here I am using package manager console.

Step3: Now we are going to create ado.net entity model. Right click on models folder and select ADO.net entity data model.

Step 4: Now select Table and all stored procedures.

Step 5: Check all stored procedures are in function import directory. EF execute SP only by functions.

Step 6: Create new controller “Home”

ASP.NET Core 2.0 Online Training

Step 7: Create View for Index action using scaffolding.

Step 7: You can update functions by right click them and by selecting edit option.

Step 8: we created Index.cshtml view using scaffolding now you can create views for Create, Delete and Insert.I am not telling you how we can do this , I already mention it in last blogs.

So write following code into your Home Controller and run it.

using EF_SP_NEW.Models;
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Mvc;

namespace EF_SP_NEW.Controllers
{
 public class HomeController : Controller
 {
//
 // GET: /Home/
 public ActionResult Index()
 {
 ExampleEntities db = new ExampleEntities();

var data = db.sp_GetLogin().ToList();
 return View(data);
 }

//
 // GET: /Home/Details/5
 public ActionResult Details(int? id)
 {
 ExampleEntities db = new ExampleEntities();
 SqlParameter param1 = new SqlParameter("@id", id);
 var data = db.Database.SqlQuery("exec sp_getloginby_id @id", param1).SingleOrDefault();

return View(data);
 }

//
 // GET: /Home/Create
 public ActionResult Create()
 {
return View();
 }

//
 // POST: /Home/Create
 [HttpPost]
 public ActionResult Create(Login logindata)
 {
 try
 {
 // TODO: Add insert logic here
 ExampleEntities db = new ExampleEntities();
 SqlParameter param1 = new SqlParameter("@Uname", logindata.UserName);
 SqlParameter param2 = new SqlParameter("@password", logindata.Password);
 SqlParameter param3 = new SqlParameter("@name", logindata.Name);
 var data = db.Database.SqlQuery("exec sp_InsertLogin @Uname ,@password,@name ", param1, param2, param3).SingleOrDefault();

//return View(data);
 return RedirectToAction("Index");
 }
 catch
 {
 return RedirectToAction("Index");
 }
 }

//
 // GET: /Home/Edit/5
 public ActionResult Edit(int id)
 {
 ExampleEntities db = new ExampleEntities();
 SqlParameter param1 = new SqlParameter("@id", id);
 var data = db.Database.SqlQuery("exec sp_getloginby_id @id", param1).SingleOrDefault();

return View(data);

}

//
 // POST: /Home/Edit/5
[HttpPost]
 public ActionResult Edit(int id, Login logindata)
 {
 try
 {
 // TODO: Add insert logic here
 ExampleEntities db = new ExampleEntities();
 SqlParameter param1 = new SqlParameter("@Uname", logindata.UserName);
 SqlParameter param2 = new SqlParameter("@password", logindata.Password);
 SqlParameter param3 = new SqlParameter("@name", logindata.Name);
 SqlParameter param4 = new SqlParameter("@id", id);
 var data = db.Database.SqlQuery("exec sp_UpdateLogin @Uname ,@password,@name, @id", param1, param2, param3, param4).SingleOrDefault();

//return View(data);
 return RedirectToAction("Index");
 }
 catch
 {
 return RedirectToAction("Index");
 }
 }

//
 // GET: /Home/Delete/5
 public ActionResult Delete(int id)
 {
 ExampleEntities db = new ExampleEntities();
 SqlParameter param1 = new SqlParameter("@id", id);
 var data = db.Database.SqlQuery("exec sp_getloginby_id @id", param1).SingleOrDefault();

return View(data);

}

//
 // POST: /Home/Delete/5
 [HttpPost]
 public ActionResult Delete(int id, string UserName)
 {
try
 {
 ExampleEntities db = new ExampleEntities();
 SqlParameter param1 = new SqlParameter("@id", id);
 var data = db.Database.SqlQuery("exec sp_Delete @id", param1).SingleOrDefault();
 return RedirectToAction("Index");
 }
 catch
 {
 return RedirectToAction("Index");
 }
 }
 }
}