Following up from this post, I set about building some services to power my application’s data requirements. These aren’t perfect by any means but they’re enough for a sample application to get going with. Working from the sketch of how the services need to look given at the end of the previous post, I put together a LINQ to SQL diagram;

and left LINQ to SQL to do concurrency checking based on every column in the Product table ( I only plan to modify the Product table ) which is its default if I remember correctly ( and, usually, overkill but it’ll do for me here ).

And I then produced a rough service interface;

public class LookupDataItem
{
  public int Id { get; set; }
  public string Title { get; set; }
}

public class ProductSales
{
  public string MonthYearLabel { get; set; }
  public decimal TotalSales { get; set; }
}

public class ProductSearchResult
{
  public int Page { get; set; }
  public int TotalPages { get; set; }
  public List<Product> Products { get; set; }
}

public class ProductChangeEntry
{
  public Product OriginalProduct { get; set; }
  public Product CurrentProduct { get; set; }
}

[ServiceContract]
public interface IProductService
{
  [OperationContract]
  Dictionary<string, List<LookupDataItem>> GetReferenceData();

  [OperationContract]
  ProductSearchResult GetProducts(int pageNumber, int pageSize, string matchString);

  [OperationContract]
  List<ProductSales> GetProductSales(int productId);

  [OperationContract]
  List<int> UpdateProducts(List<ProductChangeEntry> entries);

  [OperationContract]
  void InsertProducts(List<Product> products);
}

it’s not particularly beautiful but it’ll mostly get done what I want to get done. The way I see GetReferenceData working is that it’ll return to me a bunch of lookup values – i.e. a dictionary like;

“categories” : { 0, “food” }, { 1, “drink” }, { 2, “eggs” }

“suppliers”: { 0, “Supplier 1” }, { 1, “Supplier 2” }

and I can use that to populate ComboBoxes in the UI.

The odd-looking return value on UpdateProducts is intended to cope with the idea that we won’t always be able to successfully make all the changes requested of us and so we return a list of which updates actually worked – i.e. which IDs we did successful work on so that a caller can try and determine how successful/unsuccessful we were in making the updates that they asked for.

I don’t intend to try and do any “clever” conflict resolution on the server-side, I’ll just let the list of succeeded entries go back to the client and let the client figure it out ( which will be equally “unclever” in my case :-) ).

I made sure that my service was configured over HTTPS ( NB: ASP.NET role and membership services snipped from config file below );

<system.serviceModel>
  <behaviors>
    <serviceBehaviors>
      <behavior name="ServiceBehavior">
        <serviceAuthorization principalPermissionMode="UseAspNetRoles">
          <authorizationPolicies>
            <add policyType="AuthPolicy, AuthBits"/>
          </authorizationPolicies>
        </serviceAuthorization>
        <serviceMetadata httpGetEnabled="true"/>
        <serviceDebug includeExceptionDetailInFaults="false"/>
      </behavior>
    </serviceBehaviors>
  </behaviors>
  <bindings>
    <basicHttpBinding>
      <binding name="bindingConfig">
        <security mode="Transport"/>
      </binding>
    </basicHttpBinding>
  </bindings>
  <services>

    <service behaviorConfiguration="ServiceBehavior"
             name="ProductService">
      <endpoint address=""
                binding="basicHttpBinding"
                contract="IProductService"
                bindingConfiguration="bindingConfig"/>
    </service>

    </service>
  </services>
  <serviceHostingEnvironment aspNetCompatibilityEnabled="true"/>
</system.serviceModel>

and then wrote a basic implementation of that service interface;

[AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
public class ProductService : IProductService
{
  [PrincipalPermission(SecurityAction.Demand, Role = "users")]
  public Dictionary<string, List<LookupDataItem>> GetReferenceData()
  {
    // We could/should maybe cache this but it'd be app dependent as to
    // whether that'd make sense or not.
    Dictionary<string, List<LookupDataItem>> data = new Dictionary<string, List<LookupDataItem>>();

    using (NorthwindDataDataContext ctx = new NorthwindDataDataContext())
    {
      data.Add("suppliers",
        (
          from s in ctx.Suppliers
          select new LookupDataItem()
          {
            Id = s.SupplierID,
            Title = s.CompanyName
          }
        ).ToList());

      data.Add("categories",
        (
          from c in ctx.Categories
          select new LookupDataItem()
          {
            Id = c.CategoryID,
            Title = c.CategoryName
          }
        ).ToList());
    }
    return (data);
  }

  [PrincipalPermission(SecurityAction.Demand, Role = "users")]
  public ProductSearchResult GetProducts(int pageNumber, int pageSize, string matchString)
  {
    ProductSearchResult result = null;

    using (NorthwindDataDataContext ctx = new NorthwindDataDataContext())
    {
      ctx.DeferredLoadingEnabled = false;

      var query =
        (
          from p in ctx.Products
          select p
        );

      if (!string.IsNullOrEmpty(matchString))
      {
        query = from p in query
                where p.ProductName.ToLower().Contains(matchString.ToLower())
                select p;
      }
      int totalRecords = query.Count(); // Round trip #1.

      query =
        (
          from p in query
          orderby p.ProductID ascending
          select p
        ).Skip(pageNumber * pageSize).Take(pageSize);

      result = new ProductSearchResult()
      {
        Page = pageNumber,
        TotalPages = (int)Math.Ceiling(totalRecords / (double)pageSize),
        Products = query.ToList() // Round trip #2.
      };
    }
    return (result);
  }

  [PrincipalPermission(SecurityAction.Demand, Role = "users")]
  [PrincipalPermission(SecurityAction.Demand, Role = "viewers")]
  public List<ProductSales> GetProductSales(int productId)
  {
    List<ProductSales> sales = null;

    using (NorthwindDataDataContext ctx = new NorthwindDataDataContext())
    {
      var query =
        from od in ctx.Order_Details
        where od.ProductID == productId
        group od by new
        {
          Month = od.Order.OrderDate.Value.Month,
          Year = od.Order.OrderDate.Value.Year
        }
          into grouped
          orderby grouped.Key.Year, grouped.Key.Month ascending
          select new ProductSales
          {
            TotalSales = grouped.Sum(x => (x.Quantity * x.UnitPrice) - (decimal)x.Discount),
            MonthYearLabel = string.Format("{0}, {1}",
              grouped.Key.Month, grouped.Key.Year)
          };

      sales = query.ToList();
    }
    return (sales);
  }

  [PrincipalPermission(SecurityAction.Demand, Role = "users")]
  [PrincipalPermission(SecurityAction.Demand, Role = "editors")]
  public List<int> UpdateProducts(List<ProductChangeEntry> entries)
  {
    List<int> changedIds = new List<int>();

    using (NorthwindDataDataContext ctx = new NorthwindDataDataContext())
    {
      foreach (var item in entries)
      {
        changedIds.Add(item.CurrentProduct.ProductID);
        ctx.Products.Attach(item.CurrentProduct, item.OriginalProduct);
      }
      try
      {
        ctx.SubmitChanges(ConflictMode.ContinueOnConflict);
      }
      catch (ChangeConflictException)
      {
        foreach (var item in ctx.ChangeConflicts)
        {
          changedIds.Remove(((Product)item.Object).ProductID);

          item.Resolve(RefreshMode.OverwriteCurrentValues);
        }
        try
        {
          ctx.SubmitChanges(ConflictMode.ContinueOnConflict);
        }
        catch
        {
          changedIds = null;
        }
      }
      catch
      {
        changedIds = null;
      }
    }
    return (changedIds);
  }

  [PrincipalPermission(SecurityAction.Demand, Role = "users")]
  [PrincipalPermission(SecurityAction.Demand, Role = "editors")]
  public void InsertProducts(List<Product> products)
  {
    // TODO: Not doing much with errors here right now.
    using (NorthwindDataDataContext ctx = new NorthwindDataDataContext())
    {
      ctx.Products.InsertAllOnSubmit(products);

      try
      {
        ctx.SubmitChanges(ConflictMode.ContinueOnConflict);
      }
      catch
      {
        // TODO: Do something sensible here, alter return value.
      }
    }
  }
}

Ok, now I’ve got some services to work against, I can build a data library that makes use of those services in order to allow the controls that I want to build to data-bind.

Next post – building some data classes on the client side…