Best practice in Saving, Deleting and Updating Data

Question

Best practice in Saving, Deleting and Updating Data

Cullen

SSC Veteran

Points: 236
More actions
October 14, 2005 at 3:43 pm

#95446

Hi guys,
I am new to SQL database though have been programing for many years.
What is the best practice in terms of saving, deleting, updating?
For example:
Entering a deposit
1. I collect the information from the user through a form.
2. Create a transaction record
3. Update the account
4. If anything should go wrong in this process, I'd like to revert the whole thing and let the user know about it.
Another example:
Deleting an invoice:
1. Deleting the invoice
2. Update the accounts
3. If anything should go wrong in this process, I'd like to revert the whole thing and let the user know about it.
Last example:
Updating a transaction:
1. collect info from the user
2. locate the transaction
3. update it and related accounts
4. If anything should go wrong in this process, I'd like to revert the whole thing and let the user know about it.
I am planning to write an business application in C#.NET.
Any suggestion will be greatly appreciated.
Cullen
Dare to Question -- Care to Answer
Time is like water in a sponge, as long as you are willing you can always squeeze some.
--Lu Xun, Father of Modern Chinese Literature

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Joe Clifford SSCrazy Eights Points: 8739 More actions · Answer 1

Cullen -

The term transaction is right on the money, search for "Begin Transaction" in your sql server books online - a series of stored procedures with the appropriate begin transaction, commit &/or rollback is what you're looking for.

Joe

Cullen SSC Veteran Points: 236 More actions · Answer 2

Joe,

Thanks for the reply!

Is there any article anywhere that warns about pitfalls that are not documented in books online?

Or a best practice in SQL database programing?

Cullen

Dare to Question -- Care to Answer

Time is like water in a sponge, as long as you are willing you can always squeeze some.

--Lu Xun, Father of Modern Chinese Literature

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 3

Have a look at

http://www.sqlservercentral.com/articles/articleList.asp?categoryid=102

1. Have a proper design (very very important)

2. Try to avoid holding locks (not retrieving all rows, waiting for user input in the midst of a transaction,...)

3. Avoid network latency (roundtrips, amount of data returned)

4. Set-based solutions perform better. Avoid cursors when possible, dynamic sql.

5. Pitfall by triggers: the trigger only fires once for the whole insert/... command

...

Cullen SSC Veteran Points: 236 More actions · Answer 4

Thanks Jo for link as well as list. Truely appreciate it!

Cullen

Dare to Question -- Care to Answer

Time is like water in a sponge, as long as you are willing you can always squeeze some.

--Lu Xun, Father of Modern Chinese Literature

Kory Becker Hall of Fame Points: 3585 More actions · Answer 5

A side note with transaction management as far as begin tran/commit/rollback go. I am sure you will find this in the best practices artice, but you can use a raiserror and/or a return value from procedures to inform the application the outcome of the transaction. This will allow the application to either inform the user or handle the error and re-run the procedure.

Farrell Keough SSCoach Points: 17414 More actions · Answer 6

Excellent point!

I have seen too many applications in which this overlooked! (Often the data is checked at the front-end and the user gets an error they cannot understand if the Transaction Fails...).

I wasn't born stupid - I had to study.