学步园

很多人都知道工厂，知道反射，但是真正能用的好的并不多，我也是最近才真正明白了什么是反射，反射和.Net 里的一些其他特性是怎么应用的。在这里，分享一个自己写的小的数据库工厂的架构，和大家讨论。

目的的需求
：

在一个系统里，需要有一个统一的数据库类实现对SQL Server的增删改查
，要求有良好的扩展行和维护性。

在知道反射和属性之前的思路，是对每个实体类写相应的操作。但是现在，可以通过应用Attribute来进行自己定义

首先，要把数据库里的表和实体类对应.这样，我们简单的定义一个属性ClassesDefineSqlAttribute，用对应表和实体类。

 

[AttributeUsage(AttributeTargets.Class)]
    class ClassesDefineSqlAttribute :Attribute 
    {
        public String SqlTableName { get; set; }
        public ClassesDefineSqlAttribute()
        {
          
        }
    }

 

第一步对应好了以后，下一步要将实体类的属性与数据库表中的列进行对应，为此，定义
ClassesDefineColumnAttribute 这里，只是简单的设置了列名，列的类型（数据，主键，改之前的主键）,呵呵，一切从简，够用就好

 

    public enum ColType
    {
        /// <summary>
        /// 数据列
        /// </summary>
        Data,
        /// <summary>
        /// 主键列
        /// </summary>
        ClassCode,
        /// <summary>
        /// 级别列（在树型结构中用到)
        /// </summary>
        ClassLevel,
        /// <summary>
        /// 旧键（更新时使用）
        /// </summary>
        OldCode
    }
    [AttributeUsage(AttributeTargets.Property )]
    public class ClassesDefineColumnAttribute:Attribute 
    {
        //对应的数据库列名
        public string SqlColName { get; set; }
        /// <summary>
        /// 列的类型
        /// </summary>
        public ColType ColType { get; set; }
        /// <summary>
        /// 复杂类型的属性值 
        /// </summary>
        public string TargetProperty { get; set; }
      
    }
}

 

 

用了这些准备，就可以在数据工厂中完成增删改查了

为了通用，我们把增删改的参数都设置成object  ,这样，在操作时，先将object 的最终类别取到，然后得到上面的几个属性，动态的生成SQL语句

先看增操作

 

        public void Create(object T)
        {
            var info = T.GetType();//得到要新建的类型
            ClassesDefineSqlAttribute attribute =
                (ClassesDefineSqlAttribute)Attribute.GetCustomAttribute(info,
                typeof(ClassesDefineSqlAttribute));///得到表名

            if (attribute != null)
            {
                var Properties = info.GetProperties();///得到属性
                System.Reflection.PropertyInfo KeyProperty = null;
                String insert = "INSERT " + attribute.SqlTableName + "(";
                String values = ") Values(";
                String checksql = "";
                String GetKeySql = "WHERE 1=1";
 
                foreach (var infoattr in Properties)
                {
                     
                    var ob = Attribute.GetCustomAttribute(infoattr, typeof(ClassesDefineColumnAttribute));
                    if (ob != null)
                    {
                        var attr = ob as ClassesDefineColumnAttribute;
                        if (attr is ClassesDefineColumnAttribute)
                        {
                            var a = attr as ClassesDefineColumnAttribute;
                            if (a.ColType != ColType.OldCode)
                            {
                                if (a.ColType != ColType.ClassCode
                                   ||
                                    infoattr.PropertyType == typeof(String))
                                {
                                    string valuestr = (infoattr.GetValue(T, null) ?? "").ToString();
                                    insert += a.SqlColName + ",";
                                    values += "'" + valuestr + "',";
                                    GetKeySql = GetKeySql + " And " + a.SqlColName + "='" + valuestr + "'";
                                }
                                if (a.ColType == ColType.ClassCode)
                                {
                                    KeyProperty = infoattr;
                                    if (KeyProperty.PropertyType == typeof(String))
                                    {
                                        checksql = "Select Count(*) FROM " + attribute.SqlTableName + " Where " + a.SqlColName + "='" + (infoattr.GetValue(T, null) ?? "").ToString() + "'";
                                        if (System.Convert.ToInt32(
                                            new DataConnectionFactory().GetDataTable(checksql).Rows[0][0]) > 0)
                                            throw new Exception("无法生成新数据,与现有数据编码相同!");
                                    }
                                    else
                                    {
                                        GetKeySql = "Select " + a.SqlColName + " FROM " + attribute.SqlTableName + " " + GetKeySql;
                                    }
                                }
                            }
                        }
                    }
                }
               
                String sql = insert.Substring(0, insert.Length - 1) + values.Substring(0, values.Length - 1) + ")";
                using (var db = new DataConnectionFactory().GetConnection())
                {
                    db.ExecuteCommand(sql);
                    //设置 自增长主键的返回直

                    if (KeyProperty.PropertyType != typeof(string))
                    {
                        KeyProperty.SetValue(T,
                            new DataConnectionFactory().GetDataTable(GetKeySql).Rows[0][0], null);
                    }
                }
            }
            else
            {
                throw new Exception("类型不符合要求");
            }
        }

