October 14, 2005 at 3:43 pm
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
October 15, 2005 at 1:16 am
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
October 17, 2005 at 10:51 am
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
October 17, 2005 at 12:04 pm
Have a look at
http://qa.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
...
October 17, 2005 at 2:52 pm
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
October 19, 2005 at 7:04 am
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.
October 19, 2005 at 8:37 am
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply