0

I have a C# program where the INSERT, SELECT works fine. I want to try and UPDATE my DataGridView to the database.

If I delete a row in the GridView then it is sent to the db as a DataTable and all of this works fine except one thing, it writes new entries to that my three rows which should be 2 after delete becomes 5.

Here is my code:

 public void Update(DataTable dataforupdate, string table)
 {
     if (table == "Musik")
     {
         SQLiteDataAdapter adapter = new SQLiteDataAdapter("SELECT * FROM Musik",m_dbConnection);
         SQLiteCommandBuilder builder = new SQLiteCommandBuilder(adapter);
         adapter.Update(dataforupdate);
     }
 }

It should delete the table and then insert the datatable with all data (as it is the easiest thing to do when having multiple deletes or inserts).

EDIT: It now copies the table and inserts it, so the table is in there 2x times. It seems a bit unnecessary to use a "DELETE FROM Musik" before adding the DataTable, but it does work, if you can help I would like to know a better solution.

T.B Ygg
  • 116
  • 10
  • 2
    Why are you deleting the whole table. You should be deleting rows, and updating rows. Unless your change effects almost all your table would your method be correct. – AnthonyLambert Jun 13 '14 at 08:58
  • i thought it would be easier as i dont really know how to use this properly. – T.B Ygg Jun 13 '14 at 09:00
  • as for now it takes new entries and inserts to the db, along with all entries already there. so every time i add a new entry it will insert all entries + 1 to the db, which means the db will be doubled every time it is run. – T.B Ygg Jun 13 '14 at 09:16

1 Answers1

1

it should delete the table and then insert the datatable with all data (as it is the easiest thing to do when having multiple deletes or inserts).

I beg to differ. In fact, what you are doing is actually dangerous because you aren't using transactions. What happens if after you empty the DB there is an exception inserting the records? You lose everything.

It's fairly trivial to do multiple inserts/deletes in SQLite (or any DB for that matter) e.g. 

INSERT INTO SomeTable VALUES (A, B, C...) 
INSERT INTO SomeTable VALUES (D, E, F...)
...

For deletes, it all depends on the condition of the delete. For example, if I wanted to delete all records where A > 10

DELETE FROM SomeTable WHERE A > 10

I'd recommend you look at using an ORM like sqlite-net which does a lot of the heavy SQL lifting for you.

James
  • 80,725
  • 18
  • 167
  • 237
  • thanks for the answer.. i must admit that i got that wrong with deleting and then inserting the table. but the main thing is can this be done using datasets/datatables or should i just go with pure query strings? – T.B Ygg Jun 13 '14 at 10:16
  • I would never really recommend going with pure query strings unless you *really* had to because there are security implications that you would need to make sure you are catering for. It's generally safer (and easier) to use an ORM tool, however, there is no harm in using a `DataTable` as you currently are. The point I was trying to make was you shouldn't really be emptying a full table just to add/remove a couple of rows. – James Jun 13 '14 at 10:23
  • hi again.. and thank you for the answer, i will look at this ORM tool and see if i can figure it out. If i am about to use the DataTable then how should i use it without having to be emptying the table every time as it gets duplicated atm. – T.B Ygg Jun 13 '14 at 12:36
  • 1
    @T.BYgg you simply remove the rows you no longer need from the table and then update the adapter, see [this answer](http://stackoverflow.com/questions/1591771/datatable-how-to-conditionally-delete-rows#1591826). – James Jun 13 '14 at 12:37