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

Posted on 2012-08-30
Last Modified: 2012-08-30
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?
Question by:OFGemini
    1 Comment
    LVL 35

    Accepted Solution

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    This document covers how to connect to SQL Server and browse its contents.  It is meant for those new to Visual Studio and/or working with Microsoft SQL Server.  It is not a guide to building SQL Server database connections in your code.  This is mo…
    Wouldn’t it be nice if you could test whether an element is contained in an array by using a Contains method just like the one available on List objects? Wouldn’t it be good if you could write code like this? (CODE) In .NET 3.5, this is possible…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now