Example of a C # Oracle database Operation class

The Oracle Database operations class implemented in this article for C # can perform many common Oracle database operations, including the underlying database connection, closing the connection, outputting the recordset, executing the SQL statement, returning a dataset with paging capabilities, the type and length of the table field, and so on. There are also high-level tasks such as the automatic insertion of tables into the database. It is important to note that before you execute the SQL statement, you must open the. Read () before returning to DataReader, before you can read the data, and then use Hashtable to insert,update,del the database, noting that only the default database connection can be used at this time. ConnStr ".

The Oracle Database operations class implemented in this article for C # can perform many common Oracle database operations, including the underlying database connection, closing the connection, outputting the recordset, executing the SQL statement, returning a dataset with paging capabilities, the type and length of the table field, and so on. There are also high-level tasks such as the automatic insertion of tables into the database. It is important to note that before you execute the SQL statement, you must open the. Read () before returning to DataReader, before you can read the data, and then use Hashtable to insert,update,del the database, noting that only the default database connection can be used at this time. ConnStr ".

The complete example code for the C # Oracle database class is as follows:

Using system;using system.data;using system.data.oracleclient;using system.collections;using System.Reflection; namespace myoracomm{///conndbfororacle Summary description.  public class Connfororacle {protected OracleConnection Connection;  private string connectionString;  Public connfororacle () {string connstr; ConnStr = system.configuration.configurationsettings.appsettings["ConnStr"].  ToString ();  connectionString = ConnStr;  Connection = new OracleConnection (connectionString);  } #region constructor with parameters///constructor with parameters///Database Join String public connfororacle (string connstring) {string connstr; ConnStr = system.configuration.configurationsettings.appsettings[connstring].  ToString ();  Connection = new OracleConnection (CONNSTR); #endregion #region Open Database//Open database public void Openconn () {if (this. Connection.state!=connectionstate.open) this.  Connection.Open (); #endregion #region Close Database Join//close database join public void Closeconn () {if (Connection.state==connectionstate.open) Con Nection.Close (); #endregion #region Execute the SQL statement, return data to the DataSet///Execute SQL statement, return data to the dataset///SQL statement///Custom returned DataSet table name///return DataSet PU  Blic DataSet Returndataset (String sql,string datasetname) {DataSet dataset=new DataSet ();  Openconn ();  OracleDataAdapter orada=new OracleDataAdapter (sql,connection);  Orada.fill (Dataset,datasetname);  Closeconn ();  return dataSet; #endregion #region Execute the SQL statement, return the dataset with paging function///Execute SQL statement, return the dataset with paging function//SQL statement//display the number of records per page///< current page/param&gt  ;   Returns the dataset table name///return DataSet public DataSet Returndataset (string Sql,int pagesize,int currpageindex,string datasetname)  {DataSet dataset=new DataSet ();  Openconn ();  OracleDataAdapter orada=new OracleDataAdapter (sql,connection);  Orada.fill (Dataset,pagesize * (CurrPageIndex-1), pagesize,datasetname);  Closeconn ();  return dataSet; #endregion #region execute the SQL statement and return to DataReader, before you use it. Read () to open before you can read data///Execute SQL statement, return DataReader, before using. Read () to open, Then you can read the data///SQL statement///return a OracledatareAder public OracleDataReader Returndatareader (String sql) {openconn ();  OracleCommand command = new OracleCommand (sql,connection); return command.  ExecuteReader (System.Data.CommandBehavior.CloseConnection);   The #endregion #region execute the SQL statement, returning the total number of records//Execute SQL statement, return the total number of records//SQL statement///Return record total number of public int getrecordcount (String sql)  {int recordCount = 0;  Openconn ();  OracleCommand command = new OracleCommand (sql,connection); OracleDataReader dataReader = command.  ExecuteReader ();  while (Datareader.read ()) {recordcount++;  } datareader.close ();  Closeconn ();  return recordCount;  } #endregion #region fetch the current sequence, the condition is seq.nextval or seq.currval////////////////Take the current sequence public decimal getseq (string seqstr  Seqnum = 0;  String sql= "Select" +seqstr+ "from dual";  Openconn ();  OracleCommand command = new OracleCommand (sql,connection); OracleDataReader dataReader = command.  ExecuteReader (); if (Datareader.read ()) {Seqnum=decimal. Parse (Datareader[0].  ToString ()); } datareader.close();  Closeconn ();  return seqnum;  #endregion #region Execute the SQL statement that returns the number of rows affected///execute the SQL statement, returning the number of rows affected public int executesql (String sql) {int cmd=0;  Openconn ();  OracleCommand command = new OracleCommand (sql,connection); try {Cmd =command.   ExecuteNonQuery ();  } catch {} finally {//closeconn ();  } return CMD; } #endregion//== use Hashtable to insert,update,del the database, note that you can only use the default database connection "CONNSTR" #region automatically insert database usage based on table name and the list of tables: Insert (" Test ", HT) public int Insert (string tablename,hashtable ht) {oracleparameter[] parms=new oracleparameter[ht.  Count]; IDictionaryEnumerator et = ht.  GetEnumerator ();  DataTable Dt=gettabtype (TableName);  System.Data.OracleClient.OracleType Otype;  int size=0;  int i=0; while (ET. MoveNext ())//hash table loop {getotype (ET). Key.tostring ().   ToUpper (), dt,out otype,out size); System.Data.OracleClient.OracleParameter Op=makeparam (":" +et. Key.tostring (), Otype,size,et.   Value.tostring ()); Parms[i]=op;  Add SqlParameter object i=i+1; } string Str_sql=getinserTsqlbyht (TABLENAME,HT);  Get insert SQL statement int val=executenonquery (str_sql,parms);  return Val;   #endregion #region Update the database based on the relevant criteria: Update ("Test", "Id=:id", HT); public int Update (string tablename,string ht_where, Hashtable ht) {oracleparameter[] parms=new oracleparameter[ht.  Count]; IDictionaryEnumerator et = ht.  GetEnumerator ();  DataTable Dt=gettabtype (TableName);  System.Data.OracleClient.OracleType Otype;  int size=0;  int i=0; Thohashi table Loop while (ET. MoveNext ()) {Getotype (ET. Key.tostring ().   ToUpper (), dt,out otype,out size); System.Data.OracleClient.OracleParameter Op=makeparam (":" +et. Key.tostring (), Otype,size,et.   Value.tostring ()); Parms[i]=op;  Add SqlParameter object i=i+1; } string Str_sql=getupdatesqlbyht (TABLENAME,HT_WHERE,HT);  Get insert SQL statement int val=executenonquery (str_sql,parms);  return Val; } #endregion #region del operation, note that the number of conditions here and the number of arguments in the hash should be consistent usage: del ("Test", "Id=:id", HT) public int Del (string tablename,string HT _where,hashtable ht) {oracleparameter[] Parms=nEW Oracleparameter[ht.  Count]; IDictionaryEnumerator et = ht.  GetEnumerator ();  DataTable Dt=gettabtype (TableName);  System.Data.OracleClient.OracleType Otype;  int i=0;  int size=0; Thohashi table Loop while (ET. MoveNext ()) {Getotype (ET. Key.tostring ().   ToUpper (), dt,out otype,out size); System.Data.OracleClient.OracleParameter Op=makeparam (":" +et. Key.tostring (), ET.   Value.tostring ()); Parms[i]=op;  Add SqlParameter object i=i+1; } string Str_sql=getdelsqlbyht (TABLENAME,HT_WHERE,HT);  Get the delete Sql statement int val=executenonquery (str_sql,parms);  return Val; } #endregion//======== The internal calling function of the above three operations ================== #region automatically generate the appropriate INSERT statement (parameter type) based on the table and table name, or/or automatically generate the corresponding Inser based on the table and table name. T statement///table name to be inserted///table////or return SQL statement public static string Getinsertsqlbyht (String tablename,hashtable ht) {String St  R_sql= "";  int i=0; int HT_COUNT=HT. Count; Hash table number IDictionaryEnumerator Myenumerator = ht.  GetEnumerator ();  String Before= "";  String Behide= ""; while (Myenumerator.movenext ()) {if (i==0) {  Before= "(" +myenumerator.key;   } else if (i+1==ht_count) {before=before+ "," +myenumerator.key+ ")";   } else {before=before+ "," +myenumerator.key;  } i=i+1; } behide= "Values" +before. Replace (",", ",:").  Replace ("(", "(:");  Str_sql= "Insert into" +tablename+before+behide;  return str_sql; } #endregion #region automatically generate an UPDATE statement (parameter type) public static string Getupdatesqlbyht (String table,string Ht_where) based on the table name, where condition,  , Hashtable ht) {string str_sql= "";  int i=0; int HT_COUNT=HT. Count; Hash table number IDictionaryEnumerator Myenumerator = ht.  GetEnumerator (); while (Myenumerator.movenext ()) {if (i==0) {if (ht_where.tostring (). ToLower (). IndexOf ((myenumerator.key+ "=:" +myenumerator.key).   ToLower ()) ==-1) {str_sql=myenumerator.key+ "=:" +myenumerator.key; }} else {if (ht_where.tostring (). ToLower (). IndexOf ((":" +myenumerator.key+ "").   ToLower ()) ==-1) {str_sql=str_sql+ "," +myenumerator.key+ "=:" +myenumerator.key;  }} i=i+1; } if (Ht_where==null | | ht_where.  Replace ("", "") = = "")//update without conditions {str_sql= "update" +table+ "set" +STR_SQL;  } else {str_sql= "update" +table+ "set" +str_sql+ "where" +ht_where; } str_sql=str_sql.replace ("Set,", "set").  Replace ("Update,", "Update");  return str_sql; } #endregion #region automatically generate a del statement (parameter type) public static string Getdelsqlbyht (String table,string ht_where,h) based on the table name, where condition,  Ashtable ht) {string str_sql= "";    int i=0; int HT_COUNT=HT. Count; Hash table number IDictionaryEnumerator Myenumerator = ht.  GetEnumerator (); while (Myenumerator.movenext ()) {if (i==0) {if (ht_where.tostring (). ToLower (). IndexOf ((myenumerator.key+ "=:" +myenumerator.key).   ToLower ()) ==-1) {str_sql=myenumerator.key+ "=:" +myenumerator.key; }} else {if (ht_where.tostring (). ToLower (). IndexOf ((":" +myenumerator.key+ "").   ToLower ()) ==-1) {str_sql=str_sql+ "," +myenumerator.key+ "=:" +myenumerator.key;  }} i=i+1; } if (Ht_where==null | | ht_where.replace ("," ") = =" ")//update without conditions {str_sql=" Delete "+tAble  } else {str_sql= "Delete" +table+ "where" +ht_where;  } return str_sql;  #endregion #region Generate Oracle Parameters///////////data type///data size///value public static OracleParameter Makeparam (String paramname,system.data.oracleclient.oracletype otype,int size,object Value) {OracleParameter Para=ne  W OracleParameter (Paramname,value); Para.  Oracletype=otype; Para.  Size=size;  return para; } #endregion #region generate the Oracle parameter public static OracleParameter Makeparam (String paramname,string Value) {return new  OracleParameter (ParamName, Value); #endregion #region Assemble the Oracle SQL statement parameters according to the type and length of the table structure field public static void Getotype (String key,datatable dt,out System.Data. Oracleclient.oracletype otype,out int size) {DataView Dv=dt.  DefaultView; Dv.  Rowfilter= "Column_name=" "+key+" "; String ftype=dv[0]["Data_type"]. ToString ().  ToUpper ();   Switch (fType) {case "DATE": otype= oracletype.datetime; Size=int. Parse (dv[0]["Data_length").   ToString ());   Break Case "CHAR": otype= Oracletype.char; Size=int. Parse (dv[0]["Data_length").   ToString ());   Break   Case "LONG": otype= oracletype.double; Size=int. Parse (dv[0]["Data_length").   ToString ());   Break   Case "NVARCHAR2": otype= Oracletype.nvarchar; Size=int. Parse (dv[0]["Data_length").   ToString ());   Break   Case "VARCHAR2": otype= Oracletype.nvarchar; Size=int. Parse (dv[0]["Data_length").   ToString ());   Break   Default:otype= Oracletype.nvarchar;   size=100;  Break }} #endregion #region动态 take the type and length of the table field, there is no dynamic use of CONNSTR, is the default! by/less public System.Data.DataTable gettabtype (string tabnale) {string sql= "select Column_name,data_type,data_length fr Om all_tab_columns where table_name= ' "+tabnale.  ToUpper () + "'";  Openconn (); Return (Returndataset (SQL, "DV")).  Tables[0]; } #endregion #region Execute SQL statement public int ExecuteNonQuery (string cmdtext, params oracleparameter[] cmdparms) {Oracle  Command cmd = new OracleCommand ();   Openconn (); Cmd.  Connection=connection; Cmd.commandtext= Cmdtext; if (cmdparms! = null) {foreach (OracleParameter parm in cmdparms) cmd.  Parameters.Add (Parm); } int val = cmd.  ExecuteNonQuery (); Cmd.  Parameters.clear (); Conn.  Closeconn ();  return Val; } #endregion}}