反射+泛型+缓存 ASP.NET的数据层通用类

  1 using System;
  2 using System.Collections.Generic;
  3 using System.Text;
  4 using System.Reflection ;
  5 using System.Data ;
  6 using BaiChang.HealBlog.Model ;
  7 using System.Data.SqlClient;
  8 using BaiChang.SqlDBUtility;
  9 
 10 namespace BaiChang.Middle.Front
 11 {
 12     public  class DataAccess<T>where T:new()
 13     {
 14         #region Main Part
 15 
 16         public static DataAccess<T> GetDataAccess()
 17         {
 18             string name = "DataAccess" + typeof(T).Name;
 19             DataAccess<T> tObj = Utils.CurrentPage.Cache[name] as DataAccess<T>;
 20 
 21             object obj = new object();
 22             if (tObj == null)
 23             {
 24                 Utils.CurrentPage.Cache[name] = typeof(DataAccess < T>).Assembly.CreateInstance(typeof(DataAccess<T>).Name);
 25                 tObj = Utils.CurrentPage.Cache[name] as DataAccess<T>;
 26             }
 27             return tObj;
 28         }
 29 
 30         public static string ToSetString(string name)
 31         {
 32             string formate = "{0}={1}";
 33             return string.Format(formate, name, name);
 34         }
 35 
 36         public static List<T> ToModel(SqlDataReader reader)
 37         {
 38             Dictionary<string, PropertyInfo> ps = new Dictionary<string, PropertyInfo>();
 39             foreach (PropertyInfo p in typeof (T).GetProperties ())
 40             {
 41                 ps.Add(p.Name, p);
 42             }
 43 
 44             List<T> ms=new List<T> ();
 45             while (reader.Read())
 46             {
 47                 T m = new T();
 48                 object[] objs = null;
 49                 int count = reader.GetValues(objs);
 50                 int i = 0;
 51                 foreach (object o in objs)
 52                 {
 53                     ps[reader.GetName(i)].SetValue(m, o, null);
 54                     i++;
 55                 }
 56                 ms.Add(m);
 57             }
 58             return ms;
 59         }
 60         #region Properties
 61         //当前实体类型
 62         Type entityType;
 63         Type type
 64         {
 65             get
 66             {
 67                 if(entityType ==null)
 68                 entityType = typeof(T);
 69                 return entityType;
 70             }
 71         }
 72 
 73         //实体的所有属性
 74         PropertyInfo[] propertyInfos;
 75         PropertyInfo[] pros
 76         {
 77             get
 78             {
 79                 if (propertyInfos == null) propertyInfos = this.type.GetProperties();
 80                 return propertyInfos;
 81             }
 82         }
 83 
 84         //相对应的表名
 85         string _tableName=string.Empty ;
 86         string tableName
 87         {
 88             get
 89             {
 90                 if (_tableName == string.Empty)
 91                 {
 92                     string className = typeof(T).Name;
 93 
 94                     switch (className)
 95                     {
 96                         case "Users":
 97                         case "Role_Node_Permissions":
 98                             _tableName = "PE_" + className;
 99                         default:
100                             _tableName = "HB_" + className;
101                     }
102                 }
103             }
104         }
105         #endregion
106         #endregion
107 
108 
109         #region  成员方法
110 
111         /// <summary>
112         /// 得到最大ID
113         /// </summary>
114         public int GetMaxId()
115         {
116             return DbHelperSQL.GetMaxID(pros[0].Name , tableName);
117         }
118 
119         /// <summary>
120         /// 得到最大值
121         /// </summary>
122 
123         /// <summary>
124         /// 是否存在该记录
125         /// </summary>
126         public bool Exists(int ID)
127         {
128             StringBuilder strSql = new StringBuilder();
129             strSql.Append("select count(1) from ");
130             strSql.Append(tableName);
131             strSql.Append(" where ");
132             strSql.Append(ToSetString(pros[0].Name));
133             SqlParameter[] parameters = {
134                     new SqlParameter("@"+pros[0].Name, SqlDbType.Int,4)};
135             parameters[0].Value = ID;
136 
137             return DbHelperSQL.Exists(strSql.ToString(), parameters);
138         }
139 
140 
141         /// <summary>
142         /// 增加一条数据
143         /// </summary>
144         public int Add(T model)
145         {
146             StringBuilder strSql = new StringBuilder();
147 
148             strSql .Append ("insert into ");
149             strSql .Append (tableName );
150             strSql.Append(" (");
151 
152             //生成XXXvalues(XXX)
153             int i=1;
154             foreach (PropertyInfo p in pros)
155             {
156                 strSql .Append (p.Name );
157                 if (i != pros.Length)
158                     strSql.Append(",");
159                 i++;
160             }
161             strSql.Append(") values (");
162             i = 1;
163             foreach (PropertyInfo p in pros)
164             {
165                 strSql.Append("@");
166                 strSql.Append(p.Name);
167                 if (i != pros.Length)
168                     strSql.Append(",");
169                 i++;
170             }
171             strSql.Append(");select @@IDENTITY");
172             
173             //生成sqlparameters
174             List<SqlParameter> sqlpars = new List<SqlParameter>();
175             foreach (PropertyInfo p in pros)
176             {
177                 SqlParameter sp = new SqlParameter("@" + p.Name, p.GetValue(model, null));
178                 sqlpars.Add(sp);
179             }
180             object obj = DbHelperSQL.GetSingle(strSql.ToString(), sqlpars .ToArray ());
181             if (obj == null)
182             {
183                 return 1;
184             }
185             else
186             {
187                 return Convert.ToInt32(obj);
188             }
189         }
190 
191         /// <summary>
192         /// 更新一条数据
193         /// </summary>
194         public void Update(T model)
195         {
196             StringBuilder strSql = new StringBuilder();
197             strSql.Append("update ");
198             strSql .Append (tableName );
199             strSql .Append (" set ");
200             int i=1;
201             foreach (PropertyInfo p in pros)
202             {
203                 strSql.Append(ToSetString(p.Name));
204                 if(i!=pros.Length )
205                     strSql .Append (",");
206                 i++;
207             }
208             strSql.Append(" where ");
209             strSql.Append(ToSetString(pros[0].Name));
210             //生成sqlparameters
211             List<SqlParameter> sqlpars = new List<SqlParameter>();
212             foreach (PropertyInfo p in pros)
213             {
214                 SqlParameter sp = new SqlParameter("@" + p.Name, p.GetValue(model, null));
215                 sqlpars.Add(sp);
216             }
217 
218             DbHelperSQL.ExecuteSql(strSql.ToString(), sqlpars .ToArray());
219         }
220 
221         /// <summary>
222         /// 删除一条数据
223         /// </summary>
224         public void Delete(int ID)
225         {
226 
227             StringBuilder strSql = new StringBuilder();
228             strSql.Append("delete from ");
229             strSql.Append(tableName);
230             strSql.Append(" where ");
231             strSql.Append(ToSetString(pros[0].Name));
232             SqlParameter[] parameters = {
233                     new SqlParameter("@"+pros [0].Name, SqlDbType.Int,4)};
234             parameters[0].Value = ID;
235 
236             DbHelperSQL.ExecuteSql(strSql.ToString(), parameters);
237         }
238 
239 
240         /// <summary>
241         /// 得到一个对象实体
242         /// </summary>
243         public T GetModel(int ID)
244         {
245 
246             StringBuilder strSql = new StringBuilder();
247             strSql.Append("select  top 1 ");
248             int i=1;
249             foreach (PropertyInfo p in pros)
250             {
251                 strSql.Append(ToSetString(p.Name));
252                 if(i!=pros.Length )
253                     strSql .Append (",");
254             }
255             strSql .Append (tableName );
256             strSql.Append(" where  ");
257             strSql.Append(ToSetString(pros[0].Name));
258             SqlParameter[] parameters = {
259                     new SqlParameter("@"+pros[0].Name, SqlDbType.Int,4)};
260             parameters[0].Value = ID;
261             List<T> ms;
262             using (SqlDataReader reader = DbHelperSQL.ExecuteReader(strSql.ToString (), parameters))
263             {
264                  ms= ToModel(reader);
265                 reader.Close();
266             }
267             if (ms.Count > 0) return ms[0];
268             else return default (T);
269         }
270 
271         /// <summary>
272         /// 获得数据列表
273         /// </summary>
274         public List<T> GetList(string strWhere)
275         {
276             StringBuilder strSql = new StringBuilder();
277             strSql.Append("select * ");
278             strSql.Append(" FROM ");
279             strSql.Append(tableName);
280             if (strWhere.Trim() != "")
281             {
282                 strSql.Append(" where " + strWhere);
283             }
284 
285             List<T> ms;
286             using (SqlDataReader reader = DbHelperSQL.ExecuteReader(strSql.ToString ()))
287             {
288                 ms = ToModel(reader);
289                 reader.Close();
290             }
291             if (ms.Count > 0) return ms;
292             else return null;
293         }
294 
295         /// <summary>
296         /// 获得前几行数据
297         /// </summary>
298         public List<T> GetList(int Top, string strWhere, string filedOrder)
299         {
300             StringBuilder strSql = new StringBuilder();
301             strSql.Append("select ");
302             if (Top > 0)
303             {
304                 strSql.Append(" top " + Top.ToString());
305             }
306             strSql.Append(" * ");
307             strSql.Append(" FROM  ");
308             strSql.Append(tableName);
309             if (strWhere.Trim() != "")
310             {
311                 strSql.Append(" where " + strWhere);
312             }
313             strSql.Append(" order by " + filedOrder);
314                         List<T> ms;
315             using (SqlDataReader reader = DbHelperSQL.ExecuteReader(strSql.ToString ()))
316             {
317                 ms = ToModel(reader);
318                 reader.Close();
319             }
320             if (ms.Count > 0) return ms;
321             else return null;
322         }
323         
324 
325 
326          ///<summary>
327          ///分页获取数据列表
328          ///</summary>
329         public List<T> GetList(int PageSize,int PageIndex,string strWhere)
330         {
331             List<T> ms=GetList(strWhere);
332             int startIndex=0, endIndex=0;
333             Utils.GetPageArea(ref startIndex, ref endIndex, PageIndex, PageSize, ms.Count);
334             List<T> result = new List<T>();
335             for (int i = startIndex; i <= endIndex; i++)
336             {
337                 result.Add(ms[i]);
338             }
339             return result;
340         }
341 
342         #endregion  成员方法
343 
344 
345     }
346 }