How can I make this function delete multi rows

Question

I have a C# function and SQL server stored procedure its delete row in my gridview. How can I make it delete multiple selected rows ?

C# function:

public void DeleteReciver(int Reciver_number)
{
    DAL.DataAccessLayer DAL = new DAL.DataAccessLayer();
    DAL.Open();
    SqlParameter[] param = new SqlParameter[1];
    param[0] = new SqlParameter("@Reciver_number", SqlDbType.Int);
    param[0].Value = Reciver_number;
    DAL.excutecommand("DeleteReciver", param);
    DAL.close();
}

SQLSERVER stored procedure:

create proc DeleteReciver
@Reciver_number int
as
delete from Recivers where Reciver_number=@Reciver_number

using

if (MessageBox.Show("Are you sure you want delete this reciver? ", "delete row", MessageBoxButtons.YesNo, MessageBoxIcon.Question) == DialogResult.Yes)
{
    REC.DeleteReciver(Convert.ToInt32(this.dataGridView1.CurrentRow.Cells[2].Value));
    MessageBox.Show("Reciver deleted successfully", "delete row", MessageBoxButtons.OK, MessageBoxIcon.Information);
    this.dataGridView1.DataSource = REC.GET_ALL_RECIVERS();
}
else
{
    MessageBox.Show("cancelled", "delete row", MessageBoxButtons.OK, MessageBoxIcon.Exclamation);

}

You can change the signature of `DeleteReciver` to accept `List` as parameter, then in the method, after opening the connection in a `for/foreach` loop, call the stored procedure passing the value to delete. — Reza Aghaei, Jul 06 '16 at 19:18
Also as another option, you can use [Table-Valued Parametes](https://msdn.microsoft.com/en-us/library/bb510489.aspx). For example take a look at this post: [C# SQL Server - Passing a list to a stored procedure](http://stackoverflow.com/questions/7097079/c-sharp-sql-server-passing-a-list-to-a-stored-procedure) — Reza Aghaei, Jul 06 '16 at 19:24
If the `DataSource` of your `DataGridView` is a `DataTable` you can use a `DataAdapter` to save changes to database. This way you don't need to use that stored procedure. It's enough to have a `SELECT * FROM Recivers` command. Then all other required command can be created automatically and all changes including deleted records can be apply to database simply. For example take a look at this post: [CRUD Operations using DataGridView, DataTable and TableAdapter](http://stackoverflow.com/a/36274706/3110834) — Reza Aghaei, Jul 06 '16 at 20:42

score 1 · Answer 1 · answered Jul 06 '16 at 19:24

This will work for you

public void DeleteReciver(int[] Reciver_numbers)
    {
        DAL.DataAccessLayer DAL = new DAL.DataAccessLayer();
        DAL.Open();
        SqlParameter[] param = new SqlParameter[1];
        for (int i = 0; i < Reciver_numbers.Length; i++)
        {
            param[0] = new SqlParameter("@Reciver_number", SqlDbType.Int);
            param[0].Value = Reciver_number[i];
            DAL.excutecommand("DeleteReciver", param);
        }
        DAL.close();

    }

Pass the Id in array and use for look to execute delete command. Hope this helps.

score 0 · Answer 2 · edited Jul 06 '16 at 19:51

0

Instead of passing in one Reciver_number, why not run a loop to gather all the receiver numbers in a comma delimited string and delete them in the stored procedure.

create proc DeleteRecivers
@Reciver_numbers varchar(50)
as
delete from Recivers where Reciver_number in (@Reciver_numbers)

edited Jul 06 '16 at 19:51

Sravan Kumar

1,447
3
19
41

answered Jul 06 '16 at 19:17

EmC

9
2

1

Because you can't parameterize the IN clause in that way – Steve Jul 06 '16 at 19:18

Y. M. · Answer 3 · 2016-07-06T19:30:04.503

0

You can in SQL by sending varchar parm and make like this

create proc DeleteReciver
@Reciver_number int=null
@Reciver_numbers varchar(500)=null
as
IF isnull(@Reciver_number,0) <> 0 
   delete from Recivers where Reciver_number=@Reciver_number
ELSE 
   IF isnull(@Reciver_numbers,'') <> '' 
       exec ('delete from Recivers where Reciver_number in (' + @Reciver_numbers +')')

And on C# you send string of all the numbers you want to delete in this format 12,435,234,6565,34

edited Jul 06 '16 at 19:30

answered Jul 06 '16 at 19:24

Y. M.

107
9

1

Again, the IN clause cannot be parameterized in that way. Here the reference question http://stackoverflow.com/questions/337704/parameterize-an-sql-in-clause – Steve Jul 06 '16 at 19:26
Thank´s Steve, I have change it for exec ('delete from Recivers where Reciver_number in (' + @Reciver_numbers +')') – Y. M. Jul 07 '16 at 14:23

How can I make this function delete multi rows

3 Answers3