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; }