1 using Ruanmou.Framework.Data;
2 using Ruanmou.Libraries.IDAL;
3 using Ruanmou.Libraries.Model;
4 using System;
5 using System.Collections.Generic;
6 using System.Configuration;
7 using System.Data.SqlClient;
8 using System.Linq;
9 using System.Reflection;
10 using System.Text;
11 using System.Threading.Tasks;
12
13 namespace Ruanmou.Libraries.DAL
14 {
15 /// <summary>
16 /// Eleven 为什么要约束？
17 /// 1 希望调用者不要犯错，避免将其他实体传进来
18 /// 2 BaseModel 保证一定有ID 而且是int 自增主键
19 /// </summary>
20 public class BaseDAL : IBaseDAL
21 {
22 private static string ConnectionStringCustomers = ConfigurationManager.ConnectionStrings["Customers"].ConnectionString;
23 public bool Add<T>(T t) where T : BaseModel
24 {
25 //Eleven
26 //id是自增的 所以不能新增
27
28 Type type = t.GetType();
29
30 string columnString = string.Join(",", type.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public)//不要父类的
31 //.Where(p=>!p.Name.Equals("Id"))//去掉id--主键约束了
32 .Select(p => $"[{p.Name}]"));
33 //string valueColumn = string.Join(",", type.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public).Select(p => $"'{p.GetValue(t)}'"));
34 //引号怎么加---sqlserver 任意值都可以加单引号
35 //假如都加引号，如果Name的值里面有个单引号，sql变成什么样的 Eleven's sql会错
36 //还有sql注入风险
37 //所以要参数化
38 string valueColumn = string.Join(",", type.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public)
39 .Select(p => $"@{p.Name}"));
40 var parameterList = type.GetProperties(BindingFlags.DeclaredOnly | BindingFlags.Instance | BindingFlags.Public)
41 .Select(p => new SqlParameter($"@{p.Name}", p.GetValue(t) ?? DBNull.Value));//注意可空类型
42
43 string sql = $"Insert [{type.Name}] ({columnString}) values({valueColumn})";
44 using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers))
45 {
46 SqlCommand command = new SqlCommand(sql, conn);
47 command.Parameters.AddRange(parameterList.ToArray());
48 conn.Open();
49 return command.ExecuteNonQuery() == 1;
50 //新增后把id拿出来？ 可以的，在sql后面增加个 Select @@Identity; ExecuteScalar
51 }
52 }
53 /// <summary>
54 /// 可以提供给增删改查 到处用的
55 /// 自己试试，怎样来调用这个方法
56 /// </summary>
57 /// <typeparam name="T"></typeparam>
58 /// <param name="sql"></param>
59 /// <param name="func"></param>
60 /// <returns></returns>
61 private T ExecuteSql<T>(string sql, Func<SqlCommand, T> func)
62 {
63 using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers))
64 {
65 //conn.BeginTransaction()
66 //try
67 //{
68 //}
69 //catch (Exception)
70 //{
71 // //ROLLBack
72 // throw;
73 //}
74 SqlCommand command = new SqlCommand(sql, conn);
75 //command.Parameters.AddRange(parameterList.ToArray());
76 conn.Open();
77 return func.Invoke(command);
78 //新增后把id拿出来？ 可以的，在sql后面增加个 Select @@Identity; ExecuteScalar
79 }
80 }
81
82
83 public bool Delete<T>(T t) where T : BaseModel
84 {
85 //t.Id
86 throw new NotImplementedException();
87 }
88
89 public List<T> FindAll<T>() where T : BaseModel
90 {
91 Type type = typeof(T);
92 //string sql = $"SELECT {string.Join(",", type.GetProperties().Select(p => $"[{p.Name}]"))} FROM [{type.Name}]";
93
94 //你的类名称如果跟命名空间重复了，也不能用
95 string sql = ElevenSqlBuilder<T>.FindAllSql;
96 using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers))
97 {
98 SqlCommand command = new SqlCommand(sql, conn);
99 conn.Open();
100 var reader = command.ExecuteReader();
101 List<T> tList = new List<T>();
102 //object oObject = Activator.CreateInstance(type);
103 while (reader.Read())
104 {
105 //object oObject = Activator.CreateInstance(type);
106 ////Eleven 放在外面 对象是同一个，引用类型
107 //foreach (var prop in type.GetProperties())
108 //{
109 // prop.SetValue(oObject, reader[prop.Name] is DBNull ? null : reader[prop.Name]);
110 //}
111
112 tList.Add(this.Trans<T>(type, reader));
113 }
114 return tList;
115 }
116 }
117
118 #region Private Method
119 private T Trans<T>(Type type, SqlDataReader reader)
120 {
121 object oObject = Activator.CreateInstance(type);
122 foreach (var prop in type.GetProperties())
123 {
124 //prop.SetValue(oObject, reader[prop.Name]]);
125 //Eleven 可空类型，如果数据库存储的是null，直接SetValue会报错的
126 //prop.SetValue(oObject, reader[prop.GetColumnName()] is DBNull ? null : reader[prop.Name]);
127 prop.SetValue(oObject, reader[prop.Name] is DBNull ? null : reader[prop.Name]);
128 }
129 return (T)oObject;
130 }
131 #endregion
132
133
134 public T FindT<T>(int id) where T : BaseModel
135 {
136 Type type = typeof(T);
137 //如果数据库的表/字段名称和程序中实体不一致，尝试用特性提供，解决增删改查
138 //2种做法，要么拼装和绑定都用特性 要么就是AS一下
139 //string sql = $"SELECT {string.Join(",", type.GetProperties().Select(p => $"[{p.GetColumnName()}]"))} FROM [{type.Name}] WHERE ID={id}";
140 string sql = $"SELECT {string.Join(",", type.GetProperties().Select(p => $"[{p.GetColumnName()}] AS [{p.Name}]"))} FROM [{type.Name}] WHERE ID={id}";
141 using (SqlConnection conn = new SqlConnection(ConnectionStringCustomers))
142 {
143 SqlCommand command = new SqlCommand(sql, conn);
144 conn.Open();
145 var reader = command.ExecuteReader();
146 if (reader.Read())
147 {
148 //object oObject = Activator.CreateInstance(type);
149 //foreach (var prop in type.GetProperties())
150 //{
151 // //prop.SetValue(oObject, reader[prop.Name]]);
152 // //Eleven 可空类型，如果数据库存储的是null，直接SetValue会报错的
153 // prop.SetValue(oObject, reader[prop.Name] is DBNull ? null : reader[prop.Name]);
154 //}
155 return this.Trans<T>(type, reader);
156 }
157 else
158 {
159 return null;//Eleven 数据库没有，应该返回null 而不是一个默认对象
160 }
161 }
162 }
163
164 public bool Update<T>(T t) where T : BaseModel
165 {
166 throw new NotImplementedException();
167 }
168 }
169 }
浙公网安备 33010602011771号