Monday, December 20, 2010

Using Silverlight with Access – Part III (What's Next)

The part II release needs some improvements. So, sometime next month, we will release part III with a focus on
1.       Improving the HtmlBridge Silverlight Reflector
a.       The current Silverlight reflection class does not support all primitive types. The next version will reflect on all primitive types.
b.      The MSHTML Table Object Model will be used to support Table data to/from Access/Silverlight using an IHTMLTable bridge. See example below.
c.       The current version only supports void methods. The next version will support all primitive return types as well as an IHTMLTable bridge return type.
2.       Adding more Treeview features. Not sure what we will add at this time, but we want to show more Silverlight control code.
3.       Improving source code documentation.
4.       Improving ActiveForm.Proxy as needed.

Add Access Recordset Value to TreeviewPage.Table Example
mshtml.IHTMLTable rootTable = 
(mshtml.IHTMLTable)MyDocumentTasks.TreeviewPage.Table(“RootItems”);
 mshtml.HTMLTableRow rootItem = rootTable.insertRow();
mshtml.HTMLTableCell cell = myNewRow.insertCell();
//Assign Recordset value to table cell value
cell.innerText = rst.Fields["ProductName"].Value;
//Refresh Treeview
MyDocumentTasks.TreeviewPage.Table(“RootItems”).Refresh();

Wednesday, December 15, 2010

Using Silverlight with Access – Part II

The first release was more of a concept. The focus of this release was to improve the Silverlight control and Database form coding experience to reduce the time required to create the Silverlight / Access integration. The release simplifies wiring together Silverlight and an Access Database form. We hope the changes result in less code to write. The next blog will show more Silverlight coding.

What we have been doing

The goal of this release was to enable the ability to auto-generate Silverlight Control and Database Form reference classes with minimal lines of code. To accomplish this goal, assembly and database form reflection concepts were incorporated to auto-generate common class patterns.
Release Highlights
·         Added WidgetRelector.Transform() that transforms Silverlight reflection XML into a client-side C# class.
·         Added ActiveForm.Proxy.Transform() that transforms a database form into a C# class. Useful for any Access addin project.
·         Added Generate Reference Files dialog form. The form uses Silverlight.Emit(), WidgetRelector.Transform() and ActiveForm.Proxy.Transform() to auto-generate C# reference proxy files.
·         Enhanced TreeviewPage.AddItem(header). The method now adds a child TreeViewItem to any selected node.
·         Added TreeviewPage.RemoveItem() that removes a TreeViewItem.
·         Added an abstract HtmlBridge class that defines recommended .NET EventArgs classes. HTML DOM events raised by Silverlight resemble standard .NET events.

Silverlight Control Changes

An IReflection interface was added that defines an Emit() method.

IRelection Interface

void Emit() Method
The Emit() method uses LINQ and Assembly reflection to emit XML describing ScriptableMembers and events available to HtmlBridge. The Emit() method is used to auto-generate a client-side Silverlight control proxy reference file.
How Silverlight reflection maps to Access / Silverlight Addin Reference class

XML
Access / Silverlight Addin Reference class
<class name="TreeviewPage">…
class TreeviewPage
<attribute name="HtmlBridgeEvent.onclick" />
public new event EventHandler<ElementEventArgs> onclick;
<class name="ElementEventArgs"> …
base.onclick += new EventHandler<ElementEventArgs>(TreeviewPage_onclick);
<method name="AddItem"> …
<parameter name="itemValue">
          <parameterType name="String" …
public void AddItem(String itemValue)
  base.InvokeScriptableMethod("AddItem('" + itemValue + "')");


Create a Silverlight Control proxy reference file
The follow example creates all client-side (Addin) Silverlight reference code.
WidgetAddinRelector r = new WidgetAddinRelector((mshtml.HTMLDocument)ActiveForm
.WebBrowserControls(ActiveForm.WebBrowser.Names[0].ToString()).Object.Document);

r.Emit();

Event Attributes
HtmlBridge event attribute classes were added to define class and member level event attributes. The attributes are used to emit which HTML DOM events are raised by a Silverlight control. For example, the onclickAttribute reveals to WidgetReflector that the Silverlight control raises an HTML DOM onclick event. WidgetReflector reflects on event attributes to auto-generate HtmlBridge event code.
How HtmlBridge attributes map to Access / Silverlight Addin Reference

HtmlBridge Event Attributes
Access / Silverlight Addin Reference
[ScriptableType, onclick, onmousedown]public partial class TreeviewPage : UserControl, IReflection
public new event EventHandler<ElementEventArgs> onclick;

public new event EventHandler<ElementEventArgs> onmousedown;

base.onclick += new EventHandler<ElementEventArgs>(TreeviewPage_onclick);

base.onmousedown += new EventHandler<ElementEventArgs>(TreeviewPage_ onmousedown);
[ScriptableMember, ondataavailable]
public void AddItem(string itemValue)
public new event EventHandler<AsyncEventArgs> ondataavailable;

base.onclick += new EventHandler<AsyncEventArgs>(TreeviewPage_ ondataavailable);


HtmlBridge Attribute

The HtmlBridgeAttribute defines EventArg classes exposed by a Silverlight control.
Example
[HtmlBridge]
public class ElementEventArgs : EventArgs
{
public string Qualifier { get; private set; }
                public string Name { get; private set; }
                 public string Text { get; private set; }
                 public string Tag { get; private set; }
                 public string Value { get; private set; }
Silverlight onclick Example
onclick(new HtmlBridge.ElementEventArgs(item.Header.ToString(), tag));
Access Addin
MyDocumentTasks.TreeviewPage.onclick += new EventHandler<ElementEventArgs>(TreeviewPage_onclick);
void TreeviewPage_onclick(object sender, ElementEventArgs e)

HtmlBridge Utility Class

An HtmlBridge utility class was added to simplify calling HtmlBridge scripting methods from a Silverlight control.

Silverlight Control
HtmlBridge
htmlBridge.onclick(new HtmlBridge.ElementEventArgs(item.Header.ToString(), tag));
[HtmlBridgeEvent]
public void onclick(ElementEventArgs eventArgs)
{
      FireEvent("onclick", eventArgs);
}

public void FireEvent(string eventName)
{         HtmlPage.Window.Eval(_elementName + ".fireEvent('" + eventName + "')");
}


Access Database Changes

WidgetReflector

A WidgetReflector class was added. The class is a primarily a LINQ class that transforms a Silverlight reflection Xml stream into a Silverlight control proxy reference class. The class auto-generates a Silverlight control proxy reference class hiding the Silverlight control plumbing details from the developer.
Usage
WidgetReflector.Transform(sourceXml, sourceCodePath)

ActiveForm

ActiveForm is a static class for an Access form object wrapper. The class is defined as static since Access creates object instances.

internal static class Proxy
Internal static class that transforms or auto-generates the ActiveForm into a .NET wrapper class. Note that the class has not been tested with all Access controls.

Usage
MyDataAddin.ActiveForm.Proxy.Transform();

The Transform() method auto-generates all database form proxy reference code. For example, calling Transform() on the MyDocumentTasks form auto-generates all code enabling simple implementation methods hiding the addin plumbing details from the developer.

Examples

Implementation Code
Auto-generated Database Form code
MyDocumentTasks.ItemText.Value
private static Microsoft.Office.Interop.Access.TextBox _itemText = null;
    public static Microsoft.Office.Interop.Access.TextBox ItemText
{
      get
      {
        return _itemText;
      }
}
MyDocumentTasks.TreeviewPage.AddItem(itemValue)
public static TreeviewPage TreeviewPage;
TreeviewPage = new TreeviewPage((mshtml.HTMLDocument)
_webBrowser.Object.Document);


HtmlBridge

Abstract class inherited by Silverlight control providing base HtmlBridge members.

MyDocumentTasks.Reference

Class is now auto-generated from ActiveForm.Proxy.Transform().

TreeviewPage.Reference

Class is auto-generated from WidgetReflector.Transform().

Configuration Form

Added Addin Widger Proxy Files ribbon button to Database Tools.
Addin Widget Proxy Files opens Generate Refenence Files dialog form. Create button creates Silverlight and Database form proxy reference file. The configuration form was added to Access as a Power Tool.



Running the Generate Reference Files tool hides the Silverlight / Access addin wiring. Thus, implementation code becomes less complicated.
After running the tool and pasting the code into a .NET class file, a developer can write code similar to the following examples.
Ribbon Button to Open Form
public void myDocumentTasksButton_Click(IRibbonControl control)
{
//Open Form with reference to Addin
MyDocumentTasks.Open(this);
             
       //AddItemCommand
MyDocumentTasks.AddItemCommand.Click += new access.DispCommandButtonEvents_ClickEventHandler(AddItemCommand_Click);
MyDocumentTasks.AddItemCommand.OnClick = bindEvent;
             
//RemoveItemCommand
MyDocumentTasks.RemoveItemCommand.Click += new access.DispCommandButtonEvents_ClickEventHandler(RemoveItemCommand_Click);
MyDocumentTasks.RemoveItemCommand.OnClick = bindEvent;

             
       //TreeviewPage.onclick
MyDocumentTasks.TreeviewPage.onclick += new EventHandler<ElementEventArgs>(TreeviewPage_onclick);
             
//TreeviewPage.ondataavailable
MyDocumentTasks.TreeviewPage.ondataavailable += new EventHandler<AsyncEventArgs>(TreeviewPage_ondataavailable);
}


Add Item Click Delegate
void AddItemCommand_Click()
{
MyDocumentTasks.TreeviewPage.AddItem(MyDocumentTasks.ItemText.Value);

}


Remove Item Click Delegate

void RemoveItemCommand_Click()
{
 MyDocumentTasks.TreeviewPage.RemoveItem();
}

Silverlight TreeviewPage onclick Delegate

void TreeviewPage_onclick(object sender, ElementEventArgs e)
{
MyDocumentTasks.TabControl.Value = e.Tag;
}

Enhanced Treeview Methods
We enhanced the AddItem() method. The method adds a child TreeViewItem to any selected item. We also added an AddRootItem() and RemoveItem method.

Tuesday, November 16, 2010

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