|
I agree with what Ben Fair said, especially about transaction boundaries. I never get used to beginning and ending a transaction inside a database since then the client looses control over the ACID logic.
However passing several record with a single call is also a performance issue so it's benefitial to use such logic. One way to do it is descibed in an article I wrote: How to pass multiple records to a stored procedure[^]
Hope it helps,
Mika
|
|
|
|
|
Great article  Thanks for sharing. This definitely solves the issue I had.
What a good coincidence to asked the question and then exactly at the same day you posted the great article.
I like database related stuff(All CRUD operation) to be inside database. I use auto generated code in my application to access stored procedures. This both increase coding speed and also makes it cleaner and easier to maintain(It's easier to modify a stored procedure later instead of the application that needs recompilation.) I have this logic: A CustomerAdd stored procedure has to add a customer along with it's contact information(for instance) that might be more than one record. Database might use transactions or specific relations and constraints, triggers, functions, etc. to make sure data is maintained correctly. Why should application bothers how underlying data store system is going to store these data? This is my opinion. Actually I mostly hear people advise what you also stated, but I'm still not motivated enough to do it the other way.
Thanks again.
"In the end it's a little boy expressing himself." Yanni
|
|
|
|
|
Hamed Mosavi wrote: Great article Thanks for sharing
You're welcome.
Hamed Mosavi wrote: Database might use transactions or specific relations and constraints, triggers, functions, etc. to make sure data is maintained correctly. Why should application bothers how underlying data store system is going to store these data
I think this is kind of an everlasting war I'm not saying that starting and ending transactions at client side is absolutely the only right way. The important thing is that which ever way you choose, you know the limitations as well as the benefits.
I've used to control transactions at client side for several reasons, like:
- when business logic changes, it's easy to implement and add new functionality if the old logic is still valid (adding logic)
- code at both middle tier and database can be kept as simple building block and it's more easily reused
- if necessary transactions can be more easily propagated and distributed
- business logic can be transactional (not only the data in the database)
- if needed, retries can be used without having to execute the whole logic again etc.
But these are still opinions
|
|
|
|
|
Mika Wendelius wrote: I think this is kind of an everlasting war
 Very true.
While some of the benefits you mentioned can be done with writing good sql queries(like reusability) and sometimes it works better, some others can't and might be needed for many projects to be done inside application.
I believe in the projects that I deal with everyday, putting data related stuff on the shoulder of the database works better. Definitely it will be useful and maybe needed to have transactions in business logic specially when working on web based projects, IMHO.
It looks like you are working on larger projects, so you decided to take transactions from Database or even DAL and do it in BLL. I'm guessing you are using System.Transactions or maybe COM+ and SWC both of which are too big for my kind of small projects.
Maybe what we work on is playing an important role on the technology we use or the way we use it.
"In the end it's a little boy expressing himself." Yanni
|
|
|
|
|
You obviously have thought these through so I believe that you have a good and well defined solution. That's the most important thing
|
|
|
|
|
Mika Wendelius wrote: You obviously have thought these through
Not really. Just as much as time permits and I never find enough time.
Mika Wendelius wrote: a good and well defined solution. That's the most important thing
Yes that's certainly the important thing. I hope to be able to do my job well. Discussions in forums of CP has always been a great source of information and helped me a lot. I always learn from them. I have just had a good one. Thanks for that.
"In the end it's a little boy expressing himself." Yanni
|
|
|
|
|
Hamed Mosavi wrote: I have just had a good one
The feeling is mutual
|
|
|
|
|
That's an intriguing idea, I haven't really worked with SQL Server 2008 yet and using table valued parameters hadn't crossed my mind. I can see how it would easily lend itself to this situation. Thanks for the article!
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Ben Fair wrote: That's an intriguing idea
My thoughts exactly Previously I've done the same thing a bit differently, but now with 2008 it's easy and efficient.
|
|
|
|
|
HI ALL,
Iam using sql reporting services ,i want to keep image from database
I kept IMAGE AND KEPT THE DATASOURCE FIELD AS (EX PHOTO_LOCATION)
IN THAT PHOTO_LOCATION FIELD IAM HAVING IMAGE NAME ..
(I ALSO KET THE IMAGE LOCATION IN TAT FILED AND CHECKED ,BUT IMAGE IS NOT COIMG)
WHEN I RUN THE REPORT IMAGE IS NOT VISIBLE ONLY TAT BLANK IMAGE IS COMING .I ALSO KEPT THE MIMET TYPE CORRECTLY
|
|
|
|
|
By cbenan wrote: IN THAT PHOTO_LOCATION FIELD IAM HAVING IMAGE NAME ..
do you mean the image's file name?
You need to store the actual image in your database, and then use it in your report. Google for how to store images in a database - I'm sure you will find a large number of hits on CP too.
Using all caps in a message is the equivalent of shouting
|
|
|
|
|
ok...i hvae image path also in my database ..i gvae that field and checked ..but also it is not wrking
|
|
|
|
|
I believe there is a property of the image that can be set to 'External' that allows you to use the value to refer to a location; however, this may only work with a URL location or perhaps in UNC format. Sorry, but I don't recall the name of the property off-hand.
Keep It Simple Stupid! (KISS)
|
|
|
|
|
Anyone have simple source code to work with OleDb database such as write data into table?
thanks
|
|
|
|
|
There are loads of examples on CP and Google. Its quicker to go to google and type "OleDb insert" than it is to post your question here and wait for an answer. If you want to make a living as a developer then remember google is your friend.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Ashfield wrote: Its quicker to go to google and type "OleDb insert"
Had to try: approximately 252 000 hits.
|
|
|
|
|
Mika Wendelius wrote:
Had to try: approximately 252 000 hits.
There is bound to be something very good in all of that
"The clue train passed his station without stopping." - John Simmons / outlaw programmer
"Real programmers just throw a bunch of 1s and 0s at the computer to see what sticks" - Pete O'Hanlon
"Not only do you continue to babble nonsense, you can't even correctly remember the nonsense you babbled just minutes ago." - Rob Graham
|
|
|
|
|
I would guess so
|
|
|
|
|
I am interested in knowing whether it is possible to coordinate two database transaction using Distributed Transaction Coordinator (MSDTC).
Any reference would be good.
Thanks,
Perry
|
|
|
|
|
paresh_joe wrote: whether it is possible to coordinate two database transaction using Distributed Transaction Coordinator
Could you clarify this with a scenario.
modified on Tuesday, December 2, 2008 5:48 AM
|
|
|
|
|
Hello,
Some quick questions to help me in my investigation...
Is it possible for triggers not to execute when the performance is poor?
The scenario is this, we have a trigger on table A that copies data to table B whenever an insert/update is done on table A.
However, when we were having performance problems with the server, it seems like the trigger did not execute. This looks like the case because the data was successfully inserted in table A but it is not found in table B.
What could have been the problem?
Also, is it possible for the trigger to roll back the insert on table A if it fails? How can I do this?
Thank you in advance.
Rafferty
|
|
|
|
|
Rafferty Uy wrote: Is it possible for triggers not to execute when the performance is poor?
No, unless there's a bug in SQL Server.
Rafferty Uy wrote: is it possible for the trigger to roll back the insert on table A if it fails
Yes it is. By executing rollback statement which rolls back the entire transaction.
|
|
|
|
|
I see.
What I meant with roll back is... for example I run a stored procedure AddToTableA, which executes a trigger TableAInsertTrigger.
How can I write TableAInsertTrigger in such a way that, if it fails, it will also roll back the AddToTableA SP?
Rafferty
|
|
|
|
|
|
So, if I get this correctly... for example given the following SP and Trigger
CREATE PROCEDURE AddToTableA() AS
BEGIN
BEGIN TRANSACTION
INSERT INTO TableA VALUES(1, 'SomeValue')
IF (@@ERROR <> 0) GOTO ERR_HANDLER
COMMIT TRANSACTION
RETURN 0
ERR_HANDLER:
ROLLBACK TRANSACTION
END
CREATE TRIGGER TableAInsertTrigger ON TableA FOR INSERT
BEGIN
INSERT INTO TableB (SELECT * FROM INSERTED)
-- do something else here, one that causes error
END
In the above case, if I call AddToTableA() and since TableAInsertTrigger fails, it will roll back the inserts on both TableB (in the Trigger) and TableA (in the SP)?
btw, thank you for helping 
Rafferty
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
