Umm E Habiba Siddiqui February 2016

Column names are not saving in text file while exporting from SQL

I am exporting a table from SQL in a text file, data is exporting fine but column names are not coming in text file. here is the code:

   public static void getfiles()
   {
       SqlDataReader reader;
       string query = "Select * from tTable";
       string connStr = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;
       string strDelimiter = "\"|\"";
       string strFilePath = @"path";
       StringBuilder sb = new StringBuilder();

       using (SqlConnection conn = new SqlConnection(connStr))
       {
           conn.Open();
           using (reader = new SqlCommand(query, conn).ExecuteReader())
           {
               if (reader.HasRows)
               { 
                   Object[] items = new Object[reader.FieldCount];

                   while (reader.Read())
                   {
                       reader.GetValues(items);
                       foreach (var item in items)
                       {

                           sb.Append(item.ToString());
                           sb.Append(strDelimiter);
                       }
                       sb.Append("\n");
                   }
               }
           }
           conn.Close();
           File.WriteAllText(strFilePath, sb.ToString());
       }
   }

I don't know what changes to make in this method

Answers


René Vogt February 2016

You simply don't write the column names. This can be achieved by reader.GetName() method like this:

using (reader = new SqlCommand(query, conn).ExecuteReader())
{
    for (int i=0; i<reader.FieldCount; i++)
    {
        sb.Append(reader.GetName(i));
        sb.Append(strDelimiter);
    }
    sb.Append(Environment.NewLine);

    // your code here...
    if (reader.HasRows)
    {
        // etc...
    }
}


CodeDecode February 2016

You could include column names in your select statement ....

Select Column1 from(select Sorter = 1, Convert(VARCHAR(max), Column1) as Column1
    from TableName union all
    select 0, 'Column1') X
order by Sorter, Column1

Post Status

Asked in February 2016
Viewed 3,906 times
Voted 9
Answered 2 times

Search




Leave an answer