C#操作SqlServer MySql Oracle通用協助類

標籤：一個   ado.net   欄位   執行個體化   ble   系統   tables   param   row   

C#操作SqlServer MySql Oracle通用協助類【前言】

　　作為一款成熟的物件導向進階程式設計語言，C#在ADO.Net的支援上已然是做的很成熟，我們可以方便地調用ADO.Net操作各類別關係型資料庫，在使用了多年的Sql_Helper_DG後，由於項目需要，於是乎，就準備寫一個Mysql_Helper在實現過程中，發現ADO.Net封裝之完善，以及物件導向的封裝、繼承、多態，有了這些特性，何不把資料庫操作封裝成為一個通用的類呢，此文由此鋪展而來...

【實現功能】

　　這篇文章將要介紹的主要內容如下：

　　1、ADO.NET之SqlServer　　2、ADO.NET之Oracle　　3、ADO.NET之MySql　　4、充分利用物件導向的特徵，實現通用的操作類【環境準備】　　1、MySql連接器的DLL引用

　　使用Nuget搜尋 MySql.Data 引用即可：

　　

　　2、Oracle連接器的DLL引用

 　　使用Nuget搜尋 Oracle.ManagedDataAccess 進行引用：

　　

【實現思路】

　　在ADO.NET對SqlServer，Oracle，Mysql的操作熟練的基礎上，我們逐漸發現所有的操作都是使用的同一套的東西，不同的是:

　　SqlServer的操作使用的是SqlConnection、SqlCommand，SqlDataAdapter；

　　MySql使用的是MySqlConnection、MySqlCommand、MySqlDataAdapter；

　　Oracle使用的是OracleSqlConnection、OracleCommand、OracleDataAdapter；

　　該串連類，操作類都分別繼承自基礎類：DbConnection、DbCommand、DbDataAdapter；

　　其類間關係：

　　1.DbConnection家族

　　

　　2.DbCommand家族

　　

　　3.DBDataAdapter家族

　　

　　瞭解如上的幾個特點後，我們裡面能聯絡到了“多態”這個概念，我們可以使用同一套相同的代碼，用“多態”的特性執行個體化出不同的執行個體，進而可以進一步封裝我們的操作，達到代碼精鍊可重用的目的。

【實現過程】　　1.定義枚舉類 Opt_DataBaseType 用於參數選擇具體要執行個體的資料庫

1 public enum Opt_DataBaseType2 {3         SqlServer,4         MySql,5         Oracle6 }

　　2.自訂內部類SqlConnection_WR_Safe（多態提供DbConnection的對象、讀寫分離的支援）

