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 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 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(); } }
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 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) { 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 override void Run(Object context) { GFXDClientConnection conn; String connStr = String.Format("{0}", Configuration.GFXDConfigManager.GetGFXDServerConnectionString()); try { conn = new GFXDClientConnection(connStr); 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 (DbException e) { Fail(e); } finally { base.Run(context); } }
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(); } }
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 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 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 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 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) { 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 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 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 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 static GFXDClientConnection OpenConnection(String connStr) { GFXDClientConnection conn = new GFXDClientConnection(connStr); conn.Open(); return(conn); }
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 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 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 Create(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(); }
/// <summary> /// check for various connection attributes in addition to auth /// (examples from reference guide) /// </summary> public void BasicAuthentication() { // First use the connection with system user to create a new user. string host = "localhost"; int port = s_clientPort; string user = "******"; string passwd = "gemfire1"; string connStr = string.Format("server={0}:{1};user={2};password={3}", host, port, user, passwd); using (GFXDClientConnection conn = new GFXDClientConnection(connStr)) { conn.Open(); // fire a simple query to check the connection GFXDCommand cmd = conn.CreateCommand(); cmd.CommandText = "select count(*) from sys.members"; Assert.AreEqual(1, cmd.ExecuteScalar()); // create new user cmd.CommandText = "call sys.create_user('gemfirexd.user.gem2', 'gem2')"; Assert.AreEqual(-1, cmd.ExecuteNonQuery()); conn.Close(); } // Open a new connection to the locator having network server // with username and password in the connection string. user = "******"; passwd = "gem2"; connStr = string.Format("server={0}:{1};user={2};password={3}", host, port, user, passwd); using (GFXDClientConnection conn = new GFXDClientConnection(connStr)) { conn.Open(); // fire a simple query to check the connection GFXDCommand cmd = conn.CreateCommand(); cmd.CommandText = "select count(*) from sys.members"; Assert.AreEqual(1, cmd.ExecuteScalar()); conn.Close(); } // Open a new connection to the locator having network server // with username and password passed as properties. connStr = string.Format("server={0}:{1}", host, port); using (GFXDClientConnection conn = new GFXDClientConnection(connStr)) { Dictionary <string, string> props = new Dictionary <string, string>(); props.Add("user", user); props.Add("password", passwd); props.Add("disable-streaming", "true"); props.Add("load-balance", "false"); conn.Open(props); // fire a simple query to check the connection GFXDCommand cmd = conn.CreateCommand(); cmd.CommandText = "select count(*) from sys.members"; Assert.AreEqual(1, cmd.ExecuteScalar()); conn.Close(); } }
public void ConnectLocatorWithAuth() { // start the locator and servers with authentication SetupCommon(" -auth-provider=BUILTIN -gemfirexd.user.gem1=gem1" + " -user=gem1 -password=gem1"); // first create a new user string host = "localhost"; int port = s_clientPort; string user = "******"; string passwd = "gem1"; string connStr = string.Format("server={0}:{1};user={2};password={3}", host, port, user, passwd); using (GFXDClientConnection conn = new GFXDClientConnection(connStr)) { conn.Open(); // create user new GFXDCommand("call sys.create_user('gemfirexd.user.gem2', 'gem2')", conn).ExecuteNonQuery(); conn.Close(); } // Open a new connection to the locator having network server on localhost // with username and password in the connection string. user = "******"; passwd = "gem2"; connStr = string.Format("server={0}:{1};user={2};password={3}", host, port, user, passwd); 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 having network server on localhost // with username and password passed as properties. string connStr2 = string.Format("server={0}:{1}", host, port); using (GFXDClientConnection conn = new GFXDClientConnection(connStr2)) { Dictionary <string, string> props = new Dictionary <string, string>(); props.Add("user", user); props.Add("password", passwd); conn.Open(props); // check a query Assert.AreEqual(3, new GFXDCommand("select count(id) from sys.members", conn).ExecuteScalar()); conn.Close(); } }
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 TestConnection() { GFXDClientConnection connection = new GFXDClientConnection(DbConnectionString); connection.Open(); if (connection.State == ConnectionState.Open) { connection.Close(); return(1); } return(0); }
public void NamedParameters() { // 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, :ADDR)", 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.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 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 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 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 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 static GFXDClientConnection OpenConnection(String connStr) { GFXDClientConnection conn = new GFXDClientConnection(connStr); try { conn.Open(); ++GFXDTestRunner.ConnCount; } catch (Exception e) { throw new Exception(e.Message, e.InnerException); } return(conn); }
public void PositionalParametersBatch() { // 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 values (?, ?)", conn); cmd.Prepare(); for (int i = 0; i < s_numInserts; i++) { cmd.Parameters.Add(i); cmd.Parameters.Add("addr" + i); cmd.AddBatch(); } 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 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 DataSet ExecuteQuery(string query) { // comment this line if only non-connection pooling test desired connectionStr += ";userID=syed;password=syed;MinimumPoolSize=1;MaximumPoolSize=5;ConnectionLifetime=60;Pooling=true"; var dt = new DataSet(); try { var conn = new GFXDClientConnection(connectionStr); for (int i = 0; i < 1000; i++) { conn.Open(); GFXDCommand cmd = conn.CreateCommand(); //cmd.CommandType = CommandType.Text; cmd.CommandText = query; //for (int i = 0; i < 1; i++) //{ cmd.ExecuteNonQuery(); //} //var da = new GFXDDataAdapter(cmd); //da.Fill(dt); conn.Close(); } } catch (GFXDException ex) { string err = ex.Message; } catch (Exception ex) { string err = ex.Message; } return(dt); }
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 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 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 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 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 DataSet ExecuteQuery(string query) { // comment this line if only non-connection pooling test desired connectionStr += ";userID=syed;password=syed;MinimumPoolSize=1;MaximumPoolSize=5;ConnectionLifetime=60;Pooling=true"; var dt = new DataSet(); try { var conn = new GFXDClientConnection(connectionStr); for (int i = 0; i < 1000; i++) { conn.Open(); GFXDCommand cmd = conn.CreateCommand(); //cmd.CommandType = CommandType.Text; cmd.CommandText = query; //for (int i = 0; i < 1; i++) //{ cmd.ExecuteNonQuery(); //} //var da = new GFXDDataAdapter(cmd); //da.Fill(dt); conn.Close(); } } catch (GFXDException ex) { string err = ex.Message; } catch (Exception ex) { string err = ex.Message; } return dt; }
/// <summary> /// check for various connection attributes in addition to auth /// (examples from reference guide) /// </summary> public void BasicAuthentication() { // First use the connection with system user to create a new user. string host = "localhost"; int port = s_clientPort; string user = "******"; string passwd = "gemfire1"; string connStr = string.Format("server={0}:{1};user={2};password={3}", host, port, user, passwd); using (GFXDClientConnection conn = new GFXDClientConnection(connStr)) { conn.Open(); // fire a simple query to check the connection GFXDCommand cmd = conn.CreateCommand(); cmd.CommandText = "select count(*) from sys.members"; Assert.AreEqual(1, cmd.ExecuteScalar()); // create new user cmd.CommandText = "call sys.create_user('gemfirexd.user.gem2', 'gem2')"; Assert.AreEqual(-1, cmd.ExecuteNonQuery()); conn.Close(); } // Open a new connection to the locator having network server // with username and password in the connection string. user = "******"; passwd = "gem2"; connStr = string.Format("server={0}:{1};user={2};password={3}", host, port, user, passwd); using (GFXDClientConnection conn = new GFXDClientConnection(connStr)) { conn.Open(); // fire a simple query to check the connection GFXDCommand cmd = conn.CreateCommand(); cmd.CommandText = "select count(*) from sys.members"; Assert.AreEqual(1, cmd.ExecuteScalar()); conn.Close(); } // Open a new connection to the locator having network server // with username and password passed as properties. connStr = string.Format("server={0}:{1}", host, port); using (GFXDClientConnection conn = new GFXDClientConnection(connStr)) { Dictionary<string, string> props = new Dictionary<string, string>(); props.Add("user", user); props.Add("password", passwd); props.Add("disable-streaming", "true"); props.Add("load-balance", "false"); conn.Open(props); // fire a simple query to check the connection GFXDCommand cmd = conn.CreateCommand(); cmd.CommandText = "select count(*) from sys.members"; Assert.AreEqual(1, cmd.ExecuteScalar()); conn.Close(); } }
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(); } } }