DataAccess Universal Database access class, easy to use, powerful

The following is the DataAccess Universal database access Class I wrote, simple and easy to use, support: inline create multiple parameters, support multi-transactional submissions, support parameter reuse, support replacement database type, hope to help everyone, if you need to support the check out after conversion to entity, you can expand the DataRow to entity class, You can also combine with dapper.net for more powerful features.

    <summary>///General database access classes, support for multiple databases, no directly dependent on a database component///Zoe Wenjun///Date: 2016-6-3//</summary> Publ        IC 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 usetransaction = 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;        } paramhelper = new Parameterhelperclass (this);            } #region Private DbConnection getdbconnection () {if (DbConnection = = null)                {dbConnection = Dbproviderfactory.createconnection ();            dbconnection.connectionstring = ConnectionString;            } if (dbconnection.state = = connectionstate.closed) {dbconnection.open (); } if (usetransaction && dbtransaction = = null) {dbtransaction = Dbconnecti On.                BeginTransaction ();            Committed = false;        } return dbConnection; } Private DbCommand Builddbcommand (String sqlCmdtext, CommandType cmdtype = CommandType.Text, dbparameter[] parameters = null) {var dbcmd = Dbprovid            Erfactory.createcommand ();            var dbconn = getdbconnection ();            Dbcmd.connection = Dbconn;            Dbcmd.commandtext = Sqlcmdtext;            Dbcmd.commandtype = Cmdtype;            if (usetransaction) {dbcmd.transaction = dbtransaction;            } if (Parameters! = null) {dbCmd.Parameters.AddRange (parameters);        } return dbcmd; } Private DbCommand Builddbcommand (string sqlcmdtext, commandtype cmdtype = 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 (Sqlcmdtext, cmdtype, parameters.        ToArray ()); } Private DbCommand Builddbcommand (string sqlcmdtext, commandtype cmdtype = CommandType.Text, params object[] param OBJS) {if (PARAMOBJS! = null) {if (Paramobjs[0] is idictionary<string, OB ject>) {return Builddbcommand (Sqlcmdtext, Cmdtype, Paramobjs[0] as Idictionary<st                Ring, object>); } else if (Paramobjs is dbparameter[]) {return Builddbcommand (sqlcmdtext                , Cmdtype, Paramobjs as dbparameter[]); } else {list<dbparameter> parameters = new List<dbparameter>                    (); for (int i = 0; i < paramobjs.length; i++) {parameters. ADD (Builddbparameter ("@p" + i.tostring (), PARAMOBJS[0])); } return Builddbcommand (Sqlcmdtext, cmdtype, parameters.                ToArray ());            }} else {return Builddbcommand (Sqlcmdtext, Cmdtype, 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 publicly 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, object value, DbType DbType, parameterdirection direction = Para            Meterdirection.input) {DbParameter parameter = Dbproviderfactory.createparameter (); Parameter.            ParameterName = name; Parameter.            Value = value; Parameter.            DbType = DbType; Parameter.          Direction = Direction;  return parameter; } Public DbDataReader ExecuteReader (string sqlcmdtext, commandtype cmdtype = CommandType.Text, params object[] Para            MOBJS) {var dbcmd = Builddbcommand (Sqlcmdtext, Cmdtype, PARAMOBJS);            var dr = Dbcmd.executereader ();            Clearcommandparameters (dbcmd);        Return Dr; Public T executescalar<t> (string sqlcmdtext, commandtype cmdtype = CommandType.Text, params object[] Paramo            BJS) {T returnvalue = default (t);            var dbcmd = Builddbcommand (Sqlcmdtext, Cmdtype, PARAMOBJS);            Object result = Dbcmd.executescalar ();            try {returnvalue = (t) convert.changetype (result, typeof (T));            } catch {} clearcommandparameters (dbcmd);        Return returnvalue; Public DataSet ExecuteDataset (string sqlcmdtext, commandtype cmdtype = CommandType.Text, params object[] Paramobj      S  {var dbcmd = Builddbcommand (Sqlcmdtext, Cmdtype, PARAMOBJS);            var dbadapter = Dbproviderfactory.createdataadapter ();            Dbadapter.selectcommand = dbcmd;            DataSet Returndataset = new DataSet ();            Dbadapter.fill (Returndataset);            Clearcommandparameters (dbcmd);        return returndataset; Public DataTable executedatatable (string sqlcmdtext, commandtype cmdtype = CommandType.Text, params object[] Para            MOBJS) {DataTable returntable = new DataTable ();            DataSet Resultdataset = ExecuteDataset (Sqlcmdtext, Cmdtype, PARAMOBJS); if (resultdataset! = null && resultDataSet.Tables.Count > 0) {returntable = Resultda            Taset.tables[0];        } return returntable;        } public int ExecuteCommand (string sqlcmdtext, commandtype cmdtype = CommandType.Text, params object[] paramobjs) {var dbcmd = Builddbcommand(Sqlcmdtext, Cmdtype, PARAMOBJS);            int execresult = Dbcmd.executenonquery ();            Clearcommandparameters (dbcmd);        return execresult;            public void Dispose () {Dispose (true); Gc.        SuppressFinalize (this);                } #endregion private void Dispose (bool disposing) {if (!disposed) { if (disposing) {//releases managed resources} if (dbtransaction! = null ) {if (!committed) {dbtransaction.rollback ()                    ;                } dbtransaction.dispose (); if (dbConnection! = null) {if (Dbconnection.state! = connectionstate.c                    losed) {dbconnection.close ();                } dbconnection.dispose (); }                disposed = true;        }} ~dataaccess () {Dispose (false); } public Parameterhelperclass Parameterhelper {get {return Paramhelpe            R }} public class Parameterhelperclass {private list<dbparameter> parameterlist = Nu            ll            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 directi On = ParameterDirection.Input) {Parameterlist.add (PARent.                Builddbparameter (name, value, DbType, direction));            return this; } public Parameterhelperclass Addparameterswithvalue (params object[] paramvalues) {f or (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 (); }        }    }

