编写属于自己的SQLHelper类，泛型+反射

作者：互联网出处：控件中国网 2014-11-05 19:18:44 阅读：1

自己一直都是学习使用asp.net mvc框架+linQ的，所以对于ado.net基本上一无所知，最近接了个小项目，所以打算学习+用ado.net来完成。
　　首先就想到了，写一个通用点SQLHelper类来辅助完成数据库的操作。

1.定义一个类：public class SQLHelper<T> where T : class, new()
　　说明：SQLHelper有一个T的泛型类型，T属于class,new()，否则T就不能T t =new T()这样使用new关键字了！

2.之后就是数据库的链接和关闭等操作：

1         /// <summary>
2         /// 链接数据库字符串
3         /// </summary>
4         public static string strConnectionString = ConfigurationManager.ConnectionStrings["DataConnectionString"].ToString();
5
6         /// <summary>
7         /// 数据库连接
8         /// </summary>
9         public SqlConnection Connection = null;
10
11         public SQLHelper()
12         {
13             OpenConnect();
14         }
15
16         /// <summary>
17         /// 打开数据库连接
18         /// </summary>
19         public void OpenConnect()
20         {
21             if (Connection == null || Connection.State != System.Data.ConnectionState.Open)
22             {
23                 Connection = new SqlConnection(strConnectionString);
24
25                 Connection.Open();
26             }
27         }
28
29         /// <summary>
30         /// 关闭数据库连接
31         /// </summary>
32         public void CloseConnect()
33         {
34             if (Connection != null && Connection.State != System.Data.ConnectionState.Closed)
35             {
36                 Connection.Close();
37             }
38         }

3.对数据库进行读写操作。

1
2         /// <summary>
3         /// 执行查询语句
4         /// </summary>
5         /// <param name="strSQL">SQL语句</param>
6         /// <param name="obQuery">SQL参数的值</param>
7         /// <returns></returns>
8         public SqlDataReader ExecReader(string strSQL, object obQuery)
9         {
10             SqlCommand command = new SqlCommand(strSQL, Connection);
11
12             if (obQuery != null)
13             {
14                 PropertyInfo[] pis = obQuery.GetType().GetProperties();
15
16                 foreach (var p in pis)
17                 {
18                     command.Parameters.Add(new SqlParameter(p.Name, p.GetValue(obQuery, null)));
19                 }
20             }
21
22             SqlDataReader reader = command.ExecuteReader();
23
24             return reader;
25         }
26
27         /// <summary>
28         /// 执行返回单值的查询语句
29         /// </summary>
30         /// <param name="strSQL">SQL语句</param>
31         /// <param name="obQuery">SQL参数的值</param>
32         /// <returns></returns>
33         public object ExecSingleValue(string strSQL, object obQuery)
34         {
35             SqlCommand command = new SqlCommand(strSQL, Connection);
36
37
38             if (obQuery != null)
39             {
40                 PropertyInfo[] pis = obQuery.GetType().GetProperties();
41
42                 foreach (var p in pis)
43                 {
44                     command.Parameters.Add(new SqlParameter(p.Name, p.GetValue(obQuery, null)));
45                 }
46             }
47
48             return command.ExecuteScalar();
49         }
50
51         /// <summary>
52         /// 执行非查询语句
53         /// </summary>
54         /// <param name="strSQL">SQL语句</param>
55         /// <param name="obQuery">SQL参数的值</param>
56         /// <returns></returns>
57         public int ExecNoQuery(string strSQL, object obQuery)
58         {
59             SqlCommand command = new SqlCommand(strSQL, Connection);
60
61             if (obQuery != null)
62             {
63                 PropertyInfo[] pis = obQuery.GetType().GetProperties();
64
65                 foreach (var p in pis)
66                 {
67                     command.Parameters.Add(new SqlParameter(p.Name, p.GetValue(obQuery, null)));
68                 }
69             }
70
71             return command.ExecuteNonQuery();
72         }

