由来
话说这个功能想法由来与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 [email protected] 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 }
就这样简单的写个实体类，加上注解，就完成了数据库映射配置，程序启动后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等版本。
- 4楼DownUp
- 卤煮 挺像刘强东的 不知可有奶茶
- 3楼学亮
- 这个和最初的linq to sql很相似
- 2楼金源
- 赞一个
- 1楼入秋枫叶
- 赞一个