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
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:
• 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 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
