Thursday, March 22, 2007

.NET: Pushing Microsoft Excel, Access or SQL Server Data using Multiple Interfaces

Note: This article is written by me and was published in the BlackBerry Developers Journal in July 2005


This article will describe how to push data from Microsoft® Excel, Microsoft Access and Microsoft SQL Server to BlackBerry devices.

It builds on the article, .NET: A Simple C# Push Application and will demonstrate how to push Emergency Contact List (ECL) data to BlackBerry devices, and how to push ECL to BlackBerry devices by using three different interfaces depending on need.

  1. ECL Service Application

  2. This is like a Windows® Service, which performs a push at a regular interval to BlackBerry devices.
  3. ECL Command Line application

  4. A command line application that performs a push once to all BlackBerry devices.
  5. ECL GUI Application

  6. A Graphical User Interface (GUI) application that performs a push once to BlackBerry devices.

These interfaces depend on the ECL Logic Engine (ECLLogic.dll) to process the inputs and perform the push.

We will take all of the push logic described in the last article, put it in a Dynamic Link Library (DLL) file, and then use these interfaces to gather information from the user and pass it to the DLL so it can do the work for us.

Topics within this section include:

There are two push types that can be performed using the Browser application as detailed below.

Browser Channel Push

Browser channel push is used where the server encodes the contact data in an HTML page and pushes the page to the BlackBerry Browser. When the BlackBerry Browser receives a Channel Push, it stores the pushed content in its cache and adds an icon to the main ribbon that acts as a bookmark to that page. In this form, the program on the device is the BlackBerry Browser, so there is no ECL-specific code that needs to be installed on the device. Arrange to have a copy of the "ECLv2/Server/content" folder published as a web resource. You will need to make it possible for a BlackBerry device to fetch a URL such as "http://internal57.yourcompany.com:2303/ecl/content/ecl_unread_icon.gif" to obtain the data from "content\ecl_unread_icon.gif". You can set this property in the GUI and in Settings.config for the Service and Command Line Application.

For example.

[WebContextRoot]/ecl_unread_icon.gi.
[WebContextRoot]/ecl_read_icon.gi.
[WebContextRoot]/ecl.html --.

Client Catcher

Client catcher is used when the server encodes the contact data into a non-standard format then sends it to a custom application that must be installed on the user's BlackBerry device. When the catcher receives a pushed message, it stores the encoded data locally using the BlackBerry persistence framework. The user interface component of the BlackBerry device displays its locally stored copy of the contact list in a collapsible tree form.

Open the Application Loader file "ECLv2\Device\bin\ECLContactList.alx" with your BlackBerry Desktop Manager software and follow the procedure for installing the program on your USB connected device. Once installed, the catcher will run in the background. Alternatively, if using the simulator, open the workspace.

ECLv2\Device\src\Device\ECLContactList.jd.

... in the BlackBerry Java Development Environment (JDE) and press F5 to launch the BlackBerry device simulator. Make sure that the MDS Simulator is running too.

Customizing the Example

There are two kinds of customizations that are easily made to this example.
  1. Making it read a different spreadsheet/database format
  2. Changing how the list gets displayed on the BlackBerry device

It is important to understand how the server program reads data from the Excel workbook to make this demonstration work with different spreadsheet formats.

The server is "hard coded" to understand the arrangement of cells found in XYZ Company's spreadsheet/database table. It scans the worksheet from top to bottom, searching for rows that contain data in the first column but not in the second. Such rows are interpreted as group headings. Other rows are interpreted as people within the most recently started group. The data from all used cells in a person's row become the line items that make up the person's record when it is displayed on the device.

When the server program is processing a person's row and encounters a used cell, it applies the formatting rule for the appropriate column when generating that field's line item. Note that the spreadsheet heading row is ignored. Thus, as long as the new format is similar enough to the old, you can adapt the server to read a differently formatted spreadsheet by adjusting these properties.

The server software must be changed to read a drastically different format. All the logic for parsing spreadsheets is in "DataReader.cs". This component accesses the spreadsheet using the row/column interface provided by OLEDB. While the server issues SQL commands over OLEDB, it is not using a relational database paradigm; rather, it is using the scanning algorithm described earlier.

Another straightforward customization would be to modify how the contact list gets displayed on the device. This can be done in Settings.config file or GUI.

Open the following workspace in the BlackBerry JDE to alter the BlackBerry Handheld Software.

ECLv2\Device\srcECLContactList.jdw

After you have made the desired changes, you can run the result in the simulator. You must sign your compiled application to deploy on a real BlackBerry device because it uses RIM's persistence and networking APIs.

This demo includes a pre-signed build in "ECLv2\Device \bin\ECLContactList.cod" that can be installed without change.

You should be able to make changes you need to the look-and-feel of the device app by only minor edits to the code in "ECLv2\Device\src\ECLApplication.java".

"ECLv2\Device\src\PushedDataListener.java" is a fairly generic push listener, while "ECLv2\Device\src\DataStore.java" handles lower-level data management.

Note that there are fairly high degrees of coupling between "ECLv2\Device\src\DataStore.java" and "ECLv2\Server\src \CustomAppPusher.cs" since these classes jointly define the application's over-the-air transmission format.

Now that we have an understanding of the architecture, let's review the code.

The GUI Application has one class, PushDialog.cs. The main function is used to validate UI values and send them to ECLLogic.

The Command Line Application also has one class, CommandLine.cs. The main function is used to validate UI values and send them to ECLLogic.

The Windows Service Application has two classes.

  1. ECLService.c.
  2. ProjectInstaller.c.

The main function of ECLService is used to validate UI values then sends them to ECLLogic.

The ProjectInstaller.cs class handles the installation of a service.

These classes are used to get inputs and locations of data sources and pass them on to ECLLogic, which has all the Push Logic.

Now let us see how ECL Logic works.

Here is what happens:

Step 1

The interface class gets information from the user and then pass it to ECLLogic.cs. The ECLLogic.cs class has a struct in which the value is passed. The struct is called ECLConfiguration and can be seen below. This structure is used to send Connection information. It has twelve variables.

public struct ECLConfiguration
{
//File containing user information
//(Server Port Email/Pin)
public String recipientFile;

//Push Destination(Channel/Catcher)
public String PushTo;

//has a value 0 (Browser-Channel)
//or 1 (Browser-Channel-Delete)
public int PushType;

//This can be Access,Excel,MSSQL
public String ConnectionType;

//Database name,used in case of MS SQL
public String Database;

//File name(.xls or .mdb) or Server Name
//(used in MSSQL)
public String File_Server_Name;

//Worksheet Name(Used in Excel)
//or Table name (used in Access or MSSQL)
public String Sheet_Table_Name;

//The format in which data is displayed
//on the handheld
public String[] ColumnFormat;

//The location where the html file should
//stored after it has been formed
public String fileStoreLoc;

//The http:\\ URL where the read and
// unread icon files are
public String WebRoot;

//The name to be displayed as Channel Title
//on the handheld
public String ChannelName;

//The Source of the input(GUI/Console/Service)
public String Source;
}

Step 2

ECLLogic.cs receives the structure and then creates an instance of BrowserChannelPusher.cs or CustomAppPusher.cs. It then checks if the push type is Browser-Channel/ Browser-Channel Delete.

Pusher pusher = null;

if (config.PushTo == "Channel")
{
log.Info("Push To: Channel");

// Construct a pusher that sends to a
// browser channel.
pusher = new BrowserChannelPusher();
}
else if (config.PushTo == "Catcher")
{
log.Info("Push To: Custom Catcher");

// Construct a pusher that sends to a
// custom catcher.
pusher = new CustomAppPusher();
}

It then parses the recipient's list file to get user information and stores it in ArrayList.

ArrayList recipientEmails = getRecipients(config.recipientFile);

Step 3

If the push type is Browser-Channel then it calls DataReader.cs to access the data. While data is being accessed, it is also translated into HTML/comma delimited format by calling methods of the Pusher.cs class. All data sources have different connection strings whose configuration information is passed along with the ECLConfiguration struct from the interface classes.

//if Push Type is Browser-Channel
if(config.PushType==0)
{
// Open the data source from which we
// will read in contact data.
dataReader = new DataReader();

log.Info("Opening Data Source for retreiving data");
dataReader.openDB(config);

//Fetch the group names
log.Info("Getting list of group names");

System.Collections.ArrayList groupDescription = dataReader.GroupList(config);

// Assemble the data of all contacts (from all
// groups) that we will push to handhelds.
log.Info("Getting user contact information");

for (int j=0;j
{
System.String[] dataFields = dataReader.getContactData((System.String)
groupContactList[j],config);
pusher.addContact(dataFields);
}
}

// Log the message we just built
log.Info("Finished building the push message with contacts in " +
groupDescription.Count + " groups. " );
}

pusher.finishedConstruction(config.fileStoreLoc);

Step 4

After the data has been extracted and translated, all that is required is to push the data to the BlackBerry device. To accomplish this, loop the recipients list to send data.

// Push the message we just built to
// all recipients.
for (int i = 0; i <>
{
SenderInfo curRecipient = (SenderInfo) recipientEmails[i];

try
{
...
//Sends the message
success=pusher.sendToHandheld(curRecipient,config.WebRoot,
config.ChannelName,config.PushType);
...
}

The sendToHandheld method sets up the URL and the HTTP connection and
sends the data to the BlackBerry device.

3 comments:

Troy said...

Hi,

I recently came across your blog and have been reading along. I thought I would leave my first comment. I don't know what to say except that I have enjoyed reading.

Nice blog. I will keep visiting this blog very often.

-
Blackberry development

Android app development said...

This is one of the effective post.try to get more this kind of information.This is one of the pleasurable post.
Android app developers

Lily Taylor said...

Its a very good blog on the topic of BlackBerry development. I haven't read any interesting article like this one, Its pretty worth enough for me to visit this blog and read the valuable information.

Top 4 Dental Marketing Strategies and Ideas that can work for your dentistry clinic.