　　1.在該內部類中，我們定義類屬性DbConnection用於承接根據不同的資料庫多重參數變形執行個體化後的對應Connection
　　2.實現IDisposable介面，提供釋放DbConnection的方法
　　3.在讀資料庫連接失敗時，及時切換到讀寫主要資料庫，提升系統的可用性

 1     internal class SqlConnection_WR_Safe : IDisposable 2     { 3         /// <summary> 4         /// SqlConnection 5         /// </summary> 6         public DbConnection DbConnection { get; set; } 7  8         public SqlConnection_WR_Safe(Opt_DataBaseType dataBaseType, string ConnString_RW) 9         {10             this.DbConnection = GetDbConnection(dataBaseType, ConnString_RW);11         }12         /**13          * if read db disabled,switchover to read write db immediately14          * */15         public SqlConnection_WR_Safe(Opt_DataBaseType dataBaseType, string ConnString_R, string ConnString_RW)16         {17             try18             {19                 this.DbConnection = GetDbConnection(dataBaseType, ConnString_R);20             }21             catch (Exception)22             {23                 this.DbConnection = GetDbConnection(dataBaseType, ConnString_RW);24             }25         }26 27         /// <summary>28         /// GetDataBase ConnectionString by database type and connection string -- private use29         /// </summary>30         /// <param name="dataBaseType"></param>31         /// <param name="ConnString"></param>32         /// <returns></returns>33         private DbConnection GetDbConnection(Opt_DataBaseType dataBaseType, string ConnString)34         {35             switch (dataBaseType)36             {37                 case Opt_DataBaseType.SqlServer:38                     return new SqlConnection(ConnString);39                 case Opt_DataBaseType.MySql:40                     return new MySqlConnection(ConnString);41                 case Opt_DataBaseType.Oracle:42                     return new OracleConnection(ConnString);43                 default:44                     return new SqlConnection(ConnString);45             }46         }47         /// <summary>48         /// Must Close Connection after use49         /// </summary>50         public void Dispose()51         {52             if (this.DbConnection != null)53             {54                 this.DbConnection.Dispose();55             }56         }57     }

　　3.自訂內部類 DbCommandCommon 用於提供DbCommand對象

 1     internal class DbCommandCommon : IDisposable 2     { 3         /// <summary> 4         /// common dbcommand 5         /// </summary> 6         public DbCommand DbCommand { get; set; } 7         public DbCommandCommon(Opt_DataBaseType dataBaseType) 8         { 9             this.DbCommand = GetDbCommand(dataBaseType);10         }11 12         /// <summary>13         /// Get DbCommand select database type14         /// </summary>15         /// <param name="dataBaseType"></param>16         /// <returns></returns>17         private DbCommand GetDbCommand(Opt_DataBaseType dataBaseType)18         {19             switch (dataBaseType)20             {21                 case Opt_DataBaseType.SqlServer:22                     return new SqlCommand();23                 case Opt_DataBaseType.MySql:24                     return new MySqlCommand();25                 case Opt_DataBaseType.Oracle:26                     return new OracleCommand();27                 default:28                     return new SqlCommand();29             }30         }31         /// <summary>32         /// must dispose after use33         /// </summary>34         public void Dispose()35         {36             if (this.DbCommand != null)37             {38                 this.DbCommand.Dispose();39             }40         }41     }

　　4.自訂內部類 DbDataAdapterCommon 用於提供DbDataAdapter

　　該類繼承自DbDataAdapter，以實現DataAdapter的Fill方法，可以將結果集填充到DataSet中去。

 1     /// <summary> 2     /// DbDataAdapterCommon 3     /// </summary> 4     internal class DbDataAdapterCommon : DbDataAdapter, IDisposable 5     { 6         public DbDataAdapter DbDataAdapter { get; set; } 7         public DbDataAdapterCommon(Opt_DataBaseType dataBaseType, DbCommand dbCommand) 8         { 9             //get dbAdapter10             this.DbDataAdapter = GetDbAdapter(dataBaseType, dbCommand);11             //provid select command12             this.SelectCommand = dbCommand;13         }14         private DbDataAdapter GetDbAdapter(Opt_DataBaseType dataBaseType, DbCommand dbCommand)15         {16             switch (dataBaseType)17             {18                 case Opt_DataBaseType.SqlServer:19                     return new SqlDataAdapter();20                 case Opt_DataBaseType.MySql:21                     return new MySqlDataAdapter();22                 case Opt_DataBaseType.Oracle:23                     return new OracleDataAdapter();24                 default:25                     return new SqlDataAdapter();26             }27         }28         /// <summary>29         /// must dispose after use30         /// </summary>31         public new void Dispose()32         {33             if (this.DbDataAdapter != null)34             {35                 this.DbDataAdapter.Dispose();36             }37         }38     }

 　　5.在執行Sql查詢的時候，我們便使用我們自訂的內部類進行操作

　　>1 這裡以ExecuteNonQuery為例：

　　　　

 1 public static int ExecuteNonQuery(string commandTextOrSpName, CommandType commandType = CommandType.Text) 2 { 3     using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_RW)) 4     { 5         using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType)) 6         { 7             PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType); 8             return cmd.DbCommand.ExecuteNonQuery(); 9         }10     }11 }

　　該代碼通過參數DataBaseType確定要執行個體化的資料庫類型，ConnString_RW傳入寫資料庫的連接字串進行執行個體化，DbCommand也是使用dataBaseType執行個體我們需要實際操作的資料庫物件。
　　>2 查詢ExecuteDataSet方法：

　　

　　該方法通過參數dataBaseType確定要執行個體化的具體DbConnection，通過讀寫分離的連接字串進行選擇讀庫和寫庫。

 1 public static DataSet ExecuteDataSet(string commandTextOrSpName, CommandType commandType = CommandType.Text) 2 { 3     using (SqlConnection_WR_Safe conn = new SqlConnection_WR_Safe(dataBaseType, ConnString_R, ConnString_RW)) 4     { 5         using (DbCommandCommon cmd = new DbCommandCommon(dataBaseType)) 6         { 7             PreparCommand(conn.DbConnection, cmd.DbCommand, commandTextOrSpName, commandType); 8             using (DbDataAdapterCommon da = new DbDataAdapterCommon(dataBaseType, cmd.DbCommand)) 9             {10                 DataSet ds = new DataSet();11                 da.Fill(ds);12                 return ds;13             }14         }15     }16 }

　全部代碼見此：　　1、資料庫選取器枚舉類：Opt_DataBaseType-> View Code　　2、主類代碼Db_Helper_DG-> Db_Helper_DG　　Db_Helper_DG簡介：

　　本類分為 ExecuteNonQuery、ExecuteScalar、ExecuteScalar、ExecuteDataTable、ExecuteDataSet、ExecuteList Entity、ExecuteEntity七大部分，每一部分分為 無條件參數執行Sql語句或預存程序、SqlParameter[]參數執行Sql語句，Object[]參數執行預存程序三個重載方法。

　　方法的詳細代碼見上一條主代碼Db_Helper_DG中摺疊部分，這裡對ExecuteListEntity和ExecuteEntity方法進行著重介紹。

　　ExecuteListEntity和ExecuteEntity，此二方法是為了將查詢結果和Model即Entity實體進行映射所用，使用C#反射Reflect技術，進行將查詢結果直接賦值成為了Entity或者List<Entity>對象（此亦是ORM架構的核心）

