private string GetProductIDByCate(int cateID)
{
SqlConnection conn = DB.GetConnection();
string sql = "SELECT TOP 1 ProductID FROM Product WHERE productCategoryID = @cateID ORDER BY productID desc";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@cateID", cateID);
try
{
SqlDataReader dr;
string productID = "";
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
dr = cmd.ExecuteReader();
if (dr.Read())
{
string currentID = dr.GetString(0);
if (currentID != null)
{
string[] tmp = currentID.Split('_');
int temp = int.Parse(tmp[1]);
temp++;
productID = tmp[0] + "_" + temp;
}
}
if (productID.Equals(""))
{
sql = "select description from Cate where cateID = @ID";
cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@ID", cateID);
if (conn.State == ConnectionState.Closed)
{
conn.Open();
}
dr.Close();
dr = cmd.ExecuteReader();
if (dr.Read())
{
productID = dr.GetString(0) + "_1";
}
}
return(productID);
}
catch
{
throw;
}
finally
{
conn.Close();
}
}
public DataTable GetAllProduct()
{
DataTable dtProduct = new DataTable();
using (SqlConnection conn = DB.GetConnection())
{
conn.Open();
string sql = "select * from product where isDeleted = 0";
SqlDataAdapter da = new SqlDataAdapter(sql, conn);
da.Fill(dtProduct);
}
return(dtProduct);
}
public Product FindProduct(string ProductID)
{
Product p = null;
using (SqlConnection conn = DB.GetConnection())
{
string sql = "select * from product where ProductID = @ID";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@ID", ProductID);
try
{
SqlDataReader dr;
if (conn.State == ConnectionState.Closed)
{
conn.Open();
dr = cmd.ExecuteReader();
if (dr.Read())
{
p = new Product();
p.ProductID = ProductID;
p.CateID = dr.GetInt32(1);
p.createdDate = dr.GetSqlDateTime(2).Value;
p.UnitPrice = (float)dr.GetDouble(3);
p.ProductName = dr.GetString(4);
p.IsDeleted = dr.GetBoolean(5);
p.Image = dr.GetString(6);
p.Stock = dr.GetInt32(7);
p.Description = dr.GetString(8);
}
}
}
//catch(Exception ex)
//{
// throw ;
//}
finally
{
conn.Close();
}
}
return(p);
}
public bool deleteAccount(string staffUsername)
{
bool result = false;
using (SqlConnection conn = DB.GetConnection())
{
string sql = "delete from account where username = @username";
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.Parameters.AddWithValue("@username", staffUsername);
try
{
if (conn.State == ConnectionState.Closed)
{
conn.Open();
result = cmd.ExecuteNonQuery() > 0;
}
}
finally
{
conn.Close();
}
}
return(result);
}
