Writing Output of ODBC Query to Text file

Question

I am having trouble with the below code in writing the data to output text file, it's writing the column names fine but not the containing row (1);

I am getting the following error:

Unhandled exception. System.InvalidOperationException: Invalid attempt to call Read when reader is closed. at System.Data.Odbc.OdbcDataReader.Read() at Testing_Studio.Program.Main(String[] args) in C:\Users\Test\Desktop\Testing_Studio\Program.cs:line 31

using System;
using System.IO;
using System.Text;
using System.Data.Odbc;
using System.Data;
using System.Linq;

namespace Testing_Studio
{
    class Program
    {
        static void Main(string[] args)
        {
            String sqlQuery = File.ReadAllText(@"C:\Users\Test\Desktop\Testing_Studio\Script_2.txt");
            //display query: Console.WriteLine(sqlQuery);

            {
                //create connection
                OdbcCommand comm = new OdbcCommand();
                comm.Connection = new odbcConnection("Dsn=Test;uid=Test;pwd=Test");

                 comm.CommandText = sqlQuery;
                 comm.Connection.Open();

                 OdbcDataReader sqlReader = comm.ExecuteReader();
                 var dataTable = new DataTable(); 
                 dataTable.Load(sqlReader);
                 // Open the file for write operations.  If exists, it will overwrite due to the "false" parameter
                 using (StreamWriter writer = new StreamWriter(@"C:\Users\Test\Desktop\Testing_Studio\output.txt"))
                 {
                      while (sqlReader.Read())
                      {
                          //// get column names
                          var columnNames = dataTable.Columns.OfType<DataColumn>().Select(x => x.ColumnName);

                          //// write column names row
                         writer.WriteLine(String.Join("\t", columnNames));

                        //// get column count
                        int columnCount = dataTable.Columns.Count;
                        Console.WriteLine(columnCount);
                        //// array to hold data values
                        string[] values = new string[columnCount];

                        //// write columns in each row
                        foreach (DataRow row in dataTable.Rows)
                        {   
                             for (int i = 0; i < columnCount; i++)
                             {
                                  object value = row[i];
                                  //// check row value for null
                                  values[i] = (value != null) ? value.ToString() : "NULL";
                                  Console.WriteLine(values[i]);
                             }
                             //// write data line
                             writer.WriteLine(String.Join("\t", values));
                        }  
                    }
                }
                sqlReader.Close();
                comm.Connection.Close();
            }
        }
    }
}

Many Thanks for the support!

Answer 1

You shouldn't work like that.. you need a backend service and work with http client