Saturday, 14 September 2013

OMPM - SQL-DMO Install Required

When trying to run the Office Migration Planning Manager for Office 2010 on a machine that has Sql Server 2008 or later installed you will often get the following error message:

SQL-DMO Install Required: This operation requires the SQL Server ODBC Driver, version 3.80 or later, which comes with SQL Server 2000, SQL Server 2005 and SQL Server Express.

This is caused by a missing dependency - SQL-DMO has been deprecated and is no longer part of the Sql Server. When you search the Web looking for solution you will be advised to install the "Backward Compatibility Components" which are part of the "Feature Pack for Microsoft SQL Server 2005". So I did. However, this caused another error:

Runtime Error!
(...)
R6034
An Application has made an attempt to load the C runtime library incorrectly.
Please contact the application's support Sql server team for more information.

Investigating this took me some time. It came out that when you search for "Feature Pack for Microsoft SQL Server 2005" in the Microsoft Download Center the first result you get is actually outdated. There is another download link at the bottom of the search results list, that points to the latest version:

Correct version: Feature Pack for Microsoft SQL Server 2005 SP4.

Installing this one fixed all issues and allowed me to run the OMPM. I had a 64x version of Sql Server 2008 R2.

Friday, 31 May 2013

How to read and write Excel cells with OpenXML and C#

Recently I needed to update an Excel spreadsheet and then retrieve some recalculated values. The tricky part was that some cells that I needed to retrieve information from were formula cells (e.g. =A1+5). I needed to update some other cells first and then get the value of recalculated formula.

Unfortunately, this is not directly possible with OpenXML. If you simply try updating some cells and then retrieving the relying ones, you will get the original values for those cells, not recalculated. . This is because formula cells don't store any values, just... formulas. You can only force recalculation by opening your document in the Excel application.

Knowing this I implemented a Refresh method, that opens the Excel app in background and then closes it immediately and saves changes. Below I present my sample code.

Prerequisites
In order to compile the following code you will need the Microsoft.OpenXML SDK 2.0 (DocumentFormat.OpenXML NuGet package) and reference to Microsoft.Office.Interop.Excel (used for opening the Excel app to recalculate formulas).

Solution
Let's start with an interface for my ExcelDocument class:

/// <summary>
/// Interface defining Excel Document methods
/// </summary>
public interface IExcelDocument
{
    /// <summary>
    /// Reads a value of a spreadsheet cell
    /// </summary>
    /// <param name="sheetName">Name of the spreadsheet</param>
    /// <param name="cellCoordinates">Cell coordinates e.g. A1</param>
    /// <returns>Value of the specified cell</returns>
    CellValue ReadCell(string sheetName, string cellCoordinates);

    /// <summary>
    /// Updates a value of a spreadsheet cell
    /// </summary>
    /// <param name="sheetName">Name of the spreadsheet</param>
    /// <param name="cellCoordinates">Cell coordinates e.g. A1</param>
    /// <param name="cellValue">New cell value</param>
    void UpdateCell(string sheetName, string cellCoordinates, object cellValue);

    /// <summary>
    /// Refreshes the workbook to recalculate all formula cell values
    /// </summary>
    void Refresh();
}
Once we have the interface we need its implementation:
public class ExcelDocument : IExcelDocument
{
    private readonly string _filePath;

    public ExcelDocument(string filePath)
    {
        _filePath = filePath;
    }

