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

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

Thanks,

Seth Rowe

Rich wrote:
Show quoteHide quote

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).


Show quoteHide quote

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

Rich wrote:
Show quoteHide quote

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?


Show quoteHide quote

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


Show quoteHide quote

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

Rich

Show quoteHide quote

On 9/14/06 12:50 PM, in article
6953EA35-EC8C-469C-9FFB-BFE6DEEBB***@microsoft.com, "Rich"
<R***@discussions.microsoft.com> wrote:

Show quoteHide quoteFirst, 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()

   .
   .
   .

   If Not boolx Then
      transaction.Rollback()
   Else
      transaction.Commit()
   End If

Catch ex As Exception
   transaction.Rollback()
   Throw
Finally
   If Not transaction Is Nothing Then
      transaction.Dispose()
   End If
   If Not connection Is Nothing Then
      connection.Dispose
   End If
End Try  

A lot of your code was missing; so I kind of had to scrabble together what I
could. As a rule, I avoid DataSets, since I think they're overkill,
especially for table inserts and updates like this. But that's *my* opinion.

I do a lot of this every day where I work. It gets boring and repetitious;
but it guarantees that either *everything* goes into the database, or
nothing. The alternative is to write a stored procedure that manages the
transaction inside the database. But that means learning to manage
transactions in T-SQL. The concrete advantage of that is that it has the
advantage of being faster (because the transaction occurs entirely on the
database server), but the disadvantage is that the learning curve is a bit
steeper.

Still, I'd suggest learning both approaches. The knowledge certainly won't
hurt you. Bear in mind that when you're using transactions, you'll want to
do as much work in them as you can; they're not cheap; it takes a while to
set them up, and if you create a lot of them, the database's transaction log
can get quite large quite fast. Your DBA might not appreciate that.

I hope I've been of some help you you.

Mike Hofer
The Essential Geek: http://mikehofer.spaces.live.com

EXE and Dev environment.
Epson TM-T88 Drawer Kick
string or StringBuilder return ?
Can we Read the text contents from PDF using .net
Reflection, creating object with inherited cunstructor, how?
Change OpenFileDialog Size (width and height)
Subtract Days from a Date
TreeView node remove: what event?
DataBinding to DataGridView AND TextBoxes?
Databound RadioButton