Attribute C#

C# 利用Attribute控制物件View & input Model for StoredProcedure

劉仁竹 2021/09/16 09:55:08

1 2520

前言

假設有一個情境是希望Server端在連接資料庫時，只能用StoredProcedure，通常在MVC正常觀念下，針對新增、刪除、修改、查詢時，會依據欄位需求建置Model，在這狀況至少會有兩個Model產生，查詢與其他修改資料行為，這時候萬一有很多表格資料都需要處理新刪修查時，就會產生很多Model，如果加上欄位變動的頻率很高的狀況，除了原本就要處裡的StoredProcedure，以及前端顯示及修改Model就會花費不少時間，而處理這個狀況通常會使用ADO.NET Entity Framework自動產生Entity方式，這邊則是提供另一個使用Attribute方法來處理這個情況。

Attribute

建置設定物件額外Attribute

ModelInfoAttribute.cs

 
 [AttributeUsage(AttributeTargets.Property, AllowMultiple = false, Inherited = true)]
    public class ModelInfoAttribute: Attribute
    {

        #region InsertObjectProp

        /// <summary>
        /// 資料表欄位格式
        /// </summary>
        public SqlDbType ValueType { get; protected set; }

        /// <summary>
        /// 資料表欄位格式長度(varchar)
        /// </summary>
        public long? VarcharMaxLength { get; protected set; }

        #endregion

        /// <summary>
        /// 是否為輸入sp 欄位
        /// </summary>
        public bool IsInputSPData { get; protected set; }

        /// <summary>
        /// 前端顯示欄位
        /// </summary>
        public string TitleName { get; protected set; }

        /// <summary>
        /// Initializes a new instance of the <see cref="ModelInfoAttribute"/> class.
        /// </summary>
        /// <param name="titleName">前端顯示欄位</param>
        /// <param name="valueType">資料表欄位格式</param>
        /// <param name="isInputSPData">是否為輸入sp 欄位</param>
        /// <param name="varcharMaxLength">資料表欄位格式長度(varchar)</param>
        public ModelInfoAttribute(string titleName, SqlDbType valueType, bool isInputSPData,
            long varcharMaxLength)
        {
            TitleName = titleName;
            ValueType = valueType;
            VarcharMaxLength = varcharMaxLength;
            IsInputSPData = isInputSPData;
        }


    }

StoredProcedure

接著確認StoredProcedure格式

SQL Save

create type saveDto as table
(
	ID varchar(50), 
    CName varchar(50),
    Birthday date,
	UpdateDate datetime
);
Create PROCEDURE dbo.sSave @DataList saveDto READONLY
AS
    BEGIN
        SET NOCOUNT ON;
		        	
		INSERT INTO Temp..CustomData
		(
			ID, 
			CName, 
			Birthday,
			UpdateDate
        )
               SELECT ID, 
                      CName, 
                      Birthday,
					  UpdateDate
               FROM @DataList;
		
		SET NOCOUNT OFF;
    END;

SQL Query

Create PROCEDURE dbo.sQuery @ID VARCHAR(50)

AS
    BEGIN
        SET NOCOUNT ON;
		SELECT CName,Birthday
		FROM Temp..CustomData with(NOLOCK)
		WHERE  ID like CONCAT('%', @ID,'%') 
		SET NOCOUNT OFF;
    END;

Model

從 StoredProcedure輸出輸入欄位可知道

Query：CName,Birthday

Save：ID、CName、Birthday、UpdateDate

所以我的class Model可以寫成

    // *屬性順序必須依照[前端顯示]ModelInfo titleName 及[使用者定義表格]ModelInfo isInputSPData
    public class SaveModel
    {
        [ModelInfo("", SqlDbType.VarChar, true, 50)]
        public string ID { get; set; }     

        [ModelInfo("姓名", SqlDbType.VarChar, true, 50)]
        public string CName { get; set; }

        [ModelInfo("生日", SqlDbType.Date, true, -1)]
        public DateTime? Birthday { get; set; }

        [ModelInfo("", SqlDbType.DateTime, true, -1)]
        public DateTime? UpdateDate { get; set; }

    }

Query

建置通用ViewFieldsModel.cs

    /// <summary>
    /// 由客製model前端所需要之欄位及對應標題名稱
    /// </summary>
    public class ViewFieldModel
    {
        /// <summary>
        /// 欄位名稱
        /// </summary>
        public string FieldName { get; set; }

        /// <summary>
        /// 標題名稱
        /// </summary>
        public string TitleName { get; set; }

        public ViewFieldModel(string fieldName,string titleName)
        {
            FieldName = fieldName;
            TitleName = titleName;
        }
    }

