Storing and saving data from sql database

[ogsirus]

Hi Guys,

Im a bit puzzled on the best way to store data which will later be saved to a csv. I am doing a record search on a database table which will get cycle through each employee and get the relevent information. Once I have done this I will then create a string builder which will go through all this information and append the csv. The problem is im struggling to find the best way to store this information in the meantime. Ive tried something funky with a list dictionary but it didnt come off right. Any ideas anyone?

Regards

[ddanbe]

I would omit the StringBuilder phase and read the DB and write to the csv until done.

[ogsirus]

Would that not lead to more calls to the db which will affect performance?

[ddanbe]

I'm no DB expert, but perhaps some stored procedure could write directly to the cvs?
Perhaps read the DB and write to cvs?
The scheme read DB-->append stringbuilder-->read stringbuilder-->write cvs seems to me not very performant.

[necrovore]

In SQL server you can use the bcp utility for the doing that. See

[deceptikon]

Would that not lead to more calls to the db which will affect performance?

This question suggests you're either doing more than you've said with the database, or there are a huge number of records. Somehow I don't see an employee table being quite so large that you cannot store the whole thing in memory as a DataTable or whatnot. Further, using a single select query with whatever filter you need or one stored procedure call is about as efficient as you're going to get without involving the DBA to optimize on the database server side.

So...what are we missing in this thread?