OracleDataAdapter.Fill not mapping Oracle data types to .NET data types

  • Thread starter Thread starter news.microsoft.com
  • Start date Start date
N

news.microsoft.com

I read somewhere (I don't remember where, and can't find it again) that when
you fill a DataTable via the System.Data.OracleClient.OracleDataAdapter, the
data type of each column in the DataTable is automatically set to a .NET
equivalent of the Oracle data type. It doesn't seem to work for me with the
code below. All of my columns are of type System.String, even when the
Oracle data type is Number. Can anyone tell me how I get the DataTable
columns to assume the (equivalent) data type from the Oracle?
-----------
Imports System.Data.OracleClient

Dim dt As DataTable = New DataTable
Dim SelectStatement as String = "SELECT DISTINCT INITCAP(" & FieldName & ")
" & FieldName & " FROM " & TableName
Dim da As OracleDataAdapter = New OracleDataAdapter(SelectStatement,
DBConnection)
da.Fill(dt)
For i As Int32 = 0 To dt.Columns.Count - 1
MyWriteDebug("DataType: " & dt.Columns(i).DataType.FullName)
Next
 
I thought about it, but I would like to keep the code as generic as
possible, so it will adapt to whatever the underlying data might be.

A little context:

I am making an ASP query builder interface. I have a table in Oracle,
T_UIFIELDS, that contains a row for each field from which the user can
specify search criteria. For example, there are rows for SCIENTIFIC NAME and
DAY, which are both fields in the table that users will be querying. A row
could represent a field of (Oracle) data type NUMBER or VARCHAR2. In
T_UIFIELDS, there is a column that contains a SQL SELECT statement to get
the distinct values for that field, e.g. SELECT DISTINCT day FROM
t_observations. I have a business object UIField (that represents a row in
T_UIFIELDS), with a public function GetListDataSource. GetListDataSource
returns a DataTable populated with the results of the SELECT DISTINCT
statement for that object.

I would like the DataTable returned by GetListDataSource to have the
appropriate column data type, so I can perform numeric operations on numeric
data. If OracleDataAdapter.Fill doesn't automatically set the data type of
the DataTable columns, is there a way to ask Oracle what the data type of a
column is?

Thanks for the assistance,
Joe


Miha Markic said:
Did you consider creating dataset at design time?

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

news.microsoft.com said:
I read somewhere (I don't remember where, and can't find it again) that
when you fill a DataTable via the
System.Data.OracleClient.OracleDataAdapter, the data type of each column
in the DataTable is automatically set to a .NET equivalent of the Oracle
data type. It doesn't seem to work for me with the code below. All of my
columns are of type System.String, even when the Oracle data type is
Number. Can anyone tell me how I get the DataTable columns to assume the
(equivalent) data type from the Oracle?
-----------
Imports System.Data.OracleClient

Dim dt As DataTable = New DataTable
Dim SelectStatement as String = "SELECT DISTINCT INITCAP(" & FieldName &
") " & FieldName & " FROM " & TableName
Dim da As OracleDataAdapter = New OracleDataAdapter(SelectStatement,
DBConnection)
da.Fill(dt)
For i As Int32 = 0 To dt.Columns.Count - 1
MyWriteDebug("DataType: " & dt.Columns(i).DataType.FullName)
Next
Click to expand...
Click to expand...
 
I found my problem:

"SELECT DISTINCT INITCAP(fieldname)" returns a string, even when the Oracle
data type is NUMBER. If I remove the INITCAP function, the DataTable column
is given the correct data type.

Now my question is: how do I ask Oracle what data type a column is, so I can
avoid using the INITCAP function on numeric types?

Thanks,
Joe
 
Joe,

Joe Hodsdon said:
I found my problem:

"SELECT DISTINCT INITCAP(fieldname)" returns a string, even when the
Oracle data type is NUMBER. If I remove the INITCAP function, the
DataTable column is given the correct data type.

Now my question is: how do I ask Oracle what data type a column is, so I
can avoid using the INITCAP function on numeric types?
Click to expand...

Why are you using INITCAP in first place? It certainly returns a string as
its output is string.
Blindly using INITCAP on every field is a bit strange even if fields are
some sort of strings.
 
