我的EF功能

由来

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

很是方便，

package com.game.po.log;

import com.game.engine.utils.Config;
import com.game.po.player.Role;
import com.game.structs.player.Player;
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.Id;
import javax.persistence.MappedSuperclass;

/**
 *
 * @author Vicky
 * @mail eclipser@163.com
 * @phone 13618074943
 */
@MappedSuperclass
public abstract class BaseLog implements Serializable {
    
    private static final long serialVersionUID = 1L;
    
    @Id
    @GeneratedValue(strategy = GenerationType.AUTO)
    protected long id;

    // 所属用户ID
    @Column
    private long userid;
    
    // 所属用户名称
    @Column
    private String username;
    
    // 创建的服务器ID
    @Column
    private int serverid;
    
    // 服务器名称
    @Column(length = 64)
    private String servername;
    
    // 渠道名称
    @Column(length = 64)
    private String serverweb;
    
    // 角色ID
    @Column
    private long playerid;
    
    // 角色名称
    @Column(length = 64)
    private String playername;
    
    // 登录的服务器IP
    @Column(length = 64)
    private String loginIP;
    
    // 日志创建的服务器ID
    @Column(nullable = false)
    private int createServerID;
    
    // 日志创建的服务器名称
    @Column(nullable = false, length = 64)
    private String createServerName;
    
    // 日志创建的服务器渠道
    @Column(nullable = false, length = 64)
    private String createServerWeb;
    
    // 创建时间
    @Column(nullable = false)
    private long createTime = System.currentTimeMillis();
    
    public BaseLog() {
    }

    public BaseLog(Player player) {
        // 初始化日志字段信息
        if (player != null) {
            this.userid = player.getUserId();
            this.username = player.getUsername();
            this.serverid = player.getServerId();
            this.servername = player.getServername();
            this.serverweb = player.getServerweb();
            this.loginIP = player.getLoginIP();
            this.playerid = player.getId();
            this.playername = player.getName();
        }
        this.createServerID = Config.serverID;
        this.createServerName = Config.ServerName;
        this.createServerWeb = Config.ServerWeb;
    }

    public BaseLog(Role role) {
        if (role != null) {
            this.userid = role.getUserid();
            this.username = role.getUsername();
            this.serverid = role.getServerid();
            this.servername = role.getServername();
            this.serverweb = role.getServerweb();
            this.loginIP = role.getLoginIP();
            this.playerid = role.getPid();
            this.playername = role.getName();
        }
        
        this.createServerID = Config.serverID;
        this.createServerName = Config.ServerName;
        this.createServerWeb = Config.ServerWeb;
    }

    public long getId() {
        return id;
    }

//    public void setId(long id) {
//        this.id = id;
//    }

    public long getUserid() {
        return userid;
    }

    public void setUserid(long userid) {
        this.userid = userid;
    }

    public String getUsername() {
        return username;
    }

    public void setUsername(String username) {
        this.username = username;
    }

    public int getServerid() {
        return serverid;
    }

    public void setServerid(int serverid) {
        this.serverid = serverid;
    }

    public String getServername() {
        return servername;
    }

    public void setServername(String servername) {
        this.servername = servername;
    }

    public String getServerweb() {
        return serverweb;
    }

    public void setServerweb(String serverweb) {
        this.serverweb = serverweb;
    }

    public String getLoginIP() {
        return loginIP;
    }

    public void setLoginIP(String loginIP) {
        this.loginIP = loginIP;
    }

    public long getPlayerid() {
        return playerid;
    }

    public void setPlayerid(long playerid) {
        this.playerid = playerid;
    }

    public String getPlayername() {
        return playername;
    }

    public void setPlayername(String playername) {
        this.playername = playername;
    }

    public int getCreateServerID() {
        return createServerID;
    }

    public void setCreateServerID(int createServerID) {
        this.createServerID = createServerID;
    }

    public String getCreateServerName() {
        return createServerName;
    }

    public void setCreateServerName(String createServerName) {
        this.createServerName = createServerName;
    }

    public String getCreateServerWeb() {
        return createServerWeb;
    }

    public void setCreateServerWeb(String createServerWeb) {
        this.createServerWeb = createServerWeb;
    }

    public long getCreateTime() {
        return createTime;
    }

    public void setCreateTime(long createTime) {
        this.createTime = createTime;
    }

    @Override
    public int hashCode() {
        int hash = 7;
        hash = 23 * hash + (int) (this.id ^ (this.id >>> 32));
        return hash;
    }

