Asp.net Oracle Database Access Operations

Copy codeThe Code is as follows: using System;
Using System. Collections;
Using System. Collections. Specialized;
Using System. Data;
Using System. Data. OracleClient;
Using System. Configuration;
Using System. Data. Common;
Using System. Collections. Generic;

/// <Summary>
/// Data Access abstract basic class
///
/// </Summary>
Public class DBBase
{

// Database connection string (configured in web. config). You can dynamically change connectionString to support multiple databases.
Public static string connectionString = System. Configuration. ConfigurationManager. ConnectionStrings ["ConnectionString1"]. ToString ();
Public DBBase ()
{
}

# Region check whether the user name exists
/// <Summary>
/// Check whether the user name exists. If the user name exists, true is returned. If the user name does not exist, false is returned.
/// </Summary>
/// <Param name = "strSql"> </param>
/// <Returns> </returns>
Public static bool Exists (string strSql)
{

Using (OracleConnection connection = new OracleConnection (connectionString ))
{
Connection. Open ();
OracleCommand myCmd = new OracleCommand (strSql, connection );
Try
{
Object obj = myCmd. ExecuteScalar (); // The first row and column of the returned result
MyCmd. Parameters. Clear ();
If (Object. Equals (obj, null) | (Object. Equals (obj, System. DBNull. Value )))
{
Return false;
}
Else
{
Return true;
}
}
Catch (Exception ex)
{
Throw ex;
}
}
}

# Endregion

# Region executes a simple SQL statement to return the number of affected records

/// <Summary>
/// Execute the SQL statement and return the number of affected records
/// </Summary>
/// <Param name = "SQLString"> SQL statement </param>
/// <Returns> Number of affected records </returns>
Public static int ExecuteSql (string SQLString)
{

OracleConnection connection = null;
OracleCommand cmd = null;
Try
{
Connection = new OracleConnection (connectionString );
Cmd = new OracleCommand (SQLString, connection );
Connection. Open ();
Int rows = cmd. ExecuteNonQuery ();
Return rows;
}
Finally
{
If (cmd! = Null)
{
Cmd. Dispose ();
}
If (connection! = Null)
{
Connection. Close ();
Connection. Dispose ();
}
}
}
# Endregion

# Region executes the query statement and returns SqlDataReader
/// <Summary>
/// Execute the query statement and return SqlDataReader (Note: after calling this method, you must Close SqlDataReader)
/// </Summary>
/// <Param name = "strSQL"> query statement </param>
/// <Returns> SqlDataReader </returns>
Public static OracleDataReader ExecuteReader (string strSQL)
{
OracleConnection connection = new OracleConnection (connectionString );
OracleCommand cmd = new OracleCommand (strSQL, connection );
Try
{
Connection. Open ();
OracleDataReader myReader = cmd. ExecuteReader (CommandBehavior. CloseConnection );
Return myReader;
}
Catch (System. Data. OracleClient. OracleException e)
{
Throw e;
}
Finally
{
Connection. Close ();

}
}
# Endregion

# Region executes an SQL query statement and returns the DataTable data table
/// <Summary>
/// Execute the SQL query statement
/// </Summary>
/// <Param name = "sqlStr"> </param>
/// <Returns> return the DataTable data table </returns>
Public static DataTable GetDataTable (string sqlStr)
{
OracleConnection mycon = new OracleConnection (connectionString );
OracleCommand mycmd = new OracleCommand (sqlStr, mycon );
DataTable dt = new DataTable ();
OracleDataAdapter da = null;
Try
{
Mycon. Open ();
Da = new OracleDataAdapter (sqlStr, mycon );
Da. Fill (dt );

}
Catch (Exception ex)
{

Throw new Exception (ex. ToString ());
}
Finally
{
Mycon. Close ();
}
Return dt;
}
# Endregion

# Region Stored Procedure operations
/// <Summary>
/// Run the stored procedure and return the datatable;
/// </Summary>
/// <Param name = "storedProcName"> stored procedure name </param>
/// <Param name = "parameters"> parameter </param>
/// <Returns> </returns>
Public static DataTable RunProcedureDatatable (string storedProcName, IDataParameter [] parameters)
{
Using (OracleConnection connection = new OracleConnection (connectionString ))
{
DataSet ds = new DataSet ();
Connection. Open ();
OracleDataAdapter sqlDA = new OracleDataAdapter ();
SqlDA. SelectCommand = BuildQueryCommand (connection, storedProcName, parameters );
SqlDA. Fill (ds );
Connection. Close ();
Return ds. Tables [0];
}
}
/// <Summary>
/// Execute the Stored Procedure
/// </Summary>
/// <Param name = "storedProcName"> stored procedure name </param>
/// <Param name = "parameters"> parameter </param>
/// <Returns> </returns>
Public static int RunProcedure (string storedProcName, IDataParameter [] parameters)
{
Using (OracleConnection connection = new OracleConnection (connectionString ))
{
Try
{
Connection. Open ();
OracleCommand command = new OracleCommand (storedProcName, connection );
Command. CommandType = CommandType. StoredProcedure;
Foreach (OracleParameter parameter in parameters)
{
If (parameter! = Null)
{
// Check the output parameter of the unallocated Value and assign it to DBNull. Value.
If (parameter. Direction = ParameterDirection. InputOutput | parameter. Direction = ParameterDirection. Input )&&
(Parameter. Value = null ))
{
Parameter. Value = DBNull. Value;
}
Command. Parameters. Add (parameter );
}
}
Int rows = command. ExecuteNonQuery ();
Return rows;
}

Finally
{
Connection. Close ();
}
}
}

