.NET Magazine國際中文電子雜誌
作 者:許薰尹
審 稿:張智凱
文章編號:N140414702
出刊日期:2014/4/22
開發工具:Visual Studio 2013 Ultimate
版本:.NET Framework 4.5.1
預存程序是一些事先寫好儲存在資料庫伺服器上的程式碼,一般而言使用預存程序與直接在程式中下達SQL指令兩者比起來,使用預存程序的整體效能會較佳。預設使用Entity Framework 6 Code First來建置資料存取程式時,會在應用程式之中直接下SQL語法進行資料的異動。在這一篇文章中,將介紹如何在Entity Framework 6 Code First架構下,使用預存程序來取代直接下達SQL指令以新增、刪除、修改、查詢資料庫的資料。
我們從建立一個ASP.NET MVC 5應用程式開始。啟動Visual Studio 2013開發環境。從「File」-「New」-「Project」,在「New Project」對話盒中,設定上方的清單的.NET Framework版本為「.NET Framework 4.5.1」;選取左方「Installed」清單-「Templates」-「Visual C#」程式語言,從「Web」分類中,選取「ASP.NET Web Application」。設定專案名稱為「EF6SP」。設定專案存放路徑,然後按下「OK」鍵,請參考下圖所示:

圖 1:建立一個ASP.NET MVC 5應用程式。
在「New ASP.NET Project」對話盒中選取「Empty」,勾選下方的「MVC」,然後按下「OK」按鈕建立專案,請參考下圖所示:

圖 2:建立空白MVC專案。
安裝Entity Framework套件
空白網站預設沒有安裝Entity Framework套件。我們可以從Visual Studio 2013開發工具-「Solution Explorer」- 專案 - 「Manage NuGet Packages」開啟「Manage NuGet Packages」對話盒,選取左方「Online」清單項目, 在右上方文字方塊中輸入「Entity Framework」關鍵字,搜尋「Entity Framework」,按下「Install」按鈕做安裝,請參考下圖所示:

圖 3:安裝Entity Framework套件。
新增Employee模型
下一步是定義模型,在此新增一個Employee類別。從Visual Studio 2013開發工具-「Solution Explorer」- 專案-「Models」目錄上方按滑鼠右鍵,從快捷選單選擇「Add」- 「New Item」,開啟「Add New Item」對話盒,從右上方文字方塊輸入「Class」搜尋,選取Class,設定名稱為「Employee」,然後按下「Add」按鈕,建立新類別,請參考下圖所示:

圖 4:新增Employee模型。
在Employee.cs檔案定義以下Employee類別,包含EmpId、Name與Age三個屬性:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
namespace EF6SP.Models {
public class Employee {
public int EmpId { get; set; }
public string Name { get; set; }
public int Age { get; set; }
}
}
重複上面的步驟定義MyDBContext類別。從Visual Studio 2013開發工具-「Solution Explorer」- 專案-「Models」目錄上方按滑鼠右鍵,從快捷選單選擇「Add」- 「New Item」,開啟「Add New Item」對話盒,從右上方文字方塊輸入「Class」搜尋,選取Class,設定名稱為「MyDBContext」,然後按下「Add」按鈕,建立MyDBContext類別,並加入以下程式碼:
using System;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Data.Entity;
using System.Linq;
using System.Web;
namespace EF6SP.Models {
public class MyDBContext : DbContext {
public DbSet<Employee> Employees { get; set; }
protected override void OnModelCreating( DbModelBuilder modelBuilder ) {
base.OnModelCreating( modelBuilder );
modelBuilder.Types<Employee>( ).Configure(
emp => {
emp.HasKey( e => e.EmpId );
emp.Property( e => e.EmpId ).HasDatabaseGeneratedOption( DatabaseGeneratedOption.Identity );
emp.Property( e => e.Name ).HasMaxLength( 50 );
emp.ToTable( "Employee" , "dbo" );
}
);
modelBuilder.Entity<Employee>( ).MapToStoredProcedures( );
}
public ICollection<Employee> GetEmployees( ) {
return Database.SqlQuery<Employee>( "EXEC dbo.GetEmployees" ).ToList( );
}
}
}
MyDBContext類別繼承System.Data.Entity命名空間下的 DbContext類別,其中的Employees屬性型別為DbSet< Employee>。預設Entity Framework會將模型中名為id屬性對應到資料表的主鍵欄位,因為此範例Employee類別中員工編號的名稱為EmpID,和預設不符,故範例中利用DbModelBuilder設定模型中EmpId屬性將對應到資料表主鍵欄位,且利用DatabaseGeneratedOption.Identity設定為自動編號欄位。並設定Name的長度為「50」。最後叫用MapToStoredProcedures()方法,以便於我們自訂要用來新增、刪除、修改資料庫資料的預存程序。此外,我們在MyDBContext類別中新增一個GetEmployees()方法,當叫用此方法,便可以呼叫資料庫中名為GetEmployees的預存程序。
選取Visual Studio 2013工具「Build」-「Build Solution」建置專案。
啟用移轉
從Visual Studio 2013開發工具「TOOLS」-「Library Package Manager」-「Package Manage Console」開啟「Package Manage Console」視窗,然後在提示字元中輸入enable-migrations,啟用Entity Framework資料庫移轉功能:
enable-migrations
此命令的執行結果,請參考下圖所示:

