private static List<ProductInfo> searchAllProductList(ProductInfo productinfo)
{
List<ProductInfo> ProductList = new List<ProductInfo>();
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "Select * From product where id = @input or name = @input or brand = @input or type = @input or description = @input ; ";
msqlCommand.Parameters.AddWithValue("@input", productinfo.name);
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
while (msqlReader.Read())
{
ProductInfo Product = new ProductInfo();
Product.id = msqlReader.GetString("id");
Product.name = msqlReader.GetString("name");
Product.brand = msqlReader.GetString("brand");
Product.type = msqlReader.GetString("type");
Product.description = msqlReader.GetString("description");
ProductList.Add(Product);
}
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
return ProductList;
}
public static List<ProductInfo> searchProductList(ProductInfo productinfo)
{
return searchAllProductList(productinfo);
}
private static List<ProductInfo> QueryAllProductList()
{
List<ProductInfo> ProductList = new List<ProductInfo>();
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "Select * From product;";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
while (msqlReader.Read())
{
ProductInfo Product = new ProductInfo();
Product.id = msqlReader.GetString("id");
Product.name = msqlReader.GetString("name");
Product.brand = msqlReader.GetString("brand");
Product.type = msqlReader.GetString("type");
Product.description = msqlReader.GetString("description");
Product.availableinshop = msqlReader.GetString("availableinshop");
ProductList.Add(Product);
}
}
catch (Exception er)
{
}
finally
{
msqlConnection.Close();
}
return ProductList;
}
public static List<ProductInfo> GetSelectedProductList(ProductInfo productInfoObj)
{
List<ProductInfo> ProductList = new List<ProductInfo>();
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{ //define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "Select * From product where name = @name;";
MySql.Data.MySqlClient.MySqlDataReader msqlReader = msqlCommand.ExecuteReader();
while (msqlReader.Read())
{
ProductInfo Product = new ProductInfo();
msqlCommand.Parameters.AddWithValue("@name", Product.name);
ProductList.Add(Product);
}
}
catch (Exception er)
{
}
finally
{
msqlConnection.Close();
}
return ProductList;
}
public static void EditProduct(ProductInfo newUpdateProduct)
{
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{
//define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "UPDATE product SET name=@name,brand=@brand,type=@type,description=@description WHERE id=@id";
msqlCommand.Parameters.AddWithValue("@name", newUpdateProduct.name);
msqlCommand.Parameters.AddWithValue("@brand", newUpdateProduct.brand);
msqlCommand.Parameters.AddWithValue("@type", newUpdateProduct.type);
msqlCommand.Parameters.AddWithValue("@description", newUpdateProduct.description);
msqlCommand.Parameters.AddWithValue("@id", newUpdateProduct.id);
msqlCommand.ExecuteNonQuery();
}
catch (Exception er)
{
}
finally
{
//always close the connection
msqlConnection.Close();
}
}
public static int DoEnterProduct(ProductInfo NewProduct)
{
int returnVal = 0;
MySql.Data.MySqlClient.MySqlConnection msqlConnection = OpenDbConnection();
try
{
//define the command reference
MySql.Data.MySqlClient.MySqlCommand msqlCommand = new MySql.Data.MySqlClient.MySqlCommand();
//define the connection used by the command object
msqlCommand.Connection = msqlConnection;
msqlCommand.CommandText = "INSERT INTO product(id,name,brand,type,description,availableinshop) "
+ "VALUES(@id,@name,@brand,@type,@description,@availableinshop)";
msqlCommand.Parameters.AddWithValue("@id", NewProduct.id);
msqlCommand.Parameters.AddWithValue("@name", NewProduct.name);
msqlCommand.Parameters.AddWithValue("@brand", NewProduct.brand);
msqlCommand.Parameters.AddWithValue("@type", NewProduct.type);
msqlCommand.Parameters.AddWithValue("@description", NewProduct.description);
msqlCommand.Parameters.AddWithValue("@availableinshop", NewProduct.availableinshop);
msqlCommand.ExecuteNonQuery();
returnVal = 1;
}
catch (Exception er)
{
returnVal = 0;
}
finally
{
//always close the connection
msqlConnection.Close();
}
return returnVal;
}
private void goProductBtn_Click(object sender, RoutedEventArgs e)
{
if (productNameSrchCB.Text == "")
fetchProductData();
else
{
ProductInfo prodctInfo = new ProductInfo();
prodctInfo.name = productNameSrchCB.Text;
List<ProductInfo> products = DbInteraction.searchProductList(prodctInfo);
_productsCollection.Clear();
foreach (ProductInfo product in products)
{
_productsCollection.Add(product);
}
}
}
private void submitproductkBtn_Click(object sender, RoutedEventArgs e)
{
string availableinshop = "";
for (int i = 0; i < availableShopLView.Items.Count; i++)
{
availableinshop += availableShopLView.Items[i].ToString() + ", ";
}
if (!nameTB.Text.Equals("") && !BrandTB.Text.Equals("") && !ProductypeCB.Text.Equals("") && !productdescriptionTB.Text.Equals(""))
{
ShoppingMallData.ProductInfo newProduct = new ShoppingMallData.ProductInfo();
newProduct.id = GenerateId();
newProduct.name = nameTB.Text;
newProduct.brand = BrandTB.Text;
newProduct.type = ProductypeCB.Text;
newProduct.description = productdescriptionTB.Text;
newProduct.availableinshop = availableinshop;
//newProduct.image = shopimgPhoto.BitmapImage;
ShoppingMallDb.DbInteraction.DoEnterProduct(newProduct);
clearProductFields();
fetchProductData();
//takepic();
}
else
{
MessageBox.Show("Please Insert Info Properly");
}
}