public DataTableViewerUI(IDataAccessPoint source, string sql, string caption)
{
InitializeComponent();
try
{
using (DbConnection con = DataAccessPortal.GetInstance().ExpectServer(source, DataAccessContext.DataExport).GetConnection())
{
con.Open();
using (var cmd = DatabaseCommandHelper.GetCommand(sql, con))
using (var da = DatabaseCommandHelper.GetDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
}
}
}
catch (Exception e)
{
ExceptionViewer.Show("Failed to connect to source " + source + " and execute SQL: " + Environment.NewLine + sql, e);
}
this.Text = caption;
}
protected override void TryExtractSupportingSQLTableImpl(SupportingSQLTable sqlTable, DirectoryInfo directory, IExtractionConfiguration configuration, IDataLoadEventListener listener, out int linesWritten,
out string destinationDescription)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to download SQL for global SupportingSQL " + sqlTable.SQL));
using (var con = sqlTable.GetServer().GetConnection())
{
con.Open();
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Connection opened successfully, about to send SQL command " + sqlTable.SQL));
var cmd = DatabaseCommandHelper.GetCommand(sqlTable.SQL, con);
var da = DatabaseCommandHelper.GetDataAdapter(cmd);
var sw = new Stopwatch();
sw.Start();
DataTable dt = new DataTable();
da.Fill(dt);
dt.TableName = GetTableName(_destinationDatabase.Server.GetQuerySyntaxHelper().GetSensibleEntityNameFromString(sqlTable.Name));
linesWritten = dt.Rows.Count;
var destinationDb = GetDestinationDatabase(listener);
var tbl = destinationDb.ExpectTable(dt.TableName);
if (tbl.Exists())
{
tbl.Drop();
}
destinationDb.CreateTable(dt.TableName, dt);
destinationDescription = TargetDatabaseServer.ID + "|" + GetDatabaseName() + "|" + dt.TableName;
}
}
public static DataTable GetPeriodicityForDataTableForEvaluation(Evaluation evaluation, string pivotCategoryValue, bool pivot)
{
using (var con = evaluation.DQERepository.GetConnection())
{
string sql = "";
if (pivot)
{
sql = string.Format(PeriodicityPivotSql, evaluation.ID, pivotCategoryValue);
}
else
{
sql = @"Select [Evaluation_ID]
,CAST([Year] as varchar(4)) + '-' + datename(month,dateadd(month, [Month] - 1, 0)) as YearMonth
,[CountOfRecords]
,[RowEvaluation] from PeriodicityState where Evaluation_ID=" + evaluation.ID + " AND PivotCategory = '" + pivotCategoryValue + "'";
}
var cmd = DatabaseCommandHelper.GetCommand(sql, con.Connection, con.Transaction);
var da = DatabaseCommandHelper.GetDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
return(dt);
}
}
private void ExtractLookupTableSql(BundledLookupTable lookup, IDataLoadEventListener listener, DataLoadInfo dataLoadInfo)
{
try
{
var tempDestination = new DataTableUploadDestination();
var server = DataAccessPortal.GetInstance().ExpectServer(lookup.TableInfo, DataAccessContext.DataExport);
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to download SQL for lookup " + lookup.TableInfo.Name));
using (var con = server.GetConnection())
{
con.Open();
var sqlString = "SELECT * FROM " + lookup.TableInfo.Name;
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Connection opened successfully, about to send SQL command: " + sqlString));
var cmd = DatabaseCommandHelper.GetCommand(sqlString, con);
var da = DatabaseCommandHelper.GetDataAdapter(cmd);
var sw = new Stopwatch();
sw.Start();
DataTable dt = new DataTable();
da.Fill(dt);
dt.TableName = GetTableName(_destinationDatabase.Server.GetQuerySyntaxHelper().GetSensibleTableNameFromString(lookup.TableInfo.Name));
var tableLoadInfo = dataLoadInfo.CreateTableLoadInfo("", dt.TableName, new[] { new DataSource(sqlString, DateTime.Now) }, -1);
tableLoadInfo.Inserts = dt.Rows.Count;
listener.OnProgress(this, new ProgressEventArgs("Reading from Lookup " + lookup.TableInfo.Name, new ProgressMeasurement(dt.Rows.Count, ProgressType.Records), sw.Elapsed));
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Decided on the following destination table name for Lookup: " + dt.TableName));
tempDestination.AllowResizingColumnsAtUploadTime = true;
tempDestination.PreInitialize(GetDestinationDatabase(listener), listener);
tempDestination.ProcessPipelineData(dt, listener, new GracefulCancellationToken());
tempDestination.Dispose(listener, null);
//end auditing it
tableLoadInfo.CloseAndArchive();
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(), TargetDatabaseServer.ID + "|" + GetDatabaseName() + "|" + dt.TableName, dt.Rows.Count);
}
}
}
catch (Exception e)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Extraction of Lookup " + lookup.TableInfo.Name + " failed ", e));
throw;
}
}
private void MigrateExistingData(Func <string, bool> shouldApplySql, DbConnection con, ICheckNotifier notifier, DiscoveredTable tbl)
{
string from = _colToNuke.GetRuntimeName(LoadStage.PostLoad);
string to = _newANOColumnInfo.GetRuntimeName(LoadStage.PostLoad);
//create an empty table for the anonymised data
DbCommand cmdCreateTempMap = DatabaseCommandHelper.GetCommand(string.Format("SELECT top 0 {0},{1} into TempANOMap from {2}", from, to, tbl.GetFullyQualifiedName()), con);
if (!shouldApplySql(cmdCreateTempMap.CommandText))
{
throw new Exception("User decided not to create the TempANOMap table");
}
cmdCreateTempMap.ExecuteNonQuery();
try
{
//get the existing data
DbCommand cmdGetExistingData = DatabaseCommandHelper.GetCommand(string.Format("SELECT {0},{1} from {2}", from, to, tbl.GetFullyQualifiedName()), con);
DbDataAdapter da = DatabaseCommandHelper.GetDataAdapter(cmdGetExistingData);
DataTable dt = new DataTable();
da.Fill(dt);//into memory
//transform it in memory
ANOTransformer transformer = new ANOTransformer(_toConformTo, new FromCheckNotifierToDataLoadEventListener(notifier));
transformer.Transform(dt, dt.Columns[0], dt.Columns[1]);
var tempAnoMapTbl = tbl.Database.ExpectTable("TempANOMap");
using (var insert = tempAnoMapTbl.BeginBulkInsert())
{
insert.Upload(dt);
}
//create an empty table for the anonymised data
DbCommand cmdUpdateMainTable = DatabaseCommandHelper.GetCommand(string.Format("UPDATE source set source.{1} = map.{1} from {2} source join TempANOMap map on source.{0}=map.{0}", from, to, tbl.GetFullyQualifiedName()), con);
if (!shouldApplySql(cmdUpdateMainTable.CommandText))
{
throw new Exception("User decided not to perform update on table");
}
cmdUpdateMainTable.ExecuteNonQuery();
}
finally
{
//always drop the temp anomap
DbCommand dropMappingTable = DatabaseCommandHelper.GetCommand("DROP TABLE TempANOMap", con);
dropMappingTable.ExecuteNonQuery();
}
}
private DataTable GetLookupTableInfoContentsFromDatabase(TableInfo lookupTable)
{
//get the contents of the lookup
using (var con = DataAccessPortal.GetInstance().ExpectServer(lookupTable, DataAccessContext.InternalDataProcessing).GetConnection())
{
con.Open();
var cmd = DatabaseCommandHelper.GetCommand("Select * from " + lookupTable.Name, con);
var da = DatabaseCommandHelper.GetDataAdapter(cmd);
var dt = new System.Data.DataTable();
da.Fill(dt);
return(dt);
}
}
/// <summary>
/// Returns a table describing the number of records over time optionally only those where the pivot column in the rows
/// had the value of <paramref name="pivotCategoryValue"/>. Returns null if no rows were present in the table at the
/// time the <paramref name="evaluation"/> was run.
/// </summary>
/// <param name="evaluation"></param>
/// <param name="pivotCategoryValue"></param>
/// <param name="pivot"></param>
/// <returns></returns>
public static DataTable GetPeriodicityForDataTableForEvaluation(Evaluation evaluation, string pivotCategoryValue, bool pivot)
{
using (var con = evaluation.DQERepository.GetConnection())
{
string sql = "";
if (pivot)
{
sql = string.Format(PeriodicityPivotSql, evaluation.ID, pivotCategoryValue);
}
else
{
sql = @"Select [Evaluation_ID]
,CAST([Year] as varchar(4)) + '-' + datename(month,dateadd(month, [Month] - 1, 0)) as YearMonth
,[CountOfRecords]
,[RowEvaluation] from PeriodicityState where Evaluation_ID=" + evaluation.ID + " AND PivotCategory = '" + pivotCategoryValue + "'";
}
using (var cmd = DatabaseCommandHelper.GetCommand(sql, con.Connection, con.Transaction))
using (var da = DatabaseCommandHelper.GetDataAdapter(cmd))
{
DataTable dt = new DataTable();
da.Fill(dt);
// if there are no rows (table is empty) return null instead
if (dt.Columns.Count == 0 || dt.Rows.Count == 0)
{
return(null);
}
if (pivot)
{
dt.Columns["Correct"].SetOrdinal(3);
dt.Columns["Wrong"].SetOrdinal(4);
dt.Columns["Missing"].SetOrdinal(5);
dt.Columns["InvalidatesRow"].SetOrdinal(6);
}
return(dt);
}
}
}
public DataTable TryGetPreview()
{
DataTable chunk = new DataTable();
using (var con = DatabaseCommandHelper.GetConnection(_builder))
{
con.Open();
var da = DatabaseCommandHelper.GetDataAdapter(DatabaseCommandHelper.GetCommand(_sql, con));
int read = da.Fill(0, 100, chunk);
if (read == 0)
{
return(null);
}
return(chunk);
}
}
private ExtractCommandState ExtractSupportingSql(SupportingSQLTable sql, IDataLoadEventListener listener, DataLoadInfo dataLoadInfo)
{
try
{
var tempDestination = new DataTableUploadDestination();
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "About to download SQL for global SupportingSQL " + sql.Name));
using (var con = sql.GetServer().GetConnection())
{
con.Open();
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Connection opened successfully, about to send SQL command " + sql.SQL));
var cmd = DatabaseCommandHelper.GetCommand(sql.SQL, con);
var da = DatabaseCommandHelper.GetDataAdapter(cmd);
var sw = new Stopwatch();
sw.Start();
DataTable dt = new DataTable();
da.Fill(dt);
dt.TableName = GetTableName(_destinationDatabase.Server.GetQuerySyntaxHelper().GetSensibleTableNameFromString(sql.Name));
var tableLoadInfo = dataLoadInfo.CreateTableLoadInfo("", dt.TableName, new[] { new DataSource(sql.SQL, DateTime.Now) }, -1);
tableLoadInfo.Inserts = dt.Rows.Count;
listener.OnProgress(this, new ProgressEventArgs("Reading from SupportingSQL " + sql.Name, new ProgressMeasurement(dt.Rows.Count, ProgressType.Records), sw.Elapsed));
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Information, "Decided on the following destination table name for SupportingSQL: " + dt.TableName));
tempDestination.AllowResizingColumnsAtUploadTime = true;
tempDestination.PreInitialize(GetDestinationDatabase(listener), listener);
tempDestination.ProcessPipelineData(dt, listener, new GracefulCancellationToken());
tempDestination.Dispose(listener, null);
//end auditing it
tableLoadInfo.CloseAndArchive();
if (_request is ExtractDatasetCommand)
{
var result = (_request as ExtractDatasetCommand).CumulativeExtractionResults;
var supplementalResult = result.AddSupplementalExtractionResult(sql.SQL, sql);
supplementalResult.CompleteAudit(this.GetType(), TargetDatabaseServer.ID + "|" + GetDatabaseName() + "|" + dt.TableName, dt.Rows.Count);
}
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(), TargetDatabaseServer.ID + "|" + GetDatabaseName() + "|" + dt.TableName, dt.Rows.Count);
extractGlobalsCommand.ExtractionResults.Add(result);
}
}
}
catch (Exception e)
{
listener.OnNotify(this, new NotifyEventArgs(ProgressEventType.Error, "Extraction of SupportingSQL " + sql + " failed ", e));
return(ExtractCommandState.Crashed);
}
return(ExtractCommandState.Completed);
}
