How reduce stock quantity in SQL Server and show messagebox when is zero in C#-CodePudding

The update is working fine, but stock value when is purchased I want to show messagebox, and stop the purchase when the value is zero in the stock update code.

I tried this code, but he only reduces value if the quantity is zero showing minus in the stock value when to stop when the value is equal to zero.

private void updateQty()
{
    try
    {
       int newqty = stock - Convert.ToInt32(txtnumberofunit.Text);
       con.Open();

       SqlCommand cmd = new SqlCommand("Update medic Set quantity=@q where id=@Xkey ", con);
       //stock=Convert.ToInt32(dr)

       cmd.Parameters.AddWithValue("@q", newqty);
       cmd.Parameters.AddWithValue("@Xkey", key);

       cmd.ExecuteNonQuery();

       MessageBox.Show("Medicine updated!!");
       con.Close();

       //showExpenses();
       //Reset();
     }
     catch (Exception ex)
     {
          MessageBox.Show(ex.Message);
     }
}

CodePudding user response：

The following first asserts there is sufficient stock, if not, alert caller else update the stock. This assumes no other users are working with the same item.

Note the use of delegates and that the database does not match your database but the same will work for your code with adjustments.

public class DataOperations
{
    private const string ConnectionString 
        = "Data Source=.\\sqlexpress;Initial Catalog=NorthWind2020;Integrated Security=True";

    public delegate void OnProcessing(string text);
    public static event OnProcessing Processed;
    public static void UpdateProductStockCount(int id, int amount)
    {
        using (var cn = new SqlConnection(ConnectionString))
        {
            using (var cmd = new SqlCommand() { Connection = cn })
            {
                cmd.CommandText = "SELECT UnitsInStock  FROM dbo.Products WHERE ProductID = @Id";
                cmd.Parameters.Add("@Id", SqlDbType.Int).Value = id;
                
                cn.Open();
                
                var currentCount = (short)cmd.ExecuteScalar();
                
                if (currentCount - amount <0)
                {
                    Processed?.Invoke("Insufficient stock");
                }
                else
                {
                    cmd.CommandText = "UPDATE dbo.Products SET UnitsInStock = @InStock WHERE ProductID = @Id";
                    cmd.Parameters.Add("@InStock", SqlDbType.Int).Value = currentCount - amount;
                    
                    cmd.ExecuteNonQuery();
                    
                    Processed?.Invoke("Processed");
                }           
            } 
        }
    }
}

Form code

public partial class StackoverflowForm : Form
{
    public StackoverflowForm()
    {
        InitializeComponent();
        DataOperations.Processed  = DataOperationsOnProcessed;
    }

    private void DataOperationsOnProcessed(string text)
    {
        if (text == "Insufficient stock")
        {
            MessageBox.Show($"Sorry {text} ");
        }
        else
        {
            MessageBox.Show(text);
        }
    }

    private void updateButton_Click(object sender, EventArgs e)
    {
        DataOperations.UpdateProductStockCount(21,1);
    }
}

CodePudding user response：

As @BagusTesa suggested, a simple if could do the trick:

private void updateQty()
{
    try
    {
       int newqty = stock - Convert.ToInt32(txtnumberofunit.Text);
       if (newqty >= 0) // proceed
       {
           con.Open();

           SqlCommand cmd = new SqlCommand("Update medic Set quantity=@q where id=@Xkey ", con);

           cmd.Parameters.AddWithValue("@q", newqty);
           cmd.Parameters.AddWithValue("@Xkey", key);

           cmd.ExecuteNonQuery();

           MessageBox.Show("Medicine updated!!");
           con.Close();
        }
        else // cancel purchase
        {
            MessageBox.Show("New quantity is below 0, purchase cancelled");
        }
     }
     catch (Exception ex)
     {
          MessageBox.Show(ex.Message);
     }
}