The omnipotent SqlHelper doesn't have to worry about any databases anymore.

The omnipotent SqlHelper doesn't have to worry about any databases anymore.

I used to use only one type of database. It didn't matter. But when I switched the database, I found that the code was similar.

At first, there were two types of databases, which were too big to write two SqlHelper databases. But when there were too many databases, we found that the code reuse rate was too low.

Therefore, the following SqlHelper was born.

Using System; using System. collections; using System. collections. generic; using System. data; using System. linq; using System. text; namespace WangSql. DBUtility {public static class SqlHelperExt {public static int AddRange (this IDataParameterCollection coll, IDataParameter [] par) {int I = 0; foreach (var item in par) {coll. add (item); I ++;} return I ;}# region SqlHelper public class SqlHelper {privat E IDbConnection conn = null; private IDbCommand cmd = null; private IDataReader dr = null; private DbType = DbType. NONE; # region create a database connection // <summary> // create a database connection // </summary> public SqlHelper (string connectionString) {conn = DBFactory. createDbConnection (type, connectionString );} # endregion # region judgment and enable conn /// <summary> /// judgment and enable conn /// </summary> /// <returns> </returns> public IDbConn Ection CreatConn () {if (conn. state = ConnectionState. closed) {conn. open ();} return conn ;} # endregion # region execute the query SQL statement // <summary> // execute the query SQL statement // </summary> /// <param name = "SQL"> Query SQL statement </param> /// <returns> returns a table </returns> public DataTable ExecuteReader (string SQL) {DataTable dt = new DataTable (); using (cmd = DBFactory. createDbCommand (SQL, CreatConn () {using (dr = cmd. executeReader () {Dt. load (dr) ;}} conn. close (); return dt ;} # endregion # region execute the SQL statement with parameters in the query /// <summary> /// execute the SQL statement with parameters in the query /// </summary> /// <param name = "SQL"> query SQL statements </param> /// <param name = "par"> parameters in SQL statements </param> /// <returns> return a table </returns> public DataTable ExecuteReader (string SQL, IDataParameter [] par) {DataTable dt = new DataTable (); using (cmd = DBFactory. createDbCommand (SQL, CreatConn () {cmd. pa Rameters. addRange (par); using (dr = cmd. executeReader () {dt. load (dr) ;}} conn. close (); return dt;} public DataTable ExecuteReader (string SQL, IDataParameter par) {DataTable dt = new DataTable (); using (cmd = DBFactory. createDbCommand (SQL, CreatConn () {cmd. parameters. add (par); using (dr = cmd. executeReader () {dt. load (dr) ;}} conn. close (); return dt ;}# endregion # add, delete, and modify the SQL statement /// <Summary> /// run the SQL statement without adding or deleting parameters. /// </summary> /// <param name = "SQL"> add, delete, modified SQL statement </param> /// <param name = "par"> parameters in the SQL statement </param> /// <returns> return the number of affected rows </ returns> public int ExecuteNonQuery (string SQL) {int result = 0; using (cmd = DBFactory. createDbCommand (SQL, CreatConn () {result = cmd. executeNonQuery ();} conn. close (); return result ;}# endregion # add, delete, and modify the SQL statement // <summary> // execute add with Parameters, Delete, modify the SQL statement // </summary> /// <param name = "SQL"> add, delete, modified SQL statement </param> /// <param name = "par"> parameters in the SQL statement </param> /// <returns> return the number of affected rows </ returns> public int ExecuteNonQuery (string SQL, IDbDataParameter [] par) {int result = 0; using (cmd = DBFactory. createDbCommand (SQL, CreatConn () {cmd. parameters. addRange (par); result = cmd. executeNonQuery ();} conn. close (); return result;} public int ExecuteNonQuer Y (string SQL, IDbDataParameter par) {int result = 0; using (cmd = DBFactory. createDbCommand (SQL, CreatConn () {cmd. parameters. add (par); result = cmd. executeNonQuery ();} conn. close (); return result ;}# endregion # region transaction // <summary> // execute multiple SQL statements to implement database transactions. /// </Summary> /// <param name = "SQLList"> hash table of SQL statements (key is an SQL statement, and value is the OleDbParameter [] of this statement) </param> public bool ExecuteTransaction (Hashtable SqlList) {CreatConn (); using (IDbTransaction trans = conn. beginTransaction () {IDbCommand cmd = DBFactory. createDbCommand (type); try {// loop foreach (DictionaryEntry myDE in SqlList) {string literal text = myDE. key. toString (); IDbDataParameter [] partition parms = (IDbData Parameter []) myDE. value; PrepareCommand (cmd, conn, trans, plain text, plain parms); int val = cmd. executeNonQuery (); cmd. parameters. clear ();} trans. commit ();} catch {trans. rollback (); return false;} finally {conn. close () ;}return true;} private void PrepareCommand (IDbCommand cmd, IDbConnection conn, IDbTransaction trans, string parameter text, IDataParameter [] parameter parms) {CreatConn (); cmd. connection = Conn; cmd. CommandText = plain text; if (trans! = Null) cmd. Transaction = trans; cmd. CommandType = CommandType. Text; // specify type; if (partition parms! = Null) cmd. Parameters. AddRange (partition parms) ;}# endregion }# endregion}

The above is the core code with an extension. The main reason is that the abstract class does not contain the AddRange method. The author is too reluctant to change the original method. It is also to be consistent with the original SqlHelper, and simply expands an AddRange.

Well, since it is all abstract parameters, in reality, we still need to instantiate a specific database instance, so we need a factory to create each database instance.

Using MySql. data. mySqlClient; using Oracle. dataAccess. client; using System. collections. generic; using System. data; using System. data. oleDb; using System. data. sqlClient; using System. data. SQLite; using System. linq; using System. text; namespace WangSql {public enum DbType {// Oracle, SqlServer, MySql, Access, SqlLite NONE, ORACLE, SQLSERVER, MYSQL, ACCESS, SQLLITE} public class DBFactory {public static IDbConnection CreateDbConnection (DbType type, string connectionString) {IDbConnection conn = null; switch (type) {case DbType. ORACLE: conn = new OracleConnection (connectionString); break; case DbType. SQLSERVER: conn = new SqlConnection (connectionString); break; case DbType. MYSQL: conn = new MySqlConnection (connectionString); break; case DbType. ACCESS: conn = new OleDbConnection (connectionString); break; case DbType. SQLLITE: conn = new SQLiteConnection (connectionString); break; case DbType. NONE: throw new Exception ("database type not set"); default: throw new Exception ("this database type is not supported");} return conn ;} public static IDbCommand CreateDbCommand (DbType type) {IDbCommand cmd = null; switch (type) {case DbType. ORACLE: cmd = new OracleCommand (); break; case DbType. SQLSERVER: cmd = new SqlCommand (); break; case DbType. MYSQL: cmd = new MySqlCommand (); break; case DbType. ACCESS: cmd = new OleDbCommand (); break; case DbType. SQLLITE: cmd = new SQLiteCommand (); break; case DbType. NONE: throw new Exception ("database type not set"); default: throw new Exception ("this database type is not supported");} return cmd ;} public static IDbCommand CreateDbCommand (string SQL, IDbConnection conn) {DbType type = DbType. NONE; if (conn is OracleConnection) type = DbType. ORACLE; else if (conn is SqlConnection) type = DbType. SQLSERVER; else if (conn is MySqlConnection) type = DbType. MYSQL; else if (conn is OleDbConnection) type = DbType. ACCESS; else if (conn is SQLiteConnection) type = DbType. SQLLITE; IDbCommand cmd = null; switch (type) {case DbType. ORACLE: cmd = new OracleCommand (SQL, (OracleConnection) conn); break; case DbType. SQLSERVER: cmd = new SqlCommand (SQL, (SqlConnection) conn); break; case DbType. MYSQL: cmd = new MySqlCommand (SQL, (MySqlConnection) conn); break; case DbType. ACCESS: cmd = new OleDbCommand (SQL, (OleDbConnection) conn); break; case DbType. SQLLITE: cmd = new SQLiteCommand (SQL, (SQLiteConnection) conn); break; case DbType. NONE: throw new Exception ("database type not set"); default: throw new Exception ("this database type is not supported") ;}return cmd ;}}}

 

Haha, even if you try another database, is it very easy.

By the way, there is a problem in the above SqlHelper Singleton mode. Please give me some suggestions.