469,610 Members | 1,745 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,610 developers. It's quick & easy.

When to update the database when moving through a series of forms

Hi everyone,

I have a small problem regarding a wizard that I'm making on my website.

The wizard is obviously a series of pages that take values from the user. My
question is:

- Should I store all the values entered until the last page and then update
the database, or should I do it as I go.

At the moment I'm updating the database as I go. This causes a significant
problem in that if the user hits cancel, or if there is an exception of some
sort, the database is already updated and there is no way for the user to
delete or amend the data.

Storing up all the data in session variables seems like a complete pain in
the arse. Is there any other way that this could be done?

Sincerest thanks to anyone who can help

Kindest Regards

Simon
Nov 18 '05 #1
3 1389
It won't be so much of a pain in the rear to keep the data in Session variables if you create a DataSet to hold all the data and then use Data Adapters to send it all to the DB at the right time. In fact, you'll only need one session variable: a reference to the DataSet. The DataSet's collection of Data Table objects can have one member for each of the actual database tables your data will be stored in. A separate Data Adapter (not to be persisted between pages) will handle each individual Data Table

If you're using Visual Studio, do the following for each of the tables that your application will need to update: Drag the appropriate type of data adapter from the toolbox onto a Web Form and run through the "Configure Data Adapter" wizard. Then, right click on the adapter and select "generate dataset". This will create a DataSet and also put the Data Tables in it. The combination of the wizard and the generate dataset steps will generate a raft of code. Even if you can't use the code as is in your app you can cut-and-pase this code into your own code module(s)

If your app only needs to insert rows into the DB, then you can just use the generated SQL statements for INSERTS but the wizard will generate SELECTs, UPDATEs, DELETEs - the whole nine. When you issue the Update command for each data adapter, it will issue the correct queries, according to "row state" values that are held in the Data Table objects. Just be sure that your adapters have Command objects that execute the types of SQL statements that your row states indicate. (For example, assume a Data Table only has rows that are in "added" state. Its DataAdapter won't need DELETE or UPDATE when executing its "update" method. It will be perfectly happy to execute the necessary INSERTs without the other commands.

It's not magic, there isn't even any pixie dust involved. This approach merely saves you a lot of tedious coding. Not only do you get the SQL statements, you don't have to code up a whole potful of assign statements to store stuff in session variables and then pull it all back out and feed command parameters when it's time to commit to the database. Study the wizard-generated code carefully to make sure you understand what it's doing and selectively copy it into the right places within your app. Make it a learning process

Your DataSet can contain primary and foreign keys, relations, validations, etc. - all of which may be useful in ensuring that the data is valid before you send it to the DB. If there are constraints in your database (e.g. Foreign Keys), they may impact the order in which your application has to issue the various commands. It's important to keep in mind that the DataSet is totally disconnected from the DB; that's why you can keep in it session state without affecting the DB itself

One other thing to consider: Datasets can be "typed" (meaning that they are impemented in XML) and there's a way to store the XML in View State. Given that you'll need to pass the data from one page to the next, I couldn't say, off hand, whether that wouldbe a good approach or not. But, you might want to look into it. The tradeoff, of course, is that you wouldn't need the storage overhead of keeping all the data in session state but your page loads and round trips will have more volume to deal with

Good Luc

Nov 18 '05 #2
RAJ
You can use transaction processing for this kind of data handling. Start the
transaction on the first page, save the transaction/connection in a session
variable and pass it on to the next page. If there is an error in any of the
pages just rollback the transaction. If there are no errors at the end of
the last page commit the transaction.

You should be able to add the transaction code into your existing logic
(i.e. updating the database as you go) without any changes to existing
code.
Hope this helps.

"Simon" <sh856531@microsofts_free_email_service.com> wrote in message
news:uh**************@TK2MSFTNGP09.phx.gbl...
Hi everyone,

I have a small problem regarding a wizard that I'm making on my website.

The wizard is obviously a series of pages that take values from the user. My question is:

- Should I store all the values entered until the last page and then update the database, or should I do it as I go.

At the moment I'm updating the database as I go. This causes a significant
problem in that if the user hits cancel, or if there is an exception of some sort, the database is already updated and there is no way for the user to
delete or amend the data.

Storing up all the data in session variables seems like a complete pain in
the arse. Is there any other way that this could be done?

Sincerest thanks to anyone who can help

Kindest Regards

Simon

Nov 18 '05 #3
Thanks to you all for your suggestions!

Especially Rich who has been very generous with his time when writting that
reply. I think I'll take the session approach. Keeping a transaction open
for that length of time doesnt sound like a good idea. IT would be a l;ot
easier if it were though. I think I may use a custom object to encapsulate
the data that needs to be entered over the course of the pages.

Thanks again

Simon
Nov 18 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

9 posts views Thread by Pam Ammond | last post: by
7 posts views Thread by gthompson | last post: by
5 posts views Thread by explode | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.