C# Can I make (SqlDataReader) in pause status?

Question

I get this error:

There is already an open DataReader associated with this Command which must be closed first

This is my code :

String sql_get_completed_tasks = "select * from tasks where Ending_date is not null order by Ending_date asc";
SqlCommand command_get_completed_tasks_ = new SqlCommand(sql_get_completed_tasks, cn);

SqlDataReader reader = command_get_completed_tasks_.ExecuteReader();

while (reader.Read())
{  
    String sql_insert_completed_tasks_toTheNewTable = "insert into history_of_tasks_achievements values ("+
                reader[1] +" , N'"+ reader[2]+"' , @image , N'"+ reader[4] +"' , N'"+reader[5]+"' , N'"+ reader[6]+"' , "+
                reader[7] +" , N'"+reader[8]+"' , N'"+reader[9] +"' , "+reader[10]+" , @start_date , @end_date , "+reader[13]+
            ")";

    SqlCommand com_insert_completed_tasks_toTheNewTable = new SqlCommand(sql_insert_completed_tasks_toTheNewTable, cn);

    com_insert_completed_tasks_toTheNewTable.Parameters.Add(new SqlParameter("@image", reader[3]));
    com_insert_completed_tasks_toTheNewTable.Parameters.Add(new SqlParameter("@start_date", reader[11]));
    com_insert_completed_tasks_toTheNewTable.Parameters.Add(new SqlParameter("@end_date", reader[12]));

    com_insert_completed_tasks_toTheNewTable.ExecuteNonQuery();
}    // end of while loop

reader.Close();

MessageBox.Show("Done");

To skip this error, can I create pause or close for SqlDataReader, then re-open from the break values? What is the best solution?

You do know you can have multiple queries on the same connection active with MARS? https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/enabling-multiple-active-result-sets - Actually it opens multiple connections, so be aware of that. You can also read all of the data, and execute the insert statements after that. — Caramiriel, Oct 29 '18 at 20:27
First I must active MARS instruction , then do my inserting instruction , then inactive MARS ? — Na'il, Oct 29 '18 at 20:40
I'm going to blow your mind here. [Verbatim strings.](https://stackoverflow.com/questions/1100260/multiline-string-literal-in-c-sharp) — Shadetheartist, Oct 29 '18 at 20:42
@Na'ilLaith please please please don't concatenate values to create SQL. This is a HUGE security hole, plus it causes bugs for multiple values. You should be using *parameters*. — Marc Gravell, Oct 29 '18 at 21:19
@Shadetheartist I made a comment about verbatim strings - but I was being an idiot - I misread that you'd suggested the `$"..."` strings - my bad, sorry; `@"..."` strings are fine! — Marc Gravell, Oct 29 '18 at 21:21

score 0 · Answer 1 · answered Oct 29 '18 at 20:43

0

In this case you can use the using statement.

// set up connection, command

using (var reader = command_get_completed_tasks_.ExecuteReader())
{
    // do the reader stuff
    reader.Close();
}

// Done

answered Oct 29 '18 at 20:43

s0me1

109
7

The main problem is I have two execution operations , one inside other . – Na'il Oct 29 '18 at 21:01
oh well missed that, then i would go with Igor's solutions, combining the two statements. – s0me1 Oct 29 '18 at 21:07

Igor · Accepted Answer · 2018-10-30T01:17:02.883

0

That should really just be 1 statement, there is no need to iterate over the results from the select just to push them back to the same database using an insert.

const string sqlInsert = 
@"INSERT INTO [history_of_tasks_achievements] (col1, col2, col3, ...) 
SELECT col1, col2, col3, ... FROM [tasks] WHERE [Ending_date] IS NOT NULL ORDER BY [Ending_date] ASC";

using(var con = new SqlConnection("connection string here"))
using(var command = new SqlCommand(sqlInsert, con))
{
  con.Open();
  var inserted = command.ExecuteNonQuery();
}

Be sure to align the columns between the SELECT and the INSERT so they match.

edited Oct 30 '18 at 01:17

answered Oct 29 '18 at 20:56

Igor

60,821
10
100
175

I used your code correctly - But without "using" statements , instead of that I used "sqlconnection" and I already opened the database - But there are no data in the new table "history_of_tasks_achievements" ! – Na'il Oct 29 '18 at 21:41
And I made a correct align -The same columns sequence - between insert and select . – Na'il Oct 29 '18 at 21:45
1

@Na'ilLaith - If you don't see anything in the table then the select statement is too restrictive. To see if that is indeed the issue or not you should check the results from the `ExecuteNonQuery` method which returns the number of records affected. See the updated code, you can log or display the output somewhere. If you do have a positive number in the variable then you are looking/updating the wrong database/server or overwriting the database at runtime. – Igor Oct 30 '18 at 13:17
@lgor ... It's done successfully , and your solution is very correct . But I made a silly mistake , by checking the old table in different name -That I renamed it but did not delete it- Thank you so much lgor. – Na'il Oct 30 '18 at 21:29

C# Can I make (SqlDataReader) in pause status?

2 Answers2