C#_访问oracle通用代码

usingSystem;usingSystem.Data;usingSystem.Data.OracleClient;usingSystem.Web.UI.WebControls;namespaceSiFenManager.Util{///summary///数据库通用操作类////summarypublicclassDatabase{protectedOracleConnectioncon;//连接对象publicDatabase(){con=newOracleConnection(DafangFramework.AppConfig.DataBaseConnectionString);}publicDatabase(stringconstr){con=newOracleConnection(constr);}#region打开数据库连接///summary///打开数据库连接////summaryprivatevoidOpen(){//打开数据库连接if(con.State==ConnectionState.Closed){try{//打开数据库连接con.Open();}catch(Exceptione){throwe;}}}#endregion#region关闭数据库连接///summary///关闭数据库连接////summaryprivatevoidClose(){//判断连接的状态是否已经打开if(con.State==ConnectionState.Open){con.Close();}}#endregion#region执行查询语句，返回OracleDataReader(注意：调用该方法后，一定要对OracleDataReader进行Close)///summary///执行查询语句，返回OracleDataReader(注意：调用该方法后，一定要对OracleDataReader进行Close)////summary///paramname=sql查询语句/param///returnsOracleDataReader/returnspublicOracleDataReaderExecuteReader(stringsql){OracleDataReadermyReader;Open();OracleCommandcmd=newOracleCommand(sql,con);myReader=cmd.ExecuteReader(CommandBehavior.CloseConnection);returnmyReader;}#endregion#region执行带参数的SQL语句///summary///执行SQL语句，返回影响的记录数////summary///paramname=sqlSQL语句/param///returns影响的记录数/returnspublicintExecuteSql(stringsql,paramsOracleParameter[]cmdParms){OracleCommandcmd=newOracleCommand();{try{PrepareCommand(cmd,con,null,sql,cmdParms);introws=cmd.ExecuteNonQuery();cmd.Parameters.Clear();returnrows;}catch(System.Data.OracleClient.OracleExceptione){throwe;}}}#endregion#region执行带参数的SQL语句///summary///执行不带参数的SQL语句////summary///paramname=sqlSQL语句/parampublicvoidExecuteSql(stringsql){OracleCommandcmd=newOracleCommand(sql,con);try{Open();cmd.ExecuteNonQuery();Close();}catch(System.Data.OracleClient.OracleExceptione){Close();throwe;}}#endregion#region执行SQL语句，返回数据到DataSet中///summary///执行SQL语句，返回数据到DataSet中////summary///paramname=sqlsql语句/param///returns返回DataSet/returnspublicDataSetGetDataSet(stringsql){DataSetds=newDataSet();try{Open();//打开数据连接OracleDataAdapteradapter=newOracleDataAdapter(sql,con);adapter.Fill(ds);}catch//(Exceptionex){}finally{Close();//关闭数据库连接}returnds;}#endregion#region执行SQL语句，返回数据到自定义DataSet中///summary///执行SQL语句，返回数据到DataSet中////summary///paramname=sqlsql语句/param///paramname=DataSetName自定义返回的DataSet表名/param///returns返回DataSet/returnspublicDataSetGetDataSet(stringsql,stringDataSetName){DataSetds=newDataSet();Open();//打开数据连接OracleDataAdapteradapter=newOracleDataAdapter(sql,con);adapter.Fill(ds,DataSetName);Close();//关闭数据库连接returnds;}#endregion#region执行Sql语句,返回带分页功能的自定义dataset///summary///执行Sql语句,返回带分页功能的自定义dataset////summary///paramname=sqlSql语句/param///paramname=PageSize每页显示记录数/param///paramname=CurrPageIndex当前页/param///paramname=DataSetName返回dataset表名/param///returns返回DataSet/returnspublicDataSetGetDataSet(stringsql,intPageSize,intCurrPageIndex,stringDataSetName){DataSetds=newDataSet();Open();//打开数据连接OracleDataAdapteradapter=newOracleDataAdapter(sql,con);adapter.Fill(ds,PageSize*(CurrPageIndex-1),PageSize,DataSetName);Close();//关闭数据库连接returnds;}#endregion#region执行SQL语句，返回记录总数///summary///执行SQL语句，返回记录总数////summary///paramname=sqlsql语句/param///returns返回记录总条数/returnspublicintGetRecordCount(stringsql){intrecordCount=0;Open();//打开数据连接OracleCommandcommand=newOracleCommand(sql,con);OracleDataReaderdataReader=command.ExecuteReader();while(dataReader.Read()){recordCount++;}dataReader.Close();Close();//关闭数据库连接returnrecordCount;}#endregion#region统计某表记录总数///summary///统计某表记录总数////summary///paramname=KeyField主键/索引键/param///paramname=TableName数据库.用户名.表名/param///paramname=Condition查询条件/param///returns返回记录总数/returnspublicintGetRecordCount(stringkeyField,stringtableName,stringcondition){intRecordCount=0;stringsql=selectcount(+keyField+)ascountfrom+tableName++condition;DataSetds=GetDataSet(sql);if(ds.Tables[0].Rows.Count0){RecordCount=Convert.ToInt32(ds.Tables[0].Rows[0][0]);}ds.Clear();ds.Dispose();returnRecordCount;}///summary///统计某表记录总数////summary///paramname=Field可重复的字段/param///paramname=tableName数据库.用户名.表名/param///paramname=condition查询条件/param///paramname=flag字段是否主键/param///returns返回记录总数/returnspublicintGetRecordCount(stringField,stringtableName,stringcondition,boolflag){intRecordCount=0;if(flag){RecordCount=GetRecordCount(Field,tableName,condition);}else{stringsql=selectcount(distinct(+Field+))ascountfrom+tableName++condition;DataSetds=GetDataSet(sql);if(ds.Tables[0].Rows.Count0){RecordCount=Convert.ToInt32(ds.Tables[0].Rows[0][0]);}ds.Clear();ds.Dispose();}returnRecordCount;}#endregion#region统计某表分页总数///summary///统计某表分页总数////summary///paramname=keyField主键/索引键/param///paramname=tableName表名/param///paramname=condition查询条件/param///paramname=pageSize页宽/param///paramname=RecordCount记录总数/param///returns返回分页总数/returnspublicintGetPageCount(stringkeyField,stringtableName,stringcondition,intpageSize,intRecordCount){intPageCount=0;PageCount=(R