public void DataReaderGetSchemaTable() { using (PhoenixConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); using (IDbCommand cmd = c.CreateCommand()) { cmd.CommandText = "SELECT * FROM BIGTABLE"; using (IDataReader dr = cmd.ExecuteReader()) { DataTable dt = dr.GetSchemaTable(); Assert.IsTrue(dt.Columns.Count > 0); DataColumn dcColName = dt.Columns["ColumnName"]; Assert.IsNotNull(dcColName, "ColumnName"); DataColumn dcColSize = dt.Columns["ColumnSize"]; Assert.IsNotNull(dcColSize, "ColumnSize"); Assert.AreEqual(dcColSize.DataType, typeof(int)); DataColumn dcColOrdinal = dt.Columns["ColumnOrdinal"]; Assert.IsNotNull(dcColOrdinal, "ColumnOrdinal"); Assert.AreEqual(dcColOrdinal.DataType, typeof(int)); DataColumn dcNullable = dt.Columns["AllowDBNull"]; Assert.IsNotNull(dcNullable, "AllowDBNull"); Assert.AreEqual(dcNullable.DataType, typeof(bool)); } } } }
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)); } }
public void DataReaderItemStringIndexer() { using (PhoenixConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); using (IDbCommand cmd = c.CreateCommand()) { cmd.CommandText = "SELECT * FROM BIGTABLE"; using (IDataReader dr = cmd.ExecuteReader()) { while (dr.Read()) { for (int i = 0; i < dr.FieldCount; i++) { string name = dr.GetName(i); object o = dr[name]; object o2 = dr.GetValue(i); Assert.AreEqual(o2, o, "Mismatch on field {0}", name); } } } } } }
public void CommandExecuteNonQueryElapsedGreaterThanZero() { using (IDbConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); ReCreateTestTableIfNotExists(c); using (IDbCommand cmd = c.CreateCommand()) { 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(); // Confirm PhoenixCommand.Elapsed is working a bit. PhoenixCommand phCmd = cmd as PhoenixCommand; Assert.IsNotNull(phCmd); Assert.IsNotNull(phCmd.Elapsed); Assert.AreNotEqual(0, phCmd.Elapsed.TotalMilliseconds, nameof(phCmd.Elapsed.TotalMilliseconds)); this.TestContext.WriteLine("PhoenixCommand.Elapsed: {0}", phCmd.Elapsed); } Assert.AreEqual(1, QueryAllRows(c)); } }
public void CommandCreateDisposeTest() { using (PhoenixConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); using (IDbCommand cmd = c.CreateCommand()) { // Do nothing, just displose } } }
public void DataReaderRecordsAffectedOne() { using (PhoenixConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); using (IDbCommand cmd = c.CreateCommand()) { cmd.CommandText = "UPSERT INTO BIGTABLE (ID, MYTIMESTAMP) VALUES (502, NOW())"; using (IDataReader dr = cmd.ExecuteReader()) { Assert.AreEqual(1, dr.RecordsAffected); } } } }
public void DataReaderHasRowsFalse() { using (PhoenixConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); using (IDbCommand cmd = c.CreateCommand()) { cmd.CommandText = "UPSERT INTO BIGTABLE (ID, MYTIMESTAMP) VALUES (502, NOW())"; using (PhoenixDataReader dr = cmd.ExecuteReader() as PhoenixDataReader) { Assert.AreEqual(false, dr.HasRows); } } } }
public DataTable GetColumns(PhoenixConnection c) { if (null == c) { throw new ArgumentNullException(nameof(c)); } DataTable columns = null; StringBuilder sbSql = new StringBuilder(SqlColumnMetaData); if (c.State != ConnectionState.Open) { c.Open(); } using (IDbCommand cmd = c.CreateCommand()) { // Parameters for table name, and schema if not null. cmd.Parameters.Add(new PhoenixParameter(this.Name)); if (DBNull.Value == Row["TABLE_SCHEM"]) { sbSql.Append(SqlTableSchemaNullCriteria); } else { sbSql.Append(SqlTableSchemaCriteria); cmd.Parameters.Add(new PhoenixParameter(Row["TABLE_SCHEM"])); } cmd.CommandText = sbSql.ToString(); cmd.Prepare(); using (IDataReader dr = cmd.ExecuteReader()) { columns = new DataTable(string.Format("{0} Columns", this.Name)); columns.BeginLoadData(); columns.Load(dr); columns.EndLoadData(); } } return(columns); }
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)); } } }
/// <summary> /// Gets a DataTable containing the columns of the table which comprise the key columns of this index. /// This requies an additional trip to the Phoenix Query Server using the specified connection. /// </summary> /// <returns>The DataTable containing the index meta data.</returns> public DataTable GetKeyColumns(PhoenixConnection c) { if (null == c) { throw new ArgumentNullException(nameof(c)); } DataTable dt = null; if (c.State != ConnectionState.Open) { c.Open(); } using (IDbCommand cmd = c.CreateCommand()) { cmd.CommandText = SqlKeyColumnMetaData; cmd.Parameters.Add(new PhoenixParameter(this.Name)); //if (DBNull.Value == Row["TABLE_SCHEM"]) //{ // cmd.CommandText += SqlTableSchemaNullCriteria; //} //else //{ // cmd.CommandText += SqlTableSchemaCriteria; // cmd.Parameters.Add(new PhoenixParameter(Row["TABLE_SCHEM"])); //} cmd.Prepare(); using (IDataReader dr = cmd.ExecuteReader()) { dt = new DataTable(string.Format("{0} Key Columns", this.Name)); dt.BeginLoadData(); dt.Load(dr); dt.EndLoadData(); } } return(dt); }
public void DataTableLoadFromPhoenixDataReader() { using (PhoenixConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); using (IDbCommand cmd = c.CreateCommand()) { cmd.CommandText = "SELECT * FROM BIGTABLE"; using (IDataReader dr = cmd.ExecuteReader()) { DataTable dt = new DataTable(); dt.Load(dr); Assert.IsTrue(dt.Rows.Count > 0); Assert.IsTrue(dt.Columns.Count > 0); } } } }
public void DataReaderGetFieldType() { using (PhoenixConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); using (IDbCommand cmd = c.CreateCommand()) { cmd.CommandText = "SELECT * FROM BIGTABLE"; using (IDataReader dr = cmd.ExecuteReader()) { dr.Read(); for (int i = 0; i < dr.FieldCount; i++) { Type t = dr.GetFieldType(i); Assert.IsNotNull(t, "GetFieldType returned null!"); } } } } }
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)); } }
public void DataReaderGetValues() { using (PhoenixConnection c = new PhoenixConnection()) { c.ConnectionString = this.ConnectionString(); c.Open(); ReCreateTestTableIfNotExists(c, "DataReaderGetValuesTest", "CREATE TABLE IF NOT EXISTS DataReaderGetValuesTest (ID BIGINT PRIMARY KEY, FirstCol varchar(16), SecondCol varchar(64))", true, true); List <Func <object> > pFuncs = new List <Func <object> >(); pFuncs.Add(() => "First"); pFuncs.Add(() => "Second"); PreparedCmdParameterTest(2, "UPSERT INTO DataReaderGetValuesTest (ID, FirstCol, SecondCol) VALUES (NEXT VALUE FOR garuda.DataReaderGetValuesTestSequence, :1, :2)", pFuncs, false); using (IDbCommand cmd = c.CreateCommand()) { cmd.CommandText = "SELECT * FROM DataReaderGetValuesTest"; using (IDataReader dr = cmd.ExecuteReader()) { object[] values = new object[dr.FieldCount]; while (dr.Read()) { dr.GetValues(values); Assert.AreEqual("First", values[1]); Assert.AreEqual("Second", values[2]); } } } } }
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()); } } }
private async void _tsmiTableScriptCreate_Click(object sender, EventArgs e) { try { // Get location of context menu. This cooresponds to the point underwhich // is the node we care about. GarudaPhoenixTable table = GetTableFromTreeHitTest(); if (null != table) { DataTable columns = await table.GetColumnsAsync(_connection, true); using (IDbCommand cmd = _connection.CreateCommand()) { cmd.CommandText = string.Format("SELECT * FROM {0} LIMIT 0", table.FullName); using (IDataReader dr = cmd.ExecuteReader()) { DataTable schemaTable = dr.GetSchemaTable(); StringBuilder sbCreate = new StringBuilder(); sbCreate.AppendFormat("CREATE TABLE {0} (", table.FullName); sbCreate.AppendLine(); for (int i = 0; i < schemaTable.Rows.Count; i++) { DataRow col = schemaTable.Rows[i]; string dataType = dr.GetDataTypeName(i); string colName = col["ColumnName"].ToString(); bool isPK = table.IsColumnPrimaryKey(columns, colName); if (i > 0) { sbCreate.AppendLine(","); } // Column name and data type, with size for varchars sbCreate.AppendFormat("\t{0} {1}", colName, dataType); if ("VARCHAR" == dataType) { sbCreate.AppendFormat("({0})", col["ColumnSize"]); } // Nullable? if (!Convert.ToBoolean(col["AllowDBNull"])) { sbCreate.AppendFormat(" NOT"); } sbCreate.AppendFormat(" NULL"); // Primary key? if (isPK) { sbCreate.Append(" PRIMARY KEY"); } } sbCreate.AppendLine(); sbCreate.AppendFormat(")"); // Open a new query view tab and set the text. QueryView qv = NewQueryViewTab(null, null); qv.Text = sbCreate.ToString(); } } } } catch (Exception ex) { HandleException(ex); } }