using System;
 using System.Data;
 using System.Data.OracleClient;
 using System.Collections;
 using System.Reflection;
 namespace MyOraComm
 {
 /// <summary>
 /// ConnDbForOracle 的摘要說明。
 /// </summary>
 public class ConnForOracle
 {
  protected OracleConnection Connection;
  private string connectionString;
  public ConnForOracle()
  {
  string connStr;
  connStr = System.Configuration.ConfigurationSettings.AppSettings["connStr"].ToString();
  connectionString = connStr;
  Connection = new OracleConnection(connectionString);
  }
 
  #region 帶參數(shù)的構(gòu)造函數(shù)
  /// <summary>
  /// 帶參數(shù)的構(gòu)造函數(shù)
  /// </summary>
  /// <param name="ConnString">數(shù)據(jù)庫聯(lián)接字符串</param>
  public ConnForOracle(string ConnString)
  {
  string connStr;
  connStr = System.Configuration.ConfigurationSettings.AppSettings[ConnString].ToString();
  Connection = new OracleConnection(connStr);
  }
  #endregion
  #region 打開數(shù)據(jù)庫
  /// <summary>
  /// 打開數(shù)據(jù)庫
  /// </summary>
  public void OpenConn()
  {
  if(this.Connection.State!=ConnectionState.Open)
   this.Connection.Open();
  }
  #endregion
  #region 關(guān)閉數(shù)據(jù)庫聯(lián)接
  /// <summary>
  /// 關(guān)閉數(shù)據(jù)庫聯(lián)接
  /// </summary>
  public void CloseConn()
  {
  if(Connection.State==ConnectionState.Open)
   Connection.Close();
  }
  #endregion
  #region 執(zhí)行SQL語句，返回?cái)?shù)據(jù)到DataSet中
  /// <summary>
  /// 執(zhí)行SQL語句，返回?cái)?shù)據(jù)到DataSet中
  /// </summary>
  /// <param name="sql">sql語句</param>
  /// <param name="DataSetName">自定義返回的DataSet表名</param>
  /// <returns>返回DataSet</returns>
  public DataSet ReturnDataSet(string sql,string DataSetName)
  {
  DataSet dataSet=new DataSet();
  OpenConn();
  OracleDataAdapter OraDA=new OracleDataAdapter(sql,Connection);
  OraDA.Fill(dataSet,DataSetName);
  //  CloseConn();
  return dataSet;
  }
  #endregion
  #region 執(zhí)行Sql語句,返回帶分頁功能的dataset
  /// <summary>
  /// 執(zhí)行Sql語句,返回帶分頁功能的dataset
  /// </summary>
  /// <param name="sql">Sql語句</param>
  /// <param name="PageSize">每頁顯示記錄數(shù)</param>
  /// <param name="CurrPageIndex"><當(dāng)前頁/param>
  /// <param name="DataSetName">返回dataset表名</param>
  /// <returns>返回DataSet</returns>
  public DataSet ReturnDataSet(string sql,int PageSize,int CurrPageIndex,string DataSetName)
  {
  DataSet dataSet=new DataSet();
  OpenConn();
  OracleDataAdapter OraDA=new OracleDataAdapter(sql,Connection);
  OraDA.Fill(dataSet,PageSize * (CurrPageIndex - 1), PageSize,DataSetName);
  //  CloseConn();
  return dataSet;
  }
  #endregion
  #region 執(zhí)行SQL語句，返回 DataReader,用之前一定要先.read()打開,然后才能讀到數(shù)據(jù)
  /// <summary>
  /// 執(zhí)行SQL語句，返回 DataReader,用之前一定要先.read()打開,然后才能讀到數(shù)據(jù)
  /// </summary>
  /// <param name="sql">sql語句</param>
  /// <returns>返回一個(gè)OracleDataReader</returns>
  public OracleDataReader ReturnDataReader(String sql)
  {
  OpenConn();
  OracleCommand command = new OracleCommand(sql,Connection);
  return command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
  }
  #endregion
  #region 執(zhí)行SQL語句，返回記錄總數(shù)數(shù)
  /// <summary>
  /// 執(zhí)行SQL語句，返回記錄總數(shù)數(shù)
  /// </summary>
  /// <param name="sql">sql語句</param>
  /// <returns>返回記錄總條數(shù)</returns>
  public int GetRecordCount(string sql)
  {
  int recordCount = 0;
  OpenConn();
  OracleCommand command = new OracleCommand(sql,Connection);
  OracleDataReader dataReader = command.ExecuteReader();
  while(dataReader.Read())
  {
   recordCount++;
  }
  dataReader.Close();
  //  CloseConn();
  return recordCount;
  }
  #endregion
  #region 取當(dāng)前序列,條件為seq.nextval或seq.currval
  /// <summary>
  /// 取當(dāng)前序列
  /// </summary>
  /// <param name="seqstr"></param>
  /// <param name="table"></param>
  /// <returns></returns>
  public decimal GetSeq(string seqstr)
  {
  decimal seqnum = 0;
  string sql="select "+seqstr+" from dual";
  OpenConn();
  OracleCommand command = new OracleCommand(sql,Connection);
  OracleDataReader dataReader = command.ExecuteReader();
  if(dataReader.Read())
  {
   seqnum=decimal.Parse(dataReader[0].ToString());
  }
  dataReader.Close();
  //  CloseConn();
  return seqnum;
  }
  #endregion
  #region 執(zhí)行SQL語句,返回所影響的行數(shù)
  /// <summary>
  /// 執(zhí)行SQL語句,返回所影響的行數(shù)
  /// </summary>
  /// <param name="sql"></param>
  /// <returns></returns>
  public int ExecuteSQL(string sql)
  {
  int Cmd=0;
  OpenConn();
  OracleCommand command = new OracleCommand(sql,Connection);
  try
  {
   Cmd =command.ExecuteNonQuery(); 
  }
  catch
  {
  
  }
  finally
  {
   //  CloseConn();
  }
  return Cmd;
  }
  #endregion
  //?。剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑?
  //　＝＝用hashTable對(duì)數(shù)據(jù)庫進(jìn)行insert,update,del操作,注意此時(shí)只能用默認(rèn)的數(shù)據(jù)庫連接"connstr"＝＝
  //?。剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑?
  #region 根據(jù)表名及哈稀表自動(dòng)插入數(shù)據(jù)庫 用法：Insert("test",ht)
  public int Insert(string TableName,Hashtable ht)
  {
  OracleParameter[] Parms=new OracleParameter[ht.Count];
  IDictionaryEnumerator et = ht.GetEnumerator();
  DataTable dt=GetTabType(TableName);
  System.Data.OracleClient.OracleType otype;
  int size=0;
  int i=0;
  
  while ( et.MoveNext() ) // 作哈希表循環(huán)
  {
   GetoType(et.Key.ToString().ToUpper(),dt,out otype,out size);
   System.Data.OracleClient.OracleParameter op=MakeParam(":"+et.Key.ToString(),otype,size,et.Value.ToString());
   Parms[i]=op; // 添加SqlParameter對(duì)象
   i=i+1;
  }
  string str_Sql=GetInsertSqlbyHt(TableName,ht); // 獲得插入sql語句
  int val=ExecuteNonQuery(str_Sql,Parms);
  return val;
  }
  #endregion
 
  #region 根據(jù)相關(guān)條件對(duì)數(shù)據(jù)庫進(jìn)行更新操作 用法：Update("test","Id=:Id",ht); 
  public int Update(string TableName,string ht_Where, Hashtable ht)
  {
  OracleParameter[] Parms=new OracleParameter[ht.Count];
  IDictionaryEnumerator et = ht.GetEnumerator();
  DataTable dt=GetTabType(TableName);
  System.Data.OracleClient.OracleType otype;
  int size=0;
  int i=0;
  // 作哈希表循環(huán)
  while ( et.MoveNext() )
  {
   GetoType(et.Key.ToString().ToUpper(),dt,out otype,out size);
   System.Data.OracleClient.OracleParameter op=MakeParam(":"+et.Key.ToString(),otype,size,et.Value.ToString());
   Parms[i]=op; // 添加SqlParameter對(duì)象
   i=i+1;
  }
  string str_Sql=GetUpdateSqlbyHt(TableName,ht_Where,ht); // 獲得插入sql語句
  int val=ExecuteNonQuery(str_Sql,Parms);
  return val;
  }
  #endregion
 
  #region del操作,注意此處條件個(gè)數(shù)與hash里參數(shù)個(gè)數(shù)應(yīng)該一致 用法：Del("test","Id=:Id",ht)
  public int Del(string TableName,string ht_Where,Hashtable ht)
  {
  OracleParameter[] Parms=new OracleParameter[ht.Count];
  IDictionaryEnumerator et = ht.GetEnumerator();
  DataTable dt=GetTabType(TableName);
  System.Data.OracleClient.OracleType otype;
  int i=0;
  int size=0;
  // 作哈希表循環(huán)
  while ( et.MoveNext() )
  {
   GetoType(et.Key.ToString().ToUpper(),dt,out otype,out size);
   System.Data.OracleClient.OracleParameter op=MakeParam(":"+et.Key.ToString(),et.Value.ToString());
   Parms[i]=op; // 添加SqlParameter對(duì)象
   i=i+1;
  }
  string str_Sql=GetDelSqlbyHt(TableName,ht_Where,ht); // 獲得刪除sql語句
  int val=ExecuteNonQuery(str_Sql,Parms);
  return val;
  }
  #endregion
  //　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
  //?。剑剑剑剑剑剑剑缴厦嫒齻€(gè)操作的內(nèi)部調(diào)用函數(shù)＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
  //　＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
  #region 根據(jù)哈稀表及表名自動(dòng)生成相應(yīng)insert語句(參數(shù)類型的)
  /// <summary>
  /// 根據(jù)哈稀表及表名自動(dòng)生成相應(yīng)insert語句
  /// </summary>
  /// <param name="TableName">要插入的表名</param>
  /// <param name="ht">哈稀表</param>
  /// <returns>返回sql語句</returns>
  public static string GetInsertSqlbyHt(string TableName,Hashtable ht)
  {
  string str_Sql="";
  int i=0;
  int ht_Count=ht.Count; // 哈希表個(gè)數(shù)
  IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
  string before="";
  string behide="";
  while ( myEnumerator.MoveNext() )
  {
   if (i==0)
   {
   before="("+myEnumerator.Key;
   }
   else if (i+1==ht_Count)
   {
   before=before+","+myEnumerator.Key+")";
   }
   else
   {
   before=before+","+myEnumerator.Key;
   }
   i=i+1;
  }
  behide=" Values"+before.Replace(",",",:").Replace("(","(:");
  str_Sql="Insert into "+TableName+before+behide;
  return str_Sql;
  }
  #endregion
  #region 根據(jù)表名，where條件，哈稀表自動(dòng)生成更新語句(參數(shù)類型的)
  public static string GetUpdateSqlbyHt(string Table,string ht_Where,Hashtable ht)
  {
  string str_Sql="";
  int i=0;
  int ht_Count=ht.Count; // 哈希表個(gè)數(shù)
  IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
  while ( myEnumerator.MoveNext() )
  {
   if (i==0)
   {
   if (ht_Where.ToString().ToLower().IndexOf((myEnumerator.Key+"=:"+myEnumerator.Key).ToLower())==-1)
   {
    str_Sql=myEnumerator.Key+"=:"+myEnumerator.Key;
   }
   }
   else
   {
   if (ht_Where.ToString().ToLower().IndexOf((":"+myEnumerator.Key+" ").ToLower())==-1)
   {
    str_Sql=str_Sql+","+myEnumerator.Key+"=:"+myEnumerator.Key;
   }
   
   }
   i=i+1;
  }
  if (ht_Where==null || ht_Where.Replace(" ","")=="") // 更新時(shí)候沒有條件
  {
   str_Sql="update "+Table+" set "+str_Sql;
  }
  else
  {
   str_Sql="update "+Table+" set "+str_Sql+" where "+ht_Where;
  }
  str_Sql=str_Sql.Replace("set ,","set ").Replace("update ,","update ");
  return str_Sql;
  }
  #endregion
  #region 根據(jù)表名，where條件，哈稀表自動(dòng)生成del語句(參數(shù)類型的)
  public static string GetDelSqlbyHt(string Table,string ht_Where,Hashtable ht)
  {
  string str_Sql="";
  int i=0;
  
  int ht_Count=ht.Count; // 哈希表個(gè)數(shù)
  IDictionaryEnumerator myEnumerator = ht.GetEnumerator();
  while ( myEnumerator.MoveNext() )
  {
   if (i==0)
   {
   if (ht_Where.ToString().ToLower().IndexOf((myEnumerator.Key+"=:"+myEnumerator.Key).ToLower())==-1)
   {
    str_Sql=myEnumerator.Key+"=:"+myEnumerator.Key;
   }
   }
   else
   {
   if (ht_Where.ToString().ToLower().IndexOf((":"+myEnumerator.Key+" ").ToLower())==-1)
   {
    str_Sql=str_Sql+","+myEnumerator.Key+"=:"+myEnumerator.Key;
   }
   
   }
   i=i+1;
  }
  if (ht_Where==null || ht_Where.Replace(" ","")=="") // 更新時(shí)候沒有條件
  {
   str_Sql="Delete "+Table;
  }
  else
  {
   str_Sql="Delete "+Table+" where "+ht_Where;
  }
  return str_Sql;
  }
  #endregion
  #region 生成oracle參數(shù)
  /// <summary>
  /// 生成oracle參數(shù)
  /// </summary>
  /// <param name="ParamName">字段名</param>
  /// <param name="otype">數(shù)據(jù)類型</param>
  /// <param name="size">數(shù)據(jù)大小</param>
  /// <param name="Value">值</param>
  /// <returns></returns>
  public static OracleParameter MakeParam(string ParamName,System.Data.OracleClient.OracleType otype,int size,Object Value) 
  {
  OracleParameter para=new OracleParameter(ParamName,Value);
  para.OracleType=otype;
  para.Size=size;
  return para;
  }
  #endregion
  #region 生成oracle參數(shù)
  public static OracleParameter MakeParam(string ParamName,string Value) 
  {
  return new OracleParameter(ParamName, Value);
  }
  #endregion
  #region 根據(jù)表結(jié)構(gòu)字段的類型和長(zhǎng)度拼裝oracle sql語句參數(shù)
  public static void GetoType(string key,DataTable dt,out System.Data.OracleClient.OracleType otype,out int size)
  {
  DataView dv=dt.DefaultView;
  dv.RowFilter="column_name='"+key+"'";
  string fType=dv[0]["data_type"].ToString().ToUpper();
  switch (fType)
  {
   case "DATE":
   otype= OracleType.DateTime;
   size=int.Parse(dv[0]["data_length"].ToString());
   break;
   case "CHAR":
   otype= OracleType.Char;
   size=int.Parse(dv[0]["data_length"].ToString());
   break;
   case "LONG":
   otype= OracleType.Double;
   size=int.Parse(dv[0]["data_length"].ToString());
   break;
   case "NVARCHAR2":
   otype= OracleType.NVarChar;
   size=int.Parse(dv[0]["data_length"].ToString());
   break;
   case "VARCHAR2":
   otype= OracleType.NVarChar;
   size=int.Parse(dv[0]["data_length"].ToString());
   break;
   default:
   otype= OracleType.NVarChar;
   size=100;
   break;
  }
  }
  #endregion
  #region動(dòng)態(tài) 取表里字段的類型和長(zhǎng)度,此處沒有動(dòng)態(tài)用到connstr,是默認(rèn)的！by/文少
  public System.Data.DataTable GetTabType(string tabnale)
  {
  string sql="select column_name,data_type,data_length from all_tab_columns where table_name='"+tabnale.ToUpper()+"'";
  OpenConn();
  return (ReturnDataSet(sql,"dv")).Tables[0];
  
  }
  #endregion
  #region 執(zhí)行sql語句 
  public int ExecuteNonQuery(string cmdText, params OracleParameter[] cmdParms) 
  {
  
  OracleCommand cmd = new OracleCommand();
  OpenConn(); 
  cmd.Connection=Connection;
  cmd.CommandText = cmdText;
  if (cmdParms != null) 
  {
   foreach (OracleParameter parm in cmdParms)
   cmd.Parameters.Add(parm);
  }
  int val = cmd.ExecuteNonQuery();
  cmd.Parameters.Clear();
  //  conn.CloseConn();
  return val;
  }
  #endregion
  //?。剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑?
  //?。剑剑剑剑剑剑剑剑絻?nèi)部調(diào)用函數(shù)完＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝＝
  //?。剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑剑?
 }
 }