public float dbAvailableGroceriesExicst(AvailableGroceriesDetails availableGroceries)
{
if (con1.State != System.Data.ConnectionState.Open)
{
con1.Open();
}
SqlCommand command = new SqlCommand("SELECT * FROM [AvailableGroceries] WHERE GroceryName=@GroceryName AND GroupID=@GroupID", con1);
command.Parameters.Add("@GroceryName", SqlDbType.VarChar).Value = availableGroceries.Grocery.Name;
command.Parameters.Add("@GroupID", SqlDbType.VarChar).Value = availableGroceries.GroupID;
SqlDataReader reader = command.ExecuteReader();
if (reader.Read() == true)
{
float prosledi = float.Parse(reader.GetValue(0).ToString());
reader.Close();
command.Dispose();
con1.Close();
return(prosledi);
}
reader.Close();
command.Dispose();
con1.Close();
return(-1);
}
public List <AvailableGroceriesDetails> dbGetAvailableGroceries(string id)
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
List <AvailableGroceriesDetails> listAvailableGroceries = new List <AvailableGroceriesDetails>();
SqlCommand cmd = new SqlCommand("SELECT * FROM [AvailableGroceries] INNER JOIN [Groceries] ON AvailableGroceries.GroceryName=Groceries.Name WHERE GroupID=@id", con);
cmd.Parameters.Add("@id", System.Data.SqlDbType.VarChar).Value = id;
SqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
AvailableGroceriesDetails availableGroceries = new AvailableGroceriesDetails();
GroceryDetails grocery = new GroceryDetails();
availableGroceries.Amount = float.Parse(reader.GetValue(0).ToString());
availableGroceries.GroupID = reader.GetValue(2).ToString();
grocery.Name = reader.GetValue(3).ToString();
if (reader.GetValue(4) != null)
{
grocery.Image = Convert.FromBase64String(reader.GetValue(4).ToString());
}
grocery.Unit = reader.GetValue(5).ToString();
grocery.Category = reader.GetValue(6).ToString();
availableGroceries.Grocery = grocery;
listAvailableGroceries.Add(availableGroceries);
}
reader.Close();
cmd.Dispose();
con.Close();
return(listAvailableGroceries);
}
public void dbAvailableGroceriesInsert(AvailableGroceriesDetails availableGroceries)
{
float proveriNamirnicu = dbAvailableGroceriesExicst(availableGroceries);
if (proveriNamirnicu != -1)
{
proveriNamirnicu += availableGroceries.Amount;
dbUpdateAvailableGroceries(proveriNamirnicu, availableGroceries.Grocery.Name, availableGroceries.GroupID);
}
else
{
dbAvailableGroceriesExicst1(availableGroceries);
}
}
public void dbAvailableGroceriesExicst1(AvailableGroceriesDetails availableGroceries)
{
if (con.State != System.Data.ConnectionState.Open)
{
con.Open();
}
SqlCommand cmd = new SqlCommand("INSERT INTO [AvailableGroceries] (Amount,GroceryName,GroupID) VALUES(@Amount,@GroceryName,@GroupID)", con);
cmd.Parameters.Add("@Amount", SqlDbType.Float).Value = availableGroceries.Amount;
cmd.Parameters.Add("@GroceryName", SqlDbType.VarChar).Value = availableGroceries.Grocery.Name;
cmd.Parameters.Add("@GroupID", SqlDbType.VarChar).Value = availableGroceries.GroupID;
int test = cmd.ExecuteNonQuery();
cmd.Dispose();
con.Close();
}