    /// <see cref="IExcelDocument.ReadCell" />
    public CellValue ReadCell(string sheetName, string cellCoordinates)
    {
        using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(_filePath, false))
        {
            Cell cell = GetCell(excelDoc, sheetName, cellCoordinates);
            return cell.CellValue;
        }
    }

    /// <see cref="IExcelDocument.UpdateCell" />
    public void UpdateCell(string sheetName, string cellCoordinates, object cellValue)
    {
        using (SpreadsheetDocument excelDoc = SpreadsheetDocument.Open(_filePath, true))
        {
            // tell Excel to recalculate formulas next time it opens the doc
            excelDoc.WorkbookPart.Workbook.CalculationProperties.ForceFullCalculation = true;
            excelDoc.WorkbookPart.Workbook.CalculationProperties.FullCalculationOnLoad = true;
            
            WorksheetPart worksheetPart = GetWorksheetPart(excelDoc, sheetName);
            Cell cell = GetCell(worksheetPart, cellCoordinates);
            cell.CellValue = new CellValue(cellValue.ToString());
            worksheetPart.Worksheet.Save();
        }
    }

    /// <summary>Refreshes an Excel document by opening it and closing in background by the Excep Application</summary>
    /// <see cref="IExcelDocument.Refresh" />
    public void Refresh()
    {
        var excelApp = new Application();
        Workbook workbook = excelApp.Workbooks.Open(Path.GetFullPath(_filePath));
        workbook.Close(true);
        excelApp.Quit();
    }

    private WorksheetPart GetWorksheetPart(SpreadsheetDocument excelDoc, string sheetName)
    {
        Sheet sheet = excelDoc.WorkbookPart.Workbook.Descendants<Sheet>().SingleOrDefault(s => s.Name == sheetName);
        if (sheet == null)
        {
            throw new ArgumentException(
                String.Format("No sheet named {0} found in spreadsheet {1}", sheetName, _filePath), "sheetName");
        }
        return (WorksheetPart) excelDoc.WorkbookPart.GetPartById(sheet.Id);
    }

   private Cell GetCell(SpreadsheetDocument excelDoc, string sheetName, string cellCoordinates)
    {
        WorksheetPart worksheetPart = GetWorksheetPart(excelDoc, sheetName);
        return GetCell(worksheetPart, cellCoordinates);
    }

    private Cell GetCell(WorksheetPart worksheetPart, string cellCoordinates)
    {
        int rowIndex = int.Parse(cellCoordinates.Substring(1));
        Row row = GetRow(worksheetPart, rowIndex);

        Cell cell = row.Elements<Cell>().FirstOrDefault(c => cellCoordinates.Equals(c.CellReference.Value));
        if (cell == null)
        {
            throw new ArgumentException(String.Format("Cell {0} not found in spreadsheet", cellCoordinates));
        }
        return cell;
    }

    private Row GetRow(WorksheetPart worksheetPart, int rowIndex)
    {
        Row row = worksheetPart.Worksheet.GetFirstChild<SheetData>().
                                Elements<Row>().FirstOrDefault(r => r.RowIndex == rowIndex);
        if (row == null)
        {
            throw new ArgumentException(String.Format("No row with index {0} found in spreadsheet", rowIndex));
        }
        return row;
    }
}
I hope the code is self-explanatory and doesn't require more comments. You can optimize it for you needs e.g. when updating/reading multiple cells at once you may want to open the doc only once. Currently my code opens it and closes for each read/update request.

Wednesday, 22 May 2013

Preparation materials for MCSD Web Applications certification

I'm happy to announce that today I've become a Microsoft Certified Solutions Developer (MCSD) in Web Applications area. To achieve that I needed to pass 3 exams. Here is my short summary of each of them together with some useful preparation materials:

70-480 - Programming in HTML5 with JavaScript and CSS3

This exam begins your journey to the MCSD certificate. In general it covers exactly what is named in its title: HTML5 & CSS3. You could also use some jQuery knowledge. If you are a web developer with multiple years of experience this should be a piece of cake for you. In case you need to refresh your memory on some topics I recommend watching the free video tutorial at the Microsoft Virtual Academy:

70-486 - Developing ASP.NET MVC 4 Web Applications

This exam tests your knowledge of the Asp.Net MVC4 framework. To be honest I can't really remember if it includes WebApi questions, but it's worth to learn it anyway, as it's required for the last exam. Again, if you worked on several MVC4 projects there is nothing to be afraid of.

Before I took this exam I browsed the following book to make sure I'm not missing anything:


70-487 - Developing Windows Azure and Web Services

For me this was the hardest exam. It is because it covers a wide range of different topics. All required technologies are somehow related, but at the same time they are independent frameworks:
  • Windows Azure
  • WCF
  • MVC4 WebApi
  • Entity Framework
  • Other Data Access
