Introduction:

In this article I would like to discuss about displaying a modal dialog or popup using Kendo UI Window.  There are tons of articles on net on similar topic, however here I would like to do it using ASP.Net MVC so we use MVC partial view to render contents on Kendo Window.  We also use Kendo Grid to populate the data dynamically.   We would also cover how to refresh the Grid data using AJAX calls and how to pass a MVC model from Java script/client to MVC server controller action.

Here are the things we would like to do for the demo purposes:

Displaying a search button on a view.  Upon clicking on search button, a modal dialog will be displayed with two input fields for First and Last Names.  When user clicks on Search button after entering the data in the text boxes, we would display matching records in a grid on the same window.

Capture3

Install Kendo UI

First step to install Kendo UI.  Follow the steps as mentioned in below article:

http://docs.telerik.com/kendo-ui/aspnet-mvc/asp-net-mvc-5

Note: The above instructions talk about installing Kendo UI separately and then integrating it with our application.  If you had installed Kendo UI code using Nuget package and then used Telerik upgrade wizard visual studio extension to upgrade to professional version then you may have to do couple of changes in BundleConfig.cs to work Kendo UI properly.

bundles.Add(new ScriptBundle("~/bundles/kendo").Include(
			"~/Scripts/kendo/2014.3.1119/kendo.all.min.js",
				// "~/Scripts/kendo/kendo.timezones.min.js", // uncomment if using the Scheduler
			"~/Scripts/kendo/2014.3.1119/kendo.aspnetmvc.min.js"));

bundles.Add(new StyleBundle("~/Content/kendo/2014.3.1119/css").Include(
			"~/Content/kendo/2014.3.1119/kendo.common-bootstrap.min.css",
			"~/Content/kendo/2014.3.1119/kendo.bootstrap.min.css"));

Observe that there is a version number folder2014.3.11 in the path.  This is basically the kendo UI version you had installed.  Make sure to add correct version number and path.

Kendo Window

We use Kendo HtmlHelper extension for creating the modal window.  The kendo window is assigned with a name “window” and with a caption “Search Names”.   We use LoadContentFrom method to load the contents by calling MVC controller action.  The first parameter is action name and the second one is controller name.  We also set Modal as true so that the dialog would be a modal dialog.  HtmlAttributes method is used for styling purposes.   An OnClose event handler is attached to the window.   Here is the code snippet:

<div id="UserForm">
    <span id="SearchButton" class="k-button">Search Names</span>
</div>

<div >
    @(Html.Kendo().Window()
          .Name("window")
          .Title("Search Names")
          .LoadContentFrom("Search", "Home")
          .Draggable()
          .Actions(actions => actions.Close())
          .Modal(true).Visible(false)
          .HtmlAttributes(new { style = "margin: 10px" })
          .Events(ev => ev.Close("onClose"))
          )
</div>

We also need to add a reference to Kendo MVC UI.

@using Kendo.Mvc.UI

In java script, subscribe to search button click event handler.   Inside click method open kendo window and place it in the center.   We have also added kendoValidation so that we can validate model state before opening modal window.

<script>
    $(document).ready(function () {
        var validator = $("#UserForm").kendoValidator().data("kendoValidator");

        $("#SearchButton").bind("click", function () {

            if (validator.validate()) {
                $("#window").data("kendoWindow").open().center(true);
            }
        });
    });
</script>

In the Controller, add an action for Search functionality.  Define a class called UserDetails with First Name and Last Name fields.  Add a partial view with UserDetails model and select “Create Scaffold template” option to display First Name and Last Name fields.

[HttpGet]
public ActionResult Search()
{
	return PartialView();
}

public class UserDetails
{
	public string FirstName { get; set; }

	public string LastName { get; set; }
}

The Serach partial view code looks like below:

@using ExchangeAdda.Models
@model ExchangeAdda.Models.UserDetails

