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;
* }*/
}
}