Insert into database from array

[JonoB]

Hi all,

I am parsing a relatively large text file, and I need to split the information into various fields, and then insert the parsed data into a database.

At the moment, I have the parsing bit working fine, and I create an array to temporarily hold all the information - this is a 2-dimension array of 10 columns and about 150,000 rows (although this can vary).

Whats the easiest way for me to insert the data into the database (at the moment I am using sql server compact)? I am hoping that I dont have to loop through the array, and that I can do some kind of bulk insert.

I'm (very) new to vb (coming from a vba background though), so please go easy on me! With VBA, I would probably use an ADO recordset, but I am guessing (wildly!) that some form of SqlCeResultset may be needed in this case - am I at least on the right track?

Specs:
Windows XP
Visual Basic express 2008
Sql Server Compact 3.5

Thanks in advance.

 

[Qik3Coder]

You're going to have to spool the array. If i were doing this, i would create a data struct, and load a collection with all the structs then spool the collection, and insert all the rows into the database.

I cannot think of a way to get around spooling the collection/array to load the database. I have something similar that runs once a week.

it's not bad, you just start it and walk away.

You will have to watch out for malformed data, and start and end conditions on your reading the file. Also, you are going to have to have some "fuzzy" handling on the insert into the database, in case you come across a timeout or something similar.

-Sometimes the answer to your question is the hack that works

 

[JonoB]

So, if I understand you correctly, I use a collection to hold the data, and I then loop through the collection and insert one record at a time into the database?

 

[chrissie1]

I believe .net20. has a bulkinsert option.

Christiaan Baes
Belgium

My Blog

 

[chrissie1]

here is one method of doing it.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;316244

but inapparently (or so mr google tells me) there is also an SqlBulkUpdate class. Ask mr google for more help. Or the help files.


Christiaan Baes
Belgium

My Blog