如何在ASP.NET MVC中通过EF Code First用存储过程实现CRUD操作？

I get it, Code First can feel overwhelming when you're used to Database First. Let's break this down into super simple steps using your StudentInfo table. We'll cover two approaches: letting EF generate the stored procedures for you, and using custom ones you write yourself.

Step 1: Set Up Your Model #

First, create the StudentInfo class that matches your table columns:

public class StudentInfo
{
    public int Id { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
    public int RollNumber { get; set; }
}

Step 2: Create Your DbContext #

This is the bridge between your code and the database. We'll configure it to use stored procedures for CRUD operations:

using System.Data.Entity;

public class SchoolContext : DbContext
{
    // Constructor: connects to your database (update the connection string name as needed)
    public SchoolContext() : base("name=SchoolContext")
    {
    }

    // DbSet represents the StudentInfo table in the database
    public DbSet<StudentInfo> StudentInfos { get; set; }

    // Configure EF to use stored procedures for StudentInfo
    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        // Option 1: Let EF auto-generate CRUD stored procedures
        modelBuilder.Entity<StudentInfo>().MapToStoredProcedures();

        // Option 2: Use custom stored procedures (we'll cover this later)
        // modelBuilder.Entity<StudentInfo>()
        //     .MapToStoredProcedures(s =>
        //         s.Insert(i => i.HasName("InsertStudent"))
        //         .Update(u => u.HasName("UpdateStudent"))
        //         .Delete(d => d.HasName("DeleteStudent")));
    }
}

Step 3: Add Connection String #

In your Web.config file, add a connection string for your database (replace the placeholder values):

<connectionStrings>
    <add name="SchoolContext" 
         connectionString="Server=YOUR_SERVER_NAME;Database=SchoolDB;Trusted_Connection=True;" 
         providerName="System.Data.SqlClient" />
</connectionStrings>

Step 4: Enable Migrations & Create Database #

Open the Package Manager Console (Tools > NuGet Package Manager > Package Manager Console) and run these commands one by one:

Enable-Migrations
Add-Migration InitialCreate
Update-Database

This will create your database and generate the stored procedures (like StudentInfo_Insert, StudentInfo_Update, StudentInfo_Delete) automatically if you used Option 1.

Step 5: Perform CRUD Operations in Your Controller #

Now let's use these stored procedures in an MVC controller. Create a StudentController:

Insert a Student #

EF will automatically call the generated StudentInfo_Insert stored procedure when you save changes:

public ActionResult Create(StudentInfo student)
{
    if (ModelState.IsValid)
    {
        using (var db = new SchoolContext())
        {
            db.StudentInfos.Add(student);
            db.SaveChanges(); // Triggers StudentInfo_Insert SP
        }
        return RedirectToAction("Index");
    }
    return View(student);
}

Update a Student #

Similarly, SaveChanges() will call StudentInfo_Update:

public ActionResult Edit(StudentInfo student)
{
    if (ModelState.IsValid)
    {
        using (var db = new SchoolContext())
        {
            db.Entry(student).State = EntityState.Modified;
            db.SaveChanges(); // Triggers StudentInfo_Update SP
        }
        return RedirectToAction("Index");
    }
    return View(student);
}

Delete a Student #

Calls StudentInfo_Delete:

public ActionResult Delete(int id)
{
    using (var db = new SchoolContext())
    {
        var student = db.StudentInfos.Find(id);
        db.StudentInfos.Remove(student);
        db.SaveChanges(); // Triggers StudentInfo_Delete SP
    }
    return RedirectToAction("Index");
}

Read/Retrieve Students #

By default, EF uses a SELECT query, but if you want to use a stored procedure for reading, create a custom SP first:

-- Run this in your SQL Server Management Studio
CREATE PROCEDURE GetAllStudents
AS
BEGIN
    SELECT Id, Name, Age, RollNumber FROM StudentInfo
END

Then call it in your controller:

using System.Data.SqlClient;

public ActionResult Index()
{
    List<StudentInfo> students;
    using (var db = new SchoolContext())
    {
        // Execute the stored procedure and map results to StudentInfo objects
        students = db.Database.SqlQuery<StudentInfo>("GetAllStudents").ToList();
    }
    return View(students);
}

To get a single student by ID, create another SP:

CREATE PROCEDURE GetStudentById
    @Id INT
AS
BEGIN
    SELECT Id, Name, Age, RollNumber FROM StudentInfo WHERE Id = @Id
END

And call it like this:

public ActionResult Details(int id)
{
    StudentInfo student;
    using (var db = new SchoolContext())
    {
        student = db.Database.SqlQuery<StudentInfo>(
            "GetStudentById @Id", 
            new SqlParameter("@Id", id)
        ).SingleOrDefault();
    }
    return View(student);
}

Using Custom Stored Procedures for Insert/Update/Delete #

If you don't want EF's auto-generated SPs, write your own:

-- Insert SP
CREATE PROCEDURE InsertStudent
    @Name NVARCHAR(50),
    @Age INT,
    @RollNumber INT,
    @Id INT OUTPUT
AS
BEGIN
    INSERT INTO StudentInfo (Name, Age, RollNumber)
    VALUES (@Name, @Age, @RollNumber)
    SET @Id = SCOPE_IDENTITY() -- Return the new ID
END

-- Update SP
CREATE PROCEDURE UpdateStudent
    @Id INT,
    @Name NVARCHAR(50),
    @Age INT,
    @RollNumber INT
AS
BEGIN
    UPDATE StudentInfo
    SET Name = @Name, Age = @Age, RollNumber = @RollNumber
    WHERE Id = @Id
END

-- Delete SP
CREATE PROCEDURE DeleteStudent
    @Id INT
AS
BEGIN
    DELETE FROM StudentInfo WHERE Id = @Id
END

Then update the OnModelCreating method in your SchoolContext to map to these custom SPs:

protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
    modelBuilder.Entity<StudentInfo>()
        .MapToStoredProcedures(s =>
            s.Insert(i => i.HasName("InsertStudent")
                .Parameter(p => p.Name, "@Name")
                .Parameter(p => p.Age, "@Age")
                .Parameter(p => p.RollNumber, "@RollNumber")
                .Result(r => r.Id, "@Id")) // Map the output parameter
            .Update(u => u.HasName("UpdateStudent")
                .Parameter(p => p.Name, "@Name")
                .Parameter(p => p.Age, "@Age")
                .Parameter(p => p.RollNumber, "@RollNumber")
                .Parameter(p => p.Id, "@Id"))
            .Delete(d => d.HasName("DeleteStudent")
                .Parameter(p => p.Id, "@Id")));
}

Run Update-Database again to apply these changes, and your CRUD operations will now use your custom stored procedures.

That's it! Keep it simple, take it step by step, and you'll get the hang of it. Let me know if you hit any snags.

内容的提问来源于stack exchange，提问作者Awais Zafar