SQLHelper.cs

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Data;
 4 using System.Data.SqlClient;
 5 using System.Linq;
 6 using System.Reflection;
 7 using System.Text;
 8 using System.Threading.Tasks;
 9 
10 namespace DataBaseUility
11 {
12     /// <summary>
13     /// 数据库使用公共类
14     /// </summary>
15     public class SQLHelper
16     {
17         public static string Connection
18         {
19             get
20             {
21                 return "Data Source=.;Database=ShortMessage;Integrated Security=true";
22             }
23         }
24 
25         /// <summary>
26         /// 增删改数据
27         /// </summary>
28         /// <param name="strSql"></param>
29         /// <returns></returns>
30         public static bool Processing(string strSql)
31         {
32             using (SqlConnection sqlCon = new SqlConnection(Connection))
33             {
34                 sqlCon.Open();
35                 SqlCommand sqlCom = new SqlCommand(strSql, sqlCon);
36                 if (sqlCom.ExecuteNonQuery() > 0)
37                 {
38                     return true;
39                 }
40                 return false;
41             }
42         }
43 
44         /// <summary>
45         /// 指定条件查询，返回实体集合
46         /// </summary>
47         /// <returns></returns>
48         public static List<T> GetTabaleValue<T>(string strSql)
49             where T : class,new()
50         {
51             using (SqlConnection sqlCon = new SqlConnection(DatabaseCommon.Connection))
52             {
53                 sqlCon.Open();
54                 SqlDataAdapter sqlDA = new SqlDataAdapter(strSql, sqlCon);
55                 DataTable dt = new DataTable();
56                 sqlDA.Fill(dt);
57                 List<T> config = DTToModel<T>(dt);
58                 return config;
59             }
60         }
61 
62         /// <summary>
63         /// 数据表转换为实体
64         /// </summary>
65         /// <typeparam name="T"></typeparam>
66         /// <param name="dt"></param>
67         /// <returns></returns>
68         public static List<T> DTToModel<T>(DataTable dt) where T : class,new()
69         {
70             Type type = typeof(T);
71             PropertyInfo[] files = type.GetProperties();
72             List<T> list = new List<T>();
73             foreach (DataRow dr in dt.Rows)
74             {
75                 T model = new T();
76                 for (int i = 0; i < files.Count(); i++)
77                 {
78                     files[i].SetValue(model, dr[i]);
79                 }
80                 list.Add(model);
81             }
82             return list;
83         }
84     }
85 
86 }