    @Override
    public boolean equals(Object obj) {
        if (obj == null) {
            return false;
        }
        if (getClass() != obj.getClass()) {
            return false;
        }
        final BaseLog other = (BaseLog) obj;
        if (this.id != other.id) {
            return false;
        }
        return true;
    }
    
}

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

 

我为什么需要这样呢？

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

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

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

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

那么我只能自己动手了。

设计思路

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

废话不多说

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

    /// <summary>
    /// 数据库关联类标识符
    /// </summary>
    public class EntityAttribute : Attribute
    {

        public string Name { get; set; }

        public string Description { get; set; }
    }

实体类标识

    /// <summary>
    /// 属性字段
    /// </summary>
    public class ColumnAttribute : Attribute
    {
        public ColumnAttribute()
        {

        }
        /// <summary>
        /// 数据库对应的字段名称
        /// </summary>
        public string DBName { get; set; }
        /// <summary>
        /// 原始字段名
        /// </summary>
        public string Name { get; set; }
        /// <summary>
        /// 数据类型
        /// </summary>
        public string DBType { get; set; }
        /// <summary>
        /// 长度
        /// </summary>
        public int Length { get; set; }

        /// <summary>
        /// 是否是数据库主键
        /// </summary>
        public bool IsP { get; set; }

        /// <summary>
        /// 是否允许为null
        /// </summary>
        public bool IsNotNull { get; set; }

        /// <summary>
        /// 自增
        /// </summary>
        public bool IsAuto { get; set; }

        /// <summary>
        /// 将会被忽略的属性
        /// </summary>
        public bool IsTemp { get; set; }
        /// <summary>
        /// 描述
        /// </summary>
        public string Description { get; set; }

        /// <summary>
        /// 记录字段的参数
        /// </summary>
        public string Value { get; set; }
    }

属性字段标识

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

    public class DBCache
    {
        public DBCache()
        {
            ColumnPs = new List<ColumnAttribute>();
            Columns = new List<ColumnAttribute>();
        }
        public Type Instance { get; set; }

        public string TableName { get; set; }
        /// <summary>
        /// 主键列
        /// </summary>
        public List<ColumnAttribute> ColumnPs { get; set; }
        /// <summary>
        /// 所有列
        /// </summary>
        public List<ColumnAttribute> Columns { get; set; }

    }

 

满足基本所有需求了。

    /// <summary>
    /// 
    /// </summary>
    [EntityAttribute(Name = "user")]
    public class DBClassB
    {
        /// <summary>
        /// 
        /// </summary>
        [ColumnAttribute(DBName = "ID", Length = 4, IsP = true, IsAuto = true)]
        public int ID { get; set; }
        /// <summary>
        /// 
        /// </summary>
        public string Name { get; set; }

        [ColumnAttribute]
        public byte Sex { get; set; }

        [ColumnAttribute(IsTemp = true)]
        public string TempName { get; set; }

    }

测试类。

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

