Click here to Skip to main content
15,569,840 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi, I am trying to convert visual box fro DBF file into CSV file using visual studio 2008 c# windows application. I am succeed in visual foxpro connection and read the file.I have used oledbdataadapter to fill dataset, I am failed here with deadlock issue because i working around 800000+ records. So i go with oledbdatareader, and read each row and wrote into the csv file. It's working fine but after writing 400000+ records am again faced deadlock issue. I need to write whole 800000+ records into a single CSV file. I have attached my code here. Please help to write 800000+ records into a single CSV .

C#
OleDbCommand oleDbCommand = new OleDbCommand(query, oleDbConnection);
oleDbCommand.CommandTimeout = 60000;
using (OleDbDataReader oleDbDataReader = oleDbCommand.ExecuteReader())
{
    using (streamWriter1)
    {
        int fieldCount = oleDbDataReader.FieldCount;
        string columns = string.Empty;
        StringBuilder fullFile = new StringBuilder();
        for (int i = 0; i < fieldCount; i++)
        {
            columns += oleDbDataReader.GetName(i);
            if (i != fieldCount - 1)
            {
                columns += ",";
            }
        }
        fullFile.Append(columns);
        fullFile.Append("\r\n");
        streamWriter1.Write(fullFile.ToString());
        int j = 0;
        while (oleDbDataReader.Read())
        {
            j++;
            string rows = string.Empty;
            for (int i = 0; i < fieldCount; i++)
            {
                rows += oleDbDataReader.GetValue(i).ToString().Trim();
                rows += i != fieldCount - 1 ? "," : "";
            }
            fullFile = new StringBuilder();
            fullFile.Append(rows);
            fullFile.Append("\r\n");
            streamWriter1.Write(fullFile.ToString());
        }
    }
    oleDbDataReader.Close();
    oleDbCommand.Dispose();
    oleDbConnection.Close();  
}


What I have tried:

I have tried to write every 200000 records into different file. But this logic also failed with deadlock issues after 400000 record wrote.
Posted
Updated 3-Nov-16 0:15am
v3
Comments
Richard MacCutchan 3-Nov-16 5:06am    
What do you mean by "deadlock"? What actually happens?
sankarisiva 3-Nov-16 5:10am    
I am getting the error like " The CLR has been unable to transition from COM context 0x50aea88 to COM context 0x50aebf8 for 60 seconds. The thread that owns the destination context/apartment is most likely either doing a non pumping wait or processing a very long running operation without pumping Windows messages. This situation generally has a negative performance impact and may even lead to the application becoming non responsive or memory usage accumulating continually over time. To avoid this problem, all single threaded apartment (STA) threads should use pumping wait primitives (such as CoWaitForMultipleHandles) and routinely pump messages during long running operations. "
Richard MacCutchan 3-Nov-16 5:17am    
How many records are you trying to read in your OleDbDataReader at one time?
sankarisiva 3-Nov-16 5:21am    
Totally 800000+ records. By looping the oleDbDataReader i am reading a one record at a time and writing that record into CSV file.
Richard MacCutchan 3-Nov-16 5:23am    
That is far too many, you need to break it down into manageable chunks of a few hundred at a time.

1 solution

I don't know if this is the reason but you should not create a new StringBuilder object inside the while loop using the name of an already existing object (I'm not sure if re-using an object name in this way creates a memory leak.). Just use the existing one:
C#
while (oleDbDataReader.Read())
{
    // ...
    // Avoid this:
    //fullFile = new StringBuilder();
    // Use this instead:
    fullFile.Clear();
    fullFile.Append(rows);
    fullFile.Append("\r\n");
    streamWriter1.Write(fullFile.ToString());
}

If this is not the error source it should at least improve the performance.
For the same reason you may also change the generation of the rows string.

[EDIT]
From the comments it seems that executing this task within a worker thread may solve the problem. When performing such long running tasks within a GUI thread, that will not be able to respond to messages while the task is executed.

Some links about worker threads:
BackgroundWorker Class (System.ComponentModel)[^]
BackgroundWorker Class Sample for Beginners[^]
[/EDIT]
 
Share this answer
 
v2
Comments
sankarisiva 3-Nov-16 5:24am    
Thanks for your reply. But this solution not improving the process..... i am getting the same result what i got before this change
Jochen Arndt 3-Nov-16 5:33am    
I have read your above comments meanwhile.

Are you doing this in an own thread?
If not, you should create a worker thread.
sankarisiva 3-Nov-16 5:35am    
ohh. With worker thread we can solve this problem? Can you give some example or link
Jochen Arndt 3-Nov-16 5:42am    
If this is running within your GUI thread it will block the GUI until returning.
From your error message:
"a very long running operation without pumping Windows messages"
Using a worker thread is the usual option (better than pumping message as mentioned in the error message).

A tip:
Follow my suggestions for optimisation. If you then got more records written than before, using a worker thread should be the solution.
sankarisiva 3-Nov-16 5:48am    
Thank you. I am not worked with worker thread in past. So now i am learning the key point to use the worker thread and will post my comment after getting the best result. Thank you again

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900