·您现在的位置: 云翼网络 >> 文章中心 >> 网站建设 >> 网站建设开发 >> ASP.NET网站开发 >> 我的网络兼职MVC项目开发-细说三层

我的网络兼职MVC项目开发-细说三层

作者:佚名      ASP.NET网站开发编辑:admin      更新时间:2022-07-23

今天我们来聊一下网站的三层如何搭建起来的(网站第一版的框架)。参考网站:革西网络

在我刚接触开发的时候,三层是用的最多的框架,没有之一。借助一些代码生成器,我们能够很容易的建立三层来,其中的增删改的方法已经有了,我们只是需要在这些基础之上,根据项目的具体的业务逻辑进行一些代码的编写就能实现了。

 

首先来看DAL,涵盖了在项目中,我们所需要的大部分的方法。看代码,是这样写的:

  1  public  class DAL
  2     {
  3         #region  BaseDAL
  4         SqlDataPRovider SQLProvider = new SqlDataProvider(SqlHelper.GetConnSting());//TODO:这个动态类是单例模式?
  5         /// <summary>
  6         ///  得到最大ID
  7         /// </summary>
  8         /// <param name="table">表名</param>
  9         /// <returns></returns>
 10         public int GetMaxId(string table)
 11         {
 12             return SQLProvider.GetMaxID("Id", table);
 13         }
 14 
 15         /// <summary>
 16         /// 是否存在该记录
 17         /// </summary>
 18         /// <param name="Table">表名</param>
 19         /// <param name="Id">编号Id</param>
 20         /// <returns></returns>
 21         public bool Exists(string Table, int Id)
 22         {
 23             StringBuilder strSql = new StringBuilder();
 24             strSql.AppendFormat("select count(1) from {0}", Table);
 25             strSql.Append(" where Id=@Id");
 26             SqlParameter[] parameters = {                  
 27                     new SqlParameter("@Id", SqlDbType.Int,4)
 28             };
 29             parameters[0].Value = Id;
 30 
 31             return SQLProvider.Exists(strSql.ToString(), parameters);
 32         }
 33 
 34         /// <summary>
 35         /// 增加一条数据
 36         /// </summary>
 37         /// <param name="Field">要插入的字段</param>
 38         /// <param name="Models">插入字段的参数</param>
 39         /// <returns></returns>
 40         public int Add(string Table, string field, string[] Models)
 41         {
 42             StringBuilder strSql = new StringBuilder();
 43             strSql.AppendFormat("insert into {0}(", Table);
 44             strSql.AppendFormat("{0})", field);
 45             strSql.Append(" values (");
 46             strSql.AppendFormat("{0})", SqlParameterHelp.SqlParaValues(field));
 47             strSql.Append(";select @@IDENTITY");
 48 
 49             object obj = SQLProvider.Execute(strSql.ToString(), SqlParameterHelp.SqlPara(field, Models));
 50             if (obj == null)
 51             {
 52                 return 0;
 53             }
 54             else
 55             {
 56                 return Convert.ToInt32(obj);
 57             }
 58         }
 59         /// <summary>
 60         /// 更新一条数据
 61         /// </summary>
 62         /// <param name="id">根据id修改</param>
 63         /// <param name="table">表名</param>
 64         /// <param name="fields">修改字段如Id=@Id</param>
 65         /// <returns></returns>
 66         public bool Update(string id, string table, string fields, string[] Models)
 67         {
 68             StringBuilder strSql = new StringBuilder();
 69             strSql.AppendFormat("update {0} set ", table);
 70             strSql.AppendFormat("{0}", SqlParameterHelp.SqlParaValuesUpdate(fields));
 71             strSql.AppendFormat(" where Id={0}", id);
 72 
 73 
 74             int rows = SQLProvider.Execute(strSql.ToString(), SqlParameterHelp.SqlPara(fields, Models));
 75             if (rows > 0)
 76             {
 77                 return true;
 78             }
 79             else
 80             {
 81                 return false;
 82             }
 83         }
 84        
 85        public bool Update(string id, string table, string fields, string Models)
 86         {
 87             StringBuilder strSql = new StringBuilder();
 88             strSql.AppendFormat("update {0} set {1}={2} where Id={3} ", table, fields, Models,id); 
 89             int rows = SQLProvider.Execute(strSql.ToString());
 90             if (rows > 0)
 91             {
 92                 return true;
 93             }
 94             else
 95             {
 96                 return false;
 97             }
 98         }
 99 
100         /// <summary>
101         /// 删除一条数据
102         /// </summary>
103         /// <param name="Table">表名</param>
104         /// <param name="Id">编号</param>
105         /// <returns></returns>
106         public bool Delete(string Table, int Id)
107         {
108 
109             StringBuilder strSql = new StringBuilder();
110             strSql.AppendFormat("delete from {0} ", Table);
111             strSql.Append(" where Id=@Id");
112             SqlParameter[] parameters = {                   
113                     new SqlParameter("@Id", SqlDbType.Int,4)
114             };
115             parameters[0].Value = Id;
116 
117             int rows = SQLProvider.Execute(strSql.ToString(), parameters);
118             if (rows > 0)
119             {
120                 return true;
121             }
122             else
123             {
124                 return false;
125             }
126         }
127         /// <summary>
128         /// 批量删除数据
129         /// </summary>
130         public bool DeleteList(string table, string Idlist)
131         {
132             StringBuilder strSql = new StringBuilder();
133             strSql.AppendFormat("delete from {0} ", table);
134             strSql.Append(" where Id in (" + Idlist + ")  ");
135             int rows = SQLProvider.Execute(strSql.ToString());
136             if (rows > 0)
137             {
138                 return true;
139             }
140             else
141             {
142                 return false;
143             }
144         }
145 
146 
147         /// <summary>
148         /// 得到一个对象实体
149         /// </summary>
150         /// <typeparam name="T">实体类型</typeparam>
151         /// <param name="Table">表名</param>
152         /// <param name="Id">Id</param>
153         /// <returns></returns>
154         public T GetModles<T>(string Table, int Id) where T : new()
155         {
156             StringBuilder strSql = new StringBuilder();
157             strSql.AppendFormat("select  top 1 * from {0} ", Table);
158             strSql.Append(" where Id=@Id");
159             SqlParameter[] parameters = {
160                     new SqlParameter("@Id", SqlDbType.Int,4)
161             };
162             parameters[0].Value = Id;
163 
164             T model = new T();
165             DataSet ds = SQLProvider.GetDataset(strSql.ToString(), parameters);
166             if (ds.Tables[0].Rows.Count > 0)
167             {
168                 return DataConvert.DataRowToModel<T>(ds.Tables[0].Rows[0]);
169             }
170             else
171             {
172                 return default(T);
173             }
174         }
175 
176 
177 
178         /// <summary>
179         /// sql获得数据列表
180         /// </summary>
181         /// <param name="strWhere">sql条件</param>
182         /// <returns></returns>
183         public DataSet GetListBySql(string strWhere, string table)
184         {
185             return SQLProvider.GetDataset(strWhere.ToString());
186         }
187         /// <summary>
188         /// 获得数据列表
189         /// </summary>
190         /// <param name="strWhere">sql条件</param>
191         /// <returns></returns>
192         public DataSet GetList(string strWhere, string table)
193         {
194             StringBuilder strSql = new StringBuilder();
195             strSql.Append("select * ");
196             strSql.AppendFormat(" from {0} ", table);
197             if (strWhere.Trim() != "")
198             {
199                 strSql.Append(" where " + strWhere);
200             }
201             return SQLProvider.GetDataset(strSql.ToString());
202         }
203 
204         /// <summary>
205         /// 分页得到相应数据
206         /// </summary>
207         /// <param name="strWhere"></param>
208         /// <param name="table"></param>
209         /// <param name="strorder"></param>
210         /// <param name="start"></param>
211         /// <param name="end"></param>
212         /// <returns></returns>
213         public DataSet GetList(string strWhere, string table, string strorder, int start, int end)
214         {
215             string sql = "";
216             if (strWhere.Trim() != "")
217             {
218                 sql = "select * from(select *,ROW_NUMBER() over(order by " + strorder + " ) as Num  from " + table + " where " + strWhere + ") as newTable where Num between  " + start + " and " + end + "";
219             }
220             else
221             {
222                 sql = "select * from(select *,ROW_NUMBER() over(order by " + strorder + " ) as Num  from " + table + ") as newTable where Num between  " + start + " and " + end + "";
223             }
224             return SQLProvider.GetDataset(sql.ToString());
225         }
226 
227         /// <summary>
228         /// 非常规排序
229         /// </summary>
230         /// <param name="strTop"></param>
231         /// <param name="strWhere"></param>
232         /// <param name="table"></param>
233         /// <param name="rank"></param>
234         /// <returns></returns>
235         public DataSet GetTopListOne(string strTop, string strs, string table, string rank)
236         {
237             StringBuilder strSql = new StringBuilder();
238             strSql.Append("select top(" + strTop + ")* ");
239             strSql.AppendFormat(" from {0} ", table);
240             if (strs.Trim() != "")
241             {
242                 strSql.Append(" " + strs);
243             }
244             strSql.Append(" order by " + rank + "");
245             return SQLProvider.GetDataset(strSql.ToString());
246         }
247 
248         /// <summary>
249         /// 获取前几行数据  用于首页显示  方便
250         /// </summary>
251         /// <typeparam name="T"></typeparam>
252         /// <param name="strTop"></param>
253         /// <param name="strWhere"></param>
254         /// <param name="table"></param>
255         /// <returns></returns>
256         public DataSet GetTopList(string strTop, string strWhere, string table, string rank)
257         {
258             StringBuilder strSql = new StringBuilder();
259             strSql.Append("select top(" + strTop + ")* ");
260             strSql.AppendFormat(" from {0} ", table);
261             if (strWhere.Trim() != "")
262             {
263                 strSql.Append(" where " + strWhere);
264             }
265             strSql.Append(" order by " + rank + "");
266             return SQLProvider.GetDataset(strSql.ToString());
267         }
268 
269 
270         public DataSet GetTopList2(string strTop, string strWhere, string table, string rank)
271         {
272             StringBuilder strSql = new StringBuilder();
273             strSql.Append("select top(" + strTop + ") id,title,ImgInfo,Author,Time  ");
274             strSql.AppendFormat(" from {0} ", table);
275             if (strWhere.Trim() != "")
276             {
277                 strSql.Append(" where " + strWhere);
278             }
279             strSql.Append(" order by " + rank + "");
280             return SQLProvider.GetDataset(strSql.ToString());
281         }
282 
283         /// <summary>
284         /// 获得前几行数据
285         /// </summary>
286         /// <param name="Top">几行</param>
287         /// <param name="strWhere">sql语句</param>
288         /// <param name="filedOrder">排序</param>
289         /// <param name="table">表名</param>
290         /// <returns></returns>
291         public DataSet GetList(int Top, string strWhere, string filedOrder, string table)
292         {
293             StringBuilder strSql = new StringBuilder();
294             strSql.Append("select ");
295             if (Top > 0)
296             {
297                 strSql.Append(" top " + Top.ToString());
298             }
299             strSql.Append(" * ");
300             strSql.AppendFormat(" from {0} ", table);
301             if (strWhere.Trim() != "")
302             {
303                 strSql.Append(" where " + strWhere);
304             }
305             strSql.Append(" order by " + filedOrder);
306             return SQLProvider.GetDataset(strSql.ToString());
307         }
308 
309         /// <summary>
310         /// 获取记录总数
311         /// </summary>
312         /// <param name="strWhere">sql查询条件</param>
313         /// <param name="table">表名</param>
314         /// <returns></returns>
315         public int GetRecordCount(string strWhere, string table)
316         {
317             StringBuilder strSql = new StringBuilder();
318             strSql.AppendFormat("select count(1) from {0} ", table);
319             if (strWhere.Trim() != "")
320             {
321                 strSql.Append(" where " + strWhere);
322             }
323             object obj = SQLProvider.GetScalar(strSql.ToString());
324             if (obj == null)
325             {
326                 return 0;
327             }
328             else
329             {
330                 return Convert.ToInt32(obj);
331             }
332         }
333         /// <summary>
334         /// 分页获取数据列表
335         /// </summary>
336         /// <param name="strWhere">分页条件</param>
337         /// <param name="orderby">排序字段desc</param>
338         /// <param name="startIndex">开始页</param>
339         /// <param name="endIndex">结束页</param>
340         /// <param name="table">表名</param>
341         /// <returns></returns>
342         public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex, string table)
343         {
344             StringBuilder strSql = new StringBuilder();
345             strSql.Append("SELECT * FROM ( ");
346             strSql.Append(" SELECT ROW_NUMBER() OVER (");
347             if (!string.IsNullOrEmpty(orderby.Trim()))
348             {
349                 strSql.Append("order by T." + orderby);
350             }
351             else
352             {
353                 strSql.Append("order by T.Id desc");
354             }
355             strSql.AppendFormat(")AS Row, T.*  from {0} T ", table);
356             if (!string.IsNullOrEmpty(strWhere.Trim()))
357             {
358                 strSql.Append(" WHERE " + strWhere);
359             }
360             strSql.Append(" ) TT");
361             strSql.AppendFormat(" WHERE TT.Row between {0} and {1}", startIndex, endIndex);
362             return SQLProvider.GetDataset(strSql.ToString());
363         }
364 
365 
366         #endregion  BasicMethod
367     }
View Code

 

