导读:1建站知识我有对Oracle最基本的数据库操作做了最为简单的归类，希望可以对后来者有所参考。另外，这个类需要有日志记录类百度seo网站优化公司网站建设。

复制代码 代码如下:

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; vTempSt网站推广优化seor = 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>The name.</param> /// <param>Type of the db.</param> /// <param>The value size</param> /// <param>The direction.</param> /// <param>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.Ope网站seo优化诊断工具n) { vOracleConnection.Open(); } return vOracleConnection; } private void CloseOracleDBConn(Oraseo网站优化cleConnection 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); } } }

关键词标签: 操作 标签