Enterprise Integration Solutions

284 Part IV The Infrastructure of Integration

Private Function GetDate(ByVal query As String) As Date

Dim aSQLConnection As New SqlClient.SqlConnection( _

"Server=localhost;database=Tracking;user id=sa;pwd=")

Dim aSQLCommand As New SqlClient.SqlCommand(query, aSQLConnection)

aSQLConnection.Open()

Dim adapter As New SqlClient.SqlDataAdapter(aSQLCommand)

Dim data As New System.Data.DataSet

adapter.Fill(data)

Dim result As String = data.Tables(0).Rows(0)(0).ToString()

If result <> String.Empty Then

Return DateTime.Parse(result)

Else

Return System.DateTime.Now

End If

End Function

End Class

The code shown here contains four main functions. The basic function,

GetDate, simply accepts a SQL query and returns a date. The GetTotalTime and

GetCTime functions use GetDate to retrieve dates and to add or subtract them as

needed to produce a TimeSpan. Finally, the GetAVGCTime function uses GetCTime

to add up the component times and average them. The only task that remains

is to set the labels on the form to the numbers you have calculated.

Although this example is very simplistic, it illustrates how you can monitor

a system once you start capturing data from each processing step. You can

extend this model by capturing message content in the database. This will give

you an even greater capability to monitor the activity in your system. You might

also consider using a tool such as Microsoft Analysis Services to more com-

pletely monitor and analyze your solution.

In the next section, we will look at how to automatically generate test

messages for the system.

Comparing Entities in Disparate Systems

There are a number of advantages to implementing enterprise integration that

only become apparent late in the development cycle. One such advantage is

the ability to compare entities. In other words, when you have connected a

number of systems by using endpoints, you can query those endpoints and

return specific entity data. You can then compare the entity data from one

system with the corresponding entity data in another system and analyze the

Chapter 12 Managing and Monitoring 285

differences. This allows you to manage and monitor data quality in your inte-

grated systems.

For example, you examine how a customer in system A differs from that

same customer in system B. More importantly, you can update that entity data

in any of the connected systems. For example, if you know that the data for one

customer is correct in system A but incorrect in the remaining systems, you can

publish the correct data from system A to all the remaining systems. From a

data quality perspective, this is an amazing capability.

In this section, we will build a small application named Pet Inventory and

compare information between it and the Microsoft .NET Pet Shop application. To

start, we will create a PetInventory database like the one shown in Figure 12-3.

F12LS04

Figure 12-3 Pet inventory tables.

You need two tables for the application. The first, named Inventory,

holds the current inventory level of items. The second, ReOrder, contains the

item ID and the level of inventory at which you will reorder the product. You

will also add the following stored procedure, get_item_data, to the PetInven-

tory database.

The

get_item_data

Stored Procedure

CREATE PROCEDURE dbo.get_item_data

@ItemID char(10)

AS

SELECT Inventory.itemid, Inventory.qty, ReOrder.ReOrderLevel

FROM Inventory, ReOrder

WHERE Inventory.itemid = @ItemID and Inventory.itemid = ReOrder.itemid

GO

This procedure will return the current number in inventory and the reor-

der level for that product. The next step is to create a simple application that

uses this procedure. Create a new Microsoft Windows application and add the

InventoryItemDA class shown here.

286 Part IV The Infrastructure of Integration

The

InventoryItemDA

Class

Option Explicit On

Option Strict On

Imports System

Imports System.Data.SqlClient

Namespace EISolutions.CH12

Public Class InventoryItemDA

Public Sub New()

'default constructor

End Sub

Public Function GetItemData(ByVal itemID As String) As DataSet

Dim mySqlConnection As New _

SqlConnection( _

"Server=localhost;database=petinventory;user id=sa;pwd=")

Dim mySqlCommand As New SqlCommand( _

"get_item_data", mySqlConnection)

mySqlCommand.CommandType = CommandType.StoredProcedure

mySqlCommand.Parameters.Add("@ItemId", itemID)

mySqlConnection.Open()

Dim myDataAdapter As New SqlDataAdapter

Dim myDataSet As New DataSet("Item")

myDataAdapter.SelectCommand = mySqlCommand

myDataAdapter.Fill(myDataSet, "Item")

mySqlConnection.Dispose()

Return myDataSet

End Function

End Class

End Namespace

This simple data access class returns the System.Data.DataSet for the

InventoryItem class that follows. Next you will add the inventory item business

object.

The

InventoryItem

Class

Option Strict On

Option Explicit On

Imports System

Imports System.Data.SqlClient

Chapter 12 Managing and Monitoring 287

Namespace EISolutions.CH12

Public Class InventoryItem

Dim _id As String

Dim _quantity As Int32

Dim _reorderlevel As Int32

Public Sub New(ByVal itemID As String)

ConstructMe(itemID)

End Sub

Private Sub ConstructMe(ByVal itemID As String)

_id = itemID

Dim aDA As New EISolutions.CH12.InventoryItemDA

Dim aDataset As DataSet = aDA.GetItemData(itemID)

Me.Quantity = Convert.ToInt32(aDataset.Tables(0).Rows(0).Item(1))

Me.ReOrderLevel = _

Convert.ToInt32(aDataset.Tables(0).Rows(0).Item(2))

End Sub

Public ReadOnly Property ID() As String

Get

Return _id

End Get

End Property

Public Property Quantity() As Int32

Get

Return _quantity

End Get

Set(ByVal Value As Int32)

_quantity = Value

End Set

End Property

Public Property ReOrderLevel() As Int32

Get

Return _reorderlevel

End Get

Set(ByVal Value As Int32)

_reorderlevel = Value

End Set

End Property

End Class

End Namespace