/// <Summary>
/// Construct an OracleCommand object (used to return a result set instead of an integer)
/// </Summary>
/// <Param name = "connection"> database connection </param>
/// <Param name = "storedProcName"> stored procedure name </param>
/// <Param name = "parameters"> stored procedure parameters </param>
/// <Returns> OracleCommand </returns>
Private static OracleCommand BuildQueryCommand (OracleConnection connection, string storedProcName, IDataParameter [] parameters)
{
OracleCommand command = new OracleCommand (storedProcName, connection );
Command. CommandType = CommandType. StoredProcedure;
Foreach (OracleParameter parameter in parameters)
{
If (parameter! = Null)
{
// Check the output parameter of the unallocated Value and assign it to DBNull. Value.
If (parameter. Direction = ParameterDirection. InputOutput | parameter. Direction = ParameterDirection. Input )&&
(Parameter. Value = null ))
{
Parameter. Value = DBNull. Value;
}
Command. Parameters. Add (parameter );
}
}
Return command;
}

# Endregion

# Region Transaction Processing

/// <Summary>
/// Execute multiple SQL statements (in the form of a list) to implement database transactions.
/// </Summary>
/// <Param name = "SQLStringList"> Multiple SQL statements </param>
/// Call the Commit method of the Transaction object to complete the Transaction, or call the Rollback method to cancel the Transaction.
Public static int ExecuteSqlTran (List <String> SQLStringList)
{
Using (OracleConnection connection = new OracleConnection (connectionString ))
{
Connection. Open ();
// Create a command for the transaction
OracleCommand cmd = new OracleCommand ();
Cmd. Connection = connection;
OracleTransaction tx = connection. BeginTransaction (); // start a transaction
Cmd. Transaction = tx;
Try
{
Int count = 0;
For (int n = 0; n <SQLStringList. Count; n ++)
{
String strsql = SQLStringList [n];
If (strsql. Trim (). Length> 1)
{
Cmd. CommandText = strsql;
Count + = cmd. ExecuteNonQuery ();
}
}
Tx. Commit (); // use the Commit method to complete the transaction
Return count ;//
}
Catch
{
Tx. Rollback (); // The transaction is rolled back!
Return 0;
}
Finally
{
Cmd. Dispose ();
Connection. Close (); // Close the connection
}
}
}
# Endregion
# Region Transaction Processing

/// <Summary>
/// Execute multiple SQL statements (in the string array form) to implement database transactions.
/// </Summary>
/// <Param name = "SQLStringList"> Multiple SQL statements </param>
/// Call the Commit method of the Transaction object to complete the Transaction, or call the Rollback method to cancel the Transaction.
Public static int ExecuteTransaction (string [] SQLStringList, int p)
{
Using (OracleConnection connection = new OracleConnection (connectionString ))
{
Connection. Open ();
// Create a command for the transaction
OracleCommand cmd = new OracleCommand ();
Cmd. Connection = connection;
OracleTransaction tx = connection. BeginTransaction (); // start a transaction
Cmd. Transaction = tx;
Try
{
Int count = 0;
For (int n = 0; n <p; n ++)
{
String strsql = SQLStringList [n];
If (strsql. Trim (). Length> 1)
{
Cmd. CommandText = strsql;
Count + = cmd. ExecuteNonQuery ();
}
}
Tx. Commit (); // use the Commit method to complete the transaction
Return count ;//
}
Catch
{
Tx. Rollback (); // The transaction is rolled back!
Return 0;
}
Finally
{
Cmd. Dispose ();
Connection. Close (); // Close the connection
}
}
}

# Endregion
/// <Summary>
/// Execute the stored procedure to obtain the required number (primary keys of each table)
/// </Summary>
/// <Param name = "FlowName"> stored procedure parameters </param>
/// <Param name = "StepLen"> stored procedure parameters (1 by default) </param>
/// <Returns> Number (primary keys of each table) </returns>
Public static string Get_FlowNum (string FlowName, int StepLen = 1)
{
OracleConnection mycon = new OracleConnection (connectionString );
Try
{
Mycon. Open ();
OracleCommand MyCommand = new OracleCommand ("ALARM_GET_FLOWNUMBER", mycon );
MyCommand. CommandType = CommandType. StoredProcedure;
MyCommand. Parameters. Add (new OracleParameter ("I _FlowName", OracleType. VarChar, 50 ));
MyCommand. Parameters ["I _FlowName"]. Value = FlowName;
MyCommand. Parameters. Add (new OracleParameter ("I _SeriesNum", OracleType. Number ));
MyCommand. Parameters ["I _SeriesNum"]. Value = StepLen;
MyCommand. Parameters. Add (new OracleParameter ("O_FlowValue", OracleType. Number ));
MyCommand. Parameters ["O_FlowValue"]. Direction = ParameterDirection. Output;
MyCommand. ExecuteNonQuery ();
Return MyCommand. Parameters ["O_FlowValue"]. Value. ToString ();
}
Catch
{
Return "";
}
Finally
{
Mycon. Close ();
}
}

}