1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
|
* Oracle Data Provider
<ul>
<li>ADO.NET Data Provider for <a href="http://www.oracle.com/">Oracle</a> databases</li>
<li>Exists in namespace System.Data.OracleClient and assembly System.Data.OracleClient</li>
<li>Works on Windows and Linux</li>
<li>Works with Oracle 8i</li>
<li>Untested, but should work with Oracle 9i</li>
<li>Uses the Oracle CLI (Call Level Interface) which is a C library (API) for the Oracle Client
software</li>
<li>Internally, the OracleClient provider has OCI abstracted to an object-oriented programming model</li>
<li>Created by Daniel Morgan and Tim Coleman</li>
<li>Does not support trusted connections</li>
<li>Bugs with Mono or the data provider should be reported
in Mono's Bugzilla <a href="http://bugzilla.ximian.com/">here</a>. If you
do not have Bugzilla user account, it is free
and easy to
create one <a href="http://bugzilla.ximian.com/createaccount.cgi">here</a>.</li>
</ul>
** Current Status
<ul>
<li>OracleConnection can connect and disconnect to an Oracle 8i database on
Windows and Linux via OCI (Oracle Call-level Interface)</li>
<li>No longer uses a glue library (a library between the System.Data.OracleClient.dll
assembly and the oci library). In Current Mono cvs, System.Data.OracleClient
directly platform invokes into the oci library thanks to Tim Coleman.</li>
<li>Can have multiple connections with different transactions where each transaction is
separated from the others, so a rollback or commit in one transaction
does not affect the other.</li>
<li>Can execute simple DML SQL statements, such as,
INSERT a row into the EMP table via the OracleCommand's ExecuteNonQuery method</li>
<li>The System.Data.OracleClient.dll assembly can be built with mcs/mono via
the makefile.gnu for System.Data.OracleClient or csc/.net via the
System.Data.OracleClient.build nant build file.</li>
<li>Can retrieve data via ExecuteReader and OracleDataReader. Currently,
supports character, numeric, some date data types. ExecuteScalar
also works.</li>
<li>Simple input parameters (character and numeric data) can now
be used in SQL queries. Output parameters do not yet work.</li>
<li>OracleException and Error handling exists now.</li>
<li>Message handling needs to be added for non-critical messages
received from Oracle</li>
<li>Handling of various data types need to be added.</li>
<li>Data Adapter exists, and a DataSet can be filled using it. The
Data Adapter is abstract enough that it should work as expected.</li>
<li>Lots of missing functionality and bugs.</li>
<li>Works with SQL# command-line and GTK# versions in cvs. Only works with
simple character data though. SQL# For GTK# can only show the results to
the TextView because the Data Adapter is not yet available</li>
</ul>
** Action Plan
<ul>
<li>Be able to retrieve results via a data reader (WORKING)</li>
<li>Parameters support (IN PROGRESS)</li>
<li>transactions (WORKING)</li>
<li>Stored Procedures, Functions, and Packages support</li>
<li>Be able to fill a DataTable in a DataSet via a data adapter (IN PROGRESS)</li>
<li>Support for Oracle 8i and 9i (UNKNOWN)</li>
<li>Support LOBs</li>
<li>Support all the data types</li>
<li>Implement Connection pooling</li>
<li>Security</li>
</ul>
** Testing System.Data.OracleClient
<ul>
<li>Have a working mono and mcs</li>
<li>Have access to an Oracle 8i database or download it from
<a href="http://www.oracle.com/">Oracle</a>. If you are connecting
remotely to an Oracle database, you need the Oracle client software.
Registration to the <a href="http://technet.oracle.com/">Oracle Technology Network</a> is free. If installing on Linux,
I suggest you do a lot of searching to see how others installed Oracle on Linux.</li>
<li>Make sure System.Data.OracleClient.dll assembly is built, if not, go
into System.Data.OracleClient and do a make -f makefile.gnu (on Linux) or
../../nant/NAnt.exe (on Windows using Cygwin).</li>
<li>Take a look at TestOracleClient.cs found at mcs/class/System.Data.OracleClient/Test</li>
<li>The Data Source is an Oracle TNSNAME</li>
<li>Has a connection string format:
<pre>
"Data Source=tnsname;User ID=userid;Password=password"
</pre>
</li>
<li>C# Example:
<pre>
using System;
using System.Data;
using System.Data.OracleClient;
public class Test
{
public static void Main (string[] args)
{
string connectionString =
"Data Source=testdb;" +
"User ID=scott;" +
"Password=tiger;";
IDbConnection dbcon;
dbcon = new OracleConnection (connectionString);
dbcon.Open ();
IDbCommand dbcmd = dbcon.CreateCommand ();
string sql = "SELECT ename, job FROM scott.emp";
dbcmd.CommandText = sql;
IDataReader reader = dbcmd.ExecuteReader ();
while (reader.Read ()) {
string employeeName = reader["ename"];
string job = reader["job"];
Console.WriteLine ("Employee Name: {0} Job: {1}",
employeeName, job);
}
// clean up
reader.Close ();
reader = null;
dbcmd.CommandText = sql;
dbcmd.ExecuteNonQuery ();
dbcmd.Dispose ();
dbcmd = null;
dbcon.Close ();
dbcon = null;
}
}
</pre>
</li>
<li>Building C# Example:
<ul>
<li>Save the example to a file, such as, TestExample.cs</li>
<li>Build on Linux:
<pre>
mcs TestExample.cs -r System.Data.dll \
-r System.Data.OracleClient.dll
</pre>
</li>
<li>Build on Windows via Cygwin:
<pre>
mono C:/cygwin/home/MyHome/mono/install/bin/mcs.exe \
TestExample.cs \
-lib:C:/cygwin/home/MyHome/mono/install/lib \
-r System.Data.dll -r System.Data.OracleClient.dll
</pre>
</li>
</ul>
</li>
<li>Running the Example:
<pre>
mono TestExample.exe
</pre>
</li>
</ul>
|
