Oracle Data Provider for .NET 的使用经验

// Create the command
// sql文
OracleCommand cmdUpdate = new OracleCommand("", con);

// sql文
OracleCommand cmdDelete = new OracleCommand("", con);

// sql文
OracleCommand cmdInsert = new OracleCommand("", con);

//select statement
string strUpdateSql = "UPDATE TBL_STUDENT SET STU_NAME = :I_STU_NAME  WHERE  STU_ID = :I_STU_ID ";
string strDeleteSql = "DELETE FROM TBL_STUDENT  WHERE  STU_ID = :I_STU_ID";
string strInsertSql = "INSERT TBL_STUDENT VALUES (:I_STU_ID, :I_STU_NAME, :I_AGE,:I_BIRTHDAY, :I_SEX )";

//command和检索sql文 关联
cmdUpdate.CommandText = strUpdateSql ;
cmdDelete.CommandText = strDeleteSql ;
cmdInsert.CommandText = strInsertSql ;

//sql文中变量通过oracle参数传递
OracleParameter oraParameter;

//当设置参数的时候
//oracle参数值可通过SourceColumn属性与更新数据行的字段绑定
//取得数据值的版本可通过SourceVersion来设定。
//DataRowVersion.Current 取得数据行中修改后的数据值
//DataRowVersion.Original 取得数据行中原始的数据值
//Where条件里面的参数值往往设置为取DataRowVersion.Original版本
//更新或者插入的参数值往往设置为取DataRowVersion.Current版本

//--------------------------------------------------
//更新用的oracle command
oraParameter = new OracleParameter("I_STU_NAME", OracleDbType.Varchar2, 20);
oraParameter.SourceColumn = "STU_NAME";//更新字段值
oraParameter.SourceVersion = DataRowVersion.Current;
cmdUpdate.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("I_STU_ID", OracleDbType.Varchar2, 5);
oraParameter.SourceColumn = "STU_ID";//检索字段值
oraParameter.SourceVersion = DataRowVersion.Original;
cmdUpdate.Parameters.Add(oraParameter);

//--------------------------------------------------
//删除用的oracle command
oraParameter = new OracleParameter("I_STU_ID", OracleDbType.Varchar2, 5);
oraParameter.SourceColumn = "STU_ID";//检索字段值
oraParameter.SourceVersion = DataRowVersion.Original;
cmdDelete.Parameters.Add(oraParameter);

//--------------------------------------------------
//追加用的oracle command
oraParameter = new OracleParameter("I_STU_ID", OracleDbType.Varchar2, 5);
oraParameter.SourceColumn = "STU_ID";
oraParameter.SourceVersion = DataRowVersion.Current;
cmdInsert.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("I_STU_NAME", OracleDbType.Varchar2, 20);
oraParameter.SourceColumn = "STU_NAME";
oraParameter.SourceVersion = DataRowVersion.Current;
cmdInsert.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("I_AGE", OracleDbType.Int32);
oraParameter.SourceColumn = "AGE";
oraParameter.SourceVersion = DataRowVersion.Current;
cmdInsert.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("I_BIRTHDAY", OracleDbType.Date);
oraParameter.SourceColumn = "BIRTHDAY";
oraParameter.SourceVersion = DataRowVersion.Current;
cmdInsert.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("I_SEX", OracleDbType.Varchar2, 2);
oraParameter.SourceColumn = "SEX";
oraParameter.SourceVersion = DataRowVersion.Current;
cmdInsert.Parameters.Add(oraParameter);

using(OracleDataAdapter dataAdapter = new OracleDataAdapter())
{
 dataAdapter.InsertCommand= cmdUpdate;//追加command设置
 dataAdapter.DeleteCommand= cmdDelete;//删除command设置
 dataAdapter.UpdateCommand= cmdInsert;//更新command设置
 nRecCount = dataAdapter.Update(i_Data, "TBL_STUDENT");//数据更新
}

Console.WriteLine("Number of Update rows : {0} ", nRecCount);

// Close and Dispose OracleConnection object
con.Close();
con.Dispose();
}  

//执uc2 行结果
//例如：
//参数i_Data的TBL_STUDENT表中数据为：
//----------------------------------------------------------------------------------------------
//--| STU_ID     | STU_NAME            |  AGE  | BIRTHDAY   | SEX | RowState
//--| 00001      | aaaaaaaaaa          |  18   | 1988/05/10 |  01 | DataRowState.Modified
//--| 00002      | bbbbbbbbbb          |  19   | 1987/02/01 |  01 | DataRowState.Modified
//--| 00003      | cccccccccc          |  17   | 1989/07/10 |  00 | DataRowState.Deleted
//--| 00004      | dddddddddd          |  18   | 1988/08/10 |  01 | DataRowState.Modified
//--| 00005      | eeeeeeeeee          |  19   | 1987/09/10 |  00 | DataRowState.Added
//--| 00006      | ffffffffff          |  17   | 1989/01/10 |  01 | DataRowState.Added
//----------------------------------------------------------------------------------------------

在执行dataAdapter.Update(i_Data, "TBL_STUDENT")的数据更新后,update方法内部自动循环表中记录

STU_ID{00001，00002，00004}执行更新操作
STU_ID{00005，00006}       执行插入操作
STU_ID{00003}              执行删除操作
并且相应的sql文中的oracle变量与记录行的相应字段值填入。

1.4 但是在表的字段较多的情况下，sql文会比较冗长，对应的oracle paramter也很多，如果都是通过手动写代码来实装,
    处理方法就显得非常麻烦，代码量也很大。如果我们是对全表做更新的话，就可以改用OracleCommandBuilder类来简单实现。
// C# 例子
using(OracleDataAdapter dataAdapter = new OracleDataAdapter())
{ 
 //dataAdapter的SelectCommand中检索SQL文设定
 dataAdapter.SelectCommand  = new OracleCommand("SELECT * FROM TBL_STUDENT", cnn);
 
 //根据SelectCommand中检索SQL文，OracleCommandBuilder 将自动生成dataAdapter相应的
 //UPDATE, INSERT, DELETE 用SQL文，并且oracle paramter也相应填入。
 OracleCommandBuilder commandBuilder = new OracleCommandBuilder(dataAdapter);
 nRecCount = dataAdapter.Update(i_Data, "TBL_STUDENT");//数据更新 
}

关于OracleCommandBuilder 自动生成的SQL文，我们可以通过上述例子来详细说明：
假如 TBL_STUDENT由STU_ID，STU_NAME，AGE，BIRTHDAY，SEX这几个字段。
那么 自动生成的Update用SQL文为：
--------------------------------------------------
UPDATE TBL_STUDENT SET
     STU_ID = :CURRENT_STU_ID，
     STU_NAME = :CURRENT_STU_NAME，
     AGE = :CURRENT_AGE ，
     BIRTHDAY = :CURRENT_BIRTHDAY,
     SEX = :CURRENT_SEX
WHERE  STU_ID = :ORIGINAL_STU_ID AND
     STU_NAME = :ORIGINAL_STU_NAME AND
     AGE = :ORIGINAL_AGE  AND
     BIRTHDAY = :ORIGINAL_BIRTHDAY AND
     SEX = :ORIGINAL_SEX

//更新字段的参数值的版本为Current.
oraParameter = new OracleParameter("CURRENT_STU_ID", OracleDbType.Varchar2, 5);
oraParameter.SourceColumn = "STU_ID";
oraParameter.SourceVersion = DataRowVersion.Current;
cmdUpdate.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("CURRENT_STU_NAME", OracleDbType.Varchar2, 20);
oraParameter.SourceColumn = "STU_NAME";
oraParameter.SourceVersion = DataRowVersion.Current;
cmdUpdate.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("CURRENT_AGE", OracleDbType.Int32);
oraParameter.SourceColumn = "AGE";
oraParameter.SourceVersion = DataRowVersion.Current;
cmdUpdate.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("CURRENT_BIRTHDAY", OracleDbType.Date);
oraParameter.SourceColumn = "BIRTHDAY";
oraParameter.SourceVersion = DataRowVersion.Current;
cmdUpdate.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("CURRENT_SEX", OracleDbType.Varchar2, 2);
oraParameter.SourceColumn = "SEX";
oraParameter.SourceVersion = DataRowVersion.Current;
cmdUpdate.Parameters.Add(oraParameter);

//检索字段的参数值的版本为Origianl.
oraParameter = new OracleParameter("ORIGINAL_STU_ID", OracleDbType.Varchar2, 5);
oraParameter.SourceColumn = "STU_ID";
oraParameter.SourceVersion = DataRowVersion.Origianl;
cmdUpdate.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("ORIGINAL_STU_NAME", OracleDbType.Varchar2, 20);
oraParameter.SourceColumn = "STU_NAME";
oraParameter.SourceVersion = DataRowVersion.Origianl;
cmdUpdate.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("ORIGINAL_AGE", OracleDbType.Int32);
oraParameter.SourceColumn = "AGE";
oraParameter.SourceVersion = DataRowVersion.Origianl;
cmdUpdate.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("ORIGINAL_BIRTHDAY", OracleDbType.Date);
oraParameter.SourceColumn = "BIRTHDAY";
oraParameter.SourceVersion = DataRowVersion.Origianl;
cmdUpdate.Parameters.Add(oraParameter);

oraParameter = new OracleParameter("ORIGINAL_SEX", OracleDbType.Varchar2, 2);
oraParameter.SourceColumn = "SEX";
oraParameter.SourceVersion = DataRowVersion.Origianl;
cmdUpdate.Parameters.Add(oraParameter);

--------------------------------------------------
依次类推，delete 和 insert的sql文也可以同样原理自动生成。

1.5 另外，使用OracleDataAdapter时，不要误以为只能对update的参数表做处理，
其实工作原理是循环参数表中的记录,并且设置相应的oracle paramter.执行相应的sql文。
所以，有时候可以通过设置sql文做不同的操作，下面例子是往表TBL_CLASS_STU中插入相应记录。
记录的STU_ID，STU_NAME 取自参数TBL_STUDENT的字段。
CLASS_ID为固定值‘001‘ ，CLASS_NAME通过检索TBL_CLASS 来取得。

// C# 例子
using(OracleDataAdapter dataAdapter = new OracleDataAdapter())
{ 
    cmdInsert.CommandText  = 
 " INSERT INTO TBL_CLASS_STU( " +
     " CLASS_ID,       "+
     " CLASS_NAME,    "+
     " STU_ID, "+
     " STU_NAME )"+
    " SELECT     "+
     " ‘001‘ ,     "+
     " CLASS_NAME, "+
     " :I_STU_IDO,"+
     " :I_STU_NAME "+
    " FROM TBL_CLASS "+
    " WHERE  CLASS_ID = ‘001‘ ";

    OracleParameter oraParameter;
    oraParameter = new OracleParameter("CURRENT_STU_ID", OracleDbType.Varchar2, 5);
    oraParameter.SourceColumn = "STU_ID";
    oraParameter.SourceVersion = DataRowVersion.Current;
    cmdInsert.Parameters.Add(oraParameter);
   
    oraParameter = new OracleParameter("CURRENT_STU_NAME", OracleDbType.Varchar2, 20);
    oraParameter.SourceColumn = "STU_NAME";
    oraParameter.SourceVersion = DataRowVersion.Current;
    cmdInsert.Parameters.Add(oraParameter);
   
    dataAdapter.InsertCommand= cmdUpdate;//追加command设置
    nRecCount = dataAdapter.Update(i_Data, "TBL_STUDENT");//数据更新 
} 

前面用OracleDataAdapter访问DB，需要DataSet参数做对应操作。
但是在实行固定sql文或者存储过程的时候，我们可以采用OracleCommand 的ExecuteNonQuery()方法。

2.1 执行sql文的时候，很简单，只要设置OracleCommand 的CommandText。执行即可。
C#例
cmd.CommandText = "update emp set sal = sal + .01 where empno=7934";
int rowsUpdated = cmd.ExecuteNonQuery();

2.2 这里主要想归纳一下通过OracleCommand调用存储过程的方法。
首先设置CommandText属性为存储过程的名称。注意带package名
其次将CommandType 属性设为System.Data.CommandType.StoredProcedure
然后填入相应的参数。参数可以为oracle的各种类型。

通过下面例子说明如何调用oracle的数组参数和cursor参数。

