如何在ASP.Net MVC（C＃）中调用和执行存储过程 [英] How to call and execute stored procedures in ASP.Net MVC(C#)

问题描述

晚安，我在这里有些困惑。我已经使用ASP.NET MVC和C＃在Visual Studio中创建了数据库，模型，控制器和视图，但是我不知道如何调用我也创建的存储过程。

Good day guys, I'm in a little limbo here. I have created my database, model, controller and view in visual studio using ASP.NET MVC and C#, but I can't figure out how to call a stored procedure that I created also.

我希望在放置在视图中的按钮上调用存储过程。
此存储过程应在单击按钮时执行并显示结果。
以下是我创建的存储过程，视图，模型和控制器。

I want for the stored procedure to be called on a button I placed in my view. This stored procedure should execute and display results when the button is click. Below are the Stored procedure, view, model and controller I created.

这是我的员工模型：

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

namespace MVCSimpleApp.Models
{
    [Table("Employees")]
    public class Employee
    {
        [Display(Name ="Employee Id")]
        public int EmployeeId { get; set; }
        [Display(Name ="First Name")]
        public string FirstName { get; set; }
        [Display(Name ="Last Name")]
        public string LastName { get; set; }
    }
}

这是我的数据上下文：

using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Web;

namespace MVCSimpleApp.Models
{
    public class EmployeeContext : DbContext
    {
        public DbSet<Employee> Employee { get; set; }
    }
}

这是我的员工控制者：

using MVCSimpleApp.Models;
using System;
using System.Collections.Generic;
using System.Data.Entity;
using System.Linq;
using System.Net;
using System.Web;
using System.Web.Mvc;

namespace MVCSimpleApp.Controllers
{
    public class EmployeeController : Controller
    {
        private EmployeeContext db = new EmployeeContext();
        // GET: Employee
        public ActionResult Index()
        {

            var employees = from e in db.Employee select e;
            return View(employees);
        }
    }
 }

现在这是我存储的程序。

And now this is my Stored procedure. It is not much, just something for practice purpose.

Create Proc DisplayStudents
AS
BEGIN
     /*selecting all records from the table whose name is "Employee"*/
    Select * From Employee
END

这是我的观点：

@model IEnumerable<MVCSimpleApp.Models.Employee>

@{
    ViewBag.Title = "Index";
    Layout = "~/Views/Shared/_Layout.cshtml";
 }

 <h2>Student List</h2>

 <p>
    <a href="@Url.Action("Create")" title="Add new" class="btn btn-primary btn-lg">
        <span class="glyphicon glyphicon-plus "></span>
        Add Student
    </a>


</p>
<table class="table">
    <tr>
        <th>
            @Html.DisplayNameFor(model => model.EmployeeId)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.FirstName)
        </th>
        <th>
            @Html.DisplayNameFor(model => model.LastName)
        </th>
        <th></th>
    </tr>

 @foreach (var item in Model) {
 <tr>
    <td>
        @Html.DisplayFor(model => item.EmployeeId)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.FirstName)
    </td>
    <td>
        @Html.DisplayFor(modelItem => item.LastName)
    </td>
    <td>
        <span>
            <a href="@Url.Action("Edit", new { id = item.EmployeeId})" title="Edit Record">
                <span class="glyphicon glyphicon-pencil"></span>
            </a>
        </span>
        |
        <span>
            <a href="@Url.Action("Details", new { id = item.EmployeeId})" title="View Details">
                <span class="glyphicon glyphicon-th-list"></span>
            </a>
        </span>
        |
        <span>
            <a href="@Url.Action("Delete", new { id = item.EmployeeId})" title="Delete">
                <span class="glyphicon glyphicon-trash"></span>
            </a>
        </span>
    </td>
</tr>
}
  /*this is the button I want the stored procedure to be called on when I click it*/
  <button>Run</button>
</table>

请大家我需要您的意见和反馈。将接受将参数传递给存储过程的提示。如果我不在此处执行操作，请指正我。谢谢您的关注。

Please guys I need your opinions and feedback on this matter. Will accept tips in passing parameters to a stored procedure. Please correct me if I am not doing things right here. Thanks for your concern.

推荐答案

如果不需要使用EF，则可以通过以下方式进行：

If using EF is not a necessity you can do it in the following way:

string cnnString = System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionStringName"].ConnectionString;

SqlConnection cnn = new SqlConnection(cnnString);
SqlCommand cmd = new SqlCommand();
cmd.Connection = cnn;
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandText = "ProcedureName";
//add any parameters the stored procedure might require
cnn.Open();
object o = cmd.ExecuteScalar();
cnn.Close();

如果您需要使用实体框架，请查看此讨论。您还想使用存储过程进行插入，更新和删除签出本教程，来自微软。

If you need to use Entity Framework check out this discussion. Also you want to use the Stored Procedures for Inserting, Updating and deleting check out this tutorial from Microsoft.

要通过按钮执行代码，您可以在窗体中创建一个仅放置一个按钮的窗体，如下所示：

To execute the code from a button click you can create a form an place just one button inside the form like this:

@using(Html.BeginForm("TestAction", "TestController", FormMethod.Get))
{
    <input type="submit" value="Submit" />
}

在您的控制器中，您将有一个这样的TestAction方法

And in your controller you would have a TestAction method like this

public ActionResult TestAction(){....}

如果需要将任何参数传递给TestAction，只需在方法中将它们指定为参数，然后使用BeginForm的重载版本即可接受actionName，controllerName，routeValues和formMethod作为参数。




要将结果传递给视图，您需要根据从存储过程中接收到的值创建一个具有属性的视图模型，然后返回通过TestAction方法使用视图模型创建视图。

if you need to pass any arguments to TestAction, just specify them as parameters in the method and then use the overloaded version of BeginForm that accepts actionName, controllerName, routeValues and formMethod as arguments.

To pass the results to a view you need to create a view model with properties according to the values you recieve from the stored procedure and then, return a view with the view model from the TestAction method.

这篇关于如何在ASP.Net MVC（C＃）中调用和执行存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！