• 简易版ORM工具 Dear


    这一篇是我写的第一篇博客,可能文笔比较差还望大家见谅!小弟还有6天就毕业了结束大学生活!打算写这么一篇算是给我3年的大学生活改个墓志铭一样的东西。

    今天写的是一个我最近写的一个简易版的数据库映射工具(ORM),相信大家对于ORM工具已经有一定的了解了,现在网络上有几个比较主流的ORM工具如nhibernate和entity framework等。我是从nhibernate开始接触ORM的,对于其简单及方便的操作也不多说了。

    我的设计还是比较简单的通过反射来实现其功能,比较耗性能,上次看见一个用领域驱动设计可以不使用反射就可以实现ORM功能。表示自己还是个小菜!

    这个工具目前已经实现了插入,修改,删除和工具主键查找对象(查询方面目前还没有非常好的思路,不想使用拼接SQL的方法,感觉HQL用起来非常不错,不过现在不会移植过来,依然表示小菜!)。使用面向接口方式来实现多数据库的操作,目前有sqlserver和oracle。

    下面是项目分层:

    DALFactory层:用于生成一个IDBOperate(对于数据库操作的接口)对象。

    DBModelAttribute层:自定义特征类用于数据库实体类。

    DBModel层:数据库实体类。

    DBUtility层:ado.net操作类库,存放SQLHelper和OracleHelper2个类。

    IDAL层:数据库操作接口及其他接口。

    OracleDAL层:oracle数据库的实际操作类库。

    SQLServerDAL层:sqlserver数据库的实际操作类库。

     考虑到反射比较耗性能所以在第一次访问的时候就生成几张表的sql语句放到字典中。

      /// <summary>
        /// 用于存放各个表的SQL语句
        /// </summary>
        public class SqlSentenceStorage : ISqlSentenceStorage
        {
    
            private static IDictionary<string, SqlSentenceData> _DataBaseIntegration = new Dictionary<string, SqlSentenceData>();
    
            public static IDictionary<string, SqlSentenceData> DataBaseIntegration
            {
                get
                {
                    if (_DataBaseIntegration == null || _DataBaseIntegration.Count == default(int))
                    {
                        ChangDicData();
                    }
                    return _DataBaseIntegration;
                }
            }
    
            private static void ChangDicData()
            {
                SqlSentenceStorage sqlSentenceStorage = new SqlSentenceStorage();
                Assembly assembly = Assembly.Load("DBModel");
                foreach (Type type in assembly.GetTypes())
                {
                    if (type.IsClass && type.IsSealed)
                    {
                        SqlSentenceData sqlData = new SqlSentenceData();
                        sqlData.InSertSql = sqlSentenceStorage.InSertSql(type);
                        sqlData.UpDateSql = sqlSentenceStorage.UpDateSql(type);
                        sqlData.DeleteSql = sqlSentenceStorage.DeleteSql(type);
                        sqlData.GetSql = sqlSentenceStorage.GetSql(type);
                        _DataBaseIntegration.Add(type.Name, sqlData);
                    }
                }
            }
    
            #region ISqlSentenceStorage 成员
    
            public string InSertSql(Type modeltype)
            {
                string inSertSql = "INSERT INTO {0}({1}) VALUES ({2})";
                string tablename = "";
                object[] members = modeltype.GetCustomAttributes(true);
                for (int i = 0; i < members.Length; i++)
                {
                    if (members[i].GetType() == typeof(SqlTableAttribute))
                    {
                        tablename = ((SqlTableAttribute)members[i]).TableName;
                    }
                }
                PropertyInfo[] proInfos = modeltype.GetProperties();
                //存放字段名
                StringBuilder content1 = new StringBuilder();
                //存放参数
                StringBuilder content2 = new StringBuilder();
                foreach (PropertyInfo info in proInfos)
                {
                    foreach (Attribute ab in Attribute.GetCustomAttributes(info))
                    {
                        if (ab.GetType() == typeof(SqlColumnAttribute))
                        {
                            SqlColumnAttribute sqlcolumn = ((SqlColumnAttribute)ab);
                            string name = sqlcolumn.ColumName;
                            content1.Append(name + ",");
                            content2.Append("@" + name + ",");
                        }
                    }
                }
                content1.Remove(content1.Length - 1, 1);
                content2.Remove(content2.Length - 1, 1);
    
                return string.Format(inSertSql, tablename, content1, content2);
            }
    
            public string DeleteSql(Type modeltype)
            {
                string inSertSql = "DELETE FROM {0} WHERE 1=1{1}";
                string tablename = "";
                object[] members = modeltype.GetCustomAttributes(true);
                for (int i = 0; i < members.Length; i++)
                {
                    if (members[i].GetType() == typeof(SqlTableAttribute))
                    {
                        tablename = ((SqlTableAttribute)members[i]).TableName;
                    }
                }
                PropertyInfo[] proInfos = modeltype.GetProperties();
                //条件
                StringBuilder content1 = new StringBuilder();
                foreach (PropertyInfo info in proInfos)
                {
                    foreach (Attribute ab in Attribute.GetCustomAttributes(info))
                    {
                        if (ab.GetType() == typeof(SqlColumnAttribute))
                        {
                            SqlColumnAttribute sqlcolumn = ((SqlColumnAttribute)ab);
                            if (sqlcolumn.IsPrimaryKey)
                            {
                                content1.Append(" and " + sqlcolumn.ColumName + "=@" + sqlcolumn.ColumName);
                            }
                        }
                    }
                }
    
                return string.Format(inSertSql, tablename, content1);
            }
    
            public string UpDateSql(Type modeltype)
            {
                string inSertSql = "UPDATE {0} SET {1} WHERE 1=1{2}";
                string tablename = "";
                object[] members = modeltype.GetCustomAttributes(true);
                for (int i = 0; i < members.Length; i++)
                {
                    if (members[i].GetType() == typeof(SqlTableAttribute))
                    {
                        tablename = ((SqlTableAttribute)members[i]).TableName;
                    }
                }
                PropertyInfo[] proInfos = modeltype.GetProperties();
    
                StringBuilder content1 = new StringBuilder();
                StringBuilder content2 = new StringBuilder();
                foreach (PropertyInfo info in proInfos)
                {
                    foreach (Attribute ab in Attribute.GetCustomAttributes(info))
                    {
                        if (ab.GetType() == typeof(SqlColumnAttribute))
                        {
                            SqlColumnAttribute sqlcolumn = ((SqlColumnAttribute)ab);
    
                            if (sqlcolumn.IsPrimaryKey)
                            {
                                content2.Append(" and " + sqlcolumn.ColumName + "=@" + sqlcolumn.ColumName);
                            }
                            else
                            {
                                content1.Append(sqlcolumn.ColumName + "=@" + sqlcolumn.ColumName + ",");
                            }
                        }
                    }
                }
                content1.Remove(content1.Length - 1, 1);
    
                return string.Format(inSertSql, tablename, content1, content2);
            }
    
            public string GetSql(Type modeltype)
            {
                string inSertSql = "SELECT {0} FROM {1} WHERE 1=1{2}";
                string tablename = "";
                object[] members = modeltype.GetCustomAttributes(true);
                for (int i = 0; i < members.Length; i++)
                {
                    if (members[i].GetType() == typeof(SqlTableAttribute))
                    {
                        tablename = ((SqlTableAttribute)members[i]).TableName;
                    }
                }
                PropertyInfo[] proInfos = modeltype.GetProperties();
    
                StringBuilder content1 = new StringBuilder();
                StringBuilder content2 = new StringBuilder();
                foreach (PropertyInfo info in proInfos)
                {
                    foreach (Attribute ab in Attribute.GetCustomAttributes(info))
                    {
                        if (ab.GetType() == typeof(SqlColumnAttribute))
                        {
                            SqlColumnAttribute sqlcolumn = ((SqlColumnAttribute)ab);
                            content1.Append(sqlcolumn.ColumName + ",");
                            if (sqlcolumn.IsPrimaryKey)
                            {
                                content2.Append(" and " + sqlcolumn.ColumName + "=@" + sqlcolumn.ColumName);
                            }
                        }
                    }
                }
                content1.Remove(content1.Length - 1, 1);
    
                return string.Format(inSertSql, content1, tablename, content2);
            }
    
            #endregion
        }
    
        public class SqlSentenceData
        {
            public virtual string InSertSql { get; set; }
    
            public virtual string DeleteSql { get; set; }
    
            public virtual string UpDateSql { get; set; }
    
            public virtual string GetSql { get; set; }
        }
    View Code

    然后在下面要使用时就可以很简单的直接查找出来

    private string GetSqlStr(string name, SqlMethod method)
            {
    
                switch (method)
                {
                    case SqlMethod.Delete: return SqlSentenceStorage.DataBaseIntegration[name].DeleteSql;
    
                    case SqlMethod.InSert: return SqlSentenceStorage.DataBaseIntegration[name].InSertSql;
    
                    case SqlMethod.UpData: return SqlSentenceStorage.DataBaseIntegration[name].UpDateSql;
    
                    case SqlMethod.Get: return SqlSentenceStorage.DataBaseIntegration[name].GetSql;
                }
    
                return string.Empty;
            }
    View Code

    接下来是最主要的部分,SQLDBOperate类基础数据库操作接口(IDBOperate)已实现对sqlserver的操作。

    public class SQLDBOperate : IDBOperate
        {
            #region IDBOperate 成员
    
    
            public bool InSert(object model)
            {
                SqlParameter[] paramList = GetParams(model);
                string cmdtxt = GetSqlStr(model.GetType().Name, SqlMethod.InSert);
                int count = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionStringLocal, CommandType.Text, cmdtxt, paramList);
                if (count > default(int))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
    
            public bool UpDate(object model)
            {
                SqlParameter[] paramList = GetParams(model);
                string cmdtxt = GetSqlStr(model.GetType().Name, SqlMethod.UpData);
                int count = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionStringLocal, CommandType.Text, cmdtxt, paramList);
                if (count > default(int))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
    
            /// <summary>
            /// 根据主键来删除数据
            /// </summary>
            /// <typeparam name="TModel"></typeparam>
            /// <param name="model"></param>
            /// <returns></returns>
            public bool Delete(object model)
            {
                SqlParameter paramList = null;
                PropertyInfo[] proInfos = model.GetType().GetProperties();
    
                foreach (PropertyInfo info in proInfos)
                {
                    foreach (Attribute ab in Attribute.GetCustomAttributes(info))
                    {
                        if (ab.GetType() == typeof(SqlColumnAttribute))
                        {
                            SqlColumnAttribute sqlcolumn = ((SqlColumnAttribute)ab);
                            if (sqlcolumn.IsPrimaryKey)
                            {
                                paramList = new SqlParameter("@" + sqlcolumn.ColumName, info.GetValue(model, null));
                                break;
                            }
                        }
                    }
                }
                string cmdtxt = GetSqlStr(model.GetType().Name, SqlMethod.Delete);
                int count = SQLHelper.ExecuteNonQuery(SQLHelper.ConnectionStringLocal, CommandType.Text, cmdtxt, paramList);
                if (count > default(int))
                {
                    return true;
                }
                else
                {
                    return false;
                }
            }
    
            public TModel Get<TModel>(object keyID) where TModel : new()
            {
                Type modeltype = typeof(TModel);
                string cmdtxt = GetSqlStr(modeltype.Name, SqlMethod.Get);
                SqlParameter paramList = GetParamsKey(modeltype, keyID);
                SqlDataReader sdr = SQLHelper.ExecuteReader(SQLHelper.ConnectionStringLocal, CommandType.Text, cmdtxt, paramList);
                if (sdr.Read())
                {
                    #region 读取数据插入实体对象中
                    TModel model = new TModel();
                    PropertyInfo[] proInfos = modeltype.GetProperties();
                    int i = 0;
                    foreach (PropertyInfo info in proInfos)
                    {
                        info.SetValue(model, GetsdrValue(info.PropertyType, ref sdr, i), null);
                        i++;
                    }
    
                    #endregion
                    sdr.Dispose();
                    sdr.Close();
                    return model;
                }
                sdr.Dispose();
                sdr.Close();
                return default(TModel);
            }
    
            #endregion
    
    
            #region 内定方法用于操作一些公共方法
            /// <summary>
            /// 根据类型来读取SqlDataReader中的值
            /// </summary>
            /// <param name="type"></param>
            /// <param name="sdr"></param>
            /// <param name="i"></param>
            /// <returns></returns>
            private object GetsdrValue(Type type, ref SqlDataReader sdr, int i)
            {
                if (type == typeof(int))
                {
                    return sdr.GetInt32(i);
                }
                else if (type == typeof(string))
                {
                    return sdr.GetString(i);
                }
                else if (type == typeof(double))
                {
                    return sdr.GetDouble(i);
                }
                else if (type == typeof(DateTime))
                {
                    return sdr.GetDateTime(i);
                }
                else if (type == typeof(Decimal))
                {
                    return sdr.GetDecimal(i);
                }
                else if (type == typeof(Boolean))
                {
                    return sdr.GetBoolean(i);
                }
                else if (type == typeof(float))
                {
                    return sdr.GetFloat(i);
                }
    
                return null;
            }
    
            /// <summary>
            /// 获取参数和值
            /// </summary>
            /// <typeparam name="TModel"></typeparam>
            /// <param name="model"></param>
            /// <returns></returns>
            private SqlParameter[] GetParams(Object model)
            {
                PropertyInfo[] proInfos = model.GetType().GetProperties();
                IDictionary<string, object> dataList = new Dictionary<string, object>();
                foreach (PropertyInfo info in proInfos)
                {
                    foreach (Attribute ab in Attribute.GetCustomAttributes(info))
                    {
                        if (ab.GetType() == typeof(SqlColumnAttribute))
                        {
                            SqlColumnAttribute sqlcolumn = ((SqlColumnAttribute)ab);
                            string name = sqlcolumn.ColumName;
                            object value = info.GetValue(model, null);
                            dataList.Add(name, value);
                        }
                    }
                }
                SqlParameter[] paramList = new SqlParameter[dataList.Count];
                int i = 0;
                foreach (string key in dataList.Keys)
                {
                    SqlParameter item = new SqlParameter("@" + key, dataList[key]);
                    paramList[i] = item;
                    i++;
                }
                return paramList;
            }
    
            /// <summary>
            /// 获取主键的参数和值
            /// </summary>
            /// <typeparam name="TModel"></typeparam>
            /// <param name="model"></param>
            /// <returns></returns>
            private SqlParameter GetParamsKey(Type model, object keyID)
            {
                SqlParameter paramitem = null;
                PropertyInfo[] proInfos = model.GetProperties();
                foreach (PropertyInfo info in proInfos)
                {
                    foreach (Attribute ab in Attribute.GetCustomAttributes(info))
                    {
                        if (ab.GetType() == typeof(SqlColumnAttribute))
                        {
                            SqlColumnAttribute sqlcolumn = ((SqlColumnAttribute)ab);
                            if (sqlcolumn.IsPrimaryKey)
                                paramitem = new SqlParameter("@" + sqlcolumn.ColumName, keyID);
    
                        }
                    }
                }
                return paramitem;
            }
    }
    View Code

    接下来我写了个测试数据表,数据库里需要有一个表与它对应。

        [SqlTable("T_User")]
        public sealed class UserInfo
        {
            [SqlColumn("User_Id",true,true)]
            public int Id { get; set; }
    
            [SqlColumn("User_Name",true)]
            public string Name { get; set; }
    
            [SqlColumn("Pass_Word", true)]
            public string PassWord { get; set; }
    
            [SqlColumn("LastDateTime")]
            public DateTime LastDateTime { get; set; }
        }
    View Code

    SqlTableAttribute特征类用于表名的存放。

     [Serializable]
        public class SqlTableAttribute : Attribute
        {
            public SqlTableAttribute(string name)
            {
                this.TableName = name;
            }
    
            public string TableName { get; set; }
        }
    View Code

    SqlColumnAttribute用于字段的存放。

    [Serializable]
        public class SqlColumnAttribute:Attribute
        {
    
            public SqlColumnAttribute(string name)
            {
                this.ColumName = name;
                this.IsPrimaryKey = false;
                this.IsNotNull = false;
            }
    
            public SqlColumnAttribute(string name,bool notNull)
            {
                this.ColumName = name;
                this.IsPrimaryKey = false;
                this.IsNotNull = notNull;
            }
    
            public SqlColumnAttribute(string name, bool notNull,bool iskey)
            {
                this.ColumName = name;
                this.IsPrimaryKey = iskey;
                this.IsNotNull = notNull;
            }
    
            /// <summary>
            /// 是否为空
            /// </summary>
            public bool IsNotNull { get; set; }
    
            /// <summary>
            /// 列名
            /// </summary>
            public string ColumName { get; set; }
    
            /// <summary>
            /// 是否为主键
            /// </summary>
            public bool IsPrimaryKey { get; set; }
        }
    View Code

    好了!到这一步已经基本完工了,我们来测试一下:

    IDBOperate opens = DataAccess.CreateDBOperate();
                UserInfo user = new UserInfo();
                user.Id = 2;
                user.LastDateTime = DateTime.Now;
                user.Name = "hj";
                user.PassWord = "str";
                opens.InSert(user);
    View Code

    数据库插入成功。

    oracle数据库现在还没写,不过基本和sqlserver差不多。

    我写的不太清楚,还望见谅,以下提供源码下载,说在多也是无用,运行以下就清楚了!

    源码下载https://files.cnblogs.com/hangjian/MyORM.zip

  • 相关阅读:
    C# winfrom容器布局与工具栏&&右键菜单栏&&隐藏显示小图标的的简单事件
    C# Winform ListView控件
    MongoDB3.6.3 windows安装配置、启动
    史蒂夫•乔布斯在斯坦福大学的演讲
    SpringBoot配置文件 application.properties详解
    Elasticsearch分布式安装启动失败
    Couldn't connect to host, port: smtp.163.com, 25; timeout -1;
    CentOS 7 安装jdk9
    生成唯一的随机数(时间+随机数)
    idea 设置背景图片
  • 原文地址:https://www.cnblogs.com/hangjian/p/3114764.html
Copyright © 2020-2023  润新知