Using C # to manipulate Sdo_geometry (read and write) for Oracle spatial

First, this requires the use of ODAC, which is Oracle.DataAccess.dll, the new managed Oracle.ManagedDataAccess.dll does not support object Type and cannot be used

ODAC Reference: http://www.oracle.com/technetwork/topics/dotnet/utilsoft-086879.html
Odac use method Reference: http://blog.csdn.net/rrrrssss00/article/details/7178515 code See attachment
Http://pan.baidu.com/s/1dDquLhv
Or
Http://www.kuaipan.cn/file/id_22823997376823621.htm


The general idea is to construct a corresponding class in C # based on the contents of the Sdo_geometry object, and then use OracleParameter to manipulate the object of the class when reading and writing, to read and write to the database Sdo_geometry object. The class name is Sdogeometry, the main code is as follows (it also uses the custom sdopoint,oraclearraytypefactory and Oraclecustomtypebase classes, the code of which is shown in the corresponding file of the attachment) [CSharp]View PlainCopy  

2. [Oraclecustomtypemappingattribute ("Mdsys. Sdo_geometry ")]
4. public class sdogeometry:oraclecustomtypebase<sdogeometry>
6. {
8. private enum Oracleobjectcolumns {sdo_gtype, Sdo_srid, Sdo_point, Sdo_elem_info, sdo_ordinates}
10. private decimal? Sdo_gtype;
12. [Oracleobjectmappingattribute (0)]
14. Public decimal? Sdo_gtype
16. {
18. get { return sdo_gtype;}
20. set {Sdo_gtype = value;}
22. }
24. private decimal? Sdo_srid;
26. [Oracleobjectmappingattribute (1)]
28. Public decimal? Sdo_srid
30. {
32. get { return sdo_srid;}
34. set {Sdo_srid = value;}
36. }
38. private Sdopoint Point;
40. [Oracleobjectmappingattribute (2)]
42. Public Sdopoint Point
44. {
46. get { return point;}
48. set {point = value;}
50. }
52. private decimal[] Elemarray;
54. [Oracleobjectmappingattribute (3)]
56. public decimal[] Elemarray
58. {
60. get { return elemarray;}
62. set {Elemarray = value;}
64. }
66. private decimal[] Ordinatesarray;
68. [Oracleobjectmappingattribute (4)]
70. public decimal[] Ordinatesarray
72. {
74. get { return ordinatesarray;}
76. set {Ordinatesarray = value;}
78. }
80. [Oraclecustomtypemappingattribute ("Mdsys. Sdo_elem_info_array ")]
82. public class elemarrayfactory:oraclearraytypefactorybase<decimal> {}
84. [Oraclecustomtypemappingattribute ("Mdsys. Sdo_ordinate_array ")]
86. public class ordinatesarrayfactory:oraclearraytypefactorybase<decimal> {}
88. public override void Mapfromcustomobject ()
90. {
92. SetValue ((int) oracleobjectcolumns.sdo_gtype, sdo_gtype);
94. SetValue ((int) Oracleobjectcolumns.sdo_srid, sdo_srid);
96. SetValue ((int) oracleobjectcolumns.sdo_point, point);
98. SetValue ((int) oracleobjectcolumns.sdo_elem_info, elemarray);
100. SetValue ((int) oracleobjectcolumns.sdo_ordinates, ordinatesarray);
102. }
104. public override void Maptocustomobject ()
106. {
108. Sdo_gtype = getvalue<decimal?> ((int) oracleobjectcolumns.sdo_gtype);
110. Sdo_srid = getvalue<decimal?> ((int) oracleobjectcolumns.sdo_srid);
112. Point = Getvalue<sdopoint> ((int) oracleobjectcolumns.sdo_point);
114. Elemarray = getvalue<decimal[]> ((int) oracleobjectcolumns.sdo_elem_info);
116. Ordinatesarray = getvalue<decimal[]> ((int) oracleobjectcolumns.sdo_ordinates);
118. }
120. }

The code read from the database is (the sample table has only two columns, the ID column is the number type, and the Geo column is the Sdo_geomtry type): [CSharp]View PlainCopy  

2. OracleCommand cmd = new OracleCommand ()
4. Cmd. Connection = con;
6. Cmd.commandtype = CommandType.Text;
8. Cmd.commandtext = "Select Id,geo from Geoinfo";
10. using (oracledatareader readergeoinfo = cmd. ExecuteReader ())
12. {
14. While (Readergeoinfo.read ())
16. {
18. Geoinfo geoinfo = new Geoinfo ();
20. if (!readergeoinfo.isdbnull (0))
22. {
24. geoinfo.id = readergeoinfo.getdecimal (0);
26. }
28. if (!readergeoinfo.isdbnull (1))
30. {
32. Geoinfo.geo = (sdogeometry) readergeoinfo.getvalue (1);
34. }
36. Geoinfolist.add (Geoinfo);
38. }
40. Readergeoinfo.close ();
42. }

The inserted code is: [CSharp]View PlainCopy  

2. Cmd.commandtext = "INSERT into geoinfo values (Geoinfo_seq.nextval,:p Aram)";
4. Cmd. Parameters.clear ();
6. OracleParameter Oracleparametergeo = new OracleParameter ();
8. Oracleparametergeo.oracledbtype = Oracledbtype.object;
10. Oracleparametergeo.udttypename = "Mdsys.  Sdo_geometry ";
12. Cmd. Parameters.Add (Oracleparametergeo);
14. //creating Point
16. Sdogeometry geoPoint = new Sdogeometry ();
18. Geopoint.sdo_gtype = 2001;
20. Geopoint.point = new Sdopoint ();
22. Geopoint.point.x = 200;
24. Geopoint.point.y = 400;
26. Oracleparametergeo.value = GeoPoint;
28. //insert Point in table Geoinfo
30. Cmd. ExecuteNonQuery ();
32. //creating Polygon
34. Sdogeometry Geopolygon = new Sdogeometry ();
36. Geopolygon.sdo_gtype = 2003;
38. Geopolygon.elemarray = new decimal[] {1, 1003, 1};
40. Geopolygon.ordinatesarray = new decimal[] {3, 3, 3, 10, 10, 10, 10, 3, 3, 3};
42. Oracleparametergeo.value = Geopolygon;
44. //insert polygon into table Geoinfo
46. Cmd. ExecuteNonQuery ();

In practice, when the query results of SELECT * are put into a DataTable using the DataAdapter Fill method, the query result automatically considers the column of the DataTable to be sdogeometry if the Sdogeometry class is already defined. , very convenient, for example oracledataadapter MADP = new OracleDataAdapter ("SELECT * from Geoinfo", con); DataTable MDST = new DataTable (); Madp.fill (MDST); At this point the second column data type of MDST is Sdogeometry

Using C # to manipulate Sdo_geometry (read and write) for Oracle spatial