--- tags: C#進階VIP班 --- # 玩轉C#之【 第一次作業點評 通用類庫封裝實戰】 ## 基本的結構建置 1 建立一個數據庫,然後執行下面的數據庫腳本,會增加兩張表 User Company,大家可以去表裡面自己多加入一些數據 2 建立數據庫表映射的基類BaseModel,包括 Id屬性 建立兩個子類Model:公司和用戶,按照表結構來 ![分層架構](https://i.imgur.com/PhPKmus.png) * 共用模組層:屬於最底層,可以被DAL、IDAL、Model、Application來引用,提供一些幫助類別 * 數據庫訪問層:主要跟數據庫溝通的,IDAL(抽象),DAL(實體) * Model層:主要是對應數據庫一張一張的表 * 展示層:主要放控制台的程式 在Model層: **建置 BaseModel** ```csharp= public class BaseModel { public int Id { get; set; } } ``` **建置 Company並且繼承BaseModel** ```csharp= public class Company: BaseModel { public string Name { get; set; } public DateTime CreateTime { get; set; } public int CreatorId { get; set; } public int LastModifierId { get; set; } public DateTime LastModifyTime { get; set; } } ``` **建置 User並且繼承BaseModel** ```csharp= public class User : BaseModel { public string Name { get; set; } public string Account { get; set; } public string Password { get; set; } public string Email { get; set; } public string Mobile { get; set; } public int CompanyId { get; set; } public string CompanyName { get; set; } public int States { get; set; } public int UserType { get; set; } public DateTime LastLoginTime { get; set; } public DateTime CreateTime { get; set; } public int CreatorId { get; set; } public int LastModifierId { get; set; } public DateTime LastModifyTime { get; set; } } ``` ## 基礎查詢封裝 3 提供兩個泛型的數據庫訪問方法,用 BaseModel約束 一個是用id去查詢單個實體,(只有這一個參數) 一個是查詢出數據表的全部數據列表查詢(沒有參數) 在DALL層: **建置 BaseDAL並約束BaseModel** :::info * 在這邊約束是為了能正確的調用,才能確保主鍵是Id * FROM這邊加入"[ ]"可以防止因為type.Name是關鍵字而報錯誤 * 在程式中靜態的字串,建議放到 共用模組層 ::: ```csharp= public class BaseDAL { public T Find<T>(int id) where T:BaseModel { Type type = typeof(T); string columString =string.Join(",",type.GetProperties().Select(p=>$"[{p.Name}]")); string sql =$"SELECT{columString} FROM[{type.Name}] WHERE Id ={id}"; T t =(T)Activator.CreateInstance(type); using(SqlConnection conn = new SqlConnection(StaticConstant.SqlServerConnString)) { SqlCommand command = new SqlCommand(sql,conn); conn.Open(); SqlDataReader reader = command.ExecuteReader(); if(reader.Read())//表示有資料 開始讀 { foreach (var prop in type.GetProperties()) { prop.SetValue(t, reader[prop.Name]); } } } return t; } } ``` 在Framework層: ```csharp= piblic class StaticConstant { /// <summary> /// 數據庫連線字串 /// </summary> public static string SqlServerConnString = ConfigurationManager.ConnectionStrings["Customers"].ConnectionString; } ``` 再main中可以測試 ```csharp= static void Main(string[] args) { try { BaseDAL baseDAL = new BaseDAL(); var company = baseDAL.Find<Company>(2); } catch (Exception e) { Console.WriteLine(e); throw; } } ``` 疑!!!運行的時候怎麼跳出`{"類型 'System.DBNull' 的物件無法轉換成類型 'System.Int32'。"}` ![dbNULL](https://i.imgur.com/DOxpWcP.png) **因為資料表讀出來的內容為null但model格式是設定為int** ![資料表內容null](https://i.imgur.com/unjERme.png) ![Model](https://i.imgur.com/PPZrr57.png) 因此model可以做以下修改: ?=>設定為可空類型 ```csharp= public class Company: BaseModel { public string Name { get; set; } public DateTime CreateTime { get; set; } public int CreatorId { get; set; } public int? LastModifierId { get; set; } public DateTime? LastModifyTime { get; set; } } ``` 並且要修改一下讀取資料庫時候的判斷 ```csharp= foreach (var prop in type.GetProperties()) { prop.SetValue(t, reader[prop.Name] is DBNull ? null : reader[prop.Name]); } ``` 再來實作一下,**一個是查詢出數據表的全部數據列表查詢(沒有參數)** ```csharp= public T FindAll<T>() where T:BaseModel { Type type = typeof(T); string columnString =string.Join(",",type.GetProperties().Select(p=>$"[{p.Name}]")); string sql =$"SELECT{columnString} FROM[{type.Name}]"; using(SqlConnection conn = new SqlConnection(StaticConstant.SqlServerConnString)) { SqlCommand command = new SqlCommand(sql,conn); conn.Open(); SqlDataReader reader = command.ExecuteReader(); Lit<T> list =new List<T>(); While(reader.Read())//表示有資料 開始讀 { T t =(T)Activator.CreateInstance(type); foreach (var prop in type.GetProperties()) { prop.SetValue(t, reader[prop.Name]); } list.add(t); } } return list; } ``` **準備來優化一下程式碼** 增加一個私有方法: ```csharp= private List<T> ReaderToList<T>(SqlDataReader reader)where T : BaseModel { Type type = typeof(T); List<T> list = new List<T>(); while (reader.Read())//表示有資料 開始讀 { T t = (T)Activator.CreateInstance(type); foreach (var prop in type.GetProperties()) { object oValue = reader[prop.name()]; if (oValue is DBNull) oValue = null; prop.SetValue(t, oValue);//除了guid和列舉 } list.Add(t); } return list; } ``` 可以改造一下Find跟FindAll方法 ![](https://i.imgur.com/rWwkqDJ.png) ## mapping映射處理 **特性可以在不破壞物件的情況下增加一點訊息** **我們在Framework裡面中增加特性** ```csharp= [AttributeUsage(AttributeTargets.Property)] public class ColumAttribute:Attribute { public ColumnAttribute(string name) { this._Name = name; } private string _Name = null; public string GetColumnName() { return _Name; } } ``` 增加一個AttributeHelper 用來取得Attribute的方法 ```csharp= public class AttributeHelper { public static string GetColumnName(this PropertyInfo prop) { if(prop.IsDefined(typeof(ColumnAttribute),true)) { ColumnAttribute attribute = (ColumnAttribute) prop.GetCustomAttribute(typeof(ColumnAttribute),true); return attribute.GetColumnName(); } return prop.Name; } } ``` 再來改造一下 **Find跟FindAll方法以及ReaderToList** 將`{p.Name}` 替換成 `{p.GetColumnName()}` ## 新增、刪除、修改 5.進階需求:提供泛型的數據庫實體插入、實體更新、ID刪除數據的數據庫訪問方法 * 在BaseDal中加入Update,在這邊組SQL語法,**必須參數化** * columnString 必須參數化,不能直接塞值,不然會有 sql injection * ` p.GetValue(t) ?? DBNull.Value` =>判斷目前的變數是否為NULL 如果是就放入 DBNull.Value 如果直接放入NULL進去是會報錯誤 columnString 希望產生得格式: ```sql= [Name]=@Name,[CreateTime]=@CreateTime,[CreatorId]=@CreatorId,[LastModifierId]=@LastModifierId,[LastModifyTime]=@LastModifyTime ``` ```csharp= public void Update<T>(T t)where T:BaseModel { Type type =typeof(T); var propArray = type.GetProperties().Where(p => !p.Name.Equals("Id")); string columnString = string.Join(",", propArray.Select(p => $"[{p.GetColumnName()}]=@{p.GetColumnName()}")); var parameters = propArray.Select(p => new SqlParameter($"@{p.GetColumnName()}", p.GetValue(t) ?? DBNull.Value)).ToArray(); string sql =$"UPDATE{type.Name} SET{columnString} WHERE Id={t.id}"; using (SqlConnection conn = new SqlConnection(StaticConstant.SqlServerConnString)) { using (SqlCommand command = new SqlCommand(sql, conn)) { command.Parameters.AddRange(parameters); conn.Open(); int iResult = command.ExecuteNonQuery(); if (iResult == 0) throw new Exception("Update數據不存在"); } } } ``` 6 進階需求(可選):將數據訪問層抽象,使用簡單工廠+配置文件+反射的方式,來提供對數據訪問層的使用 在此先新增一個類別庫叫Factory 新增類別叫做DALFactory ```csharp= public class DALFactory { static DALFactory() { Assembly assembly = Assembly.Load(StaticConstant.DALDllName); DALType = assembly.GetType(StaticConstant.DALTypeName); } private static Type DALType = null; public static BaseDAL CreateInstance() { return (IBaseDAL)Activator.CreateInstance(DALType); } } ``` 在StaticConstant類別中 增加 ```csharp= private static string DALTypeDll = ConfigurationManager.AppSettings["DALTypeDll"]; public static string DALDllName = DALTypeDll.Split(',')[1]; public static string DALTypeName = DALTypeDll.Split(',')[0]; ``` appSetting增加 ```xml= <appSettings> <add key="DALTypeDll" value="Homework001.DAL.BaseDAL,Homework001.DAL"/> </appSettings> ``` ## 特性通用驗證 7 進階需求(可選):按照課堂的例子,再增加幾個驗證特性如Required(非空) Mobile(手機號格式) Email(格式) 字符串長度(最大最小)等, 注意這時候一個屬性可能會有多個驗證屬性,比如Required+Email+長度 封裝成一個泛型的數據校驗方法,數據庫增刪改的時候,嘗試完成數據校驗 在Framework中加入LengAttribute、EmailAttribute、MobileAttribute..等等 * 建議每個驗證類別都創建新的類別 LengAttribute: ```csharp= [AttributeUsage(AttributeTargets.Property | AttributeTargets.Field)] public class LengAttribute : AbstractValidateAttribute { private int _Min = 0; private int _Max = 0; public LengAttribute(int min, int max) { this._Min = min; this._Max = max; } public override bool Validate(object value) { if (value != null && !string.IsNullOrWhiteSpace(value.ToString())) { int length = value.ToString().Length; if (length > this._Min && length < this._Max) { return true; } } return false; } } ``` EmailAttribute: ```csharp= [AttributeUsage(AttributeTargets.Property | AttributeTargets.Field)] public class EmailAttribute : AbstractValidateAttribute { public override bool Validate(object oValue) { return oValue != null && Regex.IsMatch(oValue.ToString(), @"^\w+([-+.]\w+)*@\w+([-.]\w+)*\.\w+([-.]\w+)*$"); } } ``` MobileAttribute: ```csharp= public class MobileAttribute : AbstractValidateAttribute { public override bool Validate(object oValue) { if (oValue == null) { return false; } else { return Regex.IsMatch(oValue.ToString(), @"^[1]+[3,5]+\d{9}"); } } } ``` RegexAttribute: ```csharp= [AttributeUsage(AttributeTargets.Property | AttributeTargets.Field)] public class RegexAttribute : AbstractValidateAttribute { private string _RegexExpression = string.Empty; public RegexAttribute(string regex) { this._RegexExpression = regex; } public override bool Validate(object oValue) { if (oValue == null) { return false; } else { return Regex.IsMatch(oValue.ToString(), _RegexExpression); } } } ``` 在AttributeHelper擴展方法中 在加入驗證的方法 ```csharp= public static bool Validate<T>(this T tModel) where T : BaseModel { Type type = tModel.GetType(); foreach (var prop in type.GetProperties()) { if (prop.IsDefined(typeof(AbstractValidateAttribute), true)) { object[] attributeArray = prop.GetCustomAttributes(typeof(AbstractValidateAttribute), true); foreach (AbstractValidateAttribute attribute in attributeArray) { if (!attribute.Validate(prop.GetValue(tModel))) { return false;//表示终止 //throw new Exception($"{prop.Name}的值{prop.GetValue(tModel)}不對"); } } } } return true; } ``` 當在這邊寫Validate的T作約束的時候,會發生**循環引用**的問題 ,所以我們將BaseModel移至Framework中 ![循環引用](https://i.imgur.com/tiGnxQY.png) ## 泛型緩存 在DAL中加入一個類別 TSqlHelper ```csharp= static TSqlHelper() { Type type = typeof(T); string columnString = string.Join(",", type.GetProperties().Select(p => $"[{p.GetColumnName()}]")); FindSql = $"SELECT {columnString} FROM [{type.Name}] WHERE Id="; FindAllSql = $"SELECT {columnString} FROM [{type.Name}];"; } public static string FindSql = null; public static string FindAllSql = null; ``` Find 修改成 ```csharp= public T Find<T>(int id) where T : BaseModel { Type type = typeof(T); string sql = $"{TSqlHelper<T>.FindSql}{id};"; T t = null; using (SqlConnection conn = new SqlConnection(StaticConstant.SqlServerConnString)) { SqlCommand command = new SqlCommand(sql, conn); conn.Open(); SqlDataReader reader = command.ExecuteReader(); List<T> list = this.ReaderToList<T>(reader); t = list.FirstOrDefault(); } return t; } ```