LinQ构建分层架构
时间：2010-11-18 来源：翁智华
我们建立一个数据库dbo.LayerData , 包含三个表（bookCatalog，NewBookLog，userInfo），各表详细的字段和属性如下所示：
代码
代码
1 create database LayerData
2 go
3 use LayerData
4 go
5 /*建立userInfo表*/
6 create table userInfo
7 (
8 UserID int identity(1,1) primary key,
9 UserName varchar(30),
10 UserSex nvarchar(1),
11 Birthday datetime,
12 UserEmail varchar(50),
13 InsertTime datetime
14 )
15
16 go
17 /*建立NewBookLog表,新书日志表*/
18 create table NewBookLog
19 (
20 NewBookID int identity(1,1) primary key,
21 BookName varchar(50),
22 BookAuthor varchar(20),
23 InsertTime dateTime
24 )
25
26 go
27 /*建立bookCatalog表，书目列表*/
28 create table bookCatalog
29 (
30 BookID int identity(1,1) primary key,
31 BookName varchar(50),
32 BookAuthor varchar(20),
33 PublishTime dateTime,
34 BookInfo nvarchar(200)
35 )
36
37 go
38 /*-----------------------------现在开始创建存储过程（Dal中的增删改查使用存储过程来执行）----------------------------------------------------------------*/
39
40
41 /*-------------------------------UserInfo表的存储过程开始-------------------------------------------*/
42 /*---通过用户ID检索用户信息---*/
43 create procedure LP_userInfoSelect
44 (
45 @UserID int
46 )
47 as
48 begin
49 select * from userInfo where UserID=@UserID
50 end
51
52 go
53 /*---通过Where条件检索---*/
54
55 create procedure LP_userInfoSelectAll
56 (
57 @strWhere varchar(50)
58 )
59 as
60 exec('select * from userInfo where '+ @strWhere )
61 go
62
63 /*---插入语句---*/
64 create proc LP_userInfoInsert
65 (
66 @UserName varchar(30),
67 @UserSex nvarchar(1),
68 @Birthday datetime,
69 @UserEmail varchar(50),
70 @InsertTime datetime
71 )
72 as
73 begin
74 insert into userInfo values(@UserName,@UserSex,@Birthday,@UserEmail,@InsertTime)
75 end
76
77 go
78 /*-------------更新用户信息（根据用户ID更新）----------*/
79 create procedure LP_userInfoUpdate
80 (
81 @UserID int,
82 @UserName varchar(30),
83 @UserSex nvarchar(1),
84 @Birthday datetime,
85 @UserEmail varchar(50)
86 )
87 as
88 begin
89 update userInfo set UserName=@UserName,
90 UserSex=@UserSex,
91 Birthday=@Birthday,
92 UserEmail=@UserEmail
93 where UserID=@UserID
94 end
95
96 go
97 /*-----------删除用户信息（根据用户ID）-----------------*/
98 create procedure LP_userInfoDelete
99 (
100 @UserID int
101 )
102 as
103 begin
104 delete from userInfo where UserID=@UserID
105 end
106 /*-------------------------------UserInfo表的存储过程结束---------------------------------------------*/
107
108
109 /*-------------------------------bookCatalog表的存储过程开始-------------------------------------------*/
110 /*--------------书目查询（ID）---------------*/
111 create procedure LP_bookCataLogSelect
112 (
113 @BookID int
114 )
115 as
116 begin
117 select * from bookCataLog where BookID = @BookID
118 end
119
120 go
121 /*--------------书目查询（where）--------------------------*/
122 create procedure LP_bookCataLogSelectAll
123 (
124 @strWhere varchar(50)
125 )
126 as
127 begin
128 execute('select * from bookCataLog where '+@strWhere)
129 end
130
131 /*-----------------插入书目------------------------------*/
132 go
133 create procedure LP_bookCataLogInsert
134 (
135 @BookName varchar(50),
136 @BookAuthor varchar(20),
137 @PublishTime datetime,
138 @BookInfo nvarchar(200)
139 )
140 as
141 begin
142 insert into bookCatalog values(@BookName,@BookAuthor,@PublishTime,@BookInfo)
143 end
144
145 /*------------------------更新书目--------------------------------*/
146 go
147 create procedure LP_bookCataLogUpdate
148 (
149 @BookID int,
150 @BookName varchar(50),
151 @BookAuthor varchar(20),
152 @PublishTime datetime,
153 @BookInfo nvarchar(200)
154 )
155 as
156 begin
157 update bookCataLog set
158 BookName=@BookName,
159 BookAuthor=@BookAuthor,
160 PublishTime=@PublishTime,
161 BookInfo =@BookInfo
162 where BookID=@BookID
163 end
164
165 /*----------------------------删除指定书目---------------------------------*/
166 go
167 create procedure LP_bookCataLogDelete
168 (
169 @BookID int
170 )
171 as
172 begin
173 delete from bookCataLog where BookID=@BookID
174 end
175 /*-------------------------------bookCatalog表的存储过程开始-------------------------------------------*/
176
177
178 /*----------------------
179 建立一个触发器，该触发气的作用是dbo.bookCatalog添加一种书籍信息时，
180 也向dbo.NewBookLog 插入一条信息，用以检查书籍的入库情况
181 -----------------------*/
182
183 create trigger BookLog on dbo.bookCatalog
184 after insert
185 as
186 begin
187 declare @BookName varchar(50)
188 declare @BookAuthor varchar(20)
189 select @BookName=BookName,@BookAuthor=BookAuthor from inserted
190 insert into NewBookLog values(@BookName,@BookAuthor,getdate())
191 end
二、建立一个实体类库（Model），用以映射对应的表和字段
我们建立两个类文件bookCatalog.cs和userInfo.cs，对应数据库中bookCatalog和userInfo表，这边我们以bookCatalog为例，代码如下：
代码
1 namespace Model
2 {
3 [Table(Name="bookCatalog")] //(映射数据库中的bookCatalog表，使用数据上下文DataContext操作表的时候用得到，我们这边在Dal里面用存储过程映射，没使用到它，不过也顺便写一下)
4 public class bookCatalog
5 {
6 //映射bookCatalog表中的BookID字段,属性描述是主键，数据库自动生成值，类型为Int
7 [Column(IsDbGenerated = true, IsPrimaryKey = true, DbType = "Int NOT NULL IDENTITY", Name = "BookID")]
8 public int BookID { get; set; }
9
10 //映射bookCatalog表中的BookName字段
11 [Column(Name="BookName",DbType="varchar(50)")]
12 public string BookName { get; set; }
13
14 //映射bookCatalog表中的BookAuthor字段
15 [Column(Name="BookAuthor",DbType="varchar(20)")]
16 public string BookAuthor { get; set; }
17
18 //映射bookCatalog表中的PublishTime字段
19 [Column(Name="PublishTime",DbType="datetime")]
20 public DateTime PublishTime { get; set; }
21
22 //映射bookCatalog表中的BookInfo字段
23 [Column(Name="BookInfo",DbType="nvarchar(200)")]
24 public string BookInfo { get; set; }
25
26
27
28 public bookCatalog() { }
29 }
30 }
31
三、建立数据访问层类库（Dal）
建立一个数据访问类文件，来执行数据库操作的存储过程及处理传进来的参数，代码如下：
代码
1namespace Dal
2{
3 [System.Data.Linq.Mapping.DatabaseAttribute(Name = "LayerData")]//指定LinQ操作的数据库，映射数据库名LayerData
4 public class bookCatalog:DataContext //继承System.data.Linq.DataContext类，提供LinQ to SQL框架的数据上下文的主入口点
5 {
6 public bookCatalog()
7 : base(ConfigurationManager.ConnectionStrings["LinQCon"].ConnectionString)//初始化DataContext类，提供相应的数据链接
8 {
9
10 }
11
12
13 /// <summary>
14 /// 根据ID 检索书籍名称
15 /// </summary>
16 /// <param name="bookID"></param>
17 /// <returns></returns>
18 [Function(Name="dbo.LP_bookCataLogSelect")]//映射名称为dbo.LP_bookCataLogSelect的存储过程
19 public ISingleResult<Model.bookCatalog> LP_bookCataLogSelect([Parameter(Name="BookID",DbType="INT NOT NULL IDENTITY")] int bookID)//映射存储过程中的参数@BookID
20 {
21 IExecuteResult result = this.ExecuteMethodCall(this,((MethodInfo)(MethodInfo.GetCurrentMethod())),bookID);//执行存储过程
22 return ((ISingleResult<Model.bookCatalog>)(result.ReturnValue));//返回类型（ISingleResult<Model.bookCatalog>，将符合条件的整行数据返回
23 }
24
25
26
27
28 /// <summary>
29 /// 根据where条件检索书籍
30 /// </summary>
31 /// <param name="StrWhere"></param>
32 /// <returns></returns>
33 [Function(Name = "dbo.LP_bookCataLogSelectAll")]//映射名称为dbo.LP_bookCataLogSelectAll的存储过程
34 public ISingleResult<Model.bookCatalog> LP_bookCataLogSelectAll([Parameter(Name = "strWhere", DbType = "varchar(50)")] string StrWhere)//映射存储过程中的参数@strWhere
35 {
36 IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), StrWhere);
37 return ((ISingleResult<Model.bookCatalog>)(result.ReturnValue));
38 }
39
40 /// <summary>
41 /// 插入书目信息
42 /// </summary>
43 /// <param name="bookName"></param>
44 /// <param name="bookAuthor"></param>
45 /// <param name="publishTime"></param>
46 /// <param name="bookInfo"></param>
47 /// <returns></returns>
48 [Function(Name = "dbo.LP_bookCataLogInsert")]//映射名称为dbo.LP_bookCataLogInsert的存储过程
49 public int LP_bookCataLogInsert([Parameter(Name = "BookName", DbType = "varchar(50)")] string bookName,//映射存储过程中的参数@BookName
50 [Parameter(Name = "BookAuthor", DbType = "varchar(20)")] string bookAuthor,//映射存储过程中的参数@BookAuthor
51 [Parameter(Name = "PublishTime", DbType = "datetime")] DateTime publishTime, //映射存储过程中的参数@BookPublishTime
52 [Parameter(Name = "BookInfo", DbType = "nvarchar(200)")] string bookInfo)//映射存储过程中的参数@BookInfo
53 {
54 IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), bookName, bookAuthor, publishTime, bookInfo);
55 return ((int)(result.ReturnValue));//ReturnValue是数据库执行结果的返回值，在数据库中，语句执行成功时候返回 0，因此这边如果添加信息成功的话应该返回 0
56 }
57
58
59 /// <summary>
60 /// 更新书目信息
61 /// </summary>
62 /// <param name="bookID"></param>
63 /// <param name="bookName"></param>
64 /// <param name="bookAuthor"></param>
65 /// <param name="publishTime"></param>
66 /// <param name="bookInfo"></param>
67 /// <returns></returns>
68 [Function(Name="dbo.LP_bookCataLogUpdate")]//映射名称为dbo.LP_bookCataLogUpdate的存储过程
69 public int LP_bookCataLogUpdate([Parameter(Name="BookID",DbType="INT NOT NULL IDENTITY")] int bookID,[Parameter(Name = "BookName", DbType = "varchar(50)")] string bookName, [Parameter(Name = "BookAuthor", DbType = "varchar(20)")] string bookAuthor, [Parameter(Name = "PublishTime", DbType = "datetime")] DateTime publishTime, [Parameter(Name = "BookInfo", DbType = "nvarchar(200)")] string bookInfo)
70 {
71 IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), bookID, bookName, bookAuthor, publishTime, bookInfo);
72 return ((int)(result.ReturnValue));
73 }
74
75 /// <summary>
76 /// 删除指定ID的书目
77 /// </summary>
78 /// <param name="bookID"></param>
79 /// <returns></returns>
80 [Function(Name = "dbo.LP_bookCataLogDelete")]//映射名称为dbo.LP_bookCataLogDelete的存储过程
81 public int LP_bookCataLogDelete([Parameter(Name="BookID",DbType="INT NOT NULL IDENTITY")] int bookID)
82 {
83 IExecuteResult result = this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), bookID);
84 return ((int)(result.ReturnValue));
85 }
86 }
87}
88
四、建立数据操作类库（Bll），在这个类库中，我们将各个访问接口细腻化，使表示层的操作更方便，代码如下：
代码
1namespace Bll
2{
3 public class bookCatalog
4 {
5 Dal.bookCatalog dal = new Dal.bookCatalog();
6
7 //根据ID查询一条书目
8 public Model.bookCatalog SelectRow(int id)//因为只有一条数据，我们用实体类Model.bookCatalog来接受
9 {
10 ISingleResult<Model.bookCatalog> result = dal.LP_bookCataLogSelect(id);
11 return result.First();//只有一条数据，所以我们获取集合中的第一条数据，把它返回给实体类
12
13 }
14
15 //根据where子句查询书目
16 public ISingleResult<Model.bookCatalog> SelectAllRow(string strWhere)//返回实体类集合
17 {
18 ISingleResult<Model.bookCatalog> result = dal.LP_bookCataLogSelectAll(strWhere);
19 return result;
20 }
21
22 //插入一条数据
23 public bool InsertRow(Model.bookCatalog model)
24 {
25 int i = dal.LP_bookCataLogInsert(model.BookName,model.BookAuthor,model.PublishTime,model.BookInfo);
26 if (i == 0) return true;
27 return false;
28 }
29
30 //更新一条数据
31 public bool UpdateRow(Model.bookCatalog model)
32 {
33 int i = dal.LP_bookCataLogUpdate(model.BookID,model.BookName,model.BookAuthor,model.PublishTime,model.BookInfo);
34 if (i == 0) return true;
35 return false;
36 }
37
38 //删除一条数据
39 public bool DeleteRow(int id)
40 {
41 int i = dal.LP_bookCataLogDelete(id);
42 if (i == 0) return true;
43 return false;
44 }
45 }
46}
47
五、现在我们来看前台表示层（UI），因为都封装好了，代码很简单,下面是查询指定条件下的书目信息的代码
代码
1 public partial class operate : System.Web.UI.Page
2 {
3 Bll.bookCatalog bll = new Bll.bookCatalog();
4 StringBuilder str = new StringBuilder();
5
6 protected void Page_Load(object sender, EventArgs e)
7 {
8 if (!IsPostBack)
9 {
10 }
11 }
12
13 //查询所有的书目信息并显示
14 public string InitData()
15 {
16 var rows= bll.SelectAllRow("1=1"); //这边使用匿名类型，也可以写成 //ISingleResult<Model.bookCatalog> result = bll.SelectAllRow("1=1"); //foreach(Model.bookCatalog r in result)
17 foreach (var row in rows)
18 {
19 str.Append("<li id="+row.BookID+">");
20 str.Append("<a href='BookDetail.aspx?id="+row.BookID+"'>"+row.BookName+"("+row.BookAuthor+")</a>");
21 str.Append("<span class='Add'></span><span class='Edit'></span><span class='Del'></span>");
22 str.Append("</li>");
23 }
24 return str.ToString();
25 }
26 }
执行结果：
书目信息列表显示，数据库中的bookCatalog表中包含三条数据，所以这边显示三条，点击跳转到详细信息页面，右边三个图标代表删除，修改，增加
源码下载（http://files.cnblogs.com/wzh2010/LinQLayer.rar）
相关阅读 更多 +
