Solved

Inserting Text Paragraphs into MySQL DB from VB.NET

Posted on 2011-03-23
1
603 Views
Last Modified: 2012-05-11
I am trying to load portions of 300,000KB files into a table of MySQL database. The portions may contain many pages of news articles with various punctuation elements, including parethesis, quotes and semicolons.

I attempted to compile an "Insert" statement to insert the text, but for reasons realted to punctuation that failed miserably.  

Another approach I am considering is to dump the text into another data file "file.data" and initiate a bulk insert operation. The problem with that approach is I have many 300,000KB files I need to process and reading such large files from the hard drive, loading portions of these files in memory, creating temp file on the harddrive for bulk operations, then having MySQL Server read the temp files from the Hard Drive seens very slow and redundant.

There is got to be a simple way of sending data that is already stored in an object in memory as a packet to MySql Server for processing...

The points will be awarded to an expert who can advise how to efficiently load text stored in VB.NET StringCollection object into a MySQL DB table column of type "Text", while preserving the original formatting of the text.

Thank you.
0
Comment
Question by:cyber-33
1 Comment
 
LVL 75

Accepted Solution

by:
käµfm³d   👽 earned 500 total points
ID: 35202998
The only "punctuation" you should have to worry about when inserting text data into the DB is the single quote ( ' ) since that's what SQL Server uses to denote strings. All you should need to do is double-up single quotes in your source text. This is known as "escaping" and causes such a quote to be considered part of the data and not a string "marker." You could use a simple Replace() to do this:

    data = data.Replace("'", "''")  ' one single quote replaced by two singe quotes; all occurrence ls
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

770 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