asp.netOracle数据库访问操作类

   ///<summary>
   ///数据访问抽象基础类
   ///
   ///</summary>
publicclassDBBase
{

   //数据库连接字符串(web.config来配置)，可以动态更改connectionString支持多数据库.       
   publicstaticstringconnectionString=System.Configuration.ConfigurationManager.ConnectionStrings["ConnectionString1"].ToString();
   publicDBBase()
   {
   }

   #region检查用户名是否存在
   ///<summary>
   ///检查用户名是否存在，存在返回true，不存在返回false
   ///</summary>
   ///<paramname="strSql"></param>
   ///<returns></returns>
   publicstaticboolExists(stringstrSql)
   {

       using(OracleConnectionconnection=newOracleConnection(connectionString))
       {
           connection.Open();
           OracleCommandmyCmd=newOracleCommand(strSql,connection);
           try
           { 
               objectobj=myCmd.ExecuteScalar();//返回结果的第一行一列
               myCmd.Parameters.Clear();
               if((Object.Equals(obj,null))||(Object.Equals(obj,System.DBNull.Value)))
               {
                   returnfalse;
               }
               else
               {
                   returntrue;
               }          
           }
           catch(Exceptionex)
            {
                throwex;
            }
      }
   }

   #endregion

   #region 执行简单SQL语句返回影响的记录数

   ///<summary>
   ///执行SQL语句，返回影响的记录数
   ///</summary>
   ///<paramname="SQLString">SQL语句</param>
   ///<returns>影响的记录数</returns>
   publicstaticintExecuteSql(stringSQLString)
   {

       OracleConnectionconnection=null;
       OracleCommandcmd=null;
       try
       {
           connection=newOracleConnection(connectionString);
           cmd=newOracleCommand(SQLString,connection);
           connection.Open();
           introws=cmd.ExecuteNonQuery();
           returnrows;
       }
       finally
       {
           if(cmd!=null)
           {
               cmd.Dispose();
           }
           if(connection!=null)
           {
               connection.Close();
               connection.Dispose();
           }
       }
   }
      #endregion

   #region  执行查询语句，返回SqlDataReader
   ///<summary>
   ///执行查询语句，返回SqlDataReader(注意：调用该方法后，一定要对SqlDataReader进行Close)
   ///</summary>
   ///<paramname="strSQL">查询语句</param>
   ///<returns>SqlDataReader</returns>
   publicstaticOracleDataReaderExecuteReader(stringstrSQL)
   {
       OracleConnectionconnection=newOracleConnection(connectionString);
       OracleCommandcmd=newOracleCommand(strSQL,connection);
       try
       {
           connection.Open();
           OracleDataReadermyReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);
           returnmyReader;
       }
       catch(System.Data.OracleClient.OracleExceptione)
       {
           throwe;
       }
       finally
       {
           connection.Close();

       }
   }
   #endregion

   #region 执行SQL查询语句，返回DataTable数据表
   ///<summary>
   ///执行SQL查询语句
   ///</summary>
   ///<paramname="sqlStr"></param>
   ///<returns>返回DataTable数据表</returns>
   publicstaticDataTableGetDataTable(stringsqlStr)
   {
       OracleConnectionmycon=newOracleConnection(connectionString);
       OracleCommandmycmd=newOracleCommand(sqlStr,mycon);
       DataTabledt=newDataTable();
       OracleDataAdapterda=null;
       try
       {
           mycon.Open();
           da=newOracleDataAdapter(sqlStr,mycon);
           da.Fill(dt);

       }
       catch(Exceptionex)
       {

           thrownewException(ex.ToString());
       }
       finally
       {
           mycon.Close();
       }
       returndt;
   }
   #endregion

   #region存储过程操作
   ///<summary>
   /// 运行存储过程,返回datatable;
   ///</summary>
   ///<paramname="storedProcName">存储过程名称</param>
   ///<paramname="parameters">参数</param>
   ///<returns></returns>
   publicstaticDataTableRunProcedureDatatable(stringstoredProcName,IDataParameter[]parameters)
   {
       using(OracleConnectionconnection=newOracleConnection(connectionString))
       {
           DataSetds=newDataSet();
           connection.Open();
           OracleDataAdaptersqlDA=newOracleDataAdapter();
           sqlDA.SelectCommand=BuildQueryCommand(connection,storedProcName,parameters);
           sqlDA.Fill(ds);
           connection.Close();
           returnds.Tables[0];
       }
   }
   ///<summary>
   ///执行存储过程
   ///</summary>
   ///<paramname="storedProcName">存储过程名称</param>
   ///<paramname="parameters">参数</param>
   ///<returns></returns>
   publicstaticintRunProcedure(stringstoredProcName,IDataParameter[]parameters)
   {
       using(OracleConnectionconnection=newOracleConnection(connectionString))
       {
           try
           {
               connection.Open();
               OracleCommandcommand=newOracleCommand(storedProcName,connection);
               command.CommandType=CommandType.StoredProcedure;
               foreach(OracleParameterparameterinparameters)
               {
                   if(parameter!=null)
                   {
                       //检查未分配值的输出参数,将其分配以DBNull.Value.
                       if((parameter.Direction==ParameterDirection.InputOutput||parameter.Direction==ParameterDirection.Input)&&
                           (parameter.Value==null))
                       {
                           parameter.Value=DBNull.Value;
                       }
                       command.Parameters.Add(parameter);
                   }
               }
              introws=command.ExecuteNonQuery();
              returnrows;
           }

           finally
           {
               connection.Close();
           }
       }
   }

   ///<summary>
   ///构建OracleCommand对象(用来返回一个结果集，而不是一个整数值)
   ///</summary>
   ///<paramname="connection">数据库连接</param>
   ///<paramname="storedProcName">存储过程名</param>
   ///<paramname="parameters">存储过程参数</param>
   ///<returns>OracleCommand</returns>
   privatestaticOracleCommandBuildQueryCommand(OracleConnectionconnection,stringstoredProcName,IDataParameter[]parameters)
   {
       OracleCommandcommand=newOracleCommand(storedProcName,connection);
       command.CommandType=CommandType.StoredProcedure;
       foreach(OracleParameterparameterinparameters)
       {
           if(parameter!=null)
           {
               //检查未分配值的输出参数,将其分配以DBNull.Value.
               if((parameter.Direction==ParameterDirection.InputOutput||parameter.Direction==ParameterDirection.Input)&&
                   (parameter.Value==null))
               {
                   parameter.Value=DBNull.Value;
               }
               command.Parameters.Add(parameter);
           }
       }
       returncommand;
   }

   #endregion

   #region事务处理

   ///<summary>
   ///执行多条SQL语句(list的形式)，实现数据库事务。
   ///</summary>
   ///<paramname="SQLStringList">多条SQL语句</param>   
   ///调用Transaction对象的Commit方法来完成事务，或调用Rollback方法来取消事务。
   publicstaticintExecuteSqlTran(List<String>SQLStringList)
   {
       using(OracleConnectionconnection=newOracleConnection(connectionString))
       {
           connection.Open();
           //为事务创建一个命令
           OracleCommandcmd=newOracleCommand();
           cmd.Connection=connection;
           OracleTransactiontx=connection.BeginTransaction();//启动一个事务
           cmd.Transaction=tx;
           try
           {
               intcount=0;
               for(intn=0;n<SQLStringList.Count;n++)
               {
                   stringstrsql=SQLStringList[n];
                   if(strsql.Trim().Length>1)
                   {
                       cmd.CommandText=strsql;
                       count+=cmd.ExecuteNonQuery();
                   }
               }
               tx.Commit();//用Commit方法来完成事务
               returncount;//
           }
           catch
           {
               tx.Rollback();//出现错误，事务回滚！
               return0;
           }
           finally
           {
               cmd.Dispose();
               connection.Close();//关闭连接
           }
       }
   }
   #endregion
   #region事务处理

   ///<summary>
   ///执行多条SQL语句(字符串数组形式)，实现数据库事务。
   ///</summary>
   ///<paramname="SQLStringList">多条SQL语句</param>   
   ///调用Transaction对象的Commit方法来完成事务，或调用Rollback方法来取消事务。
   publicstaticintExecuteTransaction(string[]SQLStringList,intp)
   {
       using(OracleConnectionconnection=newOracleConnection(connectionString))
       {
           connection.Open();
           //为事务创建一个命令
           OracleCommandcmd=newOracleCommand();
           cmd.Connection=connection;
           OracleTransactiontx=connection.BeginTransaction();//启动一个事务
           cmd.Transaction=tx;
           try
           {
               intcount=0;
               for(intn=0;n<p;n++)
               {
                   stringstrsql=SQLStringList[n];
                   if(strsql.Trim().Length>1)
                   {
                       cmd.CommandText=strsql;
                       count+=cmd.ExecuteNonQuery();
                   }
               }
               tx.Commit();//用Commit方法来完成事务
               returncount;//
           }
           catch
           {
               tx.Rollback();//出现错误，事务回滚！
               return0;
           }
           finally
           {
               cmd.Dispose();
               connection.Close();//关闭连接
           }
       }
   }

   #endregion
   ///<summary>
   ///执行存储过程获取所需编号（各表主键）
   ///</summary>
   ///<paramname="FlowName">存储过程参数</param>
   ///<paramname="StepLen">存储过程参数（默认为1）</param>
   ///<returns>编号（各表主键）</returns>
   publicstaticstringGet_FlowNum(stringFlowName,intStepLen=1)
   {
       OracleConnectionmycon=newOracleConnection(connectionString);
       try
       {
           mycon.Open();
           OracleCommandMyCommand=newOracleCommand("ALARM_GET_FLOWNUMBER",mycon);
           MyCommand.CommandType=CommandType.StoredProcedure;
           MyCommand.Parameters.Add(newOracleParameter("I_FlowName",OracleType.VarChar,50));
           MyCommand.Parameters["I_FlowName"].Value=FlowName;
           MyCommand.Parameters.Add(newOracleParameter("I_SeriesNum",OracleType.Number));
           MyCommand.Parameters["I_SeriesNum"].Value=StepLen;
           MyCommand.Parameters.Add(newOracleParameter("O_FlowValue",OracleType.Number));
           MyCommand.Parameters["O_FlowValue"].Direction=ParameterDirection.Output;
           MyCommand.ExecuteNonQuery();
           returnMyCommand.Parameters["O_FlowValue"].Value.ToString();
       }
       catch
       {
           return"";
       }
       finally
       {
           mycon.Close();
       }
   }

}