public static List <Products_suppliers> orderby(string coluName)
{
SqlConnection connection = TRAExpertsDB.GetConnection();
List <Products_suppliers> results = new List <Products_suppliers>();
try
{
string sql = "SELECT * FROM [dbo].[Products_Suppliers] order by" + "'" + coluName + "'";
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
Products_suppliers s = new Products_suppliers();
s.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"].ToString());
s.ProductId = Convert.ToInt32(reader["ProductId"].ToString());
s.SupplierId = Convert.ToInt32(reader["SupplierId"].ToString());
results.Add(s);
}
}
catch { }
finally
{
connection.Close();
}
return(results);
}
public static Products_suppliers GetProduct_Suppliers(int ID)
{
SqlConnection connection = DataLayer.TRAExpertsDB.GetConnection();
Products_suppliers s = new Products_suppliers();
try
{
string sql = "SELECT * FROM [dbo].[Products_Suppliers] where ProductSupplierId=" + ID;
SqlCommand command = new SqlCommand(sql, connection);
SqlDataReader reader = command.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
while (reader.Read())
{
s.ProductSupplierId = Convert.ToInt32(reader["ProductSupplierId"].ToString());
s.ProductId = Convert.ToInt32(reader["ProductId"].ToString());
s.SupplierId = Convert.ToInt32(reader["SupplierId"].ToString());
//results.ADD(s);
//test
}
}
catch { }
finally
{
connection.Close();
}
return(s);
}
private void button1_Click(object sender, EventArgs e)
{
Products_suppliers pro = new Products_suppliers();
int ID = Convert.ToInt32(textBox1.Text);
pro = DataLayer.Product_SuppliersDB.GetProduct_Suppliers(ID);
textBox2.Text = pro.ProductId.ToString();
textBox3.Text = pro.SupplierId.ToString();
}
// SAVE btn clicked: update or add new
private void twoBtnSave_Click(object sender, EventArgs e)
{
if (twoTab.SelectedIndex == 1) // EDIT MODE
{
// get the current Product_supplier obj in order to compare with new one
var currentPS = Products_suppliersDB.GetProductsSuppliers()
.SingleOrDefault(ps => ps.ProductSupplierId == Convert.ToInt32(twoTxtProdSuppId.Text));
// create new Product_supplier obj using user's change
var newProdSupp = new Products_suppliers
{
ProductId = Convert.ToInt32(twoCmbProdName.SelectedValue),
SupplierId = Convert.ToInt32(twoCmbSupName.SelectedValue)
};
// compare old and new, see if there is any change
if (currentPS.ProductId == newProdSupp.ProductId &&
currentPS.SupplierId == newProdSupp.SupplierId)
{
// no change, show message
MessageBox.Show("No change were found.", "Please make changes");
}
else
{
// have change, try to update database
try
{
var rowsAffected = Products_suppliersDB.UpdateProductSupplier(currentPS, newProdSupp);
MessageBox.Show(rowsAffected + " record is successfully updated.");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
else if (twoTab.SelectedIndex == 2) // ADD MODE
{
// do validation, make sure user select Product and Supplier
if (twoCmbAddProdName.SelectedIndex < 0 ||
twoCmbAddSuppName.SelectedIndex < 0)
{
MessageBox.Show("Required data missing.", "Please choose Product and Supplier");
twoCmbAddProdName.SelectedIndex = -1;
twoCmbAddSuppName.SelectedIndex = -1;
return;
}
// create new Product_supplier obj using user's choices
var newProdSupp = new Products_suppliers
{
ProductId = Convert.ToInt32(twoCmbAddProdName.SelectedValue),
SupplierId = Convert.ToInt32(twoCmbAddSuppName.SelectedValue)
};
// try to insert new obj
try
{
var newId = Products_suppliersDB.AddProductSupplier(newProdSupp);
MessageBox.Show($"Product Supplier was successfully added, new record id: {newId}.", "Congratulations");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
