Rollback Transaction - Insert multiple rows

[Ms.Longstocking]

Hello,
I have a stored procedure that updates my table from values entered in a datatable in my windows app.

An error occurs 1/2 way through the update process. I assumed that by implementing the rollback transaction command that the inserted lines would not be saved to my db. This is not the case. Here is my code, where am I going wrong?


ALTER PROCEDURE [dbo].[spUploadUser]
(@userid varchar(10), @username varchar(50), @userstatus varchar(20))
AS
BEGIN
SET NOCOUNT ON;
DECLARE @ERROR_STATE INT;
BEGIN TRANSACTION
INSERT INTO userprofile (uid, uname, ustatus)
VALUES @userid, @username, @userstatus;
SELECT @ERROR_STATE = @@ERROR;
IF (@ERROR_STATE <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN -1
END
ELSE
COMMIT TRANSACTION
END


Regards,
MizPippz

[szlamany]

I see one insert in that SPROC -what other inserts or updates are happening?

The BEGIN TRAN and ROLLBACK/COMMIT is in that single SPROC - right??

[Ms.Longstocking]

I should have elaborated some more....

Basically what this stored proc should do (and it does work if there are no errors along the way) is insert a number of rows into my table.

So let's say that I insert 100 new rows into my dataset that's resident in my windows app, and then update my db by calling the stored proc.

What is happening is this:
Let's say row 47 (from my 100 new rows in my datatable) contains data that my db doesn't like and an error is thrown. Lines 1-46 are committed and 47-100 are not.

If I try wrapping the INSERT statement in my stored proc in a TRY/CATCH block, only row 47 is not committed and the rest are.

What I need is for ALL the rows to be rolled back should anything go wrong during the entire life of the stored proc.

I could use some guidance on how to proceed because evidently I'm not versed enough to get this working.

Kind Regards,
MizPippz

[szlamany]

Well - I'll offer some ideas and hopefully others will come along and give opinions...

You could use your COMMAND OBJECT's .BEGINTRANS and .COMMIT or .ROLLBACK methods to manage all 100 transactions from the client side.

Before the first transaction - .BeginTrans - loop through all 100 and if success .CommitTrans or otherwise .RollbackTrans.

btw - I never use these methods - but know they are around (check my spelling of them).

At any rate - I generally don't like holding locks from the client - if you pop an error message on the screen, for instance, you hold the lock on the table for way too long.

When I get presented with this type of situations I put my "set-based" thinking into place. I create a #TEMP table - insert one row at a time into it - when I complete the insert of all 100 rows I do

Insert into UserProfile Select * From #UserProfileTemp

This does the entire operation in a way that SQL likes - one big old transaction in one simple SQL action query.

And now you don't have to worry about BEGIN TRANS or COMMIT or ROLLBACK at all. All 100 rows are going to make it or none are - naturally.

You have reduced the SQL to a single query - that puts a capital A on the ATOMICITY of ACID

[Ms.Longstocking]

That's an interesting way to go about it. However, I'm iffy on creating a temp table and the ensuing overhead to accomplish this.

And here I thought this would be simple.

Lemme know if a brainstorm hits you or if you know of any good forums that specialize in T-SQL.

Regards,
MizPippz

P.S. - thanks for the reply

[szlamany]

...this might be the best T-SQL forum - I've not yet found a better one

Just to keep the conversation going...

You are tasked with keeping your transactions as short as possible - that's a pretty standard SQL rule to live by.

Now if you were going to call two insert SPROCS - I could see (barely though - it hurts me) starting the BEGINTRANS in VB - calling the two SPROCS - and then either COMMIT'ing or ROLLBACK'ing the pair in VB.

Respecting that rule above means you are careful to not "pause" after the transaction begins.

Now - you have a 100 rows. Probably in a loop. Loops are iterative - SQL is set based. How do you marry those differences and make a happy outcome.

Since you are basically using the database to "scrub" your data - one row at a time - you should consider how the database could scrub the whole lot in one statement.

btw - what is the source of this data?

also - creating a temp table is not expensive.

[Ms.Longstocking]

I think what's going on here is that I'm missing a fundamental way on how data is written to the db.

I'm not sure how versed you are in VB.NET, but here's my call of the stored proc from within my windows app:

Try
     Dim cmd As New SqlCommand()
     cmd.Connection = connect
     cmd.CommandType = Data.CommandType.StoredProcedure
     cmd.CommandText = "spUpdateUser"
     cmd.Connection = connect
     paramTemp = cmd.Parameters.Add("@userid", SqlDbType.Char)
     paramTemp.SourceColumn = "userid"
     paramTemp.SourceVersion = DataRowVersion.Current
     paramTemp = cmd.Parameters.Add("@username", SqlDbType.Char)
     paramTemp.SourceColumn = "username"
     paramTemp.SourceVersion = DataRowVersion.Current
     paramTemp = cmd.Parameters.Add("@userstatus", SqlDbType.Char)
     paramTemp.SourceColumn = "userstatus"
     paramTemp.SourceVersion = DataRowVersion.Current
     da.InsertCommand = cmd
     connect.Open
     da.Update(ds, "users")
     bs.DataSource = Nothing
     ds.Tables("users").Clear
Catch ex As Exception
     MsgBox(ex.Message)
Finally
     connect.Close()
     connect.Dispose()
     connect = Nothing
End Try

Basically, my datatable, "users", has 100 new rows that need to be copied over to an existing table in my db.

This is the working part of my stored proc:
BEGIN
INSERT INTO userprofile (uid, uname, ustatus)
VALUES @userid, @username, @userstatus;
END

Here is where I'm probably missing something. I'm not sure if internally the inserts are done in one batch or if they're looped through one at a time.

If they're being inserted one at a time, would using an alternate method, such as a bulk insert, be more appropriate?

Now, from what I've gathered, there's no easy way to write 0 rows should there be a glitch along the way. If creating a temp table is the way to go, so be it, I'll go with that. It's just that I've asked a handful of people and I'm getting a different opinion on how to proceed from each and every one of them. Discouraged? Yep.

Regards,
ConfusedPippz

[techgnome]

The answer is, yes... if there are 100 rows in the datatable to be updated, 100 calls to the database are executed - one for each row. If you are worried about a "glitch" part way through, then=, yes you will need a transaction.... BUT... not in the SP... it needs to be done in the code.

     connect.Open
Dim dbtrans As Transaction = connect.beginTransaction
     da.Update(ds, "users")
     bs.DataSource = Nothing
     ds.Tables("users").Clear
dbTrans.CommitTransaction
Catch ex As Exception
     MsgBox(ex.Message)
dbTrans.RollbackTransaction
Finally
     connect.Close()
     connect.Dispose()
     connect = Nothing
End Try

Hope that helps.

-tg

[szlamany]

@tg - that's no different then VB6 - as I suggested.

but keep in mind that this is wrong

Catch ex As Exception
     MsgBox(ex.Message)
dbTrans.RollbackTransaction

it needs to rollback before the msgbox - really important

It should be

Catch ex As Exception
dbTrans.RollbackTransaction
     MsgBox(ex.Message)

[Ms.Longstocking]

Yep. Looks like that did it. I'm not sure if it's the most efficient way of doing things.... but if the shoe fits....

Thanks guys for your input. Afer 2 days I can finally put this issue at rest and not grind my teeth at night.

I, and my dentist, thank you both.

Kind Regards
MizPippz

P.S. - Would a bulk insert result in just one call to the db for the entire batch?

[szlamany]

P.S. - Would a bulk insert result in just one call to the db for the entire batch?

Yes - exactly.

If this was in a TDF or CSV file first - then BULK INSERT would have been the preferred method. We use that in dozens of import routines.

We also have dozens of older import routines that use the insert-one-at-a-time method.

The BULK INSERT in a sql script/sproc or whatever is easier to enhance and modify in the future since the DML logic is in T-SQL instead of VB.Net. Although I guess LINQ is supposed to bring sql-like syntax into vb

[jmcilhinney]

Actually, 100 new rows does not necessarily mean 100 visits to the database. If you set the UpdateBatchSize property of your DataAdapter to 100 then there will be one visit to the database. The default value is 1, so one record at a time is saved. A value of 0 means a single batch is executed regardless of the number of pending changes. Any other value sets the maximum size of a batch. Large batches can decrease performance so if you want to use this property optimally some testing is required.