C# Fatal error encoutered during data read

Question

I'm selecting about 20,000 records from the database and then I update them one by one.
I looked for this error and I saw that setting the CommandTimeout will help, but not in my case.

        public void Initialize()
    {
        MySqlConnectionStringBuilder SQLConnect = new MySqlConnectionStringBuilder();
        SQLConnect.Server = SQLServer;
        SQLConnect.UserID = SQLUser;
        SQLConnect.Password = SQLPassword;
        SQLConnect.Database = SQLDatabase;
        SQLConnect.Port = SQLPort;
        SQLConnection = new MySqlConnection(SQLConnect.ToString());
    }

        public MySqlDataReader SQL_Query(string query)
    {
        MySqlCommand sql_command;
        sql_command = SQLConnection.CreateCommand();
        sql_command.CommandTimeout = int.MaxValue;
        sql_command.CommandText = query;
        MySqlDataReader query_result = sql_command.ExecuteReader();
        return query_result;
    }

        public void SQL_NonQuery(string query)
    {
        MySqlCommand sql_command;
        sql_command = SQLConnection.CreateCommand();
        sql_command.CommandTimeout = int.MaxValue;
        sql_command.CommandText = query;
        sql_command.ExecuteNonQuery();
    }

And here is my method which makes the select query:

        public void CleanRecords()
    {
        SQLActions.Initialize();
        SQLActions.SQL_Open();
        MySqlDataReader cashData = SQLActions.SQL_Query("SELECT `cash`.`id`, SUM(`cash`.`income_money`) AS `income_money`, `cash_data`.`total` FROM `cash_data` JOIN `cash` ON `cash`.`cash_data_id` = `cash_data`.`id` WHERE `user`='0' AND `cash_data`.`paymentterm_id`='0' OR `cash_data`.`paymentterm_id`='1' GROUP BY `cash_data_id`");
        while(cashData.Read()){
            if(cashData["income_money"].ToString() == cashData["total"].ToString()){
                UpdateRecords(cashData["id"].ToString());
            }
        }
        SQLActions.SQL_Close();
    }

And here is the method which makes the update:

        public void UpdateRecords(string rowID)
    {
        SQLActions.Initialize();
        SQLActions.SQL_Open();
        SQLActions.SQL_NonQuery("UPDATE `cash_data` SET `end_date`='" + GetMeDate() + "', `user`='1' WHERE `id`='" + rowID + "'");
        SQLActions.SQL_Close();
    }

Changing the database structure is not an option for me.
I thought that setting the timeout to the maxvalue of int will solve my problem, but is looks like this wont work in my case. Any ideas? :)

EDIT: The error which I get is "Fatal error encoutered during data read".

UPDATE:

        public void CleanRecords()
    {
        StringBuilder dataForUpdate = new StringBuilder();
        string delimiter = "";

        SQLActions.Initialize();
        SQLActions.SQL_Open();
        MySqlDataReader cashData = SQLActions.SQL_Query("SELECT `cash`.`id`, SUM(`cash`.`income_money`) AS `income_money`, `cash_data`.`total` FROM `cash_data` JOIN `cash` ON `cash`.`cash_data_id` = `cash_data`.`id` WHERE `user`='0' AND `cash_data`.`paymentterm_id`='0' OR `cash_data`.`paymentterm_id`='1' GROUP BY `cash_data_id`");
        while (cashData.Read())
        {
            if (cashData["income_money"].ToString() == cashData["total"].ToString())
            {
                dataForUpdate.Append(delimiter);
                dataForUpdate.Append("'" + cashData["id"].ToString() + "'");
                delimiter = ",";
            }
        }
        SQLActions.SQL_Close();
        UpdateRecords(dataForUpdate.ToString());
    }

    public void UpdateRecords(string rowID)
    {
        SQLActions.Initialize();
        SQLActions.SQL_Open();
        SQLActions.SQL_NonQuery("UPDATE `cash_data` SET `end_date`='" + GetMeDate() + "', `user`='1' WHERE `id` IN (" + rowID + ")");
        SQLActions.SQL_Close();
    }

