調用oracle DB 的class 封裝代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】調用oracle DB 的class 封裝
作者:bingo / 发布于2012/9/13/ 712
將調用oracle的方法封裝成class,便于系統的調用，代碼重用
<div>
將調用oracle的方法封裝成class,便于系統的調用，代碼重用
----------------------------------------
using System;
using System.Data.OracleClient;
using  System.Data;
using System.Collections;
using System.Configuration;
namespace MISEB
{
/// <summary>
/// MISDB  的摘要描述。
/// </summary>
public class  MISEBDB
{
 //private OracleConnection  m_Connection;
 private OracleConnection  m_Connection;
 private OracleTransaction  m_Ot;
 public bool m_bIsTransaction;
 public string m_DBUser = "";
 public string m_TNS =  "";
 public string m_ConnectionStr =  "";
 
 ///  <summary>
 ///
 ///  </summary>
 /// <param  name="connectionstring">
 /// it is connection string when  p_FromAppSetting is false;
 /// it is appsetting key word when  p_FromAppSetting is true;
 /// </param>
 ///  <param name="p_FromAppSetting">
 /// it is a flag stands for  whether get connection string form appsetting or string  derectly.
 /// </param>
 public MISEBDB(string  source,bool  p_FromAppSetting)
 {
  m_Connection=new  OracleConnection();
  if(p_FromAppSetting)
  {
   m_Connection.ConnectionString  = ConfigurationSettings.AppSettings[source];
  }
  else
  {
   m_Connection.ConnectionString  =  source;
  }
  
  m_ConnectionStr  =  m_Connection.ConnectionString;
  if(m_ConnectionStr.IndexOf("user  id") >= 0)
  {
   string tmp =  m_ConnectionStr.Substring(m_ConnectionStr.IndexOf("user  id"));
   tmp = tmp.Substring(tmp.IndexOf("=") +  1,tmp.IndexOf(";") - tmp.IndexOf("=") - 1);
   m_DBUser  = tmp;
  }
  if(m_ConnectionStr.ToLower().IndexOf("data source") >=  0)
  {
   string tmp =  m_ConnectionStr.Substring(m_ConnectionStr.ToLower().IndexOf("data  source"));
   tmp = tmp.Substring(tmp.IndexOf("=") +  1,tmp.IndexOf(";") - tmp.IndexOf("=") - 1);
   m_TNS =  tmp;
  }
  m_bIsTransaction = false;
 }
 /// <summary>
 /// 查詢有DB  LINK的視圖
 /// </summary>
 /// <param  name="sql"></param>
 ///  <returns></returns>
 public DataTable  GetData_WithDBLink(string  sql)
 {
  OracleCommand command = new  OracleCommand();
  m_Connection.Open();
  command.Connection  = m_Connection;
  command.Transaction =  m_Connection.BeginTransaction(IsolationLevel.ReadCommitted);       
  command.CommandText  = "SET TRANSACTION READ ONLY";
  OracleDataReader oraDr =  command.ExecuteReader();
  command.CommandText =  sql;
  OracleDataAdapter oda = new  OracleDataAdapter();
  DataTable dtTable = new  DataTable();
  oda.SelectCommand =  command;
  oda.Fill(dtTable);
  return  dtTable;
 }
 
 public DataTable  getDT(string sql)
 {
  OracleDataAdapter  myOracleDataAdapter=new  OracleDataAdapter(sql,m_Connection);
  DataTable  m_DataTable=new  DataTable();
  myOracleDataAdapter.Fill(m_DataTable);
  return  m_DataTable;
 }
 public OracleDataReader GetDataReader(string  sql)
 {
  OracleCommand oc = new  OracleCommand(sql,m_Connection);
  OracleDataReader odr =  oc.ExecuteReader();
  
  return  odr;
 }
 public void SetData(string  sql)
 {
  OracleCommand myOracleCommand=new  OracleCommand(sql,m_Connection);
  if(myOracleCommand.Connection.State.ToString()!="Open")
   myOracleCommand.Connection.Open();
  myOracleCommand.ExecuteNonQuery();
  myOracleCommand.Connection.Close();
 }
 public bool IsNull(string  sql)
 {
  object c =  null;
  if(sql.ToUpper().Trim().StartsWith("SELECT"))
  {
   OracleCommand  myOC = new  OracleCommand(sql,m_Connection);
   if(myOC.Connection.State.ToString()!="Open")
    myOC.Connection.Open();
   c  =  myOC.ExecuteOracleScalar();
   myOC.Connection.Close();
   if(c  == null)
   {
    return  true;
   }
   else
   {
    return  false;
   }
  }
  else
  {
   OracleCommand  myOC = new  OracleCommand(sql,m_Connection);
   if(myOC.Connection.State.ToString()!="Open")
    myOC.Connection.Open();
   c  =  myOC.ExecuteNonQuery();
   myOC.Connection.Close();

   if  ( (int)c >  0)
   {
    return  false;
   }
   else
   {
    return  true;
   }
  }
 }
 
