Making Best Out Of Oracle Databases Using ODP.net - Part I

原创 2004年09月05日 06:02:00
 
Great Dilemma – Vendor Specific or Portable Code

The issue of using generic oledb.net, odbc.net layers vs. vendor specific providers has always been a debate during the development of data access layers. While the generic code will avoid vendor lockin and improve portability it suffers in terms of performance and in using the advanced features of the particular database. Oracle continues to be a dominant force in the database market. Oracle has released its latest version of .net provider in the form of ODP.net version 10g. ODP.net provides a flexible, faster, optimized and more stable access to Oracle databases from .net. This series tries to cover the specific features of oracle database as used by ODP.net.

Implementation & Usage of ODP.net

The ODP.net can be installed as part of oracle server installations as well as stand alone client installations. You need the oracle client 9.2 or later to install ODP.net on top of it. The examples mentioned in this document were tested using ODP.net 10.1.0.2.0 which comes as part of Oracle 10g server installation.

The examples are written using c#. To compile a c# application to use ODP.net uses the following command line option. As you know these could be built inside visual studio also.

csc %1.cs /r:C:/oracle/product/10.1.0/DO.NET/bin/Oracle.DataAccess.dll

Substitute C:/oracle/product/10.1.0/DOTNET with an appropriate oracle home for your installation.

The following namespaces needs to be added to your application.

using Oracle.DataAccess.Client; Contains ODP.net classes and enums
using Oracle.DataAccess.Types; Contains ODP.net types

To connect using ODP.net.

OracleConnection con = new OracleConnection();
con.ConnectionString= "User Id=scott;Password=tiger;Data Source=dotnet;";

Now let us concentrate on the individual case studies.

Generating XML from Relational Tables:

Generating XML out of relational data nowadays has been a routine task in database applications for different cases. 2000 has been supporting it in the form FOR XML queries. Oracle has been supporting this feature with different PL/SQL functions. The following implementation of ODP.net achieves XML output of relational data using XmlCommandType property of Oracle Command object. Now look at the code piece.

OracleCommand cmd = new OracleCommand("", con);
cmd.CommandText = "SELECT * FROM emp";
cmd.XmlCommandType = OracleXmlCommandType.Query;
XmlReader dr = cmd.ExecuteXmlReader();
XmlDocument doc = new XmlDocument();
doc.PreserveWhitespace = true;
doc.Load(dr);
System.Console.WriteLine(doc.OuterXml);

The output generated will be as follows:

<?xml version="1.0"?>
<ROWSET>
<ROW>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>17-DEC-80</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</ROW>
<ROW>
..
..
</ROW>
</ROWSET>

Now let us play around a bit to change the default structure of the xml document using the XmlQueryProperties.

cmd.XmlQueryProperties.RootTag = "EMPLOYEE";
cmd.XmlQueryProperties.RowTag = "EMP";

The output will be transformed into:

<EMPLOYEE>
<EMP>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<JOB>CLERK</JOB>
<MGR>7902</MGR>
<HIREDATE>17-DEC-80</HIREDATE>
<SAL>800</SAL>
<DEPTNO>20</DEPTNO>
</EMP>
<EMP>

DML (Data Manipulation) using XML.

Now let us perform an insert into the emp table using a XML document as source.

Predictably XmlSaveProperties is used to set the properties of the save command.

OracleCommand cmd = new OracleCommand("", con);
cmd.XmlCommandType = OracleXmlCommandType.Insert;


cmd.CommandText = "<?xml version=/"1.0/"?>/n" +
"<EMPLOYEE>/n"+
"<EMPROW>/n" +
"<EMPNO>9999</EMPNO>/n"+
"<ENAME>IAN THROPE</ENAME>/n"+
"<JOB>MANAGER</JOB>/n"+
"<MGR>7902</MGR>/n"+
"<SAL>800</SAL>/n"+
"<DEPTNO>20</DEPTNO>/n"+
"</EMPROW>/n"+
"</EMPLOYEE>/n";

String[] keycols = new String[1];
keycols[0] = "EMPNO";
String[] updcols = new String[6];
updcols[0]="EMPNO";
updcols[1]="ENAME";
updcols[2]="JOB";
updcols[3]="MGR";
updcols[4]="SAL";
updcols[5]="DEPTNO";


cmd.XmlSaveProperties.RowTag = "EMPROW";
cmd.XmlSaveProperties.Table = "EMP";
cmd.XmlSaveProperties.UpdateColumnsList = updcols;
cmd.XmlSaveProperties.KeyColumnsList = keycols;
cmd.XmlSaveProperties.Xslt = null;
cmd.XmlSaveProperties.XsltParams = null;

int rows = 0;
rows = cmd.ExecuteNonQuery();
Console.WriteLine("Rows Inserted " + rows);

The above code piece sets an XMLDocument as a command type and sets the key, update columns and the table to be updated. Execution of the command will result in an insert of a new row to the emp table. Similar logic can be applied to delete, update using OracleXmlCommandType.Update, OracleXmlCommandType.Delete.

Applying Transformations

While it may look like transforming XML using XSLT looks like a job at the front end some times transforming one XML format to another XML can still be required at the database or middle tier itself. OracleXML extensions provided in ODP.net can facilitate this. Let us look into the following example.

