Figure 2 Add New Value

Article
06/25/2008

  
create procedure sp_AddNew(@f int)
as
begin transaction

 declare @tmpID int
 declare @tmpID1 int

 insert into A values(@f)
 If @@ERROR  <> 0 GoTo ErrorHandler
 select @tmpID= @@IDENTITY

 insert into C values (@f)
 If @@ERROR  <> 0 GoTo ErrorHandler
 select @tmpID1= @@IDENTITY

 insert into B values (@tmpID, @tmpID1)
 If @@ERROR  <> 0 GoTo ErrorHandler

commit transaction

Return (0)

ErrorHandler:
 rollback transaction
 Return  (-13)
go

Figure 4 Business Transaction Implementation

  
ComponentT ("Requires a new transaction" or "Requires a transaction"):
//"business object"
//that means, it "covers" business transaction
ComponentT::T_Add (parameters) //looks like sp_AddNew!
{
IObjectContext* pObjectContext = NULL;
BSTR stSomeRole = SysAllocString(L"SomeRole");
ICompA* pCompA = NULL;
ICompC* pCompC = NULL;
ICompB* pCompB = NULL;
HRESULT hr;
...
// Get the object's ObjectContext.
hr = GetObjectContext(&pObjectContext);

// security is enabled?.
if (pObjectContext->IsSecurityEnabled()) 
{
  //Find out if the caller is in the right role.
  hr = pObjectContext->IsCallerInRole(stSomeRole, &fIsInRole)
  if (fIsInRole)
  {// Ok
  ...
  }
  else
  {// Raise error.
  ...
  }
}
...

// Use the object's ObjectContext to instantiate CompA object.
hr = pObjectContext->CreateInstance(CLSID_CompA,
    IID_ICompA, (void**)&pCompA); 
// Call A_Add method to insert into A.
pCompA->A_Add (parameters); 
...
// Use the object's ObjectContext to instantiate CompC object.
hr = pObjectContext->CreateInstance(CLSID_CompC,
    IID_ICompC, (void**)&pCompC); 
// Call C_Add method to insert into C.
pCompC->C_Add (parameters); 
...
// Use the object's ObjectContext to instantiate CompB object.
hr = pObjectContext->CreateInstance(CLSID_CompB,
    IID_ICompB, (void**)&pCompB); 
// Call B_Add method to insert into B.
pCompB->B_Add (parameters); 
...
    
if (NOERROR)
{
// Commit MTS/DTC transaction.
  pObjectContext->SetComplete();
}
else
{
// Rollback MTS/DTC transaction.
  pObjectContext->SetAbort();
...
}
}

Figure 5 Component Code Outline

  
ComponentA ("Requires a transaction" or "Supports transactions") 
//"data access object",
//that means, it "covers" table A
ComponentA::A_Add (parameters)
{
...
//ADO code to insert into the table A
//return @tmpID
...
hr = GetObjectContext(&pObjectContext); 
if (NOERROR)
{
// Continue.
  pObjectContext->SetComplete();
}
else
{
// GoTo "ErrorHandler"
  pObjectContext->SetAbort();
}
...
}

Figure 6 ComponentB and ComponentC

  
ComponentC ("Requires a transaction" or "Supports transactions") 
//"data access object",
//that means, it "covers" table C
ComponentC::C_Add (parameters)
{
...
//ADO code to insert into the table C
//return @tmpID1
...
hr = GetObjectContext(&pObjectContext); 
if (NOERROR)
{
// Continue.
  pObjectContext->SetComplete();
}
else
{
// GoTo "ErrorHandler"
  pObjectContext->SetAbort();
}
...
}

ComponentB ("Requires a transaction" or "Supports transactions") 
//"data access object",
//that means, it "covers" table B
ComponentB::B_Add (parameters)
{
...
//ADO code to insert into the table B using @tmpID and @tmpID1
...
hr = GetObjectContext(&pObjectContext); 
if (NOERROR)
{
// Continue.
  pObjectContext->SetComplete();
}
else
{
// GoTo "ErrorHandler"
  pObjectContext->SetAbort();
}
...
}

