[Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 629
  • Last Modified:

How do I insert lots of Text Data into SQL SERVER from C#?

Hi Experts,

How do I insert lots of Text Data into SQL SERVER from C#?  My issue is I'm extracting text from a very large PDF (600MB with 1200pages) and trying to insert it into a NVARCHAR(MAX) column.

I've notice that as I read each page of the PDF and create a string out of the text, the memory rises and rises until the application crashes.  My PDF has over 1200 pages.  Then when I tried to insert the Text into the DB, I get this exception and my memory usage is about 1.4 GB of RAM.

 System.Data.SqlClient.SqlException | There is insufficient memory available in the buffer pool
. |    at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)

Then I tried appending the text of each page into the Database but my app still uses high amounts of memory and I still running into memory exceptions.

I've tried sticking a GC.Collect() into my FOR loop that reads each page of the PDF, but it doesn't help with reducing memory.  Seems like creating the String object builds up the memory.  When I only read the text from each page but don't assign it to a string memory doesn't build up.

Any ideas on how to do this?
1 Solution
Robert SchuttSoftware EngineerCommented:
You could try using a StringBuilder instead of a String but it seems there has to be a less memory intensive way, like writing to the database field in a chunked way (I think that's the term, I used it in Classic ASP once, but haven't used it in .NET)

It could be as easy as using a query like:
update table set field = field + ? where id=?

Open in new window

Then call that after constructing the StringBuilder for each page for example (changing the initial insert to set the text field to an empty string). I think a colleague of mine did something like that some time ago, specifically in that case because an Oracle statement couldn't exceed 4000 characters I believe.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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