Oracle 数据库操作类

时间:2021-09-10 09:26:37

代码如下:


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 的摘要说明 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("异常信息: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; 
vOutMessage.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("异常信息: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; 
vOutMessage.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("异常信息: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.AppSettings["LogAddress"]; 
vXmlPath = vFilePath + vXmlPath; 
XmlDocument xmlDoc = new XmlDocument(); 
xmlDoc.Load(vXmlPath); 
XmlNode root = xmlDoc.SelectSingleNode("//root"); 
XmlElement xe = xmlDoc.CreateElement("Node");//创建一个节点 
XmlElement xesub01 = xmlDoc.CreateElement("RowNum"); 
xesub01.InnerText = root.ChildNodes.Count.ToString(); 
xe.AppendChild(xesub01);//添加到节点中 
XmlElement xesub02 = xmlDoc.CreateElement("Message"); 
xesub02.InnerText = vMessage; 
xe.AppendChild(xesub02);//添加到节点中 
XmlElement xesub03 = xmlDoc.CreateElement("InserTime"); 
xesub03.InnerText = GetDateTimeNow(); 
xe.AppendChild(xesub03);//添加到节点中 
root.AppendChild(xe);//添加到节点中 
xmlDoc.Save(vXmlPath); 
root = null; 
xmlDoc = null; 

catch (Exception ex) 

WriteLog(ex.Message);