简易版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; }
    }

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

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;
        }

接下来是最主要的部分，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;
        }
}

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

    [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; }
    }

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

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

        public string TableName { get; set; }
    }

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; }
    }

好了！到这一步已经基本完工了，我们来测试一下：

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

数据库插入成功。

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

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

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