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 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 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 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 UpdateBulkData(string tableName)
{
try
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
//GFXDCommand cmd = conn.CreateCommand();
//cmd.CommandType = CommandType.Text;
//cmd.CommandText = "Delete from Sales.SalesReason where SalesReasonID between 17000 and 19485";
//var da = new GFXDDataAdapter(cmd);
//var dt = new DataTable();
//da.Fill(dt);
GFXDCommand cmd = conn.CreateCommand();
DataTable dt = new DataTable();
var adapter = new GFXDDataAdapter();
cmd.CommandText = "SELECT * FROM Sales.SalesReason";
adapter.SelectCommand = cmd;
DbCommandBuilder builder = new GFXDCommandBuilder(adapter);
adapter.Fill(dt);
for (int i = 500; i < 1000; i++)
{
dt.Rows[i].Delete();
}
adapter.Update(dt);
}
}
catch (GFXDException ex)
{
string err = ex.Message;
}
catch (Exception ex)
{
string err = ex.Message;
}
}
public void LoadTableRowListData(string table)
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string[] words = table.Split('.');
string query =
"SELECT * FROM " + table + " order by " + words[1] + "ID";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
_dtTableDataRows.Clear();
da.Fill(_dtTableDataRows);
}
}
public void LoadProductSubCategoryData()
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query =
"select ProductSubCategoryID, Name from production.productsubcategory where productcategoryid = " + CategoryIdSelected.ToString() + " order by Name";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
_dt.Clear();
da.Fill(_dt);
LoadSubCategoryList();
}
}
public void LoadTableRowListData(string table)
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string[] words = table.Split('.');
string query =
"SELECT * FROM " + table + " order by " + words[1] + "ID";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
_dtTableDataRows.Clear();
da.Fill(_dtTableDataRows);
}
}
public void LoadProductSubCategoryData()
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query =
"select ProductSubCategoryID, Name from production.productsubcategory where productcategoryid = " + CategoryIdSelected.ToString() + " order by Name";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
_dt.Clear();
da.Fill(_dt);
LoadSubCategoryList();
}
}
//public void LoadTableListData(string schema)
public void LoadTableListData()
{
using (GFXDClientConnection conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query =
"SELECT DISTINCT B.SCHEMANAME, A.TABLENAME FROM SYS.SYSTABLES A INNER JOIN SYS.SYSSCHEMAS B ON A.SCHEMAID = B.SCHEMAID WHERE A.TABLETYPE = 'T' ORDER BY B.SCHEMANAME ASC";
//"SELECT ROW_NUMBER() OVER(ORDER BY TABLE_NAME) AS rownum, TABLE_NAME as name FROM AdventureWorks.INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = '" + schema + "' and TABLE_NAME not like 'v%'";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
GFXDDataAdapter da = new GFXDDataAdapter(cmd);
_dtTableList.Clear();
da.Fill(_dtTableList);
LoadTableList();
}
}
public void LoadSchemaListData()
{
// load the connection string from the configuration files
_connectionString = Settings.Default.AdventureWorksConnectionString;
using (GFXDClientConnection conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query =
"SELECT DISTINCT B.SCHEMANAME, A.TABLENAME FROM SYS.SYSTABLES A INNER JOIN SYS.SYSSCHEMAS B ON A.SCHEMAID = B.SCHEMAID WHERE A.TABLETYPE = 'T' ORDER BY B.SCHEMANAME ASC";
//"select ROW_NUMBER() OVER(ORDER BY schema_id) AS rownum, 'AW.' + name as name FROM AdventureWorks.sys.schemas where schema_id between 5 and 9";
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
GFXDDataAdapter da = new GFXDDataAdapter(cmd);
//_dtSchemaList.Clear();
da.Fill(_dtSchemaList);
}
}
public void LoadCustomerData(int key, string value)
{
using (var conn = new GFXDClientConnection(ConnectionString))
{
conn.Open();
// load no data 1=0, but get the columns...
string query;
if (String.IsNullOrEmpty(value))
{
query = "select cus.customerid, c.firstname, c.middlename, c.lastname from person.contact c " +
"join sales.individual i on i.contactid = c.contactid join sales.customer cus on " +
"cus.customerid = i.customerid order by cus.customerid";
}
else
{
query = "select cus.customerid, c.firstname, c.middlename, c.lastname from person.contact c " +
"join sales.individual i on i.contactid = c.contactid join sales.customer cus on " +
"cus.customerid = i.customerid where cus.territoryid = " +
key.ToString() + " order by cus.customerid";
}
GFXDCommand cmd = conn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = query;
var da = new GFXDDataAdapter(cmd);
_dt.Clear();
// need to add an empty row for deselecting currently selected item.
//_dt.Rows.Add();
//_dt.AcceptChanges();
da.Fill(_dt);
LoadCustomerList();
}
}
public void 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 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 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 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 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 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();
}
}
/// <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 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 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 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 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 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 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 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);
}
}
}
}
