Friday, October 6, 2017

Call Stored Procedures from Entity Framework in ASP.NET MVC

Good day!
Here's an ASP.NET MVC example of a CRUD(Create/Update/Delete) application using stored procedures and Entity Framework 6.First, you need to perform steps 1-3 from this link Call Stored Procedures from Entity Framework 6 in C# (Part 1). For step 3, instead of creating a console application use ASP.NET MVC Empty project. Once done, the code for the controller and views are shown below:
CustomersController
private CustomerEntities db = new CustomerEntities();

// GET: Customers
public ActionResult Index()
{
 return View(db.Database.SqlQuery<Customer>("GetAllCustomers").ToList());
}

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

// POST: Customers/Create
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include = "CompanyName,ContactName,Address,Country,Phone")] Customer customer)
{
 if (ModelState.IsValid)
 {
  db.Database.ExecuteSqlCommand("EXEC dbo.InsertCustomer @CompanyName,@ContactName,@Address,@Country,@Phone",
   new SqlParameter("CompanyName",customer.CompanyName),
   new SqlParameter("ContactName", customer.ContactName),
   new SqlParameter("Address", customer.Address),
   new SqlParameter("Country", customer.Country),
   new SqlParameter("Phone", customer.Phone));

  return RedirectToAction("Index");
 }

 return View(customer);
}

// GET: Customers/Edit/5
public ActionResult Edit(int? id)
{
 if (id == null)
 {
  return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
 }
 Customer customer = db.Customers.Find(id);
 if (customer == null)
 {
  return HttpNotFound();
 }
 return View(customer);
}

// POST: Customers/Edit/5
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include = "CustomerID,CompanyName,ContactName,Address,Country,Phone")] Customer customer)
{
 if (ModelState.IsValid)
 {
  db.Database.ExecuteSqlCommand("EXEC dbo.UpdateCustomer @CustomerID,@CompanyName,@ContactName,@Address,@Country,@Phone",
    new SqlParameter("CustomerID", customer.CustomerID),
    new SqlParameter("CompanyName", customer.CompanyName),
    new SqlParameter("ContactName", customer.ContactName),
    new SqlParameter("Address", customer.Address),
    new SqlParameter("Country", customer.Country),
    new SqlParameter("Phone", customer.Phone));

  return RedirectToAction("Index");
 }
 return View(customer);
}

// GET: Customers/Delete/5
public ActionResult Delete(int? id)
{
 if (id == null)
 {
  return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
 }
 Customer customer = db.Customers.Find(id);
 if (customer == null)
 {
  return HttpNotFound();
 }
 return View(customer);
}

// POST: Customers/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
 db.Database.ExecuteSqlCommand("EXEC dbo.DeleteCustomer @CustomerID",
   new SqlParameter("CustomerID", id));

 return RedirectToAction("Index");
}
Index.cshtml
<div class="container">
    <h2>Customer List</h2>

    <p>
        @Html.ActionLink("Create New", "Create", "Customers", new { @class = "btn btn-primary" })
    </p>
    <table class="table table-bordered table-condensed">
        <tr>
            <th>
                @Html.DisplayNameFor(model => model.CompanyName)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.ContactName)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Address)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Country)
            </th>
            <th>
                @Html.DisplayNameFor(model => model.Phone)
            </th>
            <th>
                <span>Action</span>
            </th>
        </tr>

        @foreach (var item in Model)
        {
            <tr>
                <td>
                    @Html.DisplayFor(modelItem => item.CompanyName)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.ContactName)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Address)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Country)
                </td>
                <td>
                    @Html.DisplayFor(modelItem => item.Phone)
                </td>
                <td>
                    @Html.ActionLink("Edit", "Edit", new { id = item.CustomerID }, new { @class = "btn btn-info" })
                    @Html.ActionLink("Delete", "Delete", new { id = item.CustomerID }, new { @class = "btn btn-warning" })
                </td>
            </tr>
        }

    </table>

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

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

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

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

        <div class="form-group">
            @Html.LabelFor(model => model.Phone, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Phone, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Phone, "", 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-primary" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>
Edit.cshtml
@using (Html.BeginForm())
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>Edit Customer Record</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        @Html.HiddenFor(model => model.CustomerID)

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

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

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

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

        <div class="form-group">
            @Html.LabelFor(model => model.Phone, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.Phone, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.Phone, "", 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-primary" />
            </div>
        </div>
    </div>
}

<div>
    @Html.ActionLink("Back to List", "Index")
</div>
Delete.cshtml
<div class="container">
    <h4>Are you sure you want to delete this?</h4>
    <hr />
    <dl class="dl-horizontal">
        <dt>
            @Html.DisplayNameFor(model => model.CompanyName)
        </dt>

        <dd>
            @Html.EditorFor(model => model.CompanyName, new { htmlAttributes = new { @class = "form-control", @readonly = "true", @style = "background-color:white;" } })
        </dd>

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

        <dd>
            @Html.EditorFor(model => model.ContactName, new { htmlAttributes = new { @class = "form-control", @readonly = "true", @style = "background-color:white;" } })
        </dd>

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

        <dd>
            @Html.EditorFor(model => model.Address, new { htmlAttributes = new { @class = "form-control", @readonly = "true", @style = "background-color:white;" } })
        </dd>

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

        <dd>
            @Html.EditorFor(model => model.Country, new { htmlAttributes = new { @class = "form-control", @readonly = "true", @style = "background-color:white;" } })
        </dd>

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

        <dd>
            @Html.EditorFor(model => model.Phone, new { htmlAttributes = new { @class = "form-control", @readonly = "true", @style = "background-color:white;" } })
        </dd>

    </dl>

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

        <div class="form-actions no-color">
            <input type="submit" value="Delete" class="btn btn-danger" /> 
            @Html.ActionLink("Back to List", "Index", null, new { @class = "btn btn-primary"})
        </div>
    }
</div>
Expected Output
Source code: CallStoredProcedureWithASPMVC in Github

0 comments:

Post a Comment