Using C# to loop through and insert data

Question

I have a program that has to loop through a Scenarios database, and for each while loop iteration, update a second Results database. Here's the segment of code.

    public void TestScenarios(SqlConnection connection)
    {
        using (SqlCommand cmd = new SqlCommand("SELECT * FROM Scenarios", connection))
        {
            SqlDataReader reader = cmd.ExecuteReader();

            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    string Id = reader["ScenarioID"].ToString();
                    string Data = reader["ScenarioData"].ToString();
                    string Url = reader["ScenarioURL"].ToString();
                    webBrowser1.Navigate(Url);
                    InsertResults(connection, Id);
                }
            }

            reader.Close();
        }
    }

    public void InsertResults(SqlConnection conn, string Id)
    {
        using (SqlCommand cmd = new SqlCommand("INSERT INTO Results VALUES(" +
                "@ResultID, @HasSucceeded, @ScenarioID, @Screenshot)", conn))
        {
            cmd.Parameters.AddWithValue("@ResultID", 0);
            cmd.Parameters.AddWithValue("@HasSucceeded", 0);
            cmd.Parameters.AddWithValue("@ScenarioID", Id);
            cmd.Parameters.AddWithValue("@Screenshot", "screenshot.jpeg");
        }
    }

It's not working, and I'm sure I did tons of things wrong, but I'm having trouble finding direction.

In your InsertResults function, you probably need to do something like cmd.ExecuteNonQuery();. — Tim S
– Tim S, Commented Jul 18, 2013 at 21:02
As a side note, are you sure you want to call webBrowser1.Navigate in a while loop? I imagine the speed of the while loop will be significantly faster than the Navigate method. — keyboardP
– keyboardP, Commented Jul 18, 2013 at 21:03

Bill Gregg · Accepted Answer · 2013-07-18 21:01:50Z

5

Your second command is never executing. Add:

cmd.ExecuteNonQuery();

answered Jul 18, 2013 at 21:01

Bill Gregg

7,1472 gold badges24 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

sir_thursday Over a year ago

I get "There is already an open DataReader associated with this Command which must be closed first."

Bill Gregg Over a year ago

Yes, that's because you are trying to re use the connection while you are iterating over the rows. If you want to perform the insert while keeping your other connection open, you'll have to create a new connection.

Mechanical Object · Accepted Answer · 2013-07-18 21:15:33Z

2

May I propose you to use SqlBulkCopy? Here is some information on how to copy two database tables , I hope you'll find it clear.

http://www.codeproject.com/Articles/18418/Transferring-Data-Using-SqlBulkCopy

http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy%28v=vs.90%29.aspx

edited Jul 18, 2013 at 21:15

answered Jul 18, 2013 at 21:09

Mechanical Object

6666 silver badges12 bronze badges

Collectives™ on Stack Overflow

Using C# to loop through and insert data

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related