There are many different methods of integrating applications. Selecting a method of integration is based upon which integration interfaces, if any, are exposed. Implementing an adapter framework will keep an application integration platform flexible. You can create different adapters that natively interact with an application. The adapter can turn around and format that data into a normalized representation with which the rest of the integration platform can work.
An integration adapter needs to perform four specific tasks : read a configuration file, access data from an application, format that data into a normalized structure, and communicate data back to the integration server. Adapters also need to be implemented consistently within the integration platform, which is an ideal use of an interface.
The integration adapter interface defines the methods necessary to implement an adapter that plugs into the integration platform. Creating an adapter interface will also be required later for remoting communication between the adapter and the integration server. Begin by creating a new Class Library project named IntegrationCommon . Next, add a new class, named IIntegrationAdapter.cs , to the new project and implement the interface that appears in Listing 12-1.
namespace IntegrationCommon { public interface IIntegrationAdapter { void LoadConfigurationData(); string ReadRecord( int intRecordID ); string ReadAllRecords(); bool WriteRecords( string strData ); string SendToServer( string strData ); } }
The interface comprises five basic methods: LoadConfigurationData, ReadRecord, ReadAllRecords, WriteRecords, and SendToServer. The LoadConfigurationData method loads the adapter-specific settings, such as a connection string to the target database. The ReadRecord method takes an identifier as a parameter, reads that record of data, formats the data as Extensible Markup Language (XML), and sends that XML to the integration service. The ReadAllRecords method performs essentially the same tasks but returns all data records instead of one specific record. The WriteRecords method takes a parameter that contains XML data to be written into the application database. The SendToServer method handles the details of packaging and sending the application-specific XML to the integration server.
The simplest method of application integration is direct database access. In this case, the adapter can use ADO.NET functionality to retrieve data directly from an application's database. Assume the integrated application, DemoApp, has data stored in SQL Server with the table structure shown in Table 12-1.
COLUMN NAME | DATA TYPE |
---|---|
ID | int |
SubmittedOn | datetime |
Priority | char(16) |
Severity | char(16) |
Condition | char(16) |
ShortDescription | char(256) |
LongDescription | text |
ComposedBy | char(256) |
The direct database integration adapter implements the IIntegrationAdapter interface and runs locally on the computer hosting the application to be integrated. Begin by creating a new Windows service, named IntegrationDatabaseAdapter . After you have created the project in Visual Studio, rename the Service1 file to DatabaseAdapterService and change all code references from Service1 to DatabaseAdapterService . Next, add a project reference to the IntegrationCommon assembly. After adding the code reference, modify the class declaration and add the necessary interface methods. Listing 12-2 presents the DatabaseAdapterService class definition with the added interface methods and new class variables . The Windows service code generated by Visual Studio .NET has also been wrapped within #region tags so that it may be conveniently collapsed and hidden from view.
public class DatabaseAdapterService : System.ServiceProcess.ServiceBase, IntegrationCommon.IIntegrationAdapter { private void LoadConfigurationData() { return; } public string ReadRecord( int intRecordID ) { return null; } public string ReadAllRecords() { return null; } public bool WriteRecords( string strData ) { return false; } private string SendToServer( string strData ) { return null; } #region Visual Studio .NET generated Windows service code //... #endregion }
Specifying that this class implements the IIntegrationAdapter interface requires the implementation of the LoadConfigurationData, ReadRecord, ReadAllRecords, WriteRecords, and SendToServer methods. For now, temporary stub methods are inserted into the code to continue testing the Windows service.
Before a Windows service can be started, it will need to include a service installer. To add an installer, open the Windows service source file in the empty form designer. Next, right-click the form and select Add Installer from the context menu. Visual Studio will create a new file (ProjectInstaller.cs) and add two new components (serviceProcessInstaller1 and serviceInstaller2). Next, select the serviceProcessInstaller1 component and open its properties as shown in Figure 12-2. Set the account to LocalSystem to enable the service to be started and logged in under the local system account.
Next, display the properties for the serviceInstaller1 component. Set the display name, such as DatabaseAdapterService, that should be associated with this service when added to the list of services. Then, save and rebuild the service.
You cannot start a Windows service in the Visual Studio .NET debugger. You must first install the service from the .NET command prompt. Click the Windows Start button and select Programs ˜ Microsoft Visual Studio .NET 2003 ˜ Visual Studio .NET Tools ˜ Visual Studio .NET Command Prompt. Next, browse to the output directory where you can find the DatabaseAdapterService.exe file. Enter the following command to install the service:
installutil DatabaseAdapterService.exe
This command installs the new DatabaseAdapterService into the Windows environment. Next, you can start the service either from the Services section of the Control Panel or by entering the following statement at the command prompt:
net start DatabaseAdapterService
Remember to stop the service before changing and rebuilding it within the Visual Studio .NET environment. Uninstalling the service is not necessary when making code changes and rebuilding, but you should uninstall when you finish development to test the setup project. To completely uninstall the service, enter the following:
installutil /u DatabaseAdapterService.exe
The normalized representation of exchanged application data will be XML. One approach to formatting the data is to load the records into a DataSet object, set a filter for the desired records, and create an XML representation of that data as described earlier in Chapter 9, "Using XML and Web Services." A faster approach, however, is to format the data in a SQL SELECT statement using the FOR XML AUTO specifier . Listing 12-3 presents a new stored procedure that extracts a record as XML data.
CREATE PROCEDURE [dbo].[adapter_ReadRecord] ( @ID int ) AS SET NOCOUNT ON; SELECT ID, SubmittedOn, Priority, Severity, Condition, ShortDescription, LongDescription, ComposedBy FROM DemoDat WHERE ID = @ID FOR XML AUTO ; GO
This stored procedure is similar to the one created in Chapter 9, "Using XML and Web Services," in that it uses the FOR XML AUTO statement to produce XML as the result of the query. You can implement the ReadRecord adapter method using this stored procedure and return XML from the DemoApp database. Be sure to add references to the System.XML and System.Data.SqlClient namespaces. Listing 12-4 shows the complete listing for the ReadRecord method.
public string ReadRecord( int intRecordID ) { string stroutput = null; XmlReader reader = null; SqlConnection connection = null; SqlParameter parameter = null; SqlCommand command = null; try { //later, connection string is retrieved from a configuration file connection = new SqlConnection( "workstation id=MONTEREY;packet size=4096;user id=sa;data " + "source=MONTEREY;persist security info=False;" + "initial catalog=DemoApp" ); connection.Open(); command = new SqlCommand( "adapter_ReadRecord", connection ); command.CommandType = CommandType.StoredProcedure; //add the ID parameter parameter = new SqlParameter( "@ID", SqlDbType.Int ); parameter.Direction = ParameterDirection.Input; parameter.Value = intRecordID; command.Parameters.Add( parameter ); reader = command.ExecuteXmlReader(); reader.MoveToContent(); stroutput = reader.ReadOuterXml(); } catch( Exception x ) { EventLog systemLog = new EventLog(); systemLog.Source = "Integration Adapter"; systemLog.WriteEntry( x.Message, EventLogEntryType.Error, 0 ); systemLog.Dispose(); } finally { reader.Close(); connection.Close(); connection.Dispose(); command.Dispose(); } return SendToServer( stroutput ); }
This implementation of the ReadRecord method begins by opening a connection to the database. In a later section, the LoadConfiguration method will be implemented to retrieve this connection string from an external configuration file. After opening the connection, this method fills the stored procedure parameters, creates an XmlReader object, and invokes the query. After processing the stored procedure, the reader reads the produced XML content and passes it to the SendToServer method. The produced XML content appears in Listing 12-5.
<DemoDat ID="2053" SubmittedOn="2004-11-15T00:00:00" Priority="High " Severity="2 " Condition="Open " ShortDescription="Having trouble accessing my voicemail. " LongDescription="Everytime I try to listen to my voicemail, a recording says my password needs to be changed. Even after changing my password, I still can't listen to the messages." ComposedBy="Connie Teed" />
Another type of adapter may not get data from a database but rather from an export file. The original intention might have been to package and export large amounts of data from one computer, send to another location, and then import the data files into another computer.
Although document exchange integration is often considered the least reliable form of integration, it still persists today. Integration through document exchange has also evolved down to dependency upon two prominent document formats: Comma-Separated Values (CSV) and XML.
CSV is one of the oldest and most popular document formats for application integration. The CSV file format is supported by a large number of applications that can read and write data in the CSV file format, including Microsoft Excel. Its popularity lies within its simplicity. Essentially, an application identifies what data it intends to export. Then, the application creates a plain-text file containing multiple lines of data. Each line contains a row of data delimited by the comma character and terminated by a new line character. There is no underlying data structure and no concept of data types in the CSV file format.
Suppose that the DemoApp application exports rows of data into the CSV file AppDemoData.csv. The data structure might look the same with columns that output an ID, a submit date, a priority, a severity, a description, and an author:
ID,SubmittedOn,Priority,Severity,Condition,ShortDescription,ComposedBy 2053,2004-11-15,High,2,Open,Having trouble accessing my voicemail,JP Batson 2054,2004-11-15,Medium,1,Open,Printer always prints,Anke
ADO objects can access this CSV file convert it into XML with the help of a DataSet object. Listing 12-6 implements the ReadAllRecords method for a different Windows service adapter, FileAdapterService, which also implements the IIntegrationAdapter interface. You need to add references to the System.Xml and System.Data.OleDb namespaces.
public string ReadAllRecords() { string strOutput = ""; OleDbConnection connection = null; OleDbDataAdapter adapter = null; DataSet datasetFileData = null; try { connection = new OleDbConnection( "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + "c:\" + ";" + "Extended Properties=\"text;HDR=YES;FMT=Delimited\"" ); adapter = new OleDbDataAdapter( "select ID, SubmittedOn, Priority, " + "Severity, Condition, ShortDescription, ComposedBy from " + "appdemo.csv", connection ); datasetFileData = new DataSet( "DemoDat" ); adapter.Fill( datasetFileData ); strOutput = datasetFileData.GetXml(); } catch( Exception x ) { EventLog systemLog = new EventLog(); systemLog.Source = "Integration Adapter"; systemLog.WriteEntry( x.Message, EventLogEntryType.Error, 0 ); systemLog.Dispose(); } finally { connection.Close(); connection.Dispose(); adapter.Dispose(); datasetFileData.Dispose(); } return SendToServer( strOutput ); }
The ReadAllRecords method opens a connection to the source file using the OleDbConnection object. Next, an OleDbAdapter object is initialized with a query expression and a reference to the connection object. Again, the connection string should come from an external configuration file. The OleDbDataAdapter is used to fill a new DataSet object with the contents of the data file. Finally, the DataSet object's GetXml method produces the structured XML text that is passed to the SendToServer method. Listing 12-7 presents the XML that the ReadAllRecords method creates.
<DemoDat> <Table> <ID>2053</ID> <SubmittedOn>2004-11-15T00:00:00.0000000-08:00</SubmittedOn> <Priority>High</Priority> <Severity>2</Severity> <Condition>Open</Condition> <ShortDescription>Having trouble accessing my voicemail</ShortDescription> <ComposedBy>JP Batson</ComposedBy> </Table> <Table> <ID>2054</ID> <SubmittedOn>2004-11-15T00:00:00.0000000-08:00</SubmittedOn> <Priority>Medium</Priority> <Severity>1</Severity> <Condition>Open</Condition> <ShortDescription>Printer always prints</ShortDescription> <ComposedBy>Anke</ComposedBy> </Table> </DemoDat>
Both adapter services presented should retrieve their settings from an external configuration file to keep the adapters flexible.
The integration adapters access settings are stored in an external configuration file. In Chapter 8, "Developing Desktop Applications," application settings were stored in the App.config XML file and retrieved using the XPath objects. The adapters store configuration settings, such as a database connection string or properties related to the export file from which to read.
You can process incoming files with the FileSystemWatcher control. Open FileAdapterService in its design mode. Next, drag the FileSystemWatcher control from the Components tab of the Toolbox into the design space and name the control _DirectoryWatch . Next, add an event handler for this control named DirectoryWatch_Changed that handles the Changed event. Listing 12-8 implements the Changed event handler. When changes are detected to directories or files matching the criteria specified by the FileSystemWatcher control, this event handler is notified and supplied with information about the changed file or directory. In this case, the ReadAllRecords method is invoked to perform the specific file processing.
private void DirectoryWatch_Changed(object sender, System.IO.FileSystemEventArgs e) { //read the file and send to the server ReadAllRecords(); return; }
Next, you need to set the FileSystemWatcher properties from the configuration file. Create a new text file named App.config in the same directory as the Windows service source code. Next, enter the XML definition that appears in Listing 12-9 and add the file to the FileAdapterService project.
<?xml version="1.0" encoding="utf-8" ?> <configuration> <appSettings> <add key="ConnectionString" value="workstation id=MONTEREY; packet size=4096;user id=sa; data source=MONTEREY;persist security info=False; initial catalog=DemoApp" /> <add key="Directory" value="c:\" /> <add key="FileType" value="*.csv" /> </appSettings> </configuration>
This application configuration file looks different from the one created in Chapter 8, "Developing Desktop Applications." Rather than defining custom configuration tags using the <configSections> element and then accessing values with the help of XPath, these values are stored as simple name-value pairs within the normal <appSettings> region and accessed with the AppSettingsReader object. Add a reference to the System.Configuration namespace to access the AppSettingsReader object. Listing 12-10 implements the LoadConfigurationData method that retrieves the adapter settings.
public void LoadConfigurationData() { AppSettingsReader settings = new AppSettingsReader(); _DirectoryWatch.Filter = (string)settings.GetValue( "FileType", typeof(string) ); _DirectoryWatch.Path = (string)settings.GetValue( "Directory", typeof(string)); return; }
This method reads the adapter's configuration settings and sets the values for the adapter service. The AppSettingsReader object retrieves the values of various adapter settings based on key name. In this case, the code specifies the type of file to look for and the directory to search.