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?