how to rollback a procedure (sqlserver) from VB2005?

[Guest]

Hello,

I have an SP that inserts data into a sqlserver table. If a condition is
not met within the SP after the data has been inserted into the table, the SP
will rollback the insert.

If I were to write the same procedure as inline sql in a VB2005 app

cmd.CommandText = "Insert Into tbl1 Values(' & txt1.Text & ')"
....
da.Update(ds, "tbl1")

how do I implement a rollback from the app if some condition is not met?

pseudocode:
....
da.Update(ds, "tbl1")
....
If boolx.Equals(False) Then rollback da.Update(ds, "tbl1")

Thanks,
Rich

 

[rowe_newsgroups]

Why not just call the stored proc from VB.Net?

Thanks,

Seth Rowe

 

[Cor Ligthert [MVP]]

Rich,

You have to use the Connection. transaction methods for that
http://windowssdk.msdn.microsoft.com/en-us/library/1756xwa3.aspx

or the transaction class

http://windowssdk.msdn.microsoft.com/en-us/library/4zx2yex3.aspx

I hope this helps,

Cor

 

[Guest]

Thanks. Yes, this helps. I forgot about the connection object, and the
transaction object.

Rich

 

[Guest]

Actually, I am already calling the SP from the app. But I did not write the
SP. The actual SP is quite lengthy and complex and calls several other SPs
within the SP. I am looking to simplify this SP, and it looks like writing
the Sql inline in the VB2005 app is much easier to control than having them
in a bunch of SPs on the server. The primary SP rolls back the transaction
if any of the inner SPs fail.

My issue is that this particular DB has hundreds of SPs, and this one
particular SP contains like 30 -50 sub Sps which are all over the place. My
plan is to write each of these sub SPs as inline sql in my app. If one of
them fails I go to Catch and rollback the entire transaction. Oh, and some
of the SPs are superfluous - so having everything inline, I can eliminate the
superfluous SPs (ones that write temp data to temp tables I can store those
inline in a dataTable object).

 

[rowe_newsgroups]

Won't passing all of the SPs as inline SQL hurt you performance wise
(as SQL Server can't optimize them)?

Just Curious - I'm in a similar circumstance with an app I'm working on
(it too uses a boat load of stored procedures)

Thanks,

Seth Rowe

 

[Guest]

Maybe it is because I am simplifying some of the stored procedures (that I
inherited - a project that inherited) that they seem to run faster from the
app than the whole procedure in sqlserver. Well, I am not dealing with
millions of records at this time, so I am not seeing any performance hits by
running sql inline in the app. I was just tired of having to wade through
hundreds of SPs to locate the ones I needed to work on. Will be upgrading to
sqlserver2005 from 2000 in November. Wonder if they have a directory
structure I could use to store the SPs in a more orderly fashion?

 

[Michael Hofer]

Hello,

I have an SP that inserts data into a sqlserver table. If a condition is
not met within the SP after the data has been inserted into the table, the SP
will rollback the insert.

If I were to write the same procedure as inline sql in a VB2005 app

cmd.CommandText = "Insert Into tbl1 Values(' & txt1.Text & ')"
...
da.Update(ds, "tbl1")

how do I implement a rollback from the app if some condition is not met?

pseudocode:
...
da.Update(ds, "tbl1")
...
If boolx.Equals(False) Then rollback da.Update(ds, "tbl1")

Thanks,
Rich

First, I'd try the obvious approach. Test if the conditions are met *before*
you update the database. But that's the obvious approach, and you knew that.


The obvious approach failing, you'd need to use a transaction (likely a
System.Data.SqlClient.SqlTransaction). Essentially, you either want it all
to succeed, or none of it. The code looks something like this:

Dim connection As SqlConnection
Dim transaction As SqlTransaction
Dim command As SqlCommand
Dim boolx As Boolean

Try
connection = GetConnection()
connection.Open()

transaction = connection.BeginTransaction()

command = Connection.CreateCommand()
command.CommandType = CommandType.Text
command.CommandText = "Insert Into tbl1 Values(' & txt1.Text & ')"
command.Transaction = transaction
command.ExecuteNonQuery()