再来看看BLL基类:

  1 public  class BLL
  2     {
  3       private readonly DAL dal = new DAL();
  4 
  5         /// <summary>
  6         ///  得到最大ID
  7         /// </summary>
  8         /// <param name="table">表名</param>
  9         /// <returns></returns>
 10         public int GetMaxId(string table)
 11         {
 12             return dal.GetMaxId(table);
 13         }
 14         /// <summary>
 15         /// 是否存在该记录
 16         /// </summary>
 17         /// <param name="Table">表名</param>
 18         /// <param name="Id">编号Id</param>
 19         /// <returns></returns>
 20         public bool Exists(string table, int Id)
 21         {
 22             return dal.Exists(table, Id);
 23         }
 24         /// <summary>
 25         /// 增加一条数据
 26         /// </summary>
 27         /// <param name="Field">要插入的字段</param>
 28         /// <param name="Models">插入字段的参数</param>
 29         /// <returns></returns>
 30         public int Add(string Table, string Field, string[] Models)
 31         {
 32             return dal.Add(Table, Field, Models);
 33         }
 34         /// <summary>
 35         /// 更新一条数据
 36         /// </summary>
 37         /// <param name="id">根据id修改</param>
 38         /// <param name="table">表名</param>
 39         /// <param name="fields">修改字段</param>
 40         /// <returns></returns>
 41         public bool Update(string id, string table, string fields, string[] models)
 42         {
 43             return dal.Update(id, table, fields, models);
 44         }   
 45       
 46         public bool Update(string id, string table, string fields, string models)
 47         {
 48             return dal.Update(id, table, fields, models);
 49         }  
 50         /// <summary>
 51         /// 删除一条数据
 52         /// </summary>
 53         /// <param name="Table">表名</param>
 54         /// <param name="Id">编号</param>
 55         /// <returns></returns>
 56         public bool Delete(string Table, int Id)
 57         {
 58             return dal.Delete(Table, Id);
 59         }
 60         /// <summary>
 61         /// 获得数据列表
 62         /// </summary>
 63         /// <param name="strWhere">sql条件</param>
 64         /// <returns></returns>
 65         public DataSet GetList(string strWhere, string table)
 66         {
 67             return dal.GetList(strWhere, table);
 68         }
 69 
 70         #region 利用泛型类获取实体类
 71         /// <summary>
 72         /// 获得数据列表
 73         /// </summary>
 74         public List<T> GetModelList<T>(string strWhere, string table) where T : new()
 75         {
 76             DataSet ds = dal.GetList(strWhere, table);
 77             return DataTableToList<T>(ds.Tables[0]);
 78         }
 79 
 80         /// <summary>
 81         /// 纯sql获得数据列表
 82         /// </summary>
 83         public List<T> GetModelListBySql<T>(string sql, string table) where T : new()
 84         {
 85             DataSet ds = dal.GetListBySql(sql, table);
 86             return DataTableToList<T>(ds.Tables[0]);
 87         }
 88 
 89         public List<T> GetModelList<T>(string strWhere, string table, string strorder, int start, int end) where T : new()
 90         {
 91             DataSet ds = dal.GetList(strWhere, table, strorder, start, end);
 92             return DataTableToList<T>(ds.Tables[0]);
 93         }
 94 
 95         /// <summary>
 96         /// 获取前几行数据  用于首页显示  方便
 97         /// </summary>
 98         /// <typeparam name="T"></typeparam>
 99         /// <param name="strTop"></param>
100         /// <param name="strWhere"></param>
101         /// <param name="table"></param>
102         /// <returns></returns>
103         public List<T> GetModelTopList<T>(string strTop, string strWhere, string table, string rank) where T : new()
104         {
105             DataSet ds = dal.GetTopList(strTop, strWhere, table, rank);
106             return DataTableToList<T>(ds.Tables[0]);
107         }
108 
109 
110         public List<T> GetModelTopList2<T>(string strTop, string strWhere, string table, string rank) where T : new()
111         {
112             DataSet ds = dal.GetTopList2(strTop, strWhere, table, rank);
113             return DataTableToList<T>(ds.Tables[0]);
114         }
115 
116 
117         /// <summary>
118         /// 根据国家文章的前几篇排序 非常规排序
119         /// </summary>
120         /// <typeparam name="T"></typeparam>
121         /// <param name="strTop"></param>
122         /// <param name="strs"></param>
123         /// <param name="table"></param>
124         /// <param name="rank"></param>
125         /// <returns></returns>
126         public List<T> GetModelTopListOne<T>(string strTop, string strs, string table, string rank) where T : new()
127         {
128             DataSet ds = dal.GetTopListOne(strTop, strs, table, rank);
129             return DataTableToList<T>(ds.Tables[0]);
130         }
131 
132         /// <summary>
133         /// 获得数据列表
134         /// </summary>
135         public List<T> DataTableToList<T>(DataTable dt) where T : new()
136         {
137             List<T> modelList = new List<T>();
138             int rowsCount = dt.Rows.Count;
139             if (rowsCount > 0)
140             {
141                 T model;
142                 for (int n = 0; n < rowsCount; n++)
143                 {
144                     model = DataConvert.DataRowToModel<T>(dt.Rows[n]);
145                     if (model != null)
146                     {
147                         modelList.Add(model);
148                     }
149                 }
150             }
151             return modelList;
152         }
153 
154 
155         SqlDataProvider SQLProvider = new SqlDataProvider(SqlHelper.GetConnSting());
156         /// <summary>
157         /// 得到一个对象实体
158         /// </summary>
159         /// <typeparam name="T">实体类型</typeparam>
160         /// <param name="Table">表名</param>
161         /// <param name="Id">Id</param>
162         /// <returns></returns>
163         public T GetModles<T>(string Table, int Id) where T : new()
164         {
165             StringBuilder strSql = new StringBuilder();
166             strSql.AppendFormat("select  top 1 * from {0} ", Table);
167             strSql.Append(" where Id=@Id");
168             SqlParameter[] parameters = {
169                     new SqlParameter("@Id", SqlDbType.Int,4)
170             };
171             parameters[0].Value = Id;
172 
173             T model = new T();
174             DataSet ds = SQLProvider.GetDataset(strSql.ToString(), parameters);
175             if (ds.Tables[0].Rows.Count > 0)
176             {
177                 return DataConvert.DataRowToModel<T>(ds.Tables[0].Rows[0]);
178             }
179             else
180             {
181                 return default(T);
182             }
183         }
184 
185         /// <summary>
186         /// 得到一个对象实体
187         /// </summary>
188         /// <typeparam name="T">实体类型</typeparam>
189         /// <param name="Table">表名</param>
190         /// <param name="Id">Id</param>
191         /// <returns></returns>
192         public T GetModles<T>(string Table, string fields, string models) where T : new()
193         {
194             StringBuilder strSql = new StringBuilder();
195             strSql.AppendFormat("select  top 1 * from {0} ", Table);
196             strSql.AppendFormat(" where {0}=@{1}", fields, fields);
197             SqlParameter[] parameters = {
198                     new SqlParameter("@"+fields+"", models)
199             };
200 
201             T model = new T();
202             DataSet ds = SQLProvider.GetDataset(strSql.ToString(), parameters);
203             if (ds.Tables[0].Rows.Count > 0)
204             {
205                 return DataConvert.DataRowToModel<T>(ds.Tables[0].Rows[0]);
206             }
207             else
208             {
209                 return default(T);
210             }
211         }
212 
213         /// <summary>
214         /// 得到一个对象实体 sql
215         /// </summary>
216         /// <typeparam name="T">实体类型</typeparam>
217         /// <param name="Table">表名</param>
218         /// <param name="strWhere">Id</param>
219         /// <returns></returns>
220         public T GetModle<T>(string Table, string strWhere) where T : new()
221         {
222             StringBuilder strSql = new StringBuilder();
223             strSql.AppendFormat("select  top 1 * from {0} ", Table);
224             if (strWhere.Trim() != "")
225             {
226                 strSql.Append(" where " + strWhere);
227             }
228 
229             T model = new T();
230             DataSet ds = SQLProvider.GetDataset(strSql.ToString());
231             if (ds.Tables[0].Rows.Count > 0)
232             {
233                 return DataConvert.DataRowToModel<T>(ds.Tables[0].Rows[0]);
234             }
235             else
236             {
237                 return default(T);
238             }
239         }
240         /// <summary>
241         /// 得到一个对象实体
242         /// </summary>
243         /// <typeparam name="T">实体类型</typeparam>
244         /// <param name="Table">表名</param>
245         /// <param name="Name">Name</param>
246         /// <returns></returns>
247         public T GetModles<T>(string Table, string Name) where T : new()
248         {
249             StringBuilder strSql = new StringBuilder();
250             strSql.AppendFormat("select  top 1 * from {0} ", Table);
251             strSql.Append(" where UserEmail=@Name");
252             SqlParameter[] parameters = {
253                     new SqlParameter("@Name", SqlDbType.NVarChar,50)
254             };
255             parameters[0].Value = Name;
256 
257             T model = new T();
258             DataSet ds = SQLProvider.GetDataset(strSql.ToString(), parameters);
259             if (ds.Tables[0].Rows.Count > 0)
260             {
261                 return DataConvert.DataRowToModel<T>(ds.Tables[0].Rows[0]);
262             }
263             else
264             {
265                 return default(T);
266             }
267         }
268         #endregion
269 
270         /// <summary>
271         /// 获得数据列表
272         /// </summary>
273         public DataSet GetAllList(string table)
274         {
275             return GetList("", table);
276         }
277 
278         /// <summary>
279         /// 获得前几行数据
280         /// </summary>
281         /// <param name="Top">几行</param>
282         /// <param name="strWhere">sql语句</param>
283         /// <param name="filedOrder">排序</param>
284         /// <param name="table">表名</param>
285         /// <returns></returns>
286         public DataSet GetList(int Top, string strWhere, string filedOrder, string table)
287         {
288             return dal.GetList(Top, strWhere, filedOrder, table);
289         }
290         /// <summary>
291         /// 获取记录总数
292         /// </summary>
293         /// <param name="strWhere">sql查询条件</param>
294         /// <param name="table">表名</param>
295         /// <returns></returns>
296         public int GetRecordCount(string strWhere, string table)
297         {
298             return dal.GetRecordCount(strWhere, table);
299         }
300         /// <summary>
301         /// 分页获取数据列表
302         /// </summary>
303         /// <param name="strWhere">分页条件</param>
304         /// <param name="orderby">排序字段desc</param>
305         /// <param name="startIndex">开始页</param>
306         /// <param name="endIndex">结束页</param>
307         /// <param name="table">表名</param>
308         /// <returns></returns>
309         public DataSet GetListByPage(string strWhere, string orderby, int startIndex, int endIndex, string table)
310         {
311             return dal.GetListByPage(strWhere, orderby, startIndex, endIndex, table);
312         }
313 
314 
315         /// <summary>
316         /// 关键词下拉查询
317         /// </summary>
318         /// <param name="kw">关键词</param>
319         /// <returns></returns>
320         public IEnumerable<SearchSum> GetSuggestion(string kw)
321         {
322             DataTable dt = SQLProvider.ExecuteDataTable(@"select top 5 KeyWords,count(*) as searchcount  from SearchDetails 
323                                        where datediff(day,searchdatetime,getdate())<7
324                                        and keywords like @keyword
325                                        group by Keywords order by count(*) desc", new SqlParameter("@keyword", "%" + kw + "%"));
326             List<SearchSum> list = new List<SearchSum>();
327             if (dt != null && dt.Rows != null && dt.Rows.Count > 0)
328             {
329                 foreach (DataRow row in dt.Rows)
330                 {
331                     SearchSum oneModel = new SearchSum();
332                     oneModel.Keyword = Convert.ToString(row["keywords"]);
333                     oneModel.SearchCount = Convert.ToInt32(row["SearchCount"]);
334                     list.Add(oneModel);
335                 }
336             }
337             return list;
338         }
339         #region 热度查询+缓存
340         /// <summary>
341         /// 缓存
342         /// </summary>
343         /// <returns></returns>
344         public IEnumerable<SearchDetails> GetHotWords()
345         {
346             //缓存
347             var data = HttpRuntime.Cache["hotwords"];
348             if (data == null)
349             {
350                 IEnumerable<SearchDetails> hotWords = DoSelect();
351                 HttpRuntime.Cache.Insert("hotwords", hotWords, null, DateTime.Now.AddMilliseconds(30), TimeSpan.Zero);
352                 return hotWords;
353             }
354             return (IEnumerable<SearchDetails>)data;
355         }
356         /// <summary>
357         /// 查询热度
358         /// </summary>
359         /// <returns></returns>
360         private IEnumerable<SearchDetails> DoSelect()
361         {
362             string sql = @"select top 5 Keywords,count(*) as searchcount  from SearchDetails 
363                            where datediff(day,searchdatetime,getdate())<7
364                            group by Keywords order by count(*) desc";
365             List<SearchDetails> list = new List<SearchDetails>();
366 
367             list = GetModelListBySql<SearchDetails>(sql, "SearchDetails");
368             return list;
369         }
370         #endregion 
371     }
BLL类

 

最后看看Model,其中的新闻类:

  1   [Serializable]
  2    public  class NewsInfo
  3     {
  4         public NewsInfo()
  5         { }
  6         #region Model
  7         private int _id;
  8         private string _title;
  9         private string _keyword;
 10         private string _summary;
 11         private string _source; 
 12         private string _sourceUrl;
 13         private string _author;
 14         private DateTime  _time;
 15         private int _hits;
 16         private string _newsinfo; 
 17         private int _ishotspot;
 18         private int  _newstypeid;
 19         private string _newstypename;
 20         private int  _isdelete;
 21         private int  _sort;
 22         private string _imgInfo;
 23         private string _imgDec;
 24         private int _price;
 25         private int _review;
 26         private string _creatUserId;
 27         public string CreatUserId
 28         {
 29             get { return _creatUserId; }
 30             set { _creatUserId = value; }
 31         }
 32         private string checkState;
 33         public string CheckState
 34         {
 35             get { return checkState; }
 36             set { checkState = value; }
 37         }
 38 
 39         /// <summary>
 40         /// 评论
 41         /// </summary>
 42         public int Review
 43         {
 44             get { return _review; }
 45             set { _review = value; }
 46         }
 47 
 48         public int Price
 49         {
 50             get { return _price; }
 51             set { _price = value; }
 52         }
 53         public string ImgDec
 54         {
 55             get { return _imgDec; }
 56             set { _imgDec = value; }
 57         }
 58        
 59         /// <summary>
 60         /// 编号
 61         /// </summary>
 62         public int Id
 63         {
 64             set { _id = value; }
 65             get { return _id; }
 66         }
 67         
 68         /// <summary>
 69         /// 标题
 70         /// </summary>
 71         public string Title
 72         {
 73             set { _title = value; }
 74             get { return _title; }
 75         }
 76         /// <summary>
 77         /// 关键字
 78         /// </summary>
 79         public string KeyWord
 80         {
 81             set { _keyword = value; }
 82             get { return _keyword; }
 83         }
 84         /// <summary>
 85         /// 摘要
 86         /// </summary>
 87         public string Summary
 88         {
 89             set { _summary = value; }
 90             get { return _summary; }
 91         }
 92         /// <summary>
 93         /// 来源
 94         /// </summary>
 95         public string Source
 96         {
 97             set { _source = value; }
 98             get { return _source; }
 99         }
100         /// <summary>
101         /// 来源url
102         /// </summary>
103         public string SourceUrl
104         {
105             set { _sourceUrl = value; }
106             get { return _sourceUrl; }
107         }
108         /// <summary>
109         /// 作者
110         /// </summary>
111         public string Author
112         {
113             set { _author = value; }
114             get { return _author; }
115         }
116         /// <summary>
117         /// 发表时间
118         /// </summary>
119         public DateTime Time
120         {
121             set { _time = value; }
122             get { return _time; }
123         }
124         /// <summary>
125         /// 点击量
126         /// </summary>
127         public int Hits
128         {
129             set { _hits = value; }
130             get { return _hits; }
131         }
132 
133         /// <summary>
134         ///  新闻内容
135         /// </summary>
136         public string Newsinfo
137         {
138             get { return _newsinfo; }
139             set { _newsinfo = value; }
140         }
141        
142         /// <summary>
143         /// 是否是热点
144         /// </summary>
145         public int IsHotSpot
146         {
147             set { _ishotspot = value; }
148             get { return _ishotspot; }
149         }
150        
151         /// <summary>
152         /// 新闻类型编号
153         /// </summary>
154         public int NewsTypeId
155         {
156             set { _newstypeid = value; }
157             get { return _newstypeid; }
158         }
159         /// <summary>
160         /// 新闻类型名称 
161         /// </summary>
162         public string NewsTypeName
163         {
164             set { _newstypename = value; }
165             get { return _newstypename; }
166         }
167 
168         /// <summary>
169         /// 是否删除
170         /// </summary>
171         public int IsDelete
172         {
173             set { _isdelete = value; }
174             get { return _isdelete; }
175         }
176         
177         /// <summary>
178         /// 新闻排序
179         /// </summary>
180         public int Sort
181         {
182             set { _sort = value; }
183             get { return _sort; }
184         }
185 
186         /// <summary>
187         /// 图片路径
188         /// </summary>
189         public string ImgInfo
190         {
191             get { return _imgInfo; }
192             set { _imgInfo = value; }
193         }
194         #endregion Model
195     }
News Model

 

Model类使用东软的代码生成器生成的。目前为止,我们简单的三层的主要的代码已经出来了。但是怎么能把这些连在一起呢,看代码:

 1   public abstract class DataProvider
 2     {
 3         private static DataProvider _instance = null;
 4         public static DataProvider Instance
 5         {
 6             get
 7             {
 8                 if (_instance == null)
 9                 {
10                     String strConnectionString = ConfigurationManager.ConnectionStrings["ConnnectionString"].ConnectionString;
11 
12                     String strConnectionType = ConfigurationManager.ConnectionStrings["ConnectionType"].ConnectionString;
13                     if (strConnectionType.ToLower() == "sqlclient")
14                     {
15                         strConnectionType = "DAL.SqlDataProvider, DAL";
16                     }
17 
18                     Type t = Type.GetType(strConnectionType);
19                     Type[] paramTypes = new Type[1];
20                     paramTypes[0] = typeof(String);
21 
22                     Object[] paramArray = new Object[1];
23                     paramArray[0] = strConnectionString;
24 
25                     _instance = (DataProvider)((ConstructorInfo)t.GetConstructor(paramTypes)).Invoke(paramArray);
26                 }
27                 return _instance;
28             }
29         }
30 
31         #region 抽象属性和方法
32 
33         #region 数据库链接串
34         public abstract String ConnectionString { get; }
35         #endregion
36 
37         #region 执行SQL语句
38         public virtual bool Exists(string strSql, params SqlParameter[] cmdParms) { return true; }
39         public virtual int GetMaxID(string FieldName, string TableName) { return 0; }
40         public abstract Object GetScalar(String selectSql);
41         public abstract DataSet GetDataset(String selectSql);
42         public virtual DataSet GetDatasetTrans(SqlTransaction trans, String selectSql) { return null; }
43         public virtual DataSet GetDatasetTrans(OleDbTransaction trans, String selectSql) { return null; }
44         public virtual DataSet GetDataset(String selectSql, SqlParameter[] para) { return null; }
45         public virtual DataSet GetDataset(String selectSql, OleDbParameter[] para) { return null; }
46         public virtual DataSet GetDatasetTrans(SqlTransaction trans, String selectSql, SqlParameter[] para) { return null; }
47         public virtual DataSet GetDatasetTrans(OleDbTransaction trans, String selectSql, OleDbParameter[] para) { return null; }
48         public abstract int Execute(String noneQuery);
49         public virtual int Execute(String noneQuery, SqlParameter[] para) { return 0; }
50         public virtual int Execute(String noneQuery, OleDbParameter[] para) { return 0; }
51         public abstract IDataReader GetReader(String selectSql);
52         public virtual IDataReader GetReaderTrans(SqlTransaction trans, String selectSql) { return null; }
53         public virtual IDataReader GetReaderTrans(OleDbTransaction trans, String selectSql) { return null; }
54         public virtual IDataReader GetReader(String selectSql, SqlParameter[] para) { return null; }
55         public virtual IDataReader GetReader(String selectSql, OleDbParameter[] para) { return null; }
56         public virtual IDataReader GetReaderTrans(SqlTransaction trans, String selectSql, SqlParameter[] para) { return null; }
57         public virtual IDataReader GetReaderTrans(OleDbTransaction trans, String selectSql, OleDbParameter[] para) { return null; }
58         #endregion
59 
60         #region 执行存储过程
61         public abstract void ExecuteSp(String spName);
62         public virtual void ExecuteSp(SqlTransaction trans, String spName) { }
63         public virtual void ExecuteSp(OleDbTransaction trans, String spName) { }
64         public virtual void ExecuteSp(String spName, SqlParameter[] para) { }
65         public virtual void ExecuteSp(String spName, OleDbParameter[] para) { }
66         public virtual void ExecuteSp(SqlTransaction trans, String spName, SqlParameter[] para) { }
67         public virtual void ExecuteSp(OleDbTransaction trans, String spName, OleDbParameter[] para) { }
68         public abstract IDataReader GetReaderBySp(String spName);
69         public virtual IDataReader GetReaderBySp(String spName, SqlParameter[] para) { return null; }
70         public virtual IDataReader GetReaderBySp(String spName, OleDbParameter[] para) { return null; }
71         public abstract Object GetScalarBySp(String spName);
72         public virtual Object GetScalarBySp(String spName, SqlParameter[] para) { return null; }
73         public virtual Object GetScalarBySp(String spName, OleDbParameter[] para) { return null; }
74         public abstract DataSet GetDatasetBySp(String spName);
75         public virtual DataSet GetDatasetBySp(String spName, SqlParameter[] para) { return null; }
76         public virtual DataSet GetDatasetBySp(String spName, OleDbParameter[] para) { return null; }
77         #endregion
78 
79         #endregion
80     }
View Code

 

另外记得在Web.config文件中,修改数据库的链接地址哟。

到目前为止,三层的东西已经都全了。再接下来,咱们一块看看,到底是如何利用三层进行数据的增删改查的。