以及取得Model所需之欄位及標題 ConvertModelHelper.cs

        /// <summary>
        /// 回傳前端model所需之欄位及標題 排除 標題=""為空白
        /// </summary>
        /// <typeparam name="T">功能model</typeparam>
        /// <returns></returns>
        public static List<ViewFieldModel> GetProps<T>()
        {
            List<ViewFieldModel> list = new List<ViewFieldModel>();
            IList<PropertyInfo> props = new List<PropertyInfo>(typeof(T).GetProperties());
            foreach (var item in props)
            {
                var attribute = item.GetCustomAttribute(typeof(ModelInfoAttribute), false) as ModelInfoAttribute;
                list.Add(new ViewFieldModel(item.Name, attribute.TitleName));
            }
            return list.Where(x => !string.IsNullOrEmpty(x.TitleName)).ToList();
        }

在網頁建置初始化時，就可以先取得要顯示之欄位及對應

            var viewFields = ConvertModelHelper.GetProps<SaveModel>();
            var fields = viewFields.Select(x => x.FieldName).ToList();

在取得資料Query時，就可以這麼寫

            var sDynamicTable = new sDynamicTableInputMulti();

            // StoredProcedure 設定回傳欄位值
            var getQuery = sDynamicTable.GetExce("23", "sQuery", "@ID");
            var result = Newtonsoft.Json.JsonConvert.SerializeObject(getQuery);

sDynamicTableInputMulti.cs 為GetExce呼叫SQL StoredProcedure物件Query方法

        public List<Dictionary<string, object>> GetExce(string id, string commandText, string parameterName)
        {
            string strSQL = _sql;
            List<Dictionary<string, object>> results = null;
            using (var conn = new SqlConnection(strSQL))
            using (var cmd = conn.CreateCommand())
            {

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = commandText;
                cmd.Parameters.Add(parameterName, SqlDbType.VarChar).Value = id;
                
                conn.Open();
                SqlDataReader reader = cmd.ExecuteReader();

                results = new List<Dictionary<string, object>>();

                while (reader.Read())
                {
                    Dictionary<string, object> row = new Dictionary<string, object>();
                    for (int i = 0; i < reader.FieldCount; i++)
                    {
                        row.Add(reader.GetName(i), reader[i]);
                    }
                    results.Add(row);
                }

                conn.Close();
            }

            return results;
        }

取得Query資料後，有可能會不想把其他沒用的欄位移除，這邊提供一個方法處理

ConvertModelHelper.cs

        public static Dictionary<string, object> FilterReturnfields(IDictionary<string, object> keyValue, List<string> returnfields)
        {
            var value = keyValue.Where(x => returnfields.Where(r => r == x.Key).Any()).ToDictionary(p => p.Key, p => p.Value);
            return value;
        }

這樣前端取得資料時可改寫為

var result_ID = getQuery.Select(s => ConvertModelHelper.FilterReturnfields(s, fields)).ToList();

Save

建置測試上傳資料

            // 預設資料
            List<SaveModel> datas = new List<SaveModel>() 
            {
                new SaveModel(){ ID = "A1234", CName = "Tom" , Birthday = DateTime.Parse("1999/01/01")  },
                new SaveModel(){ ID = "B2345", CName = "Amy" , Birthday = DateTime.Parse("1988/02/02")  },
                new SaveModel(){ ID = "C3322", CName = "Cam" , Birthday = DateTime.Parse("1977/03/03")  }
            };

假設收到資料時，程式的處理方式如下

            // 設定預設值
            var defaultModel = new SaveModel()
            {
                UpdateDate = DateTime.Now,
            };

            List<InsertObjectProp> insertObjectProps = ConvertModelHelper.BuildObjectProps(defaultModel);

            InsertDatasFactory insertDatasFactory = new InsertDatasFactory();
            insertDatasFactory.InputDataRecord(insertObjectProps, datas, "sSave", "@DataList");

由於更新時間是系統給予，所以會建置一個SaveModel來放預設值，處理方法如下

