使用反射自动添加sql参数

来源：互联网发布：航海家软件骗局编辑：程序博客网时间：2024/04/30 18:58

1.在三层中的model下新建类TestModel.cs文件其中代码如下

private string _DIVISION_NAME_CHI;
        /// <summary>
        /// 处
        /// </summary>
        [Column(Name = "DIVISION_NAME_CHI")]
        public string DIVISION_NAME_CHI
        {
            get { return _DIVISION_NAME_CHI; }
            set { _DIVISION_NAME_CHI = value; }
        }

        private string _BU_NAME_CHI;
        /// <summary>
        /// 部门
        /// </summary>
        [Column(Name = "BU_NAME_CHI")]
        public string BU_NAME_CHI
        {
            get { return _BU_NAME_CHI; }
            set { _BU_NAME_CHI = value; }
        }

        private string _EMPLOYEEID;
        /// <summary>
        /// 工号
        /// </summary>
        [Column(Name = "EMPLOYEEID")]
        public string EMPLOYEEID
        {
            get { return _EMPLOYEEID; }
            set { _EMPLOYEEID = value; }
        }

2.在model文件夹下新建类ColumnAttribute.cs文件

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace Model
{
    public class ColumnAttribute : Attribute
    {
        private string _Name;

        public string Name
        {
            get { return _Name; }
            set { _Name = value; }
        }
    }
}

3.在DAL文件夹下新建类TestDAL.cs文件添加该方法

#region 构造sql参数集
        /// <summary>
        /// 构造sql参数集
        /// </summary>
        /// <param name="myDishInfo">测试model</param>
        /// <returns></returns>
        public static List<SqlParameter> GetSqlParametersList(ref TestModel myBJTableInfo)
        {
            Type type = myBJTableInfo.GetType();
            PropertyInfo[] propertyInfos = type.GetProperties();
            List<SqlParameter> sqlparameterList = new List<SqlParameter>();
            if (propertyInfos != null && propertyInfos.Length > 0)
            {
                foreach (PropertyInfo info in propertyInfos)
                {
                    object[] columns = info.GetCustomAttributes(typeof(ColumnAttribute), true);
                    if (columns != null && columns.Length > 0)
                    {

                        ColumnAttribute ca = columns[0] as ColumnAttribute;
                        string name = "@" + ca.Name;
                        SqlParameter para = new SqlParameter(name, info.GetValue(myBJTableInfo, null));
                        sqlparameterList.Add(para);
                    }
                }
            }
            return sqlparameterList;
        }
        #endregion

4.在TestDAL.cs引入命名空间using System.Reflection;

5.在需要添加sql参数的地方进行如下调用，就可将model中的参数和值加载进去了

    List<SqlParameter> ilistStr = new List<SqlParameter>();
            ilistStr = GetSqlParametersList(ref model);
   SqlParameter[] paras = ilistStr.ToArray();

6.缺点是无论是只需要其中的一个参数还是几个参数都会全部加载进去

0 0