The Paperless Push

Disclaimer: This blog post was made by Ravindra (Ravi) Dhole, not by me. Ravi has been building Crystal Report and .NET plugins for me since December 2007. He knows this stuff well. In the example below, Ravi explains how to build a Crystal Report plugin that hooks into an Adesso database. The objective of this plugin is to produce a customized printed report that retrieves data stored in a local Adesso database and displays it in specific locations on the report. By duplicating the steps outlined below, you should be able to create your own plugin that essentially harvests data stored in your own Adesso database. Sample code and development guides can be downloaded from the links below.

Download Sample Code
Adesso Plug-in Development Guide
Adesso ODBC Driver Guide

1. Steps to Create Crystal Report For Adesso
Adesso only support .Net framework 2.0. So we have to design and implement the crystal report using VS2005. Here we are using VS2005 with C#.Net.
1.1. Prerequisites
Ø Installed VS2005
Ø Installed Adesso Client
Ø Adesso ODBC Driver to connect to local application
Ø Need to read the instruction of Adesso Plug-in Development Guide and Adesso ODBC Driver Guide.

1.2. Create Class Library Project
Ø Open VS2005
Ø Go to File > New Project
Ø Select Visual C# > Windows
Ø Create Class Library Project. For example. Project Name is PrintWSWTableReport. Please refer screen below

Screen 1: Create Project

Ø Once class library project is created then you can find one class1.cs file. Rename this class file equivalent to project name. For example PrintWSWTablePlugin.cs.
Ø Add the references of three dlls which you can find in your Adesso installer directory
Path: C:\Program Files\Adesso Systems\Adesso\
Dll names are
· Adesso.Interop.AdessoDataObjectsLib.dll
· Adesso.Interop.TBSYNCLib.dll
· AdessoInterfaces.dll

Screen 2: Add Adesso References

Ø Add following reference of Adesso in class file.
using Adesso;
Ø Change Namespace from PrintWSWTableReport to Adesso.Client. This is important step. The plug-in will not work with any other namespace name. Plug-in class file must be present under Adesso.Client Namespace.
Ø Implement two interfaces MarshalByRefObject, IExtensionHandler within this class with following methods.
This is important step in Adesso plug-in development.

string IExtensionHandler.Handler(string filename)
{
return “”;
}

string IExtensionHandler.GetFieldNames(string filename)
{
return “”;
}

string IExtensionHandler.GetParameterNames(string filename)
{
return “”;
}
Refer screen below

Screen 3: Implement Adesso Interface Methods

1.3. Design Dataset
Ø Create two folders in project file. One with DataAccess name and another with Reports. This is not compulsory to create these folders. It just for sort out the code files.
Ø Now next step is to create dataset .xsd file. For this right click on DataAccess folder Add > New Item and select Dataset file. Here we will rename this dataset file name as DataSetWSWTable.xsd. Add new data table in this xsd file. Name of data table should be same as name of table in Adesso. Also name of columns in data table should be same as names in Adesso table. Refer screen below

Screen 4: Design Dataset

Ø Once Data set .xsd file is created then next step is to create crystal report.

1.4. Design Report
Ø For crystal report Right Click on Reports folder Add > New Item and select CrystalReport file. We will name this file as CRWSWTableReport.rpt. Once you click on OK button then it will add that rpt file under reports folder and prompt for report wizards. Refer screen below

Screen 5: Add Crystal Report

Screen 6: Crystal Report Wizards Prompt

Ø Check Using the Report Wizards and select Standard as report type and click on OK button.
Ø Next step is to set dataset for this crystal report. For this navigate to Project Data > ADO.NET Datasets. You can find dataset this section which we created in Design Dataset section. Here you can add whole dataset or particular table. Here we are adding whole dataset to rpt files. Refer screen below

Screen 7: Select Dataset

Screen 8: Assign dataset for rpt file.

Ø Click on next button. It will display added tables with relationship. May be you can clear this relationship between table if needed. Click on Clear Link to clear all relationship. Refer screen below

Screen 9: Clear Relationship between Tables

Ø Click on yes button and finally click on Finished button.
In Field Explorer, It will display list of added tables. You can just drag and drop the table columns in rpt file and design the report as per your requirement.
Note: How to design the crystal report not covered in this section. This portion is left for developer.
Refer screen below

Screen 10: Design Crystal Report

