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 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[] 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 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 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 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 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 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 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 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 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 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 int ExecuteNonQueryStatement(GFXDClientConnection conn, String statement) { GFXDCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = statement; return(cmd.ExecuteNonQuery()); }
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 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(); }
/// <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 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 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 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 void InsertTable(String schemaName, String tableName, int numRows) { GFXDClientConnection conn = DbHelper.OpenNewConnection(); GFXDCommand cmd = conn.CreateCommand(); String tableFullName = String.Format("{0}.{1}", schemaName, tableName); StringBuilder sql = new StringBuilder(); long startId = startPKeyId; sql.AppendFormat("INSERT INTO {0} (", tableFullName); foreach (DbField field in randomTable.Columns) { sql.AppendFormat("{0}, ", field.FieldName); } sql = sql.Remove(sql.Length - 2, 1).Append(") "); sql.Append("VALUES ("); foreach (DbField field in randomTable.Columns) { sql.Append("?, "); } sql = sql.Remove(sql.Length - 2, 1).Append(") "); cmd.CommandText = sql.ToString(); cmd.Prepare(); DbHelper.Log(cmd.CommandText); int batchCount = 0; for (int i = 0; i < numRows; i++) { foreach (DbField field in randomTable.Columns) { cmd.Parameters.Add(GetRandomFieldData(field)); } cmd.Parameters[0] = startId++; cmd.AddBatch(); if ((++batchCount) == batchSize) { cmd.ExecuteBatch(); batchCount = 0; } } cmd.ExecuteBatch(); }
public static long GetRandomRowId(GFXDClientConnection conn, string tableName, string identityName) { try { GFXDCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = String.Format( "SELECT {0} FROM {1} ORDER BY RANDOM() FETCH FIRST 1 ROWS ONLY", identityName, tableName); return(Convert.ToInt64(cmd.ExecuteScalar())); } catch (Exception e) { throw new Exception(e.Message, e.InnerException); } }
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(); } }
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 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 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; }
/// <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 static object ExecuteScalarStatement(GFXDClientConnection conn, String statement) { GFXDCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = statement; return cmd.ExecuteScalar(); }
public static int BatchInsert(GFXDClientConnection connection, string sql, DataTable sqlTable, DbTable esqlTable) { GFXDCommand command = null; int result = 0; int batchSize = 10; try { using (command = connection.CreateCommand()) { IDictionary<String, object> refColumns = new Dictionary<String, object>(); if (connection.IsClosed) connection.Open(); command.CommandType = CommandType.Text; command.CommandText = sql; command.Prepare(); Util.Helper.Log(command.CommandText); int batchCount = 0; for (int i = 0; i < sqlTable.Rows.Count; i++) { DataRow row = sqlTable.Rows[i]; for (int j = 0; j < esqlTable.Columns.Count; j++) { GFXDParameter param = command.CreateParameter(); param.Type = esqlTable.Columns[j].FieldType; // Set self-referenced column with null if (esqlTable.Columns[j].FieldName == esqlTable.SelfRefColumn) { refColumns.Add(row[0].ToString(), row[esqlTable.SelfRefColumn].ToString()); param.Value = DBNull.Value; } else param.Value = FormatFieldData(esqlTable.Columns[j].FieldType, row[j]); command.Parameters.Add(param); } command.AddBatch(); if ((++batchCount) == batchSize) { command.ExecuteBatch(); batchCount = 0; } } command.ExecuteBatch(); // Now update self-referenced column with actual value if (!String.IsNullOrEmpty(esqlTable.SelfRefColumn)) { command.CommandText = String.Format("UPDATE {0} SET {1} = ? WHERE {2} = ?", esqlTable.TableFullName, esqlTable.SelfRefColumn, sqlTable.Columns[0].ColumnName); command.Prepare(); batchCount = 0; foreach (String key in refColumns.Keys) { if (String.IsNullOrEmpty((String)refColumns[key]) || ((String)refColumns[key]).ToUpper() == "NULL") continue; command.Parameters.Add(refColumns[key]); command.Parameters.Add(key); command.AddBatch(); if ((++batchCount) == batchSize) { command.ExecuteBatch(); batchCount = 0; } } command.ExecuteBatch(); } } } catch (Exception e) { Util.Helper.Log(e); } return result; }
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 static int ExecuteNonQueryStatement(GFXDClientConnection conn, String statement) { GFXDCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = statement; return cmd.ExecuteNonQuery(); }
public static long Insert(string sql, bool getId) { int result = 0; using (GFXDClientConnection connection = new GFXDClientConnection(DbConnectionString)) { 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 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 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 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 static object Select(string sql, Util.QueryType type) { using (GFXDClientConnection connection = new GFXDClientConnection(DbConnectionString)) { using (GFXDCommand command = connection.CreateCommand()) { command.CommandType = System.Data.CommandType.Text; command.CommandText = sql; if (connection.IsClosed) connection.Open(); if (type == Util.QueryType.SCALAR) return command.ExecuteScalar(); using (GFXDDataAdapter adapter = command.CreateDataAdapter()) { switch (type) { case Util.QueryType.DATAROW: return GetDataRow(connection, adapter); case Util.QueryType.DATATABLE: return GetDataTable(connection, adapter); case Util.QueryType.DATASET: return GetDataSet(connection, adapter); default: return null; } } } } }
public static long GetRandomRowId(GFXDClientConnection conn, string tableName, string identityName) { try { GFXDCommand cmd = conn.CreateCommand(); cmd.CommandType = CommandType.Text; cmd.CommandText = String.Format( "SELECT {0} FROM {1} ORDER BY RANDOM() FETCH FIRST 1 ROWS ONLY", identityName, tableName); return Convert.ToInt64(cmd.ExecuteScalar()); } catch (Exception e) { throw new Exception(e.Message, e.InnerException); } }
public static DataTable SelectRandom(String tableName, int numRows) { using (GFXDClientConnection connection = new GFXDClientConnection(DbConnectionString)) { using (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, numRows); using (GFXDDataAdapter adapter = command.CreateDataAdapter()) { return GetDataTable(connection, adapter); } } } }