Details of the C Oracle database operation class instance

  • 2020-06-23 01:47:27
  • OfStack

C # described in this article so as to realize the Oracle database operations, can perform more than common Oracle database operations, contains the basic database connection, close the connection, the output record sets, execute Sql statement, with the paging function returns dataset, take exterior-interior field type and length, etc., as well as the thin table automatically inserted into the database and other advanced tasks. It should be noted that before executing SQL statement and returning DataReader, 1 must be opened with.read () before reading the data, and then use hashTable to perform insert,update,del operations on the database. Note that only the default database connection "connstr" can be used at this time.

The complete C# Oracle database class instance code is as follows:


using System;
using System.Data;
using System.Data.OracleClient;
using System.Collections;
using System.Reflection;
namespace MyOraComm
{
 /// ConnDbForOracle  The summary description of. 
 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  A constructor with parameters  
  ///  A 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 the database  
  ///  Open the database 
  public void OpenConn()
  {
  if(this.Connection.State!=ConnectionState.Open)
   this.Connection.Open();
  }
  #endregion
  #region  Close the database connection  
  ///  Close the database connection 
  public void CloseConn()
  {
  if(Connection.State==ConnectionState.Open)
   Connection.Close();
  }
  #endregion

  #region  perform SQL Statement that returns data to DataSet In the 
  ///  perform SQL Statement that returns data to DataSet In the 
  /// sql statements 
  ///  Custom returned DataSet The name of the table 
  ///  return DataSet
  public 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  perform Sql statements , Return to pagination dataset 
  ///  perform Sql statements , Return to pagination dataset
  /// Sql statements 
  ///  Number of records per page 
  /// < The current page /param>
  ///  return dataset The name of the table 
  ///  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  perform SQL Statement, return  DataReader, Before using 1 Must first .read() Open the , And then you can read the data 
  ///  perform SQL Statement, return  DataReader, Before using 1 Must first .read() Open the , And then you can read the data  
  /// sql statements 
  ///  return 1 a OracleDataReader
  public OracleDataReader ReturnDataReader(String sql)
  {
  OpenConn();
  OracleCommand command = new OracleCommand(sql,Connection);
  return command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
  }
  #endregion

  #region  perform SQL Statement that returns the total number of records 
  ///  perform SQL Statement that returns the total number of records 
  /// sql statements 
  ///  Returns the total number of records 
  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  Take the current sequence , Conditions for seq.nextval or seq.currval
  /// 
  ///  Take the current sequence 
  public decimal GetSeq(string seqstr)
  {
  decimal 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  perform SQL statements , Returns the number of rows affected 
  ///  perform SQL statements , Returns 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 Perform against the database insert,update,del operation , Note that you can only use the default database connection at this point "connstr"
  #region  Automatically insert the database based on the table name and the hashtable   Usage: 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); //  For insert sql statements 
  int val=ExecuteNonQuery(str_Sql,Parms);
  return val;
  }
  #endregion
 
  #region  Update the database according to the relevant conditions   Usage: 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;
  //  Hash 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); //  For insert sql statements 
  int val=ExecuteNonQuery(str_Sql,Parms);
  return val;
  }
  #endregion
 
  #region del operation , Notice the number of conditions and hash The number of arguments in should be 1 to   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;
  //  Hash 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 deleted sql statements 
  int val=ExecuteNonQuery(str_Sql,Parms);
  return val;
  }
  #endregion 

  // = = = = = = = up 3 Operation = = = = = = = = = = = = 

  #region  According to the hashtable and table name automatically generate corresponding insert statements ( Parameter type )
  ///  According to the hashtable and table name automatically generate corresponding insert statements  
  ///  The name of the table to insert 
  ///  The thin sheet 
  ///  return sql statements 
  public static string GetInsertSqlbyHt(string TableName,Hashtable ht)
  {
  string str_Sql="";
  int i=0;
  int ht_Count=ht.Count; //  Number of hash tables 
  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  Based on the table name, where Condition that the hash table automatically generates an update statement ( Parameter type )
  public static string GetUpdateSqlbyHt(string Table,string ht_Where,Hashtable ht)
  {
  string str_Sql="";
  int i=0;
  int ht_Count=ht.Count; //  Number of hash tables 
  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(" ","")=="") //  There are no conditions when updating 
  {
   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  Based on the table name, where Condition, hashtable automatically generated del statements ( Parameter type )
  public static string GetDelSqlbyHt(string Table,string ht_Where,Hashtable ht)
  {
  string str_Sql="";
  int i=0;
  
  int ht_Count=ht.Count; //  Number of hash tables 
  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(" ","")=="") //  There are no conditions when updating 
  {
   str_Sql="Delete "+Table;
  }
  else
  {
   str_Sql="Delete "+Table+" where "+ht_Where;
  }
  return str_Sql;
  }
  #endregion

  #region  generate oracle parameter 
  /// 
  ///  generate oracle parameter 
  ///  The field name 
  ///  The data type 
  ///  Data size 
  ///  value 
  public static OracleParameter MakeParam(string ParamName,System.Data.OracleClient.OracleType otype,int size,Object Value) 
  {
  OracleParameter para=new OracleParameter(ParamName,Value);
  para.OracleType=otype;
  para.Size=size;
  return para;
  }
  #endregion
  #region  generate oracle parameter 
  public static OracleParameter MakeParam(string ParamName,string Value) 
  {
  return new OracleParameter(ParamName, Value);
  }
  #endregion
  #region  Assembly is based on the type and length of the table structure field oracle sql Statement parameters 
  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 dynamic   Takes the type and length of a table field , There is no dynamic use here connstr, It's the default! by/ Wen less 
  public System.Data.DataTable GetTabType(string tabnale)
  {
  string sql="select column_name,data_type,data_length from all_tab_columns where table_name='"+tabnale.ToUpper()+"'";
  OpenConn();
  return (ReturnDataSet(sql,"dv")).Tables[0];
  }
  #endregion
  #region  perform sql statements  
  public int ExecuteNonQuery(string cmdText, params OracleParameter[] cmdParms) 
  {
  OracleCommand 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
 }
 }

When in use, the above code can be saved as es21EN_dbconn.cs file and then called.


Related articles: