Oracle Data Provider for .NET 的使用经验(2 )

1.3  当做更新处理的时候，调用 OracleDataAdapter 的Update方法，对输入参数DataTable中每行进行循环，
    根据当前行的状态调用相应的 INSERT、UPDATE 或 DELETE 语句。

RowState属性相关处理的OracleCommand
DataRowState.Added 该行已添加  :InsertCommand
DataRowState.Deleted 该行已删除 :DeleteCommand
DataRowState.Modified 该行已被修改 :UpdateCommand
// C# 例子
using System;
using System.Data;
using System.Xml;
using Oracle.DataAccess.Client;
class testSample
{
public void updateStuData(DataSet i_Data)
{
     //数据库连接打开
int nRecCount = 0;
OracleConnection con = new OracleConnection();
con.ConnectionString = "User Id=scott;Password=tiger;Data Source=oracle;";
con.Open();
Console.WriteLine("Connected Successfully");

// 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");//数据更新 
}