 public  object GetValue(string sql)
 {
  object  c;
  OracleCommand myOC=new  OracleCommand(sql,m_Connection);
  if(myOC.Connection.State.ToString()!="Open")
   myOC.Connection.Open();
  c=myOC.ExecuteOracleScalar();
  myOC.Connection.Close();
  return  c;
 }
 
 public void  close()
 {
  m_Connection.Close();
  m_bIsTransaction  = false;
 }
 public void  TrnsBegin()
 {
  if(m_Connection.State.ToString()  !=  "Open")
  {
   m_Connection.Open();
  }
  m_Ot  = m_Connection.BeginTransaction();
  m_bIsTransaction =  true;
 }

 public void  TrnsEnd()
 {
  m_Connection.Close();
  m_bIsTransaction  = false;
 }
 public void  TrnsCommit()
 {
  m_Ot.Commit();
 }
 public void  TrnsRollback()
 {
  m_Ot.Rollback();
 }
 public DataTable TrnsGetDT(string  sql)
 {
  OracleCommand oc = new  OracleCommand(sql,m_Connection);
  oc.Transaction =  m_Ot;
  OracleDataAdapter myOracleDataAdapter=new  OracleDataAdapter(oc);
  DataTable m_DataTable=new  DataTable();
  myOracleDataAdapter.Fill(m_DataTable);
  return  m_DataTable;
 }
 public void TrnsSetData(string  sql)
 {
  OracleCommand myOracleCommand=new  OracleCommand(sql,m_Connection);
  myOracleCommand.Transaction  =  m_Ot;
  myOracleCommand.ExecuteNonQuery();
 }
 public object TrnsGetObj(string  sql)
 {
  object  c;
  OracleCommand myOC=new  OracleCommand(sql,m_Connection);
  myOC.Transaction =  m_Ot;
  c=myOC.ExecuteOracleScalar();
  return  c;
 }
 public object[] runProc(string procname,dbparams  p)
 {
  int outcnt =  0;
  OracleCommand cmd = new  OracleCommand(procname,m_Connection);
  if(cmd.Connection.State.ToString()!="Open")
   cmd.Connection.Open();
  cmd.CommandType  = CommandType.StoredProcedure;
  for(int i = 0; i <  p.getParamCount(); i  ++)
  {
   if(p.getpName(i) != null  && p.getpName(i) != ""  )
   {
    if(p.getDirection(i)  ==  ParameterDirection.Input)
    {
     if(p.getValue(i)  != null && p.getValue(i) !=  "")
     {
     
      cmd.Parameters.Add(p.getpName(i),p.getValue(i));
     }
     else
     {
      cmd.Parameters.Add(p.getpName(i),System.DBNull.Value);
     }
    }
    
    if(p.getDirection(i)  ==  ParameterDirection.Output)
    {
     cmd.Parameters.Add(p.getpName(i),p.getType(i),p.getLen(i));
     cmd.Parameters[p.getpName(i)].Direction  = p.getDirection(i);
     outcnt  ++;
    }
    if(p.getDirection(i) ==  ParameterDirection.InputOutput)
    {
     cmd.Parameters.Add(p.getpName(i),p.getType(i),p.getLen(i));
     if(p.getValue(i)  != null && p.getValue(i) !=  "")
     {
     
      cmd.Parameters[p.getpName(i)].Value  =  p.getValue(i);
     }
     else
     {
      cmd.Parameters[p.getpName(i)].Value  =  System.DBNull.Value;
     }
     
     cmd.Parameters[p.getpName(i)].Direction  = p.getDirection(i);
     outcnt  ++;
    }
   }
  }
  cmd.ExecuteNonQuery();
  object[] record = new  object[outcnt];
  int add = 0;
  for(int i  = 0 ; i < p.getParamCount(); i  ++)
  {
   if(p.getDirection(i) ==  ParameterDirection.Output || p.getDirection(i) ==  ParameterDirection.InputOutput)
   {
    object[]  tmp =  {p.getpName(i),p.getType(i),p.getLen(i),cmd.Parameters[p.getpName(i)].Value};
    record[add]  = tmp;
    add  ++;
   }
  }
  return record;
  
 }

