private void OnRestoreButtonClicked(object sender, RoutedEventArgs e)
{
using (var conn = new SqlConnection(App.ConnectionString))
{
conn.Open();
if (App.GetTableNames(conn).Contains(SheetListBox.SelectedItem.ToString()))
{
using (var comm = new SqlCommand($"DROP TABLE [{Settings.Default.Schema}].[{SheetListBox.SelectedItem}]", conn))
{
comm.ExecuteNonQuery();
}
}
using (var comm = new SqlCommand())
{
comm.Connection = conn;
//Debug.WriteLine(Path.GetFileNameWithoutExtension(file));
comm.CommandText = $"CREATE TABLE [{Settings.Default.Schema}].[{SheetListBox.SelectedItem}] ( ";
var j = 0;
List <string> columns = App.GetAllColumnsOfTable(conn, $"{Settings.Default.Schema}_BACKUPS", SheetListBox.SelectedItem.ToString());
foreach (string column in columns)
{
if (j != columns.Count - 1)
{
comm.CommandText += $"[{column}] NVARCHAR(MAX), ";
}
else
{
comm.CommandText += $"[{column}] NVARCHAR(MAX)";
}
j++;
}
comm.CommandText += " )";
comm.ExecuteNonQuery();
}
using (var comm =
new SqlCommand(
$"INSERT INTO [{Settings.Default.Schema}].[{SheetListBox.SelectedItem}] SELECT * FROM [{Settings.Default.Schema}_BACKUPS].[{SheetListBox.SelectedItem}]",
conn))
{
comm.ExecuteNonQuery();
}
conn.Close();
}
SheetListBox.SelectedItem = null;
}
public void PopulateListBox()
{
using (var conn = new SqlConnection(App.ConnectionString))
{
conn.Open();
foreach (string tableName in App.GetTableNames(conn, $"{Settings.Default.Schema}_BACKUPS"))
{
SheetListBox.Items.Add(tableName);
}
conn.Close();
}
}
private void FillComboTable()
{
ComboTable.RowChanged += (sender, args) =>
{
SaveButton.IsEnabled = true;
//Debug.WriteLine("asdf");
//if(args.Action == DataRowAction.Change)
//_comboTable.AcceptChanges();
};
ComboTable.RowDeleted += (sender, args) =>
{
SaveButton.IsEnabled = true;
ComboTable.AcceptChanges();
};
using (var conn =
new SqlConnection(App.ConnectionString))
{
conn.Open();
if (!App.GetTableNames(conn, $"{Settings.Default.Schema}_COMBOBOXES").Contains(_prefabName))
{
return;
}
using (var cmd = new SqlCommand($"SELECT * FROM [{Settings.Default.Schema}_COMBOBOXES].[{_prefabName}]",
conn))
{
using (var sda = new SqlDataAdapter(cmd))
{
sda.Fill(ComboTable);
}
}
conn.Close();
}
ComboGrid.ItemsSource = ComboTable;
int j = 0;
ComboGrid.Loaded += (sender, args) =>
{
if (j == 0)
{
GenerateColumns();
}
j++;
};
}
private void DoneButton_OnClick(object sender, RoutedEventArgs e)
{
using (var conn = new SqlConnection(App.ConnectionString))
{
conn.Open();
if (App.GetTableNames(conn, $"{Settings.Default.Schema}_PREFABS").Contains(PrefabNameBox.Text))
{
MessageBox.Show("Identical Prefab exists. Please rename.", "Warning", MessageBoxButton.OK,
MessageBoxImage.Warning);
}
DataTable prefabTable = new DataTable();
prefabTable.Columns.Add("COLUMNS");
prefabTable.Columns.Add("TYPES");
prefabTable.Columns.Add("SORTBYS");
prefabTable.Columns.Add("GROUPS");
foreach (DataColumn column in MainWindow.MasterDataSet.Tables[TableList.SelectedItem.ToString()].Columns)
{
DataRow dr = prefabTable.NewRow();
dr[0] = column.ColumnName;
dr[1] = "TextField";
prefabTable.Rows.Add(dr);
}
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
using (var comm =
new SqlCommand(
$"CREATE TABLE [{Settings.Default.Schema}_PREFABS].[{PrefabNameBox.Text}] (COLUMNS NVARCHAR(MAX), TYPES NVARCHAR(MAX), SORTBYS NVARCHAR(MAX), GROUPS NVARCHAR(MAX))",
conn))
{
comm.ExecuteNonQuery();
}
var bulkCopy =
new SqlBulkCopy(conn)
{
DestinationTableName =
$"[{Settings.Default.Schema}_PREFABS].[{PrefabNameBox.Text}]"
};
bulkCopy.WriteToServer(prefabTable);
conn.Close();
}
_mgr.PopulateListBox();
Close();
}
public static void FillRecycledDataTable()
{
using (var conn =
new SqlConnection(App.ConnectionString))
{
conn.Open();
if (!App.GetTableNames(conn, "RECYCLED").Contains(Settings.Default.Schema))
{
using (var comm = new SqlCommand())
{
comm.Connection = conn;
comm.CommandText = $"CREATE TABLE RECYCLED.[{Settings.Default.Schema}] ( ";
for (var i = 0; i < 250; i++)
{
if (i != 249)
{
comm.CommandText += $"Column{i} NVARCHAR(MAX),";
}
else
{
comm.CommandText += $"Column{i} NVARCHAR(MAX)";
}
}
comm.CommandText += " )";
comm.ExecuteNonQuery();
}
}
using (var cmd = new SqlCommand($"SELECT * FROM [RECYCLED].[{Settings.Default.Schema}]", conn))
{
cmd.CommandType = CommandType.Text;
using (var sda = new SqlDataAdapter(cmd))
{
RecycledDataTable.TableName = Settings.Default.Schema;
sda.Fill(RecycledDataTable);
}
}
conn.Close();
}
RecycledDataTable.RowChanged += (sender, args) =>
{
if (args.Action == DataRowAction.Change || args.Action == DataRowAction.Add || args.Action == DataRowAction.Delete)
{
RecycledDataTable.AcceptChanges();
}
};
RecycledDataTable.RowDeleted += (sender, args) =>
{
if (args.Action == DataRowAction.Delete)
{
RecycledDataTable.AcceptChanges();
}
};
var autoSaveTimer = new Timer(6000)
{
Enabled = true
};
autoSaveTimer.Elapsed += (sender, args) => Task.Run(() => SaveRecycledTable());
}
public void InitializeOrRefreshEverything(int tabIndex)
{
System.Windows.Forms.Cursor.Current = Cursors.WaitCursor;
CurrentVisualStyle = Settings.Default.Theme;
App.ThisIsNowConcurrent = false;
App.ThisMadeLastChange = false;
ResetGroupsBox.IsChecked = false;
DeleteModeCheckBox.IsChecked = false;
SearchField.Clear();
SearchField.AutoCompleteSource = SearchBoxAutoCompleteItems;
MasterTabControl.Items.Clear();
var dataSets = App.MainSet(this);
MasterDataSet = dataSets;
MasterDataGrids.Clear();
EditedCells.Clear();
ProgressGrid.Visibility = Visibility.Hidden;
using (var conn =
new SqlConnection(App.ConnectionString))
{
conn.Open();
foreach (string s in App.GetTableNames(conn))
{
var tab = new TabItemExt
{
Header = s
};
MasterTabControl.Items.Add(tab);
}
int i = 0;
foreach (DataTable dt in MasterDataSet.Tables)
{
WriteToDataGrid(dt, App.GetPrefabOfDataTable(conn, dt), (TabItem)MasterTabControl.Items[i]);
i++;
}
conn.Close();
}
if (MasterTabControl.Items.Count > 0 && tabIndex < MasterTabControl.Items.Count)
{
MasterTabControl.SelectedIndex = tabIndex;
}
else if (tabIndex >= MasterTabControl.Items.Count)
{
MasterTabControl.SelectedIndex = MasterTabControl.Items.Count - 1;
}
else
{
MasterTabControl.SelectedIndex = 0;
}
RefreshRevertTables();
if (MasterTabControl.Items.Count < 1)
{
SearchField.IsEnabled = false;
ResetSorts.IsEnabled = false;
ResetGroupsBox.IsEnabled = false;
DeleteModeCheckBox.IsEnabled = false;
//RevertChanges.IsEnabled = false;
NewRowButton.IsEnabled = false;
RefreshAll.IsEnabled = false;
BarcodeTextBox.IsEnabled = false;
BarcodeInventoryCommit.IsEnabled = false;
}
else
{
SearchField.IsEnabled = true;
ResetSorts.IsEnabled = true;
ResetSorts.IsChecked = true;
ResetGroupsBox.IsEnabled = true;
DeleteModeCheckBox.IsEnabled = true;
RefreshAll.IsEnabled = true;
BarcodeTextBox.IsEnabled = true;
BarcodeInventoryCommit.IsEnabled = true;
NewRowButton.IsEnabled = true;
}
Recycled.FillRecycledDataTable();
System.Windows.Forms.Cursor.Current = Cursors.Default;
}
public EditTable(bool showWarning, string originalName, bool showPrefabs, bool isCreatingSchema)
{
InitializeComponent();
//SkinStorage.SetVisualStyle(this, "Metro");
CloseWindow.InputGestures.Add(new KeyGesture(Key.Escape));
CommandBindings.Add(new CommandBinding(CloseWindow, CloseCurrentWindow));
using (var conn = new SqlConnection(App.ConnectionString))
{
conn.Open();
foreach (string tableName in App.GetTableNames(conn, $"{Settings.Default.Schema}_PREFABS"))
{
PrefabComboBox.Items.Add(tableName);
}
conn.Close();
}
if (PrefabComboBox.Items.Count <= 0)
{
MessageBox.Show("Please create a Prefab first.", "Cannot create table", MessageBoxButton.OK,
MessageBoxImage.Warning);
Close();
return;
}
_showWarning = showWarning;
_originalName = originalName;
_showPrefabs = showPrefabs;
_isCreatingSchema = isCreatingSchema;
TableNameBox.TextChanged += (sender, args) =>
{
if (showPrefabs)
{
ChooseButton.IsEnabled =
!string.IsNullOrEmpty(TableNameBox.Text) && PrefabComboBox.SelectedIndex >= 0;
}
else
{
ChooseButton.IsEnabled = !string.IsNullOrEmpty(TableNameBox.Text);
}
};
if (showPrefabs)
{
PrefabComboBox.SelectionChanged += (sender, args) =>
{
ChooseButton.IsEnabled =
!string.IsNullOrEmpty(TableNameBox.Text) && PrefabComboBox.SelectedIndex >= 0;
};
PrefabComboBox.SelectedValue = PrefabComboBox.Items[0];
}
else
{
PrefabComboBox.Visibility = Visibility.Hidden;
PrefabComboBox.IsDropDownOpen = false;
TableNameBox.Focus();
}
if (string.IsNullOrEmpty(originalName))
{
return;
}
TableNameBox.Focus();
TableNameBox.SelectAll();
var i = 0;
GotFocus += (sender, args) =>
{
if (i == 0)
{
TableNameBox.Focus();
TableNameBox.SelectAll();
}
i++;
};
TableNameBox.Text = originalName;
using (var conn = new SqlConnection(App.ConnectionString))
{
conn.Open();
//Debug.WriteLine(App.GetPrefabOfTable(conn, originalName));
PrefabComboBox.SelectedValue = App.GetPrefabOfTable(conn, originalName);
//Debug.WriteLine(App.GetPrefabOfTable(conn, originalName));
conn.Close();
}
if (string.IsNullOrEmpty(originalName) || PrefabComboBox.SelectedIndex < 0)
{
ChooseButton.IsEnabled = false;
}
}
