C # Operations SQL Server MYSQL Oracle Common Help Class

C # Operations SQL Server MYSQL Oracle Generic helper Class "Preface"

As a mature object-oriented high-level programming language, C # in the support of ADO is already very mature, we can easily invoke the ADO operation of various types of relational database, after many years of SQL_HELPER_DG, due to project needs, so, I'm going to write a mysql_helper. In the implementation process, the discovery of the completion of the ADO package, and object-oriented encapsulation, inheritance, polymorphism, with these features, why not the database operations packaged into a generic class, this article spread ...

"Implement Features"

The main topics to be covered in this article are as follows:

1, ADO. NET SQL Server 2, ADO. NET of Oracle 3, ADO. NET MySQL 4, make full use of object-oriented features, to achieve a common operation class "Environment preparation" 1, MySQL connector DLL reference

Use NuGet to search for Mysql.data references:

　　

2. DLL references for Oracle connectors

Use the NuGet search oracle.manageddataaccess to make a reference:

　　

"Realization Idea"

On the basis of the operating proficiency of sqlserver,oracle,mysql, we have gradually found that all operations are using the same set of things, the difference is:

SQL Server operations are using SqlConnection, Sqlcommand,sqldataadapter;

MySQL uses mysqlconnection, Mysqlcommand, Mysqldataadapter;

Oracle uses Oraclesqlconnection, OracleCommand, OracleDataAdapter;

The connection class, the operation classes are inherited from the basic class, respectively: DbConnection, DbCommand, DbDataAdapter;

Their Inter-class relationships:

1.DbConnection Family

　　

2.DbCommand Family

　　

3.DBDataAdapter Family

　　

Understanding the above characteristics, we can contact the concept of "polymorphic", we can use the same set of code, with the "polymorphic" characteristics of the different instances, so that we can further encapsulate our operations to achieve the purpose of code refining reusable.

"Implementation Process" 1. Define the enumeration class Opt_databasetype the database to use for the parameter selection of the specific instance

1 public enum Opt_databasetype2 {3         sqlserver,4         mysql,5         Oracle6}

2. Custom internal class Sqlconnection_wr_safe (Polymorphic provides DbConnection object, read/write separation support)

1. In this inner class, we define the class attribute dbconnection to undertake the corresponding connection based on the different database parameter polymorphism instantiation
2. Implement IDisposable interface, provide the method of releasing DbConnection
3. When the Read database connection fails, timely switch to read-write master database, improve the system availability

 1 internal class Sqlconnection_wr_safe:idisposable 2 {3//<summary> 4//Sqlconnectio N 5//</summary> 6 public DbConnection DbConnection {get; set;} 7 8 Public Sqlconnectio N_wr_safe (Opt_databasetype databasetype, string connstring_rw) 9 {this. DbConnection = Getdbconnection (DatabaseType, CONNSTRING_RW);}12/**13 * If read db DISABLED,SW Itchover to read write DB IMMEDIATELY14 * */15 public sqlconnection_wr_safe (Opt_databasetype databasetype , string connstring_r, String connstring_rw), {try18 {.             DbConnection = Getdbconnection (DatabaseType, connstring_r);}21 catch (Exception) 22 {this. DbConnection = Getdbconnection (DatabaseType, CONNSTRING_RW),}25}26///&LT;SUMMARY&GT;2 8//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                     {databasetype) (opt_databasetype.sqlserver:38) return new SqlConnection (connstring); opt_databasetype.mysql:40 case return n EW mysqlconnection (connstring); case opt_databasetype.oracle:42 return new Oraclecon             Nection (connstring); default:44 return new SqlConnection (connstring); 45          }46}47//<summary>48//Must Close Connection after use49//</summary>50 public void DisPose () (). DbConnection! = null) Dbconnection.dispose (); 55}56}57}

3. Custom inner class Dbcommandcommon to provide DbCommand objects

 1 internal class Dbcommandcommon:idisposable 2 {3//<summary> 4//Common DbCommand 5 </summary> 6 Public DbCommand DbCommand {get, set;} 7 public Dbcommandcommon (Opt_data BaseType DatabaseType) 8 {9 this. DbCommand = Getdbcommand (databasetype),}11//<summary>13//Get DbCommand Select Da Tabase type14//</summary>15//<param name= "DatabaseType" ></param>16//&lt             ; returns></returns>17 private DbCommand Getdbcommand (Opt_databasetype databasetype) 18 {19  Switch (databasetype) {opt_databasetype.sqlserver:22 return                 New SqlCommand (); opt_databasetype.mysql:24 return new Mysqlcommand (); 25 Case opt_databasetype.oracle:26 return new OracleCommand (); 27                 default:28 return new SqlCommand ();}30}31//<sum              MARY&GT;32//must dispose after use33//</summary>34 public void Dispose () 35 {36 if (this. DbCommand = null) PNS {. Dbcommand.dispose (); 39}40}41}

4. Custom internal class Dbdataadaptercommon is used to provide dbdataadapter

The class inherits from DbDataAdapter to implement the DataAdapter Fill method, and the result set can be populated into a dataset.

 1//<summary> 2//Dbdataadaptercommon 3//</summary> 4 internal class Dbdataadaptercomm On:dbdataadapter, IDisposable 5 {6 public dbdataadapter DbDataAdapter {get; set;} 7 public Dbdata             Adaptercommon (Opt_databasetype databasetype, DbCommand DbCommand) 8 {9//get dbAdapter10 This. DbDataAdapter = Getdbadapter (DatabaseType, DbCommand);//provid Select Command12 this. SelectCommand = dbcommand;13}14 private dbdataadapter getdbadapter (Opt_databasetype databasetype, DbComma nd DbCommand) (databasetype) (OPT_DATABASETYPE.SQLS)                     erver:19 return new SqlDataAdapter (); case opt_databasetype.mysql:21 return new Mysqldataadapter (); case opt_databasetype.oracle:23 return new Oracl            Edataadapter (); 24     default:25 return new SqlDataAdapter ();}27}28//<summary>             //must Dispose after use30//</summary>31 public new void Dispose () 32 {33 if (this. DbDataAdapter = null). Dbdataadapter.dispose (); 36}37}38}

5. When executing a SQL query, we use our custom internal classes to operate

>1 here take ExecuteNonQuery as an example:

　　　　

1 public static int ExecuteNonQuery (string commandtextorspname, commandtype commandtype = CommandType.Text) 2 {3     usin G (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}

The code uses the parameter databasetype to determine the type of database to instantiate, CONNSTRING_RW the connection string passed into the write database, and DbCommand is also the database object that we need to actually manipulate with the DatabaseType instance.
>2 Query ExecuteDataset Method:

　　

This method determines the specific dbconnection to be instantiated by the parameter databasetype, and selects the read and write libraries by reading and writing the separated connection strings.

1 public static DataSet ExecuteDataset (String commandtextorspname, commandtype commandtype = CommandType.Text) 2 {3     u Sing (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             {Ten                 DataSet ds = new DataSet ();                 Fill (DS),                 return ds;13             }14         }15     }16}

All code See this: 1, database Selector enumeration class:opt_databasetype->View Code2. Main Class Code db_helper_dg->DB_HELPER_DGDB_HELPER_DG Introduction:

This class is divided into ExecuteNonQuery, ExecuteScalar, ExecuteScalar, executedatatable, ExecuteDataset, Executelist Entity, Executeentity Seven the majority of each part is divided into unconditional parameters Execute SQL statements or stored procedures, sqlparameter[] Parameters Execute SQL statements, object[] Parameters execute stored procedure three overloaded methods.

The detailed code of the method is shown in the collapse section of the previous main code DB_HELPER_DG, where the executelistentity and Executeentity methods are highlighted.

Executelistentity and Executeentity, the two methods are used to map query results and Model-entity entities, using C # Reflection reflect technology to directly assign query results to an entity or list <Entity> objects (This is also the core of the ORM framework)

　　The Executelist method explicitly calls the Getlistfromdataset method through a two-pass package, traversing the results from the dataset result set for assignment, with the following code:

 1 public static list<entity> getlistfromdataset<entity> (DataSet ds) where Entity:class 2 {3 list<entity> list = new list<entity> ();//Instantiate a List Object 4 propertyinfo[] Propertyinfos = typeof (Entity).     GetProperties (); Gets all public properties of the T object 5 6 DataTable dt = ds.    Tables[0]; Gets the DT 7 if (dt) to the DS. Rows.Count > 0) 8 {9//determine if the read row is >0 i.e. the database data has been read by Dt. Rows) {Model1 = system.activator.createinstance<entity> ();//Instantiate an object,                         Easy to fill the list with data. foreach (PropertyInfo PropertyInfo in Propertyinfos) 14 {15 TRY16 {17//Traverse all fields in model I F (row[propertyinfo.name]! = System.DBNull.Value) 19 {20//judgment value is No null if empty assignment is null see ELse21 if (PropertyInfo.PropertyType.IsGenericType && propertyInfo.PropertyType.Get Generictypedefinition (). Equals (typeof (Nullable<>))) 22 {23//if Convertsio Ntype for the Nullable class, declares a Nullableconverter class that provides conversions from the nullable class to the underlying primitive type Nullableconverter nu Llableconverter = new Nullableconverter (propertyinfo.propertytype); 25//Will Convertsionty The base primitive type of PE converted to nullable is Propertyinfo.setvalue (Model1, Convert.changetype (row[propertyi Nfo. Name], nullableconverter.underlyingtype), null);}28 els e29 {propertyinfo.setvalue (Model1, convert.changety                   PE (Row[propertyinfo.name], propertyinfo.propertytype), null); 31}32          }33 Else34 {propertyi Nfo.                         SetValue (MODEL1, NULL, NULL);//If the value of the database is empty, the assignment is null36}37}38 catch (Exception) propertyinfo.setvalue (Model1, Nu ll, null);//If the value of the database is empty, the assignment is null41}42}43 list. Add (model1);//fills the object into List}45}46 return list;47}

　　The executeentity section is also divided into two ways to get the first one from the DataReader and LINQ from the List<entity>, because the DataReader has the feature of not releasing the connection, it is not friendly in the high Concurrency environment, Therefore, it is recommended to use the second type of LINQ for List<entity> in a real production environment:

 1 public static Entity getentityfromdatareader<entity> (DbDataReader reader) where Entity:class 2 {3           Entity model = system.activator.createinstance<entity> (); Instantiate an object of type T 4 propertyinfo[] Propertyinfos = model. GetType ().     GetProperties (); Gets all public properties of the T Object 5 using (reader) 6 {7 if (reader).                     Read ()) 8 {9 foreach (PropertyInfo PropertyInfo in Propertyinfos) 10 {11//Traverse all fields in the model if (reader[propertyinfo.name]! = System.DBNull.Value ) 13 {14//determine if the value is null if NULL assignment is ELSE15 if (pr OpertyInfo.PropertyType.IsGenericType && propertyInfo.PropertyType.GetGenericTypeDefinition (). Equals (typeof (Nullable<>))) 16 {17//if Convertsiontype is Nu Llable class, declaring a NullablecoThe Nverter class, which provides conversions from the nullable class to the underlying primitive type nullableconverter nullableconverter = new Nullableconv                                 Erter (propertyinfo.propertytype); 19//Convert Convertsiontype to nullable base primitive type 20 Propertyinfo.setvalue (model, Convert.changetype (reader[propertyinfo.name), Nullableconverter.underlyingt                                 YPE), null),}22 Else23 {24 Propertyinfo.setvalue (model, Convert.changetype (reader[propertyinfo.name), Propertyinfo.propertyty                         PE), null);}26}27 Else28                         {Propertyinfo.setvalue (model, NULL, NULL);//If the value of the database is empty, the value is NULL30 }31}32 return model;//returns the assigned object of type T model33}34} Return deFault (Entity);//Returns the default value of reference type and value type 0 or null36} 

1 public static Entity getentityfromdataset<entity> (DataSet ds) where Entity:class2         {3             return Getlistfromdataset<entity> (DS). FirstOrDefault (); 4         }

"System Test"

In the full functionality of the implementation, the following we carry out code testing links.

1. MySQL database operation

　　

Various ways to assign values to DB_HELPER_DG's link string properties are not mentioned here.

　　

Create a new entity class based on the design of the test table:

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     }

　　

After filling in the connection string and assigning a value to the Connstring_default property of the Db_helper_dg class, we call the method directly to perform the query operation.

　　

Call the static method executelist to map directly to the entity class:

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             }

Here's the MySQL statement select * FROM student where classid=? ClassId then parameterized assignment? Classid=1 the query.

The results are as follows:

　　

As you can see, the query results are automatically mapped to the attributes of the entity class without any mistake.

2. SQL Server database operation

　　

Because the structure of MySQL and SQL Server is consistent for the database structure, the entity class Tb_people above is used.

　　

Once the connection string is filled in, and the Connstring_default property of the DB_HELPER_DG class is assigned, we call the method directly to query the operation.

　　

　　

We then modify the SQL statement and modify the query for SQL Server pass parameter mode:

　　　

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}

The select * from Tb_people where ClassId =1,classid is passed in the way that SQL Server parameters are passed.

　　

As you can see, the query results are automatically mapped to the attributes of the entity class without any mistake.

3, Oracle due to my current Oracle environment problems, first do not test.

C # Operations SQL Server MYSQL Oracle Common Help Class