ConvertModelHelper.cs

        /// <summary>
        /// 依據model Attributes 建置 InsertObjectProp
        /// </summary>
        /// <typeparam name="T">功能model</typeparam>
        /// <param name="obj"></param>
        /// <returns></returns>
        public static List<InsertObjectProp> BuildObjectProps<T>(T obj)
        {
            List<InsertObjectProp> insertObjectProps = new List<InsertObjectProp>();
            //取出物件Attributes
            Type myType = obj.GetType();
            IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties());
            foreach (var item in props)
            {
                var attribute = item.GetCustomAttribute(typeof(ModelInfoAttribute), false) as ModelInfoAttribute;
                object value = item.GetValue(obj,null);

                // 轉換參數 Attributes 無法使用 Null 物件 long?
                long? varcharMaxLength = attribute.VarcharMaxLength > 0 ? attribute.VarcharMaxLength : null;

                if (attribute.IsInputSPData)
                {
                    insertObjectProps.Add(new InsertObjectProp(item.Name, attribute.ValueType,
                    varcharMaxLength, value));
                }
            }

            return insertObjectProps;
        }

InsertObjectProp為處理匯入SqlMetaData物件

    /// <summary>
    /// 對應資料欄位匯入sp參數物件
    /// </summary>
    public class InsertObjectProp
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="fieldName">對應使用者定義資料表格名稱</param>
        /// <param name="valueType">資料表欄位格式</param>
        /// <param name="varcharMaxLength">資料表欄位格式長度(varchar)</param>
        /// <param name="defaultValue">預設值</param>
        public InsertObjectProp(string fieldName, SqlDbType valueType,
            long? varcharMaxLength = null, object defaultValue = null)
        {
            FieldName = fieldName;
            ValueType = valueType;
            VarcharMaxLength = varcharMaxLength;
            DefaultValue = defaultValue;
        }

        /// <summary>
        /// 對應使用者定義資料表格名稱
        /// </summary>
        public string FieldName { get; set; }

        /// <summary>
        /// 資料表欄位格式
        /// </summary>
        public SqlDbType ValueType { get; set; }

        /// <summary>
        /// 資料表欄位格式長度(varchar)
        /// </summary>
        public long? VarcharMaxLength { get; set; }

        /// <summary>
        /// 預設值
        /// </summary>
        public object DefaultValue { get; set; }

    }

建置好的InsertObjectProp之後會丟到InsertDatasFactory處理

InsertDatasFactory.cs

        /// <summary>
        /// 依據InsertObjectProp建置SqlDataRecord並輸入sp
        /// </summary>
        /// <param name="insertObjectProps">欄位參數</param>
        /// <param name="datas">資料</param>
        /// <param name="commandText">sp名稱</param>
        /// <param name="parameterName">參數名稱 例 @Datalist</param>
        public void InputDataRecord<T>(List<InsertObjectProp> insertObjectProps, List<T> datas,
            string commandText, string parameterName)
        {
            //轉換為對應物件
            List<SqlDataRecord> dataRecords = BuildDataRecord(insertObjectProps, datas);

            //傳入寫入資料
            sDynamicTableInputMulti sDynamicTableInputMulti = new sDynamicTableInputMulti();
            sDynamicTableInputMulti.Execute(dataRecords, commandText, parameterName);
        }

先將InsertObjectProp與資料datas，動態轉換為SqlDataRecord物件，

