BaseDAL

  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 }