你的位置:首页 > ASP.net教程

[ASP.net教程]我的EF功能


由来

话说这个功能想法由来与java的Hibernate功能,我需要一个类和数据库映射,很简单的写一个实体类简单配置一下就ok了,

很是方便,

 1 package com.game.po.log; 2  3 import com.game.engine.utils.Config; 4 import com.game.po.player.Role; 5 import com.game.structs.player.Player; 6 import java.io.Serializable; 7 import javax.persistence.Column; 8 import javax.persistence.GeneratedValue; 9 import javax.persistence.GenerationType; 10 import javax.persistence.Id; 11 import javax.persistence.MappedSuperclass; 12  13 /** 14  * 15  * @author Vicky 16  * @mail eclipser@163.com 17  * @phone 13618074943 18 */ 19 @MappedSuperclass 20 public abstract class BaseLog implements Serializable { 21    22   private static final long serialVersionUID = 1L; 23    24   @Id 25   @GeneratedValue(strategy = GenerationType.AUTO) 26   protected long id; 27  28   // 所属用户ID 29   @Column 30   private long userid; 31    32   // 所属用户名称 33   @Column 34   private String username; 35    36   // 创建的服务器ID 37   @Column 38   private int serverid; 39    40   // 服务器名称 41   @Column(length = 64) 42   private String servername; 43    44   // 渠道名称 45   @Column(length = 64) 46   private String serverweb; 47    48   // 角色ID 49   @Column 50   private long playerid; 51    52   // 角色名称 53   @Column(length = 64) 54   private String playername; 55    56   // 登录的服务器IP 57   @Column(length = 64) 58   private String loginIP; 59    60   // 日志创建的服务器ID 61   @Column(nullable = false) 62   private int createServerID; 63    64   // 日志创建的服务器名称 65   @Column(nullable = false, length = 64) 66   private String createServerName; 67    68   // 日志创建的服务器渠道 69   @Column(nullable = false, length = 64) 70   private String createServerWeb; 71    72   // 创建时间 73   @Column(nullable = false) 74   private long createTime = System.currentTimeMillis(); 75    76   public BaseLog() { 77   } 78  79   public BaseLog(Player player) { 80     // 初始化日志字段信息 81     if (player != null) { 82       this.userid = player.getUserId(); 83       this.username = player.getUsername(); 84       this.serverid = player.getServerId(); 85       this.servername = player.getServername(); 86       this.serverweb = player.getServerweb(); 87       this.loginIP = player.getLoginIP(); 88       this.playerid = player.getId(); 89       this.playername = player.getName(); 90     } 91     this.createServerID = Config.serverID; 92     this.createServerName = Config.ServerName; 93     this.createServerWeb = Config.ServerWeb; 94   } 95  96   public BaseLog(Role role) { 97     if (role != null) { 98       this.userid = role.getUserid(); 99       this.username = role.getUsername();100       this.serverid = role.getServerid();101       this.servername = role.getServername();102       this.serverweb = role.getServerweb();103       this.loginIP = role.getLoginIP();104       this.playerid = role.getPid();105       this.playername = role.getName();106     }107     108     this.createServerID = Config.serverID;109     this.createServerName = Config.ServerName;110     this.createServerWeb = Config.ServerWeb;111   }112 113   public long getId() {114     return id;115   }116 117 //  public void setId(long id) {118 //    this.id = id;119 //  }120 121   public long getUserid() {122     return userid;123   }124 125   public void setUserid(long userid) {126     this.userid = userid;127   }128 129   public String getUsername() {130     return username;131   }132 133   public void setUsername(String username) {134     this.username = username;135   }136 137   public int getServerid() {138     return serverid;139   }140 141   public void setServerid(int serverid) {142     this.serverid = serverid;143   }144 145   public String getServername() {146     return servername;147   }148 149   public void setServername(String servername) {150     this.servername = servername;151   }152 153   public String getServerweb() {154     return serverweb;155   }156 157   public void setServerweb(String serverweb) {158     this.serverweb = serverweb;159   }160 161   public String getLoginIP() {162     return loginIP;163   }164 165   public void setLoginIP(String loginIP) {166     this.loginIP = loginIP;167   }168 169   public long getPlayerid() {170     return playerid;171   }172 173   public void setPlayerid(long playerid) {174     this.playerid = playerid;175   }176 177   public String getPlayername() {178     return playername;179   }180 181   public void setPlayername(String playername) {182     this.playername = playername;183   }184 185   public int getCreateServerID() {186     return createServerID;187   }188 189   public void setCreateServerID(int createServerID) {190     this.createServerID = createServerID;191   }192 193   public String getCreateServerName() {194     return createServerName;195   }196 197   public void setCreateServerName(String createServerName) {198     this.createServerName = createServerName;199   }200 201   public String getCreateServerWeb() {202     return createServerWeb;203   }204 205   public void setCreateServerWeb(String createServerWeb) {206     this.createServerWeb = createServerWeb;207   }208 209   public long getCreateTime() {210     return createTime;211   }212 213   public void setCreateTime(long createTime) {214     this.createTime = createTime;215   }216 217   @Override218   public int hashCode() {219     int hash = 7;220     hash = 23 * hash + (int) (this.id ^ (this.id >>> 32));221     return hash;222   }223 224   @Override225   public boolean equals(Object obj) {226     if (obj == null) {227       return false;228     }229     if (getClass() != obj.getClass()) {230       return false;231     }232     final BaseLog other = (BaseLog) obj;233     if (this.id != other.id) {234       return false;235     }236     return true;237   }238   239 }

View Code
就这样简单的写个实体类,加上注解,就完成了数据库映射配置,程序启动后Hibernate自动完成数据库和实体类的更新。
反而EF或者linq都让我觉得有些麻烦。实体类映射数据库,数据库映射实体类。

 

我为什么需要这样呢?

所谓我就想我能不能简单实现这个功能?因为有几个需求:

我需要一些日志记录类,这些类我希望他自动生成,并且能快速的实时的存入数据库。
EF,linq之类的也能完成这样的需求,但是蛮复杂的,而且我是游戏服务器开发人员,
不能完全随意的更改代码重启程序更新数据库等操作

所以我产生了这样一个需求在某种特定的条件下我只需要传入一个实体类,希望把这个实体类的数据自动存入数据库。

于是我开始不断的百度,无奈中国无法google,别告诉FQ哈。没有找到我需要的,或者说是满足我需求的现成货。

那么我只能自己动手了。

设计思路

通过实体类的反射转化sql,然后执行数据库映射,和数据存储,读取。

废话不多说

根据 Hibernate ,EF,Linq 的实现机制肯定是需要加注解的,因为需要满足不同需求嘛,当然也可以不用加。

 1   /// <summary> 2   /// 数据库关联类标识符 3   /// </summary> 4   public class EntityAttribute : Attribute 5   { 6  7     public string Name { get; set; } 8  9     public string Description { get; set; }10   }

实体类标识

 1   /// <summary> 2   /// 属性字段 3   /// </summary> 4   public class ColumnAttribute : Attribute 5   { 6     public ColumnAttribute() 7     { 8  9     }10     /// <summary>11     /// 数据库对应的字段名称12     /// </summary>13     public string DBName { get; set; }14     /// <summary>15     /// 原始字段名16     /// </summary>17     public string Name { get; set; }18     /// <summary>19     /// 数据类型20     /// </summary>21     public string DBType { get; set; }22     /// <summary>23     /// 长度24     /// </summary>25     public int Length { get; set; }26 27     /// <summary>28     /// 是否是数据库主键29     /// </summary>30     public bool IsP { get; set; }31 32     /// <summary>33     /// 是否允许为null34     /// </summary>35     public bool IsNotNull { get; set; }36 37     /// <summary>38     /// 自增39     /// </summary>40     public bool IsAuto { get; set; }41 42     /// <summary>43     /// 将会被忽略的属性44     /// </summary>45     public bool IsTemp { get; set; }46     /// <summary>47     /// 描述48     /// </summary>49     public string Description { get; set; }50 51     /// <summary>52     /// 记录字段的参数53     /// </summary>54     public string Value { get; set; }55   }

属性字段标识

还需要一个发生实体类的时候存储实体类信息的

 1   public class DBCache 2   { 3     public DBCache() 4     { 5       ColumnPs = new List<ColumnAttribute>(); 6       Columns = new List<ColumnAttribute>(); 7     } 8     public Type Instance { get; set; } 9 10     public string TableName { get; set; }11     /// <summary>12     /// 主键列13     /// </summary>14     public List<ColumnAttribute> ColumnPs { get; set; }15     /// <summary>16     /// 所有列17     /// </summary>18     public List<ColumnAttribute> Columns { get; set; }19 20   }

 

满足基本所有需求了。

 1   /// <summary> 2   ///  3   /// </summary> 4   [EntityAttribute(Name = "user")] 5   public class DBClassB 6   { 7     /// <summary> 8     ///  9     /// </summary>10     [ColumnAttribute(DBName = "ID", Length = 4, IsP = true, IsAuto = true)]11     public int ID { get; set; }12     /// <summary>13     /// 14     /// </summary>15     public string Name { get; set; }16 17     [ColumnAttribute]18     public byte Sex { get; set; }19 20     [ColumnAttribute(IsTemp = true)]21     public string TempName { get; set; }22 23   }

测试类。

上面是使用方法和辅助注解实现。

功能如何实现呢?

功能需要反射实体类,把实体类的反射信息存储到 DBCache 中。
然后根据 DBCache 转化sql语句,来实现数据库执行映射。

这里我就以 sqlite 数据库为例实现功能为了实现多数据库功能版本切换,我们需要一个接口,

 1   /// <summary> 2   /// sql语句生成器 3   /// </summary> 4   public interface ICreateSqlScript : Sz.ScriptPool.IBaseScript 5   { 6  7     /// <summary> 8     /// 想要实现自动创建表实体类必须实现 添加 EntityAttribute 特性 9     /// </summary>10     /// <param name="key"></param>11     void InitTables(string key);12 13     /// <summary>14     /// 创建表和更新表结构15     /// </summary>16     /// <param name="dbObject">@实例对象</param>17     /// <returns></returns>18     string CreateTableSql(object dbObject, string key);19 20     /// <summary>21     /// 修改表结构22     /// </summary>23     /// <param name="dbObject"></param>24     /// <param name="key"></param>25     /// <returns></returns>26     string UpdateTableSql(object dbObject, string key);27 28     /// <summary>29     /// 删除表30     /// </summary>31     /// <param name="dbObject"></param>32     /// <param name="key"></param>33     /// <returns></returns>34     string DelTableSql(object dbObject, string key);35 36     /// <summary>37     /// 创建 Inster sql 38     /// </summary>39     /// <param name="dbObject">实例对象</param>40     /// <returns></returns>41     int InsterIntoSql(object dbObject, string key);42 43     /// <summary>44     /// 45     /// </summary>46     /// <param name="dbObject"></param>47     /// <param name="key"></param>48     /// <returns></returns>49     DataTable SelectSql(object dbObject, string key);50 51     /// <summary>52     /// 53     /// </summary>54     /// <typeparam name="T"></typeparam>55     /// <param name="dbObject"></param>56     /// <param name="key"></param>57     /// <returns></returns>58     List<T> SelectSql<T>(object dbObject, string key) where T : new();59 60     /// <summary>61     /// 创建 Update sql 62     /// </summary>63     /// <param name="dbObject">实例对象</param>64     /// <returns></returns>65     string UpdateSql(object dbObject, string key);66 67     /// <summary>68     /// 创建 Delete sql 69     /// </summary>70     /// <param name="dbObject">实例对象</param>71     /// <returns></returns>72     string DeleteSql(object dbObject, string key);73 74     /// <summary>75     /// 76     /// </summary>77     /// <param name="dbObject">实例对象</param>78     /// <returns></returns>79     void GetProperty(object dbObject, ref DBCache cache);80 81   }

然后通过脚本对象实现对接口的实现,如果不是很清楚我的脚本的机制的可以看看我之前的文章,一步一步开发Game服务器(三)加载脚本和服务器热更新(二)完整版  

 1   /// <summary> 2   ///  3   /// </summary> 4   public class CreateSqliteScript : ICreateSqlScript 5   { 6  7     const string NameKey = "Sqlite"; 8     const string exts = ".dll,.exe,"; 9  10     public void InitTables(string key) 11     { 12       if (!NameKey.Equals(key)) 13       { 14         return; 15       } 16       var asss = AppDomain.CurrentDomain.GetAssemblies(); 17       foreach (var item in asss) 18       { 19         try 20         { 21           if (!item.ManifestModule.IsResource()) 22           { 23             if (item.ManifestModule.FullyQualifiedName.ToLower().EndsWith(".exe") 24               || item.ManifestModule.FullyQualifiedName.ToLower().EndsWith(".dll")) 25             { 26               try 27               { 28                 //获取加载的所有对象模型 29                 Type[] instances = item.GetExportedTypes(); 30                 foreach (var itemType in instances) 31                 { 32                   if (!itemType.IsClass || itemType.IsAbstract) 33                   { 34                     continue; 35                   } 36                   if (itemType.IsDefined(typeof(EntityAttribute), false)) 37                   { 38                     //生成实例 39                     object obj = item.CreateInstance(itemType.FullName); 40                     CreateTableSql(obj, key); 41                   } 42                 } 43               } 44               catch (Exception ex) 45               { 46                 Logger.Error("初始化表处理错误", ex); 47               } 48             } 49           } 50         } 51         catch (Exception ex) 52         { 53           Logger.Error("初始化表处理错误", ex); 54         } 55       } 56     } 57  58     #region public string CreateTableSql(object dbObject, string key) 59     public string CreateTableSql(object dbObject, string key) 60     { 61       if (!NameKey.Equals(key)) 62       { 63         return null; 64       } 65       DBCache cache = new DBCache(); 66       this.GetProperty(dbObject, ref cache); 67       StringBuilder builder = new StringBuilder(); 68       if (cache != null) 69       { 70         //builder.AppendLine("--如果表不存在那么创建表"); 71         //builder.AppendLine("  begin"); 72         builder.AppendLine().Append("    CREATE TABLE if not exists ").Append(cache.TableName).AppendLine(" ("); 73         bool isdouhao = false; 74         for (int i = 0; i < cache.Columns.Count; i++) 75         { 76           var item = cache.Columns[i]; 77           if (!item.IsTemp) 78           { 79             if (isdouhao) builder.AppendLine(",");             80             builder.Append("      ").Append(item.DBName).Append(" "); 81             if (item.IsP)//主键 82             { 83               builder.Append("INTEGER PRIMARY KEY"); 84               if (item.IsAuto) 85               { 86                 //自增 87                 builder.Append(" AUTOINCREMENT"); 88               } 89             } 90             else if (item.IsAuto) 91             { 92               //自增 93               builder.Append("INTEGER AUTOINCREMENT"); 94             } 95             else { builder.Append(item.DBType).Append("").Append("(").Append(item.Length).Append(")"); } 96             if (item.IsNotNull) { builder.Append(" NOT NULL"); } 97             else { builder.Append(" NULL"); } 98             isdouhao = true; 99           }100         }101         builder.AppendLine(")");102         //builder.AppendLine("  end");103 104         //builder.AppendLine("  begin");105         //builder.AppendLine("    --如果表存在检查字段");106         //for (int i = 0; i < cache.Columns.Count; i++)107         //{108         //  var item = cache.Columns[i];109         //  if (!item.IsTemp)110         //  {111         //    builder.Append("alter table ").Append(cache.TableName).Append(" add ").Append(item.Name).Append(" ");112         //    if (item.IsP)//主键113         //    {114         //      builder.Append("INTEGER PRIMARY KEY");115         //      if (item.IsAuto)116         //      {117         //        //自增118         //        builder.Append(" AUTOINCREMENT");119         //      }120         //    }121         //    else if (item.IsAuto)122         //    {123         //      //自增124         //      builder.Append("INTEGER AUTOINCREMENT");125         //    }126         //    else127         //    {128         //      builder.Append(item.DBType).Append("").Append("(").Append(item.Length).Append(")");129         //    }130 131         //    if (item.IsNotNull)132         //    {133         //      builder.Append(" NOT NULL");134         //    }135         //    else136         //    {137         //      builder.Append(" NULL");138         //    }139         //    builder.AppendLine(";");140         //  }141         //}142         //builder.AppendLine("  end");143       }144       string createsql = builder.ToString();145       Logger.Info(createsql);146       try147       {148         Logger.Info("创建表完成 " + Sz.DBPool.Helpers.SqliteHelper.ExecuteNonQuery(createsql));149       }150       catch (Exception e)151       {152         Logger.Error("创建表错误:" + createsql, e);153       }154 155       return builder.ToString();156     }157     #endregion158 159     #region public string InsterIntoSql(object dbObject, string key)160     public int InsterIntoSql(object dbObject, string key)161     {162       if (!NameKey.Equals(key))163       {164         return -1;165       }166       DBCache cache = new DBCache();167       this.GetProperty(dbObject, ref cache);168       StringBuilder builder = new StringBuilder();169       if (cache != null)170       {171         bool isdouhao = false;172         builder.Append("insert into ").Append(cache.TableName).Append(" (");173         for (int i = 0; i < cache.Columns.Count; i++)174         {175           var item = cache.Columns[i];176           if (!item.IsTemp && !item.IsP)177           {178             if (isdouhao) { builder.Append(","); }179             builder.Append(item.DBName);180             isdouhao = true;181           }182         }183         builder.Append(") values (");184         isdouhao = false;185         for (int i = 0; i < cache.Columns.Count; i++)186         {187           var item = cache.Columns[i];188           if (!item.IsTemp && !item.IsP)189           {190             if (isdouhao) { builder.Append(","); }191             builder.Append(item.Value);192             isdouhao = true;193           }194         }195         builder.AppendLine("); ");196         builder.AppendLine(" select last_insert_rowid() ");197       }198       string instersql = builder.ToString();199       Logger.Info(instersql);200       try201       {202         int ret = Convert.ToInt32(Sz.DBPool.Helpers.SqliteHelper.ExecuteScalar(instersql));203         if (ret > 0)204         {205           Logger.Info("新增数据成功");206           return ret;207         }208       }209       catch (Exception e)210       {211         Logger.Error("添加数据出错:" + instersql, e);212       }213       Logger.Info("新增数据成功");214       return 0;215     }216     #endregion217 218     #region public string UpdateSql(object dbObject, string key)219 220     public string UpdateSql(object dbObject, string key)221     {222       if (!NameKey.Equals(key))223       {224         return null;225       }226       DBCache cache = new DBCache();227       this.GetProperty(dbObject, ref cache);228       StringBuilder builder = new StringBuilder();229       if (cache != null)230       {231         builder.Append("update ").Append(cache.TableName).Append(" set ");232         bool isdouhao = false;233         for (int i = 0; i < cache.Columns.Count; i++)234         {235           var item = cache.Columns[i];236           if (!item.IsTemp && !item.IsP)237           {238             if (isdouhao) builder.Append(",");239             builder.Append(item.DBName).Append(" = ").Append(item.Value);240             isdouhao = true;241           }242         }243         builder.Append(" where ");244         for (int i = 0; i < cache.Columns.Count; i++)245         {246           var item = cache.Columns[i];247           if (item.IsP)248           {249             builder.Append(item.DBName).Append(" = ").Append(item.Value);250             break;251           }252         }253       }254       string updatesql = builder.ToString();255       Logger.Info(updatesql);256       try257       {258         int ret = Sz.DBPool.Helpers.SqliteHelper.ExecuteNonQuery(updatesql);259         if (ret > 0)260         {261           Logger.Info("更新数据成功");262           return "更新成功";263         }264       }265       catch (Exception e)266       {267         Logger.Error("更新数据出错:" + updatesql, e);268       }269       Logger.Info("更新数据失败");270       return "更新数据失败";271     }272     #endregion273 274     #region public string DeleteSql(object dbObject, string key)275     public string DeleteSql(object dbObject, string key)276     {277       if (!NameKey.Equals(key))278       {279         return null;280       }281       DBCache cache = new DBCache();282       this.GetProperty(dbObject, ref cache);283       StringBuilder builder = new StringBuilder();284       if (cache != null)285       {286         builder.Append("delete from ").Append(cache.TableName).Append(" where ");287         bool isdouhao = false;288         for (int i = 0; i < cache.Columns.Count; i++)289         {290           var item = cache.Columns[i];291           if (!item.IsTemp)292           {293             if (!"0".Equals(item.Value) && !"''".Equals(item.Value) && !string.IsNullOrWhiteSpace(item.Value))294             {295               if (isdouhao) { builder.Append (" and "); }296               builder.Append(item.DBName).Append(" = ").Append(item.Value);297               isdouhao = true;298             }299           }300         }301       }302       string deletesql = builder.ToString();303       Logger.Info(deletesql);304       try305       {306         int ret = Sz.DBPool.Helpers.SqliteHelper.ExecuteNonQuery(deletesql);307         if (ret > 0)308         {309           return "删除成功";310         }311       }312       catch (Exception)313       {314         return "删除失败";315       }316       return "删除失败";317     }318     #endregion319 320     #region public void GetProperty(object dbObject, ref DBCache cache)321     public void GetProperty(object dbObject, ref DBCache cache)322     {323       Type @type = dbObject.GetType();324       if (@type.IsClass)325       {326         //if (@type.Namespace != null && @type.Namespace.StartsWith("Sz.DBPool"))327         {328           if (cache == null)329           {330             cache = new DBCache();331             cache.Instance = @type;332           }333           if (@type.IsDefined(typeof(EntityAttribute), false))334           {335             object[] entityDBs = @type.GetCustomAttributes(typeof(EntityAttribute), false);336             if (entityDBs.Length > 0)337             {338               EntityAttribute entity = (EntityAttribute)entityDBs[0];339               if (!string.IsNullOrWhiteSpace(entity.Name))340               {341                 cache.TableName = entity.Name;342               }343             }344           }345           if (string.IsNullOrWhiteSpace(cache.TableName))346           {347             cache.TableName = @type.Name;348           }349 350           var members = @type.GetProperties(BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance);351 352           foreach (var propertyInfo in members)353           {354             //Console.WriteLine(@type.FullName + " " + propertyInfo.PropertyType.FullName + " " + propertyInfo.Name);355             if (@type.FullName.Contains("System")) { continue; }356             object[] columnDBs = propertyInfo.GetCustomAttributes(typeof(ColumnAttribute), false);357             object value = propertyInfo.GetValue(dbObject, null);358             ColumnAttribute column = null;359             if (columnDBs.Length > 0) { column = (ColumnAttribute)columnDBs[0]; }360             else { column = new ColumnAttribute(); }361             bool iscontinue = false;362             if (string.IsNullOrWhiteSpace(column.DBType))363             {364               switch (propertyInfo.PropertyType.Name)365               {366                 case "Bool":367                   column.DBType = "bit";368                   break;369                 case "Byte":370                   column.DBType = "INTEGER";371                   break;372                 case "Int16":373                   column.DBType = "INTEGER";374                   break;375                 case "Int32":376                   column.DBType = "INTEGER";377                   break;378                 case "Int64":379                   column.DBType = "INTEGER";380                   break;381                 case "String":382                   if (column.Length == 0)383                   {384                     column.Length = 255;385                   }386                   column.DBType = "TEXT";387                   break;388                 default:389                   GetProperty(value, ref cache);390                   iscontinue = true;391                   break;392               }393             }394             else395             {396               GetProperty(value, ref cache);397               iscontinue = true;398             }399 400             if (iscontinue) { continue; }401 402             switch (propertyInfo.PropertyType.Name)403             {404               case "Bool":405                 column.Length = 1;406                 column.Value = value.ToString();407                 break;408               case "Byte":409                 column.Length = 1;410                 column.Value = value.ToString();411                 break;412               case "Int16":413                 column.Length = 2;414                 column.Value = value.ToString();415                 break;416               case "Int32":417                 column.Length = 4;418                 column.Value = value.ToString();419                 break;420               case "Int64":421                 column.Length = 8;422                 column.Value = value.ToString();423                 break;424               case "String":425                 if (column.Length == 0)426                 {427                   column.Length = 255;428                 }429 430                 if (value == null)431                 {432                   if (column.IsNotNull)433                   {434                     column.Value = null;435                   }436                   else437                   {438                     column.Value = "''";439                   }440                 }441                 else442                 {443                   column.Value = "'" + value + "'";444                 }445                 break;446             }447 448             column.Name = propertyInfo.Name;449 450             if (string.IsNullOrWhiteSpace(column.DBName))451             {452               column.DBName = propertyInfo.Name;453             }454 455             if (column.IsP)456             {457               cache.ColumnPs.Add(column);458             }459             cache.Columns.Add(column);460           }461         }462       }463     }464     #endregion465 466 467     public string UpdateTableSql(object dbObject, string key)468     {469       if (!NameKey.Equals(key))470       {471         return null;472       }473       return null;474     }475 476     public string DelTableSql(object dbObject, string key)477     {478       if (!NameKey.Equals(key))479       {480         return null;481       }482       return null;483     }484 485     #region public DataTable SelectSql(object dbObject, string key)486     public DataTable SelectSql(object dbObject, string key)487     {488       if (!NameKey.Equals(key))489       {490         return null;491       }492       DBCache cache = new DBCache();493       this.GetProperty(dbObject, ref cache);494       StringBuilder builder = new StringBuilder();495       if (cache != null)496       {497         bool isdouhao = false;498         string wheresql = "";499 500         builder.Append("Select * from ").Append(cache.TableName);501         for (int i = 0; i < cache.Columns.Count; i++)502         {503           var item = cache.Columns[i];504           if (!item.IsTemp)505           {506             if (!"0".Equals(item.Value) && !"''".Equals(item.Value) && !string.IsNullOrWhiteSpace(item.Value))507             {508               if (isdouhao) { wheresql += (" and "); }509               wheresql += item.DBName + (" = ") + (item.Value);510               isdouhao = true;511             }512           }513         }514         if (!string.IsNullOrWhiteSpace(wheresql))515         {516           builder.Append(" where ").Append(wheresql);517         }518         for (int i = 0; i < cache.Columns.Count; i++)519         {520           var item = cache.Columns[i];521           if (item.IsP)522           {523             builder.Append(" order by ").Append(item.DBName).Append(" desc ");524             break;525           }526         }527       }528       string selectSql = builder.ToString();529       Logger.Info(selectSql);530 531       try532       {533         DataTable table = Sz.DBPool.Helpers.SqliteHelper.ExecuteQuery(selectSql);534 535         return table;536       }537       catch (Exception e)538       {539         Logger.Error("查询数据库错误:" + selectSql, e);540       }541       return null;542     }543     #endregion544 545     #region public List<T> SelectSql<T>(object dbObject, string key) where T : new()546     public List<T> SelectSql<T>(object dbObject, string key) where T : new()547     {548       if (!NameKey.Equals(key))549       {550         return null;551       }552       List<T> ts = new List<T>();553       DataTable table = this.SelectSql(dbObject, key);554       if (table != null)555       {556         DBCache cache = new DBCache();557         this.GetProperty(dbObject, ref cache);558         foreach (DataRow item in table.Rows)559         {560           T t = new T();561           for (int i = 0; i < cache.Columns.Count; i++)562           {563             var column = cache.Columns[i];564             if (!column.IsTemp)//非临时的565             {566               object columnValue = item[column.DBName];567               //反射568               PropertyInfo info = t.GetType().GetProperty(column.Name);569               //赋值570               info.SetValue(t, Convert.ChangeType(columnValue, info.PropertyType), null);571             }572           }573           ts.Add(t);574         }575       }576       return ts;577     }578     #endregion579 580   }

 

这个脚本针对sqlite实现了数据库的映射,数据的插入,更新,删除,读取,读取反射加载实体类集合等功能

并且这段代码是通过了一个小在线运行项目的完整测试的。由于是部署客户内网运行,所以不方便提供给各位测试了。

接下来按照惯例看看执行效果,

 1 static void Main(string[] args) 2     { 3       var dbconnect = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnect"]; 4  5       Helpers.SqliteHelper.SetConnectionString(dbconnect.ConnectionString); 6       ScriptPool.ScriptManager.Instance.LoadCSharpFile(new string[] { @"..\..\..\Sz.DBPool.Scripts\" }); 7       var temps = Sz.ScriptPool.ScriptManager.Instance.GetInstances<ICreateSqlScript>(); 8       DBClassB db = new DBClassB(); 9       System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();10       watch.Start();11       //Helpers.SqliteHelper.Transaction();12       //for (int i = 0; i < 2000; i++)13       {14 15         foreach (var item in temps)16         {17           try18           {19             string createsql = item.CreateTableSql(db, "Sqlite");20             if (createsql == null)21             {22               continue;23             }24             item.InsterIntoSql(db, "Sqlite");25             item.SelectSql(db, "Sqlite");26           }27           catch (Exception e)28           {29             Logger.Debug("dd", e);30           }31         }32       }33       //Helpers.SqliteHelper.Commit();34       watch.Stop();35       Logger.Debug(watch.ElapsedMilliseconds + "");36 37       Console.ReadLine();38     }

创建数据库表返回值为-1的原因是因为已经创建过表了。

看到这里也许很多园友会喷,你这有意义嘛?有意思嘛?其实我觉得存在即合理,只要你需要就有意义,如果不需要就没有意义。

就想你一个门户网站根本不需要登录的,结果你非要做一个登录,那就完全没意思,所以不需要喷。

当然这个功能要写强大了肯定需要更多的人力和时间。也希望有帮助的园友,如果愿意可以一起开发维护这个东西。效率的话,看控制吧。控制得好就非常高的效率。.

如果愿意的园友,回复留言,我可以提供源码或者svn一起维护,mysql,sqlserver等版本。