The delete button is too slow what is the problem

Question

1.00/5 (1 vote)

See more:

i use this code to delete records who is selected by checkbox in the datagridview bout it take too long time to do the command

C#

private void delete_Click(object sender, EventArgs e)
        {
            foreach(DataGridViewRow item in advancedDataGridView1.Rows)
            {
                if(bool.Parse(item.Cells[0].Value.ToString()))
                {
                    
                    conn.Open();
                    SqlCommand cmd = new SqlCommand("delete from tabl where id = '" + item.Cells[1].Value.ToString() + "'", conn);
                    cmd.ExecuteNonQuery();
                    conn.Close();
                }
            }
            MessageBox.Show("Successfully Deleted....");
        }

and i use this code to checkbox

C#

private void Chkselectall_CheckedChanged(object sender, EventArgs e)
        {
            for(int n = 0; n< advancedDataGridView1.Rows.Count;n++)
            {
                advancedDataGridView1.Rows[n].Cells[0].Value = chkselectall.Checked;
            }
        }

What I have tried:

what should i do the solve this problem

new update

C#

sql = "delete from tabl where id in (";
           parameterCounter = 0;
           using (SqlConnection cn = new SqlConnection("Data Source=SQL5035.site4now.net;Initial Catalog=DB_A448D1_ilgswork;User Id=**********;Password=**********"))

           {
               using (SqlCommand cmd = new SqlCommand(sql, cn))
               {
                   foreach (DataGridViewRow item in advancedDataGridView1.Rows)
                   {
                       if (bool.Parse(item.Cells[0].Value.ToString()))
                       {
                           parameterCounter++;
                           parameter = new SqlParameter();
                           parameter.ParameterName = "@par" + parameterCounter.ToString();
                           parameter.DbType = System.Data.DbType.Int32;
                           parameter.Value = item.Cells[1].Value;
                           cmd.Parameters.Add(parameter);
                           sql = sql + $"{parameter.ParameterName},";
                           // collecting all ids
                       }
                   }
                   sql = sql.TrimEnd(',');
                   sql = sql + ")";

                   cmd.CommandText = sql;
                   cmd.Connection = cn;
                   cn.Open();
                   cmd.ExecuteNonQuery();
               }

Posted 8-Jan-19 22:36pm

el_tot93

Updated 29-Jan-19 2:45am

v4

Add a Solution

Comments

Maciej Los 9-Jan-19 4:40am

Seems, you don't want to listen our advices about SQL injection...
Based on this piece of code no one is able to tell the reason of slow code execution.

el_tot93 9-Jan-19 4:43am

my problem is im new in coding

Karthik_Mahalingam 9-Jan-19 8:49am

is the ID an integer ?

el_tot93 13-Jan-19 0:03am

yes

Karthik_Mahalingam 28-Jan-19 2:13am

is this solved?

el_tot93 29-Jan-19 5:55am

do you want to help or want the answer

Karthik_Mahalingam 29-Jan-19 5:59am

if the error still persists I shall help you in fixing it.

el_tot93 29-Jan-19 6:32am

lock to my update

el_tot93 29-Jan-19 6:33am

is that the write way

Karthik_Mahalingam 29-Jan-19 8:46am

yes

2 solutions

Solution 2

Do you know why it is slow, Because of the number of database calls, more number of checked item more number of database call.
Things you can do to avoid that
1) Create a stored procedure in your database which accepts list of items to be removed (How To Pass Array Or List To Stored Procedure[^])
2) Call the stored procedure using command (since you want to use the command)
3) Within a single DB call you will be able to delete N numbers of records.
for your comment that you are new to coding, These kind of problem should motivate you to write good code and it involves learning which will help you to enhance your coding skills.

Call to stored procedure

using (SqlConnection conn = new SqlConnection("yourconnectionstring")) {
    conn.Open();

    // 1.  create a command object identifying the stored procedure
    SqlCommand cmd  = new SqlCommand("yourprocedure", conn);

    // 2. set the command object so it knows to execute a stored procedure
    cmd.CommandType = CommandType.StoredProcedure;

    // 3. add parameter to command, which will be passed to the stored procedure
    cmd.Parameters.Add(new SqlParameter("yourparameter", list));
    
    // execute the command
    cmd.ExecuteNonQuery();
    
    
}

Happy learning.

Posted 9-Jan-19 1:32am

sachin.vishwa90

Comments

el_tot93 9-Jan-19 7:41am

bout im new and don't know how to work with stored procedure i didn't lernt it yet

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

RickZeeland · Accepted Answer · 2019-01-08T22:54:00

Solution 1

Instead of sending lots of commands you could try sending one command using the "IN" clause, see example here: SQL IN Operator[^]

Posted 8-Jan-19 22:54pm

RickZeeland

Comments

Maciej Los 9-Jan-19 6:18am

Good idea!
But i'm afraid it won't help. The thing is that OP wants to use commands which may cause sql injection.

Dave Kreskowiak 9-Jan-19 7:26am

His performance problem is that he appears to be using an ID field that's a string, and I'm willing to bet that field isn't indexed.

RickZeeland 9-Jan-19 9:12am

That seems very likely, but for now the IN method seems to work for him ...

[no name] 9-Jan-19 7:29am

As far as I know it is possible to dynamically create the IN clause and set afterwards the Parameter. Something liks SELECT * FROM Tbl WHERE Name IN (@p0, @p1, @p2, @p3,....). With this, SQL injection is out of question