private void fmImport_Load(object sender, EventArgs e)
{
DataGridViewTextBoxColumn col = new DataGridViewTextBoxColumn();
string column = "Columns";
col.HeaderText = column;
col.Name = "f" + column;
dgXLTable.Columns.Add(col);
col = new DataGridViewTextBoxColumn();
col.HeaderText = column;
col.Name = "f" + column;
dgXLTransfer.Columns.Add(col);
col = new DataGridViewTextBoxColumn();
col.HeaderText = column;
col.Name = "f" + column;
dgCWTransfer.Columns.Add(col);
col = new DataGridViewTextBoxColumn();
col.HeaderText = column;
col.Name = "f" + column;
dgCWTable.Columns.Add(col);
try
{
MySqlDataReader rdr = null;
string table = "";
rdr = MysqlInterface.DoQuery("SHOW TABLES;");
while (rdr.Read())
{
table = rdr.GetString(0);
cbTable.Items.Add(table);
}
}
catch (MySqlException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
}
finally
{
MysqlInterface.close();
}
}
//string stm = "SELECT id, description, purchase_price FROM part"
public static MySqlDataReader DoQuery(string qry)
{
try
{
MysqlInterface.close();
MysqlInterface.init();
MySqlDataReader rdr = null;
MySqlCommand cmd = new MySqlCommand(qry, con);
rdr = cmd.ExecuteReader();
cmd.Dispose();
return(rdr);
}
catch (MySqlException err)
{
Console.WriteLine("Error: " + err.ToString());
return(null);
}
}
private void cbTable_SelectedIndexChanged(object sender, EventArgs e)
{
dgCWTable.RowCount = 1;
dgCWTable.Rows.Clear();
dgCWTable.Columns.Clear();
dgCWTable.AutoGenerateColumns = false;
try
{
MySqlDataReader rdr = null;
string table = cbTable.Text;
string column = "";
rdr = MysqlInterface.DoQuery("SHOW COLUMNS FROM " + table + ";");
DataGridViewTextBoxColumn col = new DataGridViewTextBoxColumn();
column = "Columns";
col.HeaderText = column;
col.Name = "f" + column;
dgCWTable.Columns.Add(col);
while (rdr.Read())
{
column = rdr.GetString(0);
string[] row = { column };
dgCWTable.Rows.Add(row);
}
}
catch (MySqlException ex)
{
Console.WriteLine("Error: {0}", ex.ToString());
}
finally
{
MysqlInterface.close();
}
}
public static void LoadParts()
{
MySqlDataReader rdr = null;
string id = "";
int count = 0;
string desc = "";
string qty = "";
string pp = "";
string sp = "";
rdr = MysqlInterface.DoQuery("SELECT count(id) FROM part");
while (rdr.Read())
{
count = rdr.GetInt32(0);
}
Globals.PART_ARRAY = new string[count, 5];
rdr = MysqlInterface.DoQuery("SELECT id, description, qty_on_hand, purchase_price, selling_price FROM part");
int i = 0;
while (rdr.Read())
{
if (!rdr.IsDBNull(0))
{
id = rdr.GetString(0);
}
else
{
id = "";
}
Globals.PART_ARRAY[i, 0] = id;
if (!rdr.IsDBNull(1))
{
desc = rdr.GetString(1);
}
else
{
desc = "";
}
Globals.PART_ARRAY[i, 1] = desc;
if (!rdr.IsDBNull(2))
{
qty = rdr.GetString(2);
}
else
{
qty = "";
}
Globals.PART_ARRAY[i, 2] = qty;
if (!rdr.IsDBNull(3))
{
pp = rdr.GetString(3);
}
else
{
pp = "";
}
Globals.PART_ARRAY[i, 3] = pp;
if (!rdr.IsDBNull(4))
{
sp = rdr.GetString(4);
}
else
{
sp = "";
}
Globals.PART_ARRAY[i, 4] = sp;
i++;
}
}
public static string CreateNewTruck(string desc, string carrier)
{
MySqlConnection con = null;
MySqlDataReader reader = null;
string tId = "";
try
{
string qry = "SELECT truck_id FROM current_values;";
reader = MysqlInterface.DoQuery(qry);
while (reader.Read())
{
tId = reader.GetString(0);
}
string host = MysqlInterface.host;
string dbase = MysqlInterface.dbase;
string user = MysqlInterface.user;
string password = MysqlInterface.password;
String str = @"server=" + host + ";database=" + dbase + ";userid=" + user + "; password="******";";
con = new MySqlConnection(str);
con.Open(); //open the connection
string cmdText = "INSERT INTO truck (id, description, shipper_id)" +
"VALUES(@id, @description, @shipper_id)";
MySqlCommand cmd = new MySqlCommand(cmdText, con);
cmd.Prepare();
cmd.Parameters.AddWithValue("@id", tId);
cmd.Parameters.AddWithValue("@description", desc);
cmd.Parameters.AddWithValue("@shipper_id", carrier);
cmd.ExecuteNonQuery();
str = @"server=" + host + ";database=" + dbase + ";userid=" + user + "; password="******";";
con = new MySqlConnection(str);
con.Open();
string newTId;
newTId = (Convert.ToInt32(tId) + 1).ToString();
cmd.Connection = con;
cmd.CommandText = "UPDATE current_values SET truck_id = '" + newTId + "';";
int numRowsUpdated = cmd.ExecuteNonQuery();
}
catch (MySqlException err)
{
Console.WriteLine("Error: " + err.ToString());
}
finally
{
if (con != null)
{
con.Close(); //close the connection
}
if (reader != null)
{
reader.Close();
}
}
return(tId);
}