功能如何实现呢？

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

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

    /// <summary>
    /// sql语句生成器
    /// </summary>
    public interface ICreateSqlScript : Sz.ScriptPool.IBaseScript
    {

        /// <summary>
        /// 想要实现自动创建表实体类必须实现 添加 EntityAttribute 特性
        /// </summary>
        /// <param name="key"></param>
        void InitTables(string key);

        /// <summary>
        /// 创建表和更新表结构
        /// </summary>
        /// <param name="dbObject">@实例对象</param>
        /// <returns></returns>
        string CreateTableSql(object dbObject, string key);

        /// <summary>
        /// 修改表结构
        /// </summary>
        /// <param name="dbObject"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        string UpdateTableSql(object dbObject, string key);

        /// <summary>
        /// 删除表
        /// </summary>
        /// <param name="dbObject"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        string DelTableSql(object dbObject, string key);

        /// <summary>
        /// 创建 Inster sql 
        /// </summary>
        /// <param name="dbObject">实例对象</param>
        /// <returns></returns>
        int InsterIntoSql(object dbObject, string key);

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbObject"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        DataTable SelectSql(object dbObject, string key);

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="dbObject"></param>
        /// <param name="key"></param>
        /// <returns></returns>
        List<T> SelectSql<T>(object dbObject, string key) where T : new();

        /// <summary>
        /// 创建 Update sql 
        /// </summary>
        /// <param name="dbObject">实例对象</param>
        /// <returns></returns>
        string UpdateSql(object dbObject, string key);

        /// <summary>
        /// 创建 Delete sql 
        /// </summary>
        /// <param name="dbObject">实例对象</param>
        /// <returns></returns>
        string DeleteSql(object dbObject, string key);

        /// <summary>
        /// 
        /// </summary>
        /// <param name="dbObject">实例对象</param>
        /// <returns></returns>
        void GetProperty(object dbObject, ref DBCache cache);

    }

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

    /// <summary>
    /// 
    /// </summary>
    public class CreateSqliteScript : ICreateSqlScript
    {

        const string NameKey = "Sqlite";
        const string exts = ".dll,.exe,";

        public void InitTables(string key)
        {
            if (!NameKey.Equals(key))
            {
                return;
            }
            var asss = AppDomain.CurrentDomain.GetAssemblies();
            foreach (var item in asss)
            {
                try
                {
                    if (!item.ManifestModule.IsResource())
                    {
                        if (item.ManifestModule.FullyQualifiedName.ToLower().EndsWith(".exe")
                            || item.ManifestModule.FullyQualifiedName.ToLower().EndsWith(".dll"))
                        {
                            try
                            {
                                //获取加载的所有对象模型
                                Type[] instances = item.GetExportedTypes();
                                foreach (var itemType in instances)
                                {
                                    if (!itemType.IsClass || itemType.IsAbstract)
                                    {
                                        continue;
                                    }
                                    if (itemType.IsDefined(typeof(EntityAttribute), false))
                                    {
                                        //生成实例
                                        object obj = item.CreateInstance(itemType.FullName);
                                        CreateTableSql(obj, key);
                                    }
                                }
                            }
                            catch (Exception ex)
                            {
                                Logger.Error("初始化表处理错误", ex);
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Logger.Error("初始化表处理错误", ex);
                }
            }
        }

        #region public string CreateTableSql(object dbObject, string key)
        public string CreateTableSql(object dbObject, string key)
        {
            if (!NameKey.Equals(key))
            {
                return null;
            }
            DBCache cache = new DBCache();
            this.GetProperty(dbObject, ref cache);
            StringBuilder builder = new StringBuilder();
            if (cache != null)
            {
                //builder.AppendLine("--如果表不存在那么创建表");
                //builder.AppendLine("    begin");
                builder.AppendLine().Append("        CREATE TABLE  if not exists ").Append(cache.TableName).AppendLine(" (");
                bool isdouhao = false;
                for (int i = 0; i < cache.Columns.Count; i++)
                {
                    var item = cache.Columns[i];
                    if (!item.IsTemp)
                    {
                        if (isdouhao) builder.AppendLine(",");                        
                        builder.Append("            ").Append(item.DBName).Append(" ");
                        if (item.IsP)//主键
                        {
                            builder.Append("INTEGER PRIMARY KEY");
                            if (item.IsAuto)
                            {
                                //自增
                                builder.Append(" AUTOINCREMENT");
                            }
                        }
                        else if (item.IsAuto)
                        {
                            //自增
                            builder.Append("INTEGER AUTOINCREMENT");
                        }
                        else { builder.Append(item.DBType).Append("").Append("(").Append(item.Length).Append(")"); }
                        if (item.IsNotNull) { builder.Append(" NOT NULL"); }
                        else { builder.Append(" NULL"); }
                        isdouhao = true;
                    }
                }
                builder.AppendLine(")");
                //builder.AppendLine("    end");

                //builder.AppendLine("    begin");
                //builder.AppendLine("        --如果表存在检查字段");
                //for (int i = 0; i < cache.Columns.Count; i++)
                //{
                //    var item = cache.Columns[i];
                //    if (!item.IsTemp)
                //    {
                //        builder.Append("alter table ").Append(cache.TableName).Append(" add ").Append(item.Name).Append(" ");
                //        if (item.IsP)//主键
                //        {
                //            builder.Append("INTEGER PRIMARY KEY");
                //            if (item.IsAuto)
                //            {
                //                //自增
                //                builder.Append(" AUTOINCREMENT");
                //            }
                //        }
                //        else if (item.IsAuto)
                //        {
                //            //自增
                //            builder.Append("INTEGER AUTOINCREMENT");
                //        }
                //        else
                //        {
                //            builder.Append(item.DBType).Append("").Append("(").Append(item.Length).Append(")");
                //        }

                //        if (item.IsNotNull)
                //        {
                //            builder.Append(" NOT NULL");
                //        }
                //        else
                //        {
                //            builder.Append(" NULL");
                //        }
                //        builder.AppendLine(";");
                //    }
                //}
                //builder.AppendLine("    end");
            }
            string createsql = builder.ToString();
            Logger.Info(createsql);
            try
            {
                Logger.Info("创建表完成 " + Sz.DBPool.Helpers.SqliteHelper.ExecuteNonQuery(createsql));
            }
            catch (Exception e)
            {
                Logger.Error("创建表错误：" + createsql, e);
            }

            return builder.ToString();
        }
        #endregion

        #region public string InsterIntoSql(object dbObject, string key)
        public int InsterIntoSql(object dbObject, string key)
        {
            if (!NameKey.Equals(key))
            {
                return -1;
            }
            DBCache cache = new DBCache();
            this.GetProperty(dbObject, ref cache);
            StringBuilder builder = new StringBuilder();
            if (cache != null)
            {
                bool isdouhao = false;
                builder.Append("insert into ").Append(cache.TableName).Append(" (");
                for (int i = 0; i < cache.Columns.Count; i++)
                {
                    var item = cache.Columns[i];
                    if (!item.IsTemp && !item.IsP)
                    {
                        if (isdouhao) { builder.Append(","); }
                        builder.Append(item.DBName);
                        isdouhao = true;
                    }
                }
                builder.Append(") values (");
                isdouhao = false;
                for (int i = 0; i < cache.Columns.Count; i++)
                {
                    var item = cache.Columns[i];
                    if (!item.IsTemp && !item.IsP)
                    {
                        if (isdouhao) { builder.Append(","); }
                        builder.Append(item.Value);
                        isdouhao = true;
                    }
                }
                builder.AppendLine("); ");
                builder.AppendLine(" select last_insert_rowid() ");
            }
            string instersql = builder.ToString();
            Logger.Info(instersql);
            try
            {
                int ret = Convert.ToInt32(Sz.DBPool.Helpers.SqliteHelper.ExecuteScalar(instersql));
                if (ret > 0)
                {
                    Logger.Info("新增数据成功");
                    return ret;
                }
            }
            catch (Exception e)
            {
                Logger.Error("添加数据出错：" + instersql, e);
            }
            Logger.Info("新增数据成功");
            return 0;
        }
        #endregion

        #region public string UpdateSql(object dbObject, string key)

        public string UpdateSql(object dbObject, string key)
        {
            if (!NameKey.Equals(key))
            {
                return null;
            }
            DBCache cache = new DBCache();
            this.GetProperty(dbObject, ref cache);
            StringBuilder builder = new StringBuilder();
            if (cache != null)
            {
                builder.Append("update ").Append(cache.TableName).Append(" set ");
                bool isdouhao = false;
                for (int i = 0; i < cache.Columns.Count; i++)
                {
                    var item = cache.Columns[i];
                    if (!item.IsTemp && !item.IsP)
                    {
                        if (isdouhao) builder.Append(",");
                        builder.Append(item.DBName).Append(" = ").Append(item.Value);
                        isdouhao = true;
                    }
                }
                builder.Append(" where ");
                for (int i = 0; i < cache.Columns.Count; i++)
                {
                    var item = cache.Columns[i];
                    if (item.IsP)
                    {
                        builder.Append(item.DBName).Append(" = ").Append(item.Value);
                        break;
                    }
                }
            }
            string updatesql = builder.ToString();
            Logger.Info(updatesql);
            try
            {
                int ret = Sz.DBPool.Helpers.SqliteHelper.ExecuteNonQuery(updatesql);
                if (ret > 0)
                {
                    Logger.Info("更新数据成功");
                    return "更新成功";
                }
            }
            catch (Exception e)
            {
                Logger.Error("更新数据出错：" + updatesql, e);
            }
            Logger.Info("更新数据失败");
            return "更新数据失败";
        }
        #endregion

        #region public string DeleteSql(object dbObject, string key)
        public string DeleteSql(object dbObject, string key)
        {
            if (!NameKey.Equals(key))
            {
                return null;
            }
            DBCache cache = new DBCache();
            this.GetProperty(dbObject, ref cache);
            StringBuilder builder = new StringBuilder();
            if (cache != null)
            {
                builder.Append("delete from ").Append(cache.TableName).Append(" where ");
                bool isdouhao = false;
                for (int i = 0; i < cache.Columns.Count; i++)
                {
                    var item = cache.Columns[i];
                    if (!item.IsTemp)
                    {
                        if (!"0".Equals(item.Value) && !"''".Equals(item.Value) && !string.IsNullOrWhiteSpace(item.Value))
                        {
                            if (isdouhao) { builder.Append (" and "); }
                            builder.Append(item.DBName).Append(" = ").Append(item.Value);
                            isdouhao = true;
                        }
                    }
                }
            }
            string deletesql = builder.ToString();
            Logger.Info(deletesql);
            try
            {
                int ret = Sz.DBPool.Helpers.SqliteHelper.ExecuteNonQuery(deletesql);
                if (ret > 0)
                {
                    return "删除成功";
                }
            }
            catch (Exception)
            {
                return "删除失败";
            }
            return "删除失败";
        }
        #endregion

        #region public void GetProperty(object dbObject, ref DBCache cache)
        public void GetProperty(object dbObject, ref DBCache cache)
        {
            Type @type = dbObject.GetType();
            if (@type.IsClass)
            {
                //if (@type.Namespace != null && @type.Namespace.StartsWith("Sz.DBPool"))
                {
                    if (cache == null)
                    {
                        cache = new DBCache();
                        cache.Instance = @type;
                    }
                    if (@type.IsDefined(typeof(EntityAttribute), false))
                    {
                        object[] entityDBs = @type.GetCustomAttributes(typeof(EntityAttribute), false);
                        if (entityDBs.Length > 0)
                        {
                            EntityAttribute entity = (EntityAttribute)entityDBs[0];
                            if (!string.IsNullOrWhiteSpace(entity.Name))
                            {
                                cache.TableName = entity.Name;
                            }
                        }
                    }
                    if (string.IsNullOrWhiteSpace(cache.TableName))
                    {
                        cache.TableName = @type.Name;
                    }

                    var members = @type.GetProperties(BindingFlags.NonPublic | BindingFlags.Public | BindingFlags.Instance);

                    foreach (var propertyInfo in members)
                    {
                        //Console.WriteLine(@type.FullName + "  " + propertyInfo.PropertyType.FullName + " " + propertyInfo.Name);
                        if (@type.FullName.Contains("System")) { continue; }
                        object[] columnDBs = propertyInfo.GetCustomAttributes(typeof(ColumnAttribute), false);
                        object value = propertyInfo.GetValue(dbObject, null);
                        ColumnAttribute column = null;
                        if (columnDBs.Length > 0) { column = (ColumnAttribute)columnDBs[0]; }
                        else { column = new ColumnAttribute(); }
                        bool iscontinue = false;
                        if (string.IsNullOrWhiteSpace(column.DBType))
                        {
                            switch (propertyInfo.PropertyType.Name)
                            {
                                case "Bool":
                                    column.DBType = "bit";
                                    break;
                                case "Byte":
                                    column.DBType = "INTEGER";
                                    break;
                                case "Int16":
                                    column.DBType = "INTEGER";
                                    break;
                                case "Int32":
                                    column.DBType = "INTEGER";
                                    break;
                                case "Int64":
                                    column.DBType = "INTEGER";
                                    break;
                                case "String":
                                    if (column.Length == 0)
                                    {
                                        column.Length = 255;
                                    }
                                    column.DBType = "TEXT";
                                    break;
                                default:
                                    GetProperty(value, ref cache);
                                    iscontinue = true;
                                    break;
                            }
                        }
                        else
                        {
                            GetProperty(value, ref cache);
                            iscontinue = true;
                        }

                        if (iscontinue) { continue; }

                        switch (propertyInfo.PropertyType.Name)
                        {
                            case "Bool":
                                column.Length = 1;
                                column.Value = value.ToString();
                                break;
                            case "Byte":
                                column.Length = 1;
                                column.Value = value.ToString();
                                break;
                            case "Int16":
                                column.Length = 2;
                                column.Value = value.ToString();
                                break;
                            case "Int32":
                                column.Length = 4;
                                column.Value = value.ToString();
                                break;
                            case "Int64":
                                column.Length = 8;
                                column.Value = value.ToString();
                                break;
                            case "String":
                                if (column.Length == 0)
                                {
                                    column.Length = 255;
                                }

                                if (value == null)
                                {
                                    if (column.IsNotNull)
                                    {
                                        column.Value = null;
                                    }
                                    else
                                    {
                                        column.Value = "''";
                                    }
                                }
                                else
                                {
                                    column.Value = "'" + value + "'";
                                }
                                break;
                        }

                        column.Name = propertyInfo.Name;

                        if (string.IsNullOrWhiteSpace(column.DBName))
                        {
                            column.DBName = propertyInfo.Name;
                        }

                        if (column.IsP)
                        {
                            cache.ColumnPs.Add(column);
                        }
                        cache.Columns.Add(column);
                    }
                }
            }
        }
        #endregion


        public string UpdateTableSql(object dbObject, string key)
        {
            if (!NameKey.Equals(key))
            {
                return null;
            }
            return null;
        }

        public string DelTableSql(object dbObject, string key)
        {
            if (!NameKey.Equals(key))
            {
                return null;
            }
            return null;
        }

        #region public DataTable SelectSql(object dbObject, string key)
        public DataTable SelectSql(object dbObject, string key)
        {
            if (!NameKey.Equals(key))
            {
                return null;
            }
            DBCache cache = new DBCache();
            this.GetProperty(dbObject, ref cache);
            StringBuilder builder = new StringBuilder();
            if (cache != null)
            {
                bool isdouhao = false;
                string wheresql = "";

                builder.Append("Select * from ").Append(cache.TableName);
                for (int i = 0; i < cache.Columns.Count; i++)
                {
                    var item = cache.Columns[i];
                    if (!item.IsTemp)
                    {
                        if (!"0".Equals(item.Value) && !"''".Equals(item.Value) && !string.IsNullOrWhiteSpace(item.Value))
                        {
                            if (isdouhao) { wheresql += (" and "); }
                            wheresql += item.DBName + (" = ") + (item.Value);
                            isdouhao = true;
                        }
                    }
                }
                if (!string.IsNullOrWhiteSpace(wheresql))
                {
                    builder.Append(" where ").Append(wheresql);
                }
                for (int i = 0; i < cache.Columns.Count; i++)
                {
                    var item = cache.Columns[i];
                    if (item.IsP)
                    {
                        builder.Append(" order by ").Append(item.DBName).Append(" desc ");
                        break;
                    }
                }
            }
            string selectSql = builder.ToString();
            Logger.Info(selectSql);

            try
            {
                DataTable table = Sz.DBPool.Helpers.SqliteHelper.ExecuteQuery(selectSql);

                return table;
            }
            catch (Exception e)
            {
                Logger.Error("查询数据库错误：" + selectSql, e);
            }
            return null;
        }
        #endregion

        #region public List<T> SelectSql<T>(object dbObject, string key) where T : new()
        public List<T> SelectSql<T>(object dbObject, string key) where T : new()
        {
            if (!NameKey.Equals(key))
            {
                return null;
            }
            List<T> ts = new List<T>();
            DataTable table = this.SelectSql(dbObject, key);
            if (table != null)
            {
                DBCache cache = new DBCache();
                this.GetProperty(dbObject, ref cache);
                foreach (DataRow item in table.Rows)
                {
                    T t = new T();
                    for (int i = 0; i < cache.Columns.Count; i++)
                    {
                        var column = cache.Columns[i];
                        if (!column.IsTemp)//非临时的
                        {
                            object columnValue = item[column.DBName];
                            //反射
                            PropertyInfo info = t.GetType().GetProperty(column.Name);
                            //赋值
                            info.SetValue(t, Convert.ChangeType(columnValue, info.PropertyType), null);
                        }
                    }
                    ts.Add(t);
                }
            }
            return ts;
        }
        #endregion

    }

 

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

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

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

 static void Main(string[] args)
        {
            var dbconnect = System.Configuration.ConfigurationManager.ConnectionStrings["DBConnect"];

            Helpers.SqliteHelper.SetConnectionString(dbconnect.ConnectionString);
            ScriptPool.ScriptManager.Instance.LoadCSharpFile(new string[] { @"..\..\..\Sz.DBPool.Scripts\" });
            var temps = Sz.ScriptPool.ScriptManager.Instance.GetInstances<ICreateSqlScript>();
            DBClassB db = new DBClassB();
            System.Diagnostics.Stopwatch watch = new System.Diagnostics.Stopwatch();
            watch.Start();
            //Helpers.SqliteHelper.Transaction();
            //for (int i = 0; i < 2000; i++)
            {

                foreach (var item in temps)
                {
                    try
                    {
                        string createsql = item.CreateTableSql(db, "Sqlite");
                        if (createsql == null)
                        {
                            continue;
                        }
                        item.InsterIntoSql(db, "Sqlite");
                        item.SelectSql(db, "Sqlite");
                    }
                    catch (Exception e)
                    {
                        Logger.Debug("dd", e);
                    }
                }
            }
            //Helpers.SqliteHelper.Commit();
            watch.Stop();
            Logger.Debug(watch.ElapsedMilliseconds + "");

            Console.ReadLine();
        }

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

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

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

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

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