How do I pass 60 thousand + record dataset as XML to stored procedure using stringwriter and writetoxml ?

Question

1.23/5 (4 votes)

See more:

I have created a string which is a xml format string generated using StringWriter and WriteToXml using the dataset .. now It works for less records in dataset but when records exceed 60k + which will be the real time scenario it throws a Memory Out of Exception while storing xml generated strj g in a string variable. Please guide .

Code:

C#

StringWriter sw = new StringWriter();
dataSet.WriteXml(sw);
string result = sw.ToString();

What I have tried:

I stored it in string variable as mentioned above but it throws memory exception. Please provide a code if possible. I am uploading Excel file converting it to Dataset and then converting it to xml string format to pass it to the stored procedure

Posted 30-Aug-16 3:43am

Abrar Kazi

Updated 30-Aug-16 19:05pm

v2

Add a Solution

Comments

Maciej Los 30-Aug-16 10:06am

Sounds like bad design... Share your code. We aren't able to help you without seeing it.

Abrar Kazi 30-Aug-16 12:29pm

StringWriter sw = new StringWriter();
dataSet.WriteXml(sw);
string result = sw.ToString();

Heres the code I'm using

Dave Kreskowiak 30-Aug-16 10:46am

I would suspect that your XML generation is creating a XML string that is exceedingly large. You can't generate this in memory. You'd have to write this XML directly to a file and then return the file to the caller.

But, since we know nothing about your code nor the structure and representative example data for one of the typical objects you're sending to XML it's impossible to say anything useful for fixing the problem.

Abrar Kazi 30-Aug-16 12:29pm

StringWriter sw = new StringWriter();
dataSet.WriteXml(sw);
string result = sw.ToString();

Heres the code I'm using

Dave Kreskowiak 30-Aug-16 14:58pm

OK, so what? Without the data, it's pretty much impossible to tourbleshoot this.

The code you provided is very basic code to write a DataSet to an XML string in memory. What the error is telling you is that you're generating such a huge XML file that it exceeds the memory limits of a string. No object in .NET can be larger than 2GB is size.

So, you really need to NOT send 60,000+ records back in one XML string. You need to go back and reevaluate the problem and how you're solving it. Instead of sedning back one monolithic block of data, send back the records in sets of a hundred. You solve a problem like this by "paging" the data.

Dave Kreskowiak 30-Aug-16 10:47am

And, no, it's NOT urgent. It may be urgent to you, but it's not to a bunch of people who volunteer whatever free time they have to answer questions on this site.

2 solutions

Solution 2

My advice: forget about StringWriter[^].

You've got 2 options. Depending on method used to manipulate data, you can use :

XmlWriter Class (System.Xml)[^]
XmlSerialization[^] or even better DataContractSerialization[^].

But, you've mentioned that:

Abrar Kazi[^] wrote:
I am uploading Excel file converting it to Dataset and then converting it to xml string format to pass it to the stored procedure

In that case, your first step is OK (Excel to dataset). Next steps, should be:

connect to the database (server)
use SqlBulkCopy[^] class for dumping (populating) data into destination table

WriteToServer method

^

close connection(s)
release resources

Please, see my past answers for further details:
Upload excel to VB net[^]
import excel to db with file path[^]
How to import large excel file in datatable[^]

Posted 30-Aug-16 5:52am

Maciej Los

Add a Solution

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

Patrice T · Accepted Answer · 2016-08-30T05:21:00

Solution 1

Quote:
How do I pass 60 thousand + record dataset as XML to stored procedure using stringwriter and writetoxml ?

In small chuncks!
The trick is to reduce the memory footprint. It is just a way to avoid the memory limit, it will not improve the design.
Use a loop in which you build a small datasets of next 100 records, load it to database and loop for next set until the end.

I agree with Maciej Los, it sounds like bad design and abuse of facilities provided with the language.
Only if you explain what you do and the reasons, we will be able to help more.

Posted 30-Aug-16 5:21am

Patrice T

Comments

Maciej Los 30-Aug-16 11:57am

Good advice.
Thanks for mentioning about my comment to the question.
Cheers,
Maciej

Patrice T 30-Aug-16 11:58am

You welcome, it is normal.

Maciej Los 30-Aug-16 11:59am

;)[EDIT]
BTW: have you seen my answer?

Abrar Kazi 30-Aug-16 12:29pm

StringWriter sw = new StringWriter();
dataSet.WriteXml(sw);
string result = sw.ToString();

Heres the code I'm using

Patrice T 30-Aug-16 14:44pm

Use Improve question to update your question.
So that everyone can pay attention to this information.

How do I pass 60 thousand + record dataset as XML to stored procedure using stringwriter and writetoxml ?

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

How do I pass 60 thousand + record dataset as XML to stored procedure using stringwriter and writetoxml ?

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us