1.5. Fill Dataset and Display Crystal Report
Ø You can refer ODBC_Driver.pdf for more details.
Ø Once dataset design and report design is done then next step is to fill dataset from Adesso tables. Here we can use Adesso ODBC Driver to connect to local application. Now again back to our Plug-in class file.
Ø When user clicks on button(in Adesso form) then it Adesso execute the IExtensionHandler.Handler method. It sent the event handler xml file path to this method. Event handler xml always created in temp directory. Refer Adesso_Plug-In_Development_Guide.pdf for event handler xml structure.
Event Handler XML Path: C:\Documents and Settings\[System User]\Local Settings\Temp\ AdessoEventHandler_4212.xml. 4212 number is generated based on application. This number is different for different applications.
Adesso Plug-in directory Path: C:\Documents and Settings\[System User]\Local Settings\Temp\My Adesso Plug-Ins
Ø Then parse the xml and take application name and password which are needed for connection string.
string applicationName = doc.SelectSingleNode(“//EventHandler/@Name”).Value;
string password = doc.SelectSingleNode(“//EventHandler/@Password”).Value;
Ø Here Password is in base64 format. So convert that password from base64 to normal string.
Ø Take out the temp directory path from event handler xml.
Ø Then make connection string and fill dataset using ODBC Driver
Ø Always return while building the crystal report. It indicates that there is no error plug-in execution
Class file code is as below.

using System;
using System.Collections.Generic;
using System.Text;
using Adesso;
using System.Windows.Forms;
using System.Collections;
using PrintWSWTableReport.DataAccess;
using PrintWSWTableReport.Reports;
using System.Data;
using CrystalDecisions.Shared;
using System.Data.Odbc;
using System.Xml;
using System.IO;
using CrystalDecisions.CrystalReports.Engine;

namespace Adesso.Client
{
public class PrintWSWTablePlugin : MarshalByRefObject, IExtensionHandler
{
public String strConnectionString;
private ReportDocument rpt;
XmlDocument doc = new XmlDocument();
FileStream fsReadXml = null;

//Adesso Driver connection string
private const string LOCAL_CONNECTIONSTRING_FORMAT = “DRIVER=AdessoSQL Driver;type=local;file={0};pwd={1}”;
private static DataSetWSWTable dsWSWTable = new DataSetWSWTable();
#region IExtensionHandler Members

// This is actual event handler method which is executed when user click on button.
string IExtensionHandler.Handler(string filename)
{
try
{
try{
//Read event handler file
fsReadXml = new FileStream(filename, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
}
catch (IOException)
{
MessageBox.Show(“Could not load xml schema”);
return “”;
}
doc.Load(fsReadXml);
fsReadXml.Close();

//take application path and password from event handler xml
string applicationName = doc.SelectSingleNode(“//EventHandler/@Name”).Value;
string password = doc.SelectSingleNode(“//EventHandler/@Password”).Value;

//System.Windows.Forms.MessageBox.Show(“Application Name: ” + applicationName + ” Password: “+password);

//Make connection string
SetConnectionParameters(applicationName, password);

// Get the root node of the XmlDocument
XmlNode root = doc.DocumentElement;
//doc.Save(“F:\\ConcretePlacement.xml”);
// Get thte last child node of the XmlDocument
XmlNode eventNode = root.LastChild;

// Determine the path to where the Plug-In lives so we can get to the dependency files.
XmlAttributeCollection attributeCollection = eventNode.Attributes;
XmlAttribute attribute = attributeCollection["Assembly"];

//Take out the temp directory path
string path = attribute.Value;
int idx = path.LastIndexOf(@”\”);
if (idx != -1)
{
path = path.Remove(idx + 1, path.Length – (idx + 1));
}

//Take project number from event handler xml
string projectNo = GetProjectNo(root);
string pdfPath = path + projectNo + “.pdf”;

//fill dataset
FillReportDataset(projectNo);

//Assign report parameters
rpt = new CRWSWTableReport();
rpt.SetDataSource(dsWSWTable);
rpt.PrintOptions.PaperOrientation = PaperOrientation.Landscape;
rpt.PrintOptions.ApplyPageMargins(new PageMargins(350, 150, 150, 0));

//Export report as PDF on disk
rpt.ExportToDisk(ExportFormatType.PortableDocFormat, path + projectNo + “.pdf”);

//Open PDF in Acrobat reader
PDFOpenFileDialog(pdfPath);
}
catch(Exception ex)
{
MessageBox.Show(ex.Message.ToString());
}
//always return while building the crystal report. It indicates that there is no error plug-in execution
return “”;
}

// This is adesso event handler method. It always return blank value
string IExtensionHandler.GetFieldNames(string filename)
{
return “”;
}

// This is adesso event handler method. It always return blank value.
string IExtensionHandler.GetParameterNames(string filename)
{
return “”;
}

#endregion

// This method open the PDF in Acrobat Reader.
public void PDFOpenFileDialog(string pdfPath)
{
try
{
System.Diagnostics.Process.Start(pdfPath);
}
catch (Exception ex)
{
throw ex;
}
}

// This method read project number data from event handler xml and return it.
string GetProjectNo(XmlNode inputData)
{
String projectNo = inputData.SelectSingleNode(“//EventHandler//Table[@Name='Project']//Field[@Name='project_no']“).InnerText;
return projectNo;
}

// This method decode the password and make a connection string
private void SetConnectionParameters(string applicationName, string encodedPassword)
{
strConnectionString = String.Format(LOCAL_CONNECTIONSTRING_FORMAT, applicationName, Decode(encodedPassword));
}

// This method converts base64 format string in to normal strign and return it
private string Decode(string base64EncodedString)
{
try
{
byte[] todecode_byte = Convert.FromBase64String(base64EncodedString);
return System.Text.Encoding.Unicode.GetString(todecode_byte);
}
catch
{
return “”;
}
}
// This method fill dataset based on project number
private void FillReportDataset(string projectNo)
{
dsWSWTable.Clear();
try
{
OdbcConnection conn = new OdbcConnection(strConnectionString);
conn.Open();
//Project Data
OdbcCommand cmdProject = new OdbcCommand(“Select * from [Project] where [Project].[project_no]=’” + projectNo + “‘”, conn);
OdbcDataAdapter daProject = new OdbcDataAdapter(cmdProject);
daProject.Fill(dsWSWTable, “Project”);

//WSW Data
OdbcCommand cmdWSW = new OdbcCommand(“Select [WSW].*,[Project].[incident_no],[Project].[site_name],[Project].[DENR_incident_mgr] from [WSW],[Project] where [WSW].[project_no]=[Project].[project_no] and [WSW].[project_no] =’” + projectNo + “‘”, conn);
OdbcDataAdapter daWSW = new OdbcDataAdapter(cmdWSW);
daWSW.Fill(dsWSWTable, “WSW”);
}
conn.Close();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
}
}

1.6. Plug-in Creations in Adesso
Ø You can refer Adesso_Plug-In_Development_Guide.pdf for more details.
Ø Once we build the project then you can find class library project dll in bin/debug directory. For Example. PrintWSWTableReport.dll
Ø Open the Adesso application in Adesso client.
Ø Go to Tools > Application Designer Area
Ø Go to Plug-in selction.
Ø Click on new button for creations of new plug-in. Enter name of Plug-in click on OK button. Refer screen below

Screen 11: Create New Plug-in

Ø Once you click on OK button then we have to add more details related to plug-in. If we and to display crystal report on button click then checked checkbox of Button under Can be used as: section. Then under resources section, for Resource Dropdown, browse for our project dll. For example. PrintWSWTableReport.dll. Once this dll is added then it will prompt for Class name selection. Select Adesso.Client.PrintWSWTablePlugin. If there any dependencies other than Adesso.Interop.AdessoDataObjectsLib.dll, Adesso.Interop.TBSYNCLib.dll and AdessoInterfaces.dll these DLL’s then you can add those dependencies from Dependencies Section
Refer screen below

Screen 12: Select Plug-in DLL

Ø Click on OK button to save this plug-in
Ø Next step is too hooked this plug-in on button. So go to Form section of Application Designer. It is assume that you already design the forms for each table and added Print WSW Table Report as button on Project form. Click on button, it will display properties for button where you can specify Action as Run as Plug-in. Hooked this plug-in on button from which you want to display crystal report. Here we are applying Plug-in on projects form which will print all associated records from WSW table. Refer screen below

Screen 13: Hooked Plug-in on button

Ø Click OK button and save the form
Ø Now you can run this Plug-in from project form which will display crystal report in crystal report viewer or you can create PDF from RPT. Refer screen below

Screen 14: Run Report

Ø When user click on button then it execute the plug-in and display the report in report viewer or it will create PDF from RPT file. Here we have created the report as PDF. Refer screen below

Screen 15: Printed report in PDF format

Tip: How to test the crystal report in Adesso application.

Ø Add MessageBox.Show(“test print function.”); method in plugin file ( Eg. PrintWSWTablePlugin.cs file) where you want to check the code is run properly or not.
Ø Always use square bracket [] in adesso query for eg. select [project].[project_name] from [project] where [project].[project_no] = 5