Trying to delete entire row from dataGridView and SQLite db.

Stan Sypek

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

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes11y ago
Accepted 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 :)
+1
Vulpes11y ago
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.
0
Stan Sypek11y ago
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.
0
Stan Sypek11y ago
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!
0
Vulpes11y ago
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;
}
}

+1
Stan Sypek11y ago
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.
0
Vulpes11y ago
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);
0