Common DataAccess database types, easy to use, powerful functions, and easy to use dataaccess

Common DataAccess database types, easy to use, powerful functions, and easy to use dataaccess

The following is my DataAccess generic database types, which are easy to use and support: creating multiple parameters inline, committing multiple transactions, reusing parameters, and changing database types, I hope this will help you. If you want to convert the data to an object after checking it out, you can expand datarow to an object class, or use dapper.net to implement more powerful functions.

/// <Summary> /// generic database category, supporting multiple databases without directly relying on a database component /// Author: Zuo Wenjun // Date: 2016-6-3 // </summary> public class DataAccess: IDisposable {private static DbProviderFactory dbProviderFactory = null; private static string connectionString = null; public static string ConnectionStringName = "default "; private DbConnection dbConnection = null; private DbTransaction dbTransaction = null; private bool useTransact Ion = false; private bool disposed = false; private bool committed = false; private ParameterHelperClass paramHelper = null; public DataAccess (): this (ConnectionStringName) {} public DataAccess (string cnnStringName) {if (! ConnectionStringName. equals (cnnStringName, StringComparison. ordinalIgnoreCase) | dbProviderFactory = null | connectionString = null) {ConnectionStringName = cnnStringName; var cnnStringSection = ConfigurationManager. connectionStrings [cnnStringName]; dbProviderFactory = DbProviderFactories. getFactory (cnnStringSection. providerName); connectionString = cnnStringSection. connectionString;} paramHe Lper = new ParameterHelperClass (this) ;}# region private method private DbConnection GetDbConnection () {if (dbConnection = null) {dbConnection = dbProviderFactory. createConnection (); dbConnection. connectionString = connectionString;} if (dbConnection. state = ConnectionState. closed) {dbConnection. open ();} if (useTransaction & dbTransaction = null) {dbTransaction = dbConnection. beginTransaction (); c Ommitted = false;} return dbConnection;} private DbCommand BuildDbCommand (string sql1_text, CommandType struct type = CommandType. text, DbParameter [] parameters = null) {var dbCmd = dbProviderFactory. createCommand (); var dbConn = GetDbConnection (); dbCmd. connection = dbConn; dbCmd. commandText = sql1_text; dbCmd. commandType = random type; if (useTransaction) {dbCmd. transaction = dbTransaction;} if (Parameters! = Null) {dbCmd. parameters. addRange (parameters);} return dbCmd;} private DbCommand BuildDbCommand (string sql1_text, CommandType struct type = CommandType. text, IDictionary <string, object> paramNameValues = null) {List <DbParameter> parameters = new List <DbParameter> (); if (paramNameValues! = Null) {foreach (var item in paramNameValues) {parameters. add (BuildDbParameter (item. key, item. value);} return BuildDbCommand (sql1_text, struct type, parameters. toArray ();} private DbCommand BuildDbCommand (string sql1_text, CommandType struct type = CommandType. text, params object [] paramObjs) {if (paramObjs! = Null) {if (paramObjs [0] is IDictionary <string, object>) {return BuildDbCommand (sql1_text, struct type, paramObjs [0] as IDictionary <string, object> );} else if (paramObjs is DbParameter []) {return BuildDbCommand (sql1_text, parameter type, paramObjs as DbParameter []);} else {List <DbParameter> parameters = new List <DbParameter> (); for (int I = 0; I <paramObjs. length; I ++) {parameters. add (BuildDbParame Ter ("@ p" + I. toString (), paramObjs [0]);} return BuildDbCommand (sql1_text, struct type, parameters. toArray () ;}} else {return BuildDbCommand (sql1_text, struct type, parameters: null) ;}} private void ClearCommandParameters (DbCommand cmd) {bool canClear = true; if (cmd. connection! = Null & cmd. Connection. State! = ConnectionState. Open) {foreach (DbParameter commandParameter in cmd. Parameters) {if (commandParameter. Direction! = ParameterDirection. input) {canClear = false; break ;}} if (canClear) {cmd. parameters. clear () ;}# endregion # region public method public void UseTransaction () {useTransaction = true;} public void Commit () {if (dbTransaction! = Null & useTransaction) {dbTransaction. commit (); dbTransaction. dispose (); dbTransaction = null; committed = true; useTransaction = false;} public DbParameter BuildDbParameter (string name, object value) {DbParameter parameter = dbProviderFactory. createParameter (); parameter. parameterName = name; parameter. value = value; return parameter;} public DbParameter BuildDbParameter (string name, obje Ct value, DbType dbType, ParameterDirection direction = ParameterDirection. input) {DbParameter parameter = dbProviderFactory. createParameter (); parameter. parameterName = name; parameter. value = value; parameter. dbType = dbType; parameter. direction = direction; return parameter;} public DbDataReader ExecuteReader (string sql1_text, CommandType primitive type = CommandType. text, params object [] paramObjs) {Var dbCmd = BuildDbCommand (sql1_text, struct type, paramObjs); var dr = dbCmd. executeReader (); ClearCommandParameters (dbCmd); return dr;} public T ExecuteScalar <T> (string sql1_text, CommandType 1_type = CommandType. text, params object [] paramObjs) {T returnValue = default (T); var dbCmd = BuildDbCommand (sql1_text, struct type, paramObjs); object result = dbCmd. executeScalar (); try {returnValue = (T) Convert. changeType (result, typeof (T);} catch {} ClearCommandParameters (dbCmd); return returnValue;} public DataSet ExecuteDataSet (string sql1_text, CommandType struct type = CommandType. text, params object [] paramObjs) {var dbCmd = BuildDbCommand (sql1_text, callback type, paramObjs); var dbAdapter = dbProviderFactory. createDataAdapter (); dbAdapter. selectCommand = dbCmd; DataSet returnDataSet = ne W DataSet (); dbAdapter. fill (returnDataSet); ClearCommandParameters (dbCmd); return returnDataSet;} public DataTable ExecuteDataTable (string sql1_text, CommandType struct type = CommandType. text, params object [] paramObjs) {DataTable returnTable = new DataTable (); DataSet resultDataSet = ExecuteDataSet (sqlCmdText, struct type, paramObjs); if (resultDataSet! = Null & resultDataSet. tables. count> 0) {returnTable = resultDataSet. tables [0];} return returnTable;} public int ExecuteCommand (string sql1_text, CommandType struct type = CommandType. text, params object [] paramObjs) {var dbCmd = BuildDbCommand (sql1_text, callback type, paramObjs); int execResult = dbCmd. executeNonQuery (); ClearCommandParameters (dbCmd); return execResult;} public void Dispose () {Di Spose (true); GC. SuppressFinalize (this) ;}# endregion private void Dispose (bool disposing) {if (! Disposed) {if (disposing) {// release managed resources} if (dbTransaction! = Null) {if (! Committed) {dbTransaction. Rollback ();} dbTransaction. Dispose ();} if (dbConnection! = Null) {if (dbConnection. State! = ConnectionState. Closed) {dbConnection. Close ();} dbConnection. Dispose ();} disposed = true ;}}~ DataAccess () {Dispose (false);} public ParameterHelperClass ParameterHelper {get {return paramHelper;} public class ParameterHelperClass {private List <DbParameter> parameterList = null; private DataAccess parent = null; public ParameterHelperClass (DataAccess da) {parent = da; parameterList = new List <DbParameter> ();} public ParameterHelperClass AddParameter (string name, object value) {parameterList. add (parent. buildDbParameter (name, value); return this;} public ParameterHelperClass AddParameter (string name, object value, DbType dbType, ParameterDirection direction = ParameterDirection. input) {parameterList. add (parent. buildDbParameter (name, value, dbType, direction); return this;} public ParameterHelperClass AddParametersWithValue (params object [] paramValues) {for (int I = 0; I <paramValues. length; I ++) {parameterList. add (parent. buildDbParameter ("@ p" + I. toString (), paramValues [0]);} return this;} public DbParameter [] ToParameterArray () {var paramList = parameterList; parameterList = new List <DbParameter> (); return paramList. toArray ();}}}

