2 // TestOracleClient.cs - Tests Sytem.Data.OracleClient
3 // data provider in Mono.
5 // Part of managed C#/.NET library System.Data.OracleClient.dll
7 // Part of the Mono class libraries at
8 // mcs/class/System.Data.OracleClient/System.Data.OracleClient.OCI
11 // Assembly: System.Data.OracleClient.dll
12 // Namespace: System.Data.OracleClient
15 // mcs TestOracleClient.cs /r:System.Data.dll /r:System.Data.OracleClient.dll /nowarn:0168
18 // Daniel Morgan <danielmorgan@verizon.net>
20 // Copyright (C) Daniel Morgan, 2002, 2004-2005
25 using System.Runtime.InteropServices;
27 using System.Data.OracleClient;
29 using System.Threading;
31 namespace Test.OracleClient
33 public class OracleTest
35 private static Thread t = null;
\r
36 private static string conStr;
\r
37 public static readonly int MAX_CONNECTIONS = 30; // max connections default to 100, but I will set to 30.
\r
44 static void MonoTest(OracleConnection con)
46 Console.WriteLine (" Drop table MONO_ORACLE_TEST ...");
48 OracleCommand cmd2 = con.CreateCommand ();
49 cmd2.CommandText = "DROP TABLE MONO_ORACLE_TEST";
50 cmd2.ExecuteNonQuery ();
52 catch (OracleException oe1) {
53 // ignore if table already exists
56 OracleCommand cmd = null;
58 Console.WriteLine(" Creating table MONO_ORACLE_TEST...");
59 cmd = new OracleCommand();
61 cmd.CommandText = "CREATE TABLE MONO_ORACLE_TEST ( " +
62 " varchar2_value VarChar2(32), " +
63 " long_value long, " +
64 " number_whole_value Number(18), " +
65 " number_scaled_value Number(18,2), " +
66 " number_integer_value Integer, " +
67 " float_value Float, " +
68 " date_value Date, " +
69 " char_value Char(32), " +
70 " clob_value Clob, " +
71 " blob_value Blob, " +
72 " clob_empty_value Clob, " +
73 " blob_empty_value Blob, " +
74 " varchar2_null_value VarChar2(32), " +
75 " number_whole_null_value Number(18), " +
76 " number_scaled_null_value Number(18,2), " +
77 " number_integer_null_value Integer, " +
78 " float_null_value Float, " +
79 " date_null_value Date, " +
80 " char_null_value Char(32), " +
81 " clob_null_value Clob, " +
82 " blob_null_value Blob " +
85 cmd.ExecuteNonQuery();
87 Console.WriteLine(" Begin Trans for table MONO_ORACLE_TEST...");
88 OracleTransaction trans = con.BeginTransaction ();
90 Console.WriteLine(" Inserting value into MONO_ORACLE_TEST...");
91 cmd = new OracleCommand();
93 cmd.Transaction = trans;
94 cmd.CommandText = "INSERT INTO mono_oracle_test " +
95 " ( varchar2_value, " +
97 " number_whole_value, " +
98 " number_scaled_value, " +
99 " number_integer_value, " +
105 " clob_empty_value, " +
106 " blob_empty_value " +
110 " 'This is a LONG column', " +
115 " TO_DATE( '2004-12-31', 'YYYY-MM-DD' ), " +
123 cmd.ExecuteNonQuery();
125 Console.WriteLine(" Select/Update CLOB columns on table MONO_ORACLE_TEST...");
127 // update BLOB and CLOB columns
128 OracleCommand select = con.CreateCommand ();
129 select.Transaction = trans;
130 select.CommandText = "SELECT CLOB_VALUE, BLOB_VALUE FROM MONO_ORACLE_TEST FOR UPDATE";
131 OracleDataReader reader = select.ExecuteReader ();
133 Console.WriteLine ("ERROR: RECORD NOT FOUND");
135 Console.WriteLine(" Update CLOB column on table MONO_ORACLE_TEST...");
136 OracleLob clob = reader.GetOracleLob (0);
138 UnicodeEncoding encoding = new UnicodeEncoding ();
139 bytes = encoding.GetBytes ("Mono is fun!");
140 clob.Write (bytes, 0, bytes.Length);
143 Console.WriteLine(" Update BLOB column on table MONO_ORACLE_TEST...");
144 OracleLob blob = reader.GetOracleLob (1);
145 bytes = new byte[6] { 0x31, 0x32, 0x33, 0x34, 0x35, 0x036 };
146 blob.Write (bytes, 0, bytes.Length);
149 Console.WriteLine(" Commit trans for table MONO_ORACLE_TEST...");
152 // OracleCommand.ExecuteReader of MONO_ORACLE_TEST table
153 Console.WriteLine(" Read simple test for table MONO_ORACLE_TEST...");
154 ReadSimpleTest(con, "SELECT * FROM MONO_ORACLE_TEST");
156 // OracleCommand.ExecuteScalar
157 Console.WriteLine(" -ExecuteScalar tests...");
158 string varchar2_value = (string) ReadScalar (con,"SELECT MAX(varchar2_value) FROM MONO_ORACLE_TEST");
159 Console.WriteLine(" String Value: " + varchar2_value);
161 Console.WriteLine(" Read Scalar: number_whole_value");
162 decimal number_whole_value = (decimal)
163 ReadScalar (con,"SELECT MAX(number_whole_value) FROM MONO_ORACLE_TEST");
164 Console.WriteLine(" Int32 Value: " + number_whole_value.ToString());
166 Console.WriteLine(" Read Scalar: number_scaled_value");
167 decimal number_scaled_value = (decimal)
168 ReadScalar (con,"SELECT number_scaled_value FROM MONO_ORACLE_TEST");
169 Console.WriteLine(" Decimal Value: " + number_scaled_value.ToString());
171 Console.WriteLine(" Read Scalar: date_value");
172 DateTime date_value = (DateTime)
173 ReadScalar (con,"SELECT date_value FROM MONO_ORACLE_TEST");
174 Console.WriteLine(" DateTime Value: " + date_value.ToString());
176 Console.WriteLine(" Read Scalar: clob_value");
177 string clob_value = (string)
178 ReadScalar (con,"SELECT clob_value FROM MONO_ORACLE_TEST");
179 Console.WriteLine(" CLOB Value: " + clob_value);
181 Console.WriteLine(" Read Scalar: blob_value");
182 byte[] blob_value = (byte[])
183 ReadScalar (con,"SELECT blob_value FROM MONO_ORACLE_TEST");
184 string sblob_value = GetHexString (blob_value);
185 Console.WriteLine(" BLOB Value: " + sblob_value);
187 // OracleCommand.ExecuteOracleScalar
188 Console.WriteLine(" -ExecuteOracleScalar tests...");
189 Console.WriteLine(" Read Oracle Scalar: varchar2_value");
190 ReadOracleScalar (con,"SELECT MAX(varchar2_value) FROM MONO_ORACLE_TEST");
192 Console.WriteLine(" Read Oracle Scalar: number_whole_value");
193 ReadOracleScalar (con,"SELECT MAX(number_whole_value) FROM MONO_ORACLE_TEST");
195 Console.WriteLine(" Read Oracle Scalar: number_scaled_value");
196 ReadOracleScalar (con,"SELECT number_scaled_value FROM MONO_ORACLE_TEST");
198 Console.WriteLine(" Read Oracle Scalar: date_value");
199 ReadOracleScalar (con,"SELECT date_value FROM MONO_ORACLE_TEST");
201 Console.WriteLine(" Read Oracle Scalar: clob_value");
202 ReadOracleScalar (con,"SELECT clob_value FROM MONO_ORACLE_TEST");
204 Console.WriteLine(" Read Oracle Scalar: blob_value");
205 ReadOracleScalar (con,"SELECT blob_value FROM MONO_ORACLE_TEST");
208 static object ReadScalar (OracleConnection con, string selectSql)
210 OracleCommand cmd = null;
211 cmd = con.CreateCommand();
212 cmd.CommandText = selectSql;
214 object o = cmd.ExecuteScalar ();
216 string dataType = o.GetType ().ToString ();
217 Console.WriteLine (" DataType: " + dataType);
221 static void ReadOracleScalar (OracleConnection con, string selectSql)
223 OracleCommand cmd = null;
224 cmd = con.CreateCommand();
225 cmd.CommandText = selectSql;
227 object o = cmd.ExecuteOracleScalar ();
229 string dataType = o.GetType ().ToString ();
230 Console.WriteLine (" DataType: " + dataType);
231 if (dataType.Equals("System.Data.OracleClient.OracleLob"))
232 o = ((OracleLob) o).Value;
233 if (o.GetType ().ToString ().Equals ("System.Byte[]"))
234 o = GetHexString ((byte[])o);
236 Console.WriteLine (" Value: " + o.ToString ());
239 static void ReadSimpleTest(OracleConnection con, string selectSql)
241 OracleCommand cmd = null;
242 OracleDataReader reader = null;
244 cmd = con.CreateCommand();
245 cmd.CommandText = selectSql;
246 reader = cmd.ExecuteReader();
248 Console.WriteLine(" Results...");
249 Console.WriteLine(" Schema");
251 table = reader.GetSchemaTable();
252 for(int c = 0; c < reader.FieldCount; c++) {
253 Console.WriteLine(" Column " + c.ToString());
254 DataRow row = table.Rows[c];
256 string strColumnName = row["ColumnName"].ToString();
257 string strBaseColumnName = row["BaseColumnName"].ToString();
258 string strColumnSize = row["ColumnSize"].ToString();
259 string strNumericScale = row["NumericScale"].ToString();
260 string strNumericPrecision = row["NumericPrecision"].ToString();
261 string strDataType = row["DataType"].ToString();
263 Console.WriteLine(" ColumnName: " + strColumnName);
264 Console.WriteLine(" BaseColumnName: " + strBaseColumnName);
265 Console.WriteLine(" ColumnSize: " + strColumnSize);
266 Console.WriteLine(" NumericScale: " + strNumericScale);
267 Console.WriteLine(" NumericPrecision: " + strNumericPrecision);
268 Console.WriteLine(" DataType: " + strDataType);
272 Console.WriteLine (" Data");
273 while (reader.Read ()) {
275 Console.WriteLine (" Row: " + r.ToString ());
276 for (int f = 0; f < reader.FieldCount; f++) {
280 string sDataType = "";
281 string sFieldType = "";
282 string sDataTypeName = "";
283 string sOraDataType = "";
285 sname = reader.GetName (f);
287 if (reader.IsDBNull (f)) {
288 ovalue = DBNull.Value;
290 sDataType = "DBNull.Value";
291 sOraDataType = "DBNull.Value";
294 //ovalue = reader.GetValue (f);
295 ovalue = reader.GetOracleValue (f);
296 object oravalue = null;
298 sDataType = ovalue.GetType ().ToString ();
300 case "System.Data.OracleClient.OracleString":
301 oravalue = ((OracleString) ovalue).Value;
303 case "System.Data.OracleClient.OracleNumber":
304 oravalue = ((OracleNumber) ovalue).Value;
306 case "System.Data.OracleClient.OracleLob":
307 OracleLob lob = (OracleLob) ovalue;
308 oravalue = lob.Value;
311 case "System.Data.OracleClient.OracleDateTime":
312 oravalue = ((OracleDateTime) ovalue).Value;
314 case "System.Byte[]":
315 oravalue = GetHexString((byte[])ovalue);
317 case "System.Decimal":
318 //Console.WriteLine(" *** Get Decimal, Int16, Int32, Int64, Float, Double, ...");
319 decimal dec = reader.GetDecimal (f);
320 //Console.WriteLine(" GetDecimal: " + dec.ToString ());
322 oravalue = (object) dec;
326 } catch (NotSupportedException e) {
327 Console.WriteLine (" ** Expected exception caught for GetInt16: NotSupportedException: " + e.Message);
331 long lng = reader.GetInt64 (f);
332 Console.WriteLine(" GetInt64: " + lng.ToString ());
333 int n = reader.GetInt32 (f);
334 Console.WriteLine(" GetInt32: " + n.ToString ());
335 float flt = reader.GetFloat (f);
336 Console.WriteLine(" GetFloat: " + flt.ToString ());
337 double dbl = reader.GetDouble (f);
338 Console.WriteLine(" GetDouble: " + dbl.ToString ());
339 } catch (OverflowException oe1) {
340 Console.WriteLine (" ** Overflow exception for numbers to big or too small: " + oe1.Message);
345 oravalue = ovalue.ToString ();
350 sOraDataType = oravalue.GetType ().ToString ();
351 if (sOraDataType.Equals ("System.Byte[]"))
352 svalue = GetHexString ((byte[]) oravalue);
354 svalue = oravalue.ToString();
357 sFieldType = reader.GetFieldType(f).ToString();
358 sDataTypeName = reader.GetDataTypeName(f);
360 Console.WriteLine(" Field: " + f.ToString());
361 Console.WriteLine(" Name: " + sname);
362 Console.WriteLine(" Value: " + svalue);
363 Console.WriteLine(" Oracle Data Type: " + sOraDataType);
364 Console.WriteLine(" Data Type: " + sDataType);
365 Console.WriteLine(" Field Type: " + sFieldType);
366 Console.WriteLine(" Data Type Name: " + sDataTypeName);
370 Console.WriteLine(" No data returned.");
373 static void DataAdapterTest (OracleConnection connection)
375 Console.WriteLine(" Create select command...");
376 OracleCommand command = connection.CreateCommand ();
377 command.CommandText = "SELECT * FROM EMP";
379 Console.WriteLine(" Create data adapter...");
380 OracleDataAdapter adapter = new OracleDataAdapter (command);
382 Console.WriteLine(" Create DataSet...");
383 DataSet dataSet = new DataSet ("EMP");
385 Console.WriteLine(" Fill DataSet via data adapter...");
386 adapter.Fill (dataSet);
388 Console.WriteLine(" Get DataTable...");
389 DataTable table = dataSet.Tables [0];
391 Console.WriteLine(" Display each row...");
393 foreach (DataRow row in table.Rows) {
394 Console.WriteLine (" row {0}", rowCount + 1);
395 for (int i = 0; i < table.Columns.Count; i += 1) {
396 Console.WriteLine (" {0}: {1}", table.Columns [i].ColumnName, row [i]);
398 Console.WriteLine ();
403 public static void DataAdapterTest2 (OracleConnection con)
\r
405 DataAdapterTest2_Setup (con);
\r
406 ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");
\r
408 GetMetaData (con, "SELECT * FROM mono_adapter_test");
\r
410 DataAdapterTest2_Insert (con);
\r
411 ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");
\r
413 DataAdapterTest2_Update (con);
\r
414 ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");
\r
416 DataAdapterTest2_Delete (con);
\r
417 ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");
\r
420 public static void GetMetaData (OracleConnection con, string sql)
\r
422 OracleCommand cmd = null;
423 OracleDataReader rdr = null;
425 cmd = con.CreateCommand();
426 cmd.CommandText = sql;
428 Console.WriteLine("Read Schema With KeyInfo");
429 rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly);
432 dt = rdr.GetSchemaTable();
\r
433 foreach (DataRow schemaRow in dt.Rows) {
\r
434 foreach (DataColumn schemaCol in dt.Columns) {
\r
435 Console.WriteLine(schemaCol.ColumnName +
\r
437 schemaRow[schemaCol]);
\r
438 Console.WriteLine("---Type: " + schemaRow[schemaCol].GetType ().ToString());
\r
440 Console.WriteLine("");
\r
443 Console.WriteLine("Read Schema with No KeyInfo");
\r
445 rdr = cmd.ExecuteReader();
447 dt = rdr.GetSchemaTable();
\r
448 foreach (DataRow schemaRow in dt.Rows) {
\r
449 foreach (DataColumn schemaCol in dt.Columns) {
\r
450 Console.WriteLine(schemaCol.ColumnName +
\r
452 schemaRow[schemaCol]);
\r
453 Console.WriteLine("---Type: " + schemaRow[schemaCol].GetType ().ToString());
\r
454 Console.WriteLine();
\r
460 public static void DataAdapterTest2_Setup (OracleConnection con)
\r
462 Console.WriteLine (" Drop table mono_adapter_test ...");
464 OracleCommand cmd2 = con.CreateCommand ();
465 cmd2.CommandText = "DROP TABLE mono_adapter_test";
466 cmd2.ExecuteNonQuery ();
468 catch (OracleException oe1) {
469 // ignore if table already exists
472 OracleCommand cmd = null;
473 int rowsAffected = 0;
475 Console.WriteLine(" Creating table mono_adapter_test...");
476 cmd = new OracleCommand ();
477 cmd.Connection = con;
478 cmd.CommandText = "CREATE TABLE mono_adapter_test ( " +
479 " varchar2_value VarChar2(32), " +
480 " number_whole_value Number(18) PRIMARY KEY, " +
481 " number_scaled_value Number(18,2), " +
482 " number_integer_value Integer, " +
483 " float_value Float, " +
484 " date_value Date, " +
485 " clob_value Clob, " +
486 " blob_value Blob ) ";
488 // FIXME: char_value does not work
490 cmd.CommandText = "CREATE TABLE mono_adapter_test ( " +
491 " varchar2_value VarChar2(32), " +
492 " number_whole_value Number(18) PRIMARY KEY, " +
493 " number_scaled_value Number(18,2), " +
494 " number_integer_value Integer, " +
495 " float_value Float, " +
496 " date_value Date, " +
497 " char_value Char(32), " +
498 " clob_value Clob, " +
499 " blob_value Blob ) ";
502 rowsAffected = cmd.ExecuteNonQuery();
504 Console.WriteLine(" Begin Trans for table mono_adapter_test...");
505 OracleTransaction trans = con.BeginTransaction ();
507 Console.WriteLine(" Inserting value into mono_adapter_test...");
508 cmd = new OracleCommand();
509 cmd.Connection = con;
510 cmd.Transaction = trans;
512 cmd.CommandText = "INSERT INTO mono_adapter_test " +
513 " ( varchar2_value, " +
514 " number_whole_value, " +
515 " number_scaled_value, " +
516 " number_integer_value, " +
528 " TO_DATE( '2004-12-31', 'YYYY-MM-DD' ), " +
534 cmd.CommandText = "INSERT INTO mono_adapter_test " +
535 " ( varchar2_value, " +
536 " number_whole_value, " +
537 " number_scaled_value, " +
538 " number_integer_value, " +
551 " TO_DATE( '2004-12-31', 'YYYY-MM-DD' ), " +
557 rowsAffected = cmd.ExecuteNonQuery();
\r
559 Console.WriteLine(" Select/Update CLOB columns on table mono_adapter_test...");
561 // update BLOB and CLOB columns
562 OracleCommand select = con.CreateCommand ();
563 select.Transaction = trans;
564 select.CommandText = "SELECT CLOB_VALUE, BLOB_VALUE FROM mono_adapter_test FOR UPDATE";
565 OracleDataReader reader = select.ExecuteReader ();
567 Console.WriteLine ("ERROR: RECORD NOT FOUND");
570 Console.WriteLine(" Update CLOB column on table mono_adapter_test...");
571 OracleLob clob = reader.GetOracleLob (0);
573 UnicodeEncoding encoding = new UnicodeEncoding ();
574 bytes = encoding.GetBytes ("Mono is fun!");
575 clob.Write (bytes, 0, bytes.Length);
579 Console.WriteLine(" Update BLOB column on table mono_adapter_test...");
580 OracleLob blob = reader.GetOracleLob (1);
581 bytes = new byte[6] { 0x31, 0x32, 0x33, 0x34, 0x35, 0x036 };
582 blob.Write (bytes, 0, bytes.Length);
585 Console.WriteLine(" Commit trans for table mono_adapter_test...");
588 CommitCursor (con);
\r
591 public static void DataAdapterTest2_Insert (OracleConnection con)
593 Console.WriteLine("================================");
\r
594 Console.WriteLine("=== Adapter Insert =============");
595 Console.WriteLine("================================");
\r
596 OracleTransaction transaction = con.BeginTransaction ();
598 Console.WriteLine(" Create adapter...");
\r
599 OracleDataAdapter da = new OracleDataAdapter("select * from mono_adapter_test", con);
600 da.SelectCommand.Transaction = transaction;
602 Console.WriteLine(" Create command builder...");
\r
603 OracleCommandBuilder mycb = new OracleCommandBuilder(da);
605 Console.WriteLine(" Create data set ...");
\r
606 DataSet ds = new DataSet();
\r
608 Console.WriteLine(" Fill data set via adapter...");
\r
609 da.Fill(ds, "mono_adapter_test");
\r
611 Console.WriteLine(" New Row...");
613 myRow = ds.Tables["mono_adapter_test"].NewRow();
615 byte[] bytes = new byte[] { 0x45,0x46,0x47,0x48,0x49,0x50 };
\r
617 Console.WriteLine(" Set values in the new DataRow...");
\r
618 myRow["varchar2_value"] = "OracleClient";
\r
619 myRow["number_whole_value"] = 22;
\r
620 myRow["number_scaled_value"] = 12.34;
\r
621 myRow["number_integer_value"] = 456;
\r
622 myRow["float_value"] = 98.76;
\r
623 myRow["date_value"] = new DateTime(2001,07,09);
\r
624 Console.WriteLine(" *** FIXME; char value not working");
\r
625 //myRow["char_value"] = "Romeo";
\r
626 myRow["clob_value"] = "clobtest";
\r
627 myRow["blob_value"] = bytes;
\r
629 Console.WriteLine(" Add DataRow to DataTable...");
\r
630 ds.Tables["mono_adapter_test"].Rows.Add(myRow);
632 Console.WriteLine("da.Update(ds...");
\r
633 da.Update(ds, "mono_adapter_test");
\r
635 transaction.Commit();
638 public static void DataAdapterTest2_Update (OracleConnection con)
640 Console.WriteLine("================================");
\r
641 Console.WriteLine("=== Adapter Update =============");
642 Console.WriteLine("================================");
\r
644 OracleTransaction transaction = con.BeginTransaction ();
646 Console.WriteLine(" Create adapter...");
\r
647 OracleCommand selectCmd = con.CreateCommand ();
648 selectCmd.Transaction = transaction;
\r
649 selectCmd.CommandText = "SELECT * FROM mono_adapter_test";
\r
650 OracleDataAdapter da = new OracleDataAdapter(selectCmd);
\r
651 Console.WriteLine(" Create command builder...");
\r
652 OracleCommandBuilder mycb = new OracleCommandBuilder(da);
\r
653 Console.WriteLine(" Create data set ...");
\r
654 DataSet ds = new DataSet();
\r
656 Console.WriteLine(" Set missing schema action...");
\r
658 Console.WriteLine(" Fill data set via adapter...");
\r
659 da.Fill(ds, "mono_adapter_test");
\r
662 Console.WriteLine(" New Row...");
663 myRow = ds.Tables["mono_adapter_test"].Rows[0];
665 Console.WriteLine("Tables Count: " + ds.Tables.Count.ToString());
667 DataTable table = ds.Tables["mono_adapter_test"];
668 DataRowCollection rows;
670 Console.WriteLine(" Row Count: " + rows.Count.ToString());
673 byte[] bytes = new byte[] { 0x62,0x63,0x64,0x65,0x66,0x67 };
\r
675 Console.WriteLine(" Set values in the new DataRow...");
\r
677 myRow["varchar2_value"] = "Super Power!";
\r
679 myRow["number_scaled_value"] = 12.35;
\r
680 myRow["number_integer_value"] = 457;
\r
681 myRow["float_value"] = 198.76;
\r
682 myRow["date_value"] = new DateTime(2002,08,09);
\r
683 //myRow["char_value"] = "Juliet";
\r
684 myRow["clob_value"] = "this is a clob";
\r
685 myRow["blob_value"] = bytes;
687 Console.WriteLine("da.Update(ds...");
\r
688 da.Update(ds, "mono_adapter_test");
\r
690 transaction.Commit();
693 public static void DataAdapterTest2_Delete (OracleConnection con)
695 Console.WriteLine("================================");
\r
696 Console.WriteLine("=== Adapter Delete =============");
697 Console.WriteLine("================================");
\r
698 OracleTransaction transaction = con.BeginTransaction ();
700 Console.WriteLine(" Create adapter...");
\r
701 OracleDataAdapter da = new OracleDataAdapter("SELECT * FROM mono_adapter_test", con);
702 Console.WriteLine(" Create command builder...");
703 OracleCommandBuilder mycb = new OracleCommandBuilder(da);
704 Console.WriteLine(" set transr...");
705 da.SelectCommand.Transaction = transaction;
707 Console.WriteLine(" Create data set ...");
\r
708 DataSet ds = new DataSet();
\r
710 Console.WriteLine("Fill data set via adapter...");
\r
711 da.Fill(ds, "mono_adapter_test");
\r
713 Console.WriteLine("delete row...");
714 ds.Tables["mono_adapter_test"].Rows[0].Delete();
716 Console.WriteLine("da.Update(table...");
\r
717 da.Update(ds, "mono_adapter_test");
\r
719 Console.WriteLine("Commit...");
\r
720 transaction.Commit();
\r
723 static void TestNonQueryUsingExecuteReader(OracleConnection con)
\r
725 OracleDataReader reader = null;
\r
726 OracleTransaction trans = null;
\r
728 Console.WriteLine(" drop table mono_adapter_test...");
\r
729 OracleCommand cmd = con.CreateCommand();
\r
731 cmd.CommandText = "DROP TABLE MONO_ADAPTER_TEST";
\r
732 trans = con.BeginTransaction();
\r
733 cmd.Transaction = trans;
\r
735 reader = cmd.ExecuteReader();
\r
736 Console.WriteLine(" RowsAffected before read: " + reader.RecordsAffected.ToString());
\r
738 Console.WriteLine(" RowsAffected after read: " + reader.RecordsAffected.ToString());
\r
740 Console.WriteLine(" RowsAffected after close: " + reader.RecordsAffected.ToString());
\r
743 catch(OracleException e) {
\r
744 Console.WriteLine(" OracleException caught: " + e.Message);
\r
748 Console.WriteLine(" Create table mono_adapter_test...");
\r
749 cmd.CommandText = "CREATE TABLE MONO_ADAPTER_TEST ( " +
750 " varchar2_value VarChar2(32), " +
751 " number_whole_value Number(18,0) PRIMARY KEY ) ";
\r
752 trans = con.BeginTransaction();
\r
753 cmd.Transaction = trans;
\r
754 reader = cmd.ExecuteReader();
\r
755 Console.WriteLine(" RowsAffected before read: " + reader.RecordsAffected.ToString());
\r
757 Console.WriteLine(" RowsAffected after read: " + reader.RecordsAffected.ToString());
\r
759 Console.WriteLine(" RowsAffected after close: " + reader.RecordsAffected.ToString());
\r
762 Console.WriteLine("Insert into table mono_adapter_test...");
\r
765 "INSERT INTO MONO_ADAPTER_TEST " +
\r
766 "(VARCHAR2_VALUE,NUMBER_WHOLE_VALUE) " +
\r
769 OracleCommand cmd2 = con.CreateCommand();
\r
770 trans = con.BeginTransaction();
\r
771 cmd2.Transaction = trans;
\r
772 cmd2.CommandText = sql;
\r
774 OracleParameter myParameter1 = new OracleParameter("p1", OracleType.VarChar, 32);
775 myParameter1.Direction = ParameterDirection.Input;
\r
777 OracleParameter myParameter2 = new OracleParameter("p2", OracleType.Number);
\r
778 myParameter2.Direction = ParameterDirection.Input;
\r
780 myParameter2.Value = 182;
\r
781 myParameter1.Value = "Mono";
\r
783 cmd2.Parameters.Add (myParameter1);
\r
784 cmd2.Parameters.Add (myParameter2);
\r
787 reader = cmd2.ExecuteReader();
\r
788 Console.WriteLine(" RowsAffected before read: " + reader.RecordsAffected.ToString());
\r
790 Console.WriteLine(" RowsAffected after read: " + reader.RecordsAffected.ToString());
\r
792 Console.WriteLine(" RowsAffected after close: " + reader.RecordsAffected.ToString());
\r
794 // insert another record
\r
795 Console.WriteLine(" Insert another record...");
\r
796 myParameter2.Value = 183;
\r
797 myParameter1.Value = "Oracle";
\r
798 reader = cmd2.ExecuteReader();
\r
799 Console.WriteLine(" RowsAffected before read: " + reader.RecordsAffected.ToString());
\r
801 Console.WriteLine(" RowsAffected after read: " + reader.RecordsAffected.ToString());
\r
803 Console.WriteLine(" RowsAffected after close: " + reader.RecordsAffected.ToString());
\r
808 ReadSimpleTest(con, "SELECT * FROM MONO_ADAPTER_TEST");
\r
811 static void CommitCursor (OracleConnection con)
813 OracleCommand cmd = con.CreateCommand ();
814 cmd.CommandText = "COMMIT";
815 cmd.ExecuteNonQuery ();
820 static void RollbackTest (OracleConnection connection)
822 OracleTransaction transaction = connection.BeginTransaction ();
824 OracleCommand insert = connection.CreateCommand ();
825 insert.Transaction = transaction;
826 insert.CommandText = "INSERT INTO EMP (EMPNO, ENAME, JOB) VALUES (8787, 'T Coleman', 'Monoist')";
828 Console.WriteLine (" Inserting record ...");
830 insert.ExecuteNonQuery ();
832 OracleCommand select = connection.CreateCommand ();
833 select.CommandText = "SELECT COUNT(*) FROM EMP WHERE EMPNO = 8787";
834 select.Transaction = transaction;
835 OracleDataReader reader = select.ExecuteReader ();
838 Console.WriteLine (" Row count SHOULD BE 1, VALUE IS {0}", reader.GetValue (0));
841 Console.WriteLine (" Rolling back transaction ...");
843 transaction.Rollback ();
845 select = connection.CreateCommand ();
846 select.CommandText = "SELECT COUNT(*) FROM EMP WHERE EMPNO = 8787";
848 reader = select.ExecuteReader ();
850 Console.WriteLine (" Row count SHOULD BE 0, VALUE IS {0}", reader.GetValue (0));
854 static void CommitTest (OracleConnection connection)
856 OracleTransaction transaction = connection.BeginTransaction ();
858 OracleCommand insert = connection.CreateCommand ();
859 insert.Transaction = transaction;
860 insert.CommandText = "INSERT INTO EMP (EMPNO, ENAME, JOB) VALUES (8787, 'T Coleman', 'Monoist')";
862 Console.WriteLine (" Inserting record ...");
864 insert.ExecuteNonQuery ();
866 OracleCommand select = connection.CreateCommand ();
867 select.CommandText = "SELECT COUNT(*) FROM EMP WHERE EMPNO = 8787";
868 select.Transaction = transaction;
870 Console.WriteLine (" Row count SHOULD BE 1, VALUE IS {0}", select.ExecuteScalar ());
872 Console.WriteLine (" Committing transaction ...");
874 transaction.Commit ();
876 select = connection.CreateCommand ();
877 select.CommandText = "SELECT COUNT(*) FROM EMP WHERE EMPNO = 8787";
879 Console.WriteLine ("Row count SHOULD BE 1, VALUE IS {0}", select.ExecuteScalar ());
880 transaction = connection.BeginTransaction ();
881 OracleCommand delete = connection.CreateCommand ();
882 delete.Transaction = transaction;
883 delete.CommandText = "DELETE FROM EMP WHERE EMPNO = 8787";
884 delete.ExecuteNonQuery ();
885 transaction.Commit ();
888 public static void ParameterTest2 (OracleConnection connection)
890 Console.WriteLine(" Setting NLS_DATE_FORMAT...");
\r
892 OracleCommand cmd2 = connection.CreateCommand();
\r
893 cmd2.CommandText = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'";
\r
895 cmd2.ExecuteNonQuery ();
\r
897 Console.WriteLine(" Drop table MONO_TEST_TABLE2...");
899 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE7";
900 cmd2.ExecuteNonQuery ();
902 catch(OracleException oe1) {
903 // ignore if table already exists
906 Console.WriteLine(" Create table MONO_TEST_TABLE7...");
908 cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE7(" +
909 " COL1 VARCHAR2(8) NOT NULL, " +
910 " COL2 VARCHAR2(32), " +
911 " COL3 NUMBER(18,2), " +
912 " COL4 NUMBER(18,2), " +
913 " COL5 DATE NOT NULL, " +
915 " COL7 BLOB NOT NULL, " +
917 " COL9 CLOB NOT NULL, " +
920 cmd2.ExecuteNonQuery ();
922 Console.WriteLine(" COMMIT...");
923 cmd2.CommandText = "COMMIT";
924 cmd2.ExecuteNonQuery ();
926 Console.WriteLine(" create insert command...");
928 OracleTransaction trans = connection.BeginTransaction ();
\r
929 OracleCommand cmd = connection.CreateCommand ();
\r
930 cmd.Transaction = trans;
\r
932 cmd.CommandText = "INSERT INTO MONO_TEST_TABLE7 " +
\r
933 "(COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10) " +
\r
934 "VALUES(:P1,:P2,:P3,:P4,:P5,:P6,:P7,:P8,:P9,:P10)";
\r
936 Console.WriteLine(" Add parameters...");
\r
938 OracleParameter parm1 = cmd.Parameters.Add (":P1", OracleType.VarChar, 8);
\r
939 OracleParameter parm2 = cmd.Parameters.Add (":P2", OracleType.VarChar, 32);
\r
941 OracleParameter parm3 = cmd.Parameters.Add (":P3", OracleType.Number);
\r
942 OracleParameter parm4 = cmd.Parameters.Add (":P4", OracleType.Number);
\r
944 OracleParameter parm5 = cmd.Parameters.Add (":P5", OracleType.DateTime);
\r
945 OracleParameter parm6 = cmd.Parameters.Add (":P6", OracleType.DateTime);
\r
947 // FIXME: fix BLOBs and CLOBs in OracleParameter
\r
949 OracleParameter parm7 = cmd.Parameters.Add (":P7", OracleType.Blob);
\r
950 OracleParameter parm8 = cmd.Parameters.Add (":P8", OracleType.Blob);
\r
952 OracleParameter parm9 = cmd.Parameters.Add (":P9", OracleType.Clob);
\r
953 OracleParameter parm10 = cmd.Parameters.Add (":P10", OracleType.Clob);
\r
955 // TODO: implement out, return, and ref parameters
\r
958 decimal d = 123456789012345.678M;
\r
959 DateTime dt = DateTime.Now;
\r
961 string clob = "Clob";
\r
962 byte[] blob = new byte[] { 0x31, 0x32, 0x33, 0x34, 0x35 };
\r
964 Console.WriteLine(" Set Values...");
\r
967 parm2.Value = DBNull.Value;
\r
970 parm4.Value = DBNull.Value;
\r
973 parm6.Value = DBNull.Value;
\r
975 parm7.Value = blob;
\r
976 parm8.Value = DBNull.Value;
\r
978 parm9.Value = clob;
\r
979 parm10.Value = DBNull.Value;
\r
981 Console.WriteLine(" ExecuteNonQuery...");
\r
983 cmd.ExecuteNonQuery ();
\r
987 public static void ParameterTest (OracleConnection connection)
989 Console.WriteLine(" Setting NLS_DATE_FORMAT...");
\r
991 OracleCommand cmd2 = connection.CreateCommand();
\r
992 cmd2.CommandText = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'";
\r
994 cmd2.ExecuteNonQuery ();
\r
996 Console.WriteLine(" Drop table MONO_TEST_TABLE2...");
998 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE7";
999 cmd2.ExecuteNonQuery ();
1001 catch(OracleException oe1) {
1002 // ignore if table already exists
1005 Console.WriteLine(" Create table MONO_TEST_TABLE7...");
1007 cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE7(" +
1008 " COL1 VARCHAR2(8) NOT NULL, " +
1009 " COL2 VARCHAR2(32), " +
1010 " COL3 NUMBER(18,2) NOT NULL, " +
1011 " COL4 NUMBER(18,2), " +
1012 " COL5 DATE NOT NULL, " +
1014 " COL7 BLOB NOT NULL, " +
1016 " COL9 CLOB NOT NULL, " +
1019 cmd2.ExecuteNonQuery ();
1021 Console.WriteLine(" COMMIT...");
1022 cmd2.CommandText = "COMMIT";
1023 cmd2.ExecuteNonQuery ();
1025 Console.WriteLine(" create insert command...");
1027 OracleTransaction trans = connection.BeginTransaction ();
\r
1028 OracleCommand cmd = connection.CreateCommand ();
\r
1029 cmd.Transaction = trans;
\r
1031 cmd.CommandText = "INSERT INTO MONO_TEST_TABLE7 " +
\r
1032 "(COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10) " +
\r
1033 "VALUES(:P1,:P2,:P3,:P4,:P5,:P6,:P7,:P8,:P9,:P10)";
\r
1035 Console.WriteLine(" Add parameters...");
\r
1037 OracleParameter parm1 = cmd.Parameters.Add (":P1", OracleType.VarChar, 8);
\r
1038 OracleParameter parm2 = cmd.Parameters.Add (":P2", OracleType.VarChar, 32);
\r
1040 OracleParameter parm3 = cmd.Parameters.Add (":P3", OracleType.Number);
\r
1041 OracleParameter parm4 = cmd.Parameters.Add (":P4", OracleType.Number);
\r
1043 OracleParameter parm5 = cmd.Parameters.Add (":P5", OracleType.DateTime);
\r
1044 OracleParameter parm6 = cmd.Parameters.Add (":P6", OracleType.DateTime);
\r
1046 // FIXME: fix BLOBs and CLOBs in OracleParameter
\r
1048 OracleParameter parm7 = cmd.Parameters.Add (":P7", OracleType.Blob);
\r
1049 OracleParameter parm8 = cmd.Parameters.Add (":P8", OracleType.Blob);
\r
1051 OracleParameter parm9 = cmd.Parameters.Add (":P9", OracleType.Clob);
\r
1052 OracleParameter parm10 = cmd.Parameters.Add (":P10", OracleType.Clob);
\r
1054 // TODO: implement out, return, and ref parameters
\r
1056 string s = "Mono";
\r
1057 decimal d = 123456789012345.678M;
\r
1058 DateTime dt = DateTime.Now;
\r
1060 string clob = "Clob";
\r
1061 byte[] blob = new byte[] { 0x31, 0x32, 0x33, 0x34, 0x35 };
\r
1063 Console.WriteLine(" Set Values...");
\r
1066 parm2.Value = DBNull.Value;
\r
1069 parm4.Value = DBNull.Value;
\r
1072 parm6.Value = DBNull.Value;
\r
1074 parm7.Value = blob;
\r
1075 parm8.Value = DBNull.Value;
\r
1077 parm9.Value = clob;
\r
1078 parm10.Value = DBNull.Value;
\r
1080 Console.WriteLine(" ExecuteNonQuery...");
\r
1082 cmd.ExecuteNonQuery ();
\r
1086 public static void CLOBTest (OracleConnection connection)
1088 Console.WriteLine (" BEGIN TRANSACTION ...");
1090 OracleTransaction transaction = connection.BeginTransaction ();
1092 Console.WriteLine (" Drop table CLOBTEST ...");
1094 OracleCommand cmd2 = connection.CreateCommand ();
1095 cmd2.Transaction = transaction;
1096 cmd2.CommandText = "DROP TABLE CLOBTEST";
1097 cmd2.ExecuteNonQuery ();
1099 catch (OracleException oe1) {
1100 // ignore if table already exists
1103 Console.WriteLine (" CREATE TABLE ...");
1105 OracleCommand create = connection.CreateCommand ();
1106 create.Transaction = transaction;
1107 create.CommandText = "CREATE TABLE CLOBTEST (CLOB_COLUMN CLOB)";
1108 create.ExecuteNonQuery ();
1110 Console.WriteLine (" INSERT RECORD ...");
1112 OracleCommand insert = connection.CreateCommand ();
1113 insert.Transaction = transaction;
1114 insert.CommandText = "INSERT INTO CLOBTEST VALUES (EMPTY_CLOB())";
1115 insert.ExecuteNonQuery ();
1117 OracleCommand select = connection.CreateCommand ();
1118 select.Transaction = transaction;
1119 select.CommandText = "SELECT CLOB_COLUMN FROM CLOBTEST FOR UPDATE";
1120 Console.WriteLine (" SELECTING A CLOB (CHARACTER) VALUE FROM CLOBTEST");
1122 OracleDataReader reader = select.ExecuteReader ();
1123 if (!reader.Read ())
1124 Console.WriteLine ("ERROR: RECORD NOT FOUND");
1126 Console.WriteLine (" TESTING OracleLob OBJECT ...");
1127 OracleLob lob = reader.GetOracleLob (0);
1128 Console.WriteLine (" LENGTH: {0}", lob.Length);
1129 Console.WriteLine (" CHUNK SIZE: {0}", lob.ChunkSize);
1131 UnicodeEncoding encoding = new UnicodeEncoding ();
1133 byte[] value = new byte [lob.Length * 2];
1135 Console.WriteLine (" CURRENT POSITION: {0}", lob.Position);
1136 Console.WriteLine (" UPDATING VALUE TO 'TEST ME!'");
1137 value = encoding.GetBytes ("TEST ME!");
1138 lob.Write (value, 0, value.Length);
1140 Console.WriteLine (" CURRENT POSITION: {0}", lob.Position);
1141 Console.WriteLine (" RE-READ VALUE...");
1142 lob.Seek (1, SeekOrigin.Begin);
1144 Console.WriteLine (" CURRENT POSITION: {0}", lob.Position);
1145 value = new byte [lob.Length * 2];
1146 lob.Read (value, 0, value.Length);
1147 Console.WriteLine (" VALUE: {0}", encoding.GetString (value));
1148 Console.WriteLine (" CURRENT POSITION: {0}", lob.Position);
1150 Console.WriteLine (" CLOSE OracleLob...");
1153 Console.WriteLine (" CLOSING READER...");
1156 transaction.Commit ();
1159 public static void BLOBTest (OracleConnection connection)
1161 Console.WriteLine (" BEGIN TRANSACTION ...");
1163 OracleTransaction transaction = connection.BeginTransaction ();
1165 Console.WriteLine (" Drop table BLOBTEST ...");
1167 OracleCommand cmd2 = connection.CreateCommand ();
1168 cmd2.Transaction = transaction;
1169 cmd2.CommandText = "DROP TABLE BLOBTEST";
1170 cmd2.ExecuteNonQuery ();
1172 catch (OracleException oe1) {
1173 // ignore if table already exists
1176 Console.WriteLine (" CREATE TABLE ...");
1178 OracleCommand create = connection.CreateCommand ();
1179 create.Transaction = transaction;
1180 create.CommandText = "CREATE TABLE BLOBTEST (BLOB_COLUMN BLOB)";
1181 create.ExecuteNonQuery ();
1183 Console.WriteLine (" INSERT RECORD ...");
1185 OracleCommand insert = connection.CreateCommand ();
1186 insert.Transaction = transaction;
1187 insert.CommandText = "INSERT INTO BLOBTEST VALUES (EMPTY_BLOB())";
1188 insert.ExecuteNonQuery ();
1190 OracleCommand select = connection.CreateCommand ();
1191 select.Transaction = transaction;
1192 select.CommandText = "SELECT BLOB_COLUMN FROM BLOBTEST FOR UPDATE";
1193 Console.WriteLine (" SELECTING A BLOB (Binary) VALUE FROM BLOBTEST");
1195 OracleDataReader reader = select.ExecuteReader ();
1196 if (!reader.Read ())
1197 Console.WriteLine ("ERROR: RECORD NOT FOUND");
1199 Console.WriteLine (" TESTING OracleLob OBJECT ...");
1200 OracleLob lob = reader.GetOracleLob (0);
1202 byte[] value = null;
1205 Console.WriteLine (" UPDATING VALUE");
1207 byte[] bytes = new byte[6];
1215 lob.Write (bytes, 0, bytes.Length);
1217 Console.WriteLine (" CURRENT POSITION: {0}", lob.Position);
1218 Console.WriteLine (" RE-READ VALUE...");
1219 lob.Seek (1, SeekOrigin.Begin);
1221 Console.WriteLine (" CURRENT POSITION: {0}", lob.Position);
1222 value = new byte [lob.Length];
1223 lob.Read (value, 0, value.Length);
1226 if (value.GetType ().ToString ().Equals ("System.Byte[]"))
1227 bvalue = GetHexString (value);
1228 Console.WriteLine (" Bytes: " + bvalue);
1230 Console.WriteLine (" CURRENT POSITION: {0}", lob.Position);
1232 Console.WriteLine (" CLOSE OracleLob...");
1235 Console.WriteLine (" CLOSING READER...");
1238 transaction.Commit ();
1241 static void Wait(string msg)
1243 Console.WriteLine(msg);
1245 Console.WriteLine("Waiting... Press Enter to continue...");
1249 // use this function to read a byte array into a string
1250 // for easy display of binary data, such as, a BLOB value
1251 public static string GetHexString (byte[] bytes)
1255 StringBuilder sb2 = new StringBuilder();
1256 for (int z = 0; z < bytes.Length; z++) {
1257 byte byt = bytes[z];
1258 sb2.Append (byt.ToString("x"));
1261 bvalue = "0x" + sb2.ToString ();
1266 static void StoredProcedureTest1 (OracleConnection con)
1268 // test stored procedure with no parameters
1271 OracleCommand cmd2 = con.CreateCommand ();
1273 Console.WriteLine(" Drop table MONO_TEST_TABLE1...");
1275 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE1";
1276 cmd2.ExecuteNonQuery ();
1278 catch(OracleException oe1) {
1279 // ignore if table did not exist
1282 Console.WriteLine(" Drop procedure SP_TEST1...");
1284 cmd2.CommandText = "DROP PROCEDURE SP_TEST1";
1285 cmd2.ExecuteNonQuery ();
1287 catch(OracleException oe1) {
1288 // ignore if procedure did not exist
1291 Console.WriteLine(" Create table MONO_TEST_TABLE1...");
1292 cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE1 (" +
1293 " COL1 VARCHAR2(8), "+
1294 " COL2 VARCHAR2(32))";
1295 cmd2.ExecuteNonQuery ();
1297 Console.WriteLine(" Create stored procedure SP_TEST1...");
1298 cmd2.CommandText = "CREATE PROCEDURE SP_TEST1 " +
1301 " INSERT INTO MONO_TEST_TABLE1 (COL1,COL2) VALUES ('aaa','bbbb');" +
1304 cmd2.ExecuteNonQuery ();
1306 Console.WriteLine("COMMIT...");
1307 cmd2.CommandText = "COMMIT";
1308 cmd2.ExecuteNonQuery ();
1310 Console.WriteLine(" Call stored procedure sp_test1...");
1311 OracleCommand cmd3 = con.CreateCommand ();
\r
1312 cmd3.CommandType = CommandType.StoredProcedure;
\r
1313 cmd3.CommandText = "sp_test1";
\r
1314 cmd3.ExecuteNonQuery ();
1317 static void StoredProcedureTest2 (OracleConnection con)
1319 // test stored procedure with 2 parameters
1321 Console.WriteLine(" Drop table MONO_TEST_TABLE2...");
1322 OracleCommand cmd2 = con.CreateCommand ();
1325 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE2";
1326 cmd2.ExecuteNonQuery ();
1328 catch(OracleException oe1) {
1329 // ignore if table already exists
1332 Console.WriteLine(" Drop procedure SP_TEST2...");
1334 cmd2.CommandText = "DROP PROCEDURE SP_TEST2";
1335 cmd2.ExecuteNonQuery ();
1337 catch(OracleException oe1) {
1338 // ignore if table already exists
1341 Console.WriteLine(" Create table MONO_TEST_TABLE2...");
1343 cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE2 (" +
1344 " COL1 VARCHAR2(8), "+
1345 " COL2 VARCHAR2(32))";
1346 cmd2.ExecuteNonQuery ();
1348 Console.WriteLine(" Create stored procedure SP_TEST2...");
1349 cmd2.CommandText = "CREATE PROCEDURE SP_TEST2(parm1 VARCHAR2,parm2 VARCHAR2) " +
1352 " INSERT INTO MONO_TEST_TABLE2 (COL1,COL2) VALUES (parm1,parm2);" +
1355 cmd2.ExecuteNonQuery ();
1357 Console.WriteLine(" COMMIT...");
1358 cmd2.CommandText = "COMMIT";
1359 cmd2.ExecuteNonQuery ();
1361 Console.WriteLine(" Call stored procedure SP_TEST2 with two parameters...");
1362 OracleCommand cmd3 = con.CreateCommand ();
\r
1363 cmd3.CommandType = CommandType.StoredProcedure;
\r
1364 cmd3.CommandText = "sp_test2";
\r
1366 OracleParameter myParameter1 = new OracleParameter("parm1", OracleType.VarChar);
\r
1367 myParameter1.Value = "yyy13";
\r
1368 myParameter1.Size = 8;
\r
1369 myParameter1.Direction = ParameterDirection.Input;
\r
1371 OracleParameter myParameter2 = new OracleParameter("parm2", OracleType.VarChar);
\r
1372 myParameter2.Value = "iii13";
\r
1373 myParameter2.Size = 32;
\r
1374 myParameter2.Direction = ParameterDirection.Input;
\r
1376 cmd3.Parameters.Add (myParameter1);
\r
1377 cmd3.Parameters.Add (myParameter2);
\r
1379 cmd3.ExecuteNonQuery ();
1382 static void OutParmTest1 (OracleConnection con)
1384 // test stored procedure with 2 parameters
1385 // 1. input varchar2
1386 // 2. output varchar
1388 OracleCommand cmd2 = null;
1389 Console.WriteLine(" Drop procedure SP_OUTPUTPARMTEST1...");
1391 cmd2 = con.CreateCommand ();
1392 cmd2.CommandText = "DROP PROCEDURE SP_OUTPUTPARMTEST1";
1393 cmd2.ExecuteNonQuery ();
1395 catch(OracleException oe1) {
1396 // ignore if table already exists
1399 Console.WriteLine(" Create stored procedure SP_OUTPUTPARMTEST1...");
1400 // stored procedure concatenates strings
1402 "CREATE OR REPLACE PROCEDURE SP_TESTOUTPARM1(parm1 IN VARCHAR2,parm2 OUT VARCHAR2) " +
1405 " parm2 := 'one' || parm1 || 'three';" +
1408 cmd2.ExecuteNonQuery ();
1410 Console.WriteLine(" COMMIT...");
1411 cmd2.CommandText = "COMMIT";
1412 cmd2.ExecuteNonQuery ();
1414 Console.WriteLine(" Call stored procedure SP_TESTOUTPARM1 with two parameters...");
1415 OracleCommand cmd3 = con.CreateCommand ();
\r
1416 cmd3.CommandType = CommandType.Text;
\r
1419 " SP_TESTOUTPARM1(:p1, :p2);" +
\r
1421 OracleParameter myParameter1 = new OracleParameter("p1", OracleType.VarChar);
1422 myParameter1.Value = "two";
\r
1423 myParameter1.Size = 4;
\r
1424 myParameter1.Direction = ParameterDirection.Input;
\r
1426 OracleParameter myParameter2 = new OracleParameter("p2", OracleType.VarChar);
\r
1427 myParameter2.Size = 12;
\r
1428 myParameter2.Direction = ParameterDirection.Output;
\r
1430 cmd3.Parameters.Add (myParameter1);
\r
1431 cmd3.Parameters.Add (myParameter2);
\r
1433 cmd3.ExecuteNonQuery ();
1434 string outValue = (string) myParameter2.Value;
1435 Console.WriteLine (" Out Value should be: onetwothree");
1436 Console.WriteLine (" Out Value: " + outValue);
1439 static void OutParmTest2 (OracleConnection con)
1441 // test stored procedure with 2 parameters
1442 // 1. input number(18,2)
1443 // 2. output number(18,2)
1445 OracleCommand cmd2 = null;
1446 Console.WriteLine(" Drop procedure SP_OUTPUTPARMTEST2...");
1448 cmd2 = con.CreateCommand ();
1449 cmd2.CommandText = "DROP PROCEDURE SP_OUTPUTPARMTEST2";
1450 cmd2.ExecuteNonQuery ();
1452 catch(OracleException oe1) {
1453 // ignore if table already exists
1456 Console.WriteLine(" Create stored procedure SP_OUTPUTPARMTEST2...");
1458 // stored procedure addes two numbers
1460 "CREATE OR REPLACE PROCEDURE SP_TESTOUTPARM2(parm1 IN NUMBER,parm2 OUT NUMBER) " +
1463 " parm2 := parm1 + 3; " +
1466 cmd2.ExecuteNonQuery ();
1468 Console.WriteLine(" COMMIT...");
1469 cmd2.CommandText = "COMMIT";
1470 cmd2.ExecuteNonQuery ();
1472 Console.WriteLine(" Call stored procedure SP_TESTOUTPARM2 with two parameters...");
1473 OracleCommand cmd3 = con.CreateCommand ();
\r
1474 cmd3.CommandType = CommandType.Text;
\r
1477 " SP_TESTOUTPARM2(:p1, :p2);" +
\r
1479 OracleParameter myParameter1 = new OracleParameter("p1", OracleType.Number);
1480 myParameter1.Value = 2;
\r
1481 myParameter1.Direction = ParameterDirection.Input;
\r
1483 OracleParameter myParameter2 = new OracleParameter("p2", OracleType.Number);
\r
1484 myParameter2.Direction = ParameterDirection.Output;
\r
1486 cmd3.Parameters.Add (myParameter1);
\r
1487 cmd3.Parameters.Add (myParameter2);
\r
1489 cmd3.ExecuteNonQuery ();
1490 decimal outValue = (decimal) myParameter2.Value;
1491 Console.WriteLine (" Out Value should be: 5");
1492 Console.WriteLine (" Out Value: {0}", outValue);
1495 static void OutParmTest3 (OracleConnection con)
1497 // test stored procedure with 2 parameters
1501 OracleCommand cmd2 = null;
1502 Console.WriteLine(" Drop procedure SP_OUTPUTPARMTEST3...");
1504 cmd2 = con.CreateCommand ();
1505 cmd2.CommandText = "DROP PROCEDURE SP_OUTPUTPARMTEST3";
1506 cmd2.ExecuteNonQuery ();
1508 catch(OracleException oe1) {
1509 // ignore if table already exists
1512 Console.WriteLine(" Create stored procedure SP_OUTPUTPARMTEST3...");
1514 // stored procedure adds 3 days to date
1516 "CREATE OR REPLACE PROCEDURE SP_TESTOUTPARM3(parm1 IN DATE,parm2 OUT DATE) " +
1519 " parm2 := parm1 + 3; " +
1522 cmd2.ExecuteNonQuery ();
1524 Console.WriteLine(" COMMIT...");
1525 cmd2.CommandText = "COMMIT";
1526 cmd2.ExecuteNonQuery ();
1528 Console.WriteLine(" Call stored procedure SP_TESTOUTPARM3 with two parameters...");
1529 OracleCommand cmd3 = con.CreateCommand ();
\r
1530 cmd3.CommandType = CommandType.Text;
\r
1533 " SP_TESTOUTPARM3(:p1, :p2);" +
\r
1535 OracleParameter myParameter1 = new OracleParameter("p1", OracleType.DateTime);
1536 myParameter1.Value = new DateTime(2004,12,15);
\r
1537 myParameter1.Direction = ParameterDirection.Input;
\r
1539 OracleParameter myParameter2 = new OracleParameter("p2", OracleType.DateTime);
\r
1540 myParameter2.Direction = ParameterDirection.Output;
\r
1542 cmd3.Parameters.Add (myParameter1);
\r
1543 cmd3.Parameters.Add (myParameter2);
\r
1545 cmd3.ExecuteNonQuery ();
1546 DateTime outValue = (DateTime) myParameter2.Value;
1547 Console.WriteLine (" Out Value should be: 2004-12-18");
1548 Console.WriteLine (" Out Value: {0}", outValue.ToString ("yyyy-mm-dd"));
1551 static void ShowConnectionProperties (OracleConnection con)
1554 Console.WriteLine ("ServerVersion: " + con.ServerVersion);
1555 } catch (System.InvalidOperationException ioe) {
\r
1556 Console.WriteLine ("InvalidOperationException caught.");
\r
1557 Console.WriteLine ("Message: " + ioe.Message);
\r
1560 Console.WriteLine ("DataSource: " + con.DataSource);
1563 static void NullAggregateTest (OracleConnection con)
1565 Console.WriteLine(" Drop table MONO_TEST_TABLE3...");
1566 OracleCommand cmd2 = con.CreateCommand ();
1569 cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE3";
1570 cmd2.ExecuteNonQuery ();
1572 catch(OracleException oe1) {
1573 // ignore if table already exists
1576 Console.WriteLine(" Create table MONO_TEST_TABLE3...");
1578 cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE3 (" +
1579 " COL1 VARCHAR2(8), "+
1580 " COL2 VARCHAR2(32))";
1582 cmd2.ExecuteNonQuery ();
1584 Console.WriteLine(" Insert some rows into table MONO_TEST_TABLE3...");
1585 cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('1','one')";
1586 cmd2.ExecuteNonQuery ();
1588 cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('1','uno')";
1589 cmd2.ExecuteNonQuery ();
1591 cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('3','three')";
1592 cmd2.ExecuteNonQuery ();
1594 cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('3', null)";
1595 cmd2.ExecuteNonQuery ();
1597 cmd2.CommandText = "INSERT INTO MONO_TEST_TABLE3 (COL1, COL2) VALUES ('3','few')";
1598 cmd2.ExecuteNonQuery ();
1600 Console.WriteLine(" ExecuteScalar...");
1601 cmd2.CommandText = "SELECT COL1, COUNT(COL2) AS MAX_COL1 FROM MONO_TEST_TABLE3 GROUP BY COL1";
1602 OracleDataReader reader = cmd2.ExecuteReader ();
1603 Console.WriteLine (" Read...");
1604 while (reader.Read ()) {
1606 object obj0 = reader.GetValue (0);
1607 Console.WriteLine("Value 0: " + obj0.ToString ());
1608 object obj1 = reader.GetValue (1);
1609 Console.WriteLine("Value 1: " + obj1.ToString ());
1611 Console.WriteLine (" Read...");
1614 Console.WriteLine (" No more records.");
1617 static void OnInfoMessage (object sender, OracleInfoMessageEventArgs e)
\r
1619 Console.WriteLine("InfoMessage Message: " + e.Message.ToString());
\r
1620 Console.WriteLine("InfoMessage Code: " + e.Code.ToString());
\r
1621 Console.WriteLine("InfoMessage Source: " + e.Source.ToString());
\r
1624 static void OnStateChange (object sender, StateChangeEventArgs e)
\r
1626 Console.WriteLine("StateChange CurrentSate:" + e.CurrentState.ToString ());
\r
1627 Console.WriteLine("StateChange OriginalState:" + e.OriginalState.ToString ());
\r
1630 static void RefCursorTests(OracleConnection con)
\r
1632 SetupRefCursorTests(con); // for ref cursor tests 1 thru 3
\r
1633 RefCursorTest1(con); // using BEGIN/END
\r
1634 RefCursorTest2(con); // using call
\r
1635 RefCursorTest3(con); // using CommandType.StoredProcedure
\r
1637 RefCursorTest4(con);
\r
1640 static void SetupRefCursorTests(OracleConnection con)
\r
1642 Console.WriteLine("Setup Oracle package curspkg_join...");
\r
1644 OracleCommand cmd = con.CreateCommand();
\r
1646 cmd.CommandText =
\r
1647 "CREATE OR REPLACE PACKAGE curspkg_join AS\n" +
\r
1648 "TYPE t_cursor IS REF CURSOR;\n" +
\r
1649 "Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor);\n" +
\r
1650 "END curspkg_join;";
\r
1652 cmd.CommandText =
\r
1653 "CREATE OR REPLACE PACKAGE BODY curspkg_join AS\n" +
\r
1654 " Procedure open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor)\n" +
\r
1656 " v_cursor t_cursor;\n" +
\r
1658 " IF n_EMPNO <> 0 THEN\n" +
\r
1659 " OPEN v_cursor FOR\n" +
\r
1660 " SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME\n" +
\r
1661 " FROM EMP, DEPT\n" +
\r
1662 " WHERE EMP.DEPTNO = DEPT.DEPTNO\n" +
\r
1663 " AND EMP.EMPNO = n_EMPNO;\n" +
\r
1666 " OPEN v_cursor FOR\n" +
\r
1667 " SELECT EMP.EMPNO, EMP.ENAME, DEPT.DEPTNO, DEPT.DNAME\n" +
\r
1668 " FROM EMP, DEPT\n" +
\r
1669 " WHERE EMP.DEPTNO = DEPT.DEPTNO;\n" +
\r
1672 " io_cursor := v_cursor;\n" +
\r
1673 " END open_join_cursor1;\n" +
\r
1674 "END curspkg_join;";
\r
1675 cmd.ExecuteNonQuery();
\r
1678 public static void RefCursorTest4(OracleConnection connection)
\r
1680 Console.WriteLine("Setup test package and data...");
\r
1681 OracleCommand cmddrop = connection.CreateCommand();
\r
1683 cmddrop.CommandText = "DROP TABLE TESTTABLE";
\r
1685 cmddrop.ExecuteNonQuery();
\r
1687 catch(OracleException e) {
\r
1688 Console.WriteLine("Ignore this error: " + e.Message);
\r
1690 cmddrop.Dispose();
\r
1693 OracleCommand cmd = connection.CreateCommand();
\r
1695 // create table TESTTABLE
\r
1696 cmd.CommandText =
\r
1697 "create table TESTTABLE (\n" +
\r
1698 " col1 numeric(18,0),\n" +
\r
1699 " col2 varchar(32),\n" +
\r
1701 cmd.ExecuteNonQuery();
\r
1703 // insert some rows into TESTTABLE
\r
1704 cmd.CommandText =
\r
1705 "insert into TESTTABLE\n" +
\r
1706 "(col1, col2, col3)\n" +
\r
1707 "values(45, 'Mono', sysdate)";
\r
1708 cmd.ExecuteNonQuery();
\r
1710 cmd.CommandText =
\r
1711 "insert into TESTTABLE\n" +
\r
1712 "(col1, col2, col3)\n" +
\r
1713 "values(136, 'Fun', sysdate)";
\r
1714 cmd.ExecuteNonQuery();
\r
1716 cmd.CommandText =
\r
1717 "insert into TESTTABLE\n" +
\r
1718 "(col1, col2, col3)\n" +
\r
1719 "values(526, 'System.Data.OracleClient', sysdate)";
\r
1720 cmd.ExecuteNonQuery();
\r
1722 cmd.CommandText = "commit";
\r
1723 cmd.ExecuteNonQuery();
\r
1725 // create Oracle package TestTablePkg
\r
1726 cmd.CommandText =
\r
1727 "CREATE OR REPLACE PACKAGE TestTablePkg\n" +
\r
1729 " TYPE T_CURSOR IS REF CURSOR;\n" +
\r
1731 " PROCEDURE GetData(tableCursor OUT T_CURSOR);\n" +
\r
1732 "END TestTablePkg;";
\r
1733 cmd.ExecuteNonQuery();
\r
1735 // create Oracle package body for package TestTablePkg
\r
1736 cmd.CommandText =
\r
1737 "CREATE OR REPLACE PACKAGE BODY TestTablePkg AS\n" +
\r
1738 " PROCEDURE GetData(tableCursor OUT T_CURSOR)\n" +
\r
1741 " OPEN tableCursor FOR\n" +
\r
1743 " FROM TestTable;\n" +
\r
1744 " END GetData;\n" +
\r
1745 "END TestTablePkg;";
\r
1746 cmd.ExecuteNonQuery();
\r
1751 Console.WriteLine("Set up command and parameters to call stored proc...");
\r
1752 OracleCommand command = new OracleCommand("TestTablePkg.GetData", connection);
\r
1753 command.CommandType = CommandType.StoredProcedure;
\r
1754 OracleParameter parameter = new OracleParameter("tableCursor", OracleType.Cursor);
\r
1755 parameter.Direction = ParameterDirection.Output;
\r
1756 command.Parameters.Add(parameter);
\r
1758 Console.WriteLine("Execute...");
\r
1759 command.ExecuteNonQuery();
\r
1761 Console.WriteLine("Get OracleDataReader for cursor output parameter...");
\r
1762 OracleDataReader reader = (OracleDataReader) parameter.Value;
\r
1764 Console.WriteLine("Read data...");
\r
1766 while (reader.Read()) {
\r
1767 Console.WriteLine("Row {0}", r);
\r
1768 for (int f = 0; f < reader.FieldCount; f ++) {
\r
1769 object val = reader.GetValue(f);
\r
1770 Console.WriteLine(" Field {0} Value: {1}", f, val);
\r
1774 Console.WriteLine("Rows retrieved: {0}", r);
\r
1776 Console.WriteLine("Clean up...");
\r
1779 command.Dispose();
\r
1783 static void RefCursorTest1(OracleConnection con)
\r
1785 Console.WriteLine("Ref Cursor Test 1 - using BEGIN/END for proc - Begin...");
\r
1787 Console.WriteLine("Create command...");
\r
1788 OracleCommand cmd = new OracleCommand();
\r
1789 cmd.Connection = con;
\r
1791 cmd.CommandText =
\r
1793 " curspkg_join.open_join_cursor1(:n_Empno,:io_cursor);\n" +
\r
1796 // PL/SQL definition of stored procedure in package curspkg_join
\r
1797 // open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor)
\r
1799 Console.WriteLine("Create parameters...");
\r
1801 OracleParameter parm1 = new OracleParameter("n_Empno", OracleType.Number);
\r
1802 parm1.Direction = ParameterDirection.Input;
\r
1803 parm1.Value = 7902;
\r
1805 OracleParameter parm2 = new OracleParameter("io_cursor", OracleType.Cursor);
\r
1806 parm2.Direction = ParameterDirection.Output;
\r
1808 cmd.Parameters.Add(parm1);
\r
1809 cmd.Parameters.Add(parm2);
\r
1811 // positional parm
\r
1812 //cmd.Parameters.Add(new OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output;
\r
1814 //cmd.Parameters.Add("n_Empno", OracleType.Number, 4).Value = 7902;
\r
1816 OracleDataReader reader;
\r
1817 Console.WriteLine("Execute Non Query...");
\r
1818 cmd.ExecuteNonQuery();
\r
1820 Console.WriteLine("Get data reader (ref cursor) from out parameter...");
\r
1821 reader = (OracleDataReader) cmd.Parameters["io_cursor"].Value;
\r
1826 Console.WriteLine("Get data from ref cursor...");
\r
1827 while (reader.Read()) {
\r
1828 for (x = 0; x < reader.FieldCount; x++)
\r
1829 Console.Write(reader[x] + " ");
\r
1831 Console.WriteLine();
\r
1834 Console.WriteLine(count.ToString() + " Rows Returned.");
\r
1839 static void RefCursorTest2(OracleConnection con)
\r
1841 Console.WriteLine("Ref Cursor Test 2 - using call - Begin...");
\r
1843 Console.WriteLine("Create command...");
\r
1844 OracleCommand cmd = new OracleCommand();
\r
1845 cmd.Connection = con;
\r
1846 cmd.CommandText = "call curspkg_join.open_join_cursor1(:n_Empno,:io_cursor)";
\r
1848 // PL/SQL definition of stored procedure in package curspkg_join
\r
1849 // open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor)
\r
1851 Console.WriteLine("Create parameters...");
\r
1853 OracleParameter parm1 = new OracleParameter("n_Empno", OracleType.Number);
\r
1854 parm1.Direction = ParameterDirection.Input;
\r
1855 parm1.Value = 7902;
\r
1857 OracleParameter parm2 = new OracleParameter("io_cursor", OracleType.Cursor);
\r
1858 parm2.Direction = ParameterDirection.Output;
\r
1860 cmd.Parameters.Add(parm1);
\r
1861 cmd.Parameters.Add(parm2);
\r
1863 // positional parm
\r
1864 //cmd.Parameters.Add(new OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output;
\r
1866 //cmd.Parameters.Add("n_Empno", OracleType.Number, 4).Value = 7902;
\r
1868 OracleDataReader reader;
\r
1869 Console.WriteLine("Execute Non Query...");
\r
1870 cmd.ExecuteNonQuery();
\r
1872 Console.WriteLine("Get data reader (ref cursor) from out parameter...");
\r
1873 reader = (OracleDataReader) cmd.Parameters["io_cursor"].Value;
\r
1878 Console.WriteLine("Get data from ref cursor...");
\r
1879 while (reader.Read()) {
\r
1880 for (x = 0; x < reader.FieldCount; x++)
\r
1881 Console.Write(reader[x] + " ");
\r
1883 Console.WriteLine();
\r
1886 Console.WriteLine(count.ToString() + " Rows Returned.");
\r
1891 static void RefCursorTest3(OracleConnection con)
\r
1893 Console.WriteLine("Ref Cursor Test 3 - CommandType.StoredProcedure - Begin...");
\r
1895 Console.WriteLine("Create command...");
\r
1896 OracleCommand cmd = new OracleCommand();
\r
1897 cmd.Connection = con;
\r
1898 cmd.CommandText = "curspkg_join.open_join_cursor1";
\r
1899 cmd.CommandType = CommandType.StoredProcedure;
\r
1901 // PL/SQL definition of stored procedure in package curspkg_join
\r
1902 // open_join_cursor1 (n_EMPNO IN NUMBER, io_cursor IN OUT t_cursor)
\r
1904 Console.WriteLine("Create parameters...");
\r
1906 OracleParameter parm1 = new OracleParameter("n_Empno", OracleType.Number);
\r
1907 parm1.Direction = ParameterDirection.Input;
\r
1908 parm1.Value = 7902;
\r
1910 OracleParameter parm2 = new OracleParameter("io_cursor", OracleType.Cursor);
\r
1911 parm2.Direction = ParameterDirection.Output;
\r
1913 cmd.Parameters.Add(parm1);
\r
1914 cmd.Parameters.Add(parm2);
\r
1916 // positional parm
\r
1917 //cmd.Parameters.Add(new OracleParameter("io_cursor", OracleType.Cursor)).Direction = ParameterDirection.Output;
\r
1919 //cmd.Parameters.Add("n_Empno", OracleType.Number, 4).Value = 7902;
\r
1921 OracleDataReader reader;
\r
1922 Console.WriteLine("Execute Non Query...");
\r
1923 cmd.ExecuteNonQuery();
\r
1925 Console.WriteLine("Get data reader (ref cursor) from out parameter...");
\r
1926 reader = (OracleDataReader) cmd.Parameters["io_cursor"].Value;
\r
1931 Console.WriteLine("Get data from ref cursor...");
\r
1932 while (reader.Read()) {
\r
1933 for (x = 0; x < reader.FieldCount; x++)
\r
1934 Console.Write(reader[x] + " ");
\r
1936 Console.WriteLine();
\r
1939 Console.WriteLine(count.ToString() + " Rows Returned.");
\r
1944 static void ExternalAuthenticationTest ()
\r
1946 string user = Environment.UserName;
\r
1947 if (!Environment.UserDomainName.Equals(String.Empty))
\r
1948 user = Environment.UserDomainName + "\\" + Environment.UserName;
\r
1949 Console.WriteLine("Environment UserDomainName and UserName: " + user);
\r
1950 Console.WriteLine("Open connection using external authentication...");
\r
1951 OracleConnection con = new OracleConnection("Data Source=palis;Integrated Security=true");
\r
1954 OracleCommand cmd = con.CreateCommand();
\r
1955 cmd.CommandText = "SELECT USER FROM DUAL";
\r
1956 OracleDataReader reader = cmd.ExecuteReader();
\r
1957 if (reader.Read())
\r
1958 Console.WriteLine("User: " + reader.GetString(reader.GetOrdinal("USER")));
\r
1961 catch (Exception e) {
\r
1962 Console.WriteLine("Exception caught: " + e.Message);
\r
1963 Console.WriteLine("Probably not setup for external authentication.");
\r
1969 public static void TestPersistSucurityInfo1()
\r
1971 Console.WriteLine("\nTestPersistSucurityInfo1 - persist security info=false");
\r
1972 OracleConnection con = new OracleConnection("data source=palis;user id=scott;password=tiger;persist security info=false");
\r
1973 Console.WriteLine("ConnectionString before open: " + con.ConnectionString);
\r
1975 Console.WriteLine("ConnectionString after open: " + con.ConnectionString);
\r
1977 Console.WriteLine("ConnectionString after close: " + con.ConnectionString);
\r
1981 public static void TestPersistSucurityInfo2()
\r
1983 Console.WriteLine("\nTestPersistSucurityInfo2 - persist security info=true");
\r
1984 OracleConnection con = new OracleConnection("data source=palis;user id=scott;password=tiger;persist security info=true");
\r
1985 Console.WriteLine("ConnectionString before open: " + con.ConnectionString);
\r
1987 Console.WriteLine("ConnectionString after open: " + con.ConnectionString);
\r
1989 Console.WriteLine("ConnectionString after close: " + con.ConnectionString);
\r
1993 public static void TestPersistSucurityInfo3()
\r
1995 Console.WriteLine("\nTestPersistSucurityInfo3 - use default for persist security info which is false");
\r
1996 OracleConnection con = new OracleConnection("data source=palis;user id=scott;password=tiger");
\r
1997 Console.WriteLine("ConnectionString before open: " + con.ConnectionString);
\r
1999 Console.WriteLine("ConnectionString after open: " + con.ConnectionString);
\r
2001 Console.WriteLine("ConnectionString after close: " + con.ConnectionString);
\r
2005 public static void TestPersistSucurityInfo4()
\r
2007 Console.WriteLine("\nTestPersistSucurityInfo4 - persist security info=false with password at front");
\r
2008 OracleConnection con = new OracleConnection(";password=tiger;data source=palis;user id=scott;persist security info=false");
\r
2009 Console.WriteLine("ConnectionString before open: " + con.ConnectionString);
\r
2011 Console.WriteLine("ConnectionString after open: " + con.ConnectionString);
\r
2013 Console.WriteLine("ConnectionString after close: " + con.ConnectionString);
\r
2017 public static void TestPersistSucurityInfo5()
\r
2019 Console.WriteLine("\nTestPersistSucurityInfo5 - persist security info=false");
\r
2020 OracleConnection con = new OracleConnection("data source=palis;user id=scott;password=tiger;persist security info=false");
\r
2021 Console.WriteLine("ConnectionString before open: " + con.ConnectionString);
\r
2023 Console.WriteLine("ConnectionString after open: " + con.ConnectionString);
\r
2024 Console.WriteLine("ConnectionState for con: " + con.State.ToString() + "\n");
\r
2026 Console.WriteLine("Clone OracleConnection...");
\r
2027 OracleConnection con2 = (OracleConnection) ((ICloneable) con).Clone();
\r
2029 Console.WriteLine("ConnectionState for con2: " + con2.State.ToString());
\r
2030 Console.WriteLine("con2 ConnectionString before open: " + con2.ConnectionString);
\r
2032 Console.WriteLine("con2 ConnectionString after open: " + con2.ConnectionString);
\r
2034 Console.WriteLine("con2 ConnectionString after close: " + con2.ConnectionString);
\r
2039 public static void TestPersistSucurityInfo6()
\r
2041 Console.WriteLine("\nTestPersistSucurityInfo6 - external auth using persist security info");
\r
2043 string user = Environment.UserName;
\r
2044 if (!Environment.UserDomainName.Equals(String.Empty))
\r
2045 user = Environment.UserDomainName + "\\" + Environment.UserName;
\r
2046 Console.WriteLine("Environment UserDomainName and UserName: " + user);
\r
2047 Console.WriteLine("Open connection using external authentication...");
\r
2048 OracleConnection con = new OracleConnection("Data Source=palis;Integrated Security=true");
\r
2049 Console.WriteLine("ConnectionString before open: " + con.ConnectionString);
\r
2052 OracleCommand cmd = con.CreateCommand();
\r
2053 cmd.CommandText = "SELECT USER FROM DUAL";
\r
2054 OracleDataReader reader = cmd.ExecuteReader();
\r
2055 if (reader.Read())
\r
2056 Console.WriteLine("User: " + reader.GetString(reader.GetOrdinal("USER")));
\r
2058 Console.WriteLine("ConnectionString after close: " + con.ConnectionString);
\r
2060 catch (Exception e) {
\r
2061 Console.WriteLine("Exception caught: " + e.Message);
\r
2062 Console.WriteLine("Probably not setup for external authentication. This is fine.");
\r
2065 Console.WriteLine("ConnectionString after dispose: " + con.ConnectionString);
\r
2067 Console.WriteLine("\n\n");
\r
2070 public static void ConnectionPoolingTest1 ()
\r
2072 Console.WriteLine("Start Connection Pooling Test 1...");
\r
2073 OracleConnection[] connections = null;
\r
2074 int maxCon = MAX_CONNECTIONS + 1; // add 1 more over the max connections to cause it to wait for the next available connection
\r
2078 connections = new OracleConnection[maxCon];
\r
2080 for (i = 0; i < maxCon; i++) {
\r
2081 Console.WriteLine(" Open connection: {0}", i);
\r
2082 connections[i] = new OracleConnection(conStr);
\r
2083 connections[i].Open ();
\r
2085 } catch (InvalidOperationException e) {
\r
2086 Console.WriteLine("Expected exception InvalidOperationException caught.");
\r
2087 Console.WriteLine(e);
\r
2090 for (i = 0; i < maxCon; i++) {
\r
2091 if (connections[i] != null) {
\r
2092 Console.WriteLine(" Close connection: {0}", i);
\r
2093 if (connections[i].State == ConnectionState.Open)
\r
2094 connections[i].Close ();
\r
2095 connections[i].Dispose ();
\r
2096 connections[i] = null;
\r
2100 connections = null;
\r
2102 Console.WriteLine("Done Connection Pooling Test 1.");
\r
2105 public static void ConnectionPoolingTest2 ()
\r
2107 Console.WriteLine("Start Connection Pooling Test 2...");
\r
2108 OracleConnection[] connections = null;
\r
2109 int maxCon = MAX_CONNECTIONS;
\r
2112 connections = new OracleConnection[maxCon];
\r
2114 for (i = 0; i < maxCon; i++) {
\r
2115 Console.WriteLine(" Open connection: {0}", i);
\r
2116 connections[i] = new OracleConnection(conStr);
\r
2117 connections[i].Open ();
\r
2120 Console.WriteLine("Start another thread...");
\r
2121 t = new Thread(new ThreadStart(AnotherThreadProc));
\r
2124 Console.WriteLine("Sleep...");
\r
2125 Thread.Sleep(100);
\r
2127 Console.WriteLine("Closing...");
\r
2128 for (i = 0; i < maxCon; i++) {
\r
2129 if (connections[i] != null) {
\r
2130 Console.WriteLine(" Close connection: {0}", i);
\r
2131 if (connections[i].State == ConnectionState.Open)
\r
2132 connections[i].Close ();
\r
2133 connections[i].Dispose ();
\r
2134 connections[i] = null;
\r
2138 connections = null;
\r
2141 private static void AnotherThreadProc ()
\r
2143 Console.WriteLine("Open connection via another thread...");
\r
2144 OracleConnection[] connections = null;
\r
2145 int maxCon = MAX_CONNECTIONS;
\r
2148 connections = new OracleConnection[maxCon];
\r
2150 for (i = 0; i < maxCon; i++) {
\r
2151 Console.WriteLine(" Open connection: {0}", i);
\r
2152 connections[i] = new OracleConnection(conStr);
\r
2153 connections[i].Open ();
\r
2156 Console.WriteLine("Done Connection Pooling Test 2.");
\r
2157 System.Environment.Exit (0);
\r
2161 static void Main(string[] args)
2163 if(args.Length != 3) {
2164 Console.WriteLine("Usage: mono TestOracleClient database userid password");
2168 string connectionString = String.Format(
2169 "Data Source={0};" +
2172 args[0], args[1], args[2]);
2174 conStr = connectionString;
2176 OracleConnection con1 = new OracleConnection();
2178 ShowConnectionProperties (con1);
2180 con1.ConnectionString = connectionString;
2182 con1.InfoMessage += new OracleInfoMessageEventHandler (OnInfoMessage);
2183 con1.StateChange += new StateChangeEventHandler (OnStateChange);
2184 Console.WriteLine("Opening...");
2186 Console.WriteLine("Opened.");
2188 ShowConnectionProperties (con1);
2190 Console.WriteLine ("Mono Oracle Test BEGIN ...");
2192 Console.WriteLine ("Mono Oracle Test END ...");
2196 Console.WriteLine ("LOB Test BEGIN...");
2199 Console.WriteLine ("LOB Test END.");
2202 Console.WriteLine ("Read Simple Test BEGIN - scott.emp...");
2203 ReadSimpleTest(con1, "SELECT e.*, e.rowid FROM scott.emp e");
2204 Console.WriteLine ("Read Simple Test END - scott.emp");
2208 Console.WriteLine ("DataAdapter Test BEGIN...");
2209 DataAdapterTest(con1);
2210 Console.WriteLine ("DataAdapter Test END.");
2214 Console.WriteLine ("DataAdapter Test 2 BEGIN...");
2215 DataAdapterTest2(con1);
2216 Console.WriteLine ("DataAdapter Test 2 END.");
2220 Console.WriteLine ("Rollback Test BEGIN...");
2222 Console.WriteLine ("Rollback Test END.");
2226 Console.WriteLine ("Commit Test BEGIN...");
2228 Console.WriteLine ("Commit Test END.");
2232 Console.WriteLine ("Parameter Test BEGIN...");
2233 ParameterTest(con1);
2234 ReadSimpleTest(con1, "SELECT * FROM MONO_TEST_TABLE7");
2235 Console.WriteLine ("Parameter Test END.");
2239 Console.WriteLine ("Stored Proc Test 1 BEGIN...");
2240 StoredProcedureTest1 (con1);
2241 ReadSimpleTest(con1, "SELECT * FROM MONO_TEST_TABLE1");
2242 Console.WriteLine ("Stored Proc Test 1 END...");
2246 Console.WriteLine ("Stored Proc Test 2 BEGIN...");
2247 StoredProcedureTest2 (con1);
2248 ReadSimpleTest(con1, "SELECT * FROM MONO_TEST_TABLE2");
2249 Console.WriteLine ("Stored Proc Test 2 END...");
2251 Console.WriteLine ("Out Parameter and PL/SQL Block Test 1 BEGIN...");
2252 OutParmTest1 (con1);
2253 Console.WriteLine ("Out Parameter and PL/SQL Block Test 1 END...");
2255 Console.WriteLine ("Out Parameter and PL/SQL Block Test 2 BEGIN...");
2256 OutParmTest2 (con1);
2257 Console.WriteLine ("Out Parameter and PL/SQL Block Test 2 END...");
2259 Console.WriteLine ("Out Parameter and PL/SQL Block Test 3 BEGIN...");
2260 OutParmTest3 (con1);
2261 Console.WriteLine ("Out Parameter and PL/SQL Block Test 3 END...");
2265 Console.WriteLine ("Test a Non Query using Execute Reader BEGIN...");
2266 TestNonQueryUsingExecuteReader (con1);
2267 Console.WriteLine ("Test a Non Query using Execute Reader END...");
2271 Console.WriteLine ("Null Aggregate Warning BEGIN test...");
2272 NullAggregateTest (con1);
2273 Console.WriteLine ("Null Aggregate Warning END test...");
2275 Console.WriteLine ("Ref Cursor BEGIN tests...");
2276 RefCursorTests (con1);
2277 Console.WriteLine ("Ref Cursor END tests...");
2279 Console.WriteLine("Closing...");
2281 Console.WriteLine("Closed.");
2283 ExternalAuthenticationTest();
2285 TestPersistSucurityInfo1();
\r
2286 TestPersistSucurityInfo2();
\r
2287 TestPersistSucurityInfo3();
\r
2288 TestPersistSucurityInfo4();
\r
2289 TestPersistSucurityInfo5();
\r
2290 TestPersistSucurityInfo6();
\r
2292 //conStr = conStr + ";pooling=true;min pool size=4;max pool size=" + MAX_CONNECTIONS.ToString ();
\r
2293 //ConnectionPoolingTest1 ();
\r
2294 //ConnectionPoolingTest2 ();
\r
2296 Console.WriteLine("Done.");