Figure 7 Business Transaction in Visual Basic

  
ComponentT ("Requires a new transaction" or "Requires a transaction")
Public Sub T_Add(parameters)
Dim objA as ComponentA
Dim objB as ComponentB
Dim objC as ComponentC
On Error goto ErrorHandler

    If (GetObjectContext.IsSecurityEnabled and _
    GetObjectContext.IsCallerInRole("SomeRole")) Then
    ' Ok
    Else
    ' Raise error.
    End If

    Set objA = GetObjectContext.CreateInstance("ComponentA")
    objA.A_Add (parameters)

    Set objC= GetObjectContext.CreateInstance("ComponentC")
    objC.C_Add (parameters)

    Set objB = GetObjectContext.CreateInstance("ComponentB")
    objB.B_Add (parameters)

    GetObjectContext.SetComplete
    Exit Sub
ErrorHandler:
     GetObjectContext.SetAbort
End Sub

Figure 8 Data Access Components

  
ComponentA ("Requires a transaction" or "Supports transactions")
Public Sub A_Add (parameters)
On Error goto ErrorHandler
    'ADO code to insert into the table A
    'return @tmpID
    ...
    GetObjectContext.SetComplete
    Exit Sub
ErrorHandler:
     GetObjectContext.SetAbort
End Sub

ComponentC ("Requires a transaction" or "Supports transactions")
Public Sub C_Add (parameters)
On Error goto ErrorHandler
    'ADO code to insert into the table C
    'return @tmpID1
    ...
    GetObjectContext.SetComplete
    Exit Sub
ErrorHandler:
     GetObjectContext.SetAbort
End Sub

ComponentB ("Requires a transaction" or "Supports transactions")
Public Sub B_Add (parameters)
On Error goto ErrorHandler
    'ADO code to insert into the table B using @tmpID and @tmpID1
    ...
    GetObjectContext.SetComplete
    Exit sub
ErrorHandler:
     GetObjectContext.SetAbort
End Sub

Figure 9 Business Transaction Template

  
ComponentT ("Requires a new transaction" or "Requires a transaction")
Public Sub T_Add(parameters)
Dim obj1 as ComponentT1
Dim obj2 as ComponentT2
    ...
Dim objN as ComponentTN
On Error goto ErrorHandler

    Set obj1 = GetObjectContext.CreateInstance("ComponentT1")
    'data access component "ComponentT1" inserts a record into table T1
    obj1.T1_Add (parameters)

    Set obj2 = GetObjectContext.CreateInstance("ComponentT2")
    'Insert into table T2
    obj2.T2_Add (parameters)
    ...
    Set objN = GetObjectContext.CreateInstance("ComponentTN")
'Insert into table TN
    objN.TN_Add (parameters)

    GetObjectContext.SetComplete
    Exit Sub
ErrorHandler:
     GetObjectContext.SetAbort
End Sub

Figure 10 Data Component Implementation

  
'ComponentA ("Requires a transaction" or "Supports transactions")
Public Sub A_Add(ByVal lngi As Long, ByVal lngf1 As Long) 
Dim strSQL As String
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
 
On Error GoTo ErrorHandler
'Form the SQL INSERT statement.  Use the ADO command object to make the 
'SQL command
strSQL = "INSERT INTO A(i, f1) VALUES (?, ?)" 
Set cmd = New ADODB.Command
cmd.CommandText = strSQL
cmd.Parameters.Append cmd.CreateParameter(, adInteger, adParamInput, , 
    lngi)
cmd.Parameters.Append cmd.CreateParameter(, adInteger, adParamInput, , 
    lngf1) 
