• 两个半成品的ORM


    只要是有点结构化的思想,不可能项目里一个sqlHelper 满天飞 到处写 ,最终你的c#代码还是得返回一个Class 才好操作,sqlhelper, datatable这种东西也只是临时将就一下,稍微先进一点的思想会用一种结构化的思想把数据访问用面向对象的方式包装成一个层,比如普创 都把各个表名字 字段名字 专门用Columbus类定义了,普创的数据访问层确实是个糟糕的设计 通过Columns 反而增加了复杂度 ,不过好歹还有那么点意识在 好歹定义了列名 不会语句写乱了分不清东南西北,当然这个东西看你怎么权衡 ,比如我以前一直都是一个sqlHelper 满天飞 ,容我做个悲伤的表情。

    分享两个以前项目刀耕火种的ORM半成品

    一个是08年的时候 记得是一个李远志的朋友 推的 ,不知他是哪里抄的还是自创的,当时心智没这么成熟 没考虑到什么
    面向对象设计 和通用 ,现在看到现公司的数据库访问设计 感觉好像 天下思想殊途同归。当时08年.net3.5都还刚推出 好多都是以前那种晦涩的C++开发方式 。EntityFramework也还没推出 好多都还没有结构 和面向对象这个概念在脑子里 泛型都还少有人用 ,这在当时感觉还是一种表面上蛮新进的一种结构设计方式,至少表面上充分的利用到了面向对象 和继承 ,以及泛型这些特性。这么多年我一直到今天才把翻出来看。

    第一个(08年的):

    开始当然是实体的定义

     1 public class Clazz
     2 {
     3     private long classId;
     4 
     5     public long ClassId
     6     {
     7         get { return classId; }
     8         set { classId = value; }
     9     }
    10 
    11     private string className;
    12 
    13     public string ClassName
    14     {
    15         get { return className; }
    16         set { className = value; }
    17     }
    18 }

    接着自然是DAL层,巧妙的利用了继承两个接口的特性 ,一个接口封装了sqlhelper实现 另外一个接口 定义了相关数据访问有哪些通用方法

    SQL helper封装:

      1 internal abstract class AbstractDAL
      2 {
      3     private IDbConnection con;
      4 
      5     private IDbTransaction tran;
      6 
      7     #region 构造方法
      8 
      9     protected AbstractDAL()
     10     {
     11         this.con = ADOHlper.CreateIDbConnection();
     12     }
     13 
     14     protected AbstractDAL(IDbConnection con)
     15     {
     16         if ((this.con = con) == null)
     17             this.con = ADOHlper.CreateIDbConnection();
     18     }
     19 
     20     protected AbstractDAL(IDbTransaction tran)
     21     {
     22         if ((this.tran = tran) == null)
     23         {
     24             this.con = ADOHlper.CreateIDbConnection();
     25         }
     26         else
     27         {
     28             this.con = this.tran.Connection;
     29             if (this.con == null || this.con.State != ConnectionState.Open)
     30                 throw new ArgumentException("非法的事务参数,其连接必须存在且处于被打开状态");
     31         }
     32     }
     33 
     34     #endregion
     35 
     36     #region 创建 SQL 命令
     37 
     38     protected IDbCommand CreateIDbCommand(string commandText, CommandType commandType)
     39     {
     40         IDbCommand cmd = this.con.CreateCommand();
     41         cmd.Transaction = this.tran;
     42         cmd.CommandText = commandText;
     43         cmd.CommandType = commandType;
     44         return cmd;
     45     }
     46 
     47     protected IDbCommand CreateIDbCommand(string commandText)
     48     { return CreateIDbCommand(commandText, CommandType.Text); }
     49 
     50     protected IDbCommand CreateIDbCommand(CommandType commandType)
     51     { return CreateIDbCommand(null, commandType); }
     52 
     53     protected IDbCommand CreateIDbCommand()
     54     { return CreateIDbCommand(null, CommandType.Text); }
     55 
     56     #endregion
     57 
     58     #region 执行委托
     59 
     60     protected T Execute<T>(ExecuteHandler<T> handler)
     61     {
     62         if (handler == null)
     63             throw new ArgumentNullException("handler<T>参数不能为空");
     64 
     65         if (this.tran != null && this.con.State != ConnectionState.Open)
     66             throw new InvalidOperationException("非法操作,当前存在事务,但其连接不处于被打开状态");
     67         if (this.con.State == ConnectionState.Open)
     68         {
     69             return handler();
     70         }
     71         else
     72         {
     73             this.con.Open();
     74             try
     75             {
     76                 return handler();
     77             }
     78             finally
     79             {
     80                 this.con.Close();
     81             }
     82         }
     83     }
     84 
     85     protected void Execute(ExecuteHandler handler)
     86     {
     87         if (handler == null)
     88             throw new ArgumentNullException("handler参数不能为空");
     89 
     90         if (this.tran != null && this.con.State != ConnectionState.Open)
     91             throw new InvalidOperationException("非法操作,当前存在事务,但其连接不处于被打开状态");
     92 
     93         if (this.con.State == ConnectionState.Open)
     94         {
     95             handler();
     96         }
     97         else
     98         {
     99             this.con.Open();
    100             try
    101             {
    102                 handler();
    103             }
    104             finally
    105             {
    106                 this.con.Close();
    107             }
    108         }
    109     }
    110 
    111     #endregion
    112 }

    sqlhelper:

     1 public static class ADOHlper
     2 {
     3     private const string CONFING_KEY = "DBconnection";
     4 
     5     private static string connectionString;
     6 
     7     static ADOHlper()
     8     {
     9         connectionString = WebConfigurationManager.ConnectionStrings[CONFING_KEY].ConnectionString;
    10         if (connectionString == null)
    11             throw new InvalidOperationException("从配置文件读取连接字符串异常");
    12     }
    13 
    14     //创建连接
    15     public static IDbConnection CreateIDbConnection()
    16     { return new SqlConnection(connectionString); }
    17 
    18     //创建数据适配器
    19     public static IDbDataAdapter CreateIDbDataAdapter()
    20     { return new SqlDataAdapter(); }
    21 
    22     #region 添加参数方法
    23 
    24     public static void AddInPrameter(IDbCommand cmd, string prameterName, DbType dbType, int size, object value)
    25     {
    26         IDbDataParameter parameter = cmd.CreateParameter();
    27         parameter.ParameterName = prameterName;
    28         parameter.DbType = dbType;
    29         parameter.Size = size;
    30         parameter.Value = value != null ? value : DBNull.Value;
    31         cmd.Parameters.Add(parameter);
    32     }
    33 
    34     public static void AddInPrameter(IDbCommand cmd, string prameterName, DbType dbType, object value)
    35     {
    36         AddInPrameter(cmd, prameterName, dbType, 0, value);
    37     }
    38 
    39     #endregion
    40 }

    特定类的数据访问定义:

    1 public interface IClassDAL
    2 {
    3     DataSet GetClasses();
    4     void SaveClass(Clazz clazz);
    5     void UpdateClass(Clazz clazz);
    6     void DeleteClass(long classId);
    7 }

    最后的主角 通过接口泛化到最终的 数据访问实现 ,运用泛型委托 让底层去执行数据操作

     1 internal class ClassDALImpl : AbstractDAL, IClassDAL
     2 {
     3     public ClassDALImpl() { }
     4     public ClassDALImpl(IDbConnection con) : base(con) { }
     5     public ClassDALImpl(IDbTransaction tran) : base(tran) { }
     6     public DataSet GetClasses()
     7     {
     8         ExecuteHandler<DataSet> handler =
     9             delegate
    10             {
    11                 IDbCommand cmd = this.CreateIDbCommand("SELECT * FROM Class");
    12                 IDbDataAdapter dapter = ADOHlper.CreateIDbDataAdapter();
    13                 dapter.SelectCommand = cmd;
    14                 dapter.TableMappings.Add("Table", "Class");
    15                 DataSet dataSet = new DataSet();
    16                 dapter.Fill(dataSet);
    17                 return dataSet;
    18             };
    19         return this.Execute(handler);
    20     }
    21 
    22     public void SaveClass(Clazz clazz)
    23     {
    24         ExecuteHandler handler =
    25             delegate
    26             {
    27                 IDbCommand cmd = this.CreateIDbCommand("INSERT INTO Class VALUES(@ClassName)");
    28                 ADOHlper.AddInPrameter(cmd, "@ClassName", DbType.AnsiString, 50, clazz.ClassName);
    29                 cmd.ExecuteNonQuery();
    30 
    31                 IDbCommand ideCmd = this.CreateIDbCommand("SELECT @@IDENTITY");
    32                 clazz.ClassId = (int)ideCmd.ExecuteScalar();
    33             };
    34         this.Execute(handler);
    35     }
    36 
    37     public void UpdateClass(Clazz clazz)
    38     {
    39         ExecuteHandler handler =
    40            delegate
    41            {
    42                IDbCommand cmd = this.CreateIDbCommand("UPDATE Class SET ClassName = @ClassName");
    43                ADOHlper.AddInPrameter(cmd, "@ClassName", DbType.AnsiString, 50, clazz.ClassName);
    44                cmd.ExecuteNonQuery();
    45            };
    46         this.Execute(handler);
    47     }
    48 
    49     public void DeleteClass(long classId)
    50     {
    51         ExecuteHandler handler =
    52            delegate
    53            {
    54                IDbCommand cmd = this.CreateIDbCommand("DELETE Class WHERE ClassId = @ClassId");
    55                ADOHlper.AddInPrameter(cmd, "@ClassId", DbType.Int64, classId);
    56                cmd.ExecuteNonQuery();
    57            };
    58         this.Execute(handler);
    59     }
    60 }

    最终通过工厂模式 统一给出实例

    1 public static class FactoryDAL
    2 {
    3     public static IClassDAL CreateClassDAL()
    4     { return new test.DAL.Impl.ClassDALImpl(); }
    5 }

    但是最终还是让各种数据操作溢出到了最终实现,没有良好的利用继承实现高内聚,跟用SQLhelper差别不大,所以算不得一个好的实现。

    第二个(应该是大约2017年的):

    这种才是稍微靠谱的方式:

    首先是列定义 也可理解为实体定义

     1 public class Ht_autoprint_Column
     2 {
     3     public static string HColName_ID = "ID";
     4     public static string HColName_CardNo = "CardNo";
     5     protected string _tableName = "t_autoprint";
     6     private string _id;
     7     private string _cardno;
     8     public string ID
     9     {
    10         get
    11         {
    12             return _id;
    13         }
    14         set
    15         {
    16             _id = value;
    17         }
    18     }
    19 
    20     public string CardNo
    21     {
    22         get
    23         {
    24             return _cardno;
    25         }
    26         set
    27         {
    28             _cardno = value;
    29         }
    30     }
    31 }    

    主要的机关是 利用了 BaseTableDB的类 ,利用反射列属性完成增删改查 ,可以理解为一种灵活的sqlhelper:

      1 public class baseTableDB<T> where T : new()
      2 {
      3     private string _connString;
      4 
      5     private string _tableName;
      6 
      7     private Exception _errorInfo;
      8 
      9     public Exception ErrorInfo => _errorInfo;
     10 
     11     public bool Init(string connString, string tbleName)
     12     {
     13         _connString = connString;
     14         _tableName = tbleName;
     15         return true;
     16     }
     17 
     18     public bool Init(string ip, string port, string datebase, string user, string pwd)
     19     {
     20         try
     21         {
     22             _connString = $"Server={ip};Port={port};Database={datebase}; User={user};Password={pwd};";
     23             return true;
     24         }
     25         catch (Exception errorInfo)
     26         {
     27             Exception ex = _errorInfo = errorInfo;
     28             return false;
     29         }
     30     }
     31 
     32     private object GetValue(object o)
     33     {
     34         if (o.GetType() == typeof(char))
     35         {
     36             return Convert.ToChar(o);
     37         }
     38         if (o.GetType() == typeof(int))
     39         {
     40             return Convert.ToInt32(o);
     41         }
     42         if (o.GetType() == typeof(double))
     43         {
     44             return Convert.ToDouble(o);
     45         }
     46         if (o.GetType() == typeof(float))
     47         {
     48             return Convert.ToSingle(o);
     49         }
     50         if (o.GetType() == typeof(DateTime))
     51         {
     52             return Convert.ToDateTime(o);
     53         }
     54         if (o.GetType() == typeof(decimal))
     55         {
     56             return Convert.ToDecimal(o);
     57         }
     58         return o.ToString();
     59     }
     60 
     61     private string GetValue(Type type, object o)
     62     {
     63         try
     64         {
     65             if (type == typeof(int) || type == typeof(double) || type == typeof(float) || 
     66 type == typeof(decimal) || type == typeof(int?) || type == typeof(double?) || 
     67 type == typeof(float?) || type == typeof(decimal?))
     68             {
     69                 return o.ToString();
     70             }
     71             return "'" + o.ToString() + "'";
     72         }
     73         catch
     74         {
     75             return "null";
     76         }
     77     }
     78 
     79     public IList<T> baseSelect(string sql)
     80     {
     81         IList<T> htAutoprintColumnList = new List<T>();
     82         MySqlConnection conn = new MySqlConnection(_connString);
     83         try
     84         {
     85             conn.Open();
     86             MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(new MySqlCommand(sql, conn));
     87             DataTable dataTable = new DataTable();
     88             mySqlDataAdapter.Fill(dataTable);
     89             foreach (DataRow row in dataTable.Rows)
     90             {
     91                 T col = new T();
     92                 PropertyInfo[] properties = col.GetType().GetProperties();
     93                 foreach (PropertyInfo p in properties)
     94                 {
     95                     if (dataTable.Columns.Contains(p.Name) && row[p.Name] != DBNull.Value)
     96                     {
     97                         p.SetValue(col, GetValue(row[p.Name]), null);
     98                     }
     99                 }
    100                 htAutoprintColumnList.Add(col);
    101             }
    102             return htAutoprintColumnList;
    103         }
    104         catch (Exception errorInfo)
    105         {
    106             Exception ex = _errorInfo = errorInfo;
    107             return htAutoprintColumnList;
    108         }
    109         finally
    110         {
    111             conn.Close();
    112         }
    113     }
    114 
    115     public IList<T> Select()
    116     {
    117         IList<T> htAutoprintColumnList = new List<T>();
    118         MySqlConnection conn = new MySqlConnection(_connString);
    119         try
    120         {
    121             conn.Open();
    122             MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(new MySqlCommand($"select * from {_tableName}", conn));
    123             DataTable dataTable = new DataTable();
    124             mySqlDataAdapter.Fill(dataTable);
    125             foreach (DataRow row in dataTable.Rows)
    126             {
    127                 DataRow row2 = row;
    128                 T col = new T();
    129                 col.GetType().GetProperties().ToList()
    130                     .ForEach(delegate(PropertyInfo u)
    131                     {
    132                         u.SetValue(col, (row2[u.Name] == DBNull.Value) ? null : GetValue(row2[u.Name]), null);
    133                     });
    134                 htAutoprintColumnList.Add(col);
    135             }
    136             return htAutoprintColumnList;
    137         }
    138         catch (Exception errorInfo)
    139         {
    140             Exception ex = _errorInfo = errorInfo;
    141             return htAutoprintColumnList;
    142         }
    143         finally
    144         {
    145             conn.Close();
    146         }
    147     }
    148 
    149     public IList<T> Select(string where)
    150     {
    151         string sql = $"select * from {_tableName} where {where}";
    152         return baseSelect(sql);
    153     }
    154 
    155     public IList<T> Select(T where)
    156     {
    157         string sql = $"select * from {_tableName} where {GetWhere(where)}";
    158         return baseSelect(sql);
    159     }
    160 
    161     public bool InsertInto(T info)
    162     {
    163         MySqlConnection conn = new MySqlConnection(_connString);
    164         try
    165         {
    166             conn.Open();
    167             string sqlColName = "";
    168             string sqlColValues = "";
    169             int i = 0;
    170             info.GetType().GetProperties().ToList()
    171                 .ForEach(delegate(PropertyInfo u)
    172                 {
    173                     if (1 == i)
    174                     {
    175                         sqlColName += ", ";
    176                         sqlColValues += ", ";
    177                     }
    178                     sqlColName += u.Name;
    179                     sqlColValues += GetValue(u.PropertyType, u.GetValue(info, null));
    180                     i = 1;
    181                 });
    182             new MySqlCommand($"insert into {_tableName}({sqlColName}) values({sqlColValues})", conn).ExecuteNonQuery();
    183             return true;
    184         }
    185         catch (Exception errorInfo)
    186         {
    187             Exception ex = _errorInfo = errorInfo;
    188             return false;
    189         }
    190         finally
    191         {
    192             conn.Close();
    193         }
    194     }
    195 
    196     public bool Update(T set, string where)
    197     {
    198         MySqlConnection conn = new MySqlConnection(_connString);
    199         try
    200         {
    201             conn.Open();
    202             string sqlSet = "";
    203             int i = 0;
    204             set.GetType().GetProperties().ToList()
    205                 .ForEach(delegate(PropertyInfo u)
    206                 {
    207                     if (u.GetValue(set, null) != null)
    208                     {
    209                         if (1 == i)
    210                         {
    211                             sqlSet += ", ";
    212                         }
    213                         sqlSet = sqlSet + u.Name + "=" + GetValue(u.PropertyType, u.GetValue(set, null));
    214                         i = 1;
    215                     }
    216                 });
    217             return new MySqlCommand($"Update {_tableName} set {sqlSet} where {where}", conn).ExecuteNonQuery() != 0;
    218         }
    219         catch (Exception errorInfo)
    220         {
    221             Exception ex = _errorInfo = errorInfo;
    222             return false;
    223         }
    224         finally
    225         {
    226             conn.Close();
    227         }
    228     }
    229 
    230     public bool Update(string set, string where)
    231     {
    232         MySqlConnection conn = new MySqlConnection(_connString);
    233         try
    234         {
    235             conn.Open();
    236             return new MySqlCommand($"Update {_tableName} set {set} where {where}", conn).ExecuteNonQuery() != 0;
    237         }
    238         catch (Exception errorInfo)
    239         {
    240             throw _errorInfo = errorInfo;
    241         }
    242         finally
    243         {
    244             conn.Close();
    245         }
    246     }
    247 
    248     public bool baseUpdate(string sql)
    249     {
    250         MySqlConnection conn = new MySqlConnection(_connString);
    251         try
    252         {
    253             conn.Open();
    254             return new MySqlCommand(sql, conn).ExecuteNonQuery() != 0;
    255         }
    256         catch (Exception errorInfo)
    257         {
    258             throw new Exception($"sql:{sql}, ex:{(_errorInfo = errorInfo).ToString()}");
    259         }
    260         finally
    261         {
    262             conn.Close();
    263         }
    264     }
    265 
    266     public bool Update(string set, T where)
    267     {
    268         string sql = $"Update {_tableName} set {set} where {GetWhere(where)}";
    269         return baseUpdate(sql);
    270     }
    271 
    272     public bool Update(T set, T where)
    273     {
    274         string sqlSet = "";
    275         int i = 0;
    276         set.GetType().GetProperties().ToList()
    277             .ForEach(delegate(PropertyInfo u)
    278             {
    279                 if (u.GetValue(set, null) != null)
    280                 {
    281                     if (1 == i)
    282                     {
    283                         sqlSet += ", ";
    284                     }
    285                     sqlSet = sqlSet + u.Name + "=" + GetValue(u.PropertyType, u.GetValue(set, null));
    286                     i = 1;
    287                 }
    288             });
    289         string sql = $"Update {_tableName} set {sqlSet} where {GetWhere(where)}";
    290         return baseUpdate(sql);
    291     }
    292 
    293     public bool Delete(T where)
    294     {
    295         string sql = $"delete from {_tableName} where {GetWhere(where)}";
    296         return baseUpdate(sql);
    297     }
    298 
    299     public bool Delete(string where)
    300     {
    301         string sql = $"delete from {_tableName} where {where}";
    302         return baseUpdate(sql);
    303     }
    304 
    305     public DataTable ExecuteQuery(string sql)
    306     {
    307         new List<T>();
    308         MySqlConnection conn = new MySqlConnection(_connString);
    309         try
    310         {
    311             conn.Open();
    312             MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(new MySqlCommand($"select * from {_tableName}", conn));
    313             DataTable dataTable = new DataTable();
    314             mySqlDataAdapter.Fill(dataTable);
    315             return dataTable;
    316         }
    317         catch (Exception errorInfo)
    318         {
    319             Exception ex = _errorInfo = errorInfo;
    320             return null;
    321         }
    322         finally
    323         {
    324             conn.Close();
    325         }
    326     }
    327 
    328     public bool ExecuteNonQuery(string sql)
    329     {
    330         return baseUpdate(sql);
    331     }
    332 
    333     private string GetWhere(T where)
    334     {
    335         string sqlWhere = "";
    336         int i = 0;
    337         where.GetType().GetProperties().ToList()
    338             .ForEach(delegate(PropertyInfo u)
    339             {
    340                 if (u.GetValue(where, null) != null)
    341                 {
    342                     if (1 == i)
    343                     {
    344                         sqlWhere += " and ";
    345                     }
    346                     sqlWhere = sqlWhere + u.Name + "=" + GetValue(u.PropertyType, u.GetValue(where, null));
    347                     i = 1;
    348                 }
    349             });
    350         return sqlWhere;
    351     }
    352 }

    最后使用继承实体属性 配合sqlhelper的方式完成增删改查

      1 public class baseTableDB<T> where T : new()
      2 {
      3     private string _connString;
      4 
      5     private string _tableName;
      6 
      7     private Exception _errorInfo;
      8 
      9     public Exception ErrorInfo => _errorInfo;
     10 
     11     public bool Init(string connString, string tbleName)
     12     {
     13         _connString = connString;
     14         _tableName = tbleName;
     15         return true;
     16     }
     17 
     18     public bool Init(string ip, string port, string datebase, string user, string pwd)
     19     {
     20         try
     21         {
     22             _connString = $"Server={ip};Port={port};Database={datebase}; User={user};Password={pwd};";
     23             return true;
     24         }
     25         catch (Exception errorInfo)
     26         {
     27             Exception ex = _errorInfo = errorInfo;
     28             return false;
     29         }
     30     }
     31 
     32     private object GetValue(object o)
     33     {
     34         if (o.GetType() == typeof(char))
     35         {
     36             return Convert.ToChar(o);
     37         }
     38         if (o.GetType() == typeof(int))
     39         {
     40             return Convert.ToInt32(o);
     41         }
     42         if (o.GetType() == typeof(double))
     43         {
     44             return Convert.ToDouble(o);
     45         }
     46         if (o.GetType() == typeof(float))
     47         {
     48             return Convert.ToSingle(o);
     49         }
     50         if (o.GetType() == typeof(DateTime))
     51         {
     52             return Convert.ToDateTime(o);
     53         }
     54         if (o.GetType() == typeof(decimal))
     55         {
     56             return Convert.ToDecimal(o);
     57         }
     58         return o.ToString();
     59     }
     60 
     61     private string GetValue(Type type, object o)
     62     {
     63         try
     64         {
     65             if (type == typeof(int) || type == typeof(double) || type == typeof(float) ||
     66  type == typeof(decimal) || type == typeof(int?) || type == typeof(double?) || 
     67 type == typeof(float?) || type == typeof(decimal?))
     68             {
     69                 return o.ToString();
     70             }
     71             return "'" + o.ToString() + "'";
     72         }
     73         catch
     74         {
     75             return "null";
     76         }
     77     }
     78 
     79     public IList<T> baseSelect(string sql)
     80     {
     81         IList<T> htAutoprintColumnList = new List<T>();
     82         MySqlConnection conn = new MySqlConnection(_connString);
     83         try
     84         {
     85             conn.Open();
     86             MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(new MySqlCommand(sql, conn));
     87             DataTable dataTable = new DataTable();
     88             mySqlDataAdapter.Fill(dataTable);
     89             foreach (DataRow row in dataTable.Rows)
     90             {
     91                 T col = new T();
     92                 PropertyInfo[] properties = col.GetType().GetProperties();
     93                 foreach (PropertyInfo p in properties)
     94                 {
     95                     if (dataTable.Columns.Contains(p.Name) && row[p.Name] != DBNull.Value)
     96                     {
     97                         p.SetValue(col, GetValue(row[p.Name]), null);
     98                     }
     99                 }
    100                 htAutoprintColumnList.Add(col);
    101             }
    102             return htAutoprintColumnList;
    103         }
    104         catch (Exception errorInfo)
    105         {
    106             Exception ex = _errorInfo = errorInfo;
    107             return htAutoprintColumnList;
    108         }
    109         finally
    110         {
    111             conn.Close();
    112         }
    113     }
    114 
    115     public IList<T> Select()
    116     {
    117         IList<T> htAutoprintColumnList = new List<T>();
    118         MySqlConnection conn = new MySqlConnection(_connString);
    119         try
    120         {
    121             conn.Open();
    122             MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(new MySqlCommand($"select * from {_tableName}", conn));
    123             DataTable dataTable = new DataTable();
    124             mySqlDataAdapter.Fill(dataTable);
    125             foreach (DataRow row in dataTable.Rows)
    126             {
    127                 DataRow row2 = row;
    128                 T col = new T();
    129                 col.GetType().GetProperties().ToList()
    130                     .ForEach(delegate(PropertyInfo u)
    131                     {
    132                         u.SetValue(col, (row2[u.Name] == DBNull.Value) ? null : GetValue(row2[u.Name]), null);
    133                     });
    134                 htAutoprintColumnList.Add(col);
    135             }
    136             return htAutoprintColumnList;
    137         }
    138         catch (Exception errorInfo)
    139         {
    140             Exception ex = _errorInfo = errorInfo;
    141             return htAutoprintColumnList;
    142         }
    143         finally
    144         {
    145             conn.Close();
    146         }
    147     }
    148 
    149     public IList<T> Select(string where)
    150     {
    151         string sql = $"select * from {_tableName} where {where}";
    152         return baseSelect(sql);
    153     }
    154 
    155     public IList<T> Select(T where)
    156     {
    157         string sql = $"select * from {_tableName} where {GetWhere(where)}";
    158         return baseSelect(sql);
    159     }
    160 
    161     public bool InsertInto(T info)
    162     {
    163         MySqlConnection conn = new MySqlConnection(_connString);
    164         try
    165         {
    166             conn.Open();
    167             string sqlColName = "";
    168             string sqlColValues = "";
    169             int i = 0;
    170             info.GetType().GetProperties().ToList()
    171                 .ForEach(delegate(PropertyInfo u)
    172                 {
    173                     if (1 == i)
    174                     {
    175                         sqlColName += ", ";
    176                         sqlColValues += ", ";
    177                     }
    178                     sqlColName += u.Name;
    179                     sqlColValues += GetValue(u.PropertyType, u.GetValue(info, null));
    180                     i = 1;
    181                 });
    182             new MySqlCommand($"insert into {_tableName}({sqlColName}) values({sqlColValues})", conn).ExecuteNonQuery();
    183             return true;
    184         }
    185         catch (Exception errorInfo)
    186         {
    187             Exception ex = _errorInfo = errorInfo;
    188             return false;
    189         }
    190         finally
    191         {
    192             conn.Close();
    193         }
    194     }
    195 
    196     public bool Update(T set, string where)
    197     {
    198         MySqlConnection conn = new MySqlConnection(_connString);
    199         try
    200         {
    201             conn.Open();
    202             string sqlSet = "";
    203             int i = 0;
    204             set.GetType().GetProperties().ToList()
    205                 .ForEach(delegate(PropertyInfo u)
    206                 {
    207                     if (u.GetValue(set, null) != null)
    208                     {
    209                         if (1 == i)
    210                         {
    211                             sqlSet += ", ";
    212                         }
    213                         sqlSet = sqlSet + u.Name + "=" + GetValue(u.PropertyType, u.GetValue(set, null));
    214                         i = 1;
    215                     }
    216                 });
    217             return new MySqlCommand($"Update {_tableName} set {sqlSet} where {where}", conn).ExecuteNonQuery() != 0;
    218         }
    219         catch (Exception errorInfo)
    220         {
    221             Exception ex = _errorInfo = errorInfo;
    222             return false;
    223         }
    224         finally
    225         {
    226             conn.Close();
    227         }
    228     }
    229 
    230     public bool Update(string set, string where)
    231     {
    232         MySqlConnection conn = new MySqlConnection(_connString);
    233         try
    234         {
    235             conn.Open();
    236             return new MySqlCommand($"Update {_tableName} set {set} where {where}", conn).ExecuteNonQuery() != 0;
    237         }
    238         catch (Exception errorInfo)
    239         {
    240             throw _errorInfo = errorInfo;
    241         }
    242         finally
    243         {
    244             conn.Close();
    245         }
    246     }
    247 
    248     public bool baseUpdate(string sql)
    249     {
    250         MySqlConnection conn = new MySqlConnection(_connString);
    251         try
    252         {
    253             conn.Open();
    254             return new MySqlCommand(sql, conn).ExecuteNonQuery() != 0;
    255         }
    256         catch (Exception errorInfo)
    257         {
    258             throw new Exception($"sql:{sql}, ex:{(_errorInfo = errorInfo).ToString()}");
    259         }
    260         finally
    261         {
    262             conn.Close();
    263         }
    264     }
    265 
    266     public bool Update(string set, T where)
    267     {
    268         string sql = $"Update {_tableName} set {set} where {GetWhere(where)}";
    269         return baseUpdate(sql);
    270     }
    271 
    272     public bool Update(T set, T where)
    273     {
    274         string sqlSet = "";
    275         int i = 0;
    276         set.GetType().GetProperties().ToList()
    277             .ForEach(delegate(PropertyInfo u)
    278             {
    279                 if (u.GetValue(set, null) != null)
    280                 {
    281                     if (1 == i)
    282                     {
    283                         sqlSet += ", ";
    284                     }
    285                     sqlSet = sqlSet + u.Name + "=" + GetValue(u.PropertyType, u.GetValue(set, null));
    286                     i = 1;
    287                 }
    288             });
    289         string sql = $"Update {_tableName} set {sqlSet} where {GetWhere(where)}";
    290         return baseUpdate(sql);
    291     }
    292 
    293     public bool Delete(T where)
    294     {
    295         string sql = $"delete from {_tableName} where {GetWhere(where)}";
    296         return baseUpdate(sql);
    297     }
    298 
    299     public bool Delete(string where)
    300     {
    301         string sql = $"delete from {_tableName} where {where}";
    302         return baseUpdate(sql);
    303     }
    304 
    305     public DataTable ExecuteQuery(string sql)
    306     {
    307         new List<T>();
    308         MySqlConnection conn = new MySqlConnection(_connString);
    309         try
    310         {
    311             conn.Open();
    312             MySqlDataAdapter mySqlDataAdapter = new MySqlDataAdapter(new MySqlCommand($"select * from {_tableName}", conn));
    313             DataTable dataTable = new DataTable();
    314             mySqlDataAdapter.Fill(dataTable);
    315             return dataTable;
    316         }
    317         catch (Exception errorInfo)
    318         {
    319             Exception ex = _errorInfo = errorInfo;
    320             return null;
    321         }
    322         finally
    323         {
    324             conn.Close();
    325         }
    326     }
    327 
    328     public bool ExecuteNonQuery(string sql)
    329     {
    330         return baseUpdate(sql);
    331     }
    332 
    333     private string GetWhere(T where)
    334     {
    335         string sqlWhere = "";
    336         int i = 0;
    337         where.GetType().GetProperties().ToList()
    338             .ForEach(delegate(PropertyInfo u)
    339             {
    340                 if (u.GetValue(where, null) != null)
    341                 {
    342                     if (1 == i)
    343                     {
    344                         sqlWhere += " and ";
    345                     }
    346                     sqlWhere = sqlWhere + u.Name + "=" + GetValue(u.PropertyType, u.GetValue(where, null));
    347                     i = 1;
    348                 }
    349             });
    350         return sqlWhere;
    351     }
    352 }

     什么sugar啊各种ORM之类的也可以看到人类一路走过来都在造这些玩意儿 ,回望过去这些半成品 也算是有一些影子在里面吧。

  • 相关阅读:
    sqli-labs(30)
    sqli-labs(29)
    sqli-labs29-31关Background-6 服务器(两层)架构
    HA高可用的搭建
    克隆虚拟机,如何将克隆虚拟的网卡设置为eth0
    mysql1主多从配置
    关于mysql binlog日志的格式说明
    mysql主从同步
    tomcat的安装
    获取系统的IP
  • 原文地址:https://www.cnblogs.com/assassinx/p/14228757.html
Copyright © 2020-2023  润新知