Write Text file with delimiter

Question

I am using below code to write a text file with | delimiter

int i = 0;

StreamWriter sw = null;

sw = new StreamWriter(filePath, false);

for (i = 0; i < dt.Columns.Count; i++)

{ sw.Write(dt.Columns[i].ToString() + "\t"); }

sw.WriteLine();

foreach (DataRow row in dt.Rows)

{ object[] array = row.ItemArray; for (i = 0; i < array.Length; i++)

{ sw.Write(array[i].ToString() + "\t"); }

sw.WriteLine(); }

sw.Close();

but it writes the text file with | delimiter at the end of column and row ..

How can I not write the | delimiter at the end of Colum and row

Current result

Colum1 | column2| column3|

1001 | 1002 | 1003 |

And I would like to create file without the delimiter at the end of column and row

Colum1 | column2| column3

1001 | 1002 | 1003

Vulpes · Accepted Answer

Try it like this:
int i = 0;    StreamWriter sw = null;    sw = new StreamWriter(filePath, false);
for (i = 0; i    {        sw.Write(dt.Columns[i].ToString());        if (i    }
sw.WriteLine();
foreach(DataRow dr in dt.Rows)    {        object[] array = row.ItemArray;
for (i = 0; i       {           sw.Write(array[i].ToString());           if (i       }
sw.WriteLine();     }
sw.Close();

j c · Answer

thank you. It seems working...

Vulpes · Answer

Well the default timeout to execute a command is 30 seconds though if the stored procedure is operating on a ton of data and/or the result-set is very large this could be exceeded.
I'd try doubling it:
cmd.CommandTimeOut = 60;
You can also set it to infinite:
cmd.CommandTimeOut = 0;
but I'd avoid doing that if you can.

j c · Answer

Thanks.. but now i am geeting" Query timeout expired" error when I try to execute it
Do you think the resultset takes too long or large?  how can i redove this error?

Vulpes · Answer

The code to fill a datatable from the output of a stored procedure will be something like this:
DataTable dt = new DataTable();
using (OleDbConnection conn = new OleDbConnection("connection string")) {     using (OleDbCommand cmd = new OleDbCommand("Queryoutput", con))     {        using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))        {            cmd.CommandType = CommandType.StoredProcedure;
try           {              da.Fill(dt);           }           catch (Exception ex)           {              MessageBox.Show(ex.Message);           }        }     } }

j c · Answer

Hi Vulpes,
How can I fill the data table object with out put of Stored procedure
I have stored procedure Queryoutput and I would like to fill the data table object with the result of this stored procedure and qrite to a text file..
something like below..
OleDbDataAdapter A = new OleDbDataAdapter();
System.Data.DataTable dt = new System.Data.DataTable();
A.Fill(dt, "fill with output of Stored Procedure" );
{ sw.Write(dt.Columns[i].ToString());
}
sw.WriteLine();
{
{ sw.Write(array[i].ToString());
}
sw.WriteLine(); }
sw = int i = 0; StreamWriter sw = null ; new StreamWriter (filePath, false ); Stopwatch watch = Stopwatch .StartNew(); for (i = 0; i if (i "	" ); foreach ( DataRow row in dt.Rows) object [] array = row.ItemArray; for (i = 0; i if (i "	" );

j c · Answer

Thank you. I will try it and let you know

Vulpes · Answer

OK, here's some code to get the elapsed time and write this and the number of records to the database.
The elapsed time is in milliseconds (a long integer) and you might want to divide that by 1000 to get the number of complete seconds and cast to an int, depending on the database column type:
using System.Diagnostics;
//...
int i = 0; StreamWriter sw = null; sw = new StreamWriter(filePath, false);
Stopwatch watch = Stopwatch.StartNew();   for (i = 0; i {     sw.Write(dt.Columns[i].ToString());     if (i }
sw.WriteLine();
foreach(DataRow dr in dt.Rows) {     object[] array = row.ItemArray;
for (i = 0; i    {        sw.Write(array[i].ToString());        if (i    }
sw.WriteLine();  }
sw.Close(); watch.Stop(); long elapsed = watch.ElapsedMilliseconds;
using (SqlConnection conn = new SqlConnection("connection string")) {     using (SqlCommand cmd = new SqlCommand())     {         cmd.CommandText = @"INSERT INTO Outputlog(TotalRecord, TotalTime) VALUES (@TotalRecord, @TotalTime)";         cmd.Parameters.AddWithValue("@TotalRecord", dt.Rows.Count);          cmd.Parameters.AddWithValue("@TotalTime", elapsed);         cmd.Connection = conn;         conn.Open();         try         {             cmd.ExecuteNonQuery();         }         catch (Exception ex)         {              MessageBox.Show(ex.Message);         }     } }
There are no limitations on StreamWriter to my knowledge as it just writes the records one by one to the file. However, I'd be very surprised if doing it this way was as fast as using a native SQL Server utility such as BCP which is optimized for dealing with a large number of records.

j c · Answer

hi Vulpes,
Yes. the total time it took to write records to a text file.
I am using SQL server 2008 R2 and the table is called Outputlog and name of columns : TotalRecord, TotalTime
Also, Is there any limitations of using StreamWriter  in .net  i.e record size, data size,etc
The data can be quite large( millions of record)  and I was wondering if there are any limitations of uinsg this class in terms of how many records you can write compare to other Database tool such as BCP, SQLCMD,etc...
Thanks

Vulpes · Answer

Well, the number of rows created will be dt.Rows.Count.
When you say the total time do you mean the time that it took to write these records to the text file?
As regards the database, which one do you use and what's the name of the table and the columns for these two items?