SQLDataadapter with Delete

Question

I am just using select query and filling the dataset. and do some changes and I want to delete this entry in the dataset I am doing this

  foreach (DataRow dr in dsDatadata.Tables[0].Rows)
                                {
                                    dr.Delete();
                                }



                                sdAdapter.Update(dsDatadata,"DataData");

But it is not working .

Note:- I dont want to use SQL Delete command

How to resolve this?

Answer 1

That is because the sdAdapter doesn't have a Command for the SQL Delete Command.
You can auto-generate the INSERT, UPDATE and DELETE commands for the adapter by using a CommandBuilder, but it only works for simple queries (for single-table, no join).

foreach (DataRow dr in dsDatadata.Tables[0].Rows) {
  dr.Delete();
}
DataAdapter sdAdapter;

DbCommandBuilder cb = new SqlCommandBuilder(sdAdapter);
sdAdapter.Update(dsDatadata, "DataData");

Note: I used the SQLCommandBuilder but you should replace it with the one you use (OleDB...)
See the MSDN Documentation for more details...

If your query is more complicated, then there is no easy path, you'll have to write the SQL yourself.