Making bulk Insertion/updation

Question

I have data entry form like...

There are some empty rows and some of them have values. User can Update existing values and can also fill value in empty rows.

I need to map these values in my DB table and some of them will be inserted as new rows into the database and existing record will be updated.

I need your suggestions, How can I accomplish this scenario with best approach.

Thanks

Answer 1

For each row, I would have a primary key (hidden), a dirty flag, and a new flag. In the grid, you would set the "dirty" flag to true when changes are made. When adding new rows in the UI, you would set the new flag as well as generate a primary key (this would be easiest if you used GUIDs for the key). Then, when you post this all back to the server, you would do inserts when the new flag is set and updates for those with the dirty flag.

Once the commit of the data has completed, you would simply clear the dirty and new flags.

Of course, if the data is shared by multiple contributors and can be edited concurrently, there's a bit more involved if you don't want someone overwriting another's edits.

Answer 2

I would look into using ADO.net DataSets and DataTables as a backing store in memory for your custom data grid. ADO.net allows you to bulk load a data set out of the database and track inserts, updates, and deletes against that data in memory. Once you are done, you can then bulk process the stored transactions back into the database.

The big benefit of using ADO.net is that all the prickly change tracking code is written for you already, and the library is deployed to every .net capable machine.

While it isn't in vogue right now, you can also send ADO.net data sets across the wire using XML serialization for altering and then send it back to be processed into the database.

Google around. There are literally thousands of books, tutorials, and blog posts on how to use ADO.net.