C#用Attribute及反射封装sql增删改查

2012-07-28  来自：cnblogs  字体大小：【大 中 小】

• 摘要：本文介绍C#用Attribute及反射封装sql增删改查，并提供详细的示例代码供参考。

用Attribute及反射封装sql增删改查，单实体可以对应多个表，但单实体同一时间只对应一张表操作。

#region 执行新增        /// <summary>        /// 执行新增        /// </summary>        /// <param name="sender">对象实体</param>        /// <returns>int</returns>        protected virtual int Insert(T sender)        {            if (sender == null)            {                return 0;            }            #region 初始化数据            int TableType = 0;            //获取表名，主要应对同一个实体可能插入不同表情况。            string tableName = GetTableName(out TableType, sender);            string CmdText = "", fieldNames = "", fieldNamesValue = "";            Type senderType = sender.GetType();            PropertyInfo[] proInfos = senderType.GetProperties();            Parameters = new Dictionary<string, object>();            DataFieldAttribute attribute;            #region 从模型中取出各属性值            foreach (PropertyInfo proInfo in proInfos)            {                if (proInfo.IsDefined(typeof(DataFieldAttribute), false))                {                    attribute = (DataFieldAttribute)Attribute.GetCustomAttribute(proInfo, typeof(DataFieldAttribute));                    //获取字段名，主要应对同一个实体可能插入不同表情况。                    string fieldName = GetColumnName(TableType, attribute);//字段名称                    if (!string.IsNullOrEmpty(fieldName))                    {                        object fieldValue = proInfo.GetValue(sender, null);//字段相应的值                        #region 字段相应的值                        //非视图及自增字段则添加到SQL                        if (!attribute.View && !attribute.Identity)                        {                            if (fieldValue != null)                            {                                fieldNames += fieldName + " ,";                                fieldNamesValue += "@" + fieldName + " ,";                                Parameters.Add(fieldName, fieldValue);                            }                        }                        #endregion                    }                }            }            #endregion            if (!String.IsNullOrEmpty(fieldNames)) fieldNames = fieldNames.Trim(',');            if (!String.IsNullOrEmpty(fieldNamesValue)) fieldNamesValue = fieldNamesValue.Trim(',');            #endregion            CmdText = "INSERT INTO {0} ({1}) VALUES({2})";            CmdText = string.Format(CmdText, tableName, fieldNames, fieldNamesValue);            return ExecuteNonQuery(CmdText, CommandType.Text, Parameters, true);        }        #endregion

实体的定义，根据需要自己定义表名称。本例在新增时插入t1(待审核表),审核完成后插入t2(审核表)

/// <summary>    /// 信息属性实体    /// </summary>    [Serializable]    [DataTable("t1", "t2")]//定义资料表名称    public class InformationModel    {        #region 公有字段        private string _infoid;        /// <summary>        /// ID        /// </summary>        [DataField("Info_ID", ColumnType = DbType.Int32, PrimaryKey = true, Identity = true)]        public string InfoID        {            get            {                return _infoid;            }            set            {                _infoid = value;            }        }        private int _companyid;        /// <summary>        /// 公司ID        /// </summary>        [DataField("company_id", ColumnType = DbType.Int32, PrimaryKey = true)]        public int CompanyID        {            get            {                return _companyid;            }            set            {                _companyid = value;            }        }        private int _informationpagetype = 0;        /// <summary>        /// 表类别（1.t2表[求购|出售]，0.t1表[审核中]）        /// </summary>        [DataField(TableColumn = true)]        public int InformationPageType        {            get            {                return _informationpagetype;            }            set            {                _informationpagetype = value;            }        }        private int _informationtype;        /// <summary>        /// 信息类别（1.求购，0.出售）        /// </summary>        [DataField("informationtype")]        public int InformationType        {            get            {                return _informationtype;            }            set            {                _informationtype = value;            }        }#endregion    }