//PL/SQL
//--------------------------------------------------------
//Oracle服务器上 存储过程
//包含三个参数。
//I_ID 输入参数  VARCHAR2 类型
//I_TBL 输入参数 VARCHAR(10) 型的数组
//O_CUR 输出参数 cursor 类型
CREATE OR REPLACE PACKAGE JK_WAIN_0203.PKG_TEST IS
 TYPE TYPE_TAB_111 IS TABLE OF VARCHAR(10) INDEX BY BINARY_INTEGER;
 PROCEDURE PROC_GET_DATA(
        I_ID IN VARCHAR2, //
        I_TBL IN TYPE_TAB_111,
        O_CUR  OUT SYS_REFCURSOR);
END PKG_TEST;
/

CREATE OR REPLACE PACKAGE BODY JK_WAIN_0203.PKG_TEST IS
 PROCEDURE PROC_GET_DATA(
          I_ID IN VARCHAR2,
          I_TBL IN TYPE_TAB_111,
         O_CUR  OUT SYS_REFCURSOR)
 IS
         V_INDEX NUMBER := 0;
         V_INDEX_MAX NUMBER := 0;
           V_DATA  VARCHAR(10);
 BEGIN
           V_INDEX := I_TBL.FIRST;
           V_INDEX_MAX := I_TBL.LAST;

           WHILE V_INDEX <= V_INDEX_MAX LOOP 
                     V_DATA := I_TBL(V_INDEX); 
                     V_INDEX := V_INDEX + 1;
           END LOOP;

           OPEN O_CUR FOR SELECT * FROM TAB WHERE ROWNUM < 10;
 END PROC_GET_DATA;
END PKG_TEST;
/
---------------------------------------------------------------------------
//C#例
public void Get_PrecedureData()
{
 OracleConnection connection = null;//Connection
 OracleCommand oraCommand = new OracleCommand();
 OracleParameter paramId = null; 
 OracleParameter paramTbl = null;
 OracleParameter paramCur = null;
 OracleRefCursor pInfoCur = null;
 DataSet dtRtn = new DataSet();
 
 //数据库连接
 connection = new OracleConnection("User Id=scott;Password=tiger;Data Source=oracle");
 connection.Open();
 oraCommand.Connection = connection;
 oraCommand.Parameters.Clear();

 // 存储过程 设定
 oraCommand.CommandText = "PKG_TEST.PROC_GET_DATA";
 oraCommand.CommandType = System.Data.CommandType.StoredProcedure;

 // 输入输出参数设定
 // Varchar2型
 paramId = oraCommand.Parameters.Add( "I_ID", OracleDbType.Varchar2, ParameterDirection.Input );

 // 数组类型参数设定
 paramTbl = oraCommand.Parameters.Add( "I_TBL", OracleDbType.Varchar2, ParameterDirection.Input );
      // 将CollectionType 设为 PLSQLAssociativeArray
 paramTbl.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

 //cursor 类型参数设定
 paramCur = oraCommand.Parameters.Add("O_CUR", OracleDbType.RefCursor, ParameterDirection.Output );

 //设置参数数组的大小
 //注意：参数的Value 属性和ArrayBindSize 属性必须为元素个数相同的数组，且个数等于参数的Size 属性
 int[] bindSize = new int[10];
 string[] tblData = new string[10]; 
 for(int ii = 0; ii < 10; ii++)
 {
  tblData[ii] = ii.ToString();//数值
  bindSize[ii] = 10;//数值的大小
 }
 //数组参数设定
 paramTbl.Value = tblData; 
 paramTbl.Size = 10;   
 paramTbl.ArrayBindSize = bindSize;

 //存储过程执行
 oraCommand.ExecuteNonQuery();

 //out的cursor值的读取
 using(OracleDataAdapter da = new OracleDataAdapter())
 {
  if(paramCur.Value != System.DBNull.Value)
  {
   pInfoCur = (OracleRefCursor)paramCur.Value;
   da.Fill(dtRtn, pInfoCur);
  }
  //取得记录数
  Console.WriteLine("the number of record count is {0}", dtRtn.Tables[0].Rows.Count);
 }

 oraCommand.Dispose();
 connection.Close();
 connection.Dispose();
}