摘要
本文將向你介紹如何調用預存程序對SQL Server資料庫中的資料進行CRUD操作。文中使用的資料庫依然是在本系列第一篇文章《採用Model-First 開發方式建立資料庫》中建立的,使用的Web頁面是在第二篇文章《使用Entity Framework 進行CRUD操作》中建立的 ,可以點擊[代碼]下載鏈下載專案檔,要用VS2010開啟。
步驟1.建立預存程序
首先需要建立預存程序,第一個預存程序的功能是查表UserAccount記錄的,代碼如下:
CREATE PROCEDURE dbo.UserAccounts_SelectAll AS SET NOCOUNT ON SELECT Id, FirstName, LastName, AuditFields_InsertDate, AuditFields_UpdateDate FROM UserAccounts RETURN
下面一個預存程序功能是通過Id選擇UserAccount表中一條記錄,代碼如下:
CREATE PROCEDURE dbo.UserAccounts_SelectById ( @Id int )AS SET NOCOUNT ON SELECT Id, FirstName, LastName, AuditFields_InsertDate, AuditFields_UpdateDate FROM UserAccounts WHERE Id = @Id RETURN
第三個預存程序的功能是向UserAccounts表中插入一條記錄,代碼如下:
CREATE PROCEDURE dbo.UserAccounts_Insert( @FirstName nvarchar(50), @LastName nvarchar(50), @AuditFields_InsertDate datetime, @AuditFields_UpdateDate datetime) AS INSERT INTO UserAccounts (FirstName, LastName, AuditFields_InsertDate, AuditFields_UpdateDate) VALUES (@FirstName, @LastName, @AuditFields_InsertDate, @AuditFields_UpdateDate) SELECT SCOPE_IDENTITY() AS Id
第四個預存程序的功能是更新UserAccount表中的資料,代碼如下:
CREATE PROCEDURE dbo.UserAccounts_Update( @Id int, @FirstName nvarchar(50), @LastName nvarchar(50), @AuditFields_UpdateDate datetime) AS SET NOCOUNT ON UPDATE UserAccounts SET FirstName = @FirstName, LastName = @LastName, AuditFields_UpdateDate = @AuditFields_UpdateDate WHERE Id = @Id RETURN
最後一個預存程序的功能是刪除UserAccount表中的記錄,代碼如下:
CREATE PROCEDURE dbo.UserAccounts_Delete( @Id int ) AS SET NOCOUNT ON DELETE FROM UserAccounts WHERE Id = @Id RETURN
在我們進行下一步之前,你必須在OrderSystem資料庫中建立上面的那些預存程序。
步驟2:將預存程序添加到實體中
開啟項目中的OrderDB.edmx檔案。開啟後,會出現設計器,同時模型瀏覽器裡可以看到資料庫的實體,複雜屬性和關係。在OrderDB.edmx右鍵選擇Update Mode From DataBase,這時會出現更新嚮導,可以看到前面的建立的5個預存程序顯示出來。
展開Stored Procedures節點,選擇所有預存程序,然後點擊完成。這時模型瀏覽器中會多出5個預存程序對象。
右擊模型瀏覽器中的UserAccounts_SelectAll預存程序選擇Add Function Import,這時就可以給OrdersDBContainer類添加一個執行UserAccounts_SelectAll預存程序的方法了。
選擇預存程序執行後的傳回值類型,當我們要返回一個UserAccount列表,而Framework沒有提供這種類型,這時採用這個方法建立會很方便。我們可以先建立一個複雜類型用來表示預存程序傳回值的類型。由於SelectAll 和 SelectById 兩個預存程序返回的欄位相同,只需建立一種複雜類型。
點擊Get Column Information按鈕,VS將會檢測預存程序返回的欄位。當返回欄位檢測完後,點擊下面的Create New Complex Type 按鈕,這時會自動選擇Complex 選項,複雜類型的名字也會添加到右邊下列框中。預設的複雜類型的名字為預存程序的名字加上“Result”。由於將會在多個預存程序傳回值中使用該複雜類型,建議取一個比較通用的名字,這裡將名字改為“UserAccounts_Select_Result”,點擊OK。
這時你會看到在模型瀏覽器的Function Imports 檔案夾中多了一個UserAccounts_SelectAll方法,同時在複雜類型節點下面多了一個UserAccounts_Select_Result類型。
右擊模型瀏覽器中的UserAccounts_SelectById 預存程序選擇Add Function Import,然後選擇UserAccounts_Select_Result 類型作為該預存程序的傳回值類型,點擊OK。
下面是在UserAccount中使用插入,更新和刪除的預存程序,右擊設計器中的UserAccount實體選擇Stored Procedure Mapping,會出現詳細映射視窗。
點擊第一行的<Select Insert Function>,在下拉式清單中選擇UserAccounts_Insert ,在格子中會顯示參數列表。我們必須進行實體屬性到預存程序參數的映射。由於預存程序會返回新建立記錄的Id,必須選擇該Id賦值到哪個屬性。在Result Column Bindings節點下的<Add Result Binding>輸入Id,這裡返回的Id是預存程序中“SELECT SCOPE_IDENTITY() AS Id”,下面是綁定完成後的結果:
映射更 新的預存程序,將<Select Update Function>替換為UserAccounts_Update預存程序。其他的屬性自動對應完成,只有AuditFields_UpdateDate需要自動對應為AuditFields.UpdateDate屬性。
最後是映射Delete預存程序,將<Select Delete Function>替換為UserAccounts_Delete預存程序,Id參數映射到和實體的Id屬性。
不要忘了儲存上面的操作結果,OrdersDBContainer 現在就能使用那些預存程序了,從而進行UserAccount記錄的CRUD操作。
步驟3:建立Web表單
下面將在程式中建立一個表單,用來管理UserAccount資料。
1.在專案檔上右鍵,選擇Add->New Item..
2.選擇Web Form模板,將名字改為UsersSP.aspx,點“Add”。
3.在UserSP.aspx的div之間添加如下代碼:
<table> <tr> <td>Select A User:</td> <td><asp:DropDownList runat=server ID="ddlUsers" AutoPostBack="True"> </asp:DropDownList> </td> </tr> <tr> <td>First Name:</td> <td><asp:TextBox runat="server" ID="txtFirstName"></asp:TextBox></td> </tr> <tr> <td>Last Name:</td> <td><asp:TextBox runat="server" ID="txtLastName"></asp:TextBox></td> </tr> <tr> <td>Inserted:</td> <td><asp:Label runat="server" ID="lblInserted"></asp:Label> </td> </tr> <tr> <td>Updated:</td> <td><asp:Label runat="server" ID="lblUpdated"></asp:Label> </td> </tr></table> <asp:Button runat=server ID="btnSave" Text="Save" /><asp:Button ID="btnDelete" runat="server" Text="Delete" />
這裡採用的table的對form進行了簡單的布局。轉到Design視圖你會看到表單的樣子如下:
步驟4:將資料載入到Drop Down List中
要完成的是在頁面載入時,將UserAccount的Name和Id資料載入到Drop Down List中。當選擇特定項時,載入更加詳細的資訊。
1.雙擊Degsin視圖(F7),在後台代碼中添加Page_Load 事件。
2.處理Page_Load 事件的代碼如下:
protected void Page_Load(object sender, EventArgs e){ if (!IsPostBack) { LoadUserDropDownList(); }}
3.Page_Load方法中的LoadUserDropDownList方法代碼如下:
private void LoadUserDropDownList() { ddlUsers.DataSource = from u in db.UserAccounts_SelectAll() orderby u.LastName select new { Name = u.LastName + ", " + u.FirstName, Id = u.Id }; ddlUsers.DataTextField = "Name"; ddlUsers.DataValueField = "Id"; ddlUsers.DataBind(); ddlUsers.Items.Insert(0, new ListItem("Create New User", "")); } }
注意Linq的from語句,它調用OrderDBContainer的UserAccounts_SelectAll方法,這個方法將會執行預存程序。
DataTextField屬性設定為Name,DataValueField 設定為Id,這些都是在Linq查詢中建立的。設定完成後,就是綁定了。綁定時,才真正開始調用資料庫查詢的操作。最後給Drop Down List添加一項“Crate New User.”,這項是用來區分更新和添加操作的。
現在資料庫中還沒有任何資料,Drop Down List中只有"Crete New User"一項。
步驟5:添加和更新資料
下面將向你介紹如何添加和更新表中的資料。
1.轉到設計檢視,雙擊Save按鈕,建立該按鈕的點擊事件。
2.處理點擊事件的代碼如下:
using (OrderDBContainer db = new OrderDBContainer()) { UserAccount userAccount = new UserAccount(); userAccount.FirstName = txtFirstName.Text; userAccount.LastName = txtLastName.Text; userAccount.AuditFields.UpdateDate = DateTime.Now; if (ddlUsers.SelectedItem.Value == "") { //Adding userAccount.AuditFields.InsertDate = DateTime.Now; db.UserAccounts.AddObject(userAccount); } else { //Updating userAccount.Id = Convert.ToInt32(ddlUsers.SelectedValue); userAccount.AuditFields.InsertDate = Convert.ToDateTime(lblInserted.Text); db.UserAccounts.Attach(userAccount); db.ObjectStateManager.ChangeObjectState(userAccount, System.Data.EntityState.Modified); } db.SaveChanges(); lblInserted.Text = userAccount.AuditFields.InsertDate.ToString(); lblUpdated.Text = userAccount.AuditFields.UpdateDate.ToString(); //Reload the drop down list LoadUserDropDownList(); //Select the one the user just saved. ddlUsers.Items.FindByValue(userAccount.Id.ToString()).Selected = true; } }
代碼首先建立OrderDBContainer對象,再建立UserAccount對象,用輸入的值填充UserAccount對象屬性。更新日期用系統目前時間,接著判斷是更新操作還是添加操作了。最後就是更新Drop Down List的值並選中剛剛操作的UserAccout。這裡的db.SaveChanges()最後實際上是在資料庫中執行添加或更新語句。想資料庫添加資料時,開啟SQL Profiler會看到Insert預存程序被調用。
exec [dbo].[UserAccounts_Insert] @FirstName=N'Lloyd', @LastName=N'Sheng', @AuditFields_InsertDate='2010-04-26 18:14:42.4564241', @AuditFields_UpdateDate='2010-04-26 18:14:42.4564241'
步驟6:查詢資料
下面是實現當使用者選擇某一個Drop Down List項時,顯示詳細資料的功能。
1.雙擊視圖設計器中的Drop Down List,這時會建立Drop Down List的SelectedIndexChanged方法。
2.編寫SelectedIndexChanged方法的代碼如下:
if (ddlUsers.SelectedValue == ""){ txtFirstName.Text = ""; txtLastName.Text = ""; lblInserted.Text = ""; lblUpdated.Text = "";}else{ //Get the user from the DB using (OrderDBContainer db = new OrderDBContainer()) { int userAccountId = Convert.ToInt32(ddlUsers.SelectedValue); var userAccounts = from u in db.UserAccounts_SelectById(userAccountId) select u; txtFirstName.Text = ""; txtLastName.Text = ""; lblInserted.Text = ""; lblUpdated.Text = ""; foreach (UserAccounts_Select_Result userAccount in userAccounts) { txtFirstName.Text = userAccount.FirstName; txtLastName.Text = userAccount.LastName; lblInserted.Text = userAccount.AuditFields_InsertDate.ToString(); lblUpdated.Text = userAccount.AuditFields_UpdateDate.ToString(); } }}
代碼根據Drop Down List選擇的Id,調用UserAccounts_SelectById方法從資料庫中查詢一條資料並且顯示出來。
步驟7:刪除資料
最後就是刪除資料的功能了。 1.轉到視圖設計器,雙擊“Delete”按鈕。 2.添加如下代碼:
if using (OrderDBContainer db = new OrderDBContainer()){ if (ddlUsers.SelectedItem.Value != "") { UserAccount userAccount = new UserAccount(); userAccount.Id = Convert.ToInt32(ddlUsers.SelectedValue); db.UserAccounts.Attach(userAccount); db.ObjectStateManager.ChangeObjectState(userAccount, System.Data.EntityState.Deleted); db.SaveChanges(); LoadUserDropDownList(); txtFirstName.Text = ""; txtLastName.Text = ""; lblInserted.Text = ""; lblUpdated.Text = ""; }}
代碼首先建立了一個UserAccount對象,將它的Id設定為選中項的Id.然後將UserAccount附加到UserAccount集合中,設定它的狀態為刪除。調用SaveChanges操作,將該條資料刪除,重新整理Drop Down List的資料來源,搞定!
小結
第二篇文章中告訴你如何用Entity Framework進行CRUD操作,本篇文章張告訴你如何用預存程序和Entity Framework進行CRUD操作。
在下一篇文章中我將想你介紹如何在三層結構中使用Entity Framework,希望你能喜歡。
項目代碼:http://files.cnblogs.com/lloydsheng/OrderSystem3.zip
本文永久連結:http://lloydsheng.com/2010/04/aspnet40-entityframework4-execute-stored-procedures-using-entity-framework4.html