There are over 500 S.P.'s that have been written already. Alot of
times we(.net devs) use 1 to many of the sp's in a exe. it would be
nice to be able to "wrap" the sp's in a ado transaction.
Subman
SP Follows
ALTER PROCEDURE dbo.proc_Bajio_SalesPosting
AS
SET NOCOUNT ON
--Declarations
DECLARE @_StatusString VARCHAR(200)
DECLARE @_ProcessTime DATETIME
DECLARE @_WED DATETIME
DECLARE @_StartTime DATETIME
DECLARE @_MyRowCount INTEGER
DECLARE @_MyError INTEGER
DECLARE @_SalesPostingRunning INTEGER
DECLARE @_TERST varchar(10)
--Start The Transaction
BEGIN TRANSACTION SalePosting
SELECT @_StatusString = 'Trans count ' +
CONVERT(VARCHAR(50),@@TRANCOUNT)
PRINT @_StatusString
-- Start logging progress
SELECT @_StatusString = 'Started Stored
Procedure(proc_Bajio_SalesPosting) At - ' +
CONVERT(VARCHAR(50),GETDATE())
PRINT @_StatusString
SELECT @_ProcessTime = GETDATE()
SELECT @_StartTime = @_ProcessTime
-- Verify all sales have been entered
CREATE TABLE #MissingStores
(FranchiseNum INTEGER)
--Log Progress
SELECT @_MyError = @@ERROR, @_MyRowCount = @@RowCount
IF @_MyError <> 0
BEGIN
SELECT @_StatusString = 'STEP 10|ERROR! Creating #MissingStores
(Rolling Back) - Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
GOTO ErrorHandler
END
ELSE
BEGIN
SELECT @_StatusString = 'STEP 10|Created #MissingStores - Total
Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
SELECT @_ProcessTime = GETDATE()
END
-- Get current WeekEndingDate
SET @_WED = (SELECT TOP 1 WeekEndingDate FROM dbo.WeekEndingDate)
--Log Progress
SELECT @_MyError = @@ERROR, @_MyRowCount = @@RowCount
IF @_MyError <> 0
BEGIN
SELECT @_StatusString = 'STEP 20|ERROR! Setting WED Variable (Rolling
Back) - Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
GOTO ErrorHandler
END
ELSE
BEGIN
SELECT @_StatusString = 'STEP 20|Set WED Variable - Total Minutes - '
+
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
SELECT @_ProcessTime = GETDATE()
END
-- Check if the sales posting is already running
SELECT @_SalesPostingRunning = AvailableInd
FROM SubwayNet.dbo.ObjectGroup
WHERE ObjectGroupID = 1 -- Sales Posting
--Log Progress
SELECT @_MyError = @@ERROR, @_MyRowCount = @@RowCount
IF @_MyError <> 0
BEGIN
SELECT @_StatusString = 'STEP 30|ERROR! Setting Sales Posting
Variable (Rolling Back) - Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
GOTO ErrorHandler
END
ELSE
IF @_SalesPostingRunning <> 1
BEGIN
SELECT @_StatusString = 'Sales Posting Currently Running (Rolling
Back) - Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
GOTO ErrorHandler
END
ELSE
BEGIN
SELECT @_StatusString = 'STEP 30|Set Sales Posting Variable - Total
Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
SELECT @_ProcessTime = GETDATE()
END
-- populate the temp table with all stores that are missing sales
INSERT INTO #MissingStores
EXEC proc_Bajio_Franchise_GetAllByMissingSales
--Log Progress
SELECT @_MyError = @@ERROR, @_MyRowCount = @@RowCount
IF @_MyError <> 0
BEGIN
SELECT @_StatusString = 'STEP 40|ERROR! Populating #MissingStores
(Rolling Back) - Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
GOTO ErrorHandler
END
ELSE
IF @_MyRowCount > 0
BEGIN
SELECT @_StatusString = 'Not All Stores Sent Sales (Rolling Back) -
Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
GOTO ErrorHandler
END
ELSE
BEGIN
SELECT @_StatusString = 'STEP 40|Inserted Into #MissingStores -
Total Minutes - ' +
CONVERT(VARCHAR(50),CONVERT(NUMERIC(19,3),DATEDIFF(SECOND,@_ProcessTime,GETDATE()))/60)
+ ' ROWS AFFECTED(' + convert(varchar(50),@_MyRowCount) +')'
PRINT @_StatusString
SELECT @_ProcessTime = GETDATE()
END
COMMIT TRANSACTION SalePosting
RETURN 0
-- Error Handler Subroutine
ErrorHandler:
BEGIN
SELECT @_StatusString = CONVERT(VARCHAR(50),@@TranCount)
PRINT @_StatusString
IF @@TRANCOUNT = 1
BEGIN
ROLLBACK TRANSACTION
END
SELECT @_StatusString = CONVERT(VARCHAR(50),@@TranCount)
PRINT @_StatusString
RETURN -1
END
Test Code Follows
Imports System.Data.SqlClient
Imports Microsoft.ApplicationBlocks.Data
Public Class Form1
Inherits System.Windows.Forms.Form
#Region " Windows Form Designer generated code "
Public Sub New()
MyBase.New()
'This call is required by the Windows Form Designer.
InitializeComponent()
'Add any initialization after the InitializeComponent() call
End Sub
'Form overrides dispose to clean up the component list.
Protected Overloads Overrides Sub Dispose(ByVal disposing As
Boolean)
If disposing Then
If Not (components Is Nothing) Then
components.Dispose()
End If
End If
MyBase.Dispose(disposing)
End Sub
'Required by the Windows Form Designer
Private components As System.ComponentModel.IContainer
'NOTE: The following procedure is required by the Windows Form
Designer
'It can be modified using the Windows Form Designer.
'Do not modify it using the code editor.
Friend WithEvents SqlConnection1 As
System.Data.SqlClient.SqlConnection
Friend WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand
Friend WithEvents Button1 As System.Windows.Forms.Button
Friend WithEvents RichTextBox1 As System.Windows.Forms.RichTextBox
Friend WithEvents Button2 As System.Windows.Forms.Button
<System.Diagnostics.DebuggerStepThrough()> Private Sub
InitializeComponent()
Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand
Me.Button1 = New System.Windows.Forms.Button
Me.RichTextBox1 = New System.Windows.Forms.RichTextBox
Me.Button2 = New System.Windows.Forms.Button
Me.SuspendLayout()
'
'SqlConnection1
'
Me.SqlConnection1.ConnectionString = "workstation
id=""IS-WARAKOMSKI"";packet size=4096;integrated security=SSPI;data
sou" & _
"rce=""SPINOZA\SPINOZA2000"";persist security info=True;initial
catalog=Bajio"
'
'SqlCommand1
'
Me.SqlCommand1.CommandText =
"dbo.[proc_Bajio_SalesPosting03022006]"
Me.SqlCommand1.CommandType =
System.Data.CommandType.StoredProcedure
Me.SqlCommand1.Connection = Me.SqlConnection1
Me.SqlCommand1.Parameters.Add(New
System.Data.SqlClient.SqlParameter("@RETURN_VALUE",
System.Data.SqlDbType.Int, 4,
System.Data.ParameterDirection.ReturnValue, False, CType(0, Byte),
CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))
'
'Button1
'
Me.Button1.Location = New System.Drawing.Point(8, 16)
Me.Button1.Name = "Button1"
Me.Button1.TabIndex = 0
Me.Button1.Text = "Button1"
'
'RichTextBox1
'
Me.RichTextBox1.Location = New System.Drawing.Point(8, 64)
Me.RichTextBox1.Name = "RichTextBox1"
Me.RichTextBox1.Size = New System.Drawing.Size(904, 96)
Me.RichTextBox1.TabIndex = 1
Me.RichTextBox1.Text = ""
'
'Button2
'
Me.Button2.Location = New System.Drawing.Point(776, 192)
Me.Button2.Name = "Button2"
Me.Button2.TabIndex = 2
Me.Button2.Text = "Button2"
'
'Form1
'
Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
Me.ClientSize = New System.Drawing.Size(912, 266)
Me.Controls.Add(Me.Button2)
Me.Controls.Add(Me.RichTextBox1)
Me.Controls.Add(Me.Button1)
Me.Name = "Form1"
Me.Text = "Form1"
Me.ResumeLayout(False)
End Sub
#End Region
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
Dim tnn As SqlTransaction = Nothing
Dim strConnectionString = "data
source=devsql.subapp.subwaydai.com\Spinoza2000;initial
catalog=Bajio;user iD=Subs;password=sixears;packet size=4096"
Try
AddHandler SqlConnection1.InfoMessage, New
SqlInfoMessageEventHandler(AddressOf OnInfoMessage)
SqlConnection1.ConnectionString = strConnectionString
SqlConnection1.Open()
tnn = SqlConnection1.BeginTransaction()
SqlCommand1.Transaction = tnn
SqlCommand1.CommandText =
"dbo.proc_Bajio_SalesPosting03022006"
SqlCommand1.ExecuteNonQuery()
' SqlCommand1.CommandText =
"dbo.proc_Bajio_SalesPosting03022006_dropme"
' SqlCommand1.ExecuteNonQuery()
' SqlHelper.ExecuteNonQuery(tnn,
SqlCommand1.CommandText, SqlHelper.enuTimeOut.Default, Nothing)
tnn.Commit()
Catch Ex As SqlException
If ex.Number <> 266 Then
tnn.Rollback()
End If
Catch Ex As Exception
tnn.Rollback()
Finally
If Not tnn Is Nothing Then tnn.Dispose()
SqlConnection1.Close()
End Try
End Sub
Private Sub OnInfoMessage(ByVal sender As Object, ByVal args As
SqlInfoMessageEventArgs)
Try
Dim err As SqlError
For Each err In args.Errors
RichTextBox1.Text += ControlChars.CrLf & err.Message
Next
Catch ex As Exception
End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim strConn As String =
"Server=jalapeno;Database=Northwind;Integrated Security=True;"
Dim cnn As New SqlConnection(strConn)
Dim cmd As New SqlCommand
cnn.Open()
Dim trans As SqlTransaction = Nothing
Try
trans = cnn.BeginTransaction()
' @AccountNo (From Account)
Dim paramFromAcc As New SqlParameter("@AccountNo",
SqlDbType.Char, 20)
paramFromAcc.Value = "12345"
' @AccountNo (To Account)
Dim paramToAcc As New SqlParameter("@AccountNo",
SqlDbType.Char, 20)
paramToAcc.Value = "67890"
' @Money (Credit amount)
Dim paramCreditAmount As New SqlParameter("@Amount",
SqlDbType.Money)
paramCreditAmount.Value = 500
' @Money (Debit amount)
Dim paramDebitAmount As New SqlParameter("@Amount",
SqlDbType.Money)
paramDebitAmount.Value = 500
With cmd
.Connection = cnn
.CommandType = CommandType.StoredProcedure
.Transaction = trans
.CommandText = "Debit"
.Parameters.Add(paramFromAcc)
.Parameters.Add(paramDebitAmount)
.ExecuteNonQuery()
End With
With cmd
.Connection = cnn
.CommandType = CommandType.StoredProcedure
.Transaction = trans
.CommandText = "Credit"
.Parameters.Add(paramToAcc)
.Parameters.Add(paramCreditAmount)
.ExecuteNonQuery()
End With
trans.Commit()
Catch ex As Exception
' throw an exception
trans.Rollback()
Throw ex
Finally
If Not trans Is Nothing Then trans.Dispose()
End Try
End Sub
End Class
