• Status: Solved
  • Priority: Low
  • Security: Public
  • Views: 28
  • Last Modified:

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..
0
Ganesh S
Asked:
Ganesh S
  • 2
1 Solution
 
MishaProgrammerCommented:
Usually this error appers, when you read file with big size. May be you can put your code here?
0
 
Chinmay PatelEnterprise ArchitectCommented:
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
 
Ganesh STech Lead cum developerAuthor Commented:
Thanks..
0
 
Chinmay PatelEnterprise ArchitectCommented:
Glad I could help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now