圖 5:啟用移轉。
接著Visual Studio 2013工具會自動在專案中產生一個「Migrations」資料夾,其中包含一個Configuration.cs檔案。修改Configuration.cs檔案中的程式碼,設定AutomaticMigrationsEnabled為「true」,並修改Seed方法,在其中加入初始化資料庫Employee資料表資料的程式碼:
namespace EF6SP.Migrations {
using EF6SP.Models;
using System;
using System.Data.Entity;
using System.Data.Entity.Migrations;
using System.Linq;
internal sealed class Configuration : DbMigrationsConfiguration<EF6SP.Models.MyDBContext> {
public Configuration( ) {
AutomaticMigrationsEnabled = true;
}
protected override void Seed( EF6SP.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 }
);
}
}
}
修改結構描述資訊
接著我們需要撰寫自訂的預存程序,在「Package Manage Console」視窗,然後在提示字元中輸入add-migration EmployeeSP指令:
add-migration EmployeeSP
Visual Studio 2013工具會在Migrations資料夾下,產生一個類別檔案,包含以下程式碼,用來建立資料表與預存程序。若需要客製化,可以修改其中用來做資料新增、刪除、修改的SQL語法:
namespace EF6SP.Migrations
{
using System;
using System.Data.Entity.Migrations;
public partial class EmployeeSP : DbMigration
{
public override void Up()
{
CreateTable(
"dbo.Employee",
c => new
{
EmpId = c.Int(nullable: false, identity: true),
Name = c.String(maxLength: 50),
Age = c.Int(nullable: false),
})
.PrimaryKey(t => t.EmpId);
CreateStoredProcedure(
"dbo.Employee_Insert",
p => new
{
Name = p.String(maxLength: 50),
Age = p.Int(),
},
body:
@"INSERT [dbo].[Employee]([Name], [Age])
VALUES (@Name, @Age)
DECLARE @EmpId int
SELECT @EmpId = [EmpId]
FROM [dbo].[Employee]
WHERE @@ROWCOUNT > 0 AND [EmpId] = scope_identity()
SELECT t0.[EmpId]
FROM [dbo].[Employee] AS t0
WHERE @@ROWCOUNT > 0 AND t0.[EmpId] = @EmpId"
);
CreateStoredProcedure(
"dbo.Employee_Update",
p => new
{
EmpId = p.Int(),
Name = p.String(maxLength: 50),
Age = p.Int(),
},
body:
@"UPDATE [dbo].[Employee]
SET [Name] = @Name, [Age] = @Age
WHERE ([EmpId] = @EmpId)"
);
CreateStoredProcedure(
"dbo.Employee_Delete",
p => new
{
EmpId = p.Int(),
},
body:
@"DELETE [dbo].[Employee]
WHERE ([EmpId] = @EmpId)"
);
}
public override void Down()
{
DropStoredProcedure("dbo.Employee_Delete");
DropStoredProcedure("dbo.Employee_Update");
DropStoredProcedure("dbo.Employee_Insert");
DropTable("dbo.Employee");
}
}
}
目前我們先不客製化資料異動程式碼,修改程式如下,呼叫CreateStoredProcedure方法新增一個名為「dbo.GetEmployees」的預存程序,查詢資料庫Employee資料表的所有資料:
namespace EF6SP.Migrations
{
using System;
using System.Data.Entity.Migrations;
public partial class EmployeeSP : DbMigration
{
public override void Up()
{
CreateTable(
"dbo.Employee",
c => new
{
EmpId = c.Int(nullable: false, identity: true),
Name = c.String(maxLength: 50),
Age = c.Int(nullable: false),
})
.PrimaryKey(t => t.EmpId);
CreateStoredProcedure(
"dbo.Employee_Insert",
p => new
{
Name = p.String(maxLength: 50),
Age = p.Int(),
},
body:
@"INSERT [dbo].[Employee]([Name], [Age])
VALUES (@Name, @Age)
DECLARE @EmpId int
SELECT @EmpId = [EmpId]
FROM [dbo].[Employee]
WHERE @@ROWCOUNT > 0 AND [EmpId] = scope_identity()
SELECT t0.[EmpId]
FROM [dbo].[Employee] AS t0
WHERE @@ROWCOUNT > 0 AND t0.[EmpId] = @EmpId"
);
CreateStoredProcedure(
"dbo.Employee_Update",
p => new
{
EmpId = p.Int(),
Name = p.String(maxLength: 50),
Age = p.Int(),
},
body:
@"UPDATE [dbo].[Employee]
SET [Name] = @Name, [Age] = @Age
WHERE ([EmpId] = @EmpId)"
);
CreateStoredProcedure(
"dbo.Employee_Delete",
p => new
{
EmpId = p.Int(),
},
body:
@"DELETE [dbo].[Employee]
WHERE ([EmpId] = @EmpId)"
);
CreateStoredProcedure(
"dbo.GetEmployees" ,
body:
@"Select * from Employee"
);
}
public override void Down()
{
DropStoredProcedure("dbo.Employee_Delete");
DropStoredProcedure("dbo.Employee_Update");
DropStoredProcedure("dbo.Employee_Insert");
DropStoredProcedure( "dbo.GetEmployees" );
DropTable("dbo.Employee");
}
}
}
選取Visual Studio 2013工具「Build」-「Build Solution」建置專案。
從Visual Studio 2013開發工具「TOOLS」-「Library Package Manager」-「Package Manage Console」開啟「Package Manage Console」視窗,然後在提示字元中輸入update-database:
update-database
執行結果請參考下圖所示:

