Try our conversational search powered by Generative AI!

Gonzalo Gonzalez
Mar 15, 2010
  4814
(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
The A/A Test: What You Need to Know

Sure, we all know what an A/B test can do. But what is an A/A test? How is it different? With an A/B test, we know that we can take a webpage (our...

Lindsey Rogers | Apr 15, 2024

.Net Core Timezone ID's Windows vs Linux

Hey all, First post here and I would like to talk about Timezone ID's and How Windows and Linux systems use different IDs. We currently run a .NET...

sheider | Apr 15, 2024

What's new in Language Manager 5.3.0

In Language Manager (LM) version 5.2.0, we added an option in appsettings.json called TranslateOrCopyContentAreaChildrenBlockForTypes . It does...

Quoc Anh Nguyen | Apr 15, 2024

Optimizely Search & Navigation: Boosting in Unified Search

In the Optimizely Search & Navigation admin view, administrators can set a certain weight of different properties (title, content, summary, or...

Tung Tran | Apr 15, 2024

Optimizely CMS – Getting all content of a specific property with a simple SQL script

When you need to retrieve all content of a specific property from a Page/Block type, normally you will use the IContentLoader or IContentRepository...

Tung Tran | Apr 15, 2024

Join the Content Recommendations Work Smarter webinar May 8th 16.00-16.45 CET with expert Aidan Swain

Learn more about Content Recommendations, with Optimizely’s very own Senior Solutions consultant, Aidan Swain . He will discuss best practices and...

Karen McDougall | Apr 12, 2024