InsertDatasFactory.cs

        /// <summary>
        /// 輸出資料依據InsertObjectProp建置SqlDataRecord
        /// 注意表格中 是否有其他特殊格式!
        /// </summary>
        /// <param name="datas">輸出資料</param>
        /// <param name="insertObjectProps">屬性設定</param>
        /// <returns></returns>
        private List<SqlDataRecord> BuildDataRecord<T>(List<InsertObjectProp> insertObjectProps, List<T> datas)
        {
            var recordList = new List<SqlDataRecord>();

            List<SqlMetaData> metaDatas = BuildMetaData(insertObjectProps);

            for (int i = 0; i < datas.Count; i++)
            {
                var record = new SqlDataRecord(metaDatas.ToArray());
                var objItem = datas[i];
                Type myType = objItem.GetType();
                IList<PropertyInfo> props = new List<PropertyInfo>(myType.GetProperties());
                
                for (int j = 0; j < insertObjectProps.Count(); j++)
                {
                    var item = insertObjectProps[j];
                    var prop = props.Where(x => x.Name == item.FieldName).FirstOrDefault();
                    object objectValue = item.DefaultValue ?? prop.GetValue(objItem, null);
                    SetDataValue(record, j, i, item.FieldName, item.ValueType, objectValue);
                }
                recordList.Add(record);
            }

            return recordList;
        }
        /// <summary>
        /// 建置SqlMetaData
        /// </summary>
        /// <param name="insertObjectProps">屬性設定</param>
        /// <returns></returns>
        private List<SqlMetaData> BuildMetaData(List<InsertObjectProp> insertObjectProps)
        {
            List<SqlMetaData> metaDatas = new List<SqlMetaData>();
            foreach (var item in insertObjectProps)
            {
                if (item.ValueType == SqlDbType.VarChar || item.ValueType == SqlDbType.Char)
                {
                    metaDatas.Add(new SqlMetaData(item.FieldName, item.ValueType, item.VarcharMaxLength.Value));
                }
                else
                {
                    metaDatas.Add(new SqlMetaData(item.FieldName, item.ValueType));
                }
            }
            return metaDatas;
        }

        
        /// <summary>
        /// SqlDataRecord 建置資料
        /// </summary>
        /// <param name="record"></param>
        /// <param name="indexProp">list InsertObjectProp 排序</param>
        /// <param name="rowIndex">SqlDataRecord row</param>
        /// <param name="fieldName">欄位名稱</param>
        /// <param name="sqlDbType">SQL資料型別</param>
        /// <param name="objectValue">輸入值</param>
        private void SetDataValue(SqlDataRecord record,int indexProp, int rowIndex,string fieldName, SqlDbType sqlDbType, object objectValue)
        {
            string errorMsg = "error message";
            if (sqlDbType == SqlDbType.VarChar || sqlDbType == SqlDbType.Char)
            {
                record.SetString(indexProp, Convert.ToString(objectValue));
            }
            else if (sqlDbType == SqlDbType.Decimal) 
            {
                if (decimal.TryParse(Convert.ToString(objectValue), out decimal decimalvalue))
                {
                    record.SetDecimal(indexProp, decimalvalue);
                }
                else
                {
                    throw new ArgumentException(errorMsg);
                }
            }
            else if (sqlDbType == SqlDbType.DateTime || sqlDbType == SqlDbType.Date || sqlDbType == SqlDbType.DateTime2)
            {
                if (objectValue is DateTime)
                {
                    record.SetDateTime(indexProp, Convert.ToDateTime(objectValue));
                }
                else
                {
                    throw new ArgumentException(errorMsg);
                }
                
            }
            else if (sqlDbType == SqlDbType.Int )
            {
                if (int.TryParse(Convert.ToString(objectValue), out int intvalue))
                {
                    record.SetInt32(indexProp, intvalue);
                }
                else
                {
                    throw new ArgumentException(errorMsg);
                }
            }
            else if (sqlDbType == SqlDbType.TinyInt)
            {
                if (byte.TryParse(Convert.ToString(objectValue), out byte intvalue))
                {
                    record.SetByte(indexProp, intvalue);
                }
                else
                {
                    throw new ArgumentException(errorMsg);
                }
            }
        }

SqlDataRecord 為匯入StoredProcedure參數物件

sDynamicTableInputMulti.cs

        /// <summary>
        /// 
        /// </summary>
        /// <param name="recordList">格式化資料</param>
        /// <param name="commandText">sp名稱</param>
        /// <param name="parameterName">參數名稱 例 @Datalist</param>
        /// <returns></returns>
        public int Execute(List<SqlDataRecord> recordList, string commandText, string parameterName)
        {
            string strSQL = _sql;
            int result;
            using (var conn = new SqlConnection(strSQL))
            using (var cmd = conn.CreateCommand())
            {

                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = commandText;
                cmd.Parameters.Add(new SqlParameter
                {
                    ParameterName = parameterName,
                    SqlDbType = SqlDbType.Structured,
                    Value = recordList,
                    Direction = ParameterDirection.Input
                });
                conn.Open();
                result = cmd.ExecuteNonQuery();
                conn.Close();
            }

            return result;  
        }

結論

經過許多動態判斷處理後，有新的表格資料對應StoredProcedure，只要建置一個model，前端初始化及查詢、儲存都可以完成。

參考網站

SQL - 資料表值參數 (Table Value Parameters)

C# - 呼叫 MSSQL Store Procedure 傳入 UDT 參數 (Table-Valued Parameter)

LINQ - Dynamic select

C# - Converting SqlDataReader column values to json string in C#