CODEWITHSUNDEEP
c#wpfsqlitedatagrid
Ahmed Mohammed
Ahmed Mohammed

Reputation: 305

WPF with datagrid to delete selected items

I have a Datagrid that get filled from a SQLite table

Connect();
mAdapter = new SQLiteDataAdapter("SELECT * FROM clients", mConn);
mTable = new DataTable();
mAdapter.Fill(mTable);
dataGrid.ItemsSource = mTable.DefaultView;
mConn.Close();

I can make it delete an selecteditem but how can I make it delete selecteditems based on their id

private void dataGrid_PreviewKeyDown(object sender, System.Windows.Input.KeyEventArgs e)
    {
        if (e.Key == System.Windows.Input.Key.Delete)
        {
            if (dataGrid.SelectedItem == null)
                return;
            DataRowView rowView = (DataRowView)dataGrid.SelectedItem;
            Connect();
            using (SQLiteCommand mCmd = new SQLiteCommand("DELETE FROM clients WHERE ID=" + rowView["ID"], mConn))
            {
                mCmd.ExecuteNonQuery();
            }
            mAdapter.Update(mTable);
            mConn.Close();
        }
    }

Upvotes: 1

Views: 1014

Answers (2)

King King
King King

Reputation: 63337

Looks like you want to use raw query to delete the items. In this case to access the selected items on the DataGrid, you use the property SelectedItems. You can either delete each one with 1 query or a block of items in 1 query, here is the code to delete each one using 1 query:

if (e.Key == System.Windows.Input.Key.Delete) {
        if (dataGrid.SelectedItem == null)
            return;
        Connect();
        foreach(var item in dataGrid.SelectedItems.Cast<DataRowView>()) {
           using (var mCmd = new SQLiteCommand("DELETE FROM clients WHERE ID=" + item["ID"], mConn)) {
              mCmd.ExecuteNonQuery();
           }
        }
        mAdapter.Update(mTable);
        mConn.Close();
}

Here is the code to delete a block of items with 1 query:

if (e.Key == System.Windows.Input.Key.Delete) {
        if (dataGrid.SelectedItem == null)
            return;
        Connect();
        const int blockSize = 100;
        var inOperands = dataGrid.SelectedItems
                                 .Select((e,i) => new { 
                                    row = ((DataRowView) e)["ID"], i        
                                  })
                                 .GroupBy(e => e.i / blockSize)
                                 .Select(g => string.Format("({0})", 
                                              string.Join(",", g.Select(o => o.row))));
        foreach(var inOperand in inOperands) {
           using (var mCmd = new SQLiteCommand("DELETE FROM clients WHERE ID IN " + inOperand, mConn)) {
              mCmd.ExecuteNonQuery();
           }
        }
        mAdapter.Update(mTable);
        mConn.Close();
}

By the way you should use SQLiteParameter rather than concatenate the values manually to avoid query injection.

Upvotes: 1

shucode
shucode

Reputation: 55

As user2946329 said your code should work. I think if you need to delete multiple items you need more buttons and you have to set each button to delete through separate variables or you can loop through them via the actual sql data e.g. 

string a = select name, from tbl where name = "Ahmed" 
string b =       select name, from tbl where name = "Ahmed"etc)

then just

SQLiteCommand mCmd = new SQLiteCommand("DELETE FROM clients WHERE ID=" + a + b, mConn))
        {
            mCmd.ExecuteNonQuery();
        }

Upvotes: 1

Related Questions