LinQ构建分层架构

一、建立数据库

           我们建立一个数据库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）

  建立一个数据访问类文件，来执行数据库操作的存储过程及处理传进来的参数，代码如下：

 

代码

1 namespace 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），在这个类库中，我们将各个访问接口细腻化，使表示层的操作更方便，代码如下：

       

       

代码

1 namespace 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）

 

 

 

 

 

 

转载于:https://www.cnblogs.com/wzh2010/archive/2010/11/18/1881203.html