Multiple rows updation in sql server

Question

Hi experts ,
Below is the database which i have created in sql server .

I have a datagridview in a form where a user in , column0 writes the productnames and in column2 writes the consumed quantity . The problem is that i dont know how to update the current_quantity column values when there are mutliple values to update from the datagridview .

for example if the datagridview have the values
backpack 30

basketball 20

bicycle 14

and the user presses update button somewhere on the form . how can i update all the values in sql server .

Answer 1

Below is the database which i have created in sql server .

No, its' not, it's a screenshot and I can't query screenshots.

Please post table design and some some sample data as SQL script and the expected result.

Answer 2

Use EF Core, for example in the following I take the liberty to change column names. Not sure why CurrentQuantity is a decimal and not an int but left it as is.

CREATE TABLE [dbo].[Products](
	[Id] [INT] IDENTITY(1,1) NOT NULL,
	[ProductName] [NVARCHAR](MAX) NULL,
	[CurrentQuantity] [DECIMAL](10, 2) NULL,
 CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED 
(
	[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

EF Core code generated by EF Power Tools.

Find full source here

EF Core model

public partial class Products : INotifyPropertyChanged
{
    private string _productName;
    private decimal? _currentQuantity;
    public int Id { get; set; }

    public string ProductName
    {
        get => _productName;
        set
        {
            if (value == _productName) return;
            _productName = value;
            OnPropertyChanged();
        }
    }

    public decimal? CurrentQuantity
    {
        get => _currentQuantity;
        set
        {
            if (value == _currentQuantity) return;
            _currentQuantity = value;
            OnPropertyChanged();
        }
    }

    public event PropertyChangedEventHandler PropertyChanged;
    protected virtual void OnPropertyChanged([CallerMemberName] string propertyName = null)
    {
        PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
    }
}

DContext

public partial class Context : DbContext
{
    public Context()
    {
    }

    public Context(DbContextOptions<Context> options)
        : base(options)
    {
    }

    public virtual DbSet<Products> Products { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseSqlServer(
            """
            Data Source=(localdb)\MSSQLLocalDB;
            Initial Catalog=Demo;
            Integrated Security=True
            """);

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Products>(entity =>
        {
            entity.Property(e => e.CurrentQuantity).HasColumnType("decimal(10, 2)");
        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

Form code

public partial class Form1 : Form
{
    private Context _context = new();
    private BindingSource _bindingSource = new();
    private SortableBindingList<Products> _bindingList;
    public Form1()
    {
        InitializeComponent();
        
        _bindingList = new SortableBindingList<Products>(
            _context.Products.OrderBy(p => p.ProductName).ToList());

        _bindingSource.DataSource = _bindingList;
        dataGridView1.DataSource = _bindingSource;
        dataGridView1.Columns["Id"]!.ReadOnly = true;
        dataGridView1.ExpandColumns();
        dataGridView1.Spread();
    }

    private void SaveButton_Click(object sender, EventArgs e)
    {
        var affected = _context.SaveChanges();
        MessageBox.Show(affected == 0 ? "Nothing saved" : "Saved");
    }
}

Another option if this is only for simple updates is to look at a TableAdapter which is easy for what is being asked but getting into more advance coding without understanding TableAdapters can become frustrating. Best to use EF Core.