C # implements the sample code for access universal Access class Oledbhelper

This article mainly describes the C # implementation of Access universal access Class Oledbhelper, combined with a complete example of C # for Access database connection, query, traverse, pagination display and other related operations skills, the need for friends can refer to the following

This example describes the C # implementation of Access universal access Class Oledbhelper. Share to everyone for your reference, as follows:

Recently in a project database with access, the first time to use the Access database, just started to do some bad, the database operation and SQL Server slightly different, and the exception tracking information is meaningless, after several days of repeated search for problems, finally solved some problems, In order to access a database, I wrote a class for specialized access to manipulate the database, including, executing database commands, returning a DataSet, returning a single record, returning a DataReader, a common paging method, and several common methods of operation. Please give your comments so that I can refine this class. Although the reference sqlhelper is much simpler than it is, all the code is as follows:

Using system;using system.collections;using system.collections.generic;using system.text;using System.Data;using  System.data.common;using system.data.oledb;namespace common{//<summary>//OLE DB Library Access class//</summary>    public static class Oledbhelper {//<summary>///Access database connection string format. </summary> Public Const string access_connectionstring_template = "Provider=Microsoft.Jet.OLEDB.4.0;Data Sou    RCE={0}; ";    Hashtable to store cached parameters private static Hashtable Parmcache = hashtable.synchronized (New Hashtable ());    <summary>///execute SQL statements against System.Data.OleDb.OleDbCommand.Connection and return the number of rows affected. </summary>//<param name= "connstring" ></param>//<param name= "Cmdtype" ></param&gt    ; <param name= "Cmdtext" ></param>//<param name= "cmdparms" ></param>//&LT;RETURNS&GT;&L t;/returns> public static int ExecuteNonQuery (string connstring, ComMandtype Cmdtype, String cmdtext, params oledbparameter[] cmdparms) {OleDbCommand cmd = new OleDbCommand (); using (OleDbConnection conn = new OleDbConnection (connstring)) {PrepareCommand (CMD, conn, null, Cmdtype, cmd        Text, Cmdparms, Connectionactiontype.open); int val = cmd.        ExecuteNonQuery (); Cmd.        Parameters.clear ();      return Val;    }}///<summary> Execute the SQL statement against System.Data.OleDb.OleDbCommand.Connection and return the number of rows affected. </summary>//<param name= "conn" ></param>//<param name= "Cmdtype" ></param>/ <param name= "Cmdtext" ></param>//<param name= "cmdparms" ></param>//<returns>&lt ;/returns> public static int ExecuteNonQuery (OleDbConnection conn, CommandType Cmdtype, string cmdtext, params OLE DB      Parameter[] cmdparms) {OleDbCommand cmd = new OleDbCommand (); PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, Cmdparms, ConnectionaCtiontype.autodetection); int val = cmd.      ExecuteNonQuery (); Cmd.      Parameters.clear ();    return Val;    }///<summary>///execute the SQL statement for System.Data.OleDb.OleDbCommand.Connection and return the number of rows affected.    </summary>//<param name= "trans" ></param>//<param name= "Cmdtype" ></param> <param name= "Cmdtext" ></param>//<param name= "cmdparms" ></param>//&LT;RETURNS&GT;&L t;/returns> public static int ExecuteNonQuery (OleDbTransaction trans, CommandType Cmdtype, String cmdtext, params Ol      Edbparameter[] cmdparms) {OleDbCommand cmd = new OleDbCommand (); PrepareCommand (cmd, trans.      Connection, trans, Cmdtype, Cmdtext, Cmdparms, Connectionactiontype.none); int val = cmd.      ExecuteNonQuery (); Cmd.      Parameters.clear ();    return Val;  }///<summary>//Send System.Data.OleDb.OleDbCommand.CommandText to System.Data.OleDb.OleDbCommand.Connection and generate a System.Data.OleDb.OleDbDaTareader. </summary>//<param name= "connstring" ></param>//<param name= "Cmdtype" ></param&gt    ; <param name= "Cmdtext" ></param>//<param name= "cmdparms" ></param>//&LT;RETURNS&GT;&L t;/returns> public static OleDbDataReader ExecuteReader (String connstring, CommandType cmdtype, string Cmdtext, para      Ms Oledbparameter[] cmdparms) {OleDbCommand cmd = new OleDbCommand ();      OleDbConnection conn = new OleDbConnection (connstring);        try {preparecommand (CMD, conn, null, Cmdtype, Cmdtext, Cmdparms, Connectionactiontype.open); OleDbDataReader rdr = cmd.        ExecuteReader (); Cmd.        Parameters.clear ();      return RDR; } catch {Conn.        Close ();      Throw }}///<summary> send System.Data.OleDb.OleDbCommand.CommandText to System.Data.OleDb.OleDbCommand.Conne    Ction and generates a System.Data.OleDb.OleDbDataReader. </summary>//<param name= "conn" ></param>//<param name= "Cmdtype" ></param>//<param name= "Cmdtext" & gt;</param>//<param name= "cmdparms" ></param>///<returns></returns> public stat IC OleDbDataReader ExecuteReader (OleDbConnection conn, CommandType Cmdtype, String cmdtext, params oledbparameter[]      cmdparms) {OleDbCommand cmd = new OleDbCommand ();        try {preparecommand (CMD, conn, null, Cmdtype, Cmdtext, Cmdparms, connectionactiontype.autodetection); OleDbDataReader rdr = cmd.        ExecuteReader (); Cmd.        Parameters.clear ();      return RDR; } catch {Conn.        Close ();      Throw }}///<summary>///execute the query and return the first column of the first row in the result set returned by the query.    Other columns or rows are ignored. </summary>//<param name= "connstring" ></param>//<param name= "Cmdtype" ></param&gt    ; <param name= "Cmdtext" ></param>///<param name= "Cmdparms" ></param&Gt <returns></returns> public static Object ExecuteScalar (String connstring, CommandType cmdtype, string C      Mdtext, params oledbparameter[] cmdparms) {OleDbCommand cmd = new OleDbCommand (); using (OleDbConnection conn = new OleDbConnection (connstring)) {PrepareCommand (CMD, conn, null, Cmdtype, CMDT        Ext, cmdparms, connectionactiontype.open); Object val = cmd.        ExecuteScalar (); Cmd.        Parameters.clear ();      return Val; }}///<summary>///execute the query and return the first column of the first row in the result set returned by the query.    Other columns or rows are ignored. </summary>//<param name= "conn" ></param>//<param name= "Cmdtype" ></param>/ <param name= "Cmdtext" ></param>//<param name= "cmdparms" ></param>//<returns>&lt ;/returns> public static Object ExecuteScalar (OleDbConnection conn, CommandType Cmdtype, String cmdtext, params OleD  Bparameter[] cmdparms) {OleDbCommand cmd = new OleDbCommand ();    PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, Cmdparms, connectionactiontype.autodetection); Object val = cmd.      ExecuteScalar (); Cmd.      Parameters.clear ();    return Val;    }///<summary>///execute the query and return the result data set returned by the query. </summary>//<param name= "connstring" ></param>//<param name= "Cmdtype" ></param&gt    ; <param name= "Cmdtext" ></param>//<param name= "cmdparms" ></param>//&LT;RETURNS&GT;&L t;/returns> public static DataSet ExecuteDataset (String connstring, CommandType cmdtype, String cmdtext, params OleD      Bparameter[] cmdparms) {OleDbCommand cmd = new OleDbCommand (); using (OleDbConnection conn = new OleDbConnection (connstring)) {PrepareCommand (CMD, conn, null, Cmdtype, CMDT        Ext, cmdparms, connectionactiontype.open);        OleDbDataAdapter da = new OleDbDataAdapter (cmd);        DataSet ds = new DataSet (); Da.        Fill (DS); Cmd. Parameters.clear ();       return DS;    }}///<summary> Execute the query and return the result data set returned by the query. </summary>//<param name= "conn" ></param>//<param name= "Cmdtype" ></param>/ <param name= "Cmdtext" ></param>//<param name= "cmdparms" ></param>//<returns>&lt ;/returns> public static DataSet ExecuteDataset (OleDbConnection conn, CommandType Cmdtype, String cmdtext, params Ol      Edbparameter[] cmdparms) {OleDbCommand cmd = new OleDbCommand ();      PrepareCommand (CMD, conn, null, Cmdtype, Cmdtext, Cmdparms, connectionactiontype.autodetection);      OleDbDataAdapter da = new OleDbDataAdapter (cmd);      DataSet ds = new DataSet (); Da.      Fill (DS); Cmd.      Parameters.clear ();    return DS;    }///<summary>//OLE DB parameter object for cached queries.     </summary>//<param name= "CacheKey" ></param>//<param name= "Cmdparms" ></param> public static void Cacheparameters (stringCacheKey, params oledbparameter[] cmdparms) {Parmcache[cachekey] = cmdparms;    }///<summary> Gets the specified array of parameter objects from the cache. </summary>//<param name= "CacheKey" ></param>//<returns></returns> public s Tatic oledbparameter[] Getcachedparameters (string cacheKey) {oledbparameter[] cachedparms = (oledbparameter[]) par      Mcache[cachekey];      if (cachedparms = = null) return null;      oledbparameter[] clonedparms = new Oledbparameter[cachedparms.length]; for (int i = 0, j = cachedparms.length; I < J; i++) clonedparms[i] = (OleDbParameter) ((icloneable) cachedparms[i] ).      Clone ();    return clonedparms;    }///<summary> Prepare Command object. </summary>//<param name= "cmd" ></param>//<param name= "conn" ></param>//// Lt;param name= "Trans" ></param>//<param name= "Cmdtype" ></param>//<param name= "Cmdtext" &  Gt;</param>  <param name= "cmdparms" ></param>//<param name= "Connactiontype" ></param> private stat IC void PrepareCommand (OleDbCommand cmd, OleDbConnection conn, OleDbTransaction Trans, CommandType Cmdtype, String CmdTex T, oledbparameter[] cmdparms, Connectionactiontype connactiontype) {if (Connactiontype = = Connectionactiontype.op EN) {conn.      Open (); } else {if (conn. state! = ConnectionState.Open) Conn.      Open (); } cmd.      Connection = conn;      Cmd.commandtext = Cmdtext; if (trans! = null) cmd.      Transaction = trans;      Cmd.commandtype = Cmdtype; if (cmdparms! = null) {foreach (OleDbParameter parm in cmdparms) cmd.      Parameters.Add (Parm); }}///<summary>///Unified paging displays data records///</summary>//<param name= "connstring" > Database connection String < /param>//<param name= "PageIndex" > Current page </param>//<param name= "PageSize" > Number of Bars per page </param>//<param name= "Fileds" > Fields displayed </param>//<param name= "table" > Query table </param> <param name= "where" > Conditions of the query </param>//<param name= "order" > Sort rules </param>//<param Name= "PageCount" >out: Total pages </param>///<param name= "RecordCount" >out: Total number of bars </param>//<param NA Me= "id" > Primary key for Table </param>//<returns> return DataTable collection </returns> public static DataTable Executepager ( string connstring, int pageIndex, int pageSize, string fileds, String table, string where, string order, out int PageCount      , out int recordCount, string id) {if (PageIndex < 1) PageIndex = 1;      if (PageSize < 1) pageSize = 10; if (string.      IsNullOrEmpty (fileds)) fileds = "*"; if (string.      IsNullOrEmpty (order)) Order = "ID desc"; using (OleDbConnection conn = new OleDbConnection (connstring)) {string myvw = string.        Format ("{0}", table); String sqltext = String.Format ("SELECT count (0) as RecordCount from {0} {1}", MYVW, where);        OleDbCommand cmdcount = new OleDbCommand (SQLTEXT, conn); IF (Conn. state = = connectionstate.closed) Conn.        Open ();        RecordCount = Convert.ToInt32 (Cmdcount.executescalar ());        if ((recordCount% pageSize) > 0) PageCount = recordcount/pagesize + 1;        else PageCount = recordcount/pagesize;        OleDbCommand Cmdrecord; if (PageIndex = = 1)//First page {Cmdrecord = new OleDbCommand (string.        Format ("Select top {0} {1} from {2} {3} ORDER by {4}", PageSize, Fileds, MYVW, where, order), Conn); } else if (PageIndex > PageCount)//exceeds the total number of pages {Cmdrecord = new OleDbCommand (string.        Format ("Select top {0} {1} from {2} {3} ' ORDER by {4}", PageSize, Fileds, MYVW, "where 1=2", order), Conn);          } else {int pagelowerbound = pageSize * PAGEINDEX;     int pageupperbound = pagelowerbound-pagesize;     String recordids = RecordID (string.          Format ("Select top {0} {1} from {2} {3} ORDER by {4}", Pagelowerbound, ID, MYVW, where, order), Pageupperbound, conn); Cmdrecord = new OleDbCommand (string.        Format ("Select {0} from {1} ' where {4} in ({2}) ' ORDER by {3}", Fileds, MYVW, recordids, order, ID), conn);        } OleDbDataAdapter dataAdapter = new OleDbDataAdapter (Cmdrecord);        DataTable dt = new DataTable ();        DataAdapter.Fill (DT);      return DT; }} private static string RecordID (string query, int passcount, OleDbConnection conn) {OleDbCommand cmd = n      EW OleDbCommand (query, conn); string result = String.      Empty; using (IDataReader dr = cmd. ExecuteReader ()) {while (Dr. Read ()) {if (Passcount < 1) {result + = "," + Dr.          GetInt32 (0);        } passcount--; }} return result.    Substring (1);    }///<summary>///connection operation type enumeration.    </summary>Enum Connectionactiontype {None = 0, autodetection = 1, Open = 2}}