how do you delete data from the database that are like rows in datagridview column

Question

Hello

my project has a datagridview with 3 columns that are completed manually by the user and a database table with 100 records

i need to delete from database table the records that are like the first column(Serial) of the datagridview

Serial | Lot | Date

89985 | T2_4 | 2023-02-03

89993 | T2_4 | 2023-02-03

90009 | T2_4 | 2023-02-03

90017 | T2_4 | 2023-02-03

90025 | T2_4 | 2023-02-03

with the code below I managed to delete only one record

for (int i = 0; i < dvgSeriiNoi.Rows.Count; i++)
            {
                SqlCommand sqlCmd = new SqlCommand("DELETE Articol WHERE Serial = @Serial", con);
                sqlCmd.Parameters.AddWithValue("@Serial", dvgSeriiNoi.Rows[i].Cells["Column1"].Value.ToString());
                sqlCmd.ExecuteNonQuery();
            }

Answer 1

The following example read from a database table to populate a BindingSource which becomes the DataSource of a DataGridView. Now this data does not have to come from a database, I did it this way to keep things simple.

The delete operation is chunked into 20 at a time using WHERE IN. Let's say that in one chunk no all serial values exists the operation is performed on those that do exists.

Full source.

Coded with .NET Core 7

Container for storing data

public class Container
{
    public string Serial { get; set; }
    public DateTime SomeDate { get; set; }
}

Data class

public class DataOperations
{
    private const string ConnectionString = 
        "Data Source=.\\SQLEXPRESS;Initial Catalog=ForumExample;Integrated Security=True;Encrypt=False";

    public static List<Container> Read()
    {
        List<Container> list = new List<Container>();
        using var cn = new SqlConnection(ConnectionString);
        using var cmd = new SqlCommand()
        {
            Connection = cn,
            CommandText = "SELECT Serial,SomeDate FROM dbo.Demo1"
        };

        cn.Open();

        var reader = cmd.ExecuteReader();

        while (reader.Read())
        {
            list.Add(new Container()
            {
                Serial = reader.GetString(0),
                SomeDate = reader.GetDateTime(1)
            });
        }

        return list;
    }

    public static bool DeleteRecords(List<string> statements)
    {
        using var cn = new SqlConnection(ConnectionString);
        using var cmd = new SqlCommand() { Connection = cn };

        cn.Open();

        try
        {
            foreach (var statement in statements)
            {
                cmd.CommandText = statement;
                cmd.ExecuteNonQuery();
            }

            return true;
        }
        catch (Exception)
        {

            return false;
        }

    }
}

Form code

public partial class Form1 : Form
{
    private readonly BindingSource _bindingSource = new ();
    public Form1()
    {
        InitializeComponent();

        _bindingSource.DataSource = DataOperations.Read();
        dataGridView1.DataSource = _bindingSource;
    }

    private void DeleteButton_Click(object sender, EventArgs e)
    {
        var list = ((List<Container>)_bindingSource.DataSource).Select(x => $"'{x.Serial}'").Chunk(20).ToList();
        var deleteStatements = new List<string>();
        foreach (var serialLArray in list)
        {
            deleteStatements.Add($"DELETE FROM [Demo1] WHERE [Serial] IN ({string.Join(",", serialLArray)})");
        }

        if (DataOperations.DeleteRecords(deleteStatements))
        {
            _bindingSource.DataSource = null;
            DeleteButton.Enabled = false;
        }
        else
        {
            MessageBox.Show("Failed");
        }
    }
}