public static void Command_ExecuteDbDataReader_Sample() { Console.WriteLine(String.Format("{0} Command_ExecuteDbDataReader_Sample", databaseProductType.ToString())); Console.WriteLine("Start"); CdbcConnection con = UtilCommon.getConnection(databaseProductType); string queryStr = @"SELECT TABLE_NAME FROM ALL_TABLES"; CdbcCommand cmd = new CdbcCommand(queryStr, con); Console.WriteLine("--------------------------"); Console.WriteLine("Sql : " + queryStr); Console.WriteLine("--------------------------"); try { DbDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Console.WriteLine(reader.GetValue(0).ToString()); } } catch (Exception ex) { Console.WriteLine("Exception:"); Console.WriteLine(ex.Message); } Console.WriteLine("--------------------------"); Console.WriteLine("End"); }
public static void CommandCollection_ExecuteMultiQuery_Sample() { Console.WriteLine(String.Format("{0} CommandCollection_ExecuteMultiQuery_Sample", databaseProductType.ToString())); Console.WriteLine("Start"); Console.WriteLine("--------------------------"); Console.WriteLine("CommandCollection.ExecuteMultiQuery is original Method of CdbcCommandCollection."); Console.WriteLine("It can execute multi command in same transaction."); Console.WriteLine("It is designed to improve performance and Data Concurrency and Consistency"); Console.WriteLine("--------------------------"); Console.WriteLine("This sample will insert 3 records and than update one of the record."); Console.WriteLine("--------------------------"); CdbcConnection con = UtilCommon.getConnection(databaseProductType); string queryStr1 = @"INSERT INTO CODE_SAMPLE(VAR1 , DATE1 ) VALUES (:VAR1, SYSDATE)"; CdbcCommand cmd1 = new CdbcCommand(queryStr1, con); string[] arrParas = new string[3]; for (int i = 0; i < arrParas.Length; i++) { arrParas[i] = Guid.NewGuid().ToString(); } cmd1.ArrayBindCount = 3; CdbcParameter prm = new CdbcParameter("VAR1", CdbcOracleDbType.Varchar2); prm.Value = arrParas; cmd1.Parameters.Add(prm); string queryStr2 = @"UPDATE CODE_SAMPLE SET DATE1 = DATE1+1 WHERE VAR1=:VAR1"; CdbcCommand cmd2 = new CdbcCommand(queryStr2, con); prm = new CdbcParameter("VAR1", CdbcOracleDbType.Varchar2); prm.Value = arrParas[0]; cmd2.Parameters.Add(prm); Console.WriteLine("--------------------------"); Console.WriteLine("SQL 1 :" + queryStr1); Console.WriteLine("Para 1 :"); for (int i = 0; i < arrParas.Length; i++) { Console.WriteLine(arrParas[i]); } Console.WriteLine(""); Console.WriteLine("SQL 2 :" + queryStr2); Console.WriteLine("--------------------------"); try { CdbcCommandCollection cmdCollection = new CdbcCommandCollection(); cmdCollection.Add(cmd1); cmdCollection.Add(cmd2); cmdCollection.ExecuteMultiQuery(); for (int iCmd = 0; iCmd < cmdCollection.Count; iCmd++) { Console.WriteLine(String.Format("Command {0} Result : {1} ", iCmd, cmdCollection.ExecuteNonQueryResult[iCmd].ToString())); } } catch (Exception ex) { Console.WriteLine("Exception:"); Console.WriteLine(ex.Message); } //Confirm Data Console.WriteLine("---Confirm Data-----------------------"); string queryStrSelect = @"SELECT VAR1, DATE1 FROM CODE_SAMPLE WHERE VAR1 IN (:VAR1_0 ,:VAR1_1,:VAR1_2) "; CdbcCommand cmdSelect = new CdbcCommand(queryStrSelect, con); for (int i = 0; i < arrParas.Length; i++) { prm = new CdbcParameter("VAR1_" + i.ToString(), CdbcOracleDbType.Varchar2); prm.Value = arrParas[i]; cmdSelect.Parameters.Add(prm); } try { DbDataReader reader = cmdSelect.ExecuteReader(); while (reader.Read()) { for (int i = 0; i < reader.FieldCount; i++) { Console.WriteLine(String.Format("{0} = {1}", reader.GetName(i), reader.GetValue(i).ToString())); } } } catch (Exception ex) { Console.WriteLine("Exception:"); Console.WriteLine(ex.Message); } //Clear Data Console.WriteLine("---Clear Data-----------------------"); string queryStrDelete = @"DELETE FROM CODE_SAMPLE WHERE VAR1=:VAR1"; CdbcCommand cmdDelete = new CdbcCommand(queryStrDelete, con); cmdDelete.ArrayBindCount = 3; prm = new CdbcParameter("VAR1", CdbcOracleDbType.Varchar2); prm.Value = arrParas; cmdDelete.Parameters.Add(prm); try { int i = cmdDelete.ExecuteNonQuery(); Console.WriteLine(String.Format("{0} Line(s) Deleted.", i)); } catch (Exception ex) { Console.WriteLine("Exception:"); Console.WriteLine(ex.Message); } Console.WriteLine("--------------------------"); Console.WriteLine("End"); }