private static DataTable GetDataTable(GFXDClientConnection connection, GFXDDataAdapter adapter)
{
DataTable dt = new DataTable();
adapter.Fill(dt);
return(dt);
}
public void LoadSalesTerritoryData(int key, string value)
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query;
if (String.IsNullOrEmpty(value))
{
query = "select TerritoryID, Name from Sales.SalesTerritory order by Name";
}
else
{
query = "select TerritoryID, Name from Sales.SalesTerritory where TerritoryID = " +
key.ToString() + " order by Name";
}
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
_dt.Clear();
da.Fill(_dt);
LoadSalesTerritoryList();
}
}
private static DataSet GetDataSet(GFXDClientConnection connection, GFXDDataAdapter adapter)
{
DataSet ds = new DataSet();
adapter.Fill(ds);
return(ds);
}
public void DeleteBulkData(string tableName)
{
try
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
string[] words = tableName.Split('.');
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select MAX(" + words[1] + "ID) from " + tableName;
conn.Open();
int prodCount = Convert.ToInt32(cmd.ExecuteScalar());
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Delete from " + tableName + " where " + words[1] + "ID between ? and ?";
// Insert order_date value and add to command’s Parameters collection
GFXDParameter param1 = cmd.CreateParameter();
param1.DbType = DbType.Int32;
param1.Value = (prodCount - 1000);
cmd.Parameters.Add(param1);
// Insert subtotal value add to command’s Parameters collection
GFXDParameter param2 = cmd.CreateParameter();
param2.DbType = DbType.Int32;
param2.Value = prodCount;
cmd.Parameters.Add(param2);
var da = new GFXDDataAdapter(cmd);
var dt = new DataTable(tableName);
da.Fill(dt);
//DataTable dt = new DataTable();
//var adapter = new GFXDDataAdapter();
//cmd.CommandText = "SELECT * FROM Sales.SalesReason";
//adapter.SelectCommand = cmd;
//DbCommandBuilder builder = new GFXDCommandBuilder(adapter);
//adapter.Fill(dt);
//for (int i = 500; i < 1000; i++)
//{
// dt.Rows[i].Delete();
//}
//adapter.Update(dt);
}
}
catch (GFXDException ex)
{
string err = ex.Message;
}
catch (Exception ex)
{
string err = ex.Message;
}
}
public void LoadColorData()
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query =
"select Distinct Color from production.product p" +
" join production.productsubcategory ps on ps.ProductSubCategoryID = p.ProductSubCategoryID" +
" where ps.name = '" + SubCategorySelected + "' order by Color";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
_dt.Clear();
// need to add an empty row for deselecting currently selected item.
_dt.Rows.Add();
_dt.AcceptChanges();
//
da.Fill(_dt);
LoadColorList();
}
}
public void LoadSalesTerritoryData(int key, string value)
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query;
if (String.IsNullOrEmpty(value))
{
query = "select TerritoryID, Name from Sales.SalesTerritory order by Name";
}
else
{
query = "select TerritoryID, Name from Sales.SalesTerritory where TerritoryID = " +
key.ToString() + " order by Name";
}
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
_dt.Clear();
da.Fill(_dt);
LoadSalesTerritoryList();
}
}
public override void Run(object context)
{
try
{
String providerName = Configuration.GFXDConfigManager.GetAppSetting("dbProvider");
DbProviderFactory factory = DbProviderFactories.GetFactory(providerName);
GFXDClientConnection conn = (GFXDClientConnection)factory.CreateConnection();
GFXDDataAdapter adapter = (GFXDDataAdapter)factory.CreateDataAdapter();
conn.Open();
///
/// Add test logic here
///
conn.Close();
}
catch (Exception e)
{
Log(e);
}
finally
{
base.Run(context);
}
}
public DataSet ExecuteQuery(string query)
{
var dt = new DataSet();
try
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
da.Fill(dt);
}
}
catch (GFXDException ex)
{
string err = ex.Message;
}
catch (Exception ex)
{
string err = ex.Message;
}
return(dt);
}
public DataSet ExecuteQuery(string query)
{
var dt = new DataSet();
try
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
da.Fill(dt);
}
}
catch (GFXDException ex)
{
string err = ex.Message;
}
catch (Exception ex)
{
string err = ex.Message;
}
return dt;
}
public static object Select(GFXDClientConnection connection, string sql, QueryTypes type)
{
GFXDCommand command = connection.CreateCommand();
command.CommandType = System.Data.CommandType.Text;
command.CommandText = sql;
if (connection.IsClosed)
{
connection.Open();
}
if (type == QueryTypes.SCALAR)
{
return(command.ExecuteScalar());
}
using (GFXDDataAdapter adapter = command.CreateDataAdapter())
{
switch (type)
{
case QueryTypes.DATAROW: return(GetDataRow(connection, adapter));
case QueryTypes.DATATABLE: return(GetDataTable(connection, adapter));
case QueryTypes.DATASET: return(GetDataSet(connection, adapter));
default: return(null);
}
}
}
public static DataTable GetDataTable(GFXDClientConnection conn, String statement)
{
DataTable table = new DataTable();
GFXDCommand cmd = new GFXDCommand(statement, conn);
GFXDDataAdapter adpt = cmd.CreateDataAdapter();
adpt.Fill(table);
return(table);
}
public void DataAdapterBatch()
{
// Open a new connection to the network server running on localhost
string host = "localhost";
int port = s_clientPort;
string connStr = string.Format("server={0}:{1}", host, port);
using (GFXDClientConnection conn = new GFXDClientConnection(connStr)) {
conn.Open();
// create a table
// using the base DbCommand class rather than GemFireXD specific class
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "create table t1 (id int primary key," +
" addr varchar(20))";
cmd.ExecuteNonQuery();
try {
// populate DataTable from the underlying table
cmd = conn.CreateCommand();
cmd.CommandText = "select * from t1";
DbDataAdapter adapter = new GFXDDataAdapter((GFXDCommand)cmd);
adapter.SelectCommand = cmd;
// associate a command builder with the DataAdapter
new GFXDCommandBuilder((GFXDDataAdapter)adapter);
// fill a DataTable using the above select SQL command
// though there is no data to be populated yet, the schema will still be
// set correctly in the DataTable even with no data which is required
// before trying to make any updates
DataTable table = new DataTable();
adapter.Fill(table);
// set batch size for best performance
adapter.UpdateBatchSize = s_numInserts;
// now perform the inserts in the DataTable
for (int i = 0; i < s_numInserts; i++)
{
DataRow newRow = table.NewRow();
newRow["ID"] = i;
newRow["ADDR"] = "addr" + i;
table.Rows.Add(newRow);
}
// apply the inserts to the underlying GemFireXD table
adapter.Update(table);
// check the inserts
VerifyInserts(conn, s_numInserts);
} finally {
// drop the table
cmd = new GFXDCommand("drop table t1", conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
private static DataRow GetDataRow(GFXDClientConnection connection, GFXDDataAdapter adapter)
{
DataTable dt = GetDataTable(connection, adapter);
DataRow dr = null;
if (dt.Rows.Count > 0)
{
dr = dt.Rows[0];
}
return(dr);
}
public static DataTable SelectRandom(GFXDClientConnection connection, TableName tableName, int numRows)
{
GFXDCommand command = connection.CreateCommand();
command.CommandType = System.Data.CommandType.Text;
command.CommandText = String.Format(
"SELECT * FROM {0} ORDER BY RANDOM() FETCH FIRST {1} ROWS ONLY",
tableName.ToString(), numRows);
using (GFXDDataAdapter adapter = command.CreateDataAdapter())
{
return(GetDataTable(connection, adapter));
}
}
public void InsertBulkData(string tableName)
{
try
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
string[] words = tableName.Split('.');
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select * from " + tableName + " order by " + words[1] + "ID";
conn.Open();
GFXDDataAdapter adapter = cmd.CreateDataAdapter();
var table = new DataTable(tableName);
adapter.Fill(table);
int cnt = table.Rows.Count;
var idx = (int)table.Rows[cnt - 1].ItemArray[0];
var builder = new GFXDCommandBuilder(adapter);
adapter.InsertCommand = builder.GetInsertCommand();
// Create new product row
for (int ctx = 0; ctx < 10000; ctx++)
{
DataRow row = table.NewRow();
row[0] = ++idx;
for (int i = 1; i < (table.Rows[cnt - 1].ItemArray.Count()); i++)
{
row[i] = table.Rows[cnt - 1].ItemArray[i];
}
table.Rows.Add(row);
}
// Update the underlying table
adapter.Update(table);
}
}
catch (GFXDException ex)
{
string err = ex.Message;
}
catch (Exception ex)
{
string err = ex.Message;
}
}
public void LoadTableRowListData(string table)
{
using (GFXDClientConnection conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query =
"SELECT * FROM " + table;
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
GFXDDataAdapter da = new GFXDDataAdapter(cmd);
_dtTableDataRows.Clear();
da.Fill(_dtTableDataRows);
}
}
public void LoadTableRowListData(string table)
{
using (GFXDClientConnection conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query =
"SELECT * FROM " + table;
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
GFXDDataAdapter da = new GFXDDataAdapter(cmd);
_dtTableDataRows.Clear();
da.Fill(_dtTableDataRows);
}
}
public override void Run(Object context)
{
String tableName = null;
int numRows = 10;
try
{
tableName = DbRandom.BuildRandomTable(numRows);
GFXDConnection conn = Connection.Clone();
conn.Open();
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM " + tableName;
DataTable dt = new DataTable();
GFXDDataAdapter adpt = cmd.CreateDataAdapter();
adpt.Fill(dt);
if (dt.Rows.Count != numRows)
{
Fail("Failed to create table new table on cloned connection");
}
DbHelper.ExecuteNonQueryStatement((GFXDClientConnection)conn, "DELETE FROM " + tableName);
dt.Clear();
adpt.Fill(dt);
if (dt.Rows.Count != 0)
{
Fail("Failed to delete all table record on a cloned connection");
}
}
catch (Exception e)
{
}
finally
{
DbHelper.DropTable(tableName);
base.Run(context);
}
}
public void UpdateBulkData(string tableName)
{
try
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
//GFXDCommand cmd = conn.CreateCommand();
//cmd.CommandType = CommandType.Text;
//cmd.CommandText = "Delete from Sales.SalesReason where SalesReasonID between 17000 and 19485";
//var da = new GFXDDataAdapter(cmd);
//var dt = new DataTable();
//da.Fill(dt);
GFXDCommand cmd = conn.CreateCommand();
DataTable dt = new DataTable();
var adapter = new GFXDDataAdapter();
cmd.CommandText = "SELECT * FROM Sales.SalesReason";
adapter.SelectCommand = cmd;
DbCommandBuilder builder = new GFXDCommandBuilder(adapter);
adapter.Fill(dt);
for (int i = 500; i < 1000; i++)
{
dt.Rows[i].Delete();
}
adapter.Update(dt);
}
}
catch (GFXDException ex)
{
string err = ex.Message;
}
catch (Exception ex)
{
string err = ex.Message;
}
}
public void LoadTableRowListData(string table)
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string[] words = table.Split('.');
string query =
"SELECT * FROM " + table + " order by " + words[1] + "ID";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
_dtTableDataRows.Clear();
da.Fill(_dtTableDataRows);
}
}
public void LoadProductSubCategoryData()
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query =
"select ProductSubCategoryID, Name from production.productsubcategory where productcategoryid = " + CategoryIdSelected.ToString() + " order by Name";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
_dt.Clear();
da.Fill(_dt);
LoadSubCategoryList();
}
}
public void LoadTableRowListData(string table)
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string[] words = table.Split('.');
string query =
"SELECT * FROM " + table + " order by " + words[1] + "ID";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
_dtTableDataRows.Clear();
da.Fill(_dtTableDataRows);
}
}
public void LoadProductSubCategoryData()
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query =
"select ProductSubCategoryID, Name from production.productsubcategory where productcategoryid = " + CategoryIdSelected.ToString() + " order by Name";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
_dt.Clear();
da.Fill(_dt);
LoadSubCategoryList();
}
}
//public void LoadTableListData(string schema)
public void LoadTableListData()
{
using (GFXDClientConnection conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query =
"SELECT DISTINCT B.SCHEMANAME, A.TABLENAME FROM SYS.SYSTABLES A INNER JOIN SYS.SYSSCHEMAS B ON A.SCHEMAID = B.SCHEMAID WHERE A.TABLETYPE = 'T' ORDER BY B.SCHEMANAME ASC";
//"SELECT ROW_NUMBER() OVER(ORDER BY TABLE_NAME) AS rownum, TABLE_NAME as name FROM AdventureWorks.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + schema + "' and TABLE_NAME not like 'v%'";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
GFXDDataAdapter da = new GFXDDataAdapter(cmd);
_dtTableList.Clear();
da.Fill(_dtTableList);
LoadTableList();
}
}
public void LoadSchemaListData()
{
// load the connection string from the configuration files
_connectionString = Settings.Default.AdventureWorksConnectionString;
using (GFXDClientConnection conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query =
"SELECT DISTINCT B.SCHEMANAME, A.TABLENAME FROM SYS.SYSTABLES A INNER JOIN SYS.SYSSCHEMAS B ON A.SCHEMAID = B.SCHEMAID WHERE A.TABLETYPE = 'T' ORDER BY B.SCHEMANAME ASC";
//"select ROW_NUMBER() OVER(ORDER BY schema_id) AS rownum, 'AW.' + name as name FROM AdventureWorks.sys.schemas where schema_id between 5 and 9";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
GFXDDataAdapter da = new GFXDDataAdapter(cmd);
//_dtSchemaList.Clear();
da.Fill(_dtSchemaList);
}
}
public override void Run(Object context)
{
int numCommands = 10;
GFXDCommand[] commands = new GFXDCommand[numCommands];
try
{
int rowCount = DbHelper.GetTableRowCount("Product");
for (int i = 0; i < numCommands; i++)
{
commands[i] = Connection.CreateCommand();
commands[i].CommandText = "SELECT * FROM Product";
if (commands[i] == null)
{
Fail("Failed to create GFXDCommand from Connection object.");
}
DataTable dt = new DataTable();
GFXDDataAdapter adpt = commands[i].CreateDataAdapter();
adpt.Fill(dt);
if (dt.Rows.Count != rowCount)
{
Fail("Failed to retrieve all records from table");
}
}
}
catch (Exception e)
{
Fail(e);
}
finally
{
base.Run(context);
}
}
public void LoadCustomerData(int key, string value)
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query;
if (String.IsNullOrEmpty(value))
{
query = "select cus.customerid, c.firstname, c.middlename, c.lastname from person.contact c " +
"join sales.individual i on i.contactid = c.contactid join sales.customer cus on " +
"cus.customerid = i.customerid order by cus.customerid";
}
else
{
query = "select cus.customerid, c.firstname, c.middlename, c.lastname from person.contact c " +
"join sales.individual i on i.contactid = c.contactid join sales.customer cus on " +
"cus.customerid = i.customerid where cus.territoryid = " +
key.ToString() + " order by cus.customerid";
}
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
_dt.Clear();
// need to add an empty row for deselecting currently selected item.
//_dt.Rows.Add();
//_dt.AcceptChanges();
da.Fill(_dt);
LoadCustomerList();
}
}
public void BuildQueryAndExecute()
{
string query = string.Empty;
try
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
query =
"select * from production.product p join production.productsubcategory ps on" +
" ps.ProductSubCategoryID = p.ProductSubCategoryID where ps.name = '" +
SubCategorySelected + "'";
if (!string.IsNullOrEmpty(ColorSelected))
{
query += " AND p.Color = '" + ColorSelected + "'";
}
if (!string.IsNullOrEmpty(SellStartDateSelected))
{
query += " AND p.SellStartDate >= CAST ('" + SellStartDateSelected + "' as TIMESTAMP)";
}
if ((!string.IsNullOrEmpty(ListPriceGTSelected)) || (!string.IsNullOrEmpty(ListPriceLTSelected)))
{
if ((!string.IsNullOrEmpty(ListPriceGTSelected)) && (!string.IsNullOrEmpty(ListPriceLTSelected)))
{
query += " AND p.ListPrice BETWEEN " + ListPriceGTSelected + " AND " + ListPriceLTSelected;
}
else if (!string.IsNullOrEmpty(ListPriceGTSelected))
{
query += " AND p.ListPrice > " + ListPriceGTSelected;
}
else if (!string.IsNullOrEmpty(ListPriceLTSelected))
{
query += " AND p.ListPrice < " + ListPriceLTSelected;
}
}
query += " order by p.ProductId";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
_dtTableDataRows.Clear();
da.Fill(_dtTableDataRows);
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (!MainWindow.PeristedQList.Contains(query))
{
MainWindow.PeristedQList.Add(query);
}
}
}
public void LoadCustomerData(int key, string value)
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query;
if (String.IsNullOrEmpty(value))
{
query = "select cus.customerid, c.firstname, c.middlename, c.lastname from person.contact c " +
"join sales.individual i on i.contactid = c.contactid join sales.customer cus on " +
"cus.customerid = i.customerid order by cus.customerid";
}
else
{
query = "select cus.customerid, c.firstname, c.middlename, c.lastname from person.contact c " +
"join sales.individual i on i.contactid = c.contactid join sales.customer cus on " +
"cus.customerid = i.customerid where cus.territoryid = " +
key.ToString() + " order by cus.customerid";
}
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
_dt.Clear();
// need to add an empty row for deselecting currently selected item.
//_dt.Rows.Add();
//_dt.AcceptChanges();
da.Fill(_dt);
LoadCustomerList();
}
}
private static DataRow GetDataRow(GFXDClientConnection connection, GFXDDataAdapter adapter)
{
DataTable dt = GetDataTable(connection, adapter);
DataRow dr = null;
if (dt.Rows.Count > 0)
dr = dt.Rows[0];
return dr;
}
public override void Run(object context)
{
int updateSize = 5;
DataTable[] tables = new DataTable[updateSize];
String[] tableNames = new String[updateSize];
GFXDClientConnection[] conns = new GFXDClientConnection[updateSize];
GFXDCommand[] cmds = new GFXDCommand[updateSize];
GFXDDataAdapter[] adpts = new GFXDDataAdapter[updateSize];
try
{
for (int i = 0; i < updateSize; i++)
{
tableNames[i] = DbRandom.BuildRandomTable(5);
conns[i] = new GFXDClientConnection(ConnectionString);
cmds[i] = conns[i].CreateCommand();
cmds[i].CommandText = String.Format(
"SELECT * FROM {0} ORDER BY COL_ID ASC ", tableNames[i]);
conns[i].Open();
conns[i].AutoCommit = false;
conns[i].BeginGFXDTransaction();
adpts[i] = cmds[i].CreateDataAdapter();
tables[i] = new DataTable();
adpts[i].Fill(tables[i]);
ParseDataTable(tables[i]);
}
IList <object> data = DbRandom.GetRandomRowData();
for (int i = 0; i < updateSize; i++)
{
CommandBuilder = new GFXDCommandBuilder(adpts[i]);
for (int j = 0; j < tables[i].Rows.Count; j++)
{
for (int k = 1; k < tables[i].Columns.Count; k++)
{
tables[i].Rows[j][k] = data[k];
}
}
if (adpts[i].Update(tables[i]) != tables[i].Rows.Count)
{
Fail(String.Format(
"Failed to update table {0}", tableNames[i]));
}
try
{
conns[i].Commit();
}
catch (Exception e)
{
conns[i].Rollback();
Fail(e);
}
}
for (int i = 0; i < updateSize; i++)
{
tables[i].Clear();
adpts[i].Fill(tables[i]);
}
foreach (DataTable table in tables)
{
foreach (DataRow row in table.Rows)
{
for (int i = 1; i < row.Table.Columns.Count; i++)
{
if (!DbRandom.Compare(data[i], row, i))
{
Fail(String.Format(
"Inconsistent updated data in table [{0}] at row [{1}] column [{2}]. "
+ "Expected [{3}]; Actual [{4}]",
table.TableName,
row[0].ToString(), row.Table.Columns[i].ColumnName,
data[i].ToString(), row[i].ToString()));
}
}
}
}
}
catch (Exception e)
{
Fail(e);
}
finally
{
foreach (String tableName in tableNames)
{
DbRandom.DropTable(tableName);
}
base.Run(context);
}
}
public void DataAdapterBatch()
{
// Open a new connection to the network server running on localhost
string host = "localhost";
int port = s_clientPort;
string connStr = string.Format("server={0}:{1}", host, port);
using (GFXDClientConnection conn = new GFXDClientConnection(connStr)) {
conn.Open();
// create a table
// using the base DbCommand class rather than GemFireXD specific class
DbCommand cmd = conn.CreateCommand();
cmd.CommandText = "create table t1 (id int primary key," +
" addr varchar(20))";
cmd.ExecuteNonQuery();
try {
// populate DataTable from the underlying table
cmd = conn.CreateCommand();
cmd.CommandText = "select * from t1";
DbDataAdapter adapter = new GFXDDataAdapter((GFXDCommand)cmd);
adapter.SelectCommand = cmd;
// associate a command builder with the DataAdapter
new GFXDCommandBuilder((GFXDDataAdapter)adapter);
// fill a DataTable using the above select SQL command
// though there is no data to be populated yet, the schema will still be
// set correctly in the DataTable even with no data which is required
// before trying to make any updates
DataTable table = new DataTable();
adapter.Fill(table);
// set batch size for best performance
adapter.UpdateBatchSize = s_numInserts;
// now perform the inserts in the DataTable
for (int i = 0; i < s_numInserts; i++) {
DataRow newRow = table.NewRow();
newRow["ID"] = i;
newRow["ADDR"] = "addr" + i;
table.Rows.Add(newRow);
}
// apply the inserts to the underlying GemFireXD table
adapter.Update(table);
// check the inserts
VerifyInserts(conn, s_numInserts);
} finally {
// drop the table
cmd = new GFXDCommand("drop table t1", conn);
cmd.ExecuteNonQuery();
conn.Close();
}
}
}
public override void Run(object context)
{
int dsSize = 10;
DataSet dataset = new DataSet();
String[] tableNames = new String[dsSize];
GFXDCommand[] commands = new GFXDCommand[dsSize];
GFXDDataAdapter[] adapters = new GFXDDataAdapter[dsSize];
try
{
for (int i = 0; i < dsSize; i++)
{
tableNames[i] = DbRandom.BuildRandomTable(5);
commands[i] = Connection.CreateCommand();
commands[i].CommandText = String.Format(
"SELECT * FROM {0} ORDER BY COL_ID ASC ", tableNames[i]);
adapters[i] = commands[i].CreateDataAdapter();
adapters[i].Fill(dataset, tableNames[i]);
}
ParseDataSet(dataset);
IList <object> data = DbRandom.GetRandomRowData();
for (int i = 0; i < dsSize; i++)
{
CommandBuilder = new GFXDCommandBuilder(adapters[i]);
for (int j = 0; j < dataset.Tables[tableNames[i]].Rows.Count; j++)
{
for (int k = 1; k < dataset.Tables[tableNames[i]].Columns.Count; k++) // do not update identity column
{
dataset.Tables[tableNames[i]].Rows[j][k] = data[k];
}
}
if (adapters[i].Update(dataset, tableNames[i])
!= dataset.Tables[tableNames[i]].Rows.Count)
{
Fail(String.Format(
"Failed to update all changed rows in table {0}", tableNames[i]));
}
}
dataset.Clear();
for (int i = 0; i < dsSize; i++)
{
adapters[i].Fill(dataset, tableNames[i]);
}
ParseDataSet(dataset);
foreach (DataTable table in dataset.Tables)
{
foreach (DataRow row in table.Rows)
{
for (int i = 1; i < row.Table.Columns.Count; i++)
{
if (!DbRandom.Compare(data[i], row, i))
{
Fail(String.Format(
"Inconsistent updated data in table [{0}] at row [{1}] column [{2}]. "
+ "Expected [{3}]; Actual [{4}]",
table.TableName,
row[0].ToString(), row.Table.Columns[i].ColumnName,
data[i].ToString(), row[i].ToString()));
}
}
}
}
}
catch (Exception e)
{
Fail(e);
}
finally
{
try
{
foreach (String tableName in tableNames)
{
DbRandom.DropTable(tableName);
}
}
catch (Exception e)
{
Fail(e);
}
base.Run(context);
}
}
public override void Run(object context)
{
int dsSize = 10;
DataSet dataset = new DataSet();
String[] tableNames = new String[dsSize];
GFXDCommand[] commands = new GFXDCommand[dsSize];
GFXDDataAdapter[] adapters = new GFXDDataAdapter[dsSize];
try
{
for (int i = 0; i < dsSize; i++)
{
tableNames[i] = DbRandom.BuildRandomTable(5);
commands[i] = Connection.CreateCommand();
commands[i].CommandText = String.Format(
"SELECT * FROM {0} ORDER BY COL_ID ASC ", tableNames[i]);
adapters[i] = commands[i].CreateDataAdapter();
adapters[i].Fill(dataset, tableNames[i]);
}
ParseDataSet(dataset);
IList<object> data = DbRandom.GetRandomRowData();
for (int i = 0; i < dsSize; i++)
{
CommandBuilder = new GFXDCommandBuilder(adapters[i]);
for (int j = 0; j < dataset.Tables[tableNames[i]].Rows.Count; j++)
for (int k = 1; k < dataset.Tables[tableNames[i]].Columns.Count; k++) // do not update identity column
dataset.Tables[tableNames[i]].Rows[j][k] = data[k];
if (adapters[i].Update(dataset, tableNames[i])
!= dataset.Tables[tableNames[i]].Rows.Count)
Fail(String.Format(
"Failed to update all changed rows in table {0}", tableNames[i]));
}
dataset.Clear();
for (int i = 0; i < dsSize; i++)
adapters[i].Fill(dataset, tableNames[i]);
ParseDataSet(dataset);
foreach (DataTable table in dataset.Tables)
{
foreach (DataRow row in table.Rows)
{
for (int i = 1; i < row.Table.Columns.Count; i++)
{
if (!DbRandom.Compare(data[i], row, i))
{
Fail(String.Format(
"Inconsistent updated data in table [{0}] at row [{1}] column [{2}]. "
+ "Expected [{3}]; Actual [{4}]",
table.TableName,
row[0].ToString(), row.Table.Columns[i].ColumnName,
data[i].ToString(), row[i].ToString()));
}
}
}
}
}
catch (Exception e)
{
Fail(e);
}
finally
{
try
{
foreach(String tableName in tableNames)
DbRandom.DropTable(tableName);
}
catch (Exception e)
{
Fail(e);
}
base.Run(context);
}
}
public override void Run(object context)
{
GFXDDataAdapter adapter = null;
Command.CommandText = DbDefault.GetAddressQuery();
try
{
adapter = new GFXDDataAdapter(DbDefault.GetAddressQuery(), Connection);
adapter.SelectCommand = Command;
if (adapter.SelectCommand != Command)
{
Fail("SelectCommand does not reference Command object.");
}
if (adapter.SelectCommand.CommandText != Command.CommandText)
{
Fail("SelectCommand.CommandText does not reference Command.CommandText");
}
if (adapter.SelectCommand.Connection != Connection)
{
Fail("SelectCommand.Connection does not reference Connection object");
}
if (!adapter.AcceptChangesDuringFill)
{
Fail(String.Format("AcceptChangesDuringFill: "
+ "Expected [{0}]; Actual [{1}]",
true, adapter.AcceptChangesDuringFill));
}
if (!adapter.AcceptChangesDuringUpdate)
{
Fail(String.Format("AcceptChangesDuringUpdate: "
+ "Expected [{0}]; Actual [{1}]",
true, adapter.AcceptChangesDuringUpdate));
}
if (adapter.ContinueUpdateOnError)
{
Fail(String.Format("ContinueUpdateOnError: "
+ "Expected [{0}]; Actual [{1}]",
false, adapter.ContinueUpdateOnError));
}
if (adapter.FillLoadOption != LoadOption.OverwriteChanges)
{
Fail(String.Format("FillLoadOption: "
+ "Expected [{0}]; Actual[{1}]",
LoadOption.OverwriteChanges, adapter.FillLoadOption));
}
if (adapter.MissingMappingAction != MissingMappingAction.Passthrough)
{
Fail(String.Format("MissingMappingAction: "
+ "Expected [{0}]; Actual [{1}]",
MissingMappingAction.Passthrough, adapter.MissingMappingAction));
}
if (adapter.MissingSchemaAction != MissingSchemaAction.Add)
{
Fail(String.Format("MissingSchemaAction: "
+ "Expected [{0}]; Actual [{1}]",
MissingSchemaAction.Add, adapter.MissingSchemaAction));
}
if (adapter.ReturnProviderSpecificTypes)
{
Fail(String.Format("ReturnProviderSpecificTypes: "
+ "Expected [{0}]; Actual [{1}]",
false, adapter.ReturnProviderSpecificTypes));
}
if (adapter.UpdateBatchSize != 1)
{
Fail(String.Format("UpdateBatchSize: "
+ "Expected [{0}]; Actual [{1}]",
1, adapter.UpdateBatchSize));
}
}
catch (Exception e)
{
Fail(e);
}
finally
{
base.Run(context);
}
}
public void LoadColorData()
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query =
"select Distinct Color from production.product p" +
" join production.productsubcategory ps on ps.ProductSubCategoryID = p.ProductSubCategoryID" +
" where ps.name = '" + SubCategorySelected + "' order by Color";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
_dt.Clear();
// need to add an empty row for deselecting currently selected item.
_dt.Rows.Add();
_dt.AcceptChanges();
//
da.Fill(_dt);
LoadColorList();
}
}
public void DeleteBulkData(string tableName)
{
try
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
string[] words = tableName.Split('.');
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Select MAX(" + words[1] + "ID) from " + tableName;
conn.Open();
int prodCount = Convert.ToInt32(cmd.ExecuteScalar());
cmd.CommandType = CommandType.Text;
cmd.CommandText = "Delete from " + tableName + " where " + words[1] + "ID between ? and ?";
// Insert order_date value and add to command’s Parameters collection
GFXDParameter param1 = cmd.CreateParameter();
param1.DbType = DbType.Int32;
param1.Value = (prodCount - 1000);
cmd.Parameters.Add(param1);
// Insert subtotal value add to command’s Parameters collection
GFXDParameter param2 = cmd.CreateParameter();
param2.DbType = DbType.Int32;
param2.Value = prodCount;
cmd.Parameters.Add(param2);
var da = new GFXDDataAdapter(cmd);
var dt = new DataTable(tableName);
da.Fill(dt);
//DataTable dt = new DataTable();
//var adapter = new GFXDDataAdapter();
//cmd.CommandText = "SELECT * FROM Sales.SalesReason";
//adapter.SelectCommand = cmd;
//DbCommandBuilder builder = new GFXDCommandBuilder(adapter);
//adapter.Fill(dt);
//for (int i = 500; i < 1000; i++)
//{
// dt.Rows[i].Delete();
//}
//adapter.Update(dt);
}
}
catch (GFXDException ex)
{
string err = ex.Message;
}
catch (Exception ex)
{
string err = ex.Message;
}
}
public void UpdateBulkData(string tableName)
{
try
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
//GFXDCommand cmd = conn.CreateCommand();
//cmd.CommandType = CommandType.Text;
//cmd.CommandText = "Delete from Sales.SalesReason where SalesReasonID between 17000 and 19485";
//var da = new GFXDDataAdapter(cmd);
//var dt = new DataTable();
//da.Fill(dt);
GFXDCommand cmd = conn.CreateCommand();
DataTable dt = new DataTable();
var adapter = new GFXDDataAdapter();
cmd.CommandText = "SELECT * FROM Sales.SalesReason";
adapter.SelectCommand = cmd;
DbCommandBuilder builder = new GFXDCommandBuilder(adapter);
adapter.Fill(dt);
for (int i = 500; i < 1000; i++)
{
dt.Rows[i].Delete();
}
adapter.Update(dt);
}
}
catch (GFXDException ex)
{
string err = ex.Message;
}
catch (Exception ex)
{
string err = ex.Message;
}
}
public void LoadSchemaListData()
{
// load the connection string from the configuration files
_connectionString = Settings.Default.AdventureWorksConnectionString;
using (GFXDClientConnection conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query =
"SELECT DISTINCT B.SCHEMANAME, A.TABLENAME FROM SYS.SYSTABLES A INNER JOIN SYS.SYSSCHEMAS B ON A.SCHEMAID = B.SCHEMAID WHERE A.TABLETYPE = 'T' ORDER BY B.SCHEMANAME ASC";
//"select ROW_NUMBER() OVER(ORDER BY schema_id) AS rownum, 'AW.' + name as name FROM AdventureWorks.sys.schemas where schema_id between 5 and 9";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
GFXDDataAdapter da = new GFXDDataAdapter(cmd);
//_dtSchemaList.Clear();
da.Fill(_dtSchemaList);
}
}
public override void Run(object context)
{
GFXDDataAdapter adapter = null;
Command.CommandText = DbDefault.GetAddressQuery();
try
{
adapter = new GFXDDataAdapter(Command);
if (adapter.SelectCommand != Command)
{
Fail("SelectCommand does not reference Command object.");
}
if (adapter.SelectCommand.CommandText != Command.CommandText)
{
Fail("SelectCommand.CommandText does not reference Command.CommandText");
}
if (adapter.SelectCommand.Connection != Connection)
{
Fail("SelectCommand.Connection does not reference Connection object");
}
if (!adapter.AcceptChangesDuringFill)
{
Fail(String.Format("AcceptChangesDuringFill: "
+ "Expected [{0}]; Actual [{1}]",
true, adapter.AcceptChangesDuringFill));
}
if (!adapter.AcceptChangesDuringUpdate)
{
Fail(String.Format("AcceptChangesDuringUpdate: "
+ "Expected [{0}]; Actual [{1}]",
true, adapter.AcceptChangesDuringUpdate));
}
if (adapter.ContinueUpdateOnError)
{
Fail(String.Format("ContinueUpdateOnError: "
+ "Expected [{0}]; Actual [{1}]",
false, adapter.ContinueUpdateOnError));
}
if (adapter.FillLoadOption != LoadOption.OverwriteChanges)
{
Fail(String.Format("FillLoadOption: "
+ "Expected [{0}]; Actual[{1}]",
LoadOption.OverwriteChanges, adapter.FillLoadOption));
}
if (adapter.MissingMappingAction != MissingMappingAction.Passthrough)
{
Fail(String.Format("MissingMappingAction: "
+ "Expected [{0}]; Actual [{1}]",
MissingMappingAction.Passthrough, adapter.MissingMappingAction));
}
if (adapter.MissingSchemaAction != MissingSchemaAction.Add)
{
Fail(String.Format("MissingSchemaAction: "
+ "Expected [{0}]; Actual [{1}]",
MissingSchemaAction.Add, adapter.MissingSchemaAction));
}
if (adapter.ReturnProviderSpecificTypes)
{
Fail(String.Format("ReturnProviderSpecificTypes: "
+ "Expected [{0}]; Actual [{1}]",
false, adapter.ReturnProviderSpecificTypes));
}
if (adapter.UpdateBatchSize != 1)
{
Fail(String.Format("UpdateBatchSize: "
+ "Expected [{0}]; Actual [{1}]",
1, adapter.UpdateBatchSize));
}
}
catch (Exception e)
{
Fail(e);
}
finally
{
base.Run(context);
}
}
private static DataSet GetDataSet(GFXDClientConnection connection, GFXDDataAdapter adapter)
{
DataSet ds = new DataSet();
adapter.Fill(ds);
return ds;
}
public override void Run(object context)
{
int updateSize = 5;
DataTable[] tables = new DataTable[updateSize];
String[] tableNames = new String[updateSize];
GFXDClientConnection[] conns = new GFXDClientConnection[updateSize];
GFXDCommand[] cmds = new GFXDCommand[updateSize];
GFXDDataAdapter[] adpts = new GFXDDataAdapter[updateSize];
try
{
for (int i = 0; i < updateSize; i++)
{
tableNames[i] = DbRandom.BuildRandomTable(5);
conns[i] = new GFXDClientConnection(ConnectionString);
cmds[i] = conns[i].CreateCommand();
cmds[i].CommandText = String.Format(
"SELECT * FROM {0} ORDER BY COL_ID ASC ", tableNames[i]);
conns[i].Open();
conns[i].AutoCommit = false;
conns[i].BeginGFXDTransaction();
adpts[i] = cmds[i].CreateDataAdapter();
tables[i] = new DataTable();
adpts[i].Fill(tables[i]);
ParseDataTable(tables[i]);
}
IList<object> data = DbRandom.GetRandomRowData();
for (int i = 0; i < updateSize; i++)
{
CommandBuilder = new GFXDCommandBuilder(adpts[i]);
for (int j = 0; j < tables[i].Rows.Count; j++)
for (int k = 1; k < tables[i].Columns.Count; k++)
tables[i].Rows[j][k] = data[k];
if (adpts[i].Update(tables[i]) != tables[i].Rows.Count)
Fail(String.Format(
"Failed to update table {0}", tableNames[i]));
try
{
conns[i].Commit();
}
catch (Exception e)
{
conns[i].Rollback();
Fail(e);
}
}
for (int i = 0; i < updateSize; i++)
{
tables[i].Clear();
adpts[i].Fill(tables[i]);
}
foreach (DataTable table in tables)
{
foreach (DataRow row in table.Rows)
{
for (int i = 1; i < row.Table.Columns.Count; i++)
{
if (!DbRandom.Compare(data[i], row, i))
{
Fail(String.Format(
"Inconsistent updated data in table [{0}] at row [{1}] column [{2}]. "
+ "Expected [{3}]; Actual [{4}]",
table.TableName,
row[0].ToString(), row.Table.Columns[i].ColumnName,
data[i].ToString(), row[i].ToString()));
}
}
}
}
}
catch (Exception e)
{
Fail(e);
}
finally
{
foreach (String tableName in tableNames)
DbRandom.DropTable(tableName);
base.Run(context);
}
}
//public void LoadTableListData(string schema)
public void LoadTableListData()
{
using (GFXDClientConnection conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query =
"SELECT DISTINCT B.SCHEMANAME, A.TABLENAME FROM SYS.SYSTABLES A INNER JOIN SYS.SYSSCHEMAS B ON A.SCHEMAID = B.SCHEMAID WHERE A.TABLETYPE = 'T' ORDER BY B.SCHEMANAME ASC";
//"SELECT ROW_NUMBER() OVER(ORDER BY TABLE_NAME) AS rownum, TABLE_NAME as name FROM AdventureWorks.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + schema + "' and TABLE_NAME not like 'v%'";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
GFXDDataAdapter da = new GFXDDataAdapter(cmd);
_dtTableList.Clear();
da.Fill(_dtTableList);
LoadTableList();
}
}
public void BuildQueryAndExecute()
{
string query = string.Empty;
try
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
query =
"select * from production.product p join production.productsubcategory ps on" +
" ps.ProductSubCategoryID = p.ProductSubCategoryID where ps.name = '" +
SubCategorySelected + "'";
if (!string.IsNullOrEmpty(ColorSelected))
{
query += " AND p.Color = '" + ColorSelected + "'";
}
if (!string.IsNullOrEmpty(SellStartDateSelected))
{
query += " AND p.SellStartDate >= CAST ('" + SellStartDateSelected + "' as TIMESTAMP)";
}
if ((!string.IsNullOrEmpty(ListPriceGTSelected)) || (!string.IsNullOrEmpty(ListPriceLTSelected)))
{
if ((!string.IsNullOrEmpty(ListPriceGTSelected)) && (!string.IsNullOrEmpty(ListPriceLTSelected)))
{
query += " AND p.ListPrice BETWEEN " + ListPriceGTSelected + " AND " + ListPriceLTSelected;
}
else if (!string.IsNullOrEmpty(ListPriceGTSelected))
{
query += " AND p.ListPrice > " + ListPriceGTSelected;
}
else if (!string.IsNullOrEmpty(ListPriceLTSelected))
{
query += " AND p.ListPrice < " + ListPriceLTSelected;
}
}
query += " order by p.ProductId";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
_dtTableDataRows.Clear();
da.Fill(_dtTableDataRows);
}
}
catch (Exception e)
{
throw e;
}
finally
{
if (!MainWindow.PeristedQList.Contains(query))
{
MainWindow.PeristedQList.Add(query);
}
}
}
private static DataTable GetDataTable(GFXDClientConnection connection, GFXDDataAdapter adapter)
{
DataTable dt = new DataTable();
adapter.Fill(dt);
return dt;
}
