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.