public static DataSet MainSet(MasterTablePage page) { DataSet dataTableList = new DataSet(Settings["Schema"].ToString()); var conn = new SqlConnection(ConnectionString); conn.Open(); foreach (string tableName in GetTableNames(conn)) { var cmd = new SqlCommand($"SELECT * FROM [{Settings["Schema"]}].[{tableName}]", conn); var sda = new SqlDataAdapter(cmd) { AcceptChangesDuringUpdate = true, AcceptChangesDuringFill = true }; DataTable table = new DataTable(tableName); lock (AutoSaveLock) { sda.Fill(table); } dataTableList.Tables.Add(table); //foreach(DataRow row in set.Tables[tableName].Rows) // for(int i = 0; i < row.ItemArray.Length; i++) // if (string.IsNullOrEmpty(row[i].ToString())) // row[i] = null; Task.Run(() => { lock (AutoSaveLock) { Thread.CurrentThread.Name = $"AutoSave/Update {table.TableName}"; Thread.CurrentThread.IsBackground = true; SetTableOnChangeds(table, sda, conn, page); } }); //Task.Run(() => NotifyNewItem(table, page, dataTableList.Tables.IndexOf(table))); } conn.Close(); return(dataTableList); }
private static void SetTableOnChangeds(DataTable table, SqlDataAdapter sda, SqlConnection conn, MasterTablePage page) { if (conn.State != ConnectionState.Open) { conn.Open(); } table.RowChanged += (sender, args) => { if (!(args.Action == DataRowAction.Add || args.Action == DataRowAction.Change)) { return; } SetSdaCommands(table, args, sda, conn, page); ThisMadeLastChange = true; }; table.RowDeleting += (sender, args) => { DataRow newRecycledRow = Recycled.RecycledDataTable.NewRow(); for (int i = 0; i < args.Row.ItemArray.Length; i++) { newRecycledRow[i] = args.Row[i]; } Recycled.RecycledDataTable.Rows.Add(newRecycledRow); }; table.RowDeleted += (sender, args) => { if (args.Action != DataRowAction.Delete) { return; } SetSdaCommands(table, args, sda, conn, page); ThisMadeLastChange = true; }; if (conn.State != ConnectionState.Closed) { conn.Close(); } }
public static void SetSdaCommands(DataTable table, DataRowChangeEventArgs args, SqlDataAdapter sda, SqlConnection conn, MasterTablePage page) { lock (AutoSaveLock) { //Debug.WriteLine(string.Join(" | ", args.Row.ItemArray)); if (ThisIsNowConcurrent) { ThisIsNowConcurrent = false; return; } /*List<object> oldRow = new List<object>(); * try * { * for (int n = 0; n < args.Row.ItemArray.Length; n++) * { * try * { * oldRow.Add(args.Row[n, DataRowVersion.Original]); * } * catch * { * break; * } * } * } * catch * { * //ignored * }*/ int rowIndex = table.Rows.IndexOf(args.Row); var sdaUpdateCommand = new SqlCommand { Connection = conn, CommandText = $"UPDATE [{Settings["Schema"]}].[{table.TableName}] SET " }; var sdaInsertCommand = new SqlCommand { Connection = conn, CommandText = $"INSERT INTO [{Settings["Schema"]}].[{table.TableName}] (" }; var sdaDeleteCommand = new SqlCommand { Connection = conn, CommandText = $"DELETE FROM [{Settings["Schema"]}].[{table.TableName}] WHERE " }; int i = 0; List <string> columnsList = new List <string>(); foreach (DataColumn col in table.Columns) { columnsList.Add(col.ColumnName); } string[] columns = columnsList.ToArray(); if (args.Action != DataRowAction.Delete) { foreach (var column in columns) { var value = $"'{table.Rows[rowIndex][i].ToString().Replace("'", "''").Replace("\"", "\"\"")}'"; if (table.Rows[rowIndex][i] is DBNull || table.Rows[rowIndex][i] == null) { value = "NULL"; } /*_dispatcher.Invoke(() => * { * if (string.IsNullOrEmpty(value.ToString()) && * NSDMasterInventorySF.MainWindow.MasterDataGrids[page.MasterTabControl.SelectedIndex].Columns[i] is * GridCheckBoxColumn) * value = "False"; * });*/ if (i != columns.Length - 1) { sdaUpdateCommand.CommandText += $"[{column}] = {value}, "; sdaInsertCommand.CommandText += $"[{column}], "; } else { sdaUpdateCommand.CommandText += $"[{column}] = {value} "; sdaInsertCommand.CommandText += $"[{column}]) "; } i++; } } sdaUpdateCommand.CommandText += "WHERE "; sdaInsertCommand.CommandText += "VALUES ("; int k = 0; foreach (var column in columns) { string value = "NULL"; if (args.Row.HasVersion(DataRowVersion.Original)) { value = $"'{table.Rows[rowIndex][k, DataRowVersion.Original].ToString().Replace("'", "''").Replace("\"", "\"\"")}'"; if (table.Rows[rowIndex][k, DataRowVersion.Original] is DBNull || table.Rows[rowIndex][k, DataRowVersion.Original] == null) { value = "NULL"; } } if (k != columns.Length - 1) { switch (value) { case "NULL": sdaUpdateCommand.CommandText += $"[{column}] IS {value} AND "; sdaInsertCommand.CommandText += $"{value}, "; sdaDeleteCommand.CommandText += $"[{column}] IS {value} AND "; break; default: sdaUpdateCommand.CommandText += $"[{column}] = {value} AND "; sdaInsertCommand.CommandText += $"{value}, "; sdaDeleteCommand.CommandText += $"[{column}] = {value} AND "; break; } } else { switch (value) { case "NULL": sdaUpdateCommand.CommandText += $"[{column}] IS {value} "; sdaInsertCommand.CommandText += $"{value})"; sdaDeleteCommand.CommandText += $"[{column}] IS {value}"; break; default: sdaUpdateCommand.CommandText += $"[{column}] = {value} "; sdaInsertCommand.CommandText += $"{value})"; sdaDeleteCommand.CommandText += $"[{column}] = {value}"; break; } } k++; } sda.UpdateCommand = sdaUpdateCommand; sda.InsertCommand = sdaInsertCommand; sda.DeleteCommand = sdaDeleteCommand; try { sda.Update(table); ThisMadeLastChange = true; } catch { //Debug.WriteLine("UPDATE FAILED"); //table.AcceptChanges(); //Debug.WriteLine("Failed Row: " + table.Rows.IndexOf(args.Row) + ": " + string.Join(" | ", args.Row.ItemArray)); /*if (args.Action == DataRowAction.Change) * { * string selectTxt = sdaDeleteCommand.CommandText * .Substring(sdaDeleteCommand.CommandText.IndexOf("WHERE", StringComparison.Ordinal)).Replace("WHERE ", string.Empty); * * foreach (DataRow row in table.Select(selectTxt)) * { * for (int index = 0; index < row.ItemArray.Length; index++) * { * ThisIsNowConcurrent = true; * Debug.WriteLine("orig: " + row[index] + " change: " + args.Row[index]); * row[index] = args.Row[index]; * } * } * if (conn.State != ConnectionState.Open) * conn.Open(); * sda.InsertCommand.ExecuteNonQuery(); * if (conn.State != ConnectionState.Closed) * conn.Close(); * ThisMadeLastChange = true; * }*/ page.InitializeOrRefreshEverything(page.MasterPivot.SelectedIndex); /*MessageBox.Show( * $"Failed to update Database {App.Settings["Database"]}; Error occured.\nThis was most likely caused by a concurrency issue and/or duplicate rows. The datagrids have been refreshed.", * "Error!", MessageBoxButton.OK, MessageBoxImage.Error);*/ throw; //ThisMadeLastChange = true; //page.SaveToDb(); } /*string selectText = sdaDeleteCommand.CommandText * .Substring(sdaDeleteCommand.CommandText.IndexOf("WHERE", StringComparison.Ordinal)) * .Replace("WHERE ", string.Empty);*/ /*switch (args.Action) * { * case DataRowAction.Add: * break; * case DataRowAction.Change: * //foreach (DataRow row in table.Select(selectText)) * //{ * // for (int index = 0; index < row.ItemArray.Length; index++) * // { * // ThisIsNowConcurrent = true; * // Debug.WriteLine("orig: " + row[index] + " change: " + args.Row[index]); * // row[index] = args.Row[index]; * // } * //} * break; * case DataRowAction.Delete: * foreach (DataRow row in table.Select(selectText)) * { * ThisIsNowConcurrent = true; * row.Delete(); * } * table.AcceptChanges(); * break; * }*/ //ThisIsNowConcurrent = true; /*switch (args.Action) * { * case DataRowAction.Delete: * HubProxy.Invoke("Send", "DELETE", table.TableName, string.Join("\t", oldRow.ToArray()), * string.Join("\t", oldRow)); * break; * case DataRowAction.Change: * HubProxy.Invoke("Send", "UPDATE", table.TableName, string.Join("\t", oldRow.ToArray()), * string.Join("\t", args.Row.ItemArray)); * break; * case DataRowAction.Add: * HubProxy.Invoke("Send", "INSERT", table.TableName, string.Join("\t", oldRow.ToArray()), * string.Join("\t", args.Row.ItemArray)); * break; * }*/ } }