DataTable dt = new DataTable(); //hold data temporary
public ProductsBLL GetProductIdFromBarcode(string ProductBarcode)
{
ProductsBLL p = new ProductsBLL();
try
{
string sql = "SELECT P_Id from Products WHERE P_Barcode='" + ProductBarcode + "'";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
conn.Open();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
p.P_Id = int.Parse(dt.Rows[0]["P_Id"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
} return(p);
}
// Get Item details using the barcode
public ProductsBLL GetProductsFromBarcode(string keyword)
{
ProductsBLL p = new ProductsBLL();
SqlConnection conn = new SqlConnection(myconnstrng);
DataTable dt = new DataTable();
try
{
string sql = "SELECT P_Name,P_Price FROM Products WHERE P_Barcode LIKE '%" + keyword + "%' ";
SqlDataAdapter adapter = new SqlDataAdapter(sql, conn);
conn.Open();
adapter.Fill(dt);
if (dt.Rows.Count > 0)
{
// p.P_Barcode = dt.Rows[0]["P_Barcode"].ToString();
p.P_Name = dt.Rows[0]["P_Name"].ToString();
p.P_Price = decimal.Parse(dt.Rows[0]["P_Price"].ToString());
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
conn.Close(); return(p);
}
//Delete products from database
public bool Delete(ProductsBLL p)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{
string sql = "Delete from Products WHERE P_Id=@P_Id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@P_Id", p.P_Id);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
// Update poducts in database
public bool Update(ProductsBLL p)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
try
{//meke poddak balanna sql eke where P_Barcode=@P_Barcode enawada kiyala anthimata.
string sql = "Update Products SET P_Barcode=@P_Barcode, P_Name=@P_Name, P_Price=@P_Price WHERE P_Id=@P_Id";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@P_Id", p.P_Id);
cmd.Parameters.AddWithValue("@P_Barcode", p.P_Barcode);
cmd.Parameters.AddWithValue("@P_Name", p.P_Name);
cmd.Parameters.AddWithValue("@P_Price", p.P_Price);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
finally
{
conn.Close();
}
return(isSuccess);
}
private void txtbarcode_TextChanged(object sender, EventArgs e)
{
string keyword = txtbarcode.Text;
if (keyword == "")
{
txtname.Text = null;
txtprice.Text = "0.00";
txtqty.Value = 0;
txtTotalPrice.Text = "0.00";
return;
}
ProductsBLL p = pDAL.GetProductsFromBarcode(keyword);
txtname.Text = p.P_Name;
txtprice.Text = (p.P_Price).ToString();
// pictureBox1.Image = pDAL.ConvertByteArrayToImage(Image);
byte[] pictureBox1 = (byte[])p.P_Image;
txtqty.Value = 1;
txtTotalPrice.Text = (txtqty.Value * Convert.ToDecimal(txtprice.Text)).ToString();
}
//Inserting data into Products table.
public bool Insert(ProductsBLL p)
{
bool isSuccess = false;
SqlConnection conn = new SqlConnection(myconnstrng);
/* try
* {*/
String sql = "Insert into Products(P_Barcode,P_Name,P_Price,P_Image) values (@P_Barcode, @P_Name, @P_Price, @P_Image)";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@P_Barcode", p.P_Barcode);
cmd.Parameters.AddWithValue("@P_Name", p.P_Name);
cmd.Parameters.AddWithValue("@P_Price", p.P_Price);
cmd.Parameters.AddWithValue("@P_Image", p.P_Image);
conn.Open();
int rows = cmd.ExecuteNonQuery();
if (rows > 0)
{
isSuccess = true;
}
else
{
isSuccess = false;
}
/*}
* catch (Exception ex)
* {
* MessageBox.Show(ex.Message);
* }
* finally
* {*/
conn.Close();
// }
return(isSuccess);
}