解释:
      　　//判断是SQL语句中参数的值对象是否为空
      　　//例如：new{Id=1,Name="Test"}
            if (obQuery != null)
            {
      　　    //获取SQL语句中参数的值对象中的属性类型和值
                PropertyInfo[] pis = obQuery.GetType().GetProperties();
      　　    //循环遍历对象中的属性
                foreach (var p in pis)
                {
      　　     　//利用反射将Id,Name等值填充到SQL语句中的参数中
                    command.Parameters.Add(new SqlParameter(p.Name, p.GetValue(obQuery, null)));
                }
            }

查询操作中，返回的是SQLDataReader对象，如何获取我们所需要的列表，分页，单个实体呢？
4.返回列表List<T>，还是利用泛型+反射

1         /// <summary>
2         /// 获取列表
3         /// </summary>
4         /// <param name="strSQL">SQL语句</param>
5         /// <param name="obQuery">SQL参数的值</param>
6         /// <returns></returns>
7         public List<T> GetList(string strSQL, object obQuery)
8         {
9             //调用执行查询语句函数，返回SqlDataReader
10             SqlDataReader reader = ExecReader(strSQL, obQuery);
11             //定义返回的列表
12             List<T> list = new List<T>();
13             //定义T类型的实体
14             T model = new T();
15             //获取T类型实体的属性类型和值
16             PropertyInfo[] pis = model.GetType().GetProperties();
17             //获取数据库返回的列数
18             int intColCount = reader.FieldCount;
19             //遍历SqlDataReader
20             while (reader.Read())
21             {
22                 //定义
23                 int value_number = 0;
24                 //重新实例化T
25                 model = new T();
26                 //从数据库拿出一条数据后，循环遍历T类型的属性类型和值
27                 for (int i = 0; i < intColCount; i++)
28                 {
29                     //判断第一列是否为row_number，此为分页使用
30                     if (reader.GetName(i) == "row_number") value_number++;
31                     //设置T对应属性的值
32                     pis[i].SetValue(model, reader.GetValue(value_number), null);
33                     value_number++;
34                 }
35                 //将T添加到列表中
36                 list.Add(model);
37             }
38
39             return list;
40         }

5.返回分页，获取PagesList<T>
5.1。分页实体类：

1 public class PagedList<T> : List<T>
2     {
3         /// <summary>
4         /// 分页编号
5         /// </summary>
6         public int intPageIndex { get; set; }
7
8         /// <summary>
9         /// 分页大小
10         /// </summary>
11         public int intPageSize { get; set; }
12
13         /// <summary>
14         /// 分页数
15         /// </summary>
16         public int intPages { get; set; }
17
18         /// <summary>
19         /// 总元素的个数
20         /// </summary>
21         public int intTotalCount { get; set; }
22
23         /// <summary>
24         /// 此分页元素的个数
25         /// </summary>
26         public int intCount { get; set; }
27
28         /// <summary>
29         /// 是否有下一页
30         /// </summary>
31         public bool HasNextPage { get; set; }
32
33         /// <summary>
34         /// 是否有上一页
35         /// </summary>
36         public bool HasPrPage { get; set; }
37
38         public PagedList(int intPageIndex, int intPageSize)
39         {
40             this.intPageIndex = intPageIndex;
41             this.intPageSize = intPageSize;
42         }
43     }

5.2。实现分页，返回PagesList<T>

1         /// <summary>
2         /// 获取分页
3         /// </summary>
4         /// <param name="strTotalSQL">总共个数的SQL</param>
5         /// <param name="obTotalQuery">总共个数的SQL参数的值</param>
6         /// <param name="strSQL">分页的SQL</param>
7         /// <param name="obQuery">分页SQL参数的值</param>
8         /// <param name="intPageIndex">分页编号</param>
9         /// <param name="intPageSize">分页大小</param>
10         /// <returns></returns>
11         public PagedList<T> GetPageList(string strTotalSQL, object obTotalQuery, string strSQL, object obQuery, int intPageIndex, int intPageSize)
12         {
13             //定义分页对象的编号和大小
14             PagedList<T> pageList = new PagedList<T>(intPageIndex, intPageSize);
15             //执行获取单个值的函数，设置分页对象的总元素
16             pageList.intTotalCount = (int)ExecSingleValue(strTotalSQL, obTotalQuery);
17             //设置分页对象的分页数
18             if (pageList.intTotalCount % intPageSize == 0) pageList.intPages = pageList.intTotalCount / intPageSize;
19             else pageList.intPages = pageList.intTotalCount / intPageSize + 1;
20             //定义列表，调用获取列表的函数获取此分页的元素
21             List<T> list = GetList(strSQL, obQuery);
22             //将列表元素添加到分页对象当中
23             pageList.AddRange(list);
24             //设置分页对象是否有上一页和下一页
25             pageList.HasNextPage = pageList.intPageIndex < pageList.intPages ? true : false;
26             pageList.HasPrPage = pageList.intPageIndex > 1 ? true : false;
27
28             return pageList;
29         }

