Updating an Unbound DataTable

[grip003]

For all my past projects, I have been using a database class I wrote to do all my database updating (and retrieving) to and from MySQL. I basically have 2 commands, select and execute. My select function simply returns a DataTable. Therefore, all my changes must be made through the use of the execute function. Anyway, now I have a grid that I want to make changes to and then send all the updates to the database. I know you can bind everything, using a connection object, DataAdapter, and a DataSet and then use the DataAdapter's Update function, but I can't seem to figure it out. I would really like to be able to use my database class and just write some kind of update function. Does anyone have any suggestions? Thanks.

[Nerseus]

If you don't want to use the DataAdapter you don't have to - but you can still use the DataSet for binding and detecting changes. Assuming you get back a DataTable from your Select method, you can still bind a grid to the DataTable/DataSet. I'm not sure which grid you're using, but most grids will bind directly to the DataSet and make the changes there. The DataSet has a HasChanges property. It also has a GetChanges() method that will give you a trimmed down version of your DataSet that just has the changed rows (Updated, Inserted and Deleted). The DataRow knows the original values and the modified values automatically.

 

With all that information in the DataSet, you should be able to write some generic code to loop through the changed rows and build whatever type of code you need to use your own Execute method.

 

As always, if you need specific help, just let us know what your execute object expects. If it simply want's some dynamic SQL then I'd think about investing a half day or so to get used to the DataAdapter - abandoning a bad design in favor of a robust one is always worth it. I've used MySQL with the .Net Connector and it's very easy. Plus, the .Net Connector has full source available in C# if you wanted to look at it.

 

-ner

[grip003]

Hey Nerseus:

 

I have code that loops through and does my updating. The problem I am running into is when there is a table that has no primary key. I currently just try to match every field, which is ok, but I am worried that something might go wrong.

[Nerseus]

Without a "primary key" (defined as such, or just having a guaranteed unique set of columns) there's no guarantee you'll be updating the right row. I haven't tried this with MySQL, but I know SQL Server and Access will happily update multiple rows based on a non-unique WHERE clause - I would guess that MySQL allows the same thing.

 

If at all possible, I'd change the structure of the table to have a Primary Key. You may have to write a "cleanup script" that generates the key for you and removes or just identifies duplicates. I have a general rule that you shouldn't have to code around bad data if you can offer a solution that fixes it. Don't complicate the code for something that can be fixed elsewhere.

 

-ner

[grip003]

Yeah, I never create a table without some kind of primary key. If I really don't need one, I simply add an auto_increment key and just call it myKey or something like that. I am trying to write a generic MySQL Database Manager program to ease my administrative duties managing my company's databases. Some of the tables don't have primary keys, but luckily I should never have to modify them. Thanks for all your responses. I really appreciate any ideas since I have no one else here that I can bounce my thoughts off.