633 lines
23 KiB
C#
633 lines
23 KiB
C#
using System;
|
|
using System.Collections.Generic;
|
|
using System.ComponentModel;
|
|
using System.ComponentModel.Composition;
|
|
using System.Data;
|
|
using System.Data.SqlClient;
|
|
using System.Text;
|
|
using System.Threading.Tasks;
|
|
using System.Windows.Media;
|
|
using DTS.Common.Events;
|
|
using DTS.Common.Events.Database;
|
|
using Prism.Events;
|
|
using Prism.Regions;
|
|
using Unity;
|
|
using DTS.Common.Interactivity;
|
|
using Prism.Commands;
|
|
using DTS.Common.Interface.Database;
|
|
using DTS.Common.Storage;
|
|
using DTS.Common.Utilities.Logging;
|
|
using DTS.Common.Interface;
|
|
|
|
// ReSharper disable CheckNamespace
|
|
// ReSharper disable MemberCanBePrivate.Global
|
|
// ReSharper disable InconsistentNaming
|
|
|
|
namespace DatabaseServices
|
|
{
|
|
/// <summary>
|
|
/// this class handles DatabaseCopy functionality
|
|
/// </summary>
|
|
[PartCreationPolicy(CreationPolicy.Shared)]
|
|
public class DatabaseCopyViewModel : IDatabaseCopyViewModel
|
|
{
|
|
/// <summary>
|
|
/// The DatabaseCopy view
|
|
/// </summary>
|
|
public IDatabaseCopyView View { get; set; }
|
|
|
|
private IEventAggregator _eventAggregator { get; }
|
|
private IUnityContainer UnityContainer { get; }
|
|
|
|
public InteractionRequest<Notification> NotificationRequest { get; }
|
|
public InteractionRequest<Confirmation> ConfirmationRequest { get; }
|
|
|
|
/// <inheritdoc />
|
|
/// <summary>
|
|
/// Occurs when a property value changes.
|
|
/// </summary>
|
|
public event PropertyChangedEventHandler PropertyChanged;
|
|
|
|
public void OnPropertyChanged(string propertyName)
|
|
{
|
|
PropertyChanged?.Invoke(this, new PropertyChangedEventArgs(propertyName));
|
|
}
|
|
|
|
#region constructors and initializers
|
|
|
|
/// <summary>
|
|
/// Creates a new instance of the DatabaseCopyViewModel
|
|
/// </summary>
|
|
/// <param name="view"></param>
|
|
/// <param name="regionManager">The logical placeholder defined within the application's UI (in the shell or within views) into which views are displayed.</param>
|
|
/// <param name="eventAggregator">The EventAggregator which allows different components to publish/subscribe to events without being coupled to each other.</param>
|
|
/// <param name="unityContainer">The unityContainer.</param>
|
|
public DatabaseCopyViewModel(IDatabaseCopyView view, IRegionManager regionManager,
|
|
IEventAggregator eventAggregator, IUnityContainer unityContainer)
|
|
{
|
|
View = view;
|
|
View.DataContext = this;
|
|
|
|
NotificationRequest = new InteractionRequest<Notification>();
|
|
ConfirmationRequest = new InteractionRequest<Confirmation>();
|
|
|
|
_eventAggregator = eventAggregator;
|
|
UnityContainer = unityContainer;
|
|
_eventAggregator.GetEvent<RaiseNotification>().Subscribe(OnRaiseNotification);
|
|
_eventAggregator.GetEvent<BusyIndicatorChangeNotification>().Subscribe(OnBusyIndicatorNotification);
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Methods
|
|
|
|
public void Unset()
|
|
{
|
|
}
|
|
|
|
public void CopyDatabase()
|
|
{
|
|
_eventAggregator.GetEvent<AppStatusEvent>().Publish(AppStatusArg.Busy);
|
|
_ = Task.Run(() => { CopyFunc(); });
|
|
}
|
|
|
|
public void InitializeState(DTS.Common.Enums.Database.DbType dbType, string dbName)
|
|
{
|
|
DbName = dbName;
|
|
DatabaseType = dbType;
|
|
_eventAggregator.GetEvent<ProgressBarEvent>().Publish(new ProgressBarEventArg(CurrentTaskBar, Colors.White,
|
|
System.Windows.Visibility.Collapsed, 0D, ""));
|
|
_eventAggregator.GetEvent<ProgressBarEvent>().Publish(new ProgressBarEventArg(OverallTaskBar, Colors.White,
|
|
System.Windows.Visibility.Collapsed, 0D, ""));
|
|
OnPropertyChanged("CopyEnabled");
|
|
}
|
|
|
|
private void SetStatus(string bar, double percentage, string text)
|
|
{
|
|
_eventAggregator.GetEvent<ProgressBarEvent>()
|
|
.Publish(new ProgressBarEventArg(bar, Colors.White, System.Windows.Visibility.Visible, percentage, text));
|
|
}
|
|
private static readonly List<string> _allDBTables = new List<string>()
|
|
{
|
|
"AddOrRemove",
|
|
"AnalogDiagnostics",
|
|
"CalculatedChannelOperation",
|
|
"CalculatedChannels",
|
|
"CalibrationType",
|
|
"ChannelCodes",
|
|
"ChannelCodeType",
|
|
"Channels",
|
|
"ChannelSettings",
|
|
"CustomerDetails",
|
|
"DAS",
|
|
"DASChannels",
|
|
"DataPRODbVersion",
|
|
"DbTableVersions",
|
|
"DefaultProperties",
|
|
"DiagnosticRuns",
|
|
"GroupChannelSettings",
|
|
"GroupHardware",
|
|
"Groups",
|
|
"LabratoryDetails",
|
|
"LastUsedHardware",
|
|
"LevelTriggers",
|
|
"LockedItemCategories",
|
|
"LockedItems",
|
|
"RecordingMode",
|
|
"RecordingModes",
|
|
"ROIPeriodChannels",
|
|
"SensorBridgeLegMode",
|
|
"SensorBridgeType",
|
|
"SensorCalibrationRecord",
|
|
"SensorCalibrationRecordIRTracc",
|
|
"SensorCalibrationRecordPolynomial",
|
|
"SensorCalibrations",
|
|
"SensorChangeHistory",
|
|
"SensorChangeType",
|
|
"SensorCouplingMode",
|
|
"SensorDigitalOutputMode",
|
|
"SensorModels",
|
|
"Sensors",
|
|
"SensorsAnalog",
|
|
"SensorsDigitalIn",
|
|
"SensorsDigitalOut",
|
|
"SensorSettingMode",
|
|
"SensorShunt",
|
|
"SensorSquibFireMode",
|
|
"SensorSquibMeasurementType",
|
|
"SensorsSquib",
|
|
"SensorsStreamInput",
|
|
"SensorsStreamOutput",
|
|
"SensorStatus",
|
|
"SensorsType",
|
|
"SensorsUART",
|
|
"SensorTestHistory",
|
|
"Settings",
|
|
"SoftwareFilters",
|
|
"StoredProcedureVersions",
|
|
"TagAssignments",
|
|
"TagObjectType",
|
|
"Tags",
|
|
"tblDataPRODbVersion",
|
|
"TestEngineerDetails",
|
|
"TestGraphs",
|
|
"TestHistory",
|
|
"TestSetupGroups",
|
|
"TestSetupHardware",
|
|
"TestSetupObjectMetaData",
|
|
"TestSetupROIs",
|
|
"TestSetups",
|
|
"TestSetupSettings",
|
|
"UIItems",
|
|
"UIItemSettings",
|
|
"UserProperties",
|
|
"Users",
|
|
"UsersRoles"
|
|
};
|
|
private static readonly HashSet<string> _tablesWithIdentities = new HashSet<string>()
|
|
{
|
|
"AnalogDiagnostics",
|
|
"CalculatedChannels",
|
|
"ChannelCodes",
|
|
"ChannelCodeType",
|
|
"Channels",
|
|
"ChannelSettings",
|
|
"CustomerDetails",
|
|
"DAS",
|
|
"DASChannels",
|
|
"DiagnosticRuns",
|
|
"GroupHardware",
|
|
"Groups",
|
|
"LabratoryDetails",
|
|
"LastUsedHardware",
|
|
"LevelTriggers",
|
|
"LockedItemCategories",
|
|
"LockedItems",
|
|
"ROIPeriodChannels",
|
|
"SensorCalibrationRecord",
|
|
"SensorCalibrationRecordIRTracc",
|
|
"SensorCalibrationRecordPolynomial",
|
|
"SensorCalibrations",
|
|
"SensorChangeHistory",
|
|
"SensorChangeType",
|
|
"SensorModels",
|
|
"Sensors",
|
|
"SensorsAnalog",
|
|
"SensorsDigitalIn",
|
|
"SensorsDigitalOut",
|
|
"SensorsSquib",
|
|
"SensorsStreamInput",
|
|
"SensorsStreamOutput",
|
|
"SensorsUART",
|
|
"SensorTestHistory",
|
|
"Settings",
|
|
"SoftwareFilters",
|
|
"Tags",
|
|
"TestEngineerDetails",
|
|
"TestGraphs",
|
|
"TestHistory",
|
|
"TestSetupGroups",
|
|
"TestSetupHardware",
|
|
"TestSetupObjectMetaData",
|
|
"TestSetupROIs",
|
|
"TestSetups",
|
|
"UIItems",
|
|
"UIItemSettings",
|
|
"Users"
|
|
};
|
|
private void CopyFunc()
|
|
{
|
|
try
|
|
{
|
|
DTS.Common.Storage.DatabaseServices.BackupLocalDatabase(DbName);
|
|
}
|
|
catch (System.IO.FileNotFoundException ex)
|
|
{
|
|
_eventAggregator.GetEvent<DbStatusEvent>()
|
|
.Publish(new DbStatusArg(DbStatusArg.EventTypes.FailedToBackupLocalFileNotFound, ex));
|
|
APILogger.Log(ex);
|
|
return;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
_eventAggregator.GetEvent<DbStatusEvent>()
|
|
.Publish(new DbStatusArg(DbStatusArg.EventTypes.FailedToBackupLocal, null));
|
|
APILogger.Log(ex);
|
|
return;
|
|
}
|
|
try
|
|
{
|
|
//get list of all tables
|
|
//I'd like to do this through SQL, but ran into problems
|
|
var allTables = _allDBTables;
|
|
|
|
SetStatus(OverallTaskBar, 0, "");
|
|
SetStatus(CurrentTaskBar, 0, "");
|
|
double steps = 2 + allTables.Count;
|
|
//turn off all constraints
|
|
SetStatus(OverallTaskBar, 0, Resources.StringResources.ClearingLocalDb);
|
|
using (var cmd = LocalOnlyOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
cmd.CommandText = "EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'";
|
|
cmd.CommandType = CommandType.Text;
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
finally
|
|
{
|
|
cmd.Connection.Dispose();
|
|
}
|
|
}
|
|
double currentStep = 1;
|
|
SetStatus(OverallTaskBar, 100D * currentStep / steps, "");
|
|
|
|
//Copy remote table to local)
|
|
foreach (var table in allTables)
|
|
{
|
|
SetStatus(OverallTaskBar, 100D * currentStep / steps,
|
|
$"{Resources.StringResources.CopyingTable} {table}");
|
|
SetStatus(CurrentTaskBar, 0D, "");
|
|
CopyRemoteTable(table, _tablesWithIdentities.Contains(table));
|
|
currentStep++;
|
|
}
|
|
|
|
//Change any DASId columns in the Channels table from 0 to NULL
|
|
try
|
|
{
|
|
using (var cmd = LocalOnlyOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
cmd.CommandText = "UPDATE Channels SET DASId = NULL WHERE DASId = 0";
|
|
cmd.CommandType = CommandType.Text;
|
|
cmd.ExecuteNonQuery();
|
|
}
|
|
finally { cmd.Connection.Dispose(); }
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
APILogger.Log(ex);
|
|
}
|
|
|
|
//turn on all constraints
|
|
SetStatus(OverallTaskBar, 100D * currentStep / steps, Resources.StringResources.EnablingConstraints);
|
|
try
|
|
{
|
|
using (var cmd = LocalOnlyOperations.GetSQLCommand(true))
|
|
{
|
|
try
|
|
{
|
|
cmd.CommandText = "EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all'";
|
|
cmd.CommandType = CommandType.Text;
|
|
cmd.ExecuteNonQuery();
|
|
_eventAggregator.GetEvent<DbStatusEvent>().Publish(new DbStatusArg(DbStatusArg.EventTypes.Complete, null));
|
|
}
|
|
finally
|
|
{
|
|
cmd.Connection.Dispose();
|
|
}
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
APILogger.Log(ex);
|
|
}
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
APILogger.Log(ex);
|
|
_eventAggregator.GetEvent<DbStatusEvent>()
|
|
.Publish(new DbStatusArg(DbStatusArg.EventTypes.FailedToCopy, ex));
|
|
try
|
|
{
|
|
DTS.Common.Storage.DatabaseServices.RestoreLocalDatabase(DbName);
|
|
}
|
|
catch (Exception)
|
|
{
|
|
APILogger.Log(ex);
|
|
_eventAggregator.GetEvent<DbStatusEvent>().Publish(
|
|
new DbStatusArg(DbStatusArg.EventTypes.FailedToRestoreLocal, null));
|
|
}
|
|
}
|
|
finally
|
|
{
|
|
_eventAggregator.GetEvent<ProgressBarEvent>().Publish(new ProgressBarEventArg(OverallTaskBar,
|
|
Colors.White, System.Windows.Visibility.Collapsed, 0D, ""));
|
|
_eventAggregator.GetEvent<ProgressBarEvent>().Publish(new ProgressBarEventArg(CurrentTaskBar,
|
|
Colors.White, System.Windows.Visibility.Collapsed, 0D, ""));
|
|
_eventAggregator.GetEvent<AppStatusEvent>().Publish(AppStatusArg.Available);
|
|
}
|
|
}
|
|
|
|
private void CopyRemoteTable(string tableName, bool bIndentityTable)
|
|
{
|
|
using (var cmd = LocalOnlyOperations.GetSQLCommand(true))
|
|
{
|
|
cmd.CommandText = $"DELETE FROM [dbo].[{tableName}]";
|
|
cmd.CommandType = CommandType.Text;
|
|
cmd.ExecuteNonQuery();
|
|
cmd.Connection.Dispose();
|
|
}
|
|
|
|
DataColumnCollection columns = null;
|
|
var objects = new List<object[]>();
|
|
using (var cmd = DbOperations.GetSQLCommand(true))
|
|
{
|
|
cmd.CommandText = $"SELECT * from [dbo].[{tableName}]";
|
|
using (var ds = DbOperations.Connection.QueryDataSet(cmd))
|
|
{
|
|
if (ds.Tables[0].Rows.Count > 0)
|
|
{
|
|
columns = ds.Tables[0].Columns;
|
|
foreach (DataRow row in ds.Tables[0].Rows)
|
|
{
|
|
var rowObjects = new object[columns.Count];
|
|
for (var i = 0; i < rowObjects.Length; i++)
|
|
{
|
|
rowObjects[i] = row[i];
|
|
}
|
|
objects.Add(rowObjects);
|
|
}
|
|
}
|
|
}
|
|
cmd.Connection.Dispose();
|
|
}
|
|
if (null != columns)
|
|
{
|
|
InsertIntoLocalTable(tableName, columns, objects, bIndentityTable);
|
|
}
|
|
}
|
|
|
|
private void InsertIntoLocalTable(string tableName, DataColumnCollection columns, List<object[]> data, bool bIdentity)
|
|
{
|
|
SqlCommand cmd = null;
|
|
var index = 0;
|
|
StringBuilder sb = null;
|
|
var countSoFar = 0;
|
|
foreach (var row in data)
|
|
{
|
|
if (null == cmd)
|
|
{
|
|
index = 0;
|
|
cmd = LocalOnlyOperations.GetSQLCommand(true);
|
|
sb = new StringBuilder(500);
|
|
sb.Append(LocalOnlyOperations.BeginStatement);
|
|
if (bIdentity)
|
|
{
|
|
sb.Append($"SET IDENTITY_INSERT [dbo].[{tableName}] ON;");
|
|
}
|
|
}
|
|
|
|
sb.Append($"INSERT INTO [dbo].[{tableName}] (");
|
|
for (var i = 0; i < columns.Count; i++)
|
|
{
|
|
if (i > 0)
|
|
{
|
|
sb.Append(",");
|
|
}
|
|
sb.AppendFormat("[{0}]", columns[i].ColumnName);
|
|
}
|
|
sb.Append(") VALUES (");
|
|
|
|
for (var i = 0; i < columns.Count; i++)
|
|
{
|
|
if (i > 0)
|
|
{
|
|
sb.Append(",");
|
|
}
|
|
var key = $"@{index}_{i}";
|
|
sb.Append(key);
|
|
var dataType = TypeConvertor.ToSqlDbType(columns[i].DataType);
|
|
LocalOnlyOperations.CreateParam(cmd, key, dataType, row[i]);
|
|
}
|
|
sb.Append(");");
|
|
|
|
index++;
|
|
//18744 To avoid "The incoming request has too many parameters. The server supports a
|
|
//maximum of 2100 parameters. Reduce the number of parameters and resend the request."
|
|
//ensure that the number of parameters will not exceed 2100 if another row is added to sb.
|
|
if ((index * columns.Count >= (2100 - columns.Count)) ||
|
|
(MAX_BATCH_SIZE == index))
|
|
{
|
|
countSoFar += index;
|
|
if (bIdentity)
|
|
{
|
|
sb.Append($"SET IDENTITY_INSERT [dbo].[{tableName}] OFF;");
|
|
}
|
|
sb.Append(LocalOnlyOperations.CommitStatement);
|
|
cmd.CommandText = sb.ToString();
|
|
cmd.CommandType = CommandType.Text;
|
|
cmd.ExecuteNonQuery();
|
|
cmd.Connection.Dispose();
|
|
cmd.Dispose();
|
|
cmd = null;
|
|
SetStatus(CurrentTaskBar, 100D * countSoFar / data.Count, $"{countSoFar}/{data.Count}");
|
|
}
|
|
}
|
|
if (null != cmd)
|
|
{
|
|
if (bIdentity)
|
|
{
|
|
sb.Append($"SET IDENTITY_INSERT [dbo].[{tableName}] OFF;");
|
|
}
|
|
sb.Append(LocalOnlyOperations.CommitStatement);
|
|
cmd.CommandText = sb.ToString();
|
|
cmd.CommandType = CommandType.Text;
|
|
cmd.ExecuteNonQuery();
|
|
cmd.Connection.Dispose();
|
|
cmd.Dispose();
|
|
cmd = null;
|
|
SetStatus(CurrentTaskBar, 100D, "");
|
|
}
|
|
}
|
|
|
|
public void Cleanup()
|
|
{
|
|
}
|
|
|
|
public Task CleanupAsync()
|
|
{
|
|
return Task.CompletedTask;
|
|
}
|
|
|
|
public void Initialize()
|
|
{
|
|
}
|
|
|
|
public void Initialize(object parameter)
|
|
{
|
|
}
|
|
|
|
public void Initialize(object parameter, object model)
|
|
{
|
|
}
|
|
|
|
public Task InitializeAsync()
|
|
{
|
|
//this is existing code that was here, I was worried if I did make it async it might have issues
|
|
//so I'm just correcting a warning by not returning null at the end
|
|
try
|
|
{
|
|
var view = UnityContainer.Resolve<IStatusAndProgressBarView>();
|
|
var viewModel = UnityContainer.Resolve<IStatusAndProgressBarViewModel>();
|
|
viewModel.ProgressBarName = OverallTaskBar;
|
|
view.DataContext = viewModel;
|
|
viewModel.Initialize(this);
|
|
OverallProgressBarView = view;
|
|
|
|
view = UnityContainer.Resolve<IStatusAndProgressBarView>();
|
|
viewModel = UnityContainer.Resolve<IStatusAndProgressBarViewModel>();
|
|
view.DataContext = viewModel;
|
|
viewModel.ProgressBarName = CurrentTaskBar;
|
|
viewModel.Initialize(this);
|
|
CurrentTaskProgressBarView = view;
|
|
}
|
|
catch (Exception ex)
|
|
{
|
|
APILogger.Log(ex);
|
|
}
|
|
return Task.CompletedTask;
|
|
}
|
|
|
|
public Task InitializeAsync(object parameter)
|
|
{
|
|
return Task.CompletedTask;
|
|
}
|
|
|
|
public void Activated()
|
|
{
|
|
}
|
|
|
|
/// <summary>
|
|
/// Private Event handler for RaiseNotification event.
|
|
/// </summary>
|
|
private void OnBusyIndicatorNotification(bool eventArg)
|
|
{
|
|
IsBusy = eventArg;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Private Event handler for RaiseNotification event.
|
|
/// </summary>
|
|
private void OnRaiseNotification(NotificationContentEventArgs eventArgsWithTitle)
|
|
{
|
|
// The NotificationRequest.Raise triggers the Invoke() method of the PopupWindowAction object to show the NotificationWindow window
|
|
// Notification object expects a NotificationContentEventArgsWithoutTitle object and a Title string.
|
|
var eventArgsWithoutTitle = new NotificationContentEventArgs(eventArgsWithTitle.Message, "",
|
|
eventArgsWithTitle.Image, string.Empty);
|
|
|
|
NotificationRequest.Raise(new Notification
|
|
{
|
|
Content = eventArgsWithoutTitle,
|
|
Title = eventArgsWithTitle.Title
|
|
});
|
|
}
|
|
|
|
#endregion
|
|
|
|
#region Properties
|
|
public DTS.Common.Enums.Database.DbType DatabaseType { get; private set; }
|
|
|
|
public bool CopyEnabled => DatabaseType == DTS.Common.Enums.Database.DbType.RemoteLocalHybrid &&
|
|
DbOperations._usingCentralizedDB;
|
|
|
|
public bool IsDirty { get; private set; }
|
|
private bool _isBusy = false;
|
|
public bool IsBusy
|
|
{
|
|
get => _isBusy;
|
|
set
|
|
{
|
|
_isBusy = value;
|
|
OnPropertyChanged("IsBusy");
|
|
}
|
|
}
|
|
|
|
private bool _isMenuIncluded;
|
|
public bool IsMenuIncluded
|
|
{
|
|
get => _isMenuIncluded;
|
|
set
|
|
{
|
|
_isMenuIncluded = value;
|
|
OnPropertyChanged("IsMenuIncluded");
|
|
}
|
|
}
|
|
|
|
private bool _isNavigationIncluded;
|
|
public bool IsNavigationIncluded
|
|
{
|
|
get => _isNavigationIncluded;
|
|
set
|
|
{
|
|
_isNavigationIncluded = value;
|
|
OnPropertyChanged("IsNavigationIncluded");
|
|
}
|
|
}
|
|
public IStatusAndProgressBarView OverallProgressBarView { get; private set; }
|
|
public IStatusAndProgressBarView CurrentTaskProgressBarView { get; private set; }
|
|
private const string CurrentTaskBar = "CurrentTaskStatus";
|
|
private const string OverallTaskBar = "OverallStatus";
|
|
/// <summary>
|
|
/// the size of each batch of inserts
|
|
/// I played around with this for efficiency, but it seemed smaller sizes in general did better
|
|
/// the max size would be 2100 parameters (so column count*rows must be less than 2100 )
|
|
/// </summary>
|
|
private const int MAX_BATCH_SIZE = 20;
|
|
public string DbName { get; private set; }
|
|
private bool _isCopyVisible = true;
|
|
public bool IsCopyVisible
|
|
{
|
|
get => _isCopyVisible;
|
|
set { _isCopyVisible = value; OnPropertyChanged("IsCopyVisible"); }
|
|
}
|
|
#endregion Properties
|
|
|
|
#region Commands
|
|
|
|
#endregion
|
|
}
|
|
}
|