Is it bad idea to run SQL queries like insert update in a for loop?

Question

1.00/5 (1 vote)

See more:

C#

if (value > 0)
{
    for(int i=0;i<mydatagrid.Rows.Count;i++)
    {
            SqlCommand cc = new SqlCommand("insert into store values(@sbno,@vno,@box,@qty,@pcs,@remark,@avail)", cn);
        cc.Parameters.AddWithValue("@sbno", sbno);
        cc.Parameters.AddWithValue("@vno", vno);
        cc.Parameters.AddWithValue("@box",Int32.Parse(dataGridView1.Rows[i].Cells[0].Value.ToString()));
        cc.Parameters.AddWithValue("@qty",Int32.Parse(dataGridView1.Rows[i].Cells[1].Value.ToString()));
        cc.Parameters.AddWithValue("@pcs",);
        cc.Parameters.AddWithValue("@remark",);
        cc.Parameters.AddWithValue("@avail", );
        cc.ExecuteNonQuery();
    }
   MessageBox.Show(nstdbox + " Boxes Stored To Store");
}

What I have tried:

it is bad or ok? I don't know the practice about this
Am i doing it wrong way?

Posted 10-Sep-17 17:34pm

H M Mori

Updated 10-Sep-17 18:28pm

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 10-Sep-17 23:59pm

That way is bad. The right way is good.

2 solutions

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)

Bryian Tan · Answer 1 · 2017-09-10T18:28:00

Solution 2

How about using a Stored Procedure and pass a DataTable (Grid rows) as parameter and do the Insert/Update in the stored procedure? Here is an example that could be useful.

Sending a DataTable to a Stored Procedure[^]

Posted 10-Sep-17 18:28pm

Bryian Tan

Comments

[no name] 11-Sep-17 2:10am

sorry but not it is not asp.net question.

PIEBALDconsult · Answer 2 · 2017-09-10T18:08:00

Solution 1

The basic technique for this is:

Set up the command
Add the parameters -- do not use AddWithValue
Iterate the rows
  Set the parameter values
  Execute the command

And do not do anything like: Int32.Parse(dataGridView1.Rows[i].Cells[0].Value.ToString())

Posted 10-Sep-17 18:08pm

PIEBALDconsult

Updated 11-Sep-17 12:59pm

v2

Comments

[no name] 11-Sep-17 2:12am

And do not do anything like: Int32.Parse(dataGridView1.Rows[i].Cells[0].Value.ToString())

i have to do this because i want integer value is there any other way to get it?

PIEBALDconsult 11-Sep-17 8:42am

Of course; just let the system take care of it for you.

David_Wimbley 12-Sep-17 12:09pm

I think OP's problem is that you're vaguely telling him the route to go and also telling him what not to do but not offering up what he should do. "just let the system take care of it for you" isn't very helpful.