Updating an Unbound DataTable

G

grip003

Well-known member
Joined
Sep 2, 2004
Messages
89
Location
North Carolina
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 DataAdapters Update function, but I cant 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.
 
If you dont want to use the DataAdapter you dont 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. Im not sure which grid youre 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 wants some dynamic SQL then Id 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. Ive used MySQL with the .Net Connector and its very easy. Plus, the .Net Connector has full source available in C# if you wanted to look at it.

-ner
 
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.
 
Without a "primary key" (defined as such, or just having a guaranteed unique set of columns) theres no guarantee youll be updating the right row. I havent 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, Id 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 shouldnt have to code around bad data if you can offer a solution that fixes it. Dont complicate the code for something that can be fixed elsewhere.

-ner
 
Yeah, I never create a table without some kind of primary key. If I really dont 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 companys databases. Some of the tables dont 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.
 
Back
Top