' Open the connection object and execute the SQL command to the database.
Set conn = New ADODB.Connection
conn.Open "FILEDSN=" & someFileDSN
Set cmd.ActiveConnection = conn
cmd.Execute
'  Allow MTS transaction set to proceed.
GetObjectContext.SetComplete
Exit Sub
ErrorHandler:
'   Clean up objects on the way out.
If Not cmd Is Nothing Then
    Set cmd = Nothing
End If
If Not conn Is Nothing Then
    Set conn = Nothing
End If
'    Roll back MTS transaction set.
GetObjectContext.SetAbort
End Sub

Figure 11 Alternative Data Component

  
Public Sub A_Add(ByRef lngi As Long, ByVal lngf1 As Long)
...
cmd.CommandText = "sp_A_Add" 'Stored proc inserts into A, see below
cmd.Parameters.Append cmd.CreateParameter("Return", adInteger, 
    adParamReturnValue, , 0)
cmd.Parameters.Append cmd.CreateParameter("@f1", adInteger, adParamInput, 
    , lngf1)
cmd.Parameters.Append cmd.CreateParameter("@ID", adInteger, 
    adParamOutput, , 0)
cmd.Execute
If cmd("Return") = 0 Then 'Ok,
    lngi = cmd("@ID") ' return a new ID
Else
    lngi = -1
End If
...
'  Allow MTS transaction set to proceed.
GetObjectContext.SetComplete
Exit Sub
ErrorHandler:
...
'  Roll back MTS transaction set.
GetObjectContext.SetAbort
End Sub

Figure 12 Generic Data Component Template

  
Public Sub SomeTable_Add(ByVal typePrefixCol1 As Col1type, 
    ByVal typePrefixCol2 As Col2type, ..., typePrefixColN As ColNtype)
...
strSQL = "INSERT INTO SomeTable (Col1 , Col2, ..., ColN)  VALUES(?, ?, 
    ..., ?)"
...
cmd.Parameters.Append cmd.CreateParameter(,ADOCol1type, adParamInput, 
    ADOCol1typeLen, typePrefixCol1)
cmd.Parameters.Append cmd.CreateParameter(,ADOCol2type, adParamInput, 
    ADOCol2typeLen, typePrefixCol2)
...
cmd.Parameters.Append cmd.CreateParameter(,ADOColNtype, adParamInput, 
    ADOColNtypeLen, typePrefixColN)
...
End Sub

Figure 13 Stored Procedure Component Template

  
Public Sub SomeTable_Add(ByRef typePrefixCol1 As Col1type, 
    ByVal typePrefixCol2 As Col2type, ..., typePrefixColN As ColNtype)
...
'Stored proc inserts into SomeTable, see below
cmd.CommandText = " sp_SomeTable_Add" 
cmd.Parameters.Append cmd.CreateParameter("Return", adInteger, 
    adParamReturnValue, , 0)
cmd.Parameters.Append cmd.CreateParameter("@Col2", ADOCol2type, 
    adParamInput, ADOCol2typeLen, typePrefixCol2)
...
cmd.Parameters.Append cmd.CreateParameter("@ColN", ADOColNtype, 
    adParamInput, ADOColNtypeLen, typePrefixColN)
cmd.Parameters.Append cmd.CreateParameter("@ID", adInteger, 
    adParamOutput, , 0)
cmd.Execute
If cmd("Return") = 0 Then 
    typePrefixCol1 = cmd("@ID") 'Success
Else
    typePrefixCol1 = -1 'Error
End If
...
End Sub

Figure 14 Stored Procedure Template

  
Create procedure sp_SomeTable_Add
@Col2 Col2Type,
@Col3 Col3Type,
...
@ColN ColNType,
@ID int OUT  a new ID
As
 insert into SomeTable values(@Col2, @Col3, ..., @ColN)
 If @@ERROR  <> 0 GoTo ErrorHandler
 select @ID = @@IDENTITY
 Return (0)
ErrorHandler:
 Return  (-13)
go

Figure 2 Add New Value

Additional resources