如何使用 C# 和 ADO.NET 查询具有 SDO_GEOMETRY 类型空间列的 Oracle 表？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2581252/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I use C# and ADO.NET to query an Oracle table with a spatial column of type SDO_GEOMETRY?
提问by John Donahue
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 hereto 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.
我的开发机器运行的是 Windows 7 Enterprise，64 位版本。我正在使用 Visual Studio 2010 Release Candidate。我正在连接到 Oracle 11g Enterprise 服务器版本 11.1.0.7.0。我很难找到为 64 位 Windows 系统制作的 Oracle 客户端软件，并最终登陆这里下载我认为是正确的客户端连接软件。我添加了对版本 2.111.6.0（运行时版本为 v2.0.50727）的“Oracle.DataAccess”的引用。我的目标是 .NET CLR 4.0 版，因为我的 VS 解决方案的所有属性都是默认值，这是 2010 RC。然后，我能够在 C# 中编写一个控制台应用程序，该应用程序建立连接，执行 SELECT 语句，并在相关表不包含空间列时正确返回数据。我的问题是，当我查询的表中有 SDO_GEOMETRY 类型的列时，这不再有效。
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".
下面是我尝试运行的简单控制台应用程序，它重现了该问题。当代码到达带有“ExecuteReader”命令的行时，会引发异常并且消息为“不支持的列数据类型”。
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.
该代码在用于不包含 SDO_GEOMETRY 类型的空间列的表时有效这一事实使我认为我的 Windows 7 机器配置正确，因此当表包含不同类型的列时出现此异常，我感到很惊讶。不知道是不是我的机器或者Oracle机器上有什么配置需要做，或者是我安装的Oracle客户端软件有问题，还是旧的需要更新。
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，如果你想尝试准确地重现它，用一些包含空间列中点的行填充它，等等。
SQL Create Commands:
SQL 创建命令：
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.
任何建议或见解将不胜感激。谢谢你。
回答by Doug Porter
Here is a link to a post with a sample app using C# and ODP.net to access spatial types.
这是一篇文章的链接，其中包含使用 C# 和 ODP.net 访问空间类型的示例应用程序。
http://www.orafaq.com/forum/mv/msg/27794/296419/0/#msg_296419
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:
这里还有一个关于使用 XML 选择空间类型的示例：
