C# – MultiThreading error: There is already an open DataReader associated with this Connection which must be closed first

cmultithreadingMySQL

I have a Parallel.Foreach loop

var options = new ParallelOptions();
options.MaxDegreeOfParallelism = 1;
Parallel.ForEach(urlTable.AsEnumerable(),drow =>
{
    using (var WCC = new MasterCrawlerClass())
    {
        WCC.MasterCrawlBegin(drow);
    }
 }

This loop calls the class and loops through all my DataRows, however each of those datarows either does a DataTable fill, or does an update command to a MySQL DB. The code I have for both of those is below.

private static DataTable DTTable(string mysqlQuery, string queryName)
{
    DataTable DTTableTable = new DataTable();
    try
    {
        MySqlDataAdapter DataDTTables = new MySqlDataAdapter(mysqlQuery, MySQLProcessing.MySQLStatic.Connection);
        DataTable DataDTTablesDT = new DataTable();
        DataDTTables.SelectCommand.CommandTimeout = 240000;
        DataDTTables.Fill(DataDTTablesDT);
        DTTableTable = DataDTTablesDT;

    }
    catch (Exception ex)
    {

        GenericLogging("Failed MySQLquery: " + ex.Message.ToString(), "MySQLProcessor", "DTTable", "", "MysqlError", "", queryName, mysqlQuery);

    }
    return DTTableTable;
}
private static void MySQLInsertUpdate(string MySQLCommand, string mysqlcommand_name)
{
    try
    {
        MySqlCommand MySQLCommandFunc = new MySqlCommand(MySQLCommand, MySQLProcessing.MySQLStatic.Connection);
        MySQLCommandFunc.CommandTimeout = 240000;
        MySQLCommandFunc.ExecuteNonQuery();
    }
    catch (Exception ex)
    {
        GenericLogging("Failed MySQLquery: " + ex.Message.ToString(), "MySQLProcessor", "DTTable", "", "MysqlError", "", mysqlcommand_name, MySQLCommand);
    }
}

The thing is WCC contains 10 or so voids, each of these voids accesses the MySQL functions at least once. So if locking is the answer, is it possible to create 1 locking function for all voids? If so how? If there is another way, please let me know

Thanks!

Okay as suggested i consolidated the questions

The code has now been updated to reflect locking, please see below.

static readonly object _object = new object();

public static DataTable DTTable(string mysqlQuery, string queryName)
        {
            lock (_object)
            {
                DataTable DTTableTable = new DataTable();
                try
                {
                    using (MySqlDataAdapter DataDTTables = new MySqlDataAdapter(mysqlQuery, MySQLProcessing.MySQLStatic.Connection))
                    {
                        using (DataTable DataDTTablesDT = new DataTable())
                        {
                            DataDTTables.SelectCommand.CommandTimeout = 240000;
                            DataDTTables.Fill(DataDTTablesDT);
                            DTTableTable = DataDTTablesDT;
                            DataDTTables.Dispose();
                        }
                    }

                }
                catch (Exception ex)
                {

                    GenericLogging("Failed MySQLquery: " + ex.Message.ToString(), "MySQLProcessor", "DTTable", "", "MysqlError", "", queryName, mysqlQuery);

                }
                return DTTableTable;
            }
        }

This is the only code that calls the DataReader, how can there be two open data readers if there is a lock?

Best Answer

The problem is that ADO.NET data providers generally do not allow for more than one open data reader at a time per connection. SQL Server has the concept of multiple active result sets (MARS), but as far as I know MySQL does not yet support it.

You will probably need to specify a different connection other than MySQLProcessing.MySQLStatic.Connection. There is nothing stopping you from using more than one connection. The problem here is that connections are expensive resources so you are supposed to use them sparingly.