Oracle Data Provider for .NET 的使用经验

简介: 原文:Oracle Data Provider for .NET 的使用经验Oracle Data Provider for .NET 是Oracle提供的基于Ado.net接口的一个开发包。    开发者可以通过它,简单的实现在.net中访问oracle数据库。
原文: Oracle Data Provider for .NET 的使用经验

Oracle Data Provider for .NET 是Oracle提供的基于Ado.net接口的一个开发包。   
开发者可以通过它,简单的实现在.net中访问oracle数据库。    
因为在开发中一直使用该开发包,所以想基于此谈谈使用该开发包的一些经验。   
          
(基于版本:Oracle Data Provider for .NET Release 10.2      
关于与Microsoft .NET Framework 1.1 Data Provider for Oracle 的比较可参考以下网页  
http://msdn.microsoft.com/netframework/default.aspx?pull=/library/en-us/dnadonet/html/odpvsmsdp.asp) 
          
          
          
1. 通过DataAdapter访问数据库        
DataAdapter有四个属性SelectCommand,DeleteCommand,InsertCommand,UpdateCommand  
          
1.1 当做检索处理的时候, 执行SelectCommand的操作,返回数据集。    

// C# 例子
using System;
using System.Data;
using System.Xml;
using Oracle.DataAccess.Client;
class testSample
{
static void Main()
{
     //数据库连接打开
     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 cmd = new OracleCommand("", con);
  
     //select statement
     string strSelectSql = "SELECT STU_ID, STU_NAME, AGE, BIRTHDAY, SEX FROM TBL_STUDENT " +
      " WHERE  SEX = :I_SEX AND  BIRTHDAY >= :I_BIRTHDAY AND AGE = :I_AGE ";

     //command和检索sql文 关联
     cmd.CommandText = strSelectSql;
  
     //sql文中变量通过oracle参数传递
   OracleParameter oraParameter;

   oraParameter = new OracleParameter("I_SEX",OracleDbType.Varchar2, 2);
   oraParameter.Value = "01";
   cmd.Parameters.Add(oraParameter);
//字符型

   oraParameter = new OracleParameter("I_BIRTHDAY",OracleDbType.Date);//日期型
   oraParameter.Value = "1986/01/01";
   cmd.Parameters.Add(oraParameter);

   oraParameter = new OracleParameter("I_AGE",OracleDbType.Int32);
   oraParameter.Value = 20;
   cmd.Parameters.Add(oraParameter);

     DataSet dtTmp = new DataSet();

     using(OracleDataAdapter dataAdapter = new OracleDataAdapter())
     {
      dataAdapter.SelectCommand = cmd;//检索command设置
      dataAdapter.Fill(dtTmp);//检索结果保存在dtTmp数据集中
     }

     //
     Console.WriteLine("Number of rows : {0} ", dtTmp.Tables[0].Rows.Count);
 
     // Close and Dispose OracleConnection object
     con.Close();
     con.Dispose();

}

1.2    如果sql文只是想count(*) 获得数据库中记录件数,可以直接使用OracleCommand.ExecuteScalar()来快速取得。

 // C# 例子

Cmd = new OracleCommand( "SELECT COUNT(*) FROM TBL_STUDENT", Conn );
Object o = Cmd.ExecuteScalar();
int nRecordCount = Convert.ToInt32(o.ToString());

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

前面用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();
}

 


目录
相关文章
|
1月前
|
开发框架 Oracle 关系型数据库
ASP.NET实验室LIS系统源码 Oracle数据库
LIS是HIS的一个组成部分,通过与HIS的无缝连接可以共享HIS中的信息资源,使检验科能与门诊部、住院部、财务科和临床科室等全院各部门之间协同工作。 
48 4
|
8月前
|
关系型数据库 MySQL 数据库
找不到请求的 .Net Framework Data Provider。可能没有安装
做的一个项目,框架为.net framework 数据库为mysql 出现如标题错误 检查是否安装mysql、是否安装mysql connector net 笔者是因为没有安装后者mysql connector net 下载地址: [mysql connector net](https://downloads.mysql.com/archives/c-net/ "mysql connector net") 笔者安装截图如下: ![请在此添加图片描述](https://developer-private-1258344699.cos.ap-guangzhou.myqcloud.com/c
88 0
|
Oracle 机器学习/深度学习 数据库
深入详解Oracle data change notification
深入详解 Oracle data change notification
1510 0
|
1月前
|
数据挖掘 定位技术
.NET Compact Framework下的GPS NMEA data数据分析(二)转
.NET Compact Framework下的GPS NMEA data数据分析(二)转
18 0
|
1月前
|
存储 SQL Oracle
[Oracle]细节与使用经验
如果文中阐述不全或不对的,多多交流。
86 0
[Oracle]细节与使用经验
|
1月前
|
Oracle 关系型数据库 数据库
oracle 重启步骤及踩坑经验
oracle 重启步骤及踩坑经验
152 0
|
1月前
|
Oracle 关系型数据库 数据管理
.NET医院检验系统LIS源码,使用了oracle数据库,保证数据的隔离和安全性
LIS系统实现了实验室人力资源管理、标本管理、日常事务管理、网络管理、检验数据管理(采集、传输、处理、输出、发布)、报表管理过程的自动化,使实验室的操作人员和管理者从繁杂的手工劳作中解放出来,提高了检验人员的工作效率和效益,降低了劳动成本和差错发生率。
|
7月前
|
Oracle 关系型数据库
Oracle 中data与timstamp互转
Oracle 中data与timstamp互转
|
9月前
|
存储 Oracle Java
[亲测可用]hibernate调用Oracle存储过程|Spring Data JPA调用Oracle存储过程方法
[亲测可用]hibernate调用Oracle存储过程|Spring Data JPA调用Oracle存储过程方法
|
SQL Oracle 网络协议
.Net项目中通过ODAC方式链接Oracle数据库相关配置
.Net项目中通过ODAC方式链接Oracle数据库相关配置 一、开发环境 1.关于ODAC的链接方式 ODAC 直接使用 Oracle 调用接口 (OCI). OCI 是一种允许应用程序开发人员使用第三方开发语言存取Oracle数据服务器的过程或函数以及控制所有的SQL语句执行状态的应用程序接口。 2.使用ODAC方式的优点 使用ODAC的Net选项而无需在客户机上安装Oracle客户端来连接到Oracle.在这种情况下,ODAC仅需要TCP/IP协议的支持,从而可以创建真正的、最小的数据库应用程序。
127 0