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 }