 public object[] runTxnProc(string procname,dbparams  p)
 {
  int outcnt =  0;
  OracleCommand cmd = new  OracleCommand(procname,m_Connection);
  cmd.Transaction =  m_Ot;
  cmd.CommandType =  CommandType.StoredProcedure;
  for(int i = 0; i <  p.getParamCount(); i  ++)
  {
   if(p.getpName(i) != null  && p.getpName(i) != ""  )
   {
    if(p.getDirection(i)  ==  ParameterDirection.Input)
    {
     if(p.getValue(i)  != null && p.getValue(i) !=  "")
     {
     
      cmd.Parameters.Add(p.getpName(i),p.getValue(i));
     }
     else
     {
      cmd.Parameters.Add(p.getpName(i),System.DBNull.Value);
     }
    }
    
    if(p.getDirection(i)  ==  ParameterDirection.Output)
    {
     cmd.Parameters.Add(p.getpName(i),p.getType(i),p.getLen(i));
     cmd.Parameters[p.getpName(i)].Direction  = p.getDirection(i);
     outcnt  ++;
    }
    if(p.getDirection(i) ==  ParameterDirection.InputOutput)
    {
     cmd.Parameters.Add(p.getpName(i),p.getType(i),p.getLen(i));
     if(p.getValue(i)  != null && p.getValue(i) !=  "")
     {
     
      cmd.Parameters[p.getpName(i)].Value  =  p.getValue(i);
     }
     else
     {
      cmd.Parameters[p.getpName(i)].Value  =  System.DBNull.Value;
     }
     
     cmd.Parameters[p.getpName(i)].Direction  = p.getDirection(i);
     outcnt  ++;
    }
   }
  }
  cmd.ExecuteNonQuery();
  object[] record = new  object[outcnt];
  int add = 0;
  for(int i  = 0 ; i < p.getParamCount(); i  ++)
  {
   if(p.getDirection(i) ==  ParameterDirection.Output || p.getDirection(i) ==  ParameterDirection.InputOutput)
   {
    object[]  tmp =  {p.getpName(i),p.getType(i),p.getLen(i),cmd.Parameters[p.getpName(i)].Value};
    record[add]  = tmp;
    add  ++;
   }
  }
  return record;
  
 }
}
#region class dbparams -- parameters class of  MISDB
public class dbparams
{
 public ArrayList  al;
 public readonly System.Data.OracleClient.OracleType varchar2 =  System.Data.OracleClient.OracleType.VarChar;
 public readonly  System.Data.OracleClient.OracleType number =  System.Data.OracleClient.OracleType.Number;
 public readonly  System.Data.ParameterDirection input =  System.Data.ParameterDirection.Input;
 public readonly  System.Data.ParameterDirection output =  System.Data.ParameterDirection.Output;
 public readonly  System.Data.ParameterDirection inout =  System.Data.ParameterDirection.InputOutput;
 public dbparams()
 {
  al = new  ArrayList();
 }
 public void  clean()
 {
  al.Clear();
 }
 public bool add(string pName,object Value,OracleType  type,ParameterDirection direction,int  len)
 {
  object[] param =  {pName,Value,type,direction,len};
  al.Add(param);
  return  true;
 }
 public int  getParamCount()
 {
  return  al.Count;
 }
 public string getpName(int  i)
 {
  return  ((object[])al[i])[0].ToString();
 }
 public string getValue(int  i)
 {
  return  ((object[])al[i])[1].ToString();
 }
 public OracleType getType(int  i)
 {
  return  (OracleType)((object[])al[i])[2];
 }
 public ParameterDirection getDirection(int  i)
 {
  return  (ParameterDirection)((object[])al[i])[3];
 }
 public int getLen(int  i)
 {
  return  Convert.ToInt32(((object[])al[i])[4].ToString());
 }
}
#endregion
}

</div>
试试其它关键字
　oracle　　DB　
同语言下
可能有用的
bingo贡献的其它代码(3)