oracle-C#使用方法

usingSystem;usingSystem.Collections.Generic;usingSystem.Linq;usingSystem.Text;usingSystem.Data;usingSystem.Data.OracleClient;namespaceA0170_Oracle.Sample{///summary///用于读取Oracle数据库的例子.//////注意：这个例子所使用的表和数据，请参考项目下的Schema.sql文件。///////summaryclassReadOracleData{///summary///Oracle的数据库连接字符串.////summaryprivateconstStringconnString=@DataSource=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.210)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCL)));UserId=TEST;Password=TEST123;///summary///用于查询的SQL语句.////summaryprivateconstStringSQL=@SELECTROW_NUMBER()OVER(ORDERBYSUM(SALE_MONEY)DESC)ASNO,SALE_DATE,SUM(SALE_MONEY)ASSUM_MONEYFROMSALE_REPORTGROUPBYSALE_DATEORDERBYSUM(SALE_MONEY)DESC;///summary///将数据读取到DataSet中.////summarypublicvoidReadDataToDataSet(){Console.WriteLine(使用DataAdapter，将数据填充到DataSet中，然后脱离数据库，直接对DataSet进行处理。);//建立数据库连接.OracleConnectionconn=newOracleConnection(connString);//创建一个适配器OracleDataAdapteradapter=newOracleDataAdapter(SQL,conn);//创建DataSet，用于存储数据.DataSettestDataSet=newDataSet();//执行查询，并将数据导入DataSet.adapter.Fill(testDataSet,result_data);//关闭数据库连接.conn.Close();//处理DataSet中的每一行数据.foreach(DataRowtestRowintestDataSet.Tables[result_data].Rows){//将检索出来的数据，输出到屏幕上.Console.WriteLine(NO:{0};Date:{1};Money:{2},testRow[NO],testRow[SALE_DATE],testRow[SUM_MONEY]);}}///summary///通过Reader，依次读取每一条数据.////summarypublicvoidReadDataByReader(){Console.WriteLine(使用DataReader，逐行对查询结果进行处理。[处理过程必须保持数据库连接正常]);//建立数据库连接.OracleConnectionconn=newOracleConnection(connString);//打开连接.conn.Open();//创建一个Command.OracleCommandtestCommand=conn.CreateCommand();//定义需要执行的SQL语句.testCommand.CommandText=SQL;//执行SQL命令，结果存储到Reader中.OracleDataReadertestReader=testCommand.ExecuteReader();//处理检索出来的每一条数据.while(testReader.Read()){//将检索出来的数据，输出到屏幕上.Console.WriteLine(NO:{0};Date:{1};Money:{2},testReader[NO],testReader[SALE_DATE],testReader[SUM_MONEY]);}//关闭Reader.testReader.Close();//关闭数据库连接.conn.Close();}}}usingSystem;usingSystem.Data;usingSystem.Data.OracleClient;usingSystem.Collections;usingSystem.Reflection;namespaceMyOraComm{/**////summary///ConnDbForOracle的摘要说明。////summarypublicclassConnForOracle{protectedOracleConnectionConnection;privatestringconnectionString;publicConnForOracle(){stringconnStr;connStr=System.Configuration.ConfigurationSettings.AppSettings[connStr].ToString();connectionString=connStr;Connection=newOracleConnection(connectionString);}带参数的构造函数#region带参数的构造函数/**////summary///带参数的构造函数////summary///paramname=ConnString数据库联接字符串/parampublicConnForOracle(stringConnString){stringconnStr;connStr=System.Configuration.ConfigurationSettings.AppSettings[ConnString].ToString();Connection=newOracleConnection(connStr);}#endregion打开数据库#region打开数据库/**////summary///打开数据库////summarypublicvoidOpenConn(){if(this.Connection.State!=ConnectionState.Open)this.Connection.Open();}#endregion关闭数据库联接#region关闭数据库联接/**////summary///关闭数据库联接////summarypublicvoidCloseConn(){if(Connection.State==ConnectionState.Open)Connection.Close();}#endregion执行SQL语句，返回数据到DataSet中#region执行SQL语句，返回数据到DataSet中/**////summary///执行SQL语句，返回数据到DataSet中////summary///paramname=sqlsql语句/param///paramname=DataSetName自定义返回的DataSet表名/param///returns返回DataSet/returnspublicDataSetReturnDataSet(stringsql,stringDataSetName){DataSetdataSet=newDataSet();OpenConn();OracleDataAdapterOraDA=newOracleDataAdapter(sql,Connection);OraDA.Fill(dataSet,DataSetName);//CloseConn();returndataSet;}#endregion执行Sql语句,返回带分页功能的dataset#region执行Sql语句,返回带分页功能的dataset/**////summary///执行Sql语句,返回带分页功能的dataset////summary///paramname=sqlSql语句/param///paramname=PageSize每页显示记录数/param///paramname=CurrPageIndex当前页/param///paramname=DataSetName返回dataset表名/param///returns返回DataSet/returnspublicDataSetReturnDataSet(stringsql,intPageSize,intCurrPageIndex,stringDataSetName){DataSetdataSet=newDataSet();OpenConn();OracleDataAdapterOraDA=newOracleDataAdapter(sql,Connection);OraDA.Fill(dataSet,PageSize*(CurrPageIndex-1),PageSize,DataSetName);//CloseConn();returndataSet;}#endregion执行SQL语句，返回DataReader,用之前一定要先.read()打开,然后才能读到数据#region执行SQL语句，返回DataReader,用之前一定要先.read()打开,然后才能读到数据/**////summary///执行SQL语句，返回DataReader,用之前一定要先.read()打开,然后才能读到数据////summary///paramname=sqlsql语句/param///returns返回一个OracleDataReader/returnspublicOracleDataReaderReturnDataReader(Stringsql){OpenConn();OracleCommandcommand=newOracleCommand(sql,Connection);returncommand.ExecuteReader(System.Data.CommandBehavior.CloseConnection);}#endregion执行SQL语句，返回记录总数数#region执行SQL语句，返回记录总数数/**////summary///执行SQL语句，返回记录总数数////summary///paramname=sqlsql语句/param///returns返回记录总条数/returnspublicintGetRecordCount(stringsql){intrecordCount=0;OpenConn();OracleCommandcommand=newOracleCommand(sql,Connection);OracleDataReaderdataReader=command.ExecuteReader();while(dataReader.Read()){recordCount++;}dataReader.Close();//CloseConn();returnrecordCount;}#endregion取当前序列,条件为seq.nextval或seq.currval#region取当前序列,条件为seq.nextval或seq.currval/**////summary///取当前序列////summary///paramname=seqstr/par