sOraDataType = "DBNull.Value";
}
else {
- ovalue = reader.GetValue (f);
- //ovalue = reader.GetOracleValue (f);
+ //ovalue = reader.GetValue (f);
+ ovalue = reader.GetOracleValue (f);
object oravalue = null;
sDataType = ovalue.GetType ().ToString ();
oravalue = GetHexString((byte[])ovalue);
break;
case "System.Decimal":
- Console.WriteLine(" *** Get Decimal, Int16, Int32, Int64, Float, Double, ...");
+ //Console.WriteLine(" *** Get Decimal, Int16, Int32, Int64, Float, Double, ...");
decimal dec = reader.GetDecimal (f);
- Console.WriteLine(" GetDecimal: " + dec.ToString ());
+ //Console.WriteLine(" GetDecimal: " + dec.ToString ());
oravalue = (object) dec;
-
+/*
try {
reader.GetInt16 (f);
} catch (NotSupportedException e) {
} catch (OverflowException oe1) {
Console.WriteLine (" ** Overflow exception for numbers to big or too small: " + oe1.Message);
}
-
+ */
break;
default:
oravalue = ovalue.ToString ();
}
}
+ public static void DataAdapterTest2 (OracleConnection con) \r
+ {\r
+ DataAdapterTest2_Setup (con);\r
+ ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");\r
+ \r
+ GetMetaData (con, "SELECT * FROM mono_adapter_test");\r
+\r
+ DataAdapterTest2_Insert (con);\r
+ ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");\r
+ \r
+ DataAdapterTest2_Update (con);\r
+ ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");\r
+\r
+ DataAdapterTest2_Delete (con);\r
+ ReadSimpleTest (con, "SELECT * FROM mono_adapter_test");\r
+ }\r
+\r
+ public static void GetMetaData (OracleConnection con, string sql) \r
+ {\r
+ OracleCommand cmd = null;
+ OracleDataReader rdr = null;
+
+ cmd = con.CreateCommand();
+ cmd.CommandText = sql;
+
+ Console.WriteLine("Read Schema With KeyInfo");
+ rdr = cmd.ExecuteReader(CommandBehavior.KeyInfo | CommandBehavior.SchemaOnly);
+ \r
+ DataTable dt;\r
+ dt = rdr.GetSchemaTable();\r
+ foreach (DataRow schemaRow in dt.Rows) {\r
+ foreach (DataColumn schemaCol in dt.Columns) {\r
+ Console.WriteLine(schemaCol.ColumnName + \r
+ " = " + \r
+ schemaRow[schemaCol]);\r
+ Console.WriteLine("---Type: " + schemaRow[schemaCol].GetType ().ToString());\r
+ }\r
+ Console.WriteLine("");\r
+ }\r
+\r
+ Console.WriteLine("Read Schema with No KeyInfo");\r
+\r
+ rdr = cmd.ExecuteReader();
+\r
+ dt = rdr.GetSchemaTable();\r
+ foreach (DataRow schemaRow in dt.Rows) {\r
+ foreach (DataColumn schemaCol in dt.Columns) {\r
+ Console.WriteLine(schemaCol.ColumnName + \r
+ " = " + \r
+ schemaRow[schemaCol]);\r
+ Console.WriteLine("---Type: " + schemaRow[schemaCol].GetType ().ToString());\r
+ Console.WriteLine();\r
+ }\r
+ }\r
+\r
+ }\r
+\r
+ public static void DataAdapterTest2_Setup (OracleConnection con) \r
+ {\r
+ Console.WriteLine (" Drop table mono_adapter_test ...");
+ try {
+ OracleCommand cmd2 = con.CreateCommand ();
+ cmd2.CommandText = "DROP TABLE mono_adapter_test";
+ cmd2.ExecuteNonQuery ();
+ }
+ catch (OracleException oe1) {
+ // ignore if table already exists
+ }
+
+ OracleCommand cmd = null;
+ int rowsAffected = 0;
+
+ Console.WriteLine(" Creating table mono_adapter_test...");
+ cmd = new OracleCommand ();
+ cmd.Connection = con;
+ cmd.CommandText = "CREATE TABLE mono_adapter_test ( " +
+ " varchar2_value VarChar2(32), " +
+ " number_whole_value Number(18) PRIMARY KEY, " +
+ " number_scaled_value Number(18,2), " +
+ " number_integer_value Integer, " +
+ " float_value Float, " +
+ " date_value Date, " +
+ " clob_value Clob, " +
+ " blob_value Blob ) ";
+
+ // FIXME: char_value does not work
+ /*
+ cmd.CommandText = "CREATE TABLE mono_adapter_test ( " +
+ " varchar2_value VarChar2(32), " +
+ " number_whole_value Number(18) PRIMARY KEY, " +
+ " number_scaled_value Number(18,2), " +
+ " number_integer_value Integer, " +
+ " float_value Float, " +
+ " date_value Date, " +
+ " char_value Char(32), " +
+ " clob_value Clob, " +
+ " blob_value Blob ) ";
+ */
+
+ rowsAffected = cmd.ExecuteNonQuery();
+
+ Console.WriteLine(" Begin Trans for table mono_adapter_test...");
+ OracleTransaction trans = con.BeginTransaction ();
+
+ Console.WriteLine(" Inserting value into mono_adapter_test...");
+ cmd = new OracleCommand();
+ cmd.Connection = con;
+ cmd.Transaction = trans;
+
+ cmd.CommandText = "INSERT INTO mono_adapter_test " +
+ " ( varchar2_value, " +
+ " number_whole_value, " +
+ " number_scaled_value, " +
+ " number_integer_value, " +
+ " float_value, " +
+ " date_value, " +
+ " clob_value, " +
+ " blob_value " +
+ ") " +
+ " VALUES( " +
+ " 'Mono', " +
+ " 11, " +
+ " 456.78, " +
+ " 8765, " +
+ " 235.2, " +
+ " TO_DATE( '2004-12-31', 'YYYY-MM-DD' ), " +
+ " EMPTY_CLOB(), " +
+ " EMPTY_BLOB() " +
+ ")";
+
+ /*
+ cmd.CommandText = "INSERT INTO mono_adapter_test " +
+ " ( varchar2_value, " +
+ " number_whole_value, " +
+ " number_scaled_value, " +
+ " number_integer_value, " +
+ " float_value, " +
+ " date_value, " +
+ " char_value, " +
+ " clob_value, " +
+ " blob_value " +
+ ") " +
+ " VALUES( " +
+ " 'Mono', " +
+ " 11, " +
+ " 456.78, " +
+ " 8765, " +
+ " 235.2, " +
+ " TO_DATE( '2004-12-31', 'YYYY-MM-DD' ), " +
+ " 'US', " +
+ " EMPTY_CLOB(), " +
+ " EMPTY_BLOB() " +
+ ")";
+ */
+ rowsAffected = cmd.ExecuteNonQuery();\r
+\r
+ Console.WriteLine(" Select/Update CLOB columns on table mono_adapter_test...");
+
+ // update BLOB and CLOB columns
+ OracleCommand select = con.CreateCommand ();
+ select.Transaction = trans;
+ select.CommandText = "SELECT CLOB_VALUE, BLOB_VALUE FROM mono_adapter_test FOR UPDATE";
+ OracleDataReader reader = select.ExecuteReader ();
+ if (!reader.Read ())
+ Console.WriteLine ("ERROR: RECORD NOT FOUND");
+
+ // update clob_value
+ Console.WriteLine(" Update CLOB column on table mono_adapter_test...");
+ OracleLob clob = reader.GetOracleLob (0);
+ byte[] bytes = null;
+ UnicodeEncoding encoding = new UnicodeEncoding ();
+ bytes = encoding.GetBytes ("Mono is fun!");
+ clob.Write (bytes, 0, bytes.Length);
+ clob.Close ();
+
+ // update blob_value
+ Console.WriteLine(" Update BLOB column on table mono_adapter_test...");
+ OracleLob blob = reader.GetOracleLob (1);
+ bytes = new byte[6] { 0x31, 0x32, 0x33, 0x34, 0x35, 0x036 };
+ blob.Write (bytes, 0, bytes.Length);
+ blob.Close ();
+
+ Console.WriteLine(" Commit trans for table mono_adapter_test...");
+ trans.Commit ();
+
+ CommitCursor (con);\r
+ }\r
+\r
+ public static void DataAdapterTest2_Insert (OracleConnection con)
+ {
+ Console.WriteLine("================================");\r
+ Console.WriteLine("=== Adapter Insert =============");
+ Console.WriteLine("================================");\r
+ OracleTransaction transaction = con.BeginTransaction ();
+ \r
+ Console.WriteLine(" Create adapter...");\r
+ OracleDataAdapter da = new OracleDataAdapter("select * from mono_adapter_test", con);
+ da.SelectCommand.Transaction = transaction;
+ \r
+ Console.WriteLine(" Create command builder...");\r
+ OracleCommandBuilder mycb = new OracleCommandBuilder(da);
+\r
+ Console.WriteLine(" Create data set ...");\r
+ DataSet ds = new DataSet();\r
+
+ Console.WriteLine(" Fill data set via adapter...");\r
+ da.Fill(ds, "mono_adapter_test");\r
+\r
+ Console.WriteLine(" New Row...");
+ DataRow myRow;\r
+ myRow = ds.Tables["mono_adapter_test"].NewRow();
+\r
+ byte[] bytes = new byte[] { 0x45,0x46,0x47,0x48,0x49,0x50 };\r
+\r
+ Console.WriteLine(" Set values in the new DataRow...");\r
+ myRow["varchar2_value"] = "OracleClient";\r
+ myRow["number_whole_value"] = 22;\r
+ myRow["number_scaled_value"] = 12.34;\r
+ myRow["number_integer_value"] = 456;\r
+ myRow["float_value"] = 98.76;\r
+ myRow["date_value"] = new DateTime(2001,07,09);\r
+ Console.WriteLine(" *** FIXME; char value not working");\r
+ //myRow["char_value"] = "Romeo";\r
+ myRow["clob_value"] = "clobtest";\r
+ myRow["blob_value"] = bytes;\r
+ \r
+ Console.WriteLine(" Add DataRow to DataTable..."); \r
+ ds.Tables["mono_adapter_test"].Rows.Add(myRow);
+\r
+ Console.WriteLine("da.Update(ds...");\r
+ da.Update(ds, "mono_adapter_test");\r
+\r
+ transaction.Commit();
+ }
+\r
+ public static void DataAdapterTest2_Update (OracleConnection con)
+ {
+ Console.WriteLine("================================");\r
+ Console.WriteLine("=== Adapter Update =============");
+ Console.WriteLine("================================");\r
+\r
+ OracleTransaction transaction = con.BeginTransaction ();
+\r
+ Console.WriteLine(" Create adapter...");\r
+ OracleCommand selectCmd = con.CreateCommand ();
+ selectCmd.Transaction = transaction;\r
+ selectCmd.CommandText = "SELECT * FROM mono_adapter_test";\r
+ OracleDataAdapter da = new OracleDataAdapter(selectCmd);\r
+ Console.WriteLine(" Create command builder...");\r
+ OracleCommandBuilder mycb = new OracleCommandBuilder(da);\r
+ Console.WriteLine(" Create data set ...");\r
+ DataSet ds = new DataSet();\r
+\r
+ Console.WriteLine(" Set missing schema action...");\r
+ \r
+ Console.WriteLine(" Fill data set via adapter...");\r
+ da.Fill(ds, "mono_adapter_test");\r
+ DataRow myRow;\r
+\r
+ Console.WriteLine(" New Row...");
+ myRow = ds.Tables["mono_adapter_test"].Rows[0];
+
+ Console.WriteLine("Tables Count: " + ds.Tables.Count.ToString());
+
+ DataTable table = ds.Tables["mono_adapter_test"];
+ DataRowCollection rows;
+ rows = table.Rows;
+ Console.WriteLine(" Row Count: " + rows.Count.ToString());
+ myRow = rows[0];\r
+\r
+ byte[] bytes = new byte[] { 0x62,0x63,0x64,0x65,0x66,0x67 };\r
+\r
+ Console.WriteLine(" Set values in the new DataRow...");\r
+
+ myRow["varchar2_value"] = "Super Power!";\r
+ \r
+ myRow["number_scaled_value"] = 12.35;\r
+ myRow["number_integer_value"] = 457;\r
+ myRow["float_value"] = 198.76;\r
+ myRow["date_value"] = new DateTime(2002,08,09);\r
+ //myRow["char_value"] = "Juliet";\r
+ myRow["clob_value"] = "this is a clob";\r
+ myRow["blob_value"] = bytes;
+\r
+ Console.WriteLine("da.Update(ds...");\r
+ da.Update(ds, "mono_adapter_test");\r
+\r
+ transaction.Commit();
+ }\r
+\r
+ public static void DataAdapterTest2_Delete (OracleConnection con)
+ {
+ Console.WriteLine("================================");\r
+ Console.WriteLine("=== Adapter Delete =============");
+ Console.WriteLine("================================");\r
+ OracleTransaction transaction = con.BeginTransaction ();
+ \r
+ Console.WriteLine(" Create adapter...");\r
+ OracleDataAdapter da = new OracleDataAdapter("SELECT * FROM mono_adapter_test", con);
+ Console.WriteLine(" Create command builder...");
+ OracleCommandBuilder mycb = new OracleCommandBuilder(da);
+ Console.WriteLine(" set transr...");
+ da.SelectCommand.Transaction = transaction;
+
+ Console.WriteLine(" Create data set ...");\r
+ DataSet ds = new DataSet();\r
+ \r
+ Console.WriteLine("Fill data set via adapter...");\r
+ da.Fill(ds, "mono_adapter_test");\r
+\r
+ Console.WriteLine("delete row...");
+ ds.Tables["mono_adapter_test"].Rows[0].Delete();
+
+ Console.WriteLine("da.Update(table...");\r
+ da.Update(ds, "mono_adapter_test");\r
+\r
+ Console.WriteLine("Commit...");\r
+ transaction.Commit();\r
+ }
+
+ static void TestNonQueryUsingExecuteReader(OracleConnection con) \r
+ {\r
+ OracleDataReader reader = null;\r
+ OracleTransaction trans = null;\r
+\r
+ Console.WriteLine(" drop table mono_adapter_test...");\r
+ OracleCommand cmd = con.CreateCommand();\r
+\r
+ cmd.CommandText = "DROP TABLE MONO_ADAPTER_TEST";\r
+ trans = con.BeginTransaction();\r
+ cmd.Transaction = trans;\r
+ try {\r
+ reader = cmd.ExecuteReader();\r
+ Console.WriteLine(" RowsAffected before read: " + reader.RecordsAffected.ToString());\r
+ reader.Read();\r
+ Console.WriteLine(" RowsAffected after read: " + reader.RecordsAffected.ToString());\r
+ reader.Close();\r
+ Console.WriteLine(" RowsAffected after close: " + reader.RecordsAffected.ToString());\r
+ trans.Commit();\r
+ }\r
+ catch(OracleException e) {\r
+ Console.WriteLine(" OracleException caught: " + e.Message);\r
+ trans.Commit();\r
+ }\r
+\r
+ Console.WriteLine(" Create table mono_adapter_test...");\r
+ cmd.CommandText = "CREATE TABLE MONO_ADAPTER_TEST ( " +
+ " varchar2_value VarChar2(32), " +
+ " number_whole_value Number(18,0) PRIMARY KEY ) ";\r
+ trans = con.BeginTransaction();\r
+ cmd.Transaction = trans;\r
+ reader = cmd.ExecuteReader();\r
+ Console.WriteLine(" RowsAffected before read: " + reader.RecordsAffected.ToString());\r
+ reader.Read();\r
+ Console.WriteLine(" RowsAffected after read: " + reader.RecordsAffected.ToString());\r
+ reader.Close();\r
+ Console.WriteLine(" RowsAffected after close: " + reader.RecordsAffected.ToString());\r
+ trans.Commit();\r
+\r
+ Console.WriteLine("Insert into table mono_adapter_test...");\r
+ \r
+ string sql =\r
+ "INSERT INTO MONO_ADAPTER_TEST " +\r
+ "(VARCHAR2_VALUE,NUMBER_WHOLE_VALUE) " +\r
+ "VALUES(:p1,:p2)";\r
+\r
+ OracleCommand cmd2 = con.CreateCommand();\r
+ trans = con.BeginTransaction();\r
+ cmd2.Transaction = trans;\r
+ cmd2.CommandText = sql;\r
+ \r
+ OracleParameter myParameter1 = new OracleParameter("p1", OracleType.VarChar, 32);
+ myParameter1.Direction = ParameterDirection.Input;\r
+ \r
+ OracleParameter myParameter2 = new OracleParameter("p2", OracleType.Number);\r
+ myParameter2.Direction = ParameterDirection.Input;\r
+\r
+ myParameter2.Value = 182;\r
+ myParameter1.Value = "Mono";\r
+\r
+ cmd2.Parameters.Add (myParameter1);\r
+ cmd2.Parameters.Add (myParameter2);\r
+ \r
+ // insert 1 record\r
+ reader = cmd2.ExecuteReader();\r
+ Console.WriteLine(" RowsAffected before read: " + reader.RecordsAffected.ToString());\r
+ reader.Read();\r
+ Console.WriteLine(" RowsAffected after read: " + reader.RecordsAffected.ToString());\r
+ reader.Close();\r
+ Console.WriteLine(" RowsAffected after close: " + reader.RecordsAffected.ToString());\r
+\r
+ // insert another record\r
+ Console.WriteLine(" Insert another record...");\r
+ myParameter2.Value = 183;\r
+ myParameter1.Value = "Oracle";\r
+ reader = cmd2.ExecuteReader();\r
+ Console.WriteLine(" RowsAffected before read: " + reader.RecordsAffected.ToString());\r
+ reader.Read();\r
+ Console.WriteLine(" RowsAffected after read: " + reader.RecordsAffected.ToString());\r
+ reader.Close();\r
+ Console.WriteLine(" RowsAffected after close: " + reader.RecordsAffected.ToString());\r
+\r
+ trans.Commit();\r
+ trans = null;\r
+ \r
+ ReadSimpleTest(con, "SELECT * FROM MONO_ADAPTER_TEST");\r
+ }\r
+
+ static void CommitCursor (OracleConnection con)
+ {
+ OracleCommand cmd = con.CreateCommand ();
+ cmd.CommandText = "COMMIT";
+ cmd.ExecuteNonQuery ();
+ cmd.Dispose ();
+ cmd = null;
+ }\r
+
static void RollbackTest (OracleConnection connection)
{
OracleTransaction transaction = connection.BeginTransaction ();
transaction.Commit ();
}
- public static void ParameterTest (OracleConnection connection)
+ public static void ParameterTest2 (OracleConnection connection)
+ {
+ Console.WriteLine(" Setting NLS_DATE_FORMAT...");\r
+\r
+ OracleCommand cmd2 = connection.CreateCommand();\r
+ cmd2.CommandText = "ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS'";\r
+ \r
+ cmd2.ExecuteNonQuery ();\r
+\r
+ Console.WriteLine(" Drop table MONO_TEST_TABLE2...");
+ try {
+ cmd2.CommandText = "DROP TABLE MONO_TEST_TABLE7";
+ cmd2.ExecuteNonQuery ();
+ }
+ catch(OracleException oe1) {
+ // ignore if table already exists
+ }
+
+ Console.WriteLine(" Create table MONO_TEST_TABLE7...");
+
+ cmd2.CommandText = "CREATE TABLE MONO_TEST_TABLE7(" +
+ " COL1 VARCHAR2(8) NOT NULL, " +
+ " COL2 VARCHAR2(32), " +
+ " COL3 NUMBER(18,2), " +
+ " COL4 NUMBER(18,2), " +
+ " COL5 DATE NOT NULL, " +
+ " COL6 DATE, " +
+ " COL7 BLOB NOT NULL, " +
+ " COL8 BLOB, " +
+ " COL9 CLOB NOT NULL, " +
+ " COL10 CLOB " +
+ ")";
+ cmd2.ExecuteNonQuery ();
+
+ Console.WriteLine(" COMMIT...");
+ cmd2.CommandText = "COMMIT";
+ cmd2.ExecuteNonQuery ();
+
+ Console.WriteLine(" create insert command...");
+\r
+ OracleTransaction trans = connection.BeginTransaction ();\r
+ OracleCommand cmd = connection.CreateCommand ();\r
+ cmd.Transaction = trans;\r
+
+ cmd.CommandText = "INSERT INTO MONO_TEST_TABLE7 " + \r
+ "(COL1,COL2,COL3,COL4,COL5,COL6,COL7,COL8,COL9,COL10) " + \r
+ "VALUES(:P1,:P2,:P3,:P4,:P5,:P6,:P7,:P8,:P9,:P10)";\r
+ \r
+ Console.WriteLine(" Add parameters...");\r
+\r
+ OracleParameter parm1 = cmd.Parameters.Add (":P1", OracleType.VarChar, 8);\r
+ OracleParameter parm2 = cmd.Parameters.Add (":P2", OracleType.VarChar, 32);\r
+ \r
+ OracleParameter parm3 = cmd.Parameters.Add (":P3", OracleType.Number);\r
+ OracleParameter parm4 = cmd.Parameters.Add (":P4", OracleType.Number);\r
+ \r
+ OracleParameter parm5 = cmd.Parameters.Add (":P5", OracleType.DateTime);\r
+ OracleParameter parm6 = cmd.Parameters.Add (":P6", OracleType.DateTime);\r
+\r
+ // FIXME: fix BLOBs and CLOBs in OracleParameter\r
+\r
+ OracleParameter parm7 = cmd.Parameters.Add (":P7", OracleType.Blob);\r
+ OracleParameter parm8 = cmd.Parameters.Add (":P8", OracleType.Blob);\r
+\r
+ OracleParameter parm9 = cmd.Parameters.Add (":P9", OracleType.Clob);\r
+ OracleParameter parm10 = cmd.Parameters.Add (":P10", OracleType.Clob);\r
+\r
+ // TODO: implement out, return, and ref parameters\r
+\r
+ string s = "Mono";\r
+ decimal d = 123456789012345.678M;\r
+ DateTime dt = DateTime.Now;\r
+\r
+ string clob = "Clob";\r
+ byte[] blob = new byte[] { 0x31, 0x32, 0x33, 0x34, 0x35 };\r
+ \r
+ Console.WriteLine(" Set Values...");\r
+\r
+ parm1.Value = s;\r
+ parm2.Value = DBNull.Value;\r
+ \r
+ parm3.Value = d;\r
+ parm4.Value = DBNull.Value;\r
+ \r
+ parm5.Value = dt;\r
+ parm6.Value = DBNull.Value;\r
+ \r
+ parm7.Value = blob;\r
+ parm8.Value = DBNull.Value;\r
+\r
+ parm9.Value = clob;\r
+ parm10.Value = DBNull.Value;\r
+ \r
+ Console.WriteLine(" ExecuteNonQuery...");\r
+\r
+ cmd.ExecuteNonQuery ();\r
+ trans.Commit();
+ }
+
+ public static void ParameterTest (OracleConnection connection)
{
Console.WriteLine(" Setting NLS_DATE_FORMAT...");\r
\r
cmd3.ExecuteNonQuery ();
}
+ static void OutParmTest1 (OracleConnection con)
+ {
+ // test stored procedure with 2 parameters
+ // 1. input varchar2
+ // 2. output varchar
+
+ OracleCommand cmd2 = null;
+ Console.WriteLine(" Drop procedure SP_OUTPUTPARMTEST1...");
+ try {
+ cmd2 = con.CreateCommand ();
+ cmd2.CommandText = "DROP PROCEDURE SP_OUTPUTPARMTEST1";
+ cmd2.ExecuteNonQuery ();
+ }
+ catch(OracleException oe1) {
+ // ignore if table already exists
+ }
+
+ Console.WriteLine(" Create stored procedure SP_OUTPUTPARMTEST1...");
+ // stored procedure concatenates strings
+ cmd2.CommandText =
+ "CREATE OR REPLACE PROCEDURE SP_TESTOUTPARM1(parm1 IN VARCHAR2,parm2 OUT VARCHAR2) " +
+ "IS " +
+ "BEGIN " +
+ " parm2 := 'one' || parm1 || 'three';" +
+ "END;";
+
+ cmd2.ExecuteNonQuery ();
+
+ Console.WriteLine(" COMMIT...");
+ cmd2.CommandText = "COMMIT";
+ cmd2.ExecuteNonQuery ();
+
+ Console.WriteLine(" Call stored procedure SP_TESTOUTPARM1 with two parameters...");
+ OracleCommand cmd3 = con.CreateCommand ();\r
+ cmd3.CommandType = CommandType.Text;\r
+ cmd3.CommandText =
+ "BEGIN " +
+ " SP_TESTOUTPARM1(:p1, :p2);" +\r
+ "END;";\r
+ OracleParameter myParameter1 = new OracleParameter("p1", OracleType.VarChar);
+ myParameter1.Value = "two";\r
+ myParameter1.Size = 4;\r
+ myParameter1.Direction = ParameterDirection.Input;\r
+ \r
+ OracleParameter myParameter2 = new OracleParameter("p2", OracleType.VarChar);\r
+ myParameter2.Size = 12;\r
+ myParameter2.Direction = ParameterDirection.Output;\r
+\r
+ cmd3.Parameters.Add (myParameter1);\r
+ cmd3.Parameters.Add (myParameter2);\r
+
+ cmd3.ExecuteNonQuery ();
+ string outValue = (string) myParameter2.Value;
+ Console.WriteLine (" Out Value should be: onetwothree");
+ Console.WriteLine (" Out Value: " + outValue);
+ }
+
+ static void OutParmTest2 (OracleConnection con)
+ {
+ // test stored procedure with 2 parameters
+ // 1. input number(18,2)
+ // 2. output number(18,2)
+
+ OracleCommand cmd2 = null;
+ Console.WriteLine(" Drop procedure SP_OUTPUTPARMTEST2...");
+ try {
+ cmd2 = con.CreateCommand ();
+ cmd2.CommandText = "DROP PROCEDURE SP_OUTPUTPARMTEST2";
+ cmd2.ExecuteNonQuery ();
+ }
+ catch(OracleException oe1) {
+ // ignore if table already exists
+ }
+
+ Console.WriteLine(" Create stored procedure SP_OUTPUTPARMTEST2...");
+
+ // stored procedure addes two numbers
+ cmd2.CommandText =
+ "CREATE OR REPLACE PROCEDURE SP_TESTOUTPARM2(parm1 IN NUMBER,parm2 OUT NUMBER) " +
+ "IS " +
+ "BEGIN " +
+ " parm2 := parm1 + 3; " +
+ "END;";
+
+ cmd2.ExecuteNonQuery ();
+
+ Console.WriteLine(" COMMIT...");
+ cmd2.CommandText = "COMMIT";
+ cmd2.ExecuteNonQuery ();
+
+ Console.WriteLine(" Call stored procedure SP_TESTOUTPARM2 with two parameters...");
+ OracleCommand cmd3 = con.CreateCommand ();\r
+ cmd3.CommandType = CommandType.Text;\r
+ cmd3.CommandText =
+ "BEGIN " +
+ " SP_TESTOUTPARM2(:p1, :p2);" +\r
+ "END;";\r
+ OracleParameter myParameter1 = new OracleParameter("p1", OracleType.Number);
+ myParameter1.Value = 2;\r
+ myParameter1.Direction = ParameterDirection.Input;\r
+ \r
+ OracleParameter myParameter2 = new OracleParameter("p2", OracleType.Number);\r
+ myParameter2.Direction = ParameterDirection.Output;\r
+\r
+ cmd3.Parameters.Add (myParameter1);\r
+ cmd3.Parameters.Add (myParameter2);\r
+
+ cmd3.ExecuteNonQuery ();
+ decimal outValue = (decimal) myParameter2.Value;
+ Console.WriteLine (" Out Value should be: 5");
+ Console.WriteLine (" Out Value: {0}", outValue);
+ }
+
+ static void OutParmTest3 (OracleConnection con)
+ {
+ // test stored procedure with 2 parameters
+ // 1. input date
+ // 2. output date
+
+ OracleCommand cmd2 = null;
+ Console.WriteLine(" Drop procedure SP_OUTPUTPARMTEST3...");
+ try {
+ cmd2 = con.CreateCommand ();
+ cmd2.CommandText = "DROP PROCEDURE SP_OUTPUTPARMTEST3";
+ cmd2.ExecuteNonQuery ();
+ }
+ catch(OracleException oe1) {
+ // ignore if table already exists
+ }
+
+ Console.WriteLine(" Create stored procedure SP_OUTPUTPARMTEST3...");
+
+ // stored procedure adds 3 days to date
+ cmd2.CommandText =
+ "CREATE OR REPLACE PROCEDURE SP_TESTOUTPARM3(parm1 IN DATE,parm2 OUT DATE) " +
+ "IS " +
+ "BEGIN " +
+ " parm2 := parm1 + 3; " +
+ "END;";
+
+ cmd2.ExecuteNonQuery ();
+
+ Console.WriteLine(" COMMIT...");
+ cmd2.CommandText = "COMMIT";
+ cmd2.ExecuteNonQuery ();
+
+ Console.WriteLine(" Call stored procedure SP_TESTOUTPARM3 with two parameters...");
+ OracleCommand cmd3 = con.CreateCommand ();\r
+ cmd3.CommandType = CommandType.Text;\r
+ cmd3.CommandText =
+ "BEGIN " +
+ " SP_TESTOUTPARM3(:p1, :p2);" +\r
+ "END;";\r
+ OracleParameter myParameter1 = new OracleParameter("p1", OracleType.DateTime);
+ myParameter1.Value = new DateTime(2004,12,15);\r
+ myParameter1.Direction = ParameterDirection.Input;\r
+ \r
+ OracleParameter myParameter2 = new OracleParameter("p2", OracleType.DateTime);\r
+ myParameter2.Direction = ParameterDirection.Output;\r
+\r
+ cmd3.Parameters.Add (myParameter1);\r
+ cmd3.Parameters.Add (myParameter2);\r
+
+ cmd3.ExecuteNonQuery ();
+ DateTime outValue = (DateTime) myParameter2.Value;
+ Console.WriteLine (" Out Value should be: 2004-12-18");
+ Console.WriteLine (" Out Value: {0}", outValue.ToString ("yyyy-mm-dd"));
+ }
+
static void ShowConnectionProperties (OracleConnection con)
{
try {
Wait ("");
+ Console.WriteLine ("DataAdapter Test 2 BEGIN...");
+ DataAdapterTest2(con1);
+ Console.WriteLine ("DataAdapter Test 2 END.");
+
+ Wait ("");
+
Console.WriteLine ("Rollback Test BEGIN...");
RollbackTest(con1);
Console.WriteLine ("Rollback Test END.");
ReadSimpleTest(con1, "SELECT * FROM MONO_TEST_TABLE2");
Console.WriteLine ("Stored Proc Test 2 END...");
+ Console.WriteLine ("Out Parameter and PL/SQL Block Test 1 BEGIN...");
+ OutParmTest1 (con1);
+ Console.WriteLine ("Out Parameter and PL/SQL Block Test 1 END...");
+
+ Console.WriteLine ("Out Parameter and PL/SQL Block Test 2 BEGIN...");
+ OutParmTest2 (con1);
+ Console.WriteLine ("Out Parameter and PL/SQL Block Test 2 END...");
+
+ Console.WriteLine ("Out Parameter and PL/SQL Block Test 3 BEGIN...");
+ OutParmTest3 (con1);
+ Console.WriteLine ("Out Parameter and PL/SQL Block Test 3 END...");
+
+ Wait ("");
+
+ Console.WriteLine ("Test a Non Query using Execute Reader BEGIN...");
+ TestNonQueryUsingExecuteReader (con1);
+ Console.WriteLine ("Test a Non Query using Execute Reader END...");
+
Wait ("");
Console.WriteLine ("Null Aggregate Warning BEGIN test...");
con1.Close ();
Console.WriteLine("Closed.");
- conStr = conStr + ";pooling=true;min pool size=4;max pool size=" + MAX_CONNECTIONS.ToString ();\r
- ConnectionPoolingTest1 ();\r
- ConnectionPoolingTest2 ();\r
+ //conStr = conStr + ";pooling=true;min pool size=4;max pool size=" + MAX_CONNECTIONS.ToString ();\r
+ //ConnectionPoolingTest1 ();\r
+ //ConnectionPoolingTest2 ();\r
Console.WriteLine("Done.");
}
projects / mono.git / blobdiff