What is your connectionstring? Do you have set the MultipleActiveResultSets=True? — Steve
– Steve, Commented Jun 17, 2013 at 8:05
What does GetMeDate() do? The part of the query that you show could be probably done in one update (instead of reading the lines and updating them one by one). That would likely fix your timeout issue as well — Paolo Falabella
– Paolo Falabella, Commented Jun 17, 2013 at 8:11

Paolo Falabella · Accepted Answer · 2013-06-17 08:49:57Z

1

You may be able to use

UPDATE cash_data .... WHERE id IN (SELECT ....)

and do everything in one go. Otherwise, you could do it in two steps: first the select collects all the ids, close the connection and then do the update in obne go with all the ids. The code for the second option might look something like this:

    public void CleanRecords()
    {
        StringBuilder builder = new StringBuilder();
        string delimiter = "";

        SQLActions.Initialize();
        SQLActions.SQL_Open();
        MySqlDataReader cashData = SQLActions.SQL_Query("SELECT `cash`.`id`, SUM(`cash`.`income_money`) AS `income_money`, `cash_data`.`total` FROM `cash_data` JOIN `cash` ON `cash`.`cash_data_id` = `cash_data`.`id` WHERE `user`='0' AND `cash_data`.`paymentterm_id`='0' OR `cash_data`.`paymentterm_id`='1' GROUP BY `cash_data_id`");
        while(cashData.Read()){
            if(cashData["income_money"].ToString() == cashData["total"].ToString()){
                builder.Append(delimiter);
                builder.Append("'" + cashData["id"].ToString() + "'");
                delimiter = ",";       
            }
        }
        SQLActions.SQL_Close();

        UpdateRecords(builder.ToString());  


    }

public void UpdateRecords(string rowIDs)
{
    SQLActions.Initialize();
    SQLActions.SQL_Open();
    SQLActions.SQL_NonQuery("UPDATE `cash_data` SET `end_date`='" + GetMeDate() + "', `user`='1' WHERE `id` IN (" + rowIDs + ")";
    SQLActions.SQL_Close();
}

answered Jun 17, 2013 at 8:49

Paolo Falabella

26k3 gold badges79 silver badges91 bronze badges

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

12 Comments

Steve Over a year ago

+1 just for the smart use of the delimiter, however, that's the right direction to go, unless for the string concatenations, but this is another problem

Paolo Falabella Over a year ago

@Steve ehm... the StringBuilder technique is from a snippet of code I have copied from somewhere, so (while I no longer know who to attribute it to) I will not take credit for it. EDIT: found it, it was jon Skeet, who would have thought... :) stackoverflow.com/questions/581448/…

Paul Reed Over a year ago

Excellent! Thank you! Except that the stringbuilder I guess inserts double quote (") in the string at the beginning and in the end, which mysql dont understand correctly and it does not look in the string. How I can get rid of them?

Paolo Falabella Over a year ago

@PaulReed ty changing builder.Append("'" + cashData["id"].ToString() + "'"); to builder.Append(cashData["id"].ToString());

Paul Reed Over a year ago

Tried, but it didnt help :(

|

Rohit Singh · Accepted Answer · 2013-06-17 08:30:25Z

0

There are multiple problem:

First: You have reading information around 20K using data reader and then doing update one by one in reader itself. Reader holds the connection open until you are finished. So this is not the good way to do it. Solution: We can read the information using Data Adapter.

Second: Rather than doing one by one update, we can update in bulk in one go. There are multiple option for bulk operation. In SQL u can do either by sending information in XML format or u can use Table Valued Parameter (TVP) (http://www.codeproject.com/Articles/22205/ADO-NET-and-OPENXML-to-Perform-Bulk-Database-Opera) OR (http://dev.mysql.com/doc/refman/5.5/en/load-xml.html)

answered Jun 17, 2013 at 8:30

Rohit Singh

1062 bronze badges

1 Comment

Rohit Singh Over a year ago

it depends case by case. Please read the below link stackoverflow.com/questions/1676753/…

Collectives™ on Stack Overflow

C# Fatal error encoutered during data read

2 Answers 2

12 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

12 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related