　　ExecuteList方法通過二次封裝，顯式調用GetListFromDataSet方法，從DataSet結果集中遍曆結果以進行賦值，代碼如下：

 1 public static List<Entity> GetListFromDataSet<Entity>(DataSet ds) where Entity : class 2         { 3             List<Entity> list = new List<Entity>();//執行個體化一個list對象 4             PropertyInfo[] propertyInfos = typeof(Entity).GetProperties();     //擷取T對象的所有公用屬性 5  6             DataTable dt = ds.Tables[0];    // 擷取到ds的dt 7             if (dt.Rows.Count > 0) 8             { 9                 //判斷讀取的行是否>0 即資料庫資料已被讀取10                 foreach (DataRow row in dt.Rows)11                 {12                     Entity model1 = System.Activator.CreateInstance<Entity>();//執行個體化一個對象，便於往list裡填充資料13                     foreach (PropertyInfo propertyInfo in propertyInfos)14                     {15                         try16                         {17                             //遍曆模型裡所有的欄位18                             if (row[propertyInfo.Name] != System.DBNull.Value)19                             {20                                 //判斷值是否為空白，如果空賦值為null見else21                                 if (propertyInfo.PropertyType.IsGenericType && propertyInfo.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))22                                 {23                                     //如果convertsionType為nullable類，聲明一個NullableConverter類，該類提供從Nullable類到基礎基元類型的轉換24                                     NullableConverter nullableConverter = new NullableConverter(propertyInfo.PropertyType);25                                     //將convertsionType轉換為nullable對的基礎基元類型26                                     propertyInfo.SetValue(model1, Convert.ChangeType(row[propertyInfo.Name], nullableConverter.UnderlyingType), null);27                                 }28                                 else29                                 {30                                     propertyInfo.SetValue(model1, Convert.ChangeType(row[propertyInfo.Name], propertyInfo.PropertyType), null);31                                 }32                             }33                             else34                             {35                                 propertyInfo.SetValue(model1, null, null);//如果資料庫的值為空白，則賦值為null36                             }37                         }38                         catch (Exception)39                         {40                             propertyInfo.SetValue(model1, null, null);//如果資料庫的值為空白，則賦值為null41                         }42                     }43                     list.Add(model1);//將對象填充到list中44                 }45             }46             return list;47         }

　　ExecuteEntity部分又分為從DataReader中擷取和Linq從List<Entity>擷取第一條進行擷取兩種方式，由於DataReader有佔用串連不釋放的特點，在高並發的環境下使用並不友好，因此在實際生產環境中使用推薦使用第二種Linq擷取List<Entity>的方式：

 1 public static Entity GetEntityFromDataReader<Entity>(DbDataReader reader) where Entity : class 2         { 3             Entity model = System.Activator.CreateInstance<Entity>();           //執行個體化一個T類型對象 4             PropertyInfo[] propertyInfos = model.GetType().GetProperties();     //擷取T對象的所有公用屬性 5             using (reader) 6             { 7                 if (reader.Read()) 8                 { 9                     foreach (PropertyInfo propertyInfo in propertyInfos)10                     {11                         //遍曆模型裡所有的欄位12                         if (reader[propertyInfo.Name] != System.DBNull.Value)13                         {14                             //判斷值是否為空白，如果空賦值為null見else15                             if (propertyInfo.PropertyType.IsGenericType && propertyInfo.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))16                             {17                                 //如果convertsionType為nullable類，聲明一個NullableConverter類，該類提供從Nullable類到基礎基元類型的轉換18                                 NullableConverter nullableConverter = new NullableConverter(propertyInfo.PropertyType);19                                 //將convertsionType轉換為nullable對的基礎基元類型20                                 propertyInfo.SetValue(model, Convert.ChangeType(reader[propertyInfo.Name], nullableConverter.UnderlyingType), null);21                             }22                             else23                             {24                                 propertyInfo.SetValue(model, Convert.ChangeType(reader[propertyInfo.Name], propertyInfo.PropertyType), null);25                             }26                         }27                         else28                         {29                             propertyInfo.SetValue(model, null, null);//如果資料庫的值為空白，則賦值為null30                         }31                     }32                     return model;//返回T類型的賦值後的對象 model33                 }34             }35             return default(Entity);//返回參考型別和實值型別的預設值0或null36         }

1 public static Entity GetEntityFromDataSet<Entity>(DataSet ds) where Entity : class2         {3             return GetListFromDataSet<Entity>(ds).FirstOrDefault();4         }

【系統測試】

 　　在全部功能實現之餘，下面我們進行代碼測試環節。

　　1、MySql資料庫操作

　　

　　各種方式給Db_Helper_DG的連結字串屬性進行賦值，這裡不再贅述。

 　　

　　

　　根據測試表的設計進行建立對應的實體類：

1 public class TB_People2     {3         public Guid Uid { get; set; }4         public string Name { get; set; }5         public int Age { get; set; }6         public int ClassId { get; set; }7     }

　　

　　填寫好連接字串，並給Db_Helper_DG類的ConnString_Default屬性賦值後，我們直接調用方法進行查詢操作。

　　

　　調用靜態方法ExecuteList以便直接映射到實體類：

1 List<TB_People> peopleList = Db_Helper_DG.ExecuteList<TB_People>("select * from student where ClassId=?ClassId", System.Data.CommandType.Text, new MySqlParameter("?ClassId", 1));2             foreach (var item in peopleList)3             {4                 Console.WriteLine(item.Name);5             }

 　　

　　這裡的MySql語句 select * from student where ClassId=?ClassId 然後參數化賦值 ?ClassId=1 進行查詢。

　　結果如下：

　　

　　可見，查詢結果並無任何差池，自動對應到了實體類的屬性。

　　2、SqlServer資料庫操作

　　

　　因為資料庫結構MySql和SqlServer的結構是一致的，因此使用上述的實體類TB_People。

　　

 　　同樣填寫連接字串，並給Db_Helper_DG類的ConnString_Default屬性賦值後，我們直接調用方法進行查詢操作。

　　

　　

　　然後我們修改Sql語句，並且修改為SqlServer傳遞參數方式進行查詢：

　　　

1 List<TB_People> peopleList = Db_Helper_DG.ExecuteList<TB_People>("select * from TB_People where [email protected]", System.Data.CommandType.Text, new SqlParameter("@ClassId", 1));2 foreach (var item in peopleList)3 {4     Console.WriteLine(item.Name);5 }

　　select * from TB_People where ClassId =1，ClassId按照SqlServer參數傳遞的方式進行傳遞。

　　

 　　可見，查詢結果並無任何差池，自動對應到了實體類的屬性。

　　3、Oracle由於本人當前Oracle環境問題，先不進行測試。

C#操作SqlServer MySql Oracle通用協助類