#副標=商務處理與整合 (5) #大標=協調流程的例外處理及補償 #眉標=BizTalk Server 2006 #作者=文/彭靖灝 ==========box 程式1=========== _ Public Class FlowControl Private ConnectionString As String = "data source=(local);” & _ initial catalog=MicTest;integrated security=sspi" Public Sub DummyCall() If IsError() Then Throw New FlowControlException("There is something worng!") Else ' Do nothing End If End Sub Private Function IsError() As Boolean Dim result As Boolean Dim scn As New SqlConnection(ConnectionString) Dim scm As New SqlCommand Dim sql As String = "SELECT ConfigValue FROM Configurations " & _ "WHERE ConfigKey='IsError'" With scm .Connection = scn .CommandText = sql .CommandType = CommandType.Text End With Try scn.Open() Dim ConfigValue As String ConfigValue = CType(scm.ExecuteScalar(), String) If ConfigValue.ToLower = "yes" Then result = True Else result = False End If Catch ex As Exception Throw New ApplicationException(ex.Message) Finally scn.Close() End Try Return result End Function End Class ==================end=================== =========box 程式2========= Imports System.Data Imports System.Data.SqlClient Imports System.EnterpriseServices _ Public Class TitleTxDBHelper Inherits ServicedComponent Public Sub New() MyBase.New() End Sub Private ConnectionString = "data source=(local);" & _ "initial catalog=MicTest;integrated security=sspi" _ Public Function GetTitle(ByVal TitleID As Int32) As TitleInfo Dim scn As New SqlConnection(ConnectionString) Dim scm As New SqlCommand() Dim sql As String = "SELECT * FROM Titles WHERE TitleID={0}" Dim result As TitleInfo With scm .Connection = scn .CommandType = CommandType.Text .CommandText = String.Format(sql, TitleID.ToString) End With Try Dim dr As SqlDataReader scn.Open() dr = scm.ExecuteReader If dr.HasRows Then dr.Read() result = New TitleInfo(TitleID, _ dr("TitleName"), dr("Author"), dr("Publisher")) dr.Close() Else result = Nothing End If Catch ex As Exception Throw New ApplicationException(ex.Message) Finally scn.Close() End Try Return result End Function Public Function UpdateTitle(ByVal ExistTitle As TitleInfo) As Boolean Dim scn As New SqlConnection(ConnectionString) Dim scm As New SqlCommand() Dim sql As String = "UPDATE Titles SET TitleName=@TitleName, " & _ "Author=@Author, Publisher=@Publisher WHERE TitleID=@TitleID" Dim result As Boolean If GetTitle(ExistTitle.TitleID) Is Nothing Then result = False Else With scm     .Connection = scn .CommandType = CommandType.Text .CommandText = sql .Parameters.Add(New SqlParameter("@TitleName", _ SqlDbType.NVarChar, 50)).Value = ExistTitle.TitleName .Parameters.Add(New SqlParameter("@Author", _ SqlDbType.NVarChar, 50)).Value = ExistTitle.Author .Parameters.Add(New SqlParameter("@Publisher", _ SqlDbType.NVarChar, 50)).Value = ExistTitle.Publisher .Parameters.Add(New SqlParameter("@TitleID", _ SqlDbType.Int)).Value = ExistTitle.TitleID End With Try scn.Open() scm.ExecuteNonQuery() result = True ContextUtil.MyTransactionVote = TransactionVote.Commit Catch ex As Exception ContextUtil.MyTransactionVote = TransactionVote.Abort Throw New ApplicationException(ex.Message) Finally scn.Close() ContextUtil.DeactivateOnReturn = True End Try End If Return result End Function End Class ================end============== ===========box 程式3========== _ Public Class TitleDBHelper Private ConnectionString = "data source=(local);" & _ "initial catalog=MicTest;integrated security=sspi" Public Function AddTitle(ByVal NewTitle As TitleInfo) As Int32 Dim scn As New SqlConnection(ConnectionString) Dim scm As New SqlCommand() Dim sql As String = "INSERT INTO Titles " & _ "(TitleName, Author, Publisher) " & _ "VALUES (@TitleName, @Author, @Publisher);SET @TitleID=@@Identity" Dim result As Int32 With scm .Connection = scn .CommandType = CommandType.Text .CommandText = sql .Parameters.Add(New SqlParameter("@TitleName", _ SqlDbType.NVarChar, 50)).Value = NewTitle.TitleName .Parameters.Add(New SqlParameter("@Author", _ SqlDbType.NVarChar, 50)).Value = NewTitle.Author .Parameters.Add(New SqlParameter("@Publisher", _ SqlDbType.NVarChar, 50)).Value = NewTitle.Publisher .Parameters.Add(New SqlParameter("@TitleID", _ SqlDbType.Int)).Direction = ParameterDirection.Output End With Try scn.Open() scm.ExecuteNonQuery() result = scm.Parameters("@TitleID").Value Catch ex As Exception Throw New ApplicationException(ex.Message) Finally scn.Close() End Try Return result End Function Public Sub DeleteTitle(ByVal TitleID As Int32) Dim scn As New SqlConnection(ConnectionString) Dim scm As New SqlCommand() Dim sql As String = "DELETE Titles WHERE TitleID={0}" With scm .Connection = scn .CommandType = CommandType.Text .CommandText = String.Format(sql, TitleID.ToString) End With Try scn.Open() scm.ExecuteNonQuery() Catch ex As Exception Throw New ApplicationException(ex.Message) Finally scn.Close() End Try End Sub End Class =============end=========