Oracle Database Operations

Copy codeThe Code is as follows:
Using System;
Using System. Data;
Using System. Configuration;
Using System. Data. OracleClient;
Using System. Text;
Using System. Windows. Forms;
Using System. Xml;
Using Transactions;
/// <Summary>
/// DB summary description Written By Luos. Luo, the creator of SalePlayer. Com
/// </Summary>
Public class MyOraDB
{
Public MyOraDB ()
{
}
Public int ExcuteSqlWithNoQuery (string vSql)
{
Int vI = 0;
OracleConnection vOracleConn = OpenOracleDBConn ();
Try
{
If (vOracleConn. State! = ConnectionState. Open)
{
VOracleConn. Open ();
}
OracleCommand vOracleCmd = new OracleCommand ();
VOracleCmd. Connection = vOracleConn;
VOracleCmd. CommandText = vSql;
VOracleCmd. CommandType = CommandType. Text;
VI = vOracleCmd. ExecuteNonQuery ();
}
Catch (Exception ex)
{
MyLog vMyLog = new MyLog ();
VMyLog. WriteLog ("MyOraDB", vSql, ex );
}
Finally
{
CloseOracleDBConn (vOracleConn );
}
Return vI;
}
Public int ExcuteSqlWithSingleNum (string vSql)
{
Int vI = 0;
OracleConnection vOracleConn = OpenOracleDBConn ();
Try
{
If (vOracleConn. State! = ConnectionState. Open)
{
VOracleConn. Open ();
}
OracleDataReader vOracleDataReader = CreateOracleDataReader (vSql );
While (vOracleDataReader. Read ())
{
VI = vOracleDataReader. GetInt32 (0 );
}
VOracleDataReader. Close ();
}
Catch (Exception ex)
{
MyLog vMyLog = new MyLog ();
VMyLog. WriteLog ("MyOraDB", vSql, ex );
}
Finally
{
CloseOracleDBConn (vOracleConn );
}
Return vI;
}
Public string ExcuteSqlWithSingleString (string vSql)
{
StringBuilder vTempStrBld = new StringBuilder ();
OracleConnection vOracleConn = OpenOracleDBConn ();
Try
{
If (vOracleConn. State! = ConnectionState. Open)
{
VOracleConn. Open ();
}
OracleDataReader vOracleDataReader = CreateOracleDataReader (vSql );
While (vOracleDataReader. Read ())
{
VTempStrBld. Append (vOracleDataReader. GetString (0 ));
}
VOracleDataReader. Close ();
}
Catch (Exception ex)
{
MyLog vMyLog = new MyLog ();
VMyLog. WriteLog ("MyOraDB", vSql, ex );
}
Finally
{
CloseOracleDBConn (vOracleConn );
}
Return vTempStrBld. ToString ();
}
Public DataTable ExcuteSqlWithDataTable (string vSql)
{
DataTable vDataTable = new DataTable ();
OracleConnection vOracleConn = OpenOracleDBConn ();
Try
{
If (vOracleConn. State! = ConnectionState. Open)
{
VOracleConn. Open ();
}
OracleDataAdapter vOracleDataAdapter = new OracleDataAdapter (vSql, vOracleConn );
VOracleDataAdapter. Fill (vDataTable );
}
Catch (Exception ex)
{
MyLog vMyLog = new MyLog ();
VMyLog. WriteLog ("MyOraDB", vSql, ex );
}
Finally
{
CloseOracleDBConn (vOracleConn );
}
Return vDataTable;
}
Public DataSet ExcuteSqlWithDataSet (string vSql)
{
DataSet vDataSet = new DataSet ();
OracleConnection vOracleConn = OpenOracleDBConn ();
Try
{
If (vOracleConn. State! = ConnectionState. Open)
{
VOracleConn. Open ();
}
OracleDataAdapter vOracleDataAdapter = new OracleDataAdapter (vSql, vOracleConn );
VOracleDataAdapter. Fill (vDataSet );
}
Catch (Exception ex)
{
MyLog vMyLog = new MyLog ();
VMyLog. WriteLog ("MyOraDB", vSql, ex );
}
Finally
{
CloseOracleDBConn (vOracleConn );
}
Return vDataSet;
}
Public string ExcuteSqlTransactionWithString (string [] vSqlArray)
{
Int vI = vSqlArray. Length;
String vSql = string. Empty;
OracleConnection vOracleConn = OpenOracleDBConn ();
If (vOracleConn. State! = ConnectionState. Open)
{
VOracleConn. Open ();
}
OracleTransaction vOracleTrans = vOracleConn. BeginTransaction (IsolationLevel. ReadCommitted );
OracleCommand vOracleCmd = new OracleCommand ();
VOracleCmd. Connection = vOracleConn;
VOracleCmd. Transaction = vOracleTrans;
Try
{
For (int I = 0; I <vI; I ++)
{
If (string. IsNullOrEmpty (vSqlArray [I]) = false)
{
VSql = vSqlArray [I];
VOracleCmd. CommandText = vSql;
VOracleCmd. ExecuteNonQuery ();
}
}
VOracleTrans. Commit ();
}
Catch (Exception ex)
{
VOracleTrans. Rollback ();
CloseOracleDBConn (vOracleConn );
MyLog vMyLog = new MyLog ();
VMyLog. WriteLog ("", vSql, ex );
Return ex. Message;
}
CloseOracleDBConn (vOracleConn );
Return "SUCCESS ";
}
Public void ExcuteProcedureWithNonQuery (string vProcedureName, OracleParameter [] parameters)
{
OracleConnection vOracleConn = OpenOracleDBConn ();
Try
{
If (vOracleConn. State! = ConnectionState. Open)
{
VOracleConn. Open ();
}
OracleCommand vOracleCmd = new OracleCommand ();
VOracleCmd. Connection = vOracleConn;
VOracleCmd. CommandText = vProcedureName;
VOracleCmd. CommandType = CommandType. StoredProcedure;
Foreach (OracleParameter parameter in parameters)
{
VOracleCmd. Parameters. Add (parameter );
}
VOracleCmd. ExecuteNonQuery ();
}
Catch (Exception ex)
{
MyLog vMyLog = new MyLog ();
WriteLog ("exception Message: ExcuteProcedureWithNonQuery:" + ex. Message );
}
Finally
{
CloseOracleDBConn (vOracleConn );
}
}
Public string ExcuteProcedureWithSingleString (string vProcedureName, OracleParameter [] parameters)
{
String vTempStr = string. Empty;
OracleParameter vOutMessage;
OracleConnection vOracleConn = OpenOracleDBConn ();
Try
{
If (vOracleConn. State! = ConnectionState. Open)
{
VOracleConn. Open ();
}
OracleCommand vOracleCmd = new OracleCommand ();
VOracleCmd. Connection = vOracleConn;
VOracleCmd. CommandText = vProcedureName;
VOracleCmd. CommandType = CommandType. StoredProcedure;
VOutMessage = new OracleParameter ("O_FLAG", OracleType. VarChar );
VOutMessage. Direction = ParameterDirection. Output;
Voutf-8 message. Size = 100;
VOracleCmd. Parameters. Add (vOutMessage );
Foreach (OracleParameter parameter in parameters)
{
VOracleCmd. Parameters. Add (parameter );
}
VOracleCmd. ExecuteNonQuery ();
VOracleCmd. Dispose ();
VOracleCmd = null;
VTempStr = vOutMessage. Value. ToString ();
}
Catch (Exception ex)
{
MyLog vMyLog = new MyLog ();
WriteLog ("exception Message: ExcuteProcedureWithNonQuery:" + ex. Message );
}
Finally
{
CloseOracleDBConn (vOracleConn );
}
Return vTempStr;
}
Public int ExcuteProcedureWithSingleNum (string vProcedureName, OracleParameter [] parameters)
{
Int vI = 0;
OracleParameter vOutMessage;
OracleConnection vOracleConn = OpenOracleDBConn ();
Try
{
If (vOracleConn. State! = ConnectionState. Open)
{
VOracleConn. Open ();
}
OracleCommand vOracleCmd = new OracleCommand ();
VOracleCmd. Connection = vOracleConn;
VOracleCmd. CommandText = vProcedureName;
VOracleCmd. CommandType = CommandType. StoredProcedure;
VOutMessage = new OracleParameter ("O_FLAG", OracleType. Int32 );
VOutMessage. Direction = ParameterDirection. Output;
Voutf-8 message. Size = 100;
VOracleCmd. Parameters. Add (vOutMessage );
Foreach (OracleParameter parameter in parameters)
{
VOracleCmd. Parameters. Add (parameter );
}
VOracleCmd. ExecuteNonQuery ();
VOracleCmd. Dispose ();
VOracleCmd = null;
VI = System. Convert. ToInt32 (vOutMessage. Value );
}
Catch (Exception ex)
{
MyLog vMyLog = new MyLog ();
WriteLog ("exception Message: ExcuteProcedureWithNonQuery:" + ex. Message );
}
Finally
{
CloseOracleDBConn (vOracleConn );
}
Return vI;
}
/// <Summary>
/// Creates the parameter.
/// </Summary>
/// <Param name = "name"> The name. </param>
/// <Param name = "dbType"> Type of the db. </param>
/// <Param name = "size"> The value size </param>
/// <Param name = "direction"> The direction. </param>
/// <Param name = "paramValue"> The param value. </param>
/// <Returns> </returns>
Public OracleParameter CreateParameter (string vProcedureName, OracleType vOracleType, int vSize, ParameterDirection vDirection, object vParamValue)
{
OracleParameter vOracleParameter = new OracleParameter ();
VOracleParameter. ParameterName = vProcedureName;
VOracleParameter. OracleType = vOracleType;
VOracleParameter. Size = vSize;
VOracleParameter. Direction = vDirection;
If (! (VOracleParameter. Direction = ParameterDirection. Output ))
{
VOracleParameter. Value = vParamValue;
}
Return vOracleParameter;
}
Private OracleConnection OpenOracleDBConn ()
{
String vConnStr = string. Empty;
String vOraDBName = System. Configuration. ConfigurationManager. AppSettings ["OraDBName"];
Switch (vOraDBName)
{
Case "MESDB_03 ":
VConnStr = "Data Source = SZMESDB; Persist Security Info = True; User ID = MESDB_03; Password = MESDB; Unicode = True ;";
Break;
Case "MESDBTEST_03 ":
VConnStr = "Data Source = SZMESDB; Persist Security Info = True; User ID = MESDB_03; Password = MESDB; Unicode = True ;";
Break;
Default:
VConnStr = "Data Source = SZMESDB; Persist Security Info = True; User ID = MESDBTEST_03; Password = MESDB; Unicode = True ;";
Break;
}
OracleConnection vOracleConnection = new OracleConnection (vConnStr );
If (vOracleConnection. State! = ConnectionState. Open)
{
VOracleConnection. Open ();
}
Return vOracleConnection;
}
Private void CloseOracleDBConn (OracleConnection vOracleConnection)
{
If (vOracleConnection. State = ConnectionState. Open)
{
VOracleConnection. Close ();
}
}
Private OracleDataReader CreateOracleDataReader (string vSql)
{
OracleConnection vOracleConn = OpenOracleDBConn ();
OracleCommand vOracleCommand = new OracleCommand (vSql, vOracleConn );
OracleDataReader vOracleDataReader = vOracleCommand. ExecuteReader ();
Return vOracleDataReader;
}
Private OracleDataAdapter CreateOleDbDataAdapter (string vSql)
{
OracleConnection vOracleConn = OpenOracleDBConn ();
OracleDataAdapter vOracleDataAdapter = new OracleDataAdapter (vSql, vOracleConn );
CloseOracleDBConn (vOracleConn );
Return vOracleDataAdapter;
}
Public string GetDateTimeNow ()
{
Return System. DateTime. Now. ToString ("u"). Replace ("Z", ""). Replace ("z ","");
}
Private void WriteLog (string vMessage)
{
Try
{
String vTempValue = string. Empty;
String vFilePath = Application. StartupPath;
String vXmlPath = System. Configuration. ConfigurationManager. receivettings ["LogAddress"];
VXmlPath = vFilePath + vXmlPath;
XmlDocument xmlDoc = new XmlDocument ();
XmlDoc. Load (vXmlPath );
XmlNode root = xmlDoc. SelectSingleNode ("// root ");
XmlElement xe = xmlDoc. CreateElement ("Node"); // create a Node
XmlElement xesub01 = xmlDoc. CreateElement ("RowNum ");
Xesub01.InnerText = root. ChildNodes. Count. ToString ();
Xe. AppendChild (xesub01); // Add to Node
XmlElement xesub02 = xmlDoc. CreateElement ("Message ");
Xesub02.InnerText = vMessage;
Xe. AppendChild (xesub02); // Add to Node
XmlElement xesub03 = xmlDoc. CreateElement ("InserTime ");
Xesub03.InnerText = GetDateTimeNow ();
Xe. AppendChild (xesub03); // Add to Node
Root. AppendChild (xe); // Add to Node
XmlDoc. Save (vXmlPath );
Root = null;
XmlDoc = null;
}
Catch (Exception ex)
{
WriteLog (ex. Message );
}
}
}