using System;
using ;
using ;
using ;
using ;
using ;
using ;
using ;
/// <summary>
/// Data access abstract basic class
///
/// </summary>
public class DBBase
{
//The database connection string (to configure), you can dynamically change the connectionString to support multiple databases.
public static string connectionString = ["ConnectionString1"].ToString();
public DBBase()
{
}
#region Check whether the username exists
/// <summary>
/// Check whether the user name exists. Return true if it exists. If it does not exist, return false if it does not exist.
/// </summary>
/// <param name="strSql"></param>
/// <returns></returns>
public static bool Exists(string strSql)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
();
OracleCommand myCmd = new OracleCommand(strSql, connection);
try
{
object obj = (); //Return the first row and column of the result
();
if (((obj, null)) || ((obj, )))
{
return false;
}
else
{
return true;
}
}
catch (Exception ex)
{
throw ex;
}
}
}
#endregion
#region Execute a simple SQL statement to return the number of records affected
/// <summary>
/// Execute SQL statements to return the number of records affected
/// </summary>
/// <param name="SQLString">SQL statement</param>
/// <returns>Number of records affected</returns>
public static int ExecuteSql(string SQLString)
{
OracleConnection connection = null;
OracleCommand cmd = null;
try
{
connection = new OracleConnection(connectionString);
cmd = new OracleCommand(SQLString, connection);
();
int rows = ();
return rows;
}
finally
{
if (cmd != null)
{
();
}
if (connection != null)
{
();
();
}
}
}
#endregion
#region Execute the query statement and return SqlDataReader
/// <summary>
/// Execute the query statement and return the SqlDataReader (Note: After calling this method, be sure to close the 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
{
();
OracleDataReader myReader = ();
return myReader;
}
catch ( e)
{
throw e;
}
finally
{
();
}
}
#endregion
#region Execute SQL query statement and return DataTable data table
/// <summary>
/// Execute SQL query statement
/// </summary>
/// <param name="sqlStr"></param>
/// <returns>Return to 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
{
();
da = new OracleDataAdapter(sqlStr, mycon);
(dt);
}
catch (Exception ex)
{
throw new Exception(());
}
finally
{
();
}
return dt;
}
#endregion
#region stored procedure operation
/// <summary>
/// Run the stored procedure and return datatable;
/// </summary>
/// <param name="storedProcName">StoredProcName</param>
/// <param name="parameters">parameters</param>
/// <returns></returns>
public static DataTable RunProcedureDatatable(string storedProcName, IDataParameter[] parameters)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
DataSet ds = new DataSet();
();
OracleDataAdapter sqlDA = new OracleDataAdapter();
= BuildQueryCommand(connection, storedProcName, parameters);
(ds);
();
return [0];
}
}
/// <summary>
/// Execute stored procedures
/// </summary>
/// <param name="storedProcName">StoredProcName</param>
/// <param name="parameters">parameters</param>
/// <returns></returns>
public static int RunProcedure(string storedProcName, IDataParameter[] parameters)
{
using (OracleConnection connection = new OracleConnection(connectionString))
{
try
{
();
OracleCommand command = new OracleCommand(storedProcName, connection);
= ;
foreach (OracleParameter parameter in parameters)
{
if (parameter != null)
{
// Check the output parameters of unassigned values and assign them to.
if (( == || == ) &&
( == null))
{
= ;
}
(parameter);
}
}
int rows = ();
return rows;
}
finally
{
();
}
}
}
/// <summary>
///Build OracleCommand object (used to return a result set, not an integer value)
/// </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);
= ;
foreach (OracleParameter parameter in parameters)
{
if (parameter != null)
{
// Check the output parameters of unassigned values and assign them to.
if (( == || == ) &&
( == null))
{
= ;
}
(parameter);
}
}
return command;
}
#endregion
#region Transaction Processing
/// <summary>
/// Execute multiple SQL statements (in the form of 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))
{
();
// Create a command for the transaction
OracleCommand cmd = new OracleCommand();
= connection;
OracleTransaction tx = ();// Start a transaction
= tx;
try
{
int count = 0;
for (int n = 0; n < ; n++)
{
string strsql = SQLStringList[n];
if (().Length > 1)
{
= strsql;
count += ();
}
}
();//Use the Commit method to complete the transaction
return count;//
}
catch
{
();// An error occurs and the transaction rolls back!
return 0;
}
finally
{
();
();//Close the connection
}
}
}
#endregion
#region Transaction Processing
/// <summary>
/// Execute multiple SQL statements (in the form of string arrays) 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))
{
();
// Create a command for the transaction
OracleCommand cmd = new OracleCommand();
= connection;
OracleTransaction tx = ();// Start a transaction
= tx;
try
{
int count = 0;
for (int n = 0; n < p; n++)
{
string strsql = SQLStringList[n];
if (().Length > 1)
{
= strsql;
count += ();
}
}
();//Use the Commit method to complete the transaction
return count;//
}
catch
{
();// An error occurs and the transaction rolls back!
return 0;
}
finally
{
();
();//Close the connection
}
}
}
#endregion
/// <summary>
/// Execute stored procedures to obtain the required number (primary keys of each table)
/// </summary>
/// <param name="FlowName">Stored procedure parameters</param>
/// <param name="StepLen">Stored procedure parameters (default is 1)</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
{
();
OracleCommand MyCommand = new OracleCommand("ALARM_GET_FLOWNUMBER", mycon);
= ;
(new OracleParameter("I_FlowName", , 50));
["I_FlowName"].Value = FlowName;
(new OracleParameter("I_SeriesNum", ));
["I_SeriesNum"].Value = StepLen;
(new OracleParameter("O_FlowValue", ));
["O_FlowValue"].Direction = ;
();
return ["O_FlowValue"].();
}
catch
{
return "";
}
finally
{
();
}
}
}