运用泛型实现增删改

最近在做项目时,频繁的使用增删改，把大量时间花费在写Sql语句和参数上，就想能不能写一个通用的方法省略掉写sql 和参数。想了一段时间，想到.net里面有Attitude类，可以自定义属性，这样就可以根据自定义属性获取sql字段和参数名，然后构造出sql语句和sqlParamertes ，要想根据不同表的增删改，可以用泛型实现。

首先定义一个继承自Attribute的自定义类：Entry包括 数据库列名、参数名、参数长度、参数类型、是否包括改字段属性。

代码如下：

 public class Entry:Attribute
  {
        private string columnName;

        /// <summary>
        /// 列名
        /// </summary>
        public string ColumnName
        {
            get { return columnName; }
            set { columnName = value; }
        }
        /// <summary>
        /// 参数名
        /// </summary>
        private string paramName;

        public string ParamName
        {
            get { return paramName; }
            set { paramName = value; }
        }
        /// <summary>
        /// 参数类型
        /// </summary>
        private SqlDbType paramType;

        public SqlDbType ParamType
        {
            get { return paramType; }
            set { paramType = value; }
        }
       
        /// <summary>
        /// 参数大小
        /// </summary>
        private int paramLength;

        public int ParamLength
        {
            get { return paramLength; }
            set { paramLength = value; }
        }
        /// <summary>
        /// 是否加入实体
        /// </summary>
        

        private bool show=true;

        public bool Show
        {
            get { return show; }
            set { show = value; }
        }
        public Entry() { }

        public Entry(string colName, SqlDbType paramType)
        {
            this.columnName = colName;
            this.paramType = paramType;
        }

        public Entry(string colName, string paramName, SqlDbType paramType)
        {
            this.columnName = colName;
            this.paramName = paramName;
            this.paramType = paramType;
        }

        public Entry(string colName, string paramName, SqlDbType paramType, int paramLength)
        {
            this.columnName = colName;
            this.paramName = paramName;
            this.paramType = paramType;
            this.ParamLength = paramLength;
        }

        public Entry(string colName, string paramName, SqlDbType paramType, int paramLength, bool show)
        {
            this.columnName = colName;
            this.paramName = paramName;
            this.paramType = paramType;
            this.ParamLength = paramLength;
            this.show = show;
        }
}

接下来就是根据自定类动态构造增加数据的方法。要想获取自定义属性，可以采用反射机制获取字段名，参数名，参数长度等信息。

下面四泛型实现的添加数据的Add方法

public static void Add<T>(T entity, string tableName) where T : class, new()
        {
          
            Type type=typeof (T);
         
            List<string> fields = new List<string>();
            List<string> paramNames = new List<string>();
            PropertyInfo[] propertys = type.GetProperties();
            List<SqlParameter> parameters = new List<SqlParameter>();
            SqlParameter param = null;

            foreach (PropertyInfo info in propertys) //Folder所有属性
            {
                object[] attributes = info.GetCustomAttributes(true);
               
                foreach (object attribute in attributes)
                {
                    if (attribute is Entry)
                    {
                        Entry entry =attribute as Entry;

                        fields.Add(entry.ColumnName);
                        paramNames.Add(entry.ParamName);

                        param = new SqlParameter(entry.ParamName, entry.ParamType, entry.ParamLength);
                        param.Value=info.GetValue (entity,null);
                        parameters.Add(param);
                       
                    }
                }
                
            }

            StringBuilder sBuilder = new StringBuilder();
            string fieldSql = string.Empty;
            string paramSql = string.Empty;

            sBuilder.Append("Insert into ");
            sBuilder.Append(tableName);
            sBuilder.Append(" (");

            foreach (string field in fields)
            {
                fieldSql+=field + ","; 
            }

            fieldSql = fieldSql.Substring(0, fieldSql.LastIndexOf(','));
            sBuilder.Append(fieldSql);
            sBuilder.Append(" ) values (");

            foreach (string pN in paramNames)
            {
                paramSql += "@" + pN + ",";
            }

            paramSql = paramSql.Substring(0, paramSql.LastIndexOf(','));
            sBuilder.Append(paramSql);
            sBuilder.Append(" )");

            ExecuteSql(sBuilder.ToString(), parameters.ToArray ());
            
        }

下面是一个具体例子：根据自定义属性配置参数名、参数类型等基本信息

    public class Folder
    {
        /// <summary>
        /// Folder Id  Guid 
        /// </summary>
        [Entry("Id","Id",SqlDbType.NVarChar,32)]
        public string Id { set; get; }

        /// <summary>
        /// Folder Name
        /// </summary>
        [Entry("Name", "Name", SqlDbType.NVarChar, 32)]
        public string Name { set; get; }

        /// <summary>
        /// Folder Parent Id 
        /// </summary>
        [Entry("ParentId", "ParentId", SqlDbType.NVarChar, 32)]
        public string ParentId { set; get; }

        /// <summary>
        /// 
        /// </summary>
        [Entry("UserId", "UserId", SqlDbType.NVarChar, 32)]
        public int UserId { set; get; }

        /// <summary>
        ///Pengding type: add/update/delete 
        /// </summary>
        public string Type { set; get; }

        [Entry("CreateTime", "CreateTime", SqlDbType.DateTime, 32)]
        public DateTime CreateTime { set; get; }
    }

同理，Delete、Update可以写出一个通用的方法来。不过，Update可能有点麻烦，很多时候只更新个别字段，这个时候就 不能把所有字段都更新。最近工作比较忙，这个问题以后有时间再去研究。