How to retrieve column names when saving a SQL query to a text file

Question

I wrote a method which saves the content of a specific table to a text file. Unfortunately the names of the columns are not retrieved. "Only" the data of each cell is written to the text file.

How do i need to adapt my code to also include the name of the columns?

private void WriteSQLQueryOutputToTextFile(string DBUser, string DBUserPassword, string sqlQuery, string databaseName, string nameOfOutputFile, string nameOfRow0, string nameOfRow1, string nameOfRow2)
{
  StreamWriter outputFile = new StreamWriter(dWTestResult + "\\DatabaseUpgradeCheck\\" + nameOfOutputFile);

  using (SqlConnection sqlCon = new SqlConnection("Data Source=" + GetEnvironmentVariable.MachineName + "; Initial Catalog=" + databaseName + "; User ID=" + DBUser + "; Password=" + DBUserPassword + ";"))
  {
      SqlCommand command = new SqlCommand(sqlQuery, sqlCon);
      sqlCon.Open();
      SqlDataReader reader = command.ExecuteReader();
      try
      {
          while (reader.Read())
          {
              outputFile.WriteLine(String.Format("{0}, {1}, {2}",
              reader[nameOfRow0], reader[nameOfRow1], reader[nameOfRow2]));
          }
      }
      catch (Exception ex)
      {
          logger.Debug(ex, "Writing Database Output to the text file failed");
      }
      finally
      {
          reader.Close();
          outputFile.Close();
      }     
   }
}

An option for you: If you use a DataAdapter and fill a DataTable you will get the Column Headers in the DataTable — JayV, Mar 15 '19 at 10:10

haldo · Accepted Answer · 2019-04-06T20:50:05.930

2

Add a count variable, and if count == 0 add the column names. It looks like you know the names of the columns already so you have a couple of options.

First option: Just write the name.

try
{
    int count = 0;
    while (reader.Read())
    {
        if (count == 0)
        {
            outputFile.WriteLine(String.Format("{0}, {1}, {2}",
                nameOfRow0, nameOfRow1, nameOfRow2));
        }

        outputFile.WriteLine(String.Format("{0}, {1}, {2}",
            reader[nameOfRow0], reader[nameOfRow1], reader[nameOfRow2]));

        count++;    
    }
}

Or (if you don't know the column names) use reader.GetName(i):

try
{
    int count = 0;
    while (reader.Read())
    {
        // if this is the first row, read the column names
        if (count == 0)
        {
            outputFile.WriteLine(String.Format("{0}, {1}, {2}",
               reader.GetName(0), reader.GetName(1), reader.GetName(2)));
        }

        // otherwise just the data (including 1st row)
        outputFile.WriteLine(String.Format("{0}, {1}, {2}",
           reader.GetValue(0), reader.GetValue(1), reader.GetValue(2)));

        count++;         
    }
}

edited Apr 06 '19 at 20:50

answered Mar 15 '19 at 10:20

haldo

14,512
5
46
52

Hey Haldo, i really like your approaches. I used the one where the column name are not know because it makes the method more flexible. Even though i copied your example (shame on me) it only retrieves the column names. Any idea why? – fabian09 Mar 15 '19 at 11:20
@fabian09 Yes, I had an error in the code. Corrected now - the `else` is not needed as you always want to read the data in the first row, but you only want to read the column names for the first iteration. With the `else` statement it was skipping the first row of data. It should read the first row of data now, and read the column names. Let me know if it still doesn't work. – haldo Mar 15 '19 at 11:32
Now, it works :). One more question. Do you have any idea if it is possible to retrieve the exact format of the DB into the text file? Meaning: the same structure of the DB. Right now, every line is aligned to the left. That is why the content of the column does not fit to the column names – fabian09 Mar 15 '19 at 11:44
@fabian09 That's a tricky task because each column may have a different length. If all columns are separated by `,` you could rename the file to use a `.csv` extension then you can open it in Excel and the columns/rows will look just like the database. – haldo Mar 15 '19 at 11:49
Okay. Thank you. – fabian09 Mar 15 '19 at 11:59
i need to ask one more question. The thing is, i want to read out six database tables with a different amount of columns. Your suggestions are bound to three columns. If i want to read out a table with only two columns i get an System.IndexOutOfRangeException: Index was outside the bounds of the array. Is there a way to make your code more flexible so that i can handle different kinds of table with different amounts of columns? – fabian09 Apr 02 '19 at 08:43
@fabian09 you could use `FieldCount` of the data reader or use SqlDataAdapter and a DataTable instead of sql data reader. Both of these options you will probably need to loop over the columns to create your desired output. – haldo Apr 02 '19 at 15:57

score 0 · Answer 2 · answered Mar 15 '19 at 10:20

0

hi i think you can use : reader.GetName('index') for example for first column : reader.GetName(0)

See more at this link : https://learn.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqldatareader.getname?view=netframework-4.7.2

answered Mar 15 '19 at 10:20

Sanpas

1,170
10
29

score 0 · Answer 3 · answered Mar 15 '19 at 10:22

Please try this and set data row and column name as your need.

using (SqlConnection sqlCon = new SqlConnection("Data Source=" + GetEnvironmentVariable.MachineName + "; Initial Catalog=" + databaseName + "; User ID=" + DBUser + "; Password=" + DBUserPassword + ";"))
  {
      SqlCommand command = new SqlCommand(sqlQuery, sqlCon);
      sqlCon.Open();
      SqlDataAdapter da = new SqlDataAdapter();
      da.SelectCommand = cmd; 
      DataTable dt = new DataTable(); 
      da.fill(dt);
      try
      {
         if(dt != null && dt.Rows.Count > 0)
         {
            string columnName = dt.Columns[0].ToString();
            DataRow dr = dt.Rows[0];
         }
      }
      catch (Exception ex)
      {
          logger.Debug(ex, "Writing Database Output to the text file failed");
      }
      finally
      {
          reader.Close();
          outputFile.Close();
      }     
   }

How to retrieve column names when saving a SQL query to a text file

3 Answers3