How to write data in database efficiently using c#?

Question

Hi, my windows app is reading text file and inserting it into the database. The problem is text file is extremely big (at least for our low-end machines). It has 100 thousands rows and it takes time to write it into the database.

Can you guys suggest how should i read and write the data efficiently so that it does not hog machine memory? FYI... Column delimiter : '|' Row delimiter : NewLine

It has approximately 10 columns.. (It has an information of clients...like first name, last name, address, phones, emails etc.)

CONSIDER THAT...I AM RESTRICTED FROM USING BULK CMD.

Answer 1

You don't say what kind of database you're using, but if it is SQL Server, then you should look into the BULK INSERT command or the BCP utility.

Answer 2

Given that there is absolutely no chance of getting help from your security folks and using BULK commands, here is the approach I would take:

1. Make sure you are reading the entire text file first before inserting into the database. Thus reducing the I/O.

2. Check what indexes you have on the destination table. Can you insert into a temporary table with no indexes or dependencies so that the individual inserts are fast?

3. Does this data need to be visible immediately after insert? If not then you can have a scheduled job to read from the temp table in step 2 above and insert into the destination table (that has indexes, foreign keys etc.).

Answer 3

Is it possible for you to register your custom assembly into Sql Server? (I'm assuming it's sql server because you've already said you used bulk insert earlier).

Than you can call your assembly to do (mostly) whatever you need, like getting a file from some service (or whatever your option is), parsing and inserting directly into tables.

This is not an option I like, but it could be a saver sometimes.