开发者社区> 杰克.陈> 正文

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

 


版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
MySql的配置——详细教程
免安装版的Mysql   MySQL关是一种关系数据库管理系统,所使用的 SQL 语言是用于访问数据库的最常用的 标准化语言,其特点为体积小、速度快、总体拥有成本低,尤其是开放源码这一特点,在 Web 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System:关系数据 库管理系统)应用软件之一。   在本博文里,我主要以MySql免安装版为例,帮助大家解决配置mysql。
5 0
文末送书|数据分析必知必会之维度&指标
在业务场景中,维度和指标是基础,清晰准确地定义维度和指标能帮助我们更好地探寻数字背后的含义。下图列举了一些电商常用的指标和维度,你能正确区分吗?
5 0
SpringBoot系列:MybatisPlus的入门级使用教程(上)
今天是大年初二,xdm都去哪里玩了?我在家宅了一天,没地方去的那就继续一起学习,内卷起来!
4 0
spring security技术分享(四)
用户认证就是判断一个用户的身份是否合法的过程,用户去访问系统资源时系统要求验证用户的身份信息,身份合法方可继续访问,不合法则拒绝访问。 认证是确认某主体在某系统中是否合法、可用的过程。这里的主体既可以是登录系统的用户,也可以是接入的设备或者其他系统。
5 0
牛逼!一文看懂 Java 并发编程在各主流框架中的应用
Spring、Netty、Mybatis 等框架的代码中大量运用了 Java 多线程编程技巧。并发编程处理的恰当与否,将直接影响架构的性能。 本文通过对这些框架源码的分析,结合并发编程的常用技巧,来讲解多线程编程在这些主流框架中的应用。
4 0
SpringBoot系列:MybatisPlus的入门级使用教程(下)
距离上一篇文章已经过去一个多月了,最近忙着项目上线,连轴转了一个多月,头发都少了不少!今天分享的是MP入门使用教程的最后的部分,也是使用的比较多的地方,就是如何使用MP进行条件查询!
4 0
SpringBoot系列:整合Spring Security
Spring 是一个非常流行和成功的 Java 应用开发框架。Spring Security 基于 Spring 框架,提供了一套 Web 应用安全性的完整解决方案。一般来说,Web 应用的安全性包括用户认证(Authentication*)和用户授权(Authorization) 两个部分。
5 0
iOS-底层原理36:内存优化(一) 野指针探测
iOS-底层原理36:内存优化(一) 野指针探测
4 0
深挖 Mybatis 源码:缓存模块
MyBatis 中的缓存分为一级缓存、二级缓存,但在本质上是相同的,它们使用的都是 Cache 接口的实现。在这篇文章里,我们就来分析 Cache 接口以及多个实现类的具体实现。
3 0
Android Glide 的简单使用(一)(上)
前言 Gilde简介 Android SDK 要求 所需权限 网络加载 本地存储 性能 Glide基本用法 简单使用
1 0
+关注
杰克.陈
一个安静的程序猿~
10427
文章
2
问答
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载