LinQ构建分层架构

一、建立数据库

           我们建立一个数据库dbo.LayerData , 包含三个表（bookCatalog，NewBookLog，userInfo），各表详细的字段和属性如下所示：

          

createdatabase LayerData
  2  go
  3 use LayerData
  4 go
  5 /*建立userInfo表*/
  6 createtable userInfo
  7 (
  8   UserID intidentity(1,1) primarykey,
  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 createtable NewBookLog
 19 (
 20    NewBookID intidentity(1,1) primarykey,
 21    BookName varchar(50),
 22    BookAuthor varchar(20),
 23    InsertTime dateTime
 24 )
 25 
 26 go
 27 /*建立bookCatalog表，书目列表*/
 28 createtable bookCatalog
 29 (
 30   BookID intidentity(1,1) primarykey,
 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 createprocedure LP_userInfoSelect
 44 (
 45 @UserIDint  
 46 )
 47 as
 48 begin
 49 select*from userInfo where UserID=@UserID
 50 end
 51 
 52 go
 53 /*---通过Where条件检索---*/
 54 
 55 createprocedure LP_userInfoSelectAll
 56 (
 57 @strWherevarchar(50)
 58 )
 59 as
 60 exec('select * from userInfo where '+@strWhere )
 61 go
 62 
 63 /*---插入语句---*/
 64 createproc LP_userInfoInsert
 65 (
 66 @UserNamevarchar(30),
 67 @UserSexnvarchar(1),
 68 @Birthdaydatetime,
 69 @UserEmailvarchar(50),
 70 @InsertTimedatetime
 71 )
 72 as
 73 begin
 74 insertinto userInfo values(@UserName,@UserSex,@Birthday,@UserEmail,@InsertTime)
 75 end
 76 
 77 go
 78 /*-------------更新用户信息（根据用户ID更新）----------*/
 79 createprocedure LP_userInfoUpdate
 80 (
 81 @UserIDint,
 82 @UserNamevarchar(30),
 83 @UserSexnvarchar(1),
 84 @Birthdaydatetime,
 85 @UserEmailvarchar(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 createprocedure LP_userInfoDelete
 99 (
100 @UserIDint
101 )
102 as
103 begin
104 deletefrom userInfo where UserID=@UserID
105 end
106 /*-------------------------------UserInfo表的存储过程结束---------------------------------------------*/
107 
108 
109 /*-------------------------------bookCatalog表的存储过程开始-------------------------------------------*/
110 /*--------------书目查询（ID）---------------*/
111 createprocedure LP_bookCataLogSelect
112 (
113 @BookIDint  
114 )
115 as
116 begin
117 select*from bookCataLog where BookID =@BookID
118 end
119 
120 go
121 /*--------------书目查询（where）--------------------------*/
122 createprocedure LP_bookCataLogSelectAll
123 (
124 @strWherevarchar(50)
125 )
126 as
127 begin
128 execute('select * from bookCataLog where '+@strWhere)
129 end
130 
131 /*-----------------插入书目------------------------------*/
132 go
133 createprocedure LP_bookCataLogInsert
134 (
135 @BookNamevarchar(50),
136 @BookAuthorvarchar(20),
137 @PublishTimedatetime,
138 @BookInfonvarchar(200)  
139 )
140 as
141 begin
142 insertinto bookCatalog values(@BookName,@BookAuthor,@PublishTime,@BookInfo)
143 end
144 
145 /*------------------------更新书目--------------------------------*/
146 go
147 createprocedure LP_bookCataLogUpdate
148 (
149 @BookIDint,
150 @BookNamevarchar(50),
151 @BookAuthorvarchar(20),
152 @PublishTimedatetime,
153 @BookInfonvarchar(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 createprocedure LP_bookCataLogDelete
168 (
169 @BookIDint
170 )
171 as
172 begin
173 deletefrom bookCataLog where BookID=@BookID
174 end
175 /*-------------------------------bookCatalog表的存储过程开始-------------------------------------------*/
176 
177 
178 /*----------------------
179 建立一个触发器，该触发气的作用是dbo.bookCatalog添加一种书籍信息时，
180 也向dbo.NewBookLog 插入一条信息，用以检查书籍的入库情况
181 -----------------------*/
182 
183 createtrigger BookLog on dbo.bookCatalog
184 after insert
185 as
186 begin
187 declare@BookNamevarchar(50)
188 declare@BookAuthorvarchar(20)
189 select@BookName=BookName,@BookAuthor=BookAuthor from inserted  
190 insertinto NewBookLog values(@BookName,@BookAuthor,getdate())
191 end

 

二、建立一个实体类库（Model），用以映射对应的表和字段

       我们建立两个类文件bookCatalog.cs和userInfo.cs，对应数据库中bookCatalog和userInfo表，这边我们以bookCatalog为例，代码如下：

     

namespace Model
 2 {
 3    [Table(Name="bookCatalog")] //(映射数据库中的bookCatalog表，使用数据上下文DataContext操作表的时候用得到，我们这边在Dal里面用存储过程映射，没使用到它，不过也顺便写一下)
 4 publicclass bookCatalog
 5     {
 6 //映射bookCatalog表中的BookID字段,属性描述是主键，数据库自动生成值，类型为Int
 7         [Column(IsDbGenerated =true, IsPrimaryKey =true, DbType ="Int NOT NULL IDENTITY", Name ="BookID")]
 8 publicint BookID { get; set; }
 9 
10 //映射bookCatalog表中的BookName字段
11         [Column(Name="BookName",DbType="varchar(50)")]
12 publicstring BookName { get; set; }
13         
14 //映射bookCatalog表中的BookAuthor字段
15         [Column(Name="BookAuthor",DbType="varchar(20)")]
16 publicstring 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 publicstring BookInfo { get; set; }
25 
26 
27 
28 public bookCatalog() { }
29    }
30 }
31 

 

三、建立数据访问层类库（Dal）

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

 

namespace Dal
 2{
 3    [System.Data.Linq.Mapping.DatabaseAttribute(Name ="LayerData")]//指定LinQ操作的数据库，映射数据库名LayerData
 4publicclass bookCatalog:DataContext //继承System.data.Linq.DataContext类，提供LinQ to SQL框架的数据上下文的主入口点
 5  {
 6public 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的存储过程
19public 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);//执行存储过程
22return ((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的存储过程
34public 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);
37return ((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的存储过程
49publicint 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);
55return ((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的存储过程
69publicint 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);
72return ((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的存储过程
81publicint LP_bookCataLogDelete([Parameter(Name="BookID",DbType="INT NOT NULL IDENTITY")] int bookID)
82       {
83           IExecuteResult result =this.ExecuteMethodCall(this, ((MethodInfo)(MethodInfo.GetCurrentMethod())), bookID);
84return ((int)(result.ReturnValue));
85       }
86  }
87}
88

   

 

  四、建立数据操作类库（Bll），在这个类库中，我们将各个访问接口细腻化，使表示层的操作更方便，代码如下：

       

       

namespace Bll
 2{
 3publicclass bookCatalog
 4   {
 5       Dal.bookCatalog dal =new Dal.bookCatalog();
 6
 7//根据ID查询一条书目
 8public Model.bookCatalog SelectRow(int id)//因为只有一条数据，我们用实体类Model.bookCatalog来接受
 9       {
10           ISingleResult<Model.bookCatalog> result = dal.LP_bookCataLogSelect(id);
11return result.First();//只有一条数据，所以我们获取集合中的第一条数据，把它返回给实体类
12           
13       }
14
15//根据where子句查询书目
16public ISingleResult<Model.bookCatalog> SelectAllRow(string strWhere)//返回实体类集合
17       {
18           ISingleResult<Model.bookCatalog> result = dal.LP_bookCataLogSelectAll(strWhere);
19return result;
20       }
21
22//插入一条数据
23publicbool InsertRow(Model.bookCatalog model)
24       {
25int i = dal.LP_bookCataLogInsert(model.BookName,model.BookAuthor,model.PublishTime,model.BookInfo);
26if (i ==0) returntrue;
27returnfalse;
28       }
29
30//更新一条数据
31publicbool UpdateRow(Model.bookCatalog model)
32       {
33int i = dal.LP_bookCataLogUpdate(model.BookID,model.BookName,model.BookAuthor,model.PublishTime,model.BookInfo);
34if (i ==0) returntrue;
35returnfalse;
36       }
37
38//删除一条数据
39publicbool DeleteRow(int id)
40       {
41int i = dal.LP_bookCataLogDelete(id);
42if (i ==0) returntrue;
43returnfalse;
44       }
45   }
46}
47

 

 

五、现在我们来看前台表示层（UI），因为都封装好了，代码很简单,下面是查询指定条件下的书目信息的代码

publicpartialclass operate : System.Web.UI.Page
 2    {
 3        Bll.bookCatalog bll =new Bll.bookCatalog();
 4        StringBuilder str =new StringBuilder();
 5        
 6protectedvoid Page_Load(object sender, EventArgs e)
 7        {
 8if (!IsPostBack)
 9            {               
10            }
11        }
12
13//查询所有的书目信息并显示
14publicstring InitData()
15        {
16            var rows=  bll.SelectAllRow("1=1");
              //这边使用匿名类型，也可以写成
              //ISingleResult<Model.bookCatalog> result = bll.SelectAllRow("1=1");
              //foreach(Model.bookCatalog r in result)                
17foreach (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            }
24return str.ToString();
25        }
26    }

执行结果：

 

书目信息列表显示，数据库中的bookCatalog表中包含三条数据，所以这边显示三条，点击跳转到详细信息页面，右边三个图标代表删除，修改，增加

 

源码下载（https://files.cnblogs.com/wzh2010/LinQLayer.rar）