• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营
  • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    Oracle 数据库操作类
    复制代码 代码如下:

    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);
    }
    }
    }
    上一篇:oracle 存储过程和函数例子
    下一篇:ORACLE 分区表的设计
  • 相关文章
  • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

    Oracle 数据库操作类 Oracle,数据库,操作,类,Oracle,