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 void UpdateRow(object param) { Product product = (Product)ObjectFactory.Create(ObjectType.Product); product.ProductId = Convert.ToInt64(param); try { GFXDClientConnection conn = new GFXDClientConnection(ConnectionString); conn.Open(); DbController dbc = new DbController(conn); dbc.UpdateProduct(product); Product updatedProd = dbc.GetProduct(product.ProductId); if (!product.Validate(updatedProd)) { Fail("Product update validation failed."); } } catch (Exception e) { Fail(e); } }
public override void Run(Object context) { GFXDClientConnection conn; try { conn = new GFXDClientConnection( Configuration.GFXDConfigManager.GetGFXDServerConnectionString()); conn.Open(); if (conn.State != System.Data.ConnectionState.Open) { Fail(String.Format("Invalid connection state. " + "Expected [{0}]; Actual [{1}]", System.Data.ConnectionState.Open, conn.State)); } } catch (Exception e) { Fail(e); } finally { base.Run(context); } }
public void ConnectDefaultsIncludingWithProperty() { // 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(); // check a query Assert.AreEqual(1, new GFXDCommand("select count(id) from sys.members", conn).ExecuteScalar()); conn.Close(); } // Open a new connection to the server with streaming disabled using (GFXDClientConnection conn = new GFXDClientConnection(connStr)) { Dictionary <string, string> props = new Dictionary <string, string>(); props.Add("disable-streaming", "true"); conn.Open(props); // check a query Assert.AreEqual(1, new GFXDCommand("select count(id) from sys.members", conn).ExecuteScalar()); conn.Close(); } }
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 static long[] GetAllRowIds(GFXDClientConnection conn, string tableName, string identityName) { IList <long> listIds = new List <long>(); try { GFXDCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = String.Format("SELECT {0} FROM {1} ORDER BY {2} ASC", identityName, tableName, identityName); GFXDDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { listIds.Add(rdr.GetInt64(0)); } } catch (Exception e) { throw new Exception(e.Message, e.InnerException); } return(listIds.ToArray <long>()); }
public override void Run(Object context) { GFXDClientConnection conn; try { conn = new GFXDClientConnection(); if (conn.State != System.Data.ConnectionState.Closed) { Fail(String.Format("Invalid connection state. " + "Expected [{0}]; Actual [{1}]", System.Data.ConnectionState.Closed, conn.State)); } try { conn.Open(); Fail("GFXDException is expected when no connection string is specified"); } catch (Exception e) { Log(e); // passed } } catch(Exception e) { Fail(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 override void Run(object context) { try { String providerName = Configuration.GFXDConfigManager.GetAppSetting("dbProvider"); DbProviderFactory factory = DbProviderFactories.GetFactory(providerName); GFXDClientConnection conn = (GFXDClientConnection)factory.CreateConnection(); 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; }
private void UpdateRow(object param) { GFXDClientConnection conn = null; Product product = (Product)ObjectFactory.Create(ObjectType.Product); product.ProductId = Convert.ToInt64(param); try { conn = new GFXDClientConnection(ConnectionString); conn.Open(); conn.BeginGFXDTransaction(); DbController dbc = new DbController(conn); dbc.UpdateProduct(product); Product updatedProd = dbc.GetProduct(product.ProductId); conn.Commit(); if (!product.Validate(updatedProd)) Fail("Product update validation failed."); } catch(GFXDException se) { if(!se.State.Equals("X0Z02")) Fail(se); conn.Rollback(); } catch (Exception e) { Fail(e); conn.Rollback(); } }
public static object GetDataField(GFXDClientConnection conn, string tableName, string fieldName, string identityName, long identityValue) { if (conn == null) { return(null); } if (conn.IsClosed) { conn.Open(); } object field = new object(); try { GFXDCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = String.Format("SELECT {0} FROM {1} WHERE {2}={3}", fieldName, tableName, identityName, identityValue); field = cmd.ExecuteScalar(); cmd.Close(); } catch (Exception e) { throw new Exception(e.Message, e.InnerException); } return(field); }
public override void Run(Object context) { GFXDClientConnection conn; try { conn = new GFXDClientConnection(); if (conn.State != System.Data.ConnectionState.Closed) { Fail(String.Format("Invalid connection state. " + "Expected [{0}]; Actual [{1}]", System.Data.ConnectionState.Closed, conn.State)); } try { conn.Open(); Fail("GFXDException is expected when no connection string is specified"); } catch (Exception e) { Log(e); // passed } } catch (Exception e) { Fail(e); } finally { base.Run(context); } }
public void ConnectLocatorIncludingWithProperty() { // start the locator and servers without any authentication SetupCommon(""); // 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(); // check a query Assert.AreEqual(3, new GFXDCommand("select count(id) from sys.members", conn).ExecuteScalar()); conn.Close(); } // Open a new connection to the locator with load-balancing disabled using (GFXDClientConnection conn = new GFXDClientConnection(connStr)) { Dictionary <string, string> props = new Dictionary <string, string>(); props.Add("load-balance", "false"); conn.Open(props); // check a query Assert.AreEqual(3, new GFXDCommand("select count(id) from sys.members", conn).ExecuteScalar()); conn.Close(); } }
private static DataSet GetDataSet(GFXDClientConnection connection, GFXDDataAdapter adapter) { DataSet ds = new DataSet(); adapter.Fill(ds); return(ds); }
private void MigrateData() { Helper.Log(String.Format("Start migrating table {0}", TableFullName)); DataTable sqlTable = null; Migrator.OnMigrateEvent(new MigrateEventArgs( Result.Unknown, String.Format("Copying table {0} data...", TableFullName))); sqlTable = SQLSvrDbi.GetTableData(TableFullName); GFXDClientConnection connection = GemFireXDDbi.OpenNewConnection(); GemFireXDDbi.BatchInsert(connection, GetInsertSql(), sqlTable, this); while (connection.State == ConnectionState.Executing) { Helper.Log("Connection is still executing"); Thread.Sleep(5000); } Validate(sqlTable); Helper.Log(String.Format("Table {0} migrated", TableFullName)); }
private static DataTable GetDataTable(GFXDClientConnection connection, GFXDDataAdapter adapter) { DataTable dt = new DataTable(); adapter.Fill(dt); 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 long Insert(string sql, bool getId) { int result = 0; using (GFXDClientConnection connection = new GFXDClientConnection(connString)) { ++GFXDTestRunner.ConnCount; using (GFXDCommand command = connection.CreateCommand()) { command.CommandType = System.Data.CommandType.Text; command.CommandText = sql; connection.Open(); result = command.ExecuteNonQuery(); if (getId) { command.CommandText = "SELECT @@IDENTITY"; result = int.Parse(command.ExecuteScalar().ToString()); } } } return(result); }
public void ConnectDefaultsIncludingWithProperty() { // 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(); // check a query Assert.AreEqual(1, new GFXDCommand("select count(id) from sys.members", conn).ExecuteScalar()); conn.Close(); } // Open a new connection to the server with streaming disabled using (GFXDClientConnection conn = new GFXDClientConnection(connStr)) { Dictionary<string, string> props = new Dictionary<string, string>(); props.Add("disable-streaming", "true"); conn.Open(props); // check a query Assert.AreEqual(1, new GFXDCommand("select count(id) from sys.members", conn).ExecuteScalar()); conn.Close(); } }
public static long GetLastRowId(GFXDClientConnection conn, string tableName, string identityName) { long id = 0; try { GFXDCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = String.Format( "SELECT {0} FROM {1} ORDER BY {2} DESC FETCH FIRST 1 ROWS ONLY", identityName, tableName, identityName); GFXDDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { id = long.Parse(rdr.GetString(0)); } } catch (Exception e) { throw new Exception(e.Message, e.InnerException); } return(id); }
public override void Run(Object context) { GFXDClientConnection conn; String connStr = Configuration.GFXDConfigManager.GetGFXDServerConnectionString(); try { conn = new GFXDClientConnection(connStr); if (conn.State != System.Data.ConnectionState.Closed) { Fail(String.Format("Invalid connection state. " + "Expected {0}; Actual {1}", System.Data.ConnectionState.Closed, conn.State)); } } catch (Exception e) { Fail(e); } finally { base.Run(context); } }
public void ExecuteNonQuery() { try { using (var conn = new GFXDClientConnection(ConnectionString)) { conn.Open(); // create a table // using the base DbCommand class rather than GemFireXD specific class DbCommand cmd = conn.CreateCommand(); cmd.CommandText = "create table dbo.TestData5 (TestData5ID int primary key, name varchar(50))"; //cmd.CommandText = "drop table dbo.TestData2"; cmd.ExecuteNonQuery(); } } catch (GFXDException ex) { string err = ex.Message; } catch (Exception ex) { string err = ex.Message; } }
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 static long DeleteFromTable(GFXDClientConnection conn, String tableName) { long rowsDeleted = ExecuteNonQueryStatement( conn, String.Format("DELETE FROM {0}", tableName)); return(rowsDeleted); }
public static GFXDClientConnection OpenConnection(String connStr) { GFXDClientConnection conn = new GFXDClientConnection(connStr); conn.Open(); return(conn); }
public static int GetTableRowCount( GFXDClientConnection conn, String tableName) { int rowCount = Convert.ToInt32(ExecuteScalarStatement( conn, String.Format("SELECT COUNT(*) FROM {0}", tableName))); return(rowCount); }
public void ExcuteTransaction(List<string> tables) { try { using (var conn = new GFXDClientConnection(ConnectionString)) { // Open connection, disable auto-commit, and start transaction conn.Open(); conn.AutoCommit = false; conn.BeginGFXDTransaction(IsolationLevel.ReadCommitted); GFXDCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; for (int i = 0; i < tables.Count; i++) { string[] words = tables[i].Split('.'); cmd.CommandText = "Select * from " + tables[i] + " order by " + words[1] + "ID"; ; var adapter = cmd.CreateDataAdapter(); var table = new DataTable(tables[i]); 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 j = 1; j < (table.Rows[cnt - 1].ItemArray.Count()); j++) { row[j] = table.Rows[cnt - 1].ItemArray[j]; } table.Rows.Add(row); } // Update the underlying table adapter.Update(table); } // Commit transaction conn.Commit(); } } catch (GFXDException ex) { string err = ex.Message; } catch (Exception ex) { string err = ex.Message; } }
public void ExcuteTransaction(List <string> tables) { try { using (var conn = new GFXDClientConnection(ConnectionString)) { // Open connection, disable auto-commit, and start transaction conn.Open(); conn.AutoCommit = false; conn.BeginGFXDTransaction(IsolationLevel.ReadCommitted); GFXDCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; for (int i = 0; i < tables.Count; i++) { string[] words = tables[i].Split('.'); cmd.CommandText = "Select * from " + tables[i] + " order by " + words[1] + "ID";; var adapter = cmd.CreateDataAdapter(); var table = new DataTable(tables[i]); 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 j = 1; j < (table.Rows[cnt - 1].ItemArray.Count()); j++) { row[j] = table.Rows[cnt - 1].ItemArray[j]; } table.Rows.Add(row); } // Update the underlying table adapter.Update(table); } // Commit transaction conn.Commit(); } } catch (GFXDException ex) { string err = ex.Message; } catch (Exception ex) { string err = ex.Message; } }
public static object ExecuteScalarStatement(GFXDClientConnection conn, String statement) { GFXDCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = statement; return(cmd.ExecuteScalar()); }
public static int ExecuteNonQueryStatement(GFXDClientConnection conn, String statement) { GFXDCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = statement; return(cmd.ExecuteNonQuery()); }
public void GenericCoding() { // 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 { // insert into the table using named parameters // using an abstracted method that can deal with difference in the // conventions of named parameters in different drivers cmd = conn.CreateCommand(); string idPrm = GetEscapedParameterName("ID"); string addrPrm = GetEscapedParameterName("ADDR"); cmd.CommandText = "insert into t1 values (" + idPrm + "," + addrPrm + ")"; cmd.Prepare(); // using the base DbParameter class DbParameter prm; for (int i = 0; i < 1000; i++) { // first the parameter for ID cmd.Parameters.Clear(); prm = cmd.CreateParameter(); prm.ParameterName = "ID"; prm.DbType = DbType.Int32; prm.Value = i; cmd.Parameters.Add(prm); // next the parameter for ADDR prm = cmd.CreateParameter(); prm.ParameterName = "ADDR"; prm.DbType = DbType.String; prm.Value = "addr" + i; cmd.Parameters.Add(prm); cmd.ExecuteNonQuery(); } // check the inserts VerifyInserts(conn, s_numInserts); } finally { // drop the table cmd = new GFXDCommand("drop table t1", conn); cmd.ExecuteNonQuery(); conn.Close(); } } }
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(); } } }
public static int Drop(GFXDClientConnection connection, string sql) { GFXDCommand command = connection.CreateCommand(); command.CommandType = System.Data.CommandType.Text; command.CommandText = sql; if (connection.IsClosed) connection.Open(); return command.ExecuteNonQuery(); }
public static int GetTableColumnCount( GFXDClientConnection conn, String tableName) { int colCount = Convert.ToInt32(ExecuteScalarStatement( conn, String.Format( "SELECT COUNT(*) FROM SYS.SYSCOLUMNS WHERE REFERENCEID = " + "(SELECT TABLEID FROM SYS.SYSTABLES WHERE TABLENAME = '{0}')", tableName))); return(colCount); }
public static int TestConnection() { GFXDClientConnection connection = new GFXDClientConnection(DbConnectionString); connection.Open(); if(connection.State == ConnectionState.Open) { connection.Close(); return 1; } return 0; }
public static int Drop(string sql) { using (GFXDClientConnection connection = new GFXDClientConnection(connString)) { using (GFXDCommand command = connection.CreateCommand()) { command.CommandType = System.Data.CommandType.Text; command.CommandText = sql; connection.Open(); return command.ExecuteNonQuery(); } } }
public override void Run(Object context) { GFXDClientConnection conn = null; try { conn = new GFXDClientConnection("xxxinvalid"); Fail("GFXDException is expected with an invalid connection string"); } catch(Exception e) { Log(e); } finally { base.Run(context); } }
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 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 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(); } }
private void SelectRows(object param) { long rowCount = Convert.ToInt64(param); try { GFXDClientConnection conn = new GFXDClientConnection(ConnectionString); conn.Open(); DbController dbc = new DbController(conn); IList<Product> products = dbc.GetProducts(); if(products.Count != rowCount) Fail(String.Format("Query returned incorrect number or rows. " + "Expected [{0}]; Actual [{1}]", rowCount, products.Count)); } catch (Exception e) { Fail(e); } }
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 void UpdateRow(object param) { Product product = (Product)ObjectFactory.Create(ObjectType.Product); product.ProductId = Convert.ToInt64(param); try { GFXDClientConnection conn = new GFXDClientConnection(ConnectionString); conn.Open(); DbController dbc = new DbController(conn); dbc.UpdateProduct(product); Product updatedProd = dbc.GetProduct(product.ProductId); if (!product.Validate(updatedProd)) Fail("Product update validation failed."); } catch (Exception e) { Fail(e); } }
public override void Run(Object context) { GFXDClientConnection conn; String connStr = Configuration.GFXDConfigManager.GetGFXDServerConnectionString(); try { conn = new GFXDClientConnection(connStr); if (conn.State != System.Data.ConnectionState.Closed) { Fail(String.Format("Invalid connection state. " + "Expected {0}; Actual {1}", System.Data.ConnectionState.Closed, conn.State)); } } catch(Exception e) { Fail(e); } finally { base.Run(context); } }
public void TransactionTest() { // 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 GFXDCommand cmd = new GFXDCommand("create table t1 (id int primary" + " key, addr varchar(20))", conn); cmd.ExecuteNonQuery(); try { GFXDTransaction tran = conn.BeginTransaction(IsolationLevel .ReadCommitted); cmd.Transaction = tran; // insert into the table using positional parameters cmd = new GFXDCommand("insert into t1 (id, addr) values (?, ?)", conn); cmd.Prepare(); for (int i = 0; i < s_numInserts; i++) { cmd.Parameters.Clear(); cmd.Parameters.Add(i); cmd.Parameters.Add("addr" + i); cmd.ExecuteNonQuery(); } tran.Commit(); // check the inserts VerifyInserts(conn, s_numInserts); // fire some updates and if any unsuccessful then rollback the transaction cmd.CommandText = "update t1 set addr = ? where id = ?"; tran = conn.BeginTransaction(IsolationLevel.ReadCommitted); cmd.Transaction = tran; bool success = true; for (int i = 100; i < 200; i++) { cmd.Parameters.Clear(); cmd.Parameters.Add("address" + i); cmd.Parameters.Add(i); if (cmd.ExecuteNonQuery() != 1) { // update failed; rolling back the entire transaction success = false; tran.Rollback(); break; } } // command for verification string verifyText = "select * from t1 where id >= 100 and id < 200"; if (success) { // verify the updates in transactional data VerifyInserts(conn, verifyText, 100, 199, "address"); // all succeeded; commit the transaction tran.Commit(); // verify the updates after commit VerifyInserts(conn, verifyText, 100, 199, "address"); } else { // verify no updates VerifyInserts(conn, verifyText, 100, 199, "addr"); } } finally { // drop the table cmd = new GFXDCommand("drop table t1", conn); cmd.ExecuteNonQuery(); conn.Close(); } } }
public void PositionalParameters() { // 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 GFXDCommand cmd = new GFXDCommand("create table t1 (id int primary" + " key, addr varchar(20))", conn); cmd.ExecuteNonQuery(); try { // insert into the table using positional parameters cmd = new GFXDCommand("insert into t1 (id, addr) values (?, ?)", conn); cmd.Prepare(); for (int i = 0; i < s_numInserts; i++) { cmd.Parameters.Clear(); cmd.Parameters.Add(i); cmd.Parameters.Add("addr" + i); cmd.ExecuteNonQuery(); } // check the inserts VerifyInserts(conn, s_numInserts); } finally { // drop the table cmd = new GFXDCommand("drop table t1", conn); cmd.ExecuteNonQuery(); conn.Close(); } } }
public void MixedParameters() { // 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 GFXDCommand cmd = new GFXDCommand("create table t1 (id int primary" + " key, addr varchar(20))", conn); cmd.ExecuteNonQuery(); try { // insert into the table using named parameters cmd = new GFXDCommand("insert into t1 values (:ID, ?)", conn); cmd.Prepare(); DbParameter prm; for (int i = 0; i < s_numInserts; i++) { // first the parameter for ID cmd.Parameters.Clear(); prm = cmd.CreateParameter(); prm.ParameterName = "ID"; prm.DbType = DbType.Int32; prm.Value = i; cmd.Parameters.Add(prm); // then the parameter for ADDR prm = cmd.CreateParameter(); prm.ParameterName = "ADDR"; prm.Value = "addr" + i; cmd.Parameters.Add(prm); cmd.ExecuteNonQuery(); } // 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 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 MultipleStringsBatch() { // 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 GFXDCommand cmd = new GFXDCommand("create table t1 (id int primary" + " key, addr varchar(20))", conn); cmd.ExecuteNonQuery(); try { string cmdStr; // insert into the table using different command strings for (int i = 0; i < s_numInserts; i++) { cmdStr = "insert into t1 values (" + i + ", 'addr" + i + "')"; cmd.AddBatch(cmdStr); } int[] results = cmd.ExecuteBatch(); // check the inserts Assert.AreEqual(s_numInserts, results.Length); for (int i = 0; i < s_numInserts; i++) { Assert.AreEqual(1, results[i], "unexpected result=" + results[i] + " for i=" + i); } // also check in the database VerifyInserts(conn, s_numInserts); } finally { // drop the table cmd = new GFXDCommand("drop table t1", conn); cmd.ExecuteNonQuery(); conn.Close(); } } }
public SupplierDao(GFXDClientConnection connection) : base(connection) { }
public void DataReaderWithPositionalParameters() { // 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 GFXDCommand cmd = new GFXDCommand("create table t1 (id int primary" + " key, addr varchar(20))", conn); cmd.ExecuteNonQuery(); try { // insert into the table using positional parameters cmd = new GFXDCommand("insert into t1 (id, addr) values (?, ?)", conn); cmd.Prepare(); for (int i = 1; i <= s_numInserts; i++) { cmd.Parameters.Clear(); cmd.Parameters.Add(i); cmd.Parameters.Add("addr" + i); cmd.ExecuteNonQuery(); } // now query the table using a DataReader cmd.Parameters.Clear(); cmd.CommandText = "select * from t1"; GFXDDataReader reader = cmd.ExecuteReader(); int[] ids = new int[s_numInserts]; int numResults = 0; while (reader.Read()) { int id = reader.GetInt32(0); string addr = reader.GetString(1); if (ids[id - 1] != 0) { throw new Exception("Duplicate value for ID=" + id + " addr=" + addr); } ids[id - 1] = id; numResults++; } reader.Close(); if (numResults != s_numInserts) { throw new Exception("unexpected number of results " + numResults); } } finally { // drop the table cmd = new GFXDCommand("drop table t1", conn); cmd.ExecuteNonQuery(); conn.Close(); } } }
public SupplierDao(GFXDClientConnection connection, String schemaName) : base(connection, schemaName) { }