Hi Miha,

I'm using INITCAP because the resulting DataTable is only used as the bound
datasource of a ListBox. Users will choose filter criteria from from that
listbox, so I want it to be presentable.

I'm still learning (in fact, I'm a student), so I welcome any advice or
alternatives.

Thanks again,
Joe
 
If you are using INITCAP then you should know on which database field...
You can't blankly use it everywhere.
 
So that brings me back to my previous question: is there a way to get the
data type of a column from Oracle, so I can test for a VARCHAR2 type before
using INITCAP?

Joe

Miha Markic said:
If you are using INITCAP then you should know on which database field...
You can't blankly use it everywhere.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Joe Hodsdon said:
Hi Miha,

I'm using INITCAP because the resulting DataTable is only used as the
bound datasource of a ListBox. Users will choose filter criteria from
from that listbox, so I want it to be presentable.

I'm still learning (in fact, I'm a student), so I welcome any advice or
alternatives.

Thanks again,
Joe
Click to expand...
Click to expand...
 
Hi Joe,

Joe Hodsdon said:
So that brings me back to my previous question: is there a way to get the
data type of a column from Oracle, so I can test for a VARCHAR2 type
before using INITCAP?
Click to expand...

Well, not exactly - you shouldn't initcap all strings IMO. You should do
that only on fields you want to. Why would you want to capitalize all
fields?
Anyway, you could extract the schema using
OleDbConnection.GetOleDbSchemaTable or using some Oracle specific sql
statement.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
Joe

Miha Markic said:
If you are using INITCAP then you should know on which database field...
You can't blankly use it everywhere.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

Joe Hodsdon said:
Hi Miha,

I'm using INITCAP because the resulting DataTable is only used as the
bound datasource of a ListBox. Users will choose filter criteria from
from that listbox, so I want it to be presentable.

I'm still learning (in fact, I'm a student), so I welcome any advice or
alternatives.

Thanks again,
Joe

"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Joe,

message I found my problem:

"SELECT DISTINCT INITCAP(fieldname)" returns a string, even when the
Oracle data type is NUMBER. If I remove the INITCAP function, the
DataTable column is given the correct data type.

Now my question is: how do I ask Oracle what data type a column is, so
I can avoid using the INITCAP function on numeric types?

Why are you using INITCAP in first place? It certainly returns a string
as its output is string.
Blindly using INITCAP on every field is a bit strange even if fields
are some sort of strings.
Click to expand...
Click to expand...
Click to expand...
 
Thanks, I'll look into GetOleDbSchemaTable.

Joe

Miha Markic said:
Hi Joe,

Joe Hodsdon said:
So that brings me back to my previous question: is there a way to get the
data type of a column from Oracle, so I can test for a VARCHAR2 type
before using INITCAP?
Click to expand...

Well, not exactly - you shouldn't initcap all strings IMO. You should do
that only on fields you want to. Why would you want to capitalize all
fields?
Anyway, you could extract the schema using
OleDbConnection.GetOleDbSchemaTable or using some Oracle specific sql
statement.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/
Joe

Miha Markic said:
If you are using INITCAP then you should know on which database field...
You can't blankly use it everywhere.

--
Miha Markic [MVP C#]
RightHand .NET consulting & development www.rthand.com
Blog: http://cs.rthand.com/blogs/blog_with_righthand/

message Hi Miha,

I'm using INITCAP because the resulting DataTable is only used as the
bound datasource of a ListBox. Users will choose filter criteria from
from that listbox, so I want it to be presentable.

I'm still learning (in fact, I'm a student), so I welcome any advice or
alternatives.

Thanks again,
Joe

"Miha Markic [MVP C#]" <miha at rthand com> wrote in message
Joe,

message I found my problem:

"SELECT DISTINCT INITCAP(fieldname)" returns a string, even when the
Oracle data type is NUMBER. If I remove the INITCAP function, the
DataTable column is given the correct data type.

Now my question is: how do I ask Oracle what data type a column is,
so I can avoid using the INITCAP function on numeric types?

Why are you using INITCAP in first place? It certainly returns a
string as its output is string.
Blindly using INITCAP on every field is a bit strange even if fields
are some sort of strings.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top