我的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     @Override
218     public int hashCode() {
219         int hash = 7;
220         hash = 23 * hash + (int) (this.id ^ (this.id >>> 32));
221         return hash;
222     }
223 
224     @Override
225     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         /// 是否允许为null
34         /// </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                 //        else
127                 //        {
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                 //        else
136                 //        {
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             try
147             {
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         #endregion
158 
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             try
201             {
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         #endregion
217 
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             try
257             {
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         #endregion
273 
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             try
305             {
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         #endregion
319 
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                         else
395                         {
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                                     else
437                                     {
438                                         column.Value = "''";
439                                     }
440                                 }
441                                 else
442                                 {
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         #endregion
465 
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             try
532             {
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         #endregion
544 
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         #endregion
579 
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                     try
18                     {
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等版本。

 

posted on 2015-10-14 18:39  無心道(失足程序员)  阅读(1675)  评论(7编辑  收藏  举报