Trying to delete entire row from dataGridView and SQLite db.

Question

I have a SQLite database that loads in a form. I can delete data from the dataGridView no problem. I can also delete it from the table, but only if I select a cell in the table. I can't figure out how to remove it from the table by selecting the entire row. The code I am using:

Code to load the table:

private void readAllRecords()
        {
            try
            {
                var datatable = new DataTable();
                myConnection.Open();
                string commandText = "Select * From Albums";
                SQLiteDataAdapter mySqliteDataAdapter = new SQLiteDataAdapter(commandText, myConnection);
                mySqliteDataAdapter.Fill(datatable);
                myConnection.Close();
                dataGridView.DataSource = datatable;
            }

            catch (Exception)
            {
                throw;
            }
        }

Code to delete:

private void btnDelete_Click(object sender, EventArgs e)
        {
            try
            {
                myConnection.Open();
                SQLiteCommand myCommand = new SQLiteCommand(myConnection);
                myCommand.CommandText = "Delete FROM Albums WHERE AlbumName = '" + this.txtAlbumTitle.Text + "' ";

//Must click in AlbumTitle cell per above line to get delete to work

                myCommand.ExecuteNonQuery();
                myConnection.Close();
                //MessageBox.Show("Your record has been deleted.");
                txtArtistName.Text = "     Artist Name";
                txtAlbumTitle.Text = "     Album Title";
                txtYear.Text = " Year";
                cmbxGenre.Text = "     Genre";
                cmbxMedium.Text = "     Medium";
                btnSave.Enabled = false;
                readAllRecords();
            }

catch (Exception)
{

                throw;
            }
        }

When I use:

foreach (DataGridViewRow row in dataGridView.SelectedRows)
    {
        if (!row.IsNewRow)
        dataGridView.Rows.Remove(row);
    }

This removes the selected row from the dataGridView, but does not remove it from the database.

Can someone show me how to combine this so I can highlight a row (or multiple rows) in the dataGridView and remove them from the database? TIA

Stan

Vulpes · Answer

The secret is the CommandBuilder. If you construct one of these and pass it a DataAdapter and if you also set the latter's SelectCommand property then the CommandBuilder will automatically generate the appropriate INSERT UPDATE and DELETE commands when the DataAdapter's Update statement is invoked. To do this the database table needs to have a primary key or a unique column and the CommandBuilder gets the rest of the information it needs from the SelectCommand property. The DataAdapter knows which rows need updating because flags are set internally each time you insert update or delete a row from the table or a control to which its bound. Although I know this stuff works with the DataAdapters for SQL Server ODBC and Oracle I wasn't 100% sure it would work with SQLite as well but it seems that it does :)

Vulpes · Answer

OK I think I know what the problem is. As long as the Albums table has a primary key or a unique column then (with luck) making these changes to the readAllRecords() method should cause the DELETE statements to be generated automatically: private void readAllRecords() { try { var datatable = new DataTable(); mySqliteDataAdapter = new SQLiteDataAdapter(); mySqliteDataAdapter.SelectCommand = new SQLiteCommand(Select * From Albums myConnection); var builder = new SQLiteCommandBuilder(mySqliteDataAdapter); myConnection.Open(); mySqliteDataAdapter.Fill(datatable); myConnection.Close(); dataGridView.DataSource = datatable; } catch (Exception) { throw; } }

Vulpes · Answer

There's a bit more on the workings of the CommandBuilder here on MSDN: http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommandbuilder%28v=vs.110%29.aspx Although this is for SQL Server it now seems safe to assume that it works the same for SQLite or any other database for which there is an ADO.NET provider.

Stan Sypek · Answer

Thanks I appreciate it. I saw articles online about the commandBuilder with the automatic INSERT UPDATE and DELETE but nothing I read was making sense. I can use this elsewhere in the program as well. I know this isn't an answer but I'm marking it correct as well. I can't be the only one that needs to see this.

Stan Sypek · Answer

You win the Internetz. Thanks!!! I'm trying to understand this. Is it the myConnection here --> Select * From Albums myConnection? If it's not a long explanation can you tell me why this worked? Thanks again!

Stan Sypek · Answer

Thanks Vulpes. When I try this I get: Update requires a valid DeleteCommand when passed DataRow collection with deleted rows. But if I use the delete command I had it does not handle deleting rows? **Edit** I was not calling the update method before. I was just using the code as is and it worked just not the way I wanted as mentioned in the post.

Vulpes · Answer

I've never worked with SQLite but as the DGV is databound I'd have thought that you'd need to call the SQLiteDataAdapter's Update method (assuming it has one) to remove the deleted rows from the database. However as this is currently a local variable you'll need to move it to form level first. So: private SQLiteDataAdapter mySqliteDataAdapter; // now a form level variable private void readAllRecords() { try { var datatable = new DataTable(); myConnection.Open(); string commandText = Select * From Albums; mySqliteDataAdapter = new SQLiteDataAdapter(commandText myConnection); // now using form level variable mySqliteDataAdapter.Fill(datatable); myConnection.Close(); dataGridView.DataSource = datatable; } catch (Exception) { throw; } } The code to delete the selected rows will then be: foreach (DataGridViewRow row in dataGridView.SelectedRows) { if (!row.IsNewRow) dataGridView.Rows.Remove(row); } DataTable datatable = (DataTable)dataGridView.DataSource; mySqliteDataAdapter.Update(datatable);