public static void ProcessProject(Project project)
{
Log.Info("Project processing started ...");
try
{
using (var sqlConnection = new SqlConnection(project.ConnectionString.Value))
{
sqlConnection.Open();
var tables = project.Tables;
if (project.Tables == null || project.Tables.Count == 0)
{
tables = GetAvailableTables(sqlConnection, project.OutputFolder.Value);
}
Log.Info("{0} tables to process", tables.Count.ToString());
foreach (var table in tables)
{
ProcessTable(sqlConnection, table, project);
}
}
}
catch (SqlException ex)
{
Log.Error(ex.Message);
}
catch (Exception ex)
{
Log.Error(ex);
}
}
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);
}
}