For a variety of flexible usages, use the sample code as follows:

Use one: Create a parameter array object inline, and then execute the SQL command

            using (dataaccess da = new DataAccess ())            {                var programinfo = new Programinfo () {name= "test", version= "1.0", Insta Lledlocation=appdomain.currentdomain.basedirectory};                 var parameters = da. Parameterhelper.addparameter ("@Mbno", "18823167089")                          . Addparameter ("@Msg", String. Format ("program name: {0}, Version: {1}, installation path: {2}, stopped running, please handle it ASAP!") ",                                        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 Two: Use transactions to commit on a usage basis

            using (dataaccess da = new DataAccess ())            {                var programinfo = new Programinfo () {Name = "Test", Version = "1.0", I Nstalledlocation = AppDomain.CurrentDomain.BaseDirectory};                 var parameters = da. Parameterhelper.addparameter ("@Mbno", "18823167089")                          . Addparameter ("@Msg", String. Format ("program name: {0}, Version: {1}, installation path: {2}, stopped running, please handle it ASAP!") ",                                        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 Three: Use a transaction to execute multiple SQL commands at once using a two-based usage

            using (dataaccess da = new DataAccess ()) {var programinfo = new Programinfo () {Nam                 E = "Test", Version = "1.0", installedlocation = AppDomain.CurrentDomain.BaseDirectory}; var parameters = da. Parameterhelper.addparameter ("@Mbno", "18823167089"). Addparameter ("@Msg", String. Format ("program name: {0}, Version: {1}, installation path: {2}, stopped running, please handle it ASAP!")                          ", 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 Four: Use multiple transactions on a usage basis to execute multiple SQL with multiple commits

            using (dataaccess da = new DataAccess ()) {var programinfo = new Programinfo () {Nam                E = "Test", Version = "1.0", installedlocation = AppDomain.CurrentDomain.BaseDirectory}; var parameters = da. Parameterhelper.addparameter ("@Mbno", "18823167089"). Addparameter ("@Msg", String. Format ("program name: {0}, Version: {1}, installation path: {2}, stopped running, please handle it ASAP!")                          ", 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 Five: Transaction commit +sql Command query

            using (dataaccess da = new DataAccess ()) {var programinfo = new Programinfo () {Nam                 E = "Test", Version = "1.0", installedlocation = AppDomain.CurrentDomain.BaseDirectory}; var parameters = da. Parameterhelper.addparameter ("@Mbno", "18823167089"). Addparameter ("@Msg", String. Format ("program name: {0}, Version: {1}, installation path: {2}, stopped running, please handle it ASAP!")                          ", 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 [email protected]", paramobjs:parameters); System.Windows.Forms.MessageBox.Show (table.            Rows.Count.ToString ()); }

Usage VI: Do not create parameters inline, but instead directly pass in parameters of each type when executing SQL commands

            using (dataaccess da = new DataAccess ()) {var programinfo = new Programinfo () {Nam                E = "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", "18823167089"}, {"@Msg", String. Format ("program name: {0}, Version: {1}, installation path: {2}, stopped running, please handle it ASAP!")                                    ", 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 [emAil protected] ", System.Data.CommandType.Text,                                            "18823167089"//If you are using an array of input values directly, the parameter placeholders in the SQL command must be defined as: @p0, @p1 ...                ); System.Windows.Forms.MessageBox.Show (table.            Rows.Count.ToString ()); }

Usage VII: In addition to the Addparameter (string name, object value) method that uses the Dataaccess.parameterhelper property above, you can also use Addparameter (string Name, object value, DbType DbType, parameterdirection direction = ParameterDirection.Input) to create a parameter specifying the input output and type, and Addparameterswithvalue (params object[] paramvalues) to create parameters based on the value array

If you need to change the database type, simply add the relevant connection subnodes in the connectionstrings node of the configuration file, note the ProviderName feature, providername commonly used 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 database: Providername= "SYSTEM.DATA.ODBC"

DataAccess Universal Database access class, easy to use and powerful