Try our conversational search powered by Generative AI!

Gonzalo Gonzalez
Mar 15, 2010
  4854
(0 votes)

EPiServer CMS6 on Oracle moving on to ODP.NET

On 15th June 2009, Microsoft annouced their decision to deprecate their System.Data.OracleClient assembly. As an EPiServer developer working with Oracle, you’ll probably know that EPiServer CMS 5 uses this assembly. We decided to change direction in EPiServer CMS 6 and use the ODP.NET ADO.NET provider from the Oracle Corporation.

In recent years, ODP.NET has added lots of new features,  such as performance tuning, user-defined types, advanced queuing, RAC connection pooling, and supporting multiple ODP.NET client versions simultaneously on the same machine.

Time to move on!

Below you can find some “ugly” peaces of code done with the previous data provider, due to differences between .NET CLR Types and Oracle data types:

E.g. Creation of a Clob parameter:

try
{
// WARNING! This is the ugliest piece of code I have ever written...
// not for the faint-of-heart.
// Believe it or not, but this is the way to handle large data
// fields in Oracle :-(
cmd.CommandText = "declare xx clob;
begin dbms_lob.createtemporary(xx, false, 0);
:tempblob := xx; end;"
;
cmd.Parameters.Add(new OracleParameter("tempblob", OracleType.Clob)).Direction =
ParameterDirection.Output;
cmd.ExecuteNonQuery();
OracleLob tempLob = (OracleLob)cmd.Parameters[0].Value;


System.Text.UnicodeEncoding Unicode = new System.Text.UnicodeEncoding();
byte[] tempbuff = Unicode.GetBytes(paramValue);
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
tempLob.Write(tempbuff, 0, tempbuff.Length);
tempLob.EndBatch();
param =
new OracleParameter(ProviderSpecificParameterName(parameterName), OracleType.Clob);
((OracleParameter)param).Value = tempLob;
if (localTransaction)
{
cmd.Transaction.Commit();
}
}

 
 E.g Creation of a Blob Parameter:
 


if (serializedData.Length < 32000)
{
CreateParameter(cmd, "p_ItemData", OracleType.Blob, serializedData);
}
else
{
// WARNING! This is the ugliest piece of code I have ever copied.
OracleCommand tmpCmd = CreateCommand("declare xx blob; begin
dbms_lob.createtemporary(xx, false, 0); :tempblob := xx; end;"
);
tmpCmd.CommandType = CommandType.Text;
tmpCmd.Parameters.Add(new OracleParameter(
"tempblob", OracleType.Blob)).Direction = ParameterDirection.Output;
tmpCmd.ExecuteNonQuery();

OracleLob tempLob = (OracleLob)tmpCmd.Parameters[0].Value;
tempLob.BeginBatch(OracleLobOpenMode.ReadWrite);
tempLob.Write(serializedData, 0, serializedData.Length);
tempLob.EndBatch();

tmpCmd.Parameters.Clear();
tmpCmd.Dispose();

OracleParameter param =
new OracleParameter("p_ItemData", OracleType.Blob);
((OracleParameter)param).Value = tempLob;
cmd.Parameters.Add(param);
}

 
 
 
 
What does it look like now?
 
for Blob:
CreateParameter(cmd, "p_ItemData", OracleDbType.Blob, serializedData);
 
Internally CreateParameter create  an Oracle Parameter associated to the command(cmd):
 
private static void CreateParameter(OracleCommand command,
String parameterName,
OracleDbType parameterType,
Object parameterValue,
ParameterDirection parameterDirection)
{
OracleParameter param = command.CreateParameter();
param.ParameterName = parameterName;
param.OracleDbType = parameterType;
param.Value = parameterValue;
param.Direction = parameterDirection;

command.Parameters.Add(param);
}

 
for Clob:
param = new OracleParameter(ProviderSpecificParameterName(parameterName),
OracleDbType.Clob);

Much better!!!
 
There are some misconceptions to clarify:      
Misconception: "I have to download a particular version of ODP.NET depending on the version of my database."
Fact: Any version of ODP.NET works with any version of Oracle Database.
Misconception: "EPiServer is compiled against ODAC 11.1.0.6.21( Oracle.DataAccess 2.111.6.0.)  I'm in big trouble!"
Fact: Multiple versions of ODP.NET(Oracle Clients) can live on the same box and the application can target whatever specific one you need. You can of course also use assembly redirects.
Misconception: "This means I have to pay for an Oracle provider now!"
Fact: ODP.NET is free!
You’ll find more information in the ODP.NET for Microsoft  OracleClient Developers webpage.
Happy coding:)
 

 

Mar 15, 2010

Comments

Please login to comment.
Latest blogs
From Procrastination to Proficiency: Navigating Your Journey to Web Experimentation Certification

Hey there, Optimizely enthusiasts!   Join me in celebrating a milestone – I'm officially a certified web experimentation expert! It's an exhilarati...

Silvio Pacitto | May 17, 2024

GPT-4o Now Available for Optimizely via the AI-Assistant plugin!

I am excited to announce that GPT-4o is now available for Optimizely users through the Epicweb AI-Assistant integration. This means you can leverag...

Luc Gosso (MVP) | May 17, 2024 | Syndicated blog

The downside of being too fast

Today when I was tracking down some changes, I came across this commit comment Who wrote this? Me, almost 5 years ago. I did have a chuckle in my...

Quan Mai | May 17, 2024 | Syndicated blog

Optimizely Forms: Safeguarding Your Data

With the rise of cyber threats and privacy concerns, safeguarding sensitive information has become a top priority for businesses across all...

K Khan | May 16, 2024