public void TransactionReuseCommitTest() { int toInsert = 10; using (IDbConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); ReCreateTestTableIfNotExists(c); using (IDbTransaction tx = c.BeginTransaction()) { for (int i = 0; i < toInsert; i++) { using (IDbCommand cmd = c.CreateCommand()) { cmd.Transaction = tx; cmd.CommandText = string.Format("UPSERT INTO GARUDATEST (ID, AircraftIcaoNumber, MyInt, MyUint, MyUlong, MyTingInt, MyTime, MyDate, MyTimestamp, MyUnsignedTime, MyFloat) VALUES (NEXT VALUE FOR garuda.testsequence, 'NINTX1', 5, 4, 3, 2, CURRENT_TIME(), CURRENT_DATE(), '2016-07-25 22:28:00', CURRENT_TIME(), 1.2 / .4)"); cmd.ExecuteNonQuery(); } } tx.Commit(); } Assert.AreEqual(toInsert, QueryAllRows(c)); } }
private void PreparedCmdParameterTest(int rowsToInsert, string sql, List <Func <object> > pFunc, bool assertTotalRows = true) { using (IDbConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); ReCreateTestTableIfNotExists(c); using (IDbTransaction tx = c.BeginTransaction()) { using (IDbCommand cmd = c.CreateCommand()) { cmd.Transaction = tx; cmd.CommandText = sql; cmd.Prepare(); for (int i = 0; i < rowsToInsert; i++) { // Parameters loop foreach (var pf in pFunc) { // Create a parameter used in the query var p = cmd.CreateParameter(); p.Value = pf(); cmd.Parameters.Add(p); } cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } } tx.Commit(); } if (assertTotalRows) { Assert.AreEqual(rowsToInsert, QueryAllRows(c)); } } }
public void CommandPrepareTest() { int toInsert = 10; using (IDbConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); ReCreateTestTableIfNotExists(c); using (IDbTransaction tx = c.BeginTransaction()) { using (IDbCommand cmd = c.CreateCommand()) { cmd.Transaction = tx; cmd.CommandText = string.Format("UPSERT INTO GARUDATEST (ID, AircraftIcaoNumber, MyInt, MyUint, MyUlong, MyTingInt, MyTime, MyDate, MyTimestamp, MyUnsignedTime, MyFloat) VALUES (NEXT VALUE FOR garuda.testsequence, :1, 12, 14, 87, 45, CURRENT_TIME(), CURRENT_DATE(), '2016-07-25 22:28:00', CURRENT_TIME(), 1.2 / .4)"); cmd.Prepare(); for (int i = 0; i < toInsert; i++) { // Create a parameter used in the query var p1 = cmd.CreateParameter(); p1.Value = string.Format("N{0}", DateTime.Now.ToString("hmmss")); cmd.Parameters.Add(p1); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); } } tx.Commit(); } Assert.AreEqual(toInsert, QueryAllRows(c)); } }
static void Main(string[] args) { GarudaUtilCmdLineArgs cmdLine = new GarudaUtilCmdLineArgs(args); try { if (args.Length == 0) { Application.EnableVisualStyles(); Application.SetCompatibleTextRenderingDefault(false); Application.Run(new MainForm()); } else { // Command Line mode using (IDbConnection phConn = new PhoenixConnection()) { phConn.ConnectionString = cmdLine.ConnectionString; phConn.Open(); //(phConn as PhoenixConnection).SystemTables(); using (IDbCommand cmd = phConn.CreateCommand()) { cmd.CommandText = "DROP TABLE IF EXISTS GARUDATEST"; cmd.ExecuteNonQuery(); cmd.CommandText = "CREATE TABLE IF NOT EXISTS GARUDATEST (ID BIGINT PRIMARY KEY, AircraftIcaoNumber varchar(16), MyInt INTEGER, MyUint UNSIGNED_INT, MyUlong UNSIGNED_LONG, MyTingInt TINYINT, MyTime TIME, MyDate DATE, MyTimestamp TIMESTAMP, MyUnsignedTime UNSIGNED_TIME, MyFloat FLOAT, MyBinary BINARY(16), MyArray INTEGER[2] )"; cmd.ExecuteNonQuery(); bool bCreateSequence = true; cmd.CommandText = "SELECT sequence_schema, sequence_name, start_with, increment_by, cache_size FROM SYSTEM.\"SEQUENCE\""; // WHERE sequence_schema = 'garuda' AND sequence_name='testsequence' using (IDataReader reader = cmd.ExecuteReader()) { while (reader.Read()) { if (reader.GetString(1).Equals("testsequence", StringComparison.InvariantCultureIgnoreCase)) { bCreateSequence = false; break; } } } if (bCreateSequence) { cmd.CommandText = "CREATE SEQUENCE garuda.testsequence"; cmd.ExecuteNonQuery(); } // Insert a bunch of data... using (IDbTransaction tx = phConn.BeginTransaction()) { cmd.Transaction = tx; cmd.CommandText = string.Format("UPSERT INTO GARUDATEST (ID, AircraftIcaoNumber, MyInt, MyUint, MyUlong, MyTingInt, MyTime, MyDate, MyTimestamp, MyUnsignedTime, MyFloat) VALUES (NEXT VALUE FOR garuda.testsequence, 'NINTX1', 5, 4, 3, 2, CURRENT_TIME(), CURRENT_DATE(), '2016-07-25 22:28:00', CURRENT_TIME(), 1.2 / .4)"); cmd.ExecuteNonQuery(); tx.Rollback(); } // Insert a bunch of data... int recordsToInsert = 10; for (int i = 0; i < recordsToInsert; i++) { cmd.CommandText = string.Format("UPSERT INTO GARUDATEST (ID, AircraftIcaoNumber, MyInt, MyUint, MyUlong, MyTingInt, MyTime, MyDate, MyTimestamp, MyUnsignedTime, MyFloat) VALUES (NEXT VALUE FOR garuda.testsequence, 'N{0}', 5, 4, 3, 2, CURRENT_TIME(), CURRENT_DATE(), '2016-07-25 22:28:00', CURRENT_TIME(), 1.2 / .4)", DateTime.Now.ToString("hmmss")); cmd.ExecuteNonQuery(); } cmd.CommandText = "SELECT * FROM GARUDATEST"; using (IDataReader reader = cmd.ExecuteReader()) { int iRecords = 0; while (reader.Read()) { iRecords++; for (int i = 0; i < reader.FieldCount; i++) { Console.WriteLine(string.Format("{0}: {1} ({2})", reader.GetName(i), reader.GetValue(i), reader.GetDataTypeName(i))); } } if (iRecords != recordsToInsert) { MessageBox.Show(string.Format("Expected {0}, got {1} records.", recordsToInsert, iRecords), "Warning"); } } } } } } catch (Exception ex) { System.Diagnostics.Trace.WriteLine(ex); if (cmdLine.ShowException) { MessageBox.Show(ex.ToString(), ex.GetType().ToString()); } } }