private void GetAllTables(DataGridView dgv, ComboBox cbo)
{
//make sure text is a number
//int limitInt = -1;
//if (!Validator.IsInteger(txtLimit.Text, out limitInt))
//{
// limitInt = 5;
//}
//make sure text is a number
string limitStr;
if (Validator.IsIntegerRegEx(txtLimit.Text))
{
limitStr = txtLimit.Text;
}
else
{
limitStr = "5";
}
//string table = cboTableNames.SelectedItem.ToString();
string table = cbo.SelectedItem.ToString();
//string query = "SELECT * FROM " + table + " LIMIT " + limitInt.ToString() + ";";
string query = "SELECT * FROM `" + table + "` LIMIT " + limitStr + ";"; //NEED BACKTICKS (`) for table name (`IN`)
//dgvTableResults.DataSource = EpicorDB.MySqlDataToGridView(query);
dgv.DataSource = EpicorDB.MySqlDataToGridView(query);
//copy to clipboard
dgv.ClipboardCopyMode = DataGridViewClipboardCopyMode.EnableWithoutHeaderText;
dgv.AutoResizeColumns();
}
//GET SKU# LIST (QUERY)
private List <string> GetSkuList()
{
DataTable dt;
List <string> skuList = new List <string>();
try
{
//string query = "SELECT DISTINCT in_item_number AS SKU FROM view_IN_alternate LIMIT 5";
string query = "SELECT DISTINCT in_item_number AS SKU FROM view_IN_alternate";
//cboMySku.ItemsSource = EpicorDB.MySqlDataToDataTable(query).DefaultView;
dt = EpicorDB.MySqlDataToDataTable(query); //save query as data table
skuList = new List <string>(); //pass list to combobox
for (int i = 0; i < dt.Rows.Count; i++)
{
string sku = dt.Rows[i]["SKU"].ToString();
sku = sku.Replace(" ", ""); //remove whitespace
skuList.Add(sku); //add each SKU to list
}
}
catch (MySqlException ex2)
{
MessageBox.Show("There was a problem with the selection, please pick another sku#", "Error: MySQL Exception");
//MessageBox.Show("There was an error" + "\nMessage: " + ex2.Message + "\nStack: " + ex2.StackTrace, "Error: MySQL Exception");
}
catch (Exception ex1)
{
MessageBox.Show("There was a problem with the selection, please pick another sku#", "Error: General Exception");
//MessageBox.Show("There was an error" + "\nMessage: " + ex1.Message + "\nStack: " + ex1.StackTrace, "Error: General Exception");
}
return(skuList);
}
//********************************
//********************************
//INVENTORY (RAW) TAB
private void LoadInventoryRawDataGridView()
{
string query = "SELECT * FROM view_IN_alternate";
dgvInventoryRaw.DataSource = EpicorDB.MySqlDataToGridView(query);
dgvInventoryRaw.AutoResizeColumns();
}
private void CustomQuery(DataGridView dgv)
{
//set query from textbox
string query = txtCustomQuery.Text;
//dgvTableResults.DataSource = EpicorDB.MySqlDataToGridView(query);
dgv.DataSource = EpicorDB.MySqlDataToGridView(query);
dgv.AutoResizeColumns();
}
private void txtLikeFilter_TextChanged(object sender, EventArgs e)
{
string likeFilter = txtLikeFilter.Text;
//SELECT * FROM inventory WHERE color LIKE '%blue%'
//@"SELECT table_name,column_name FROM information_schema.columns WHERE column_name like '%sku%'"
string query = @"SELECT table_name,column_name FROM information_schema.columns WHERE column_name like '%" + likeFilter + "%'";
dgvSearch.DataSource = EpicorDB.MySqlDataToGridView(query);
dgvSearch.AutoResizeColumns();
}
//CUSTOM TAB
//********************************
//********************************
//INVENTORY TAB
private void LoadInventoryDataGridView()
{
string query = "SELECT DISTINCT in_prime_department AS DeptNo, " +
"in_item_number AS SKU, " +
"in_item_description AS Description, " +
"in_item_weight AS Attr1, " +
"in_weight_unit AS Unit, " +
"in_retail_price AS Retail, " +
"in_quantity_on_hand AS QOH, " +
"in_quantity_on_order AS QOO " +
"FROM view_IN_alternate " +
"WHERE in_sds_seq_all_stores_id=1;"; //only shows first result (there are multiple lines otherwise)
dgvInventory.DataSource = EpicorDB.MySqlDataToGridView(query);
dgvInventory.AutoResizeColumns();
}
private void Form1_Load(object sender, EventArgs e)
{
//--------------
//TABLE TAB
//push data to table...
//EpicorDB.MySqlDataToGridView("SELECT * FROM dw_customer LIMIT 3", dgvTableResults);
dgvTableResults.DataSource = EpicorDB.MySqlDataToGridView("SELECT * FROM dw_customer LIMIT 3");
dgvTableResults.AutoResizeColumns();
//dgvTableResults.DataSource = EpicorDB.MySqlDataToGridView("SHOW TABLES");
//data to combobox (Table Names)
EpicorDB.MySqlDataToComboBox(cboTableNames);
//--------------
//--------------
//SEARCH TAB
//search for SKU (or other relevant tables)
string query = @"SELECT table_name,column_name FROM information_schema.columns WHERE column_name like '%sku%'";
dgvSearch.DataSource = EpicorDB.MySqlDataToGridView(query);
dgvSearch.AutoResizeColumns();
//--------------
//--------------
//CUSTOM SEARCH
//pulldown
EpicorDB.MySqlDataToComboBox(cboCustomQuery);
//SELECT * FROM (SHOW TABLES);
//string query2 = @"SELECT * FROM information_schema.columns WHERE column_name like '%sku%'";
string query2 = @"SELECT * FROM information_schema.columns";
dgvCustomQuery.DataSource = EpicorDB.MySqlDataToGridView(query2);
dgvSearch.AutoResizeColumns();
//--------------
//--------------
//INVENTORY TAB
LoadInventoryDataGridView();
//--------------
//--------------
//INVENTORY (RAW) TAB
LoadInventoryRawDataGridView();
//--------------
}
//LOAD DATAGRID VIEW
private void LoadDataGrid(string query)
{
try
{
//string query = "SELECT * FROM dw_customer LIMIT 3";
//add to grid
dgvMySql.ItemsSource = EpicorDB.MySqlDataToDataTable(query).DefaultView;
}
catch (MySqlException ex2)
{
MessageBox.Show("There was a problem with the selection, please pick another sku#", "Error: MySQL Exception");
//MessageBox.Show("There was an error" + "\nMessage: " + ex2.Message + "\nStack: " + ex2.StackTrace, "Error: MySQL Exception");
}
catch (Exception ex1)
{
MessageBox.Show("There was a problem with the selection, please pick another sku#", "Error: General Exception");
//MessageBox.Show("There was an error" + "\nMessage: " + ex1.Message + "\nStack: " + ex1.StackTrace, "Error: General Exception");
}
}
//LOAD DATAGRID VIEW (BY SKU)
private void LoadDataGridBySku(string sku)
{
try
{
//string query = "SELECT DISTINCT in_item_number AS SKU FROM view_IN_alternate";
//mysql query
string query = "SELECT DISTINCT in_prime_department AS DeptNo, " +
"in_item_number AS SKU, " +
"in_item_description AS Description, " +
"in_item_weight AS Attr1, " +
"in_weight_unit AS Unit, " +
"in_retail_price AS Retail, " +
"in_quantity_on_hand AS QOH, " +
"in_quantity_on_order AS QOO " +
"FROM view_IN_alternate " +
// "WHERE in_item_number=" + sku + ";";
"where in_sds_seq_all_stores_id=1 " + //filters to only show one entry (need to confirm this is correct)
"and in_item_number=" + sku + ";"; //input sku# here (parameterized query?)(needs to be a number)
//data table (get data from query)
DataTable dt = new DataTable();
dt = EpicorDB.MySqlDataToDataTable(query);
//add (row#) column
DataColumn col2 = dt.Columns.Add("#", typeof(Int32));
col2.SetOrdinal(0); //set to left column
//----------
//autoincrement columns
int index = 0;
foreach (DataRow row in dt.Rows)
{
row.SetField(col2, ++index);
}
//set autoincrement (new rows...)
col2.AutoIncrement = true;
col2.AutoIncrementSeed = ++index;
col2.AutoIncrementStep = 1;
col2.ReadOnly = true; //read-only
//----------
//set table to read-only
//dgvMySql.IsReadOnly = true; //set the whole table (can't change checkbox column?)
for (int i = 0; i < dt.Columns.Count; i++)
{
dt.Columns[i].ReadOnly = true; //make all columns (read-only)
}
//add (checkbox) column for selecting
DataColumn col = dt.Columns.Add("Selection", typeof(bool));
col.SetOrdinal(0); //set to left column
col.ReadOnly = false; //make this column (accessible only)
//add to grid
//dgvMySql.ItemsSource = EpicorDB.MySqlDataToDataTable(query).DefaultView;
dgvMySql.ItemsSource = dt.DefaultView;
}
catch (MySqlException ex2)
{
MessageBox.Show("There was a problem with the selection, please pick another sku#", "Error: MySQL Exception");
//MessageBox.Show("There was an error" + "\nMessage: " + ex2.Message + "\nStack: " + ex2.StackTrace, "Error: MySQL Exception");
}
catch (Exception ex1)
{
MessageBox.Show("There was a problem with the selection, please pick another sku#", "Error: General Exception");
//MessageBox.Show("There was an error" + "\nMessage: " + ex1.Message + "\nStack: " + ex1.StackTrace, "Error: General Exception");
}
}