@using (Html.BeginForm()) 
{
    @Html.AntiForgeryToken()
    
    <div class="form-horizontal">
        <h4>UserDetails</h4>
        <hr />
        @Html.ValidationSummary(true, "", new { @class = "text-danger" })
        <div class="form-group">
            @Html.LabelFor(model => model.FirstName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.FirstName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.FirstName, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            @Html.LabelFor(model => model.LastName, htmlAttributes: new { @class = "control-label col-md-2" })
            <div class="col-md-10">
                @Html.EditorFor(model => model.LastName, new { htmlAttributes = new { @class = "form-control" } })
                @Html.ValidationMessageFor(model => model.LastName, "", new { @class = "text-danger" })
            </div>
        </div>

        <div class="form-group">
            <div class="col-md-offset-2 col-md-10">
                <input id ="FindBtn" type="submit" value="Search" class="btn btn-default" />
            </div>
        </div>
    </div>
}

Kendo Grid

Now we need add a kendo grid to the Search view which displays the search results.  Bind the Grid to UserDetails model and add columns for First and Last names.

<div>
    <div>
        @(Html.Kendo().Grid<UserDetails>()
              .Name("AssociateSearch")
              .HtmlAttributes(new { style = "max-height: 50px; border:0px" })
              .Columns(columns =>
              {
                  columns.Bound(x => x.FirstName);
                  columns.Bound(x => x.LastName);
              })
              .Selectable()
              .Resizable(resizing => resizing.Columns(true))
              .DataSource(dataSource => dataSource
                  .Ajax())
        )

    </div>
</div>

Now, add a click event in java script to make an AJAX request to load the data.  Here, we are creating client side model which is equal to server side UserDetails model.  The properties are populated using document getElementById method.  Specify the action name as Url.  Make sure to add content type as application/json.  The data parameter is assigned with earlier created model.  Add an event handler for success and refresh grid data upon success.

<script>
    $('#FindBtn').click(function (e) {
        e.preventDefault();
        var UserDetails =
        {
            "FirstName": document.getElementById('FirstName').value,
            "LastName": document.getElementById('LastName').value,
        };
        $.ajax({
            url: "SearchJsonRequest",
            type: 'POST',
            contentType: "application/json;charset=utf-8",
            data: JSON.stringify(UserDetails),
            dataType: "json",
            success: function (data) {
                var grid = $('#AssociateSearch').getKendoGrid();
                grid.dataSource.data(data);
                grid.refresh();
            }
        });
        return false;
    });
</script>

The final step is to add an action in the controller for search functionality. I have added some dummy data to create a collection and return to the client for demo purpose. In reality there might be another service/BLL/DB call to fetch data based on search criteria.  The important thing to note is the method should return JsonResult.  The method accepts the UserDetails model data which is a better way for passing data from client to server.

[HttpPost]
public JsonResult SearchJsonRequest(UserDetails details)
{
	//Implement your search logic here

	var list = new List<UserDetails>()
	{
		new UserDetails{ FirstName = "Test 1 First Name", LastName = "Test 1 Last Name" },
		new UserDetails{ FirstName = "Test 2 First Name", LastName = "Test 2 Last Name" },
		new UserDetails{ FirstName = "Test 3 First Name", LastName = "Test 3 Last Name" },
	};

	return Json(list);
}

Advertisements

Namespace Mapping using XmlnsDefinition

In a typical WPF application/project we do have multiple projects/folders hence multiple namespaces.  Now when we want to use the classes from different namespaces in a XAML file, we would need to reference each namespace.

For instance, let’s say we have two projects called ABC.Presentation, ABC.WPF.  And say, we have following folders in the project hence following namespaces:

ABC.Presentation.Controls

ABC.Presentation.Behaviors

ABC.Presentation.Converters

ABC.Presentation.AttachedProperties

Now we would like to use some of the classes from these namespaces in our another project ABC.WPF.   To do that we would need to add following references inside XAML file. Of course a project reference to ABC.Presentation should be added to ABC.WPF project.


xmlns:t="clr-namespace:ABC.Presentation.Controls;assembly=ABC.Presentation"

xmlns:t="clr-namespace:ABC.Presentation.Behaviors;assembly=ABC.Presentation"

xmlns:t="clr-namespace:ABC.Presentation.Converters;assembly=ABC.Presentation"

xmlns:t="clr-namespace:ABC.Presentation.AttachedProperties;assembly=ABC.Presentation"

This makes your XAML file with bunch of references and the same thing has to be done with each XAML page in your application.   Another disadvantage with this is more maintainability.  If you change the namespace of any of existing one for any reason, now you need go to each XAML file and update there.

So it would be a better idea if we could have all of these references in a single place and Yes, it can be done.    Here are the steps:

Go to ABC.Presentation project and open AssemblyInfo.cs file.  Add XmlnsDefinition for each name space.  The first parameter is XAML namespace identifier.  This can be anything but it should be unique in your application.  Typically it is structured as Orgnization name/schema/customname.   The second parameter is CLR namespace name.  The advantage here is, we could use the same XAML namespace name for multiple CLR namespaces.


[assembly: XmlnsDefinition("http://www.abc.com/schema/presentation", "ABC.Presentation.Controls")]

[assembly: XmlnsDefinition("http://www.abc.com/schema/presentation", "ABC.Presentation.Behaviors")]

[assembly: XmlnsDefinition("http://www.abc.com/schema/presentation", "ABC.Presentation.Converters")]

[assembly: XmlnsDefinition("http://www.abc.com/schema/presentation", "ABC.Presentation.AttachedProperties")]

Now how do we make use of this identifier.   Inside the XAML file we could simply declare in a single line as shown below:


xmlns:pre="http://www.abc.com/schema/presentation"

Then using ‘pre’ we could access all the classes from different namespaces.

 
XmlnsDefinition doesn’t work: The tag/name does not exist in XML namespace

After following the steps as described above, I tried building my solution and I get the error saying the class name does not exist in namespace http: //www.ABC.com/schema/presentation.

After analyzing more I found the solution to fix the issue.  It looks like a Visual Studio bug.  After defining XAML namespace Identifier in AssemblyInfo file I built the project (ABC.Presentation) and then went to main project (ABC.Presentation where I am trying to refer the class names in XAML file), removed the project reference to ABC.Presentation.  And then re-added the reference.   That fixed the issue.


Note:
XmlnsDefinition works with foreign assemblies only.  That said, if you try to define a XAML namespace identifier for different namespaces and try to use it in the same assembly, it wouldn’t work.

In this article I would like to discuss on hosting a WCF service in IIS.

Create a WCF service application:

As a first step, create a  WCF service application.   From Visual Studio, go to File -> New -> Project.  Select ‘WCF Service Application’ under WCF in templates window.

The following components will be present in the newly created project:

  •         App_Data folder
  •         IService1.cs interface
  •         Service1.sbc and Service1.svc.cs files.  You can rename the interface and service files.
  •         Web.config file.

The Service1 class is defined with following two service methods and I don’t make any changes rather I would use it as it is.

  •         GetData
  •         GetDataUsingDataContract

Now we need to add end points for the service.  Go to web.config file and add following configuration under System.ServiceModel section.  Replace Contract and Service names according to your service namespace and class names.

<pre><system.serviceModel>
	<services>
  	<service name="MyTestService.Service1" >
    	<endpoint address="" binding="basicHttpBinding" contract="MyTestService.IService1">
    	</endpoint>
    	<endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange" />
  	</service>
    </services>
…

 

Host WCF service in IIS:

Run inetmgr command from Start -> Run to open IIS.
Right click on Sites and select Add Website. Enter Site name.

Service

Note: When you add a new Website, by default it creates a new application pool. You may assign it to an existing pool. The newly created pool may be configured with .Net V2.0. So go to Application Pools in IIS and double-click on newly created application pool. If it is pointing to older .Net versions then select appropriate framework version (in our case, the web application is created with .net V4.0 in first step).
Now try to access the service and we should be able to access service metadata. The URL should be similar to following:
http://localhost/Service1.svc

We can use WCF Test Client tool to test your service.

Creation of Desktop and Start Menu shortcuts:

In this article I would discuss on creating Start Menu and Desktop shortcuts for an application using Wix installer scripts. I would also discuss about a warning message 1910 which is related to unable to delete desktop shortcut while uninstalling and how to address the issue.
(here I am not going to discuss about creating a Wix installer for the application, rather I assume we
already have a Wix installer and we will be adding Start Menu and Desktop shortcuts to it).

First step, we need to add componentRef’s for start menu and desktop short cuts under Feature element. They are named as ApplicationShortcut, ApplicationShortcutDesktop respectively in the below example.

<Feature Id="ProductFeature" Title="<App name>" Level="1">
      ...
      <ComponentRef Id="ApplicationShortcut" />
      <ComponentRef Id="ApplicationShortcutDesktop" />
    </Feature>

Second step, add directories under Fragment for these two folders using ProgramMenuFolder and DesktopFolder keywords as shown below.

<Directory Id="ProgramMenuFolder">
        <Directory Id="ApplicationProgramsFolder" Name="<App name>" />
      </Directory>
      <Directory Id="DesktopFolder" Name="Desktop"></Directory>
    </Directory>

Add component for ApplicationShortcut under ApplicationProgramsFolder directory reference.

We need to generate a unique guid for each component. Add shortcut for ApplicationStartMenuShortcut under component. Target property need to refer to Installation folder and should specify the executable name. Assign install folder for WorkingDirectory property so that app related file I/O refers to this location.

The next step is to add RemoveFolder, which will be called during uninstallation process.

Final step is to add a RegistryValue and specify as current user.

Follow the same steps for adding desktop shortcut.

The complete section looks like below:

 <Feature Id="ProductFeature" Title="<App name>" Level="1">
      ...
      <ComponentRef Id="ApplicationShortcut" />
      <ComponentRef Id="ApplicationShortcutDesktop" />
    </Feature>
  </Product>
  <Fragment>
    <Directory Id="TARGETDIR" Name="SourceDir">
      <Directory Id="ProgramFilesFolder">
        <Directory Id="INSTALLFOLDER" Name="<App name>">
          <Directory Id="ENRESOURCESFOLDER" Name="en" />
        </Directory>
      </Directory>
      <Directory Id="ProgramMenuFolder">
        <Directory Id="ApplicationProgramsFolder" Name="<App name>" />
      </Directory>
      <Directory Id="DesktopFolder" Name="Desktop"></Directory>
    </Directory>
  </Fragment>
  <Fragment>
    <DirectoryRef Id="ApplicationProgramsFolder">
      <Component Id="ApplicationShortcut" Guid="<add guid>">
        <Shortcut Id="ApplicationStartMenuShortcut" Name="<app name>" Description="<your app description>" Target="[INSTALLFOLDER]YourApp.exe" WorkingDirectory="INSTALLFOLDER" />
        <RemoveFolder Id="RemoveApplicationProgramsFolder" Directory="ApplicationProgramsFolder" On="uninstall" />
        <RegistryValue Root="HKCU" Key="Software\<Your app folder>" Name="installed" Type="integer" Value="1" KeyPath="yes" />
      </Component>
    </DirectoryRef>
    <DirectoryRef Id="DesktopFolder">
      <Component Id="ApplicationShortcutDesktop" Guid="<Add guid>">
        <Shortcut Id="ApplicationDesktopShortcut" Name="<Your app name>" Description="<Your app description>" Target="[INSTALLFOLDER]YourApp.exe" WorkingDirectory="INSTALLFOLDER" />
        <RemoveFolder Id="RemoveDesktopFolder" Directory="DesktopFolder" On="uninstall" />
        <RegistryValue Root="HKCU" Key="Software\<Your app folder>" Name="installed" Type="integer" Value="1" KeyPath="yes" />
      </Component>
    </DirectoryRef>
  </Fragment>

Warning 1910:
One of the issue I have observed with desktop shortcut is, it throws following warning while uninstalling.

Warning 1910. Could not remove Shortcut . Verify that the shortcut file exists and that you can access it.

Uninstallation process can’t delete the desktop shortcut due to above warning. It happens only for desktop shortcut and there are no issues with start menu shortcut. The uninstallation would be successful even though there is warning, however the desktop shortcut wouldn’t be deleted. Based on analysis it looks like it is happening due to file/directory attribute settings of desktop.

To fix this issue, go to C:\Users\Public\Desktop and find desktop.ini file. This is a system file. So if you don’t see it then go to folder options and uncheck following check boxes under view tab to view hidden/system files.

  • Don’t show hidden files, folders, or drives.
  • Hide protected operating system files (Recommended).

Un check above check boxes and desktop.ini file would be visible now.

Go to file properties of desktop.ini and Un-check Read-only to remove read only property.
Now try to uninstall your application and you wouldn’t see the earlier warning message.

Introduction:
In this post I would like to discuss about writing Unit Tests for BLL components which uses EF for DB operations. My intension here is not to focus on writing tests for DB CRUD operations but to test business logic which involves retrieving further details from Database using EF to validate business scenarios. For these scenarios, either BLL component has to get data from Database or we need to explicitly provide some kind of sample data/objects so that validations can be done against of these objects.

Following are few of the different ways for doing this:

Using SQL Server Compact Edition(SQL CE):
SQL CE database can be created dynamically (.sdf file on disc) and data can be populated using SQL CE schema/sample data scripts. SQL Server scripts don’t work with SQL CE. Using SQL Server Compact Tool we can generate SQL CE compatible Schema/sample data scripts.
Here is the code snippet how to programmatically create CE database. System.Data.SqlServerCe reference has to be added to the project.
Connection String can be defined as shown below:

strConnString = “Data Source=” + CE_DB_FilePath_OnDisk;

SqlCeConnection objConn = null;
objConn = new SqlCeConnection(strConnString);
string[] arrCommands = strDatabaseSchema.Split(new string[] { m_strCommandSeparator }, StringSplitOptions.RemoveEmptyEntries);
SqlCeCommand objCmd = new SqlCeCommand();
objCmd.Connection = objConn;
objConn.Open();

foreach (string strCmd in arrCommands)
{
    string strTrimmedCmd = strCmd.Trim();
    if (!String.IsNullOrEmpty(strTrimmedCmd))
    {
        objCmd.CommandText = strTrimmedCmd;
        objCmd.ExecuteNonQuery();
    }
}

Once we create the CE Database, we can create context by passing connection string.

Public class LibraryContext : DbContext
{
    public LibraryContent()
           : base("LibraryDBConnection")
    {
    }
}

Connection string can be configured in .config file.

<connectionStrings>
    <add name=" LibraryDBConnection "
         providerName="System.Data.SqlServerCe.4.0"
         connectionString="Data Source=|DataDirectory|LibraryDb.sdf"/>
</connectionStrings>

Pros:
SQL CE is in memory/file IO based and doesn’t require installation of SQL. So these tests can be executed on build agents on TFS without any additional SQL pre-requisites.
It is a sample database which is a replica of production Database, so most of the business logic scenarios can be covered.

Cons:
The issue with this approach is, we can’t create single DBContext which serves both SQL Server and SQL CE using same model (.edmx) due to following reasons:
SQL server model uses “System.Data.SqlClient” provider whereas SQL CE expects “System.Data.SqlServerCe.4.0”

Provider=”System.Data.SqlClient”
ProviderManifestToken
EntitySet contains Schema=”dbo” however this is not applicable for SQL CE.
SQL CE limitations such as it doesn’t support Stored Procedures, Views, Triggers

Mocking DB context:
Using Moq framework we can have in-memory implementations for DB context and can be used for unit tests.
For example we have a simple model for Novels and Magazines. The context and interface looks like as shown below:

Public interface ILibraryContext
{
    DbSet Books { get; set;}
	DbSet Magazines { get; set;}
	void SaveChanges();
}

Public class LibraryContext : DbContext, ILibraryContext
{
	Public DbSet Books { get; set;}
	Public DbSet Magazines { get; set;}
	Public void SaveChanges()
    {
        //…
    }
}

Now we can mock the context.

_context = new Mock< ILibraryContext>(MockBehavior.Strict);

We need to Setup the methods which are used while testing the actual methods.

_context.Setup(c => c.SaveChanges()).Returns(LocalSaveMethod);

Implement some sample method for save.
We also need to setup objects. Objects setup requires corresponding object data.

_bookData = new List
            {
            new Book(), new Book()
};
_context.Setup(c => c. Books).Returns(PopulateBooksMockSet);

Private IDbSet PopulateBooksMockSet()
{
    Mock<IDbSet> _booksMockSet = new Mock<IDbSet>();
    _ booksMockSet.As().Setup(
        m => m.Provider).Returns(_bookData.AsQueryable().Provider);

    _ booksMockSet.As<IQueryable>().Setup(
        m => m.Expression).Returns(__bookData.AsQueryable().Expression);

    _ booksMockSet.As<IQueryable>().Setup(
        m => m.ElementType).Returns(__bookData.AsQueryable().ElementType);

    _ booksMockSet.As<IQueryable>().Setup(
        m => m.GetEnumerator()).Returns(__bookData.AsQueryable().GetEnumerator());
    return _ booksMockSet.Object;
}

Say the following is the BLL method which we wanted to test.


public void UpdateBookName(ILibraryContext context, Book bookObj, string newName)
{
    var bookInstance = context.Books.Where(b => b.Id == bookObj.Id).FirstOrDefault();

    if (bookInstance != null)
    {
        bookInstance.Name = newName;
        context.Books.Attach(bookInstance);
        context.SaveChanges();
    }

}

The typical test case would look like this:


[TestMethod]
public void UpdateName_Test()
{
     string newName = "Updated name";
     UpdateBookName(context, bookObj, newName);

     Assert.AreEqual(bookObj.Name, newName, "Name not updated");
}

Now how do we test delete object scenarios.
Generally, when we call context.Remove(object), the object doesn’t get removed from collection instead the object state will be changed to deleted state. When we call context.SaveChanges(), the ojects which are in deleted state will get removed/deleted. With mocking context, saveChanges is a dummy operation.
To test this scenario, we can use Mock<IDbSet<>>. Using Mock sets, We can check if a method was called with a particular parameter or not. We can also check how many times it was called.

bookMockSet.Verify(m => m.Remove(deletedBook), Times.Once(), “Book is not deleted”)

Pros:
Mocking DbContext works with in memory objects hence it would be faster.
It doesn’t have any addition overhead such as installation/setup etc.

Cons:
Though we have in memory context it is not equal to DbContext. It uses “LINQ to objects” compared to “LINQ to Entities” which EF actually does.
Creating mock objects (sample data/objects) is tedious.
Though it has such limitations, it can provide a good level of unit test coverage for EF.

Using SQL Express instance:
Create a separate Database programmatically on SQL express instance and populate sample data for every time when a test is executed. Delete the Data at the end of Test execution, so that the data can be repopulated next time when another test is executed. This is more like an integration testing than unit testing.

Pros:
It is a full blown end to end testing.
It doesn’t have any other maintenance overhead such as creating sample shema/data

Cons:
This makes SQL express installation as pre-requisite for build agents on TFS.

Please provide your valuable feedback/comments/suggestions that will help me improve my writing.

Introduction:

An app for Office is basically a webpage that is hosted inside an Office client application.  It enables users to run the same solutions across applications, platforms and devices.  The applications are developed by using the powerful web technologies such as HTML5, XML, CSS3, JavaScript and REST API’s.    Refer MSDN documentation on Office Apps for more details.

The three basic types of Office apps are task pane, content and mail apps.  In this article I am going to explain how to develop an Office 2013 Excel Task pane app that acts as a live data work book.  The app will have following functionalities:

  • Communicating with an external WCF service to fetch live data
  • Write data into excel work sheet from Task pane app.
  • Track data modifications in excel work sheet.
  • Save modifications to Database through WCF service.

pic1

Connecting to a WCF data service:

Here I am going to start with WCF communication part.  However if you are looking for instructions on how to create a basic Excel task pane app, please refer MSDN.  The task pane app can communicate with an external WCF data service in following ways:

  1. Office Client (JavaScript) to External WCF service
  2. Web Service to WCF Service

1)     Office Client (JavaScript) to External WCF data service:

In this approach the communication part is written in java script, which is executed at client side.

Use JQuery to communicate with WCF data service as shown below.    The first step is construct an URI which should be understandable to WCF Data service.  Writing complex URI queries is tricky however a tool called LINQPad can be used for URI construction.  In this example Dataservice is hosted at http://localhost:17203/MyService end point.

pic2

One of the major issue with java script client side communication is same-origin policy limitation.  Generally browsers don’t allow clients talking to servers which are not in same domain.  One of the way to overcome this issue is enabling CORS.  The following setting will do that.

$.support.cors = true;

Tip: When you add a project of type “Office 2013 App”, visual studio creates a web project for you for developing UI for the app.   If you run app in debug mode from Visual Studio, the web project will be hosted on IIS Express on SSL.  In that case, above code may throw following exception “Access denied”.  To resolve this issue, host the web application on IIS (basic http) or use a self-hosted Web service.

The other approach is using JayData. JayData library comes with a utility called JaySvcUtil.exe which can be used to generate a context file in java script.  Use following command to generated the context file.pic3

Once context.js file is created, add it to the project.  Below is the command to generate java script context.

Tip: WCF data service by default enables metadata on $metadata end point.  So make sure to append ‘$metadata’ to the service end point while generating context.

pic4

Generating static context with JaySvcUtil.exe may have maintenance issues.  Whenever service contracts are changed we need to regenerate proxy and add it to the project.  We can avoid that by creating context dynamically with following command.