Despite the exam`s title I was under impression that there were not that many questions related to Windows Azure. The basic overview of Azure features would suffice to answer most of them. There was a lot of questions related to WCF & Data Access though. Luckily there are excellent study guides available. Here are 2 that I liked most:
  • Study Guide #1
    My personal favourite; it relays strongly on pluralsight video trainings, which are usually very good.
  • Study Guide #2
    A nice alternative for those of you who don't have access to pluralsight. Most links are referencing free online materials.
If you read/watch all linked materials you will be good to go ;) Good luck future MCSDs!

Wednesday, 15 May 2013

XAML ListView - scroll SelectedItem to the middle

In the Windows Store app that I recently worked on I used several ListViews to present some data. I had 2 requirements for those lists:
  • The selected item should be always visible
  • In addition it should be displayed in the middle of the list when possible (it's not possible for the first element).
This is what I wanted to achieve:

The first requirement is quite easy to fulfill by using ListView.ScrollIntoView() method. However, this doesn't satisfy the second requirement, as you don't have any control on where exactly the item will appear.

Solution

Instead of working with the ListView directly I worked with the included ScollView control. Here is my method for scrolling with some comments:
public void ScrollToSelected(ListView list, Object selected)
{
    var scrollViewer = list.GetFirstDescendantOfType<ScrollViewer>();
    if (scrollViewer == null) return;

    // Calculate the offset to be used for scrolling.
    // In my case I use ViewPort height, index of the selected item and a fixed value 3 to adjust the result
    double halfList = scrollViewer.ViewportHeight/2;
    int itemIndex = list.Items.IndexOf(selected);
    double scrollOffset = itemIndex - halfList + 3;

    // If offset happens to be bigger than scrollable height use the scrollable height
    // Possible for items from the end of the list
    if (scrollOffset > scrollViewer.ScrollableHeight)
    {
        scrollOffset = scrollViewer.ScrollableHeight;
    }

    // scroll to calculated offset
    scrollViewer.ScrollToVerticalOffset(scrollOffset);
}

Tuesday, 14 May 2013

Windows Store - Your app doesn’t meet requirement 4.1

Recently I uploaded my first Windows 8 app to the Windows Store. At first, my app didn't pass the certification process because of the following issue (Notes from Testers):
The app has declared access to network capabilities and no privacy statement was provided in the Description page. The app has declared access to network capabilities and no privacy statement was provided in the Windows Settings Charm.

Luckily for me the issue is widely described here. In short, if your app uses internet you need to define a privacy policy. the privacy policy needs to be linked from the app description page and also avaiable from the Settings Charm.

Solution

This is what you need to do to satisfy this requirement:
  1. Create a webpage describing your privacy policy (how you use user data etc.).
  2. Deploy that webpage to any server, so it's available to everybody who wants to read it.
  3. Add a link to your privacy page on the app`s description page in the Windows Store Developer Center:
  4. Add a link to the privacy policy page to your Settings Charm.
    Here is how I do it (basing on this post):
    1. Open the App.xaml.cs file
    2. Reference the following namespace:
      using Windows.UI.ApplicationSettings;
    3. Create following methods for adding the new action called 'Privacy Policy" to your Settings pane:
      private void AddPrivacyPolicy(SettingsPane sender, SettingsPaneCommandsRequestedEventArgs args)
      {
          var privacyPolicyCommand = 
              new SettingsCommand("privacyPolicy", "Privacy Policy", (uiCommand) => ShowPolicyPage());
          args.Request.ApplicationCommands.Add(privacyPolicyCommand);
      }
      
      private async void ShowPolicyPage()
      {
          var uri = new Uri("http://YOUR-SERVER/privacy-policy.html")
          await Launcher.LaunchUriAsync(uri);
      }
    4. In the OnLaunched method register the newly created method:
      SettingsPane.GetForCurrentView().CommandsRequested += AddPrivacyPolicy;
  5. Rebuild your package and resubmit to the Windows Store. At this time that issue should be resolved.
When adding a Privacy Policy item to the Settings Charm you may consider using the Callisto project and its Settings Flyout support. It makes complex customization of that Settings Pane easier. In my case however it was shorter to add that single command in a regular way.

BTW I was really surprized how quick the certification process was - it took only 8h from the moment of submission! It's a huge improvment comparing to my experience with WP7 apps. I hope it stays that way.

Thursday, 25 April 2013

XAML - SelectedItem in ComboBox doesn't work

I'm currently working on a small Windows Store app. The app uses mainly default controls, with ComboBox being one of them. Recently I had a problem with selecting a default value in my ComboBox.

My XAML code looked like this:



In the design view this actually showed the correct selected item, however it didn't work when the app was ran.

Solution

The solution was actually very trivial. If you take a look at my code you'll see that the SelectedItem property is defined before the ItemSource property. It came out that the order does matter, so the SelectedItem should be defined after the ItemSource.

Wednesday, 24 October 2012

Using task based async WCF client methods in synchronous scenarios

