private static void ProcessTable(SqlConnection sqlConnection, Table table, Project project)
{
var outputFolderPath = string.IsNullOrEmpty(table.Output) ? project.OutputFolder.Value : table.Output;
if (!Directory.Exists(outputFolderPath))
{
Directory.CreateDirectory(outputFolderPath);
}
string fullFileName = Path.Combine(outputFolderPath, table.Csv);
int records = 0;
try
{
List<Tuple<string, string>> schema = GetTableSchema(sqlConnection, table.Name);
if (schema.Count == 0)
{
Log.Warning("No columns was found in the '{0}' table to export into a csv file.", table.Name);
return;
}
if (File.Exists(fullFileName))
{
File.Delete(fullFileName);
}
using (var streamWriter = new StreamWriter(new FileStream(fullFileName, FileMode.OpenOrCreate)))
{
var csvWriter = new CsvWriter(streamWriter);
// Write Header
foreach (var tuple in schema)
{
csvWriter.WriteField(tuple.Item1);
}
csvWriter.NextRecord();
// Write records
var query = ConstructRecordQuery(table.Name, schema, project.MaximumRowsInTable.Value);
using (var command = new SqlCommand(query) {Connection = sqlConnection})
{
using (var dataReader = command.ExecuteReader())
{
while (dataReader.Read())
{
for (int i = 0; i < schema.Count; i++)
{
object value = dataReader.GetValue(i);
if (value is string)
{
value = ((string) value).Trim(); // Remove all white spaces
}
csvWriter.WriteField(value);
}
records++;
csvWriter.NextRecord();
}
}
}
}
Log.Info("{0} records of '{1}' table successfully exported to {2}.", records, table.Name, fullFileName);
}
catch (Exception ex)
{
Log.Error("{0} export failed because of exception: {1}", table.Name, ex.Message);
}
}
private static List<Table> GetAvailableTables(SqlConnection sqlConnection, string outputFolder)
{
const string CommandText = "SELECT '['+TABLE_SCHEMA+'].['+ TABLE_NAME + ']' FROM INFORMATION_SCHEMA.TABLES ORDER BY TABLE_SCHEMA, TABLE_NAME";
var result = new List<Table>();
using (var schemaCommand = new SqlCommand(CommandText) {Connection = sqlConnection, CommandTimeout = 300})
{
using (var schemaReader = schemaCommand.ExecuteReader())
{
while (schemaReader.Read())
{
var table = new Table();
table.Name = schemaReader.GetString(0);
table.Csv = ExtractTableName(table.Name) + ".csv";
table.Output = outputFolder;
result.Add(table);
}
}
}
return result;
}