C ා learning — encapsulation of basic Oracle database operations (connection, addition, deletion, modification, query)

Time:2020-1-22

Write before:

C ා package of sqlserver: https://www.cnblogs.com/mexihq/p/11636785.html

Similar to the c-encapsulation of sqlserver in the previous article, the small edition encapsulates the Oracle database, which is convenient for later development and use, mainly including connection, addition, deletion, modification and query of Oracle database. If there is any problem, please give advice. In the future, several other commonly used databases will also be collated accordingly. Don’t say much, just start the code.

It indicates that a SqlConnection is convenient for subsequent use.

Private oracleconnection oracle_con;; // declare an oracleconnection for easy use

 

Oracle open:

///

/// Oracle open
///

/// link statement
/// Success:success; Fail:reason
public string Oracle_Open(string link)
{
  try
  {
    oracle_con = new OracleConnection(link);
    oracle_con.Open();
    return “success”;
  }
  catch (Exception ex)
  {
    return ex.Message;
  }
}

Oracle shutdown:

///

/// Oracle close
///

/// Success:success Fail:reason
public string Oracle_Close()
{
  try
  {
    if (oracle_con == null)
    {
      return “No database connection”;
    }
    if (oracle_con.State == ConnectionState.Open)
    {
      oracle_con.Close();
      oracle_con.Dispose();
    }
    else
    {
      if (oracle_con.State == ConnectionState.Closed)
      {
        return “success”;
      }
      if (oracle_con.State == ConnectionState.Broken)
      {
        return “ConnectionState:Broken”;
      }
    }
    return “success”;
  }
  catch (Exception ex)
  {
    return ex.Message;
  }

}

Addition, deletion and modification of Oracle:

///

/// Oracle insert,delete,update
///

/// insert,delete,update statement
/// Success:success + Number of affected rows; Fail:reason
public string Oracle_Insdelupd(string sql)
{
  try
  {
    int num = 0;
    if (oracle_con == null)
    {
      return “Please open the database connection first”;
    }
    if (oracle_con.State == ConnectionState.Open)
    {
      OracleCommand oracleCommand = new OracleCommand(sql, oracle_con);
      num = oracleCommand.ExecuteNonQuery();
    }
    else
    {
      if (oracle_con.State == ConnectionState.Closed)
      {
        return “Database connection closed”;
      }
      if (oracle_con.State == ConnectionState.Broken)
      {
        return “Database connection is destroyed”;
      }
    }
    return “success” + num;
  }
  catch (Exception ex)
  {
    return ex.Message.ToString();
  }
}

Oracle query:

///

/// Oracle select
///

/// select statement
/// Success:success; Fail:reason
/// select result
public DataSet Oracle_Select(string sql, out string record)
{
  try
  {
    DataSet dataSet = new DataSet();
    if (oracle_con != null)
    {
      if (oracle_con.State == ConnectionState.Open)
      {
        OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(sql, oracle_con);
        oracleDataAdapter.Fill(dataSet, “sample”);
        oracleDataAdapter.Dispose();
        record = “OK”;
        return dataSet;
      }
      if (oracle_con.State == ConnectionState.Closed)
      {
        record = “Database connection closed”;
      }
      else if (oracle_con.State == ConnectionState.Broken)
      {
        record = “Database connection is destroyed”;
      }
    }
    else
    {
      record = “Please open the database connection first”;
    }
    record = “error”;
    return dataSet;
  }
  catch (Exception ex)
  {
    DataSet dataSet = new DataSet();
    record = ex.Message.ToString();
    return dataSet;
  }
}

Small editors found that the above encapsulation method is still very troublesome. When adding, deleting, modifying and querying Oracle, they have to open the database first, and then close it, which is more troublesome in practice. Therefore, the above two methods of adding, deleting, modifying and querying are overloaded. Each time, the database is opened first and then closed.

///

/// Oracle insert,delete,update
///

/// insert,delete,update statement
/// link statement
/// Success:success + Number of affected rows; Fail:reason
public string Oracle_Insdelupd(string sql, string link)
{
  try
  {
    int num = 0;
    using (OracleConnection oracleConnection = new OracleConnection(link))
    {
      DataSet dataSet = new DataSet();
      oracleConnection.Open();
      OracleCommand oracleCommand = new OracleCommand(sql, oracleConnection);
      num = oracleCommand.ExecuteNonQuery();
      oracleConnection.Close();
      return “success” + num;
    }
  }
  catch (Exception ex)
  {
    return ex.Message.ToString();
  }
}

 

///

/// Oracle select
///

/// select statement
/// link statement
/// Success:success; Fail:reason
/// select result
public DataSet Oracle_Select(string sql, string link, out string record)
{
  try
  {
    using (OracleConnection oracleConnection = new OracleConnection(link))
    {
      DataSet dataSet = new DataSet();
      oracleConnection.Open();
      OracleDataAdapter oracleDataAdapter = new OracleDataAdapter(sql, oracleConnection);
      oracleDataAdapter.Fill(dataSet, “sample”);
      oracleDataAdapter.Dispose();
      oracleConnection.Close();
      record = “success”;
      return dataSet;
    }
  }
  catch (Exception ex)
  {
    DataSet dataSet = new DataSet();
    record = ex.Message.ToString();
    return dataSet;
  }
}