Problem in Export to Excel from SQL Bulk data through .NET Code

Hi,

My clients want to see the entire records from my table.

we have given option to our esteemed client as "Export to Excel" (Button Click Event)

This option is working when less than 2 Lakhs records.

If more than 2 Lakhs records then he is getting the error says "Out of memory file stream exception"

What is error and how do we solve this problem..

Steps to perform now as following.

--------------------

1. Reading the data from Sql table.

2. store the data in data set.

3. using file stream )response.header.etc), save into workbook.

4.download the excel.

The above steps will work only lsess amount of data, if more than 2 lakhs then this will not working.

We are working many applications export to excel option. but, those are very limited data.

thiis is first time we are facing more than 5 lakhs records issues..
Ganesh STech Lead cum developerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

MishaProgrammerCommented:
Usually this error appers, when you read file with big size. May be you can put your code here?
0
Chinmay PatelChief Technical NinjaCommented:
Hi Ganesh,

I understand that it is hard to explain certain things to the customer but I think sometimes it is our duty to do just that.

In your case while you could do splits of say 1L records each and then pop-up 5 (or more) download options I strongly suggest that you look at the alternative.

In a similar case(and trust me if they want to expore anything more than few thousand records) this is a better approach.

1. When user clicks Export to Excel, check the total number of records - Discuss with your customer regarding what should be an ideal number for this.
2. If total number of records, let's say, is more than 1L, notify the user that S/he will be notified when their file is ready.
3. Save the details of this export - using Web Service or files - anything that suits your technical and business requirements.
3. Run a process on server, preferably a windows service OR command line app with the required parameters, generate the file, compress it and send out an email / notification to the user who requested this file.

If the above approach is not possible then the only way out I see is
1. Take the request, split into say 1L records each batch. Generate these files 1 by 1, append them to a single file, make sure your page does not time out - keep your user updated with the progress
2. Once all the files are generated, combine them, store them on your server, give a link to the end user.
3. You might want to periodically run a check for orphan files and delete them.

Regards,
Chinmay.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ganesh STech Lead cum developerAuthor Commented:
Thanks..
0
Chinmay PatelChief Technical NinjaCommented:
Glad I could help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.