增操作完成，改的操作也很类似

 

        public void Save(object T)
        {
            var info = T.GetType();
            ClassesDefineSqlAttribute attribute = (ClassesDefineSqlAttribute)Attribute.GetCustomAttribute(info, typeof(ClassesDefineSqlAttribute));
            String oldCode = "";
            String newCode = "";
            bool HaveOldCode = false;
 
            System.Reflection.PropertyInfo KeyProperty = null;
            if (attribute != null)
            {
                var Properties = info.GetProperties();
                String insert = "UPDATE " + attribute.SqlTableName + " SET ";
                String wheresql = "";
                String checksql = "";
                foreach (var infoattr in Properties)
                {
                   
                    var ob = Attribute.GetCustomAttribute(infoattr, typeof(ClassesDefineColumnAttribute));
                    if (ob != null)
                    {
                        var attr = ob as ClassesDefineColumnAttribute;
                        if (attr is ClassesDefineColumnAttribute)
                        {
                            var valueStr = infoattr.GetValue(T, null).ToString();
                            var a = attr as ClassesDefineColumnAttribute;
                            if (attr.ColType == ColType.Data
                                || (attr.ColType == ColType.ClassCode && infoattr.PropertyType == typeof(String)))
                                insert += a.SqlColName + "='" + valueStr + "',";
                            if (a.ColType == ColType.ClassCode)
                            {
                                checksql = a.SqlColName + "='" + valueStr + "'";
                                KeyProperty = infoattr;
                                newCode = valueStr;
                            }
                            if (a.ColType == ColType.OldCode)
                            {
                                HaveOldCode = true;
                                wheresql = " Where " + a.SqlColName + "='" + valueStr + "'";
                                oldCode = valueStr;
                            }
                        }
                    }
                }
               
                if (HaveOldCode)
                {
                    if (newCode != oldCode)
                    {
                        checksql = "Select count(*) FROM " + attribute.SqlTableName + wheresql + " OR  " + checksql;
                        if (System.Convert.ToInt32(
                            new DataConnectionFactory().GetDataTable(checksql).Rows[0][0]) >= 2)
                            throw new Exception("无法生成新数据,与现有数据编码相同!");
                    }
                }
                else
                    wheresql = "Where " + checksql;
                String sql = insert.Substring(0, insert.Length - 1) + wheresql;
                using (var db = new DataConnectionFactory().GetConnection())
                {
                    db.ExecuteCommand(sql);
                }
            }
            else
            {
                throw new Exception("类型不符合要求");
            }
        }

最后是删除的操作

 

     public void Delete(object T)
        {
            var info = T.GetType();
            ClassesDefineSqlAttribute attribute = (ClassesDefineSqlAttribute)Attribute.GetCustomAttribute(info, typeof(ClassesDefineSqlAttribute));
            TreeNodeAttribute treedef = (TreeNodeAttribute)Attribute.GetCustomAttribute(info, typeof(TreeNodeAttribute));
       
            if (attribute != null)
            {
                var Properties = info.GetProperties();
                String insert = "Delete " + attribute.SqlTableName + " ";
                
                foreach (var infoattr in Properties)
                {                     var ob = Attribute.GetCustomAttribute(infoattr, typeof(ClassesDefineColumnAttribute));
                    if (ob != null)
                    {
                        var attr = ob as ClassesDefineColumnAttribute;
                        if (attr is ClassesDefineColumnAttribute)
                        {
                            var valueStr = infoattr.GetValue(T, null).ToString();
                            var a = attr as ClassesDefineColumnAttribute;
                           
                            if (a.ColType == ColType.OldCode)
                            {
                                insert  += " Where " + a.SqlColName + "='" + valueStr + "'";
                                break;
                            }
                            else if (a.ColType == ColType.ClassCode)
                            {
                                insert += " Where " + a.SqlColName + "='" + valueStr + "'";
                                break;
                            }
                        }
                    }
                }
                String sql = insert;
          
                using (var db = new DataConnectionFactory().GetConnection())
                {
                    db.ExecuteCommand(sql);
                }
            }
            else
            {
                throw new Exception("类型不符合要求");
            }
        }

 

这样，再有类型 ，只要做好相应的标记，便 可直接应用这个类了

如我的表示档案盒的类型FileBox

 

    [ClassesDefineSql(SqlTableName = "DA_FileBox")]
    public class FileBox
    {
        /// <summary>
        /// 档案盒
        /// </summary>
        [ClassesDefineColumn(ColType = ColType.ClassCode, SqlColName = "BoxNo")]
        public String BoxNo
        {
            get;
            set;
        }
        /// <summary>
        /// 标签ID
        /// </summary>
        [ClassesDefineColumn(ColType = ColType.Data, SqlColName = "TagNo")]
        public string TagID
        {
            get;
            set;
        }
        /// <summary>
        /// 类别信息
        /// </summary>
        [ClassesDefineColumn(ColType = ColType.Data, SqlColName = "ClassNo")]
        public s











														

返回