2014年4月1日 星期二

在EntityFramework 6 使用預存程序

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

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

  • New Project

image

image

  • 安裝Entity Framework

image

  • Add New Model Class

image

image

  • 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

 

image

  • 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

image

  • 修改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

image

  • 工具會產生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

image

  • Add Controller

image

  • HomeController

image

  • 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
image

image

  • F5執行

image

  • 檢視DB

image

總網頁瀏覽量