Solved

Inserting Text Paragraphs into MySQL DB from VB.NET

Posted on 2011-03-23
1
605 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

Secure Your Active Directory - April 20, 2017

Active Directory plays a critical role in your company’s IT infrastructure and keeping it secure in today’s hacker-infested world is a must.
Microsoft published 300+ pages of guidance, but who has the time, money, and resources to implement? Register now to find an easier way.

Question has a verified solution.

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

Suggested Solutions

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…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 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