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, Jun 17 '13 at 08: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, Jun 17 '13 at 08:11
I updated my question. The connectionstring is in my Initialize() method. GetMeDate() simply returns the date in specific format. And, yes you are right. I wanted to do the select and update queries in one method, but as I know, C# cannot run two mysql queries in one time, or I'm wrong or you have something different in mind? :) — Paul Reed, Jun 17 '13 at 08:16
Try to add to your code that build the connection string `SQLConnect.MultipleActiveResultSets=True` See here http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlconnectionstringbuilder.multipleactiveresultsets.aspx .Let me know if this resolves the problem so I can change this comment in an answer — Steve, Jun 17 '13 at 08:23
Visual studio debugger says missing refference when I add the MARS. When I add it into the stringbuilder method it says "Keyword not supported". Sorry for the noob behavior, but I'm still walking into C# programming. — Paul Reed, Jun 17 '13 at 08:37
MARS used to be not available for MySql, but I have only found old (2009!) docs about this, so things may well have changed now: http://forums.mysql.com/read.php?38,65931,260092 — Paolo Falabella, Jun 17 '13 at 09:12

score 1 · Accepted Answer · answered Jun 17 '13 at 08:49

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 '13 at 08:49

Paolo Falabella

24,914
3
72
86

+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 – Steve Jun 17 '13 at 08:53
@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... :) http://stackoverflow.com/questions/581448/join-a-string-using-delimiters – Paolo Falabella Jun 17 '13 at 09:05
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? – Paul Reed Jun 17 '13 at 09:51
@PaulReed ty changing `builder.Append("'" + cashData["id"].ToString() + "'");` to `builder.Append(cashData["id"].ToString())`; – Paolo Falabella Jun 17 '13 at 09:54
Tried, but it didnt help :( – Paul Reed Jun 17 '13 at 11:20
Mmm... what error do you get? The StringBuilder should not add any double quotes. See [this gist](https://gist.github.com/pfalabella/5796348) where I've stripped all the database code, to just demonstrate how the strings with the queries are built. – Paolo Falabella Jun 17 '13 at 11:55
I don't get any error. I see that the mysql doesnt look in the query proper. I tried to simulate the query and run it directly into the SQL. And if it is `UPDATE cash_data SET end_date='2013-06-07 00:00:00', user='1' WHERE `id` IN ("'1', '2', '3', '4', '5'")` it doesnt affect any rows. but if its without the double quotes in the start and in the end `('1', '2', '3', '4', '5')` works fine, but seems like stringbuilder is automaticly adding these double quotes. – Paul Reed Jun 17 '13 at 12:02
@PaulReed his is strange... Could you post the updated version of your code (the one that is giving you the issue?) – Paolo Falabella Jun 17 '13 at 12:06
Any other suggestions? :( I'm stuck here for several hours and now I dont have a clue where is the mistake – Paul Reed Jun 17 '13 at 13:47
@PaulReed you seem to be missing a closed ) at the end of SQLActions.SQL_NonQuery. It should be `SQLActions.SQL_NonQuery("UPDATE cash_data SET end_date='" + GetMeDate() + "', user='1' WHERE id IN (" + rowIDs + ")");`. I've also removed all the backticks, since they should not be necessary (and they mess with the formatting of code on SO :) ). – Paolo Falabella Jun 17 '13 at 13:47
Hmm, my mistake while updating the post. In my code the closing ) is in his place. I'm getting totally confused right now. I tried to watch the string variables with line breaking and there was no sign of anything wrong. The iteration goes, the stringbuilder builds the string, but when it goes to the query nothing happens actually. No exception, no any sign of error. :( – Paul Reed Jun 17 '13 at 13:52
Okay, I started logging all queries and I can confirm that the problem is not in the code. Its something with the query. – Paul Reed Jun 17 '13 at 14:10

score 0 · Answer 2 · answered Jun 17 '13 at 08:30

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)

it depends case by case. Please read the below link http://stackoverflow.com/questions/1676753/sqldataadapter-vs-sqldatareader — Rohit Singh, Jun 20 '13 at 04:01

C# Fatal error encoutered during data read

2 Answers2