How to add quantity in existing item in DataGridView?

Question

I have a form that asks 3 inputs from a user. These inputs are ProductName, Quantity, and Price. These 3 inputs will proceed into a database and it will display in a datagridview, which is in a separate form. I want that if I will add an item, it will only combine with the item inside the database, if they have the same input/data. For example, if I will add the product "plates" with a price of 50 and a quantity of 3 in my database and my database has already the product "plates" with the same price, it will just be combined or the quantity will be added instead of adding a new cell.

Here's my code for the Add button:

 double presyo, qty;
 presyo = double.Parse(txtPrice.Text);
 qty = double.Parse(txtQuantity.Text);

con = new SqlConnection(@"Data Source=.\sqlexpress;Initial Catalog=CasaFranca;Integrated Security=True");
con.Open();
cmd = new SqlCommand("INSERT INTO Sales (ProductName, Quantity, Price, Total) VALUES (@ProductName, @Quantity, @Price, @Total)", con);
cmd.Parameters.Add("@ProductName", cmbProduct.Text);
cmd.Parameters.Add("@Quantity", txtQuantity.Text);
cmd.Parameters.Add("@Price", txtPrice.Text);
cmd.Parameters.Add("@Total", (presyo * qty));
cmd.ExecuteNonQuery();
MessageBox.Show("Item sucessfully added to database");

Here's a sample output:

As you can see, they're stored separately even if they have the same product name and price. I want them to be merged as one and their quantities be added. What should I do?

Answer 1

Hmm.. you've gone about your basic database access in the lowest-level/hardest/most tedious way possible, so I'll presume you'll want to carry on in this way, but I think it would make life easier if you consider switching to some library designed to do data access (datasets, EF) but at the very least you should investigate Dapper..

Run an update and if it produces 0 rows, run an insert instead:

con.Open();
var ins = "INSERT INTO Sales (ProductName, Quantity, Price, Total) VALUES (@ProductName, @Quantity, @Price, @Total)";
var upd = "UPDATE Sales SET Quantity = Quantity + @Quantity, Price = Price + @Price, Total = Total + @Total WHERE ProductName = @ProductName";

cmd = new SqlCommand(upd, con);
cmd.Parameters.Add("@ProductName", cmbProduct.Text);
cmd.Parameters.Add("@Quantity", txtQuantity.Text);
cmd.Parameters.Add("@Price", txtPrice.Text);
cmd.Parameters.Add("@Total", (presyo * qty));
if(cmd.ExecuteNonQuery() == 0){
    cmd.CommandText = ins;
    cmd.ExecuteNonQuery();
}

Note that this isn't very concurrent safe; if two people make the sale at exactly the same time you can still get duplicated rows.. Consider instead putting a primary key, use it in the query

Also, there isn't any need to store the total; you can calculate it any time you want. I would also say that, unless this is for a single shopping basket where the price of an item is guaranteed same, it would probably make some sense to store multiple rows if it's a sales history log, otherwise you lose info like per-sale price changes or discounts if all you ever do is store a total

Answer 2

Your question has nothing to do with the DataGridView. Since I see that your displayed table is updated correctly, I assume that you've mastered displaying your data.

So let's focus on your SQL.

Hide how you access the data: implement a Repository

Try to convince your project leader that you would be way more productive if you could switch your interface to Entity Framework or Dapper.

Even if you can't convince him, you will need a class that represents one row of your table, and a repository pattern to Add / Fetch / Update / Delete items from the table.

Something like this:

class Sale
{
    public Id {get; set; }
    public string ProductName {get; set;}
    public int Quantity {get; set;}
    public decimal Price {get; set;}
    public decimal Total {get; set;}
}

The repository class, is a class that hides how you store your Sales. It can be in a database, but for all you know, it can be in an XML file, or somewhere on the internet. Or maybe internally it is just a Dictionary for your unit tests.

So the repository is an object that represents some storage: you can store items of some classes in it, and later you can retrieve the stored items, even after a restart of the computer.

The nice thing about this hiding of how the items are stored, is that you are free to change your internal structure without having to change the code that uses the Repository. (and your unit tests!).

It could be that later you decide change your database such that it has the first normalized form: a separate table with Product information, and in your Sales a foreign key to the Product table, so you can get the name from the Product table, instead that every Sale has the same product name over and over again.

By hiding how the items are stored in the repository, users of the repository won't know, and won't have to know that Sales are in fact stored in separate tables.

A typical repository class will implement an interface. In your case it will have methods like:

interface ISaleRepository
{
    int Add(Sale sale);     // return the primary key
    Sale Fetch(int id);     // fetch by primary key

    Update(Sale sale);
    Remove(int id);

    int Count();           // returns number of Sales;
    IEnumerable<Sale> FetchAll();
}

I don't know your Sales model, but if you think ProductName is unique, consider to add:

Sale Fetch(string productName);  // fetch by unique productName

Back to your question

You want an "Add or Update": if the Sale is already in the database, only update the Quantity:

Sale AddOrUpdate(Sale sale);  // returns the updated Sale

If you are not interested in the updated Sale, consider to return void.

Your AddOrUpdate can reuse other ISaleRepository methods:

public Sale AddOrUpdate(Sale sale)
{
    // Is there already a Sale?
    Sale existingSale = this.Fetch(sale.ProductName);
    if (existingSale == null)
    {
        // no such Sale yet. Add it now:
        this.Add(sale);  // don't forget to update the Id! Usually done in Add
        existingSale = sale;
    }
    else
    {
        // Sale exists. Update
        existingSale.Quantity += sale.Quantity;
        this.Update(existingSale);
    }
    return existingSale; // return the updated value as it is in the database
}

This solution requires two database accesses. I assume that you won't be AddOrUpdate items hundreds of times per second. In that case the reusability and simplicity of the code weighs up against performance loss.

If you really have to watch your performance, some SQL dialects let you update in one go.

Since I use entity framework, my SQL is a bit rusty, but this solution gets you on track

const string sqlText = @"
begin tran
    if exists (select * from table with (updlock,serializable) where key = @key)
    begin
       update table set ...
       where key = @key
    end
else
begin
   insert into table (key, ...) values (@key, ...)
end
commit tran";

string dbConnectionString = FetchDbConnectionString();
using (var dbConnection = new dbConnection(dbConnectionString))
{
    using (var dbCommand = dbConnection.CreateCommand())
    {
        dbCommand.CommandText = sqlText;

        // add parameters:
        dbCommand.Parameters.AddWithValue(@ProductName, sale.ProductName);
        dbCommand.Parameters.AddWithValue(@Quantity, sale.Quantity);
        ...

        // Open the database and execute the query:
        dbConnection.Open();
        ...
    }
}

A normal Add without fetching data would be:

dbCommand.ExecuteNonQuery();

If you return only the Id of the added item:

return (int)dbCommand.ExecuteScalar();

If you are returning the updated data:

using (var dbReader = dbcommand.ExecuteReader())
{
     // expect only one item:
     if (dbReader.MoveNext())
     {
         return new Sale
         {
              ProductName = dbReader.GetString(0),
              Quantity = dbReader.GetInt32(1),
              ...
         };
     }
     // else: nothing returned. Decide what to do: exception?
}