6.获取单个实体类：T

1         /// <summary>
2         /// 获取单个实体
3         /// </summary>
4         /// <param name="strSQL">SQL语句</param>
5         /// <param name="obQuery">SQL参数的值</param>
6         /// <returns></returns>
7         public T GetTM(string strSQL, object obQuery)
8         {
9             //调用执行查询语句，返回SqlDataReader
10             SqlDataReader reader = ExecReader(strSQL, obQuery);
11             //新建一个T类型
12             T model = new T();
13             //获取T类型的属性类型和值
14             PropertyInfo[] pis = model.GetType().GetProperties();
15             //获取数据库返回数据的列数
16             int intColCount = reader.FieldCount;
17             //读取数据，填充T
18             if (reader.Read())
19             {
20                 int value_number = 0;
21                 for (int i = 0; i < intColCount; i++)
22                 {
23                     pis[i].SetValue(model, reader.GetValue(value_number), null);
24                     value_number++;
25                 }
26             }
27
28             return model;
29         }

7.调用方法：ResumeTM为数据库表的实体类

1 public List<ResumeTM> GetResumeByJobInfoId(int intJobInfoId)
2         {
3             SQLHelper<ResumeTM> helper = new SQLHelper<ResumeTM>();
4
5             string strSQL = @"select * from Resume where FirstJobId=@JobInfoId or SecondJobId=@JobInfoId order by CreateTime desc";
6
7             object obQuery = new { JobInfoId=intJobInfoId};
8
9             List<ResumeTM> list = helper.GetList(strSQL, obQuery);
10
11             helper.CloseConnect();
12
13             return list;
14         }
15
16         public ResumeTM GetResumeById(int intId)
17         {
18             SQLHelper<ResumeTM> helper = new SQLHelper<ResumeTM>();
19
20             string strSQL = @"select * from Resume where Id=@Id";
21
22             object obQuery = new { Id=intId};
23
24             ResumeTM tm = helper.GetTM(strSQL, obQuery);
25
26             helper.CloseConnect();
27
28             return tm;
29         }
30
31         public PagedList<ResumeTM> GetResume(int intPageIndex, int intPaegSize)
32         {
33             SQLHelper<ResumeTM> helper = new SQLHelper<ResumeTM>();
34
35             string strTotalCount = @"select count(*) from Resume";
36
37             string strSQL = @"select * from
38                              (
39                                 select row_number() over(order by CreateTime desc) as row_number,* from Resume
40                              ) as t0
41                             where t0.row_number between @intPageSize*(@intPageIndex-1)+1 and @ingPageSize*@intPageIndex";
42             object obQuery = new { intPageSize=intPaegSize,intPageIndex=intPageIndex};
43
44             PagedList<ResumeTM> list = helper.GetPageList(strTotalCount, null, strSQL, obQuery, intPageIndex, intPaegSize);
45
46             helper.CloseConnect();
47
48             return list;
49         }
50
51         public void Delete(int intId)
52         {
53             SQLHelper<ResumeTM> helper = new SQLHelper<ResumeTM>();
54
55             string strSQL = @"delete from Resume where Id=@Id";
56
57             object obQuery = new { Id=intId};
58
59             helper.ExecNoQuery(strSQL, obQuery);
60
61             helper.CloseConnect();
62         }

哈哈。。写好了。。。有什么写得不好的，希望大家多多指教。拍拍砖。。。

功能分类