Monday, March 19, 2012

key/value pair table update query

I'm working with a table that I've created called Config which contains key/value pairs used to get and set site-wide settings. I'm now trying to create a web form which updates the table but I'm not sure how to create the most effective UPDATE query.

Table of course takes this form key | value
----------
config_setting1 | value1
config_setting2 | value2

I'm working with a System.Collections.Specialized.StringDictionary Class object which contains all of the pairs from my webform... anybody have a creative way to build an UPDATE string using this object?

Thanks for any help and suggestions,
ecolner@.yahoo.comI suggest you to create static method to your database class that updates the configuration. The idea is simple.

1) Create parameterized SqlCommand for updates (update conf set value=@.value where setting=@.setting).
2) Start database transaction.
3) Iterate through you string dictionary and on every turn evaluate @.value and @.setting parameters again and then execute the command.
4) If there is errors then roll back database changes, otherwise commit the changes
5) Dispose the update command

No comments:

Post a Comment