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 MetaDataGarudaPhoenixTableGetIndexes()
{
string expectedTable = "BIGTABLE";
using (PhoenixConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
DataTable dt = c.GetTables();
foreach (DataRow row in dt.Rows)
{
if (row["TABLE_NAME"].ToString() == expectedTable)
{
var tbl = new Garuda.Data.MetaData.GarudaPhoenixTable(row);
DataTable dtCols = tbl.GetIndexes(c);
Assert.IsNotNull(dtCols);
Assert.IsNotNull(dtCols.Rows);
Assert.IsTrue(dtCols.Rows.Count > 0);
Assert.IsNotNull(dtCols.Columns);
break;
}
}
}
}
public void MetaDataGarudaPhoenixTableGenerateUpsertStatement()
{
string expectedTable = "BIGTABLE";
using (PhoenixConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
DataTable dt = c.GetTables();
foreach (DataRow row in dt.Rows)
{
if (row["TABLE_NAME"].ToString() == expectedTable)
{
var tbl = new Garuda.Data.MetaData.GarudaPhoenixTable(row);
Task <string> tUpsert = tbl.GenerateUpsertStatementAsync(c);
tUpsert.Wait();
Assert.IsNotNull(tUpsert.Result);
Assert.IsTrue(tUpsert.Result.StartsWith("UPSERT INTO"));
TestContext.WriteLine(tUpsert.Result);
break;
}
}
}
}
public void ConnectionStringNullTest()
{
using (PhoenixConnection c = new PhoenixConnection())
{
c.ConnectionString = null;
}
}
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 BulkCopyTest2_Salted3Table()
{
Stopwatch sw = new Stopwatch();
using (PhoenixConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
string tableName = CreateBulkCopyTableIfNotExists(c, false, 3);
string nextValueForSequence = string.Format("NEXT VALUE FOR garuda.{0}", SequenceNameForTable(tableName));
PhoenixBulkCopy bc = new PhoenixBulkCopy(c);
DataTable dt = ConvertCSVtoDataTable(System.Configuration.ConfigurationManager.AppSettings["BulkCopyCsvTestFile"]);
// Query the table and measure performance
sw.Start();
bc.DestinationTableName = tableName;
bc.ColumnMappings.Add("ID", new PhoenixBulkCopyColumnMapping(nextValueForSequence));
bc.BatchSize = 100;
bc.WriteToServer(dt);
sw.Stop();
WriteBulkCopyPerf(dt.Rows.Count, sw.ElapsedMilliseconds, "BulkCopySalted3PerfFile");
// How many rows did we get back?
this.TestContext.WriteLine("Bulk Copy Rows: {0}", dt.Rows.Count);
this.TestContext.WriteLine("Bulk Copy Time: {0}ms", sw.ElapsedMilliseconds);
// More than zero?
Assert.IsTrue(dt.Rows.Count > 0);
}
}
/// <summary>
/// Generates the Upsert statement associated with this table.
/// </summary>
/// <param name="c"></param>
/// <param name="refresh">If true and cached column meta data exists, it is refreshed from the PQS. Defaults to true.</param>
/// <returns></returns>
public async Task <string> GenerateUpsertStatementAsync(PhoenixConnection c, bool refresh = true)
{
if (null == c)
{
throw new ArgumentNullException(nameof(c));
}
DataTable columns = await this.GetColumnsAsync(c, true);
StringBuilder sbUpsert = new StringBuilder();
StringBuilder sbValues = new StringBuilder();
sbUpsert.AppendFormat("UPSERT INTO {0} (", this.FullName);
for (int i = 0; i < columns.Rows.Count; i++)
{
DataRow col = columns.Rows[i];
if (i > 0)
{
sbUpsert.Append(",");
sbValues.Append(",");
}
sbUpsert.Append(col["COLUMN_NAME"]);
sbValues.Append("?");
}
sbUpsert.AppendFormat(") VALUES ({0})", sbValues.ToString());
return(sbUpsert.ToString());
}
public void ConnectionChangeDatabase()
{
using (PhoenixConnection c = new PhoenixConnection(this.ConnectionString()))
{
c.ChangeDatabase("whatever");
}
}
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 MetaDataGarudaPhoenixTableConstructor()
{
string expectedTable = "BIGTABLE";
using (PhoenixConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
DataTable dt = c.GetTables();
foreach (DataRow row in dt.Rows)
{
var tbl = new Garuda.Data.MetaData.GarudaPhoenixTable(row);
if (row["TABLE_NAME"].ToString() == expectedTable)
{
Assert.IsNotNull(tbl.Row);
Assert.IsInstanceOfType(tbl.Row, typeof(DataRow));
Assert.IsNotNull(tbl.Name);
Assert.AreEqual(expectedTable, tbl.Name);
Assert.IsNotNull(tbl.FullName);
Assert.AreEqual(expectedTable, tbl.FullName);
Assert.IsNotNull(tbl.Schema);
Assert.AreEqual(string.Empty, tbl.Schema);
break;
}
}
}
}
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 ConstructorConnectionStringNullTest()
{
using (PhoenixConnection c = new PhoenixConnection(null))
{
// Shouldn't get here anyway
Assert.AreEqual <ConnectionState>(ConnectionState.Closed, c.State);
}
}
public void ConstructorConnectionStringMalformedTest()
{
string cs = "Server=;UserID=";
using (PhoenixConnection c = new PhoenixConnection(cs))
{
Assert.AreEqual <ConnectionState>(ConnectionState.Closed, c.State);
}
}
/// <summary>
/// Gets a DataTable containing the indexes of this table and associated meta data.
/// This requies an additional trip to the Phoenix Query Server using the specified connection.
/// </summary>
/// <param name="c">The PhoenixConnection to use when querying additional index meta data.</param>
/// <param name="refresh">If true and a cached copy exists, refresh the data from the PQS.</param>
/// <returns>The DataTable containing the index meta data.</returns>
public async Task <DataTable> GetIndexesAsync(PhoenixConnection c, bool refresh)
{
if (null == this._indexes || refresh)
{
this._indexes = await Task.Factory.StartNew(() => GetIndexes(c));
}
return(_indexes);
}
/// <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>
/// <param name="c">The PhoenixConnection to use when querying additional index meta data.</param>
/// <param name="refresh">If true and a cached copy exists, refresh the data from the PQS.</param>
/// <returns>The DataTable containing the index meta data.</returns>
public async Task <DataTable> GetKeyColumnsAsync(PhoenixConnection c, bool refresh)
{
if (null == this._keyColumns || refresh)
{
this._keyColumns = await Task.Factory.StartNew(() => GetKeyColumns(c));
}
return(_keyColumns);
}
public void ConnectionTablesDataTable()
{
using (PhoenixConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
DataTable tables = c.GetTables();
Assert.IsTrue(tables.Rows.Count > 0);
}
}
private async void _tsbConnect_Click(object sender, EventArgs e)
{
try
{
UpdateBusyWaitState(true, Properties.Resources.StatusConnecting);
LoginForm frmLogin = new LoginForm();
if (DialogResult.OK == frmLogin.ShowDialog())
{
_connection = frmLogin.Connection;
_tsslConnection.Text = frmLogin.Server;
// Add the server root to the tree
TreeNode root = null;
foreach (TreeNode tn in _treeView.Nodes)
{
var ph = tn.Tag as PhoenixConnection;
if (tn.Text == frmLogin.Server)
{
root = tn;
break;
}
}
// If not found, add new.
if (null == root)
{
root = _treeView.Nodes.Add(frmLogin.Server);
}
root.Tag = _connection;
root.ImageIndex = TreeImgNdx.Server;
RefreshTreeTables();
_tsbNewQuery.Enabled = true;
_tsbOpenFile.Enabled = true;
_tsbSave.Enabled = true;
_tsbRefreshTree.Enabled = true;
if (_tabControl.TabPages.Count == 0)
{
_tsbNewQuery_Click(_tsbNewQuery, new EventArgs());
}
}
}
catch (Exception ex)
{
HandleException(ex);
}
finally
{
UpdateBusyWaitState(false, null);
}
}
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 ConnectionConstructorBasicOpenDisposeTest()
{
using (PhoenixConnection c = new PhoenixConnection(this.ConnectionString()))
{
c.Open();
TestContext.WriteLine("ConnectionId: {0}", c.ConnectionId);
Assert.AreEqual <ConnectionState>(ConnectionState.Open, c.State);
Assert.IsNotNull(c.ConnectionId);
Assert.IsNotNull(c.ConnectionString);
}
}
public QueryView(MainForm mainForm, string connectionString, FileInfo fileInfo)
{
InitializeComponent();
_mainForm = mainForm;
_connectionString = connectionString;
_connection = new PhoenixConnection(_connectionString);
_fileInfo = fileInfo;
if (null != _fileInfo)
{
this.Text = System.IO.File.ReadAllText(this._fileInfo.FullName);
}
this.Dock = DockStyle.Fill;
}
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 void CommandExplain()
{
using (PhoenixConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
using (PhoenixCommand cmd = new PhoenixCommand(c))
{
cmd.CommandText = "SELECT * FROM BIGTABLE WHERE ID < 1000";
DataTable dt = cmd.Explain();
Assert.IsNotNull(dt);
}
}
}
public void CommandExecuteScalar()
{
using (PhoenixConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
using (PhoenixCommand cmd = new PhoenixCommand(c))
{
cmd.CommandText = "SELECT COUNT(*) FROM BIGTABLE WHERE ID < 1000";
object oVal = cmd.ExecuteScalar();
Assert.IsNotNull(oVal);
Assert.IsInstanceOfType(oVal, typeof(long));
}
}
}
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 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);
}
public void ExecuteNonQueryUpsert1KBigTable()
{
int rowsToInsert = 1000;
List <Func <object> > pFuncs = new List <Func <object> >();
pFuncs.Add(() => string.Format("N{0}", DateTime.Now.ToString("hmmss")));
pFuncs.Add(() => Guid.NewGuid().ToString());
pFuncs.Add(() => DateTime.Now);
using (IDbConnection c = new PhoenixConnection())
{
c.ConnectionString = this.ConnectionString();
c.Open();
CreateBigTestTableIfNotExists(c, false);
}
PreparedCmdParameterTest(rowsToInsert,
"UPSERT INTO bigtable (ID, AircraftIcaoNumber, LruFlightKey, MyTimestamp) VALUES (NEXT VALUE FOR garuda.bigtableSequence, :1, :2, :3)",
pFuncs, false);
}
private void _btnOk_Click(object sender, EventArgs e)
{
try
{
StringBuilder conStr = new StringBuilder();
conStr.AppendFormat("server={0};", _cbServer.Text);
switch ((_cbMode.SelectedItem as ModeItem).Key)
{
case PhoenixConnectionModeStr.HdiGateway:
conStr.AppendFormat("User Id={0};", _txtUserId.Text);
conStr.AppendFormat("Password={0};", _txtPasswd.Text);
conStr.AppendFormat("Mode={0};", PhoenixConnectionModeStr.HdiGateway);
break;
case PhoenixConnectionModeStr.Vnet:
//conStr.AppendFormat("Mode:{0};", "VNET")
break;
}
conStr.Append("Request Timeout=30000;");
this.Server = _cbServer.Text;
this.Connection = new PhoenixConnection();
this.Connection.ConnectionString = conStr.ToString();
this.Connection.Open();
if (this.Connection.State == ConnectionState.Open)
{
AddServerToListIfNotExists();
}
this.DialogResult = DialogResult.OK;
}
catch (Exception ex)
{
HandleException(ex);
}
}
/// <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);
}
}
}
}
