public void ImportData(string tableName, DataTable dataTable)
{
// Don't continue if no data passed
if (dataTable == null)
{
throw new ArgumentNullException(nameof(dataTable));
}
CommandExecutor commandExecutor = new CommandExecutor();
InsertCommandBuilder insertCommandBuilder;
// To prevent attempting to import deleted rows, need to accept changes on the passed datatable first
dataTable.AcceptChanges();
// Run an INSERT command for each row
foreach (DataRow row in dataTable.Rows)
{
insertCommandBuilder = new InsertCommandBuilder();
insertCommandBuilder.SetTableName(tableName);
// Construct INSERT command
for (int i = 0; i < dataTable.Columns.Count; i++)
{
if (dataTable.Columns[i].ColumnName == "_id")
{
continue;
}
insertCommandBuilder.AddField(dataTable.Columns[i].ColumnName, row[i]);
}
// Insert into database
commandExecutor.ExecuteNonQuery(insertCommandBuilder.ToString());
}
}
private void dgvResults_RowLeave(object sender, DataGridViewCellEventArgs e)
{
var insertCommand = new InsertCommandBuilder();
try
{
// Grid is readonly
if (dgvResults.ReadOnly)
{
return;
}
if (!dgvResults.Columns.Contains("_id"))
{
return;
}
// Ignore new rows that are being cancelled
if (dgvResults.Rows[e.RowIndex].Cells["_id"].Value == null)
{
return;
}
// Ignore existing rows
if (dgvResults.Rows[e.RowIndex].Cells["_id"].Value.ToString() != "")
{
return;
}
// Don't insert blank rows
if (IsGridRowBlank(dgvResults.Rows[e.RowIndex]) || HasGridRowOnlyDefaultValues(dgvResults.Rows[e.RowIndex]))
{
return;
}
// Exit if there's currently errors on the rows
if (dgvResults.Rows[e.RowIndex].ErrorText != "")
{
return;
}
// Build insert command
insertCommand.SetTableName(_currentTable);
foreach (DataGridViewCell cell in dgvResults.Rows[e.RowIndex].Cells)
{
// Ignore _id column as this will be automatically populated
if (dgvResults.Columns[cell.ColumnIndex].Name == "_id")
{
continue;
}
insertCommand.AddField(dgvResults.Columns[cell.ColumnIndex].Name, cell.Value);
}
// Run insert command
_commandExecutor.ExecuteNonQuery(insertCommand.ToString());
_repositionToEndOfGrid = true;
// Re-run query command - needs to use BeginInvoke call to avoid reentrant errors
BeginInvoke(new MethodInvoker(PopulateGridFromSelectQuery));
}
catch (Exception ex)
{
DisplayError(ex);
}
}