What is the fastest way to write to a text file from a database table?

Question

I have a data analysis application, and I need to be able to export database tables to a delimited text file using C #. Due to the architecture of the application, this data must be added to the C # application. The database export function cannot be used. The size of the tables can vary from several columns and from several hundred rows to ~ 100 columns to more than a million rows.

Additional comments based comments -

I have a Windows service acting as a data access layer that will receive an export request from a presentation layer. After the export is complete, the service will then have to transfer the export back to the presentation layer, which will be either a WPF application or a Silverlight application as a stream object. Then the user will be given the opportunity to save or open the export.

What is the fastest way to do this?

thanks

Answer 1

hmm, first of all, if it is not necessary to use C #, the sql management console can perform such a task.

To achieve the best performance, I would call consumer-producer flow concept 2,

• One thread will be the reader responsible for reading items from the database - in this case, I highly recommend using IReader to read values ​​and putting them in the cuncurrent queue.
• Another will be a writer who will simply use fileStream to write data from the queue.

You can also achieve much greater productivity by reading the information through the page image, that is, if you know that you will have 100,000 records, divide them into pieces of 1000, ask the reader to read these pieces from the database and put them in the queue.

Although the later solution is more complicated, it will allow you to use your processor in the best way and avoid latency.

Answer 2

for SQL Server: use BCP

http://www.simple-talk.com/sql/database-administration/creating-csv-files-using-bcp-and-stored-procedures/

Answer 3

If you are using SQL Server 2008 (or possibly 2005), you can right-click the database and select "Tasks-> Export Data". Select the database as input and select "Flat file destination" as the output. Specify a file name, specify a double quote as a text specifier, click Next several times, and you're done. You can even save the task as an SSIS package that you can run again.

Performing this method uses SSIS under covers. It has very high performance because it uses multiple threads in the pipeline.

Answer 4

I would look at the SQLBulkCopy object.

Answer 5

If you really need to use C #, the fastest way would be to use ADO.NET DataReader, it is read-only and forwarding only, it might suit you. Just be careful with null fields, this doesn't work very well if you need to deal with them, maybe other ADO.NET resources will be more interesting for you.

Answer 6

If you just need to quickly query the data, you can use the "Firehose" cursors in one or more threads and just read directly from the database.

Answer 7

var sqlConnection = new SqlConnection(ConfigurationManager.ConnectionStrings["connstr"].ToString());
var sqlDataAdapter = new SqlDataAdapter("select * from tnm_story_status", sqlConnection); sqlConnection.Open();
var dataSet = new DataSet();
sqlDataAdapter.Fill(dataSet); sqlConnection.Close();
var dataTable = dataSet.Tables[0];
var streamWriter = new StreamWriter(@"C:\db.txt", false);
var sb = new StringBuilder();
for (var col = 0; col < dataTable.Columns.Count; col++)
{
if (sb.ToString() != "") sb.Append(",");
sb.Append(dataTable.Columns[col].ColumnName);
}
streamWriter.WriteLine(sb.ToString());
sb.Remove(0, sb.ToString().Length);
for (var row = 0; row < dataTable.Rows.Count; row++ )
{
for (var col = 0; col < dataTable.Columns.Count; col++)
{
if (sb.ToString() != "") sb.Append(",");
sb.Append(dataTable.Rows[row][col].ToString());
}
streamWriter.WriteLine(sb.ToString());
sb.Remove(0, sb.ToString().Length);
}
streamWriter.Close();