asp.net mvc stored procedure using entity framework

Database :

-- Login Table 

CREATE TABLE [dbo].[login](
	[id] [int] IDENTITY(1,1) NOT NULL,
	[username] [varchar](50) NULL,
	[password] [nvarchar](50) NULL,
	[lastlogin] [datetime] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

-- Stored Procedure
-- Stored Procedure to fetch login table data
Create proc [dbo].[sp_getlogin]
AS
select *from login

-- Fetch Login table data on the basis of id
Create proc [dbo].[sp_getloginbyid] @id int
as
select *from login where id=@id

-- Insert Record stored procedure into login table

Create proc [dbo].[sp_logininsert] @username VARCHAR(50), @password VARCHAR(50)
AS
insert into login(username,password) values(@username, @password)

-- Update Login Table Record using stored procedure 
Create proc [dbo].[sp_loginupdate] @id int ,  @username VARCHAR(50), @password VARCHAR(50) 
AS
update [login] set username=@username , password=@password where id=@id

-- Delete Login Table Record using stored procedure 
Create proc [dbo].[sp_deletelogin] @id INT
AS
delete from login where id=@id

Home Controller

using System.Linq;
using System.Web.Mvc;
using SPEx.Models;
using System.Data.SqlClient;

namespace SPEx.Controllers
{
    public class HomeController : Controller
    {
        // GET: Home
        public ActionResult Index()
        {
            var db = new angularEntities();
            var data = db.sp_getlogin();
            return View(data.ToList());
        }

        // GET: Home/Details/5
        public ActionResult Details(int id)
        {
            var db = new angularEntities();
            SqlParameter param1 = new SqlParameter("@id", id);
            var data = db.Database.SqlQuery<login>("exec sp_getloginbyid @id", param1).SingleOrDefault();
            return View(data);
        }

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

        // POST: Home/Create
        [HttpPost]
        public ActionResult Create(login obj)
        {
            try
            {
                var db = new angularEntities();
                SqlParameter param1 = new SqlParameter("@username", obj.username);
                SqlParameter param2 = new SqlParameter("@password", obj.password);
                var data = db.Database.ExecuteSqlCommand("sp_logininsert @username , @password", param1, param2);

                // TODO: Add insert logic here

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

        // GET: Home/Edit/5
        public ActionResult Edit(int id)
        {
            var db = new angularEntities();
            SqlParameter param1 = new SqlParameter("@id", id);
            var data = db.Database.SqlQuery<login>("exec sp_getloginbyid @id", param1).SingleOrDefault();
            return View(data);
        }

        // POST: Home/Edit/5
        [HttpPost]
        public ActionResult Edit(int? id, login obj)
        {


            var db = new angularEntities();
            SqlParameter param1 = new SqlParameter("@username", obj.username);
            SqlParameter param2 = new SqlParameter("@password", obj.password);
            SqlParameter param3 = new SqlParameter("@id", id);
            var data = db.Database.ExecuteSqlCommand("sp_loginupdate @id, @username, @password", param3, param1, param2);
            //  db.SaveChanges();
            return RedirectToAction("Index");
        }

        // GET: Home/Delete/5
        public ActionResult Delete(int id)
        {
            var db = new angularEntities();
            SqlParameter param1 = new SqlParameter("@id", id);
            var data = db.Database.SqlQuery<login>("exec sp_getloginbyid @id", param1).SingleOrDefault();
         
            return View(data);
        }

        // POST: Home/Delete/5
        [HttpPost]
        public ActionResult Delete(int id, login collection)
        {
            try
            {
                // TODO: Add delete logic here
               

                var db = new angularEntities();
                SqlParameter param = new SqlParameter("@id", id);
                db.Database.ExecuteSqlCommand("sp_deletelogin @id", param);
                return RedirectToAction("Index");
            }
            catch
            {
                return View();
            }
        }
        public ActionResult dd()
        {
            var db = new angularEntities();
            ViewBag.dddata = new SelectList(db.logins.ToList(), "id", "username");

            return View();
        }
        public ActionResult submitdata()
        {
            return View();
        }
    }
}

Index.cshtml

@model IEnumerable<SPEx.Models.login>

@{
    ViewBag.Title = "Index";
}

<h2>Index</h2>

<p>
    @Html.ActionLink("Create New", "Create")
</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.username)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.password)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.lastlogin)
        </th>
        <th></th>
    </tr>

@foreach (var item in Model) {
    <tr>
        <td>
            @Html.DisplayFor(modelItem => item.username)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.password)
        </td>
        <td>
            @Html.DisplayFor(modelItem => item.lastlogin)
        </td>
        <td>
            @Html.ActionLink("Edit", "Edit", new { id=item.id }) |
            @Html.ActionLink("Details", "Details", new { id=item.id }) |
            @Html.ActionLink("Delete", "Delete", new { id=item.id })
        </td>
    </tr>
}

</table>

Create.cshtml

@model SPEx.Models.login

@{
    ViewBag.Title = "Create";
}

<h2>Create</h2>


@using (Html.BeginForm()) 
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>login</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.username, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.username, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.username, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.password, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.password, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.password, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.lastlogin, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.lastlogin, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.lastlogin, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Create" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>

Edit.cshtml

@model SPEx.Models.login

@{
    ViewBag.Title = "Edit";
}

<h2>Edit</h2>


@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>login</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        @Html.HiddenFor(model => model.id)

        <div class="form-group">
            @Html.LabelFor(model => model.username, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.username, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.username, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.password, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.password, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.password, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.lastlogin, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.lastlogin, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.lastlogin, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input type="submit" value="Save" class="btn btn-default" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>

<script src="~/Scripts/jquery-1.10.2.min.js"></script>
<script src="~/Scripts/jquery.validate.min.js"></script>
<script src="~/Scripts/jquery.validate.unobtrusive.min.js"></script>

Details.cshtml

@model SPEx.Models.login

@{
    ViewBag.Title = "Details";
}

<h2>Details</h2>

<div>
    <h4>login</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.username)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.username)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.password)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.password)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.lastlogin)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.lastlogin)
        </dd>

    </dl>
</div>
<p>
    @Html.ActionLink("Edit", "Edit", new { id = Model.id }) |
    @Html.ActionLink("Back to List", "Index")
</p>

Delete.cshtml

@model SPEx.Models.login

@{
    ViewBag.Title = "Delete";
}

<h2>Delete</h2>

<h3>Are you sure you want to delete this?</h3>
<div>
    <h4>login</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.username)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.username)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.password)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.password)
        </dd>

        <dt>
            @Html.DisplayNameFor(model => model.lastlogin)
        </dt>

        <dd>
            @Html.DisplayFor(model => model.lastlogin)
        </dd>

    </dl>

    @using (Html.BeginForm()) {
        @Html.AntiForgeryToken()

        <div class="form-actions no-color">
            <input type="submit" value="Delete" class="btn btn-default" /> |
            @Html.ActionLink("Back to List", "Index")
        </div>
    }
</div>

Part-1 Video

Part-2 Video

1 Comment

Submit a Comment Cancel reply