private const string ConnectionString =
        "server=*****.**;Port=****;database=**;username=****;password=*****;pooling=false;";

    private DataSet _localDatabaseCopy;
    private MySqlDataAdapter _myDataAdapter;

    private void Form1_Load(object sender, System.EventArgs e)
    {
        InitializeLocalDatabaseCopy();
        InitializeDataGridView();
        dataGridView1.AllowUserToAddRows = false;
    }

    /// <summary>
    ///  Binds the DataGridView to the BindingSource and load the data from the database.
    /// </summary>

    private void InitializeDataGridView()
    {
        DataTable dataTable = _localDatabaseCopy.Tables[Resources.WorkingDataDatabaseTableName];
        dataGridView1.DataSource = new BindingSource { DataSource = dataTable };
        dataGridView1.MultiSelect = true;
        dataTable.Columns[Resources.ColumnName_IDNr].AutoIncrement = true;
        dataTable.Columns[Resources.ColumnName_IDNr].AutoIncrementSeed = -1;
        dataTable.Columns[Resources.ColumnName_IDNr].AutoIncrementStep = -1;


    }
    /// <summary>
    /// Fills the _myDataAdapter DataAdapter with the workingData & approvedData DataTables and adds the 2 DataTables to the _localDatabaseCopy DataSet.
    /// </summary>
    private void InitializeLocalDatabaseCopy()
    {
        _localDatabaseCopy = new DataSet();
        DataTable workingData = new DataTable(Resources.WorkingDataDatabaseTableName);
        DataTable approvedData = new DataTable(Resources.ApprovedDataDatabaseTableName);
        using (MySqlConnection connection = new MySqlConnection(ConnectionString))
        {
            _myDataAdapter = new MySqlDataAdapter(string.Format("select * from {0}", Resources.WorkingDataDatabaseTableName), connection);
            _myDataAdapter.Fill(workingData);
            _myDataAdapter.SelectCommand.CommandText = string.Format("select * from {0}", Resources.ApprovedDataDatabaseTableName);
            _myDataAdapter.Fill(approvedData);
        }

        _localDatabaseCopy.Tables.Add(workingData);
        _localDatabaseCopy.Tables.Add(approvedData);
    }



    private void SaveChangesToDb(string tableName)
    {
        MySqlCommandBuilder builder = new MySqlCommandBuilder(_myDataAdapter);

        _myDataAdapter.SelectCommand.CommandText = String.Format("select * from {0}", tableName);
        _myDataAdapter.UpdateCommand = builder.GetUpdateCommand();
        _myDataAdapter.DeleteCommand = builder.GetDeleteCommand();
        _myDataAdapter.InsertCommand = builder.GetInsertCommand();

        DataTable deletedRecords = _localDatabaseCopy.Tables[tableName].GetChanges(DataRowState.Deleted);
        DataTable modifiedRecords = _localDatabaseCopy.Tables[tableName].GetChanges(DataRowState.Modified);
        DataTable addedRecords = _localDatabaseCopy.Tables[tableName].GetChanges(DataRowState.Added);
        try
        {
            if (deletedRecords != null)
                _myDataAdapter.Update(deletedRecords);
            if (modifiedRecords != null)
                _myDataAdapter.Update(modifiedRecords);
            if (addedRecords != null)
                _myDataAdapter.Update(addedRecords);

            _localDatabaseCopy.AcceptChanges();

        }
        catch (Exception exception)
        {
            MessageBox.Show(exception.Message);
        }
        finally
        {
            if (deletedRecords != null)
                deletedRecords.Dispose();
            if (modifiedRecords != null)
                modifiedRecords.Dispose();
            if (addedRecords != null)
                addedRecords.Dispose();
        }

    }



    private void btnSave_Click(object sender, EventArgs e)
    {

        SaveChangesToDb(Resources.WorkingDataDatabaseTableName);

    }


    private void DeleteDataRows(DataRow[] rowsToDelete, string tableIndex)
    {
        foreach (DataRow row in rowsToDelete)
            _localDatabaseCopy.Tables[tableIndex].Rows.Remove(row);
    }


    /// <summary>
    /// Transfers the approved DataRows from the DataTable/DataGridview to the Approved Table in the MySql Database
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    private void btnTransferApproved_Click(object sender, EventArgs e)
    {
        SetWaitingCursor();

        TransferApprovedDataRows();
        DeleteTransferedRowsFromWorkingDataTable();
        SaveChangesToDb(Resources.WorkingDataDatabaseTableName);

        SetDefaultCursor();
    }

    private void DeleteTransferedRowsFromWorkingDataTable()
    {
        DataRow[] approvedRowsToDelete = _localDatabaseCopy.Tables[Resources.WorkingDataDatabaseTableName].Select("Approved = 1");
        DeleteDataRows(approvedRowsToDelete, Resources.WorkingDataDatabaseTableName);

    }

    private void TransferApprovedDataRows()
    {
        DataTable myTableCopy = _localDatabaseCopy.Tables[Resources.WorkingDataDatabaseTableName].Copy();
        DataRow[] approvedRows = myTableCopy.Select("Approved = 1");

        // if there are no rows which have been approved, quit
        if (approvedRows.Any())
        {
            foreach (DataRow row in approvedRows)
            {
                // checking if the row which is to be approved already exists in the approved-table
                bool redundant = false;
                foreach (
                    DataRow approvedRow in _localDatabaseCopy.Tables[Resources.ApprovedDataDatabaseTableName].Rows)
                    redundant |= approvedRow[Resources.ColumnName_IDNr].ToString() ==
                                 row[Resources.ColumnName_IDNr].ToString();

                if (!redundant)
                    _localDatabaseCopy.Tables[Resources.ApprovedDataDatabaseTableName].Rows.Add(row.ItemArray);
            }
            // writing the changes back to the database, Approved Table
            SaveChangesToDb(Resources.ApprovedDataDatabaseTableName);
        }

        if (!approvedRows.Any())
            return;


    }


}

private void DeleteDataRows(DataRow[] rowsToDelete, string tableIndex)
{
    foreach (DataRow row in rowsToDelete)
        row.Delete();
}