Flexible usage. The sample code is as follows:

Usage 1: Create a parameter array object in inline mode, and then execute the SQL command

Using (DataAccess da = new DataAccess () {var programInfo = new ProgramInfo () {Name = "test", Version = "1.0", InstalledLocation = AppDomain. currentDomain. baseDirectory}; var parameters = da. parameterHelper. addParameter ("@ Mbno", "1882316708 *"). addParameter ("@ Msg", string. format ("program name: {0}, version: {1}, installation path: {2}, stopped. Please process it as soon as possible! ", ProgramInfo. name, programInfo. version, programInfo. installedLocation )). addParameter ("@ SendTime", DateTime. now ). addParameter ("@ KndType", "monitoring exception notification "). toParameterArray (); da. executeCommand ("insert into OutBox (Mbno, Msg, SendTime, KndType) values (@ Mbno, @ Msg, @ SendTime, @ KndType)", paramObjs: parameters );}

Usage 2: commit using transactions on the basis of usage 1

Using (DataAccess da = new DataAccess () {var programInfo = new ProgramInfo () {Name = "test", Version = "1.0", InstalledLocation = AppDomain. currentDomain. baseDirectory}; var parameters = da. parameterHelper. addParameter ("@ Mbno", "1882316708 *"). addParameter ("@ Msg", string. format ("program name: {0}, version: {1}, installation path: {2}, stopped. Please process it as soon as possible! ", ProgramInfo. name, programInfo. version, programInfo. installedLocation )). addParameter ("@ SendTime", DateTime. now ). addParameter ("@ KndType", "monitoring exception notification "). toParameterArray (); da. useTransaction (); da. executeCommand ("insert into OutBox (Mbno, Msg, SendTime, KndType) values (@ Mbno, @ Msg, @ SendTime, @ KndType)", paramObjs: parameters); da. commit ();}

Usage 3: Use transactions to execute multiple SQL commands at a time on the basis of usage 2

Using (DataAccess da = new DataAccess () {var programInfo = new ProgramInfo () {Name = "test", Version = "1.0", InstalledLocation = AppDomain. currentDomain. baseDirectory}; var parameters = da. parameterHelper. addParameter ("@ Mbno", "1882316708 *"). addParameter ("@ Msg", string. format ("program name: {0}, version: {1}, installation path: {2}, stopped. Please process it as soon as possible! ", ProgramInfo. name, programInfo. version, programInfo. installedLocation )). addParameter ("@ SendTime", DateTime. now ). addParameter ("@ KndType", "monitoring exception notification "). toParameterArray (); da. useTransaction (); da. executeCommand ("insert into OutBox (Mbno, Msg, SendTime, KndType) values (@ Mbno, @ Msg, @ SendTime, @ KndType)", paramObjs: parameters); da. executeCommand ("insert into OutBox (Mbno, Msg, SendTime, KndType) values (@ Mbno, @ Msg, @ SendTime, @ KndType)", paramObjs: parameters); da. commit ();}

Usage 4: On the basis of usage 1, multiple transactions are used to execute multiple SQL statements and submit them multiple times.

Using (DataAccess da = new DataAccess () {var programInfo = new ProgramInfo () {Name = "test", Version = "1.0", InstalledLocation = AppDomain. currentDomain. baseDirectory}; var parameters = da. parameterHelper. addParameter ("@ Mbno", "1882316708 *"). addParameter ("@ Msg", string. format ("program name: {0}, version: {1}, installation path: {2}, stopped. Please process it as soon as possible! ", ProgramInfo. name, programInfo. version, programInfo. installedLocation )). addParameter ("@ SendTime", DateTime. now ). addParameter ("@ KndType", "monitoring exception notification "). toParameterArray (); da. useTransaction (); da. executeCommand ("insert into OutBox (Mbno, Msg, SendTime, KndType) values (@ Mbno, @ Msg, @ SendTime, @ KndType)", paramObjs: parameters); da. commit (); da. useTransaction (); da. executeCommand ("insert into OutBox (Mbno, Msg, SendTime, KndType) values (@ Mbno, @ Msg, @ SendTime, @ KndType)", paramObjs: parameters); da. commit ();}

Usage 5: Transaction submission + SQL command Query

Using (DataAccess da = new DataAccess () {var programInfo = new ProgramInfo () {Name = "test", Version = "1.0", InstalledLocation = AppDomain. currentDomain. baseDirectory}; var parameters = da. parameterHelper. addParameter ("@ Mbno", "1882316708 *"). addParameter ("@ Msg", string. format ("program name: {0}, version: {1}, installation path: {2}, stopped. Please process it as soon as possible! ", ProgramInfo. name, programInfo. version, programInfo. installedLocation )). addParameter ("@ SendTime", DateTime. now ). addParameter ("@ KndType", "monitoring exception notification "). toParameterArray (); da. useTransaction (); da. executeCommand ("insert into OutBox (Mbno, Msg, SendTime, KndType) values (@ Mbno, @ Msg, @ SendTime, @ KndType)", paramObjs: parameters); da. commit (); parameters = da. parameterHelper. addParameter ("@ Mbno", "18823167089 "). toParameterArray (); var table = da. executeDataTable ("select Mbno, Msg, SendTime, KndType from OutBox where Mbno = @ Mbno", paramObjs: parameters); System. windows. forms. messageBox. show (table. rows. count. toString ());}

Usage 6: parameters are directly input when running SQL commands instead of being created in inline mode.

Using (DataAccess da = new DataAccess () {var programInfo = new ProgramInfo () {Name = "test", Version = "1.0", InstalledLocation = AppDomain. currentDomain. baseDirectory}; da. executeCommand ("insert into OutBox (Mbno, Msg, SendTime, KndType) values (@ Mbno, @ Msg, @ SendTime, @ KndType)", System. data. commandType. text, new Dictionary <string, object >{{" @ Mbno "," 1882316708 * "},{" @ Msg ", string. format ("program name: {0}, version This: {1}, installation path: {2}, stopped. Please handle it as soon as possible! ", ProgramInfo. name, programInfo. version, programInfo. installedLocation)}, {"@ SendTime", DateTime. now },{ "@ KndType", "monitoring exception notification" }}); var table = da. executeDataTable ("select Mbno, Msg, SendTime, KndType from OutBox where Mbno = @ p0", System. data. commandType. text, "18823167089" // If the input value array is used directly, the parameter placeholder in the SQL command must be defined as @ p0, @ p1 ...); system. windows. forms. messageBox. show (table. rows. count. toString ());}

Usage 7: Besides using DataAccess. the AddParameter (string name, object value) method of the ParameterHelper attribute is used to create parameters. You can also use AddParameter (string name, object value, DbType dbType, ParameterDirection direction = ParameterDirection. and AddParametersWithValue (params object [] paramValues) to create parameters based on the value array.

To change the database type, you only need to add related connection subnodes to the connectionStrings node in the configuration file. Pay attention to the providerName feature. Common providerName features are as follows:

Aceess Database: providerName = "System. Data. OleDb"

Oracle Database: providerName = "System. Data. OracleClient" or providerName = "Oracle. DataAccess. Client"

SQLite Database: providerName = "System. Data. SQLite"

SQL SERVER database: providerName = "System. Data. SqlClient"

MYSQL database: providerName = "MySql. Data. MySqlClient"

ODBC connection to the database: providerName = "System. Data. Odbc"