protected virtual void TryExtractSupportingSQLTableImpl(SupportingSQLTable sqlTable, DirectoryInfo directory, IExtractionConfiguration configuration, IDataLoadEventListener listener, out int linesWritten, out string destinationDescription)
{
var extractor = new ExtractTableVerbatim(sqlTable.GetServer(), sqlTable.SQL, sqlTable.Name, directory, configuration.Separator, DateFormat);
linesWritten = extractor.DoExtraction();
destinationDescription = extractor.OutputFilename;
}
public override void Execute()
{
base.Execute();
var collection = new ViewCohortExtractionUICollection(Cohort)
{
Top = Sample,
IncludeCohortID = _includeCohortID
};
FileInfo toFile = ToFile;
if (AskForFile)
{
toFile = BasicActivator.SelectFile("Save cohort as", "Comma Separated Values", "*.csv");
if (toFile == null)
{
// user cancelled selecting a file
return;
}
}
if (toFile == null)
{
BasicActivator.ShowData(collection);
}
else
{
ExtractTableVerbatim.ExtractDataToFile(collection, toFile);
}
}
private void WriteBundleContents(IExtractableDatasetBundle datasetBundle, IDataLoadEventListener job, GracefulCancellationToken cancellationToken)
{
var rootDir = _request.GetExtractionDirectory();
var supportingSQLFolder = new DirectoryInfo(Path.Combine(rootDir.FullName, SupportingSQLTable.ExtractionFolderName));
var lookupDir = rootDir.CreateSubdirectory("Lookups");
//extract the documents
foreach (SupportingDocument doc in datasetBundle.Documents)
{
datasetBundle.States[doc] = TryExtractSupportingDocument(rootDir, doc, job)
? ExtractCommandState.Completed
: ExtractCommandState.Crashed;
}
//extract supporting SQL
foreach (SupportingSQLTable sql in datasetBundle.SupportingSQL)
{
datasetBundle.States[sql] = TryExtractSupportingSQLTable(supportingSQLFolder, _request.Configuration, sql, job, _dataLoadInfo)
? ExtractCommandState.Completed
: ExtractCommandState.Crashed;
}
//extract lookups
foreach (BundledLookupTable lookup in datasetBundle.LookupTables)
{
try
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to extract lookup " + lookup));
var server = DataAccessPortal.GetInstance().ExpectServer(lookup.TableInfo, DataAccessContext.DataExport);
Stopwatch sw = new Stopwatch();
sw.Start();
//extracts all of them
var extractTableVerbatim = new ExtractTableVerbatim(server, new [] { lookup.TableInfo.Name }, lookupDir, _request.Configuration.Separator, DateFormat);
int linesWritten = extractTableVerbatim.DoExtraction();
sw.Stop();
job.OnProgress(this, new ProgressEventArgs("Lookup " + lookup, new ProgressMeasurement(linesWritten, ProgressType.Records), sw.Elapsed));
if (_request is ExtractDatasetCommand)
{
var result = (_request as ExtractDatasetCommand).CumulativeExtractionResults;
var supplementalResult = result.AddSupplementalExtractionResult("SELECT * FROM " + lookup.TableInfo.Name, lookup.TableInfo);
supplementalResult.CompleteAudit(this.GetType(), extractTableVerbatim.OutputFilename, linesWritten);
}
datasetBundle.States[lookup] = ExtractCommandState.Completed;
}
catch (Exception e)
{
job.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Error occurred trying to extract lookup " + lookup + " on server " + lookup.TableInfo.Server, e));
datasetBundle.States[lookup] = ExtractCommandState.Crashed;
}
}
haveWrittenBundleContents = true;
}
protected virtual void TryExtractLookupTableImpl(BundledLookupTable lookup, DirectoryInfo lookupDir, IExtractionConfiguration requestConfiguration, IDataLoadEventListener listener, out int linesWritten, out string destinationDescription)
{
//extracts all of them
var extractTableVerbatim = new ExtractTableVerbatim(lookupDir, _request.Configuration.Separator, DateFormat, lookup.TableInfo.Discover(DataAccessContext.DataExport));
linesWritten = extractTableVerbatim.DoExtraction();
destinationDescription = extractTableVerbatim.OutputFilename;
}
public override void ShowData(IViewSQLAndResultsCollection collection)
{
var point = collection.GetDataAccessPoint();
var db = DataAccessPortal.GetInstance().ExpectDatabase(point, DataAccessContext.InternalDataProcessing);
var toRun = new ExtractTableVerbatim(db.Server, collection.GetSql(), Console.OpenStandardOutput(), ",", null);
toRun.DoExtraction();
}
public override void Execute()
{
if (ToFile == null)
{
BasicActivator.ShowData(_collection);
}
else
{
ExtractTableVerbatim.ExtractDataToFile(_collection, ToFile);
}
}
public override void Execute()
{
base.Execute();
if (_toFile != null)
{
var collection = new ViewAggregateExtractUICollection(_aggregate);
ExtractTableVerbatim.ExtractDataToFile(collection, _toFile);
}
else
{
BasicActivator.ShowGraph(_aggregate);
}
}
public override void Execute()
{
base.Execute();
var collection = new ArbitraryTableExtractionUICollection(_table)
{
OverrideSql = _query
};
if (_toFile != null)
{
ExtractTableVerbatim.ExtractDataToFile(collection, _toFile);
}
else
{
BasicActivator.ShowData(collection);
}
}
public override void Execute()
{
DataTable dt = new DataTable();
var db = SelectOne(_loggingServers, null, true);
var server = db.Discover(DataAccessContext.Logging).Server;
if (db != null)
{
using (var con = server.GetConnection())
{
con.Open();
string sql = String.Format(@"SELECT * FROM (
SELECT [dataLoadRunID]
,eventType
,[description]
,[source]
,[time]
,[ID]
FROM {0}
{2}
UNION
SELECT [dataLoadRunID]
,'OnError'
,[description]
,[source]
,[time]
,[ID]
FROM {1}
{2}
) as x
order by time ASC", LoggingTables.ProgressLog, LoggingTables.FatalError, _filter.GetWhereSql());
var output = BasicActivator.SelectFile("Output CSV file");
var extract = new ExtractTableVerbatim(server, sql, output.Name, output.Directory, ",",
CultureInfo.CurrentCulture.DateTimeFormat.FullDateTimePattern);
extract.DoExtraction();
}
}
}
private bool TryExtractSupportingSQLTable(DirectoryInfo directory, IExtractionConfiguration configuration,
SupportingSQLTable sql, IDataLoadEventListener listener, DataLoadInfo dataLoadInfo)
{
try
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Preparing to extract Supporting SQL " + sql + " to directory " + directory.FullName));
Stopwatch sw = new Stopwatch();
sw.Start();
//start auditing it as a table load
string target = Path.Combine(directory.FullName, sql.Name + ".csv");
var tableLoadInfo = dataLoadInfo.CreateTableLoadInfo("", target, new[] { new DataSource(sql.SQL, DateTime.Now) }, -1);
var extractor = new ExtractTableVerbatim(sql.GetServer(), sql.SQL, sql.Name,
directory, configuration.Separator, DateFormat);
int sqlLinesWritten = extractor.DoExtraction();
sw.Stop();
//end auditing it
tableLoadInfo.Inserts = sqlLinesWritten;
tableLoadInfo.CloseAndArchive();
if (_request is ExtractDatasetCommand)
{
var result = (_request as ExtractDatasetCommand).CumulativeExtractionResults;
var supplementalResult = result.AddSupplementalExtractionResult(sql.SQL, sql);
supplementalResult.CompleteAudit(this.GetType(), extractor.OutputFilename, sqlLinesWritten);
}
else
{
var extractGlobalsCommand = (_request as ExtractGlobalsCommand);
Debug.Assert(extractGlobalsCommand != null, "extractGlobalsCommand != null");
var result =
new SupplementalExtractionResults(extractGlobalsCommand.RepositoryLocator.DataExportRepository,
extractGlobalsCommand.Configuration,
sql.SQL,
sql);
result.CompleteAudit(this.GetType(), extractor.OutputFilename, sqlLinesWritten);
extractGlobalsCommand.ExtractionResults.Add(result);
}
listener.OnProgress(this, new ProgressEventArgs("Extract " + sql, new ProgressMeasurement(sqlLinesWritten, ProgressType.Records), sw.Elapsed));
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Extracted " + sqlLinesWritten + " records from SupportingSQL " + sql + " into directory " + directory.FullName));
return(true);
}
catch (Exception e)
{
if (e is SqlException)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Failed to run extraction SQL (make sure to fully specify all database/table/column objects completely):" + Environment.NewLine + sql.SQL, e));
}
else
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Failed to extract " + sql + " into directory " + directory.FullName, e));
}
return(false);
}
}
