How do I use C# and ADO.NET to query an Oracle table with a spatial column of type SDO_GEOMETRY?

Question

My development machine is running Windows 7 Enterprise, 64-bit version. I am using Visual Studio 2010 Release Candidate. I am connecting to an Oracle 11g Enterprise server version 11.1.0.7.0. I had a difficult time locating Oracle client software that is made for 64-bit Windows systems and eventually landed here to download what I assume is the proper client connectivity software. I added a reference to "Oracle.DataAccess" which is version 2.111.6.0 (Runtime Version is v2.0.50727). I am targeting .NET CLR version 4.0 since all properties of my VS Solution are defaults and this is 2010 RC. I was then able to write a console application in C# that established connectivity, executed a SELECT statement, and properly returned data when the table in question does NOT contain a spatial column. My problem is that this no longer works when the table I query has a column of type SDO_GEOMETRY in it.

Below is the simple console application I am trying to run that reproduces the problem. When the code gets to the line with the "ExecuteReader" command, an exception is raised and the message is "Unsupported column datatype".

using System;
using System.Data;
using Oracle.DataAccess.Client;

namespace ConsoleTestOracle
{
    class Program
    {
        static void Main(string[] args)
        {
            string oradb = string.Format("Data Source={0};User Id={1};Password={2};",
                "hostname/servicename", "login", "password");

            try
            {
                using (OracleConnection conn = new OracleConnection(oradb))
                {
                    conn.Open();

                    OracleCommand cmd = new OracleCommand();
                    cmd.Connection = conn;
                    cmd.CommandText = "select * from SDO_8307_2D_POINTS";
                    cmd.CommandType = CommandType.Text;

                    OracleDataReader dr = cmd.ExecuteReader();
                }
            }
            catch (Exception e)
            {
                string error = e.Message;
            }
        }
    }
}

The fact that this code works when used against a table that does not contain a spatial column of type SDO_GEOMETRY makes me think I have my windows 7 machine properly configured so I am surprised that I get this exception when the table contains different kinds of columns. I don't know if there is some configuration on my machine or the Oracle machine that needs to be done, or if the Oracle client software I have installed is wrong, or old and needs to be updated.

Here is the SQL I used to create the table, populate it with some rows containing points in the spatial column, etc. if you want to try to reproduce this exactly.

SQL Create Commands:

create table SDO_8307_2D_Points (ObjectID number(38) not null unique, TestID number, shape SDO_GEOMETRY);
Insert into SDO_8307_2D_Points values (1, 1, SDO_GEOMETRY(2001, 8307, null, SDO_ELEM_INFO_ARRAY(1, 1, 1),  SDO_ORDINATE_ARRAY(10.0, 10.0)));
Insert into SDO_8307_2D_Points values (2, 2, SDO_GEOMETRY(2001, 8307, null, SDO_ELEM_INFO_ARRAY(1, 1, 1),  SDO_ORDINATE_ARRAY(10.0, 20.0)));
insert into user_sdo_geom_metadata values ('SDO_8307_2D_Points', 'SHAPE', SDO_DIM_ARRAY(SDO_DIM_ELEMENT('Lat', -180, 180, 0.05), SDO_DIM_ELEMENT('Long', -90, 90, 0.05)), 8307);
create index SDO_8307_2D_Point_indx on SDO_8307_2D_Points(shape) indextype is mdsys.spatial_index PARAMETERS ('sdo_indx_dims=2' );

Any advice or insights would be greatly appreciated. Thank you.

Answer 1

Here is a link to a post with a sample app using C# and ODP.net to access spatial types.

http://www.orafaq.com/forum/mv/msg/27794/296419/0/#msg_296419

There is also a sample here about using XML to select the spatial types:

http://forums.oracle.com/forums/thread.jspa?threadID=241076