Assuming that your company outsources the calculation of taxes to an external firm and the external firm wants the employee records in a different format where by employee’s SAL and COMM has to be grouped under a new element called CTC. Let us apply the transformation using the following style sheet.

string converttotaxformat="<?xml version=/"1.0/" encoding=/"UTF-8/"?>/n" +
"<TAXINFO xmlns:xsl=/"http://www.w3.org/1999/XSL/Transform/" xsl:version=/"1.0/">/n" +
"<xsl:for-each select=/"EMPLOYEE/EMP/">/n" +
"<TAXREC>/n" +
" <EMPNO><xsl:value-of select=/"EMPNO/"/></EMPNO>/n"+
" <ENAME><xsl:value-of select=/"ENAME/"/></ENAME>/n"+
" <CTC>/n"+
" <SAL><xsl:value-of select=/"SAL/"/></SAL>/n"+
" <COMM><xsl:value-of select=/"COMM/"/></COMM>/n"+
" </CTC>/n"+
" <JOB><xsl:value-of select=/"JOB/"/></JOB>/n"+
" <DEPTNO><xsl:value-of select=/"DEPTNO/"/></DEPTNO>/n"+
"</TAXREC>/n"+
"</xsl:for-each>/n"+
"</TAXINFO>";


OracleCommand cmd = new OracleCommand("", con);
cmd.CommandText = "SELECT * FROM emp";
cmd.XmlCommandType = OracleXmlCommandType.Query;
cmd.XmlQueryProperties.RootTag = "EMPLOYEE";
cmd.XmlQueryProperties.RowTag = "EMP";
cmd.XmlQueryProperties.Xslt = converttotaxformat;
XmlReader dr = cmd.ExecuteXmlReader();

XmlDocument doc = new XmlDocument();
doc.PreserveWhitespace = true;
doc.Load(dr);

System.Console.WriteLine(doc.OuterXml);

The output will look like the following.

<TAXINFO xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xsl:version="1.0">
<TAXREC>
<EMPNO>7369</EMPNO>
<ENAME>SMITH</ENAME>
<CTC>
<SAL>800</SAL>
<COMM>80</COMM>
</CTC>
<JOB>CLERK</JOB>
<DEPTNO>20</DEPTNO>
</TAXREC>
<TAXREC>
<EMPNO>7499</EMPNO>

Conclusion

Oracle provider for .net has many features which could be used while writing vendor specific code. This document explains the XML related features and I am planning to write about other features in the future articles.

使用vivado进行逻辑开发时,进行到Generate Bitstream时报错

使用vivado进行逻辑开发时,进行到Generate Bitstream时报错,如下:   [Drc 23-20] Rule violation (NSTD-1) Unspecified...
  • hemmingway
  • hemmingway
  • 2014年09月29日 14:55
  • 14446

XCode7打包上传报错

在XCode7上传应用时,上传失败遇到两个错误,提示如下: ERROR ITMS-90535: "Unexpected CFBundleExecutable Key. The bundle at 'x...
  • LVXIANGAN
  • LVXIANGAN
  • 2015年11月07日 13:56
  • 8040

Vivado 约束条件出错

 When generating a bitstream, the following error messages occur: ERROR: [Drc 23-20] Rule violat...
  • LovingDuo
  • LovingDuo
  • 2016年07月01日 09:50
  • 2498

csharp: Oracle Stored Procedure DAL using ODP.NET

Oracle sql: --书分类目录kind -- Geovin Du create table BookKindList ( BookKindID INT PRIMARY KEY, ...
  • geovindu
  • geovindu
  • 2016年09月21日 11:26
  • 224

Beginning ASP.NET Databases using C#

  • 2009年02月19日 15:58
  • 11.95MB
  • 下载

oracle odp.net 32位/64位版本的问题

http://www.cnblogs.com/yjmyzz/archive/2011/04/19/2020793.html 如果你的机器上安装了odp.net,且确信machine.co...
  • plean
  • plean
  • 2012年05月11日 14:35
  • 719

使用 ODP.NET 访问 Oracle 数据库

要相使用 Oracle Data Provider For .NET(ODP.NET), 必须先安装 ODP.NET 或者是 ODAC(Oracle Data Access Components...
  • thunder_0927
  • thunder_0927
  • 2013年12月16日 16:25
  • 691

精通 Oracle+Python,第 9 部分:Jython 和 IronPython — 在 Python 中使用 JDBC 和 ODP.NET

作者:Przemyslaw Piotrowski 这一部分将介绍 Python 的两个最常用的开发环境 — Java 和 .NET,以及这些平台的 Python 原生实现。 2011 年 12 月...
  • lively1982
  • lively1982
  • 2016年07月22日 14:51
  • 511

oracle ODP.NET 批量插入或更新

  • 2016年11月30日 16:47
  • 7KB
  • 下载

Pro ODP.NET for Oracle Database 11g

  • 2012年08月30日 15:01
  • 7.44MB
  • 下载
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Making Best Out Of Oracle Databases Using ODP.net - Part I
举报原因:
原因补充:

(最多只允许输入30个字)