<div class="codetitle"><a style="CURSOR: pointer" data="52143" class="copybut" id="copybut52143" onclick="doCopy('code52143')"> 代码如下:<div class="codebody" id="code52143"> using System; using System.Data; using System.Configuration; using System.Data.OracleClient; using System.Text; using System.Windows.Forms; using System.Xml; using Transactions; /// /// DB 的摘要说明 Written By Luos.Luo,the creator of SalePlayer.Com /// 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",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",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",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",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("",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; } /// /// Creates the parameter. /// /// <param name="name">The name. /// <param name="dbType">Type of the db. /// <param name="size">The value size /// <param name="direction">The direction. /// <param name="paramValue">The param value. /// 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); } } }
(编辑:鄂州站长网)
【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容!
|