pic5

Another approach is to use JasonP. I am not covering this topic here however it can be used to resolve Cross domain communication issues.

2)     Web Server to WCF Service:

As mentioned earlier, due to same-origin policy limitations we may encounter issues like ‘Access Denied’ exceptions while trying to communicate with external WCF service in java script.  So this is an alternate approach to overcome that issue.

The communication happens in two steps in this approach:

Office Client <-> Webserver (server side Code) <-> External WCF service

Office client (java script) calls a Web Method which is executed at ASP server side.  Then Web Service makes a call to WCF service.  Here are the steps to achieve it.

Add a service reference (using WCF data service end point) to the project.  Add an ASP .Net page to the project.  Go to the class in aspx.cs file and write following lines of code.

pic6

We are creating a context using Service reference by specifying the service end point. In this example it returns a list of accounts.   Make sure the method is decorated with WebMethod attribute so that it can be accessed from client’s JavaScript.  Using PageMethods we can call the server side method as shown below.

pic7

Writing data into Excel sheet:

Till now we have focused on WCF service communication and fetching the data from service.    Now we are going to see how we can populate the excel sheet with the data.  setSelectedDataAsync method is used for writing data into excel sheet in selected location. This is defined in Office JavaScript library.  We can write data in 3 formats such as matrix, table, and text.  I will use table data type so that we can display headers along with data.  First create table data. In below example, ‘res’ is the collection which is received from WCF service.  First we specify the headers and then push the data rows.

pic8

Now write the table data in excel sheet at current selection.  Most of the Office java script library methods are asynchronous so that it doesn’t block the Excel interaction with end user.

pic9

Tracking changes in the excel sheet:

Now we want to track changes made by user in excel sheet so that modifications can be automatically sent to WCF service for saving them in Database.  We can use databindings and datachanged event hanlder.

Office.context.document.bindings is a collection of bindings specific to the document.  Add a new binding to this collection and specify the type as table data.  On success, we can add a handler of type ‘BidningDataChanged’.  When data is changed we receive a parameter called eventArgs.   We can use eventArgs.binding.getDataAsync method to reading current data from excel.  One of the drawback with datachanged is, it doesn’t specify what is changed.  It could be an update to a cell or addition/deletion of new row/column, so we need to write custom logic to identify the change and act on it accordingly.

pic10

Please provide your valuable feedback/comments/suggestions that will help me improve my writing.