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

 

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. SQL Server 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.