圖 6:更新資料庫。
檢視資料庫結構描述資訊
完成以上的動作之後,資料庫已經建立了。預設資料庫會放在SQLExpress,若開發的電腦中沒有安裝SQLExpress,則預設資料庫將會是一個檔案型的LocalDB資料庫,放在App_Data資料夾下。我們來利用Visual Stuido 2013檢視新建立的資料庫以及其結構描述資訊。
開啟Visual Studio 2013 「Server Explorer」視窗,點選「Connect to Database」按鈕。在「Add Connection」視窗中,設以下屬性:
· 資料來源 (Data Source) :Microsoft SQL Server (SQLClient)。
· Server name欄位:輸入「.\SQLExpress」。
· Select or enter a database name欄位:選擇「MyDBContext」資料庫。
完成後應如下圖所示:

圖 7:建立連線。
從「Server Explorer」視窗中,你就可以看到新增的Employee資料表,以及預存程序,請參考下圖所示:

圖 8:自動建立的資料表與預存程序。
檢視Employee資料表定義。在「Server Explorer」視窗,Employee資料表項目上方按滑鼠右鍵,選取「Open Table Definition」,請參考下圖所示:

圖 9:Open Table Definition。
便可以看到其結構描述資訊,其中EmpID為自動編號欄位;Name的長度為「50」,請參考下圖所示:

圖 10:Employee資料表。
新增控制器
下一步我們來透過Visual Studio 2013的「Add Scaffold」功能來建立控制器,以及展現資料的檢視(View)。在「Solution Explorer」視窗,專案的「Controllers」資料夾上方,按滑鼠右鍵,選取「Add」-「Controller」,此時會開啟「Add Scaffold」視窗,選取「MVC 5 Controller with views, using Entity Framework」,然後按下「Add」按鈕,請參考下圖所示:

圖 11:「Add Scaffold」功能。
在「Add Controller」視出設定以下資料:
· Controller Name:「EmployeeController」。
· Model Class :「Employee」。
· Data context class:「MyDBContext」
· 勾選下方的「Generate views」,清除勾選其它核取方塊,請參考下圖所示:

