该教材主要是运用到泛型、反射和实体类扩展属性
步骤一、建立扩展属性类
实体类扩展属性要继承Attribute基类完成
1 [AttributeUsage(AttributeTargets.Property)] 2 public class FieldAttribute : Attribute 3 { 4 /// 5 ///是否为主键（true/false） 6 /// 7 public bool PropertyKey { get; set; } 8 /// 9 /// 是否为自动增长（true/false）10 /// 11 public bool Identity { get; set; }12 } 1 [AttributeUsage(AttributeTargets.Class)]2 public class TableAttribute:Attribute3 {4 /// 5 /// 数据库表名6 /// 7 public string TableName { get; set; }8 } 步骤二、创建实体类并引用扩展实体类属性
注意： [Serializable]表示实体类可被序列化
[Table(TableName = "emesc.Dome")]表示引入的扩展属性名并且数据库名为emesc.Dome
[Field(PropertyKey = true)]表示是否为主键
1 [Table(TableName = "emesc.Dome")] 2 [Serializable] 3 public class Dome 4 { 5 [Field(PropertyKey = true)] 6 public string EMP_NO { get; set; } 7 public string EMP_NAME { get; set; } 8 public string EMP_DESC { get; set; } 9 public string TYPE { get; set; }10 public string EMP_RANK { get; set; }11 public string EMP_PASS { get; set; }12 public object FACTORY_CODE { get; set; }13 public DateTime QUIT_DATE { get; set; }14 public object CALENDAR_CODE { get; set; }15 } 步骤三、创建BaseClass 该Class主要是用来封装ORM框架的通用方法（增、删、查、改）
1.反射出实体类所有字段属性
注意：反射实体类字段主要运用到PropertyInfo[]和泛型T
PropertyInfo[] 主要反射实体类字段属性
T 主要是用来传递实体类对象
1 private static string GetClassType(T modelName) { 2 try { 3 StringBuilder type= new StringBuilder(); 4 PropertyInfo[] props = modelName.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);//抓取泛型類的字段屬性; 5 foreach ( PropertyInfo Info in props) { 6 //if ((Info.GetValue(modelName,null) + "").Equals("")|| (Info.GetValue(modelName, null) + "").Equals(null)) continue; 7 //if (Info.PropertyType.Name.StartsWith("DateTime")) 8 //{ 9 // if (Convert.ToDateTime(Info.GetValue(modelName, null)) == DateTime.MinValue) continue;//判斷時間是否為最小時間 ;10 //}11 type.Append(Info.Name+",");12 }13 return type.ToString().Substring(0, Start.Length - 1);//拼接所有字段属性名14 }15 catch (Exception ex)16 {17 throw ex;18 }19 }
2.反射扩展类字段属性
1 ///2 /// 获取实体类中的唯一id和对应的表名 3 /// 4 ///5 /// 6 /// 7 public static List GetModelFeature (T modelName) { 8 try { 9 List list = new List ();10 var info= modelName.GetType().GetCustomAttributes(typeof(TableAttribute), false);11 foreach (var item in info)12 {13 TableAttribute attr = item as TableAttribute;14 if (attr != null)15 {16 string tableName = attr.TableName;//表名只有获取一次17 list.Add(tableName);18 break;19 }20 }21 PropertyInfo[] props = modelName.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);//抓取泛型類的字段屬性;22 foreach (var item in props)23 {24 object[] objAttrs = item.GetCustomAttributes(typeof(FieldAttribute), true);25 if (objAttrs.Length > 0)26 {27 FieldAttribute attr = objAttrs[0] as FieldAttribute;28 if (attr.PropertyKey == true)29 {30 list.Add(item.Name+" = '"+ item.GetValue(modelName,null)+"'");//抓取筛选条件31 }32 }33 }34 return list;35 }36 catch (Exception ex)37 {38 throw ex;39 }40 }
3. 编写通用增删查改方法 insert、update、delete、Query(select)方法
通用方法编写思维引导：当我们在对数据库进行操作时我们发现不管怎么对数据库操作都少不了表名、字段名和值 那么我们可以封装函数用来存储字段名和值
如：insert into 表名 (字段名) values(值)
update set 字段名=‘值’ from 表名 where 字段名=‘值’
delete 表名 where 字段名=‘值’
select 字段名 from 表名 where 字段名=‘值
存储字段名代码（核心代码）：
1 private static string GetNumericField(T modelName) 2 { 3 try 4 { 5 StringBuilder Start = new StringBuilder(); 6 PropertyInfo[] props = modelName.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);//抓取泛型類的字段屬性; 7 foreach (var item in props) 8 { 9 Start.Append(item.Name + ",");10 11 }12 return Start.ToString().Substring(0, Start.Length - 1);13 }14 catch (Exception ex)15 {16 throw ex;17 }18 }
存储值代码（核心代码）：
1 private static string GetValues(T modelName) 2 { 3 try { 4 StringBuilder End = new StringBuilder(); 5 PropertyInfo[] props = modelName.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);//抓取泛型類的字段屬性; 6 foreach (var item in props) 7 { 8 if ((item.GetValue(modelName, null) + "").Equals("") || (item.GetValue(modelName, null) + "").Equals(null)) continue; 9 if (item.PropertyType.Name.StartsWith("String"))//判斷字段類型10 {11 End.Append(" and " + item.Name + " = '" + item.GetValue(modelName, null) + "'");12 }13 else if (item.PropertyType.Name.StartsWith("DateTime"))//判斷字段的屬性是否為時間類型//判斷字段類型14 {15 if (Convert.ToDateTime(item.GetValue(modelName, null)) == DateTime.MinValue)16 {17 continue;18 }19 else20 {21 End.Append(" and " + item.Name + " = '" + item.GetValue(modelName, null) + "'");22 }23 }24 else25 {26 End.Append(" and " + item.Name + " = " + item.GetValue(modelName, null) + "");27 }28 29 }30 return End.ToString();31 }32 catch (Exception ex)33 {34 throw ex;35 }36 }
下图为完整版的通用增删改的代码：
1 ///2 /// 最低兼容.net 4.0（該ORM緊支持對單表操作） 3 /// 6 public class BaseFunction 7 { 8 DBHelper DB = new DBHelper(); 9 ///開發時間：2019/5/27 4 ///開發人員：会害羞的青蛙 5 ///10 /// 添加數據（數據錄入時必須將時間轉換為DateTime） 11 /// 12 ///13 /// 14 /// 15 /// 16 public static int Save (T modelName) { 17 try { 18 List list = GetModelFeature(modelName); 19 string dbName = list[0]; 20 StringBuilder SQL = new StringBuilder("insert into "); 21 StringBuilder End = new StringBuilder(); 22 SQL.Append(dbName + " ("+ GetSaveStart(modelName)+") values ("+ GetSaveEnd(modelName) + ")"); 23 return DBHelper.ExecuteNonQuery(SQL.ToString().Trim().ToUpper()); 24 } 25 catch (Exception ex) 26 { 27 throw ex; 28 } 29 } 30 /// 31 /// 添加數據（數據錄入時必須將時間轉換為DateTime） 32 /// 34 ///connectionString為自定義數據庫連接地址支持不同數據庫操作 33 ///35 /// 36 /// 37 /// 38 public static int Save (T modelName,string connectionString) 39 { 40 try 41 { 42 List list = GetModelFeature(modelName); 43 string dbName = list[0]; 44 StringBuilder SQL = new StringBuilder("insert into "); 45 StringBuilder End = new StringBuilder(); 46 SQL.Append(dbName + " (" + GetSaveStart(modelName) + ") values (" + GetSaveEnd(modelName) + ")"); 47 return DBHelper.ExecuteNonQuery(SQL.ToString().Trim().ToUpper(), connectionString); 48 } 49 catch (Exception ex) 50 { 51 throw ex; 52 } 53 } 54 /// 55 /// 根据主键ID修改數據（若没有主键请在实体类定义一个主键，數據編輯時必須將時間轉換為DateTime） 56 /// 57 ///58 /// 59 /// 60 /// 61 public static int Update (T modelName) 62 { 63 try { 64 List list = GetModelFeature(modelName); 65 string dbName = list[0];//數據庫名 66 StringBuilder SQL = new StringBuilder("update "+ dbName + " set "); 67 StringBuilder End = new StringBuilder(); 68 SQL.Append(GetEditStart(modelName) +" where " + list[1]); 69 return DBHelper.ExecuteNonQuery(SQL.ToString().Trim().ToUpper()); 70 } 71 catch (Exception ex) 72 { 73 throw ex; 74 } 75 } 76 /// 77 /// 根据主键ID修改數據（若没有主键请在实体类定义一个主键，數據編輯時必須將時間轉換為DateTime） 78 /// 80 ///connectionString為自定義數據庫連接地址支持不同數據庫操作 79 ///81 /// 82 /// 83 /// 84 public static int Update (T modelName, string connectionString) 85 { 86 try 87 { 88 List list = GetModelFeature(modelName); 89 string dbName = list[0];//數據庫名 90 StringBuilder SQL = new StringBuilder("update " + dbName + " set "); 91 StringBuilder End = new StringBuilder(); 92 SQL.Append(GetEditStart(modelName) + " where " + list[1]); 93 return DBHelper.ExecuteNonQuery(SQL.ToString().Trim().ToUpper(), connectionString); 94 } 95 catch (Exception ex) 96 { 97 throw ex; 98 } 99 }100 /// 101 /// 根据主键ID删除数据（若没有主键请在实体类定义一个主键）102 /// 103 ///104 /// 105 /// 106 public static int Delete (T modelName)107 {108 try { 109 List list = GetModelFeature(modelName);110 string dbName = list[0];//數據庫名111 string primaryKey = string.Empty;112 string primaryValue = string.Empty;113 PropertyInfo[] props = modelName.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);//抓取泛型類的字段屬性;114 foreach (var item in props)115 {116 object[] objAttrs = item.GetCustomAttributes(typeof(FieldAttribute), true);117 if (objAttrs.Length > 0)118 {119 FieldAttribute attr = objAttrs[0] as FieldAttribute;120 if (attr.PropertyKey == true)121 {122 primaryKey = item.Name;123 primaryValue = item.GetValue(modelName, null)+"";124 }125 }126 }127 if (primaryValue=="") { throw new Exception("主键不能为空！"); }128 StringBuilder SQL = new StringBuilder("delete " + dbName+ " where " + primaryKey+"='"+ primaryValue + "'");129 return DBHelper.ExecuteNonQuery(SQL.ToString().Trim().ToUpper());130 }131 catch (Exception ex)132 {133 throw ex;134 }135 }136 /// 137 /// 根据主键ID删除数据（若没有主键请在实体类定义一个主键）138 /// 140 ///connectionString為自定義數據庫連接地址支持不同數據庫操作 139 ///141 /// 142 /// 143 public static int Delete (T modelName, string connectionString)144 {145 try146 {147 List list = GetModelFeature(modelName);148 string dbName = list[0];//數據庫名149 string primaryKey = string.Empty;150 string primaryValue = string.Empty;151 PropertyInfo[] props = modelName.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);//抓取泛型類的字段屬性;152 foreach (var item in props)153 {154 object[] objAttrs = item.GetCustomAttributes(typeof(FieldAttribute), true);155 if (objAttrs.Length > 0)156 {157 FieldAttribute attr = objAttrs[0] as FieldAttribute;158 if (attr.PropertyKey == true)159 {160 primaryKey = item.Name;161 primaryValue = item.GetValue(modelName, null) + "";162 }163 }164 }165 if (primaryValue == "") { throw new Exception("主键不能为空！"); }166 StringBuilder SQL = new StringBuilder("delete " + dbName + " where " + primaryKey + "='" + primaryValue + "'");167 return DBHelper.ExecuteNonQuery(SQL.ToString().Trim().ToUpper(), connectionString);168 }169 catch (Exception ex)170 {171 throw ex;172 }173 }174 /// 175 /// 查询所有结果 modelName（实体类）176 /// 177 ///178 /// 179 /// 180 public static DataTable QueryList (T modelName) {181 try { 182 List list = GetModelFeature(modelName);183 string dbName = list[0];//數據庫名184 StringBuilder Start = new StringBuilder();185 Start.Append(GetQueryStart(modelName));186 StringBuilder End = new StringBuilder();187 End.Append(GetQueryEnd(modelName));188 string SQL = "SELECT " + Start + " FROM " + dbName + " WHERE 1=1" + End;189 return DBHelper.GetMultipleResults(SQL.ToString().Trim().ToUpper());190 }191 catch (Exception ex)192 {193 throw ex;194 }195 }196 /// 197 /// 查询所有结果 modelName（实体类）198 /// 200 ///connectionString為自定義數據庫連接地址支持不同數據庫操作 199 ///201 /// 202 /// 203 public static DataTable QueryList (T modelName, string connectionString)204 {205 try206 {207 List list = GetModelFeature(modelName);208 string dbName = list[0];//數據庫名209 StringBuilder Start = new StringBuilder();210 Start.Append(GetQueryStart(modelName));211 StringBuilder End = new StringBuilder();212 End.Append(GetQueryEnd(modelName));213 string SQL = "SELECT " + Start + " FROM " + dbName + " WHERE 1=1" + End;214 return DBHelper.GetMultipleResults(SQL.ToString().Trim().ToUpper(), connectionString);215 }216 catch (Exception ex)217 {218 throw ex;219 }220 }221 /// 222 /// 查询所有结果（包含分页）modelName（实体类），page（当前页），linmt（当前页的总条数），count（结果集总条数）223 /// 224 ///225 /// 226 /// 227 /// 228 /// 229 public static DataTable QueryList (T modelName, int page, int linmt ,out int count)230 {231 try {232 List list = GetModelFeature(modelName);233 string dbName = list[0];//數據庫名234 StringBuilder Start = new StringBuilder();235 Start.Append(GetQueryStart(modelName));236 StringBuilder StartToPagination = new StringBuilder();237 StartToPagination.Append(GetQueryStartToPagination(modelName));238 StringBuilder End = new StringBuilder();239 End.Append(GetQueryEnd(modelName));240 241 count = Convert.ToInt32(DBHelper.GetMultipleResults("SELECT COUNT(*) AS COUNTNUM FROM " + dbName+" WHERE 1=1 "+ End).Rows[0]["COUNTNUM"]);242 string SQL = "SELECT " + StartToPagination + " FROM (SELECT ROWNUM AS NUM ," + Start + " FROM " + dbName + " WHERE 1=1" + End + " AND ROWNUM<=" + linmt + "*" + page + ") WHERE NUM>" + linmt + "*(" + page + "-1) ";243 return DBHelper.GetMultipleResults(SQL.ToString().Trim().ToUpper());244 }245 catch (Exception ex) {246 throw ex;247 }248 }249 /// 250 /// 查询所有结果（包含分页）modelName（实体类），page（当前页），linmt（当前页的总条数），count（结果集总条数）251 /// 253 ///connectionString為自定義數據庫連接地址支持不同數據庫操作 252 ///254 /// 255 /// 256 /// 257 /// 258 public static DataTable QueryList (T modelName, string connectionString, int page, int linmt, out int count)259 {260 try261 {262 List list = GetModelFeature(modelName);263 string dbName = list[0];//數據庫名264 StringBuilder Start = new StringBuilder();265 Start.Append(GetQueryStart(modelName));266 StringBuilder StartToPagination = new StringBuilder();267 StartToPagination.Append(GetQueryStartToPagination(modelName));268 StringBuilder End = new StringBuilder();269 End.Append(GetQueryEnd(modelName));270 271 count = Convert.ToInt32(DBHelper.GetMultipleResults("SELECT COUNT(*) AS COUNTNUM FROM " + dbName + " WHERE 1=1 " + End).Rows[0]["COUNTNUM"]);272 string SQL = "SELECT " + StartToPagination + " FROM (SELECT ROWNUM AS NUM ," + Start + " FROM " + dbName + " WHERE 1=1" + End + " AND ROWNUM<=" + linmt + "*" + page + ") WHERE NUM>" + linmt + "*(" + page + "-1) ";273 return DBHelper.GetMultipleResults(SQL.ToString().Trim().ToUpper(), connectionString);274 }275 catch (Exception ex)276 {277 throw ex;278 }279 }280 /// 281 /// 获取实体类中的唯一id和对应的表名282 /// 283 ///284 /// 285 /// 286 public static List GetModelFeature (T modelName) {287 try {288 List list = new List ();289 var info= modelName.GetType().GetCustomAttributes(typeof(TableAttribute), false);290 foreach (var item in info)291 {292 TableAttribute attr = item as TableAttribute;293 if (attr != null)294 {295 string tableName = attr.TableName;//表名只有获取一次296 list.Add(tableName);297 break;298 }299 }300 PropertyInfo[] props = modelName.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);//抓取泛型類的字段屬性;301 foreach (var item in props)302 {303 object[] objAttrs = item.GetCustomAttributes(typeof(FieldAttribute), true);304 if (objAttrs.Length > 0)305 {306 FieldAttribute attr = objAttrs[0] as FieldAttribute;307 if (attr.PropertyKey == true)308 {309 list.Add(item.Name+" = '"+ item.GetValue(modelName,null)+"'");//抓取筛选条件310 }311 }312 }313 return list;314 }315 catch (Exception ex)316 {317 throw ex;318 }319 }320 /// 321 /// 獲取起始字段322 /// 323 ///324 /// 325 /// 326 private static string GetSaveStart (T modelName) {327 try {328 StringBuilder Start = new StringBuilder();329 PropertyInfo[] props = modelName.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);//抓取泛型類的字段屬性;330 foreach ( PropertyInfo Info in props) {331 if ((Info.GetValue(modelName,null) + "").Equals("")|| (Info.GetValue(modelName, null) + "").Equals(null)) continue;332 if (Info.PropertyType.Name.StartsWith("DateTime"))333 {334 if (Convert.ToDateTime(Info.GetValue(modelName, null)) == DateTime.MinValue) continue;//判斷時間是否為最小時間 ;335 }336 Start.Append(Info.Name+",");337 }338 return Start.ToString().Substring(0, Start.Length - 1);339 }340 catch (Exception ex)341 {342 throw ex;343 }344 }345 /// 346 /// 獲取字段對應的結果347 /// 348 ///349 /// 350 /// 351 private static string GetSaveEnd (T modelName)352 {353 try { 354 StringBuilder End = new StringBuilder();355 PropertyInfo[] props = modelName.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);//抓取泛型類的字段屬性;356 foreach (PropertyInfo Info in props)357 {358 if ((Info.GetValue(modelName, null) + "").Equals("") || (Info.GetValue(modelName, null) + "").Equals(null)) continue;359 360 if (Info.PropertyType.Name.StartsWith("String"))//判斷字段類型361 {362 End.Append("'" + Info.GetValue(modelName, null) + "',");363 }364 else if (Info.PropertyType.Name.StartsWith("DateTime"))//判斷字段類型365 {366 if (Convert.ToDateTime(Info.GetValue(modelName, null)) == DateTime.MinValue)//判斷時間是否為最小時間367 {368 continue;369 }370 else {371 End.Append("to_date('" + ((DateTime)(Info.GetValue(modelName, null))).ToString("yyyy-MM-dd HH:mm:ss") + "','YYYY-MM-DD HH24:MI:SS'),");372 }373 }374 else {375 End.Append(Info.GetValue(modelName, null) + ",");376 }377 }378 return End.ToString().Substring(0, End.Length - 1);379 }380 catch (Exception ex)381 {382 throw ex;383 }384 }385 /// 386 /// 重新定義分頁後的查詢字段並且格式化DateTime樣式387 /// 388 ///389 /// 390 /// 391 private static string GetQueryStartToPagination (T modelName) {392 try { 393 StringBuilder Start = new StringBuilder();394 PropertyInfo[] props = modelName.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);//抓取泛型類的字段屬性;395 foreach (var item in props)396 {397 if (item.PropertyType.Name.StartsWith("DateTime"))//判斷字段的屬性是否為時間類型398 {399 Start.Append("TO_CHAR(" + item.Name + ",'YYYY-MM-DD HH:mm:ss') AS " + item.Name + ",");//格式化時間400 }401 else {402 Start.Append(item.Name + ",");403 }404 405 }406 return Start.ToString().Substring(0, Start.Length - 1);407 }408 catch (Exception ex)409 {410 throw ex;411 }412 }413 private static string GetQueryStart (T modelName)414 {415 try416 {417 StringBuilder Start = new StringBuilder();418 PropertyInfo[] props = modelName.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);//抓取泛型類的字段屬性;419 foreach (var item in props)420 {421 Start.Append(item.Name + ",");422 423 }424 return Start.ToString().Substring(0, Start.Length - 1);425 }426 catch (Exception ex)427 {428 throw ex;429 }430 }431 private static string GetQueryEnd (T modelName)432 {433 try {434 StringBuilder End = new StringBuilder();435 PropertyInfo[] props = modelName.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);//抓取泛型類的字段屬性;436 foreach (var item in props)437 {438 if ((item.GetValue(modelName, null) + "").Equals("") || (item.GetValue(modelName, null) + "").Equals(null)) continue;439 if (item.PropertyType.Name.StartsWith("String"))//判斷字段類型440 {441 End.Append(" and " + item.Name + " = '" + item.GetValue(modelName, null) + "'");442 }443 else if (item.PropertyType.Name.StartsWith("DateTime"))//判斷字段的屬性是否為時間類型//判斷字段類型444 {445 if (Convert.ToDateTime(item.GetValue(modelName, null)) == DateTime.MinValue)446 {447 continue;448 }449 else450 {451 End.Append(" and " + item.Name + " = '" + item.GetValue(modelName, null) + "'");452 }453 }454 else455 {456 End.Append(" and " + item.Name + " = " + item.GetValue(modelName, null) + "");457 }458 459 }460 return End.ToString();461 }462 catch (Exception ex)463 {464 throw ex;465 }466 }467 /// 468 /// 编辑起始内容469 /// 470 ///471 /// 472 /// 473 private static string GetEditStart (T modelName) {474 try { 475 StringBuilder Start = new StringBuilder();476 PropertyInfo[] props = modelName.GetType().GetProperties(BindingFlags.Public | BindingFlags.Instance);//抓取泛型類的字段屬性;477 foreach (PropertyInfo Info in props)478 {479 object[] objAttrs = Info.GetCustomAttributes(typeof(FieldAttribute), true);480 if (objAttrs.Length > 0)481 {482 FieldAttribute attr = objAttrs[0] as FieldAttribute;483 if (attr.PropertyKey == true) continue;//判断自定义特性中的主键484 }485 if ((Info.GetValue(modelName, null) + "").Equals("") || (Info.GetValue(modelName, null) + "").Equals(null)) continue;486 487 if (Info.PropertyType.Name.StartsWith("String"))//判斷字段類型488 {489 Start.Append(Info.Name + "='" + Info.GetValue(modelName, null) + "',");490 }491 else if (Info.PropertyType.Name.StartsWith("DateTime"))//判斷字段類型492 {493 if (Convert.ToDateTime(Info.GetValue(modelName, null)) == DateTime.MinValue)//判斷時間是否為最小時間494 {495 continue;496 }497 else498 {499 Start.Append(Info.Name+"=to_date('" + ((DateTime)(Info.GetValue(modelName, null))).ToString("yyyy-MM-dd HH:mm:ss") + "','YYYY-MM-DD HH24:MI:SS'),");500 }501 }502 else {503 Start.Append(Info.Name + "=" + Info.GetValue(modelName, null) + ",");504 }505 }506 return Start.ToString().Substring(0, Start.Length - 1);507 }508 catch (Exception ex)509 {510 throw ex;511 }512 }513 }
警告：严禁以商用名义贩卖此代码
代码紧提供学习使用，若要转载此教程备注原创者：会害羞的青蛙