ODP-based data layer base class C # source code

# Region using
Using System;
Using System. Configuration;
Using System. Data;
Using Oracle. DataAccess. Client;
# Endregion

Namespace WIS. Base. Data
{
/// <Summary>
/// <Table style = "font-size: 12px">
/// <Tr> <td> <B> file name </B>: DbObject. cs </td> </tr>
/// <Tr> <td> <B> function description </B>: data layer base class that provides basic operations on underlying data </td> </tr>
/// <Tr> <td> <B> creator </B>: Xia chuntao xchuntao@163.com qq: 23106676 </td> </tr>
/// <Tr> <td> <B> creation time </B>: </td> </tr>
/// </Table>
/// </Summary>
Public class DbObject
{
# Region member variables
/// <Summary>
/// <Table style = "font-size: 12px">
/// <Tr> <td> <B> function description </B>: Oracle data connection object </td> </tr>
/// <Tr> <td> <B> creator </B>: Xia chuntao </td> </tr>
/// <Tr> <td> <B> creation time </B>: </td> </tr>
/// </Table>
/// </Summary>
Protected OracleConnection Connection;

/// <Summary> <table style = "font-size: 12px">
/// <Tr> <td> <B> function description </B>: data connection string </td> </tr>
/// <Tr> <td> <B> creator </B>: Xia chuntao </td> </tr>
/// <Tr> <td> <B> creation time </B>: </td> </tr>
/// </Table> </summary>
Private string connectionString;
# Endregion

# Region Constructor
/// <Summary> <table style = "font-size: 12px">
/// <Tr> <td> <B> function description </B>: constructor. Use the default data connection string ConnectionString in the configuration file, initialize the data connection object </td> </tr>
/// <Tr> <td> <B> creator </B>: Xia chuntao </td> </tr>
/// <Tr> <td> <B> creation time </B>: </td> </tr>
/// </Table> </summary>
Public DbObject ()
{
ConnectionString = ConfigurationSettings. etettings. Get ("ConnectionString"); // The connection string obtained from Web. Config.
Connection = new OracleConnection (connectionString );
}
/// <Summary> <table style = "font-size: 12px">
/// <Tr> <td> <B> function description </B>: constructor. The constructor concatenates strings based on specified data, initialize the data connection object </td> </tr>
/// <Tr> <td> <B> creator </B>: Xia chuntao </td> </tr>
/// <Tr> <td> <B> creation time </B>: </td> </tr>
/// </Table> </summary>
/// <Param name = "newConnectionString"> data connection string </param>
Public DbObject (string newConnectionString)
{
ConnectionString = newConnectionString;
Connection = new OracleConnection (connectionString );
}
# Endregion

# Region private Method

/// <Summary> <table style = "font-size: 12px">
/// <Tr> <td> <B> function description </B>: creates an OracleCommand object for generating OracleDataReader </td> </tr>
/// <Tr> <td> <B> creator </B>: Xia chuntao </td> </tr>
/// <Tr> <td> <B> creation time </B>: </td> </tr>
/// </Table> </summary>
/// <Param name = "storedProcName"> stored procedure name </param>
/// <Param name = "parameters"> List of parameter objects in a stored procedure (array) </param>
/// <Returns> OracleCommand object </returns>
Private OracleCommand BuildCommand (string storedProcName, IDataParameter [] parameters)
{
OracleCommand command = new OracleCommand (storedProcName, Connection );
Command. CommandType = CommandType. StoredProcedure;

Foreach (OracleParameter parameter in parameters)
{
Command. Parameters. Add (parameter );
}

Return command;

}
# Endregion

# Region running the Stored Procedure
/// <Summary>
/// <Table style = "font-size: 12px">
/// <Tr> <td> <B> function description </B>: runs the stored procedure, obtains the number of affected rows, and returns the running result of the stored procedure. </td> </tr>
/// <Tr> <td> <B> creator </B>: Xia chuntao </td> </tr>
/// <Tr> <td> <B> creation time </B>: </td> </tr>
/// </Table>
/// </Summary>
/// <Param name = "storedProcName"> stored procedure name </param>
/// <Param name = "parameters"> List of parameter objects in a stored procedure (array) </param>
/// <Param name = "rowsAffected"> output parameter: number of records affected by the Stored Procedure </param>
/// <Returns> running result of the stored procedure </returns>
Public object RunProcedure (string storedProcName, IDataParameter [] parameters, out int rowsAffected)
{
Object result;

// If (Connection. State. ToString () = "Closed") Connection. Open ();
Connection. Open ();
OracleCommand command = BuildCommand (storedProcName, parameters );
RowsAffected = command. ExecuteNonQuery ();
// If the "ReturnValue" parameter exists, the return value is returned. Otherwise, null is returned.
Bool blnHasReturn = false;
For (int I = 0; I <parameters. Length; I ++)
{
If (parameters [I]. Direction = ParameterDirection. ReturnValue)
{
BlnHasReturn = true;
Break;
}
}
If (blnHasReturn)
Result = command. Parameters ["ReturnValue"]. Value;
Else
Result = null;

Connection. Close ();
Return result;
}

/// <Summary> <table style = "font-size: 12px">
/// <Tr> <td> <B> function description </B>: runs the stored procedure and returns the generated OracleDataReader object </td> </tr>
/// <Tr> <td> <B> creator </B>: Xia chuntao </td> </tr>
/// <Tr> <td> <B> creation time </B>: </td> </tr>
/// </Table> </summary>
/// <Param name = "storedProcName"> stored procedure name </param>
/// <Param name = "parameters"> List of parameter objects in a stored procedure (array) </param>
/// <Returns> OracleDataReader object </returns>
Public OracleDataReader RunProcedure (string storedProcName, IDataParameter [] parameters)
{
OracleDataReader returnReader;

Connection. Open ();
OracleCommand command = BuildCommand (storedProcName, parameters );
Command. CommandType = CommandType. StoredProcedure;

ReturnReader = command. ExecuteReader ();
// Connection. Close ();
Return returnReader;
}

/// <Summary> <table style = "font-size: 12px">
/// <Tr> <td> <B> function description </B>: runs the stored procedure and creates a DataSet object,
/// Save the running result to the specified able and return the DataSet object </td> </tr>
/// <Tr> <td> <B> creator </B>: Xia chuntao </td> </tr>
/// <Tr> <td> <B> creation time </B>: </td> </tr>
/// </Table> </summary>
/// <Param name = "storedProcName"> stored procedure name </param>
/// <Param name = "parameters"> List of parameter objects in a stored procedure (array) </param>
/// <Param name = "tableName"> data table name </param>
/// <Returns> DataSet object </returns>
Public DataSet RunProcedure (string storedProcName, IDataParameter [] parameters, string tableName)
{
DataSet dataSet = new DataSet ();
Connection. Open ();
OracleDataAdapter sqlDA = new OracleDataAdapter ();
SqlDA. SelectCommand = BuildCommand (storedProcName, parameters );
SqlDA. Fill (dataSet, tableName );
Connection. Close ();

Return dataSet;
}

/// <Summary> <table style = "font-size: 12px">
/// <Tr> <td> <B> function description </B>: runs the stored procedure and saves the running result to a specified table of an existing DataSet object, no return value </td> </tr>
/// <Tr> <td> <B> creator </B>: Xia chuntao </td> </tr>
/// <Tr> <td> <B> creation time </B>: </td> </tr>
/// </Table> </summary>
/// <Param name = "storedProcName"> stored procedure name </param>
/// <Param name = "parameters"> List of parameter objects in a stored procedure (array) </param>
/// <Param name = "dataSet"> DataSet object </param>
/// <Param name = "tableName"> data table name </param>
Public void RunProcedure (string storedProcName, IDataParameter [] parameters, DataSet dataSet, string tableName)
{
Connection. Open ();
OracleDataAdapter sqlDA = new OracleDataAdapter ();
SqlDA. SelectCommand = BuildCommand (storedProcName, parameters );
SqlDA. Fill (dataSet, tableName );
Connection. Close ();
}
# Endregion

# Region run SQL statements
/// <Summary> <table style = "font-size: 12px">
/// <Tr> <td> <B> function description </B>: runs the SQL statement related to database writing and returns the number of affected rows. </td> </tr>
/// <Tr> <td> <B> creator </B>: Xia chuntao </td> </tr>
/// <Tr> <td> <B> creation time </B>: </td> </tr>
/// </Table> </summary>
/// <Param name = "sqlString"> SQL statement </param>
/// <Returns> affected rows </returns>
Public int ExecNonQuery (string sqlString)
{
Int RowAffected;
// If (Connection. State. ToString () = "Closed") Connection. Open ();
Connection. Open ();
OracleCommand command = new OracleCommand (sqlString, Connection );
RowAffected = command. ExecuteNonQuery ();
Connection. Close ();
 
Return RowAffected;

}

/// <Summary> <table style = "font-size: 12px">
/// <Tr> <td> <B> function description </B>: runs an SQL statement and returns the OracleDataReader object </td> </tr>
/// <Tr> <td> <B> creator </B>: Xia chuntao </td> </tr>
/// <Tr> <td> <B> creation time </B>: </td> </tr>
/// </Table> </summary>
/// <Param name = "sqlString"> SQL statement </param>
/// <Returns> SqlDataReader object </returns>
Public OracleDataReader ExecSqlString (string sqlString)
{
OracleDataReader returnReader;

// If (Connection. State. ToString () = "Closed") Connection. Open ();
Connection. Open ();
OracleCommand command = new OracleCommand (sqlString, Connection );
ReturnReader = command. ExecuteReader ();
// Connection. Close ();

Return returnReader;
}

/// <Summary> <table style = "font-size: 12px">
/// <Tr> <td> <B> function description </B>: run an SQL statement and return a DataSet object </td> </tr>
/// <Tr> <td> <B> creator </B>: Xia chuntao </td> </tr>
/// <Tr> <td> <B> creation time </B>: </td> </tr>
/// </Table> </summary>
/// <Param name = "string"> SQL statement </param>
/// <Param name = "tableName"> data table name </param>
/// <Returns> DataSet object </returns>
Public DataSet ExecSqlString (string sqlString, string tableName)
{
DataSet dataSet = new DataSet ();
// If (Connection. State. ToString () = "Closed") Connection. Open ();
Connection. Open ();
OracleDataAdapter sqlDA = new OracleDataAdapter ();
SqlDA. SelectCommand = new OracleCommand (sqlString, Connection );
SqlDA. Fill (dataSet, tableName );
Connection. Close ();

Return dataSet;
}

/// <Summary> <table style = "font-size: 12px">
/// <Tr> <td> <B> function description </B>: runs an SQL statement and saves the running result to a specified table of an existing DataSet object, no return value </td> </tr>
/// <Tr> <td> <B> creator </B>: Xia chuntao </td> </tr>
/// <Tr> <td> <B> creation time </B>: </td> </tr>
/// </Table> </summary>
/// <Param name = "sqlString"> SQL statement </param>
/// <Param name = "dataSet"> DataSet object </param>
/// <Param name = "tableName"> data table name </param>
Public void ExecSqlString (string sqlString, DataSet dataSet, string tableName)
{
// If (Connection. State. ToString () = "Closed") Connection. Open ();
Connection. Open ();
OracleDataAdapter sqlDA = new OracleDataAdapter ();
SqlDA. SelectCommand = new OracleCommand (sqlString, Connection );
SqlDA. Fill (dataSet, tableName );
Connection. Close ();
}

/// <Summary> <table style = "font-size: 12px">
/// <Tr> <td> <B> function description </B>: runs an SQL statement and returns the first column of the first row of the query result, ignore other rows or columns </td> </tr>
/// <Tr> <td> <B> creator </B>: Xia chuntao </td> </tr>
/// <Tr> <td> <B> creation time </B>: </td> </tr>
/// </Table> </summary>
/// <Param name = "sqlString"> SQL statement </param>
/// <Returns> affected rows </returns>
Public object ExecScalar (string sqlString)
{
Object returnScalar;
// If (Connection. State. ToString () = "Closed") Connection. Open ();
Connection. Open ();
OracleCommand command = new OracleCommand (sqlString, Connection );
ReturnScalar = command. ExecuteScalar ();
// Connection. Close ();

Return returnScalar;
}
# Endregion

# Region close data connection
/// <Summary> <table style = "font-size: 12px">
/// <Tr> <td> <B> function description </B>: Disable data connection </td> </tr>
/// <Tr> <td> <B> creator </B>: Xia chuntao </td> </tr>
/// <Tr> <td> <B> creation time </B>: </td> </tr>
/// </Table> </summary>
Public void Close ()
{
If (Connection. State. ToString () = "Open ")
Connection. Close ();
}
# Endregion

# Region destructor
/// <Summary> <table style = "font-size: 12px">
/// <Tr> <td> <B> function description </B>: destructor, aftercare, and data connection release </td> </tr>
/// <Tr> <td> <B> creator </B>: Xia chuntao </td> </tr>
/// <Tr> <td> <B> creation time </B>: </td> </tr>
/// </Table> </summary>
~ DbObject ()
{
If (Connection. State. ToString () = "Open ")
Connection. Close ();
Connection. Dispose ();
}
# Endregion

}
}