圖 12:Add Controller。
Visual Studio 2013會自動在專案中「Views\Employee」資料夾下,產生Index.cshtml、Edit.cshtml、Details.cshtml、Delete.cshtml與Create.cshtml檔案,以及顯示資料的程式碼。以下則是Controller的程式碼:
namespace EF6SP.Controllers
{
public class EmployeeController : Controller
{
private MyDBContext db = new MyDBContext();
// GET: /Employee/
public ActionResult Index( ) {
return View( db.Employees.ToList( ) );
}
// GET: /Employee/Details/5
public ActionResult Details(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Employee employee = db.Employees.Find(id);
if (employee == null)
{
return HttpNotFound();
}
return View(employee);
}
// GET: /Employee/Create
public ActionResult Create()
{
return View();
}
// POST: /Employee/Create
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include="EmpId,Name,Age")] Employee employee)
{
if (ModelState.IsValid)
{
db.Employees.Add(employee);
db.SaveChanges();
return RedirectToAction("Index");
}
return View(employee);
}
// GET: /Employee/Edit/5
public ActionResult Edit(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Employee employee = db.Employees.Find(id);
if (employee == null)
{
return HttpNotFound();
}
return View(employee);
}
// POST: /Employee/Edit/5
// To protect from overposting attacks, please enable the specific properties you want to bind to, for
// more details see http://go.microsoft.com/fwlink/?LinkId=317598.
[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Edit([Bind(Include="EmpId,Name,Age")] Employee employee)
{
if (ModelState.IsValid)
{
db.Entry(employee).State = EntityState.Modified;
db.SaveChanges();
return RedirectToAction("Index");
}
return View(employee);
}
// GET: /Employee/Delete/5
public ActionResult Delete(int? id)
{
if (id == null)
{
return new HttpStatusCodeResult(HttpStatusCode.BadRequest);
}
Employee employee = db.Employees.Find(id);
if (employee == null)
{
return HttpNotFound();
}
return View(employee);
}
// POST: /Employee/Delete/5
[HttpPost, ActionName("Delete")]
[ValidateAntiForgeryToken]
public ActionResult DeleteConfirmed(int id)
{
Employee employee = db.Employees.Find(id);
db.Employees.Remove(employee);
db.SaveChanges();
return RedirectToAction("Index");
}
protected override void Dispose(bool disposing)
{
if (disposing)
{
db.Dispose();
}
base.Dispose(disposing);
}
}
}
測試
現在我們可以測試資料存取程式碼了。按CTRL+F5執行Index檢視,執行結果參考如下,當執行Emoloyee\Index View就可以看到資料表所有內容表列在網頁之中:

圖 13:Employee Index View。
在執行過程中,啟用Visual Studio 2013的IntelliTrace功能,記錄執行流程,則可以看到以下呼叫查詢資料時的記錄,直接下達Select語法進行查詢,請參考下圖所示:

圖 14:使用IntelliTrace追蹤查詢。
呼叫預存程序
讓我們改用自定的預存程序進行查詢,叫用GetEmployees預存程序,讀取資料。修改EmployeeController的Index()方法如下:
public ActionResult Index( ) {
return View( db.GetEmployees( ).ToList( ) );
}
按CTRL+F5執行Index檢視,執行結果和上圖相同。利用IntelliTrace記錄執行流程,則可以看到以下呼叫GetEmployee預存程序的記錄,請參考下圖所示:

圖 15:使用IntelliTrace追蹤查詢-使用預存程序。
INSERT
按CTRL+F5執行Index檢視,按下網頁上「Create New」連結,試著新增一筆資料,請參考下圖所示:

圖 16:新增資料。
使用IntelliTrace功能紀錄的執行結果,請參考下圖所示:

圖 17:使用IntelliTrace追蹤新增-使用預存程序。
UPDATE
試著點選網頁上的任一筆資料做修改,請參考下圖所示:

圖 18:修改資料。
將修改完的資料寫回資料庫,請參考下圖所示:

圖 19::修改資料。
使用IntelliTrace功能紀錄的執行結果,請參考下圖所示:

圖 20:使用IntelliTrace追蹤修改-使用預存程序。
DELETE
試著點選網頁上的任一筆資料做刪除,請參考下圖所示:

圖 21:刪除資料。
使用IntelliTrace功能紀錄的執行結果,請參考下圖所示:

圖 22:使用IntelliTrace追蹤刪除-使用預存程序。