在EntityFramework 6 使用預存程序

Tool：Visual Studio 2013 Ultimate
OS：Windows 8.1
.NET Framework : 4.5.1

建立一個ASP.NETMVC5專案，利用Code First建立資料表，與預存程序，在Controller中叫用預存程序取得資料顯示在View上。

• New Project

• 安裝Entity Framework

• Add New Model Class

• Add Code:

namespace WebApplication9.Models {
  public class Employee {
    public int ID { get; set; }
    public string Name { get; set; }
    public int Age { get; set; }
  }
}

• Add DbContext Class

 

• Code

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

namespace WebApplication9.Models {
  public class MyDBContext : DbContext {
    public DbSet<Employee> Employees { get; set; }
    public ICollection<Employee> GetEmployeeByID( int id ) {
      return Database.SqlQuery<Employee>( "EXEC dbo.GetEmployeeByID @ID" , new SqlParameter( "ID" , id ) ).ToList( );
    }
  }
}

• enable-migrations

• 修改Configuration.cs

namespace WebApplication9.Migrations {
  using System;
  using System.Data.Entity;
  using System.Data.Entity.Migrations;
  using System.Linq;
  using WebApplication9.Models;

  internal sealed class Configuration : DbMigrationsConfiguration<WebApplication9.Models.MyDBContext> {
    public Configuration( ) {
      AutomaticMigrationsEnabled = true;
    }

    protected override void Seed( WebApplication9.Models.MyDBContext context ) {
     context.Employees.AddOrUpdate( p => p.Name ,
      new Employee { Name = "Mary" , Age = 30 } ,
      new Employee { Name = "Candy" , Age = 45 } ,
      new Employee { Name = "Jessie" , Age = 34 }
      );
    }
  }
}

• add-migration SP_GetEmployeeByID

• 工具會產生SP_GetEmployeeB類別

namespace WebApplication9.Migrations
{
    using System;
    using System.Data.Entity.Migrations;
   
    public partial class SP_GetEmployeeByID : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.Employees",
                c => new
                    {
                        ID = c.Int(nullable: false, identity: true),
                        Name = c.String(),
                        Age = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.ID);
           
        }
       
        public override void Down()
        {
            DropTable("dbo.Employees");
        }
    }
}

• 修改SP_GetEmployeeByID ，加入建立SP的程式

namespace WebApplication9.Migrations
{
    using System;
    using System.Data.Entity.Migrations;
   
    public partial class SP_GetEmployeeByID : DbMigration
    {
        public override void Up()
        {
            CreateTable(
                "dbo.Employees",
                c => new
                    {
                        ID = c.Int(nullable: false, identity: true),
                        Name = c.String(),
                        Age = c.Int(nullable: false),
                    })
                .PrimaryKey(t => t.ID);

          CreateStoredProcedure(
           "dbo.GetEmployeeByID" ,
           p => new {
             ID = p.Int( )
           } ,
           body:
               @"SELECT * from Employees where ID=@ID"
       );
        }
       
        public override void Down()
        {
            DropTable("dbo.Employees");
        }
    }
}

• update-database

• Add Controller

• HomeController

• Code，呼叫SP

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using WebApplication9.Models;

namespace WebApplication9.Controllers {
  public class HomeController : Controller {
    //
    // GET: /Home/
    private MyDBContext db = new MyDBContext( );
    public ActionResult Index( ) {
      return View( db.GetEmployeeByID( 1 ).ToList( ) );
    }
  }
}

• Add View
  

• F5執行

• 檢視DB