If you are already working with the Visual Studio 2012 you may have noticed that the dialog box for generating Service References has changed a bit. It offers an additional option to generate task based asynchronous client methods (see picture below). In VS2010 you could generate async operations for the service client as well, but in VS2012 you have the additional choice:
  • Generate asynchronous operations
    This one gives you the exact same result as choosing to generate async operations in VS2010 i.e. for each service method you will have 2 additional client methods: *Async & *Completed. One of my previous posts partially explains how to use them and provide more links on that topic.
  • Generate task-based operations
    When writing your async code this option allows you to take advantage of the Task Parallel Library (TPL) that was introduced with .Net 4. This post is not meant to be a TPL tutorial, but there are many sites explaining the concept. My personal favourite is "Introduction to Async and Parallel Programming in .NET 4" by Dr. Joe Hummel. For now it is enough to say that using tasks (i.e. TPL library) can make handling your async scenarios easier and the async code more readable/maintainable.

The interesting thing is that since .Net 4.5 and its async/await feature you can easily benefit from using task-based async operation even in fully synchronous scenarios. You may start wondering what can be advantages of using async techniques & libraries in fully synchronous scenarios. The answer is: performance! If you are using regular synchronous methods, they block current thread until it receives the response. In case of task-based operations combined with async/await feature the thread is released to the pool while waiting for the service response.

Obviously this advantage only applies in some scenarios e.g. in web applications running on IIS, where requests are handled in parallel by threads from the pool. If you are working a single threaded client app you will not benefit from this approach.

Sample code

In this post I'll re-use the sample code from my last post. I'll convert synchronous calls to my sample web service so they use task-based async operations. So, let me remind you the interface of the web service that we will use:

[ServiceContract]
public interface IStringService
{
    [OperationContract]
    string ReverseString(string input);
}
Now, let's update the service client. There is actually not much conversion that needs to be done. First, you need to ensure that the generated code includes task-based async operations (right click on service reference -> "Configure service reference"). Once you have async operations generated, you need to transform the existing code to work with tasks and use async/await feature:
public async Task<string> ReverseAsync(string input)
{
    return await _client.ReverseStringAsync(input);
}
Comparing to the original, purely synchronous method we have following changes:
  • Async keyword added to the method signature
  • Method return type changed to Task<string> (i.e. original return type 'wrapped' with Task)
  • Different service client method used: ReverseStringAsync instead of ReverseString
  • Await keyword added before the client method call
  • "*Async" suffix added to the method name (recommended convention)
These changes are enough to start taking advantage of async features of .net 4.5. We only need to update our tests, but there is only a few changes here as well.

Updated integration test:

[TestMethod]
public void TestStringHelper_Reverse()
{
    StringHelper sh = new StringHelper();
    string result = sh.ReverseAsync("abc").Result;
    Assert.AreEqual("cba", result);
}
Notice that I only added a call to .Result property as this time the method returns Task.

And the unit test:

[TestMethod]
public void TestStringHelper_Reverse()
{
    // Create channel mock
    Mock<IStringServiceChannel> channelMock = new Mock<IStringServiceChannel>(MockBehavior.Strict);

    // setup the mock to expect the ReverseStringAsync method
    channelMock.Setup(c => c.ReverseStringAsync("abc")).Returns(Task.FromResult("cba"));

    // create string helper and invoke the ReverseAsync method
    StringHelper sh = new StringHelper(channelMock.Object);
    string result = sh.ReverseAsync("abc").Result;
    Assert.AreEqual("cba", result);

    //verify that the method was called on the mock
    channelMock.Verify(c => c.ReverseStringAsync("abc"), Times.Once());
 }
The main thing to notice here is that we use Task.FromResult to create a task wrapping our sample result when mocking the client method.

Asp.Net MVC

I already mentioned that described approach will only be beneficial in some types of apps e.g. webapps running on IIS. A sample Asp.Net MVC4 controller action using our async client could look as follows:
public async Task<ActionResult> Index()
{
    ViewBag.Message = "Reversed 'abc' string: "
                      + await _stringHelper.ReverseAsync("abc");
    return View();
}
Again, notice async and await keywords added to the action method, supported in MVC4.
A detailed tutorial for using aync/await with MVC4 can be found here.

Sample code for this post on my github:
https://github.com/filipczaja/BlogSamples/tree/master/MockingWCFClientWithMoqAsync