本篇演示一對多關係,和兩個表聯合查詢。
使用已有數據庫,我們演示一個家庭理財的小程序
本文參考Teddy's Knowledge Base的相關文章,這篇文章只演示單表操作.
Step1: 創建數據庫Family
Account表:記錄每一筆消費,列ConsumeCategory是消費的類別。
ConsumeCatagory表:消費類別。
ConsumeCatagory----Account(1:n)
if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[FK_Account_AccountType] ' )
and OBJECTPROPERTY (id, N ' IsForeignKey ' ) = 1 )
ALTER TABLE [ dbo ] . [ Consume ] DROP CONSTRAINT FK_Account_AccountType
GO
if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[Consume] ' )
and OBJECTPROPERTY (id, N ' IsUserTable ' ) = 1 )
drop table [ dbo ] . [ Consume ]
GO
if exists ( select * from dbo.sysobjects where id = object_id (N ' [dbo].[ConsumeCatagory] ' )
and OBJECTPROPERTY (id, N ' IsUserTable ' ) = 1 )
drop table [ dbo ] . [ ConsumeCatagory ]
GO
CREATE TABLE [ dbo ] . [ Consume ] (
[ ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ ConsumeCatagory ] [ int ] NULL ,
[ ConsumeDateTime ] [ datetime ] NULL ,
[ ConsumeMoney ] [ money ] NULL ,
[ ConsumeDescription ] [ ntext ] COLLATE Chinese_PRC_CI_AS NULL ,
[ ConsumeUser ] [ nchar ] ( 10 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ ConsumeType ] [ nchar ] ( 10 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [ PRIMARY ] TEXTIMAGE_ON [ PRIMARY ]
GO
CREATE TABLE [ dbo ] . [ ConsumeCatagory ] (
[ ID ] [ int ] IDENTITY ( 1 , 1 ) NOT NULL ,
[ Type ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL ,
[ Description ] [ nvarchar ] ( 50 ) COLLATE Chinese_PRC_CI_AS NULL
) ON [ PRIMARY ]
GO
ALTER TABLE [ dbo ] . [ Consume ] ADD
CONSTRAINT [ PK_Account ] PRIMARY KEY CLUSTERED
(
[ ID ]
) ON [ PRIMARY ]
GO
ALTER TABLE [ dbo ] . [ ConsumeCatagory ] ADD
CONSTRAINT [ PK_AccountType ] PRIMARY KEY CLUSTERED
(
[ ID ]
) ON [ PRIMARY ]
GO
ALTER TABLE [ dbo ] . [ Consume ] ADD
CONSTRAINT [ FK_Account_AccountType ] FOREIGN KEY
(
[ ConsumeCatagory ]
) REFERENCES [ dbo ] . [ ConsumeCatagory ] (
[ ID ]
)
GO
Step2:
(1)安裝下載的Nbear組件下dist\SetupNBearVsPlugin.exe插件(可自動生成實體類)
(2)打開VS2005,新建一個空的解決方案,添加兩個C#類庫工程EntityDesigns和Entities,刪除自動生成的class1.cs.
(3)添加一個website的網站項目。
(4)在Entities下添加Entities.cs, 在網站根目錄下添加web.config文件和EntityConfig.xml文件.
(5)修改web.config文件
< configSections >
< section name ="entityConfig" type ="NBear.Common.EntityConfigurationSection, NBear.Common" />
</ configSections >
< entityConfig >
< includes >
< add key ="Sample Entity Config" value ="~/EntityConfig.xml" />
</ includes >
</ entityConfig >
< appSettings />
< connectionStrings >
< add name ="DbName" connectionString ="Server=WDS;Database=NBearDB;Uid=sa;Pwd=sa"
providerName ="NBear.Data.SqlServer.SqlDbProvider" />
</ connectionStrings >
(6)在EntityDesigns目錄下添加EntityDesignToEntityConfig.xml文件,內容如下:
<? xml version="1.0" encoding="utf-8" ?>
< EntityDesignToEntityConfiguration xmlns:xsi ="http://www.w3.org/2001/XMLSchema-instance"
xmlns:xsd ="http://www.w3.org/2001/XMLSchema" >
< CompileMode > Debug </ CompileMode >
< InputDllName > EntityDesigns.dll </ InputDllName > //實體設計類
< OutputNamespace > Entities </ OutputNamespace > //實體類
< OutputLanguage > C# </ OutputLanguage > //輸出語言
<!-- <OutputCodeFileEncoding>utf-8</OutputCodeFileEncoding> -->
< EntityCodePath > ..\Entities\Entities.cs </ EntityCodePath > //實體類文件
< EntityConfigPath > ..\website\EntityConfig.xml </ EntityConfigPath >
< SqlSync enable ="false" > //更改實體時,是否同步更新數據庫,建議設爲false,否則將清空現有數據庫的數據
< SqlServerFolder > C:\Program Files\Microsoft SQL Server\80\Tools\Binn </ SqlServerFolder >
< ServerName > WDS </ ServerName >
< UserID > sa </ UserID >
< Password > sa </ Password >
< DatabaseName > NBearDB </ DatabaseName >
</ SqlSync >
</ EntityDesignToEntityConfiguration >
(7)在EntityDesigns目錄下添加EntityDesigns.cs
(8)打開下載的Nbear組件下dist\NBear.Tools.DbToEntityDesign.exe程序 ,設置連接字符串,我的是這樣:
Server=WDS;Database=NBearDB;Uid=sa;Pwd=sa 點擊connect, 選擇要生成的表,點擊Generate Entities Design.將會得到下面的代碼:
生成實體如下
拷貝上面的代碼到EntityDesigns.cs,爲EntityDesigns添加NBear.Common.Design引用.
修改EntityDesigns.cs實體爲
(9)編譯EntityDesigns工程,編譯成功,剛纔添加Entities.cs,EntityConfig.xml文件將被自動填入正確的內容。
(10)爲Entities工程引用NBear.Common,然後編譯Entities工程。
(11)爲WebSite添加Entities項目編譯後的Entities.dll.再引用NBear.Data
(12)開始使用設計好的實體。
在default頁添加兩個gridview, gvdAccountType顯示消費類別, gvdAccount顯示消費明細。代碼如下
aspx頁面的設置
運行得到如下界面
我們爲什看到上面消費明細裏的類別顯示的是類別名,而不是1,2,3,4呢?是因爲我們利用實體關係進行了自動的聯合查詢,
綁定時我們綁定到Consume實體的catagory屬性的Type屬性就可以了(上面代碼突出顯示紅色的地方)。
注:當我爲Consume實體加上
[FkReverseQuery(LazyLoad = true)]
[MappingName("ConsumeCatagory")]
ConsumeCatagory catagory
{
get;
set;
}
以上代碼時,我必須註釋掉 int? ConsumeCatagory { get; set; }這句,不然添加數據會出錯。
現在我們添加一條類別爲買菜的數據時只能這樣(不能直接這樣用today.ConsumeCatagory=1, 因爲已經沒有這個屬性了),我們得先找到類別爲買菜的對象,然後付給consume對象的catagory屬性就可以了。(奇怪爲什麼需要註釋掉,或者還有別的辦法?)
現在我們假如想得到買菜類別的所有消費,我們只需這樣