Faced the problem: How to insert / delete / update records in the database? I want to make it so that the user worked with the table, clicked the "Save" button and all changes were committed.

GitHub project

Adding works, the update is not, why it is not clear, deletion with an error: you can not get data from the deleted row. Please tell me how to deal with this problem.

I work with the form: "CitiesDialog".

/// <summary> /// Метод обновляет строку в таблице Cities /// </summary> /// <param name="cityRow">Редактируемая строка</param> public void ChangeCity(DataRow cityRow) { string commadString = "UPDATE Cities " + "SET CityName = @CityName " + "WHERE CityID = @CityID;"; using (OleDbConnection connection = new OleDbConnection(connectionString)) { try { connection.Open(); OleDbCommand cmd = new OleDbCommand(commadString, connection); cmd.Parameters.AddWithValue("@CityID", cityRow["CityID"]); cmd.Parameters.AddWithValue("@CityName", cityRow["CityName"]); cmd.ExecuteNonQuery(); } catch (Exception) { throw; } } cityRow.AcceptChanges(); } /// <summary> /// Метод удаляет запись из таблицы Cities /// </summary> /// <param name="cityRow">Удаляемая строка</param> public void DeleteCity(DataRow cityRow) { string commadString = "DELETE Cities " + "WHERE CityID = @CityID;"; using (OleDbConnection connection = new OleDbConnection(connectionString)) { try { connection.Open(); OleDbCommand cmd = new OleDbCommand(commadString, connection); cmd.Parameters.AddWithValue("@CityID", cityRow["CityID"]); cmd.ExecuteNonQuery(); } catch (Exception) { throw; } } cityRow.AcceptChanges(); } /// <summary> /// Метод вставляет/обновляет/удаляет записи /// в зависимости от состояния /// </summary> /// <param name="cityTable">Изменяемая таблица</param> public void UpdateCities(DataTable cityTable) { foreach (DataRow city in cityTable.Rows) { if (city.RowState == DataRowState.Added) InsertCity(city); if (city.RowState == DataRowState.Modified) ChangeCity(city); if (city.RowState == DataRowState.Deleted) DeleteCity(city); } cityTable.AcceptChanges(); } /// <summary> /// Изменить запись /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button2_Click(object sender, EventArgs e) { string id = dataGridView1.CurrentRow.Cells["CityID"].Value.ToString(); DataRow rowToEdit = cities.Rows.Find(id); try { if (textBox1.Text.Count() != 0) { rowToEdit["CityName"] = textBox1.Text.Trim(); } else { throw new Exception(cities.Columns["CityName"].Caption + " обязательно для заполнения"); } } catch (Exception ex) { MessageBox.Show(ex.Message); } } /// <summary> /// Удалить запись /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button3_Click(object sender, EventArgs e) { string id = dataGridView1.CurrentRow.Cells["CityID"].Value.ToString(); DataRow rowToDelete = cities.Rows.Find(id); try { rowToDelete.Delete(); } catch (Exception ex) { MessageBox.Show(ex.Message); } } /// <summary> /// Сохранить изменения в таблице /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button5_Click(object sender, EventArgs e) { try { dal.UpdateCities(cities); DialogResult = DialogResult.OK; } catch (Exception ex) { MessageBox.Show(ex.Message); DialogResult = DialogResult.Cancel; } finally { this.Close(); } }
  • If you are given an exhaustive answer, mark it as correct (a daw opposite the selected answer). - Nicolas Chabanovsky ♦

2 answers 2

There are no deleted rows in your table - they have also been deleted! Therefore, you do not get them in the foreach .

Try using the GetChanges() method to get changes including deleted rows.

  • With the removal figured out, but I had to change the input parameters for the method. GetChanges () came in handy, the loop only runs through the modified lines, although maybe I did not apply it correctly. The issue of updating records is still relevant. No errors, methods work, but nothing happens. - Algolux
  • And with the update, what's wrong? Have you tried to debug? - Pavel Mayorov
  • Yes, I tried, the method works, the correct parameters are passed to it. Eksepshenov does not arise. I also tried to change the request and the parameters. I opened the form, changed the tablet, saved the changes, rediscovered it, nothing. And in the database too. - Algolux
  • It does not happen. Somewhere there should be an exception. - Pavel Mayorov

I assume that RowState.Deleted is placed after the row has already been removed from the DataTable. Obviously, to get any information on this line will not work. If you want the changes to be saved across the entire table at once, this will be problematic. As an option to store somewhere before changing the table and then line by line compare the keys.

  • Perhaps you are right, but I did this: if (city.RowState == DataRowState.Deleted) DeleteCity(city["CityID", DataRowVersion.Original].ToString()); The issue with the update is still relevant, no errors, all the methods work. I did it step by step, the parameters are being passed correctly, which I can’t understand. - Algolux