我的EF功能，EF功能

我的EF功能，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等版本。