public static bool ModifyProduct(ProductClass product)
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string updateStatement =
"UPDATE products SET " +
"name = @name, " +
"description = @description, " +
"updated = @updated, " +
"cost = @cost, " +
"status = @status " +
" WHERE id = @id ";
SqlCommand updateCommand =
new SqlCommand(updateStatement, connection);
updateCommand.Parameters.AddWithValue(
"@id", product.idProduct);
updateCommand.Parameters.AddWithValue(
"@name", product.nameProduct);
updateCommand.Parameters.AddWithValue(
"@description", product.descriptionProduct);
updateCommand.Parameters.AddWithValue(
"@updated", product.updatedProduct);
updateCommand.Parameters.AddWithValue(
"@cost", product.costProduct);
updateCommand.Parameters.AddWithValue(
"@status", product.idStatusProduct);
try
{
connection.Open();
int count = updateCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool AddProduct(ProductClass product)
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string insertStatement =
"INSERT INTO products " +
"( name, description, created, updated, cost, status ) " +
"VALUES ( @name, @description, @created, @updated, @cost, @status )";
SqlCommand insertCommand =
new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue(
"@name", product.nameProduct);
insertCommand.Parameters.AddWithValue(
"@description", product.descriptionProduct);
insertCommand.Parameters.AddWithValue(
"@created", product.createdProduct);
insertCommand.Parameters.AddWithValue(
"@updated", product.updatedProduct);
insertCommand.Parameters.AddWithValue(
"@cost", product.costProduct);
insertCommand.Parameters.AddWithValue(
"@status", product.idStatusProduct);
try
{
connection.Open();
int count = insertCommand.ExecuteNonQuery();
if (count > 0)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <ProductClass> SearchProduct(string search) ////////////////////////////
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string selectStatement
= "SELECT id, name, description, created, updated, cost, status "
+ "FROM products "
+ "WHERE name = @name" +
" OR cost = @cost";
SqlCommand selectcommand =
new SqlCommand(selectStatement, connection);
selectcommand.Parameters.AddWithValue("@name", search);
selectcommand.Parameters.AddWithValue("@cost", search);
try
{
connection.Open();
SqlDataReader custReader =
selectcommand.ExecuteReader();
List <ProductClass> products = new List <ProductClass>();
while (custReader.Read())
{
ProductClass product = new ProductClass();
product.idProduct = (int)custReader["id"];
product.nameProduct = custReader["name"].ToString();
product.descriptionProduct = custReader["description"].ToString();
product.updatedProduct = Convert.ToDateTime(custReader["updated"]);
product.createdProduct = Convert.ToDateTime(custReader["created"]);
product.costProduct = Convert.ToDouble(custReader["cost"]);
product.idStatusProduct = Convert.ToInt32(custReader["status"]);
products.Add(product);
}
custReader.Close();
return(products);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
private void btnAddProduct_Click(object sender, EventArgs e)
{
if (IsValidData())
{
addproduct.nameProduct = txtNameProduct.Text;
addproduct.descriptionProduct = txtDescriptionProduct.Text;
addproduct.createdProduct = DateTime.Now;
txtCreatedProduct.Text = createdProduct.ToString("d");
addproduct.updatedProduct = DateTime.Now;
txtUpdatedProduct.Text = updatedProduct.ToString("d");
addproduct.costProduct = Convert.ToDouble(txtCostProduct.Text);
addproduct.idStatusProduct = (int)comboBoxStatusProduct.SelectedValue;
this.added = addproduct;
this.DialogResult = DialogResult.OK;
this.Close();
}
}
private void btnAddProduct_Click(object sender, EventArgs e)
{
ProductClass modifyProduct = new ProductClass();
String x = "AddProduct";
AddProduct addProductClick = new AddProduct(x, modifyProduct);
var result = addProductClick.ShowDialog();
if (result == DialogResult.OK)
{
ProductClass AddProducts = addProductClick.added;
bool returnedResult = ProductDB_Class.AddProduct(AddProducts);
if (returnedResult)
{
FillListViewProduct();
MessageBox.Show("Product added");
}
else
{
MessageBox.Show("Error");
}
}
}
private void btnModifyProduct_Click(object sender, EventArgs e)
{
ProductClass modifyProduct = new ProductClass();
if (lstProduct.SelectedItems.Count > 0)
{
ListViewItem item = lstProduct.SelectedItems[0];
modifyProduct.nameProduct = item.SubItems[0].Text;
modifyProduct.descriptionProduct = item.SubItems[1].Text;
modifyProduct.createdProduct = Convert.ToDateTime(item.SubItems[2].Text);
modifyProduct.updatedProduct = Convert.ToDateTime(item.SubItems[3].Text);
modifyProduct.costProduct = Convert.ToDouble(item.SubItems[4].Text);
modifyProduct.idStatusProduct = Convert.ToInt32(item.SubItems[6].Text);
modifyProduct.idProduct = Convert.ToInt32(item.SubItems[7].Text);
String x = "ModifyProduct";
AddProduct modifyProductClick = new AddProduct(x, modifyProduct);
var result = modifyProductClick.ShowDialog();
if (result == DialogResult.OK)
{
ProductClass modifyingProduct = modifyProductClick.modifiedProduct;
bool returnedResult = ProductDB_Class.ModifyProduct(modifyingProduct);
if (returnedResult)
{
FillListViewProduct();
MessageBox.Show("Product changed");
}
else
{
MessageBox.Show("Error");
}
}
}
else
{
MessageBox.Show("Please select a product ");
}
}
public static List <ProductClass> GetProductByID(int order_id)
{
List <ProductClass> products = new List <ProductClass>();
SqlConnection connection = CompanyDB_Class.GetConnection();
string selectStatement = "SELECT products.name, products.description, products.cost, orders_products.amount "
+ " FROM products "
+ " INNER JOIN orders_products ON orders_products.product_id = products.id "
+ " WHERE order_id = @order_id";
SqlCommand selectcommand = new SqlCommand(selectStatement, connection);
selectcommand.Parameters.AddWithValue("@order_id", order_id);
try
{
connection.Open();
SqlDataReader custReader = selectcommand.ExecuteReader();
while (custReader.Read())
{
ProductClass product = new ProductClass();
product.nameProduct = custReader["name"].ToString();
product.descriptionProduct = custReader["description"].ToString();
product.costProduct = Convert.ToDouble(custReader["cost"]);
product.ammountProduct = Convert.ToInt32(custReader["amount"]);
products.Add(product);
}
return(products);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
private void btnReportProduct_Click(object sender, EventArgs e)
{
ProductClass reportProduct = new ProductClass();
if (lstProduct.SelectedItems.Count > 0)
{
ListViewItem item = lstProduct.SelectedItems[0];
reportProduct.nameProduct = item.SubItems[0].Text;
reportProduct.descriptionProduct = item.SubItems[1].Text;
reportProduct.createdProduct = Convert.ToDateTime(item.SubItems[2].Text);
reportProduct.updatedProduct = Convert.ToDateTime(item.SubItems[3].Text);
reportProduct.costProduct = Convert.ToDouble(item.SubItems[4].Text);
reportProduct.statusProduct = item.SubItems[5].Text;
reportProduct.idStatusProduct = Convert.ToInt32(item.SubItems[6].Text);
reportProduct.idProduct = Convert.ToInt32(item.SubItems[7].Text);
ReportProduct report = new ReportProduct(reportProduct);
report.Show();
}
else
{
MessageBox.Show("Please select a product ");
}
}
public static List <ProductClass> GetProductsName()
{
SqlConnection connection = CompanyDB_Class.GetConnection();
string selectStatement
= "SELECT id, name, cost "
+ "FROM products";
SqlCommand selectcommand =
new SqlCommand(selectStatement, connection);
try
{
connection.Open();
SqlDataReader custReader =
selectcommand.ExecuteReader();
List <ProductClass> products = new List <ProductClass>();
while (custReader.Read())
{
ProductClass product = new ProductClass();
product.idProduct = (int)custReader["id"];
product.nameProduct = custReader["name"].ToString();
product.costProduct = Convert.ToDouble(custReader["cost"]);
products.Add(product);
}
custReader.Close();
return(products);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public AddProduct(String x, ProductClass product)
{
modifyProducts = product;
y = x;
InitializeComponent();
}
public ReportProduct(ProductClass product)
{
products = product;
InitializeComponent();
}