.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追蹤刪除-使用預存程序。
訂閱