How to use OleDbConnection or SqlCommand to read Schema and database records

Question

Hello,

Can someone suggest how I can improve the code below to use just one kind of SQL connection to read both the Database table column names and the records in the table? I am using OleDb to read the column names from the table, then SqlCommand to read the records, but I would like to use only the one or the other. How can I read the DB records using OleDb please?

Can I use SqlCommand to read the column names from the table schema?

Thank you for taking the time to read this!

 

using System;
using System.IO;
using System.Text;
using System.Data;
using System.Collections; //For using array list
using System.Data.OleDb;
using System.Threading;
using System.Data.SqlClient;
using System.Configuration; //Used for Configuration Manager

namespace exportToCSVfile
{
    public class Program
    {
        static int Main( string[] args )
        {

            //SQL Connection - used for reading the DB table column names
            OleDbConnection connOleDB = new OleDbConnection();
            connOleDB.ConnectionString = ( the connection string );

            //SQL Connection - used for reading the DB records
            SqlConnection conn = new SqlConnection( another connection string );
            DataTable schemaTable;
            string strRow; // represents a full row

            // Used for writing the CSV file.
            string fileOut = ConfigurationManager.AppSettings["directoryPathKey"] + ConfigurationManager.AppSettings["fileCSVNameKey"];

            // Used for writing the log file.
            string fileLogFullPath = ConfigurationManager.AppSettings["directoryPathKey"] + ConfigurationManager.AppSettings["fileLogNameKey"];

            //Check the directory in the config file exists and exit if not
            if ( !Directory.Exists( ConfigurationManager.AppSettings["directoryPathKey"] ) )
            {
                Console.WriteLine (
                 "Directory \"{0}\" does not exist", ConfigurationManager.AppSettings["directoryPathKey"]);
                //Pause
                Console.ReadLine();
                return 0;
            }

 

            try
            {
                connOleDB.Open();

                /// <Summary>
                /// Connect to the database and read the table headers
                /// Be sure to use an account that has permission to list the columns in the table.
                /// <Summary>

                //Retrieve schema information about columns.
                //Restrict to just the Employees TABLE.
                schemaTable = connOleDB.GetOleDbSchemaTable( OleDbSchemaGuid.Columns,
                              new Object[] { null, null, "SomeDatabaseTable", null } );

                //List the column name from each row in the schema table.
                int theArrayBounds = schemaTable.Rows.Count;
                string[] columnNames = new string[theArrayBounds];

                for ( int i = 0; i < schemaTable.Rows.Count; i++ )
                {
                    object objColumnNames = ( schemaTable.Rows[i].ItemArray[3].ToString() );
                    columnNames[i] = objColumnNames.ToString();
                    //Console.WriteLine( schemaTable.Rows[i].ItemArray[3].ToString() );//For debugging purposes
                }

                //Explicitly close - don't wait on garbage collection.
                connOleDB.Close();
                //Console.WriteLine( "Connection Closed." );

 

                /// <Summary>
                /// Connect to the database and read the records
                /// <Summary>
                //Connects to the database, and makes the select command.
                string sqlQuery = "SELECT * FROM SomeDatabaseTable";
                SqlCommand command = new SqlCommand( sqlQuery, conn );
                conn.Open();

                // Creates a SqlDataReader instance to read data from the table.
                SqlDataReader dr = command.ExecuteReader();

                // Retrieves the schema of the table.
                DataTable dtSchema = dr.GetSchemaTable();

                // Writes the column headers.
                StreamWriter sw = new StreamWriter( fileOut, false, Encoding.Default );// Creates the CSV file as a stream, using the given encoding.
                string columnHeaderString = String.Join( ",", columnNames );//Convert an array to a comma-delimited string
                sw.WriteLine( columnHeaderString );

 

                // Reads the rows one by one from the SqlDataReader
                // transfers them to a string with the given separator character and writes it to the file.
                while ( dr.Read() )
                {
                    strRow = "";
                    for ( int i = 0; i < dr.FieldCount; i++ )
                    {
                        strRow += Convert.ToString( dr.GetValue( i ) );
                        if ( i < dr.FieldCount - 1 )
                        {
                            strRow += ",";
                        }
                    }
                    sw.WriteLine( strRow );
                }

                // Closes the text stream and the database connection.
                sw.Close();
                conn.Close();
            }
            catch ( Exception exception )
            {
                Console.WriteLine( exception );
                Thread.Sleep( 3000 );
                using ( TextWriter tw = new StreamWriter( fileLogFullPath ) )
                {
                    tw.WriteLine( DateTime.Now + "Error: " + exception.ToString() );
                }

                //Explicitly close - don't wait on garbage collection.
                connOleDB.Close();
                //Console.WriteLine( "Connection Closed." );
                // Closes the text stream and the database connection.
                conn.Close();
                return 0;
            }
            finally
            {
                //Explicitly close - don't wait on garbage collection.
                connOleDB.Close();
                //Console.WriteLine( "Connection Closed." );
                // Closes the text stream and the database connection.
                conn.Close();
            }
           
            return 1;
        }
    }
}

I have taken help from various sources for the code and I extend my most grateful thanks; I would include references but I've lost the links during my extensive searching.