Using Silverlight with Access

By Derrick VanArnam, Software Developer and Russell Fox, Database Administrator

This post will show how to integrate Silverlight into an Access database, providing interoperability between Access and a Silverlight Treeview control. The methods outlined below will allow you to use Silverlight's impressive set of controls, including the Treeview, DataGrid, Video, and charts, in your Microsoft Office applications.

Incorporating Silverlight into your existing Access application can breathe new life into an aging system. Also, Silverlight is designed for the web with WCF service support deeply backed into the platform, giving you the ability to blend Silverlight and the Cloud into an Access database. The Silverlight HTML Bridge feature makes this possible. On the Access side, we have the Web Browser Control (WBC), native to Access 2010. Silverlight integration into Access is made possible by placing an Access WBC onto a Form and loading an HTML page containing a Silverlight control.

This integration is based on the Observer design pattern wherein an Access Web Browser Control and Silverlight are event publishers and event listeners.  In addition, Silverlight supports creating scriptable methods that can be called from the WBC.

This blog post will show how to use Silverlight in Access. However, the concepts presented apply to any client application that supports the WBC.

Sample Project

To show the concept, we have a project at http://DesktopWeb.CodePlex.com.

The scenario adds a Silverlight web browser page to a form within the Access Project Management sample database. The database has local tables; however, the tables could be linked to a MyDocuments SharePoint list. Clicking a Document Tasks ribbon button opens a My Document Tasks form hosting the Web Browser Control that has a Silverlight Treeview control.

Clicking a document task category fires an onclick event. Access handles the click event by changing a Tab Page.

Clicking an Access button calls the Silverlight Treeview.AddItem() method that adds a Treeview item.

To Setup the sample database

To install the sample database:

•        Go to http://desktopweb.codeplex.com/releases/view/54874#DownloadId=162278.

•        Click the I Agree button on the To download … popup.

•        Run the MyDataAddinsetup.msi file.

            Note: You can save then run the msi. In addition, source code is available on desktopweb.codeplex.com.

•        Follow the setup steps.

Note

The addin solution was created on Windows 7 64 Bit; therefore, the setup location will be at C:\Program Files (x86)\desktopWeb\MyDataAddinSetup.

                Change the Web Browser Control ControlSource to the following on Windows 32 Bit:

  ="C:\Program Files\desktopWeb\MyDataAddinSetup\DocumentPanelPage_Modified.html"

To view the Access sample database

Open the sample database at

C:\Program Files (x86)\desktopWeb\MyDataAddinSetup\Project Management.accdb

Project Management Sample

Sample Ribbon

After opening the sample database:

•        Click the MyDocuments ribbon tab

•        Click the Document Tasks ribbon button to open the sample My Documents Tasks form

To use the sample My Documents Tasks form

•        Click My Tasks, Blog or Links within the Silverlight Treeview to navigate to the corresponding Tab Page

•        Click the Add button to add a Treeview Item to the Silverlight Treeview

Access Command Button calling a Silverlight method

The Access Web Browser can get an HTML element and call a Silverlight script method. Conversely, Access can handle a Silverlight originated event by creating an event delegate on any of the DOMDocument events. For example, you can create an HTMLDocument.onclick event delegate to handle a document click.

Use the execScript method on the HTMLDocument window object to call a Silverlight method from the Access Web Browser Control. For example, this code  calls the AddItem method:

                "silverlightControl.Content.silverlightScriptableClass.AddItem('New Item');”

 The following code snippet shows how this is done. Silverlight methods are asynchronous; therefore, the AddItem method fires an ondataavailable event when the method completes. The sample database immediately fires the event. This blog is for concept demonstration purposes only. A full treatment of asynchronous WCF calls is beyond the scope of this post.

Access Addin

Access CommandButton calling a Silverlight method and Silverlight ondataavailable event

void newCommand_Click()

{

MyDocumentTasks.TreeView.AddItem(MyDocumentTasks.ItemText.Value);

}

//Silverlight Event Subscriber Delegate

//Maps to HtmlDocumentEvent_ondataavailable

void TreeView_ondataavailable(object sender, AsyncEventArgs e)

{

MessageBox.Show("TreeView_ondataavailable Event: " + e.Value, "Silverlight

 Sample");

}

Treeview AddItem Silverlight Script

public class Treeview … {

    public void AddItem(string value)

    {

string script =

  "silverlightControl.Content.silverlightScriptableClass

  .AddItem('" + value + "');";

      

this.HtmlDocument.parentWindow.execScript(script);

    }

 

    //Silverlight "silverlightControlHost.fireEvent('ondataavailable')"

    void HtmlDocumentEvent_ondataavailable(mshtml.IHTMLEventObj pEvtObj)

    {

      if (this.ondataavailable != null)

      {

       this.ondataavailable(this, new

         AsyncEventArgs(pEvtObj.srcElement.getAttribute("value")));

      }

    }

}

Silverlight

Silverlight Treeview AddItem method with ondataavailable event

[ScriptableMember]

public void AddItem(string text)

{        

//Call method

treeView.Items.Add(text);

//Set EventPublisher Value

       System.Windows.Browser.HtmlPage.Document.GetElementById("silverlightControlHost")

            .SetAttribute("value", "value added by Silverlight method");

//Fire ondataavailable event

HtmlPage.Window.Eval("silverlightControlHost.fireEvent('ondataavailable')");

}

Diagram 1 – Access Web Browser Control calling Treeview.AddItem

Silverlight can fire events that the Access WBC can handle. A Silverlight User Control can get an HTML element and fire a DOM event, such as onclick, that a WBC element click delegate handles. Silverlight is the Event Publisher or Subject and the Access WBC is the Event Listener or Observer. Diagram 2 illustrates the concept:

•        Get the silverlightControlHost element using Silverlight HTML Bridge.

•        Set an event attribute allowing the observer to get data from the DOM .

•        Call HTMLWindow.Eval() to fire silverlightControlHost element onclick event.

•        An EventHandler defined from the Access WBC handles the silverlightControlHost onclick event.

Note

Any DOM event can be fired from Silverlight and handled by the WBC DOM element object.

Diagram 2 – Silverlight Event Publisher, Access Web Browser Control Event Listener

Silverlight Event Subscriber Delegate

Access Web Browser Control

Treeview Click Delegate (Silverlight Event Subscriber Delegate)

//Silverlight Treeview Event

//Maps to HtmlDocumentEvent_onclick

void TreeView_onclick(object sender, TreeviewItemEventArgs e)

{

MyDocumentTasks.TabControl.Value = e.Tag;

}

Treeview DocumentEvent_onclick delegate

//Silverlight delegate from "silverlightControlHost.fireEvent('onclick')"

bool HtmlDocumentEvent_onclick(mshtml.IHTMLEventObj pEvtObj)

{

if (this.onclick != null)

{

       //Fire Event

              this.onclick(this, new  

         TreeviewItemEventArgs(pEvtObj.srcElement.getAttribute("header"),

                pEvtObj.srcElement.getAttribute("tag")));

      }

      return false;

}

Silverlight

Silverlight onclick event

private void treeView1_SelectedItemChanged(object sender, 

  RoutedPropertyChangedEventArgs<object> e)

{

TreeViewItem item = (TreeViewItem)e.NewValue;

      

       //Set HTML Document Attribute

System.Windows.Browser.HtmlPage.Document

  .GetElementById("silverlightControlHost")

         .SetAttribute("header", item.Header.ToString());

      

       //Fire DHTML onclick event

System.Windows.Browser.HtmlPage.Window

  .Eval("silverlightControlHost.fireEvent('onclick')");

      

}

Going further

Although wiring the events together appears to be a lot of work, the architecture opens up some cool and interesting Access integration with Silverlight:

•        a Navigation Treeview

•        an Accordion navigator

•        a Bing map

•        a Content Rotate such as http://msdn.microsoft.com/en-us/office/default.aspx that integrates with Access

•        a Windows 7 Silverlight App that runs in Access and your Windows 7 phone

•        seamless integration between SharePoint and Access

•        or anything that can be done in Silverlight!

Sample source code

                http://desktopweb.codeplex.com/SourceControl/list/changesets