Oracle Database Operations

Source: Internet
Author: User
Tags oracleconnection

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 );
}
}
}
Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.