RE: Migration in .NET to support Oracle-databases

#1

Hi Wim,

I have posted a reply in another thread in
microsoft.public.dotnet.framework.adonet.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

--------------------

Quote:

Content-Class: urn:content-classes:message
From: "Wim Verbeke" <wim.verbeke (AT) ormvision (DOT) com
Sender: "Wim Verbeke" <wim.verbeke (AT) ormvision (DOT) com
Subject: Migration in .NET to support Oracle-databases
Date: Mon, 20 Oct 2003 08:11:23 -0700
Lines: 110
Message-ID: <000e01c3971c$6c9ad230$a301280a (AT) phx (DOT) gbl
MIME-Version: 1.0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
Thread-Index: AcOXHGya/8c7Q4IJTTiMgl/ZTZJHcg==
Newsgroups:
microsoft.public.data.ado,microsoft.public.data.od bc,microsoft.public.sqlser

ver.programming,microsoft.public.dotnet.framework. adonet,microsoft.public.do
tnet.framework.odbcnet

Quote:

Path: cpmsftngxa06.phx.gbl
Xref: cpmsftngxa06.phx.gbl microsoft.public.data.odbc:17608
microsoft.public.sqlserver.programming:397211

microsoft.public.dotnet.framework.adonet:64060
microsoft.public.dotnet.framework.odbcnet:3195
microsoft.public.data.ado:64109

Quote:

NNTP-Posting-Host: TK2MSFTNGXA11 10.40.1.163
X-Tomcat-NG: microsoft.public.dotnet.framework.odbcnet

Dear Oracle.NET guru's,

Hopefully we can rely on your experience to solve our
migration
issues. Thanks for your time!

Our application is currently based on a Sql Server
datasource.
However, we would like to support other datasources as
well.
That's why our DAL-layer is using an IDataProvider
interface
which exposes database functions like:
ExecuteScalar, ExecuteNonQuery, ExecuteDataReader...
These functions are mapped (together with statement-
stuff) to
the implemented client, eg. SqlClient, OracleClient.

At this point, we are developing the Oracle-dataprovider,
based
on .NETs System.Data.OracleClient and the Oracle Client
tools.
The IDataProvider-implementation does at first sight its
job.
Our main concern however is the type-conversion. In the
current
design we've used uniqueidentifiers as primary keys in the
database-design. This is very handy if you work with
disconnected
datasets and distributed services which share the same
datasource.
However, the uniqueidentifier-type is not known in
Oracle. There
is an equivalent which can store the same information as
an
uniqueidentifier, namely RAW(16), but this does not get
converted
on the fly to the System.Guid type in .NET.
Note that we are currently developing _support_ for
Oracle, we are
not migrating to Oracle. This means that we don't want to
adjust
our code above the DAL-layer.

Because of this situation, some problems occur.
For example, we use typed-datasets (generated by AdHoc
Query Tool
from Microsofts ADO.NET Tools).
Suppose we have a DataSet with a column CompanyGuid.
The .NET
native type is System.Guid, the OracleDbType is RAW(16).
When
filling such a dataset from a DataAdapter, we get a
conversion-error:
"Object does not implement IConvertable"
This is simply because the RAW-type cannot be implicitly
converted
to System.Guid. This issue causes a lot of problems,
because we
don't want to change the code above the DAL-layer.

Is there a way to transparently adjust these types? If
this can't
be done, We'll have to choose between some drastic
options:

1. Adjust IDataProvider-implementation for Oracle to
transform
Guid to ByteArray (to datastore) and ByteArray to Guid
(from
datastore). Use RAW(16) DbType in Oracle.
Problem with IDataReader.GetGuid()
Problem with IDataAdapter.Fill()
(convert complete columns of type Guid / ByteArray)

2. Usage of ByteArray/String in datasource and DAL-layer
Adjust current SQL Server database (!),
adjust DAL-components and
adjust the functions which use the (queried) datasets.

Which solution can be seen as "best practices"?

The TimeStamp-field is not such a huge issue, because
this gets
translated as a .NET ByteArray, so if we create a RAW-
oracle type
with the same functionality (generated by datasource),
then this
should work fine. However, it seems that the RAW datatype
is not
so popular anymore and that blob is the alternative. This
is ofcourse
not the type we would use to represent an array of 8
bytes...
Should we change this field as well or should we stick to
the RAW
db type?

I know this is a very lengthy message, but maybe there is
someone,
somewhere on this planet who could make us very happy

Anyway, thanks for reading until this line!

Kind regards,

Wim Verbeke
.NET Developer

wim.verbeke (AT) ormvision (DOT) com

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

RE: Migration in .NET to support Oracle-databases

Dotnet Framework (ODBC.net) microsoft.public.dotnet.framework.odbcnet

RE: Migration in .NET to support Oracle-databases - 10-21-2003 , 06:44 AM