How to process 2M records and saved into datatable

Question

0.00/5 (No votes)

See more:

Hello,

I have 2M records in the table which I am fetching it through store proc. Then I want to convert it into JSON format. When I am fetching it and storing it into datatable I am getting below error. How I can fetch 2M records and saved it into JSON format.

Error :- The memory could not be written.

What I have tried:

C#

DataTable dt = new DataTable();

           using (var connection = new SqlConnection(ConfigurationManager.ConnectionStrings["CRM"].ConnectionString))
           {


               using (var cmd = new SqlCommand("SPGetList", connection))
               {
                   cmd.CommandType = System.Data.CommandType.StoredProcedure;
                   SqlDataAdapter da = new SqlDataAdapter();
                   da.SelectCommand = cmd;

                   da.Fill(dt);// This line causing an error and throwing the memory exception error.

               }
           }
           return DataTableToJSONWithStringBuilder(dt);

Posted 11-Jul-17 18:42pm

Telstra

Updated 11-Jul-17 21:10pm

v3

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2017-07-11T19:03:00

How should we know?
We have no idea what your DataTableToJSONWithStringBuilder method is doing - other than the broad outline the method name provides - nor any idea exactly which line is causing the problem.

So, its going to be up to you.
Put a breakpoint on the first line in that code, and run your app through the debugger. Then look at your code, and at your data and work out what should happen manually. Then single step each line checking that what you expected to happen is exactly what did. When it isn't, that's when you have a problem, and you can back-track (or run it again and look more closely) to find out why.

Sorry, but we can't do that for you - time for you to learn a new (and very, very useful) skill: debugging!

Quote:
Hi,
I am also thinking the same. But I am not a sql expert. How I can fetch it in batches like first 50k and then so on..

Depends on your SQL server version.
The old way is to use ORDER BY with a ROW_NUMBER:

SQL

SELECT MyColumns 
FROM (SELECT MyClolumns, ROW_NUMBER() OVER (ORDER BY UniqueKeyColumn) AS RowNum FROM MyTable) s
WHERE s.RowNum BETWEEN @StartRow AND @EndRow

This works from SQL 2005 onwards.
SQL 2012 introduced OFFSET-FETCH:

SQL

SELECT MyColumns FROM MyTable ORDER BY UniqueKeyColumn OFFSET @STARTROW ROWS FETCH NEXT @ROWSCOUNT ROWS ONLY

Atlapure Ambrish · Answer 2 · 2017-07-11T21:08:00

Can you try to use 'data reader' instead of adapter and see if the fetching of data is successful?

if the 'data reader' works for fetching data then you can convert/store the resultset returned to datatable or a complex type as required for further processing (i.e. converting to JSON).

You can also use TimeSpan to check the difference in time required to fetch records using adapter and reader. E.g.: do this excercise for 50K, 150K, and 500K records. I am sure data reader is going to be faster.

How to process 2M records and saved into datatable

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0