Dieg's world

iCarMode - available on the appStore !!

2011-08-26T01:43:00.002+03:00

Today my post is not another .net/sql/programming post.

Today I am happy to present my new iPhone app 1st release.

The application is called - iCarMode and it's a joined effort of Mark Mishaev & myself.

Small quote from the appStore description:

"iCarMode presents a new user interface speacilly designed to allow safer use of your iPhone while driving.

The large buttons give you a quick and comfortable access to your favorite contacts, iPod playlists, navigation and other useful features."

If you ever tried to play music, make a phone call or find your favorite GPS app from your (hopefully) docked iPhone you probably noticed how uncomfortable it is, if not to say - dangerous.

iCarMode is meant to expose a simpler UI to allow you to perform those actions in a safer mode.

Here's the main screenshot:

For more details, goto the product's new website: http://www.iCarMode.com
And don't forget to download, rate and spread the word at the appStore.

Best Regards,
Diego

Web Application Automatic Deployment using MsBuild with Microsoft SDC Task Library

2011-05-31T15:31:00.014+03:00

Introduction

Projects commonly have a web application component, be it a front end or in the form of WebServices. This article will demonstrate how the web applications can be automatically deployed and configured under IIS using the Microsoft SDC Task Library extensions for MsBuild.

The SDC Task Library is free and available at http://codeplex.com/sdctasks Over 300 tasks included in this library including tasks for: creating websites, creating application pools, creating ActiveDirectory users, running FxCop, configuring virtual servers, creating zip files, configuring COM+, creating folder shares, installing into the GAC, configuring SQL Server, configuring BizTalk 2004 and BizTalk 2006 etc.

Step 1

Create project xml file and import SDC tasks

<Project xmlns="http://schemas.microsoft.com/developer/msbuild/2003">
 <Import Project="Microsoft.Sdc.Common.tasks"/>
 …
</Project>

Step 2

Create deployment Work Plan

<Target Name="Deploy_Webapp">
 <CallTarget Targets="CopyFolders" />
 <CallTarget Targets="SetPermissions" />
 <CallTarget Targets="CleanIIS" />
 <CallTarget Targets="CreateWebSite" />
</Target>

Step 3

Implement every Target in Work Plan, let's focus on CreateWebSite. First of all I will create a WebSite and then I will create a VirtualDirectory.

<Target Name="CreateWebSite" DependsOnTargets="CleanIIS">
 <Web.WebSite.Create
    Description="testWebSite"
    Path="C:\Server"
    MachineName="localhost"/>
 <Web.WebSite.CreateVirtualDirectory
    VirtualDirectoryName="testVD"
    Path="C:\Server"
    MachineName="localhost"
    AppPoolID="DefaultAppPool"
    AppCreate="True"
    WebSiteName="testWebSite"
    AuthFlags="NTLM"/>
</Target>

CreateVirtualDirectory attributes:

virtualDirectoryName - name of the virtual driectory to create
path - Path to map the virtual directory to.
machineName - Machine to add the virtual directory on.
appPoolID - Application pool to run the virtual directory under
appCreate - Set to "true" to create an application for this virtual directory.
webSiteName - Web site to attach the virtual directory onto.
authFlags - Authentication flags to apply to the directory.

Step 4

Create batch file to make all this easier to run

@ECHO OFF

IF EXIST %windir%\Microsoft.NET\Framework64\v2.0.50727\ (
 %windir%\Microsoft.NET\Framework64\v2.0.50727\msbuild configServer.xml
) ELSE (
 %windir%\Microsoft.NET\Framework\v2.0.50727\msbuild configServer.xml
)

Finally

Microsoft SDC Task Library extensions for MsBuild provides us much more options, You can read a manual that comes with it in order to learn more.

Complite Example: here

Using DataAnnotations to validate entities

2011-05-08T22:19:00.000+03:00

In every sample on the internet regarding MVC and Entity Framework 4.0, we can find the use of DataAnnotations.

DataAnnotations allows us to add to our entities some Meta data including simple rules of validation (as well as hiding column, defining display name etc.).
By simple validation rules I mean we can define:
- Required fields
- String Length
- Range

Adding DataAnnotations is quite simple, after adding a reference to System.ComponentModel.DataAnnotations, we can add a partial class extending the relevant entity from our Entity Framework edmx and defining a metadatatype class with the rules.
For example:

    [MetadataType(typeof(TrackMetaData))]
    public partial class Track
    {
        public class TrackMetaData
        {
            [ScaffoldColumn(false)]
            [DisplayName("Track Id")]
            public int Id { get; set; }

            [StringLength(50)]
            public string Name{ get; set; }

            [Range(typeof(DateTime), "1/1/1753", "31/12/9999",
               ErrorMessage = "Value for {0} must be between {1} and {2}")]
            [DisplayName("Play Date")]
            public Nullable PlayDate { get; set; }
        }
    }
}

In this example we can see the use of ScaffoldColumn, StringLegth, DisplayName & Range...for those who work on multi language system or just prefer - you can retrieve the error message from a resource file as well.

Adding this class will work for MVC, MVC's plumbing will use this class to show error messages when relevant...but what if we have another type of client and we want to make sure data is validated before saving it to database?

Add this simple helper class to your project (or even better to your framework/core/library):

   public class ValidationHelper
    {
        /// 
        /// Check if specified object is valid
        /// 

        /// The object to validate        /// 
        public static bool IsValid(object obj)
        {
            return (!Validate(obj).Any());
        }

        /// 
        /// Validate an object against Data Annotations meta data defined against the object 
        /// 

        /// The object to validate        /// A List of  
        public static List Validate(object obj)
        {
            Type instanceType = obj.GetType();
            Type metaData = null;
            MetadataTypeAttribute[] metaAttr = (MetadataTypeAttribute[])instanceType.GetCustomAttributes(typeof(MetadataTypeAttribute), true);

            if (metaAttr.Count() > 0)
            {
                metaData = metaAttr[0].MetadataClassType;
            }
            else
            {
                throw new InvalidOperationException("Cannot validate object, no metadata assoicated with the specified type");
            }

            TypeDescriptor.AddProviderTransparent(
            new AssociatedMetadataTypeTypeDescriptionProvider(instanceType, metaData), instanceType);

            List results = new List();
            ValidationContext ctx = new ValidationContext(obj, null, null);

            bool valid = Validator.TryValidateObject(obj, ctx, results, true);
            return results;
        }

        /// 
        /// Get Validation errors as string
        /// 

        /// The object to validate        /// 
        public static string GetValidationErrors(object obj)
        {
            List errors = Validate(obj);

            var errorText = new StringBuilder();
            foreach (var error in errors)
            {
                errorText.Append(error.ErrorMessage + Environment.NewLine);
            }
            return errorText.ToString();
        }
    }

Than..add a few lines of code to your UnitOfWork (see my previous post for details: EF4 Self Tracking Entities & Repository design pattern)

    public class UnitOfWork:IUnitOfWork, IDisposable
    {

        public void ApplyChanges(string entityName, object entity)
        {
            if (entity == null)
                return;

            if (entity is IObjectWithChangeTracker)
            {
                bool ok = ValidationHelper.IsValid(entity);

                if (!ok)
                {
                    string message = string.Format("Can not apply changes to the '{0}' entity due to validation errors", entity.ToString());
                    LogUtil.LogInfo(string.Format("{0} ({1})", message, ValidationHelper.GetValidationErrors(entity)));

                    throw new ValidationException(message);
                }

                _context.ApplyChanges(entityName, (IObjectWithChangeTracker)entity);
            }
            else
            {
                throw new ArgumentException("entity must implement IObjectWithChangeTracker to use applyChanges");
            }
        }
    }

That's it..before the save of every entity (you implemented a MetadataType class for), it will validate the object and refuse to applyChanges or any other policy you decide is suitable to your project.

Happy validation,
Diego

EF4 Self Tracking Entities & Repository design pattern

2011-04-07T01:33:00.004+03:00

Today I'm going to talk about a new sample project I was building these days just to get to know Entity Framework 4.0 better.

After reading Mark Mishaev's post here (Building N-Tier Applications with Entity Framework 4) & a few of his references I decided to explore the self tracking entities.

As I see it, Self tracking entities is one of the most basic features the previous version of EF was missing.

Self tracking entities as the name implies is the ability of our entities to contain their state, if you're familiar with datasets & their diffgram capability than you probably used the RowState property to detect which rows are new, which are updated, deleted or unchanged - that is what tracking means.

With STE (self tracking entities) you can send your entities to your client & when receiving it back, detect easily all the changes your client made and save it to your database (of course after choosing the right concurrency strategy for your application), without it - you'll find yourself comparing the returned entity with your database to decide which changes were made & setting their state one by one before you can actually save.

So STE is the ultimate solution? Not for sure....one of the big disadvantages of STE is the fact that your client must know the actual entity - meaning:
1. It will work only if you code both the server & client (it's not always the case), proxies passing the entity's datamembers won't do the tracking stuff...
2. Your client must be .net client.
3. Any changes to entity structure/logic will require client publishing (a big minus architecturally speaking).

If you're absolutely sure you can ignore these disadvantages in your specific application - you will gain a simple & great way to track changes out of the box.

Searching for the best way to implement the use of EF I encounter many discussions about a few related patterns, the two that repeatedly stood out were Repository & UnitOfWork.

To make a long reading short..

Repository will help us separating our business logic layer from knowing anything about entity framework - potentially can allow us to control our dataAccess layer behavior (cache, trace, security etc) better and change the implementation without changing the whole application, it will also allow us to replace the repository with an in-memory repository which can be a great way to unit test our application without a database.

namespace Dieg.Framework.DataAccess
{
    public interface IRepository
    { 
        T GetById(int id);
        IEnumerable GetAll();
        IEnumerable Query(Expression<Func<T, bool>> filter);
        void Add(T entity);
        void Remove(T entity);

        void ApplyChanges(T entity);
    }
}

Implementation:

namespace Dieg.Framework.DataAccess
{
    public abstract class Repository : IRepository where T : class
    {
        protected IObjectSet _objectSet;
        protected IUnitOfWork _uow;

        public Repository(IUnitOfWork uow) 
        {
            _uow = uow;
            _objectSet = _uow.CreateObjectSet();
        }

        public abstract T GetById(int id);

        public IEnumerable GetAll()
        {
            return _objectSet;
        }

        public IEnumerable Query(System.Linq.Expressions.Expression<Func<T, bool>> filter)
        {
            return _objectSet.Where(filter);
        }

        public void Add(T entity)
        {
            _objectSet.AddObject(entity);
        }

        public void Remove(T entity)
        {
            _objectSet.DeleteObject(entity);
        }

        public abstract string Name
        {
            get;
        }

        public abstract void ApplyChanges(T entity);
    }
}

Implementing a specific repository, I prefer implementing a separate repository for each entity this way we can choose a unique behavior for each entity (for example: not all entities allow all CRUD operations, maybe there are different authorization rules for some entities etc).

namespace Dieg.MusicLibrary.DataAccess
{
    public class ArtistRepository:Repository
    {
        public const string ENTITY_NAME = "Artist"; 

        public ArtistRepository(UnitOfWork uow):base(uow)
        { }

        public override Artist GetById(int id)
        {
            return _objectSet.SingleOrDefault(a => a.Id == id);
        }

        public override string Name
        {
            get { return ENTITY_NAME; }
        }


        public override void ApplyChanges(Artist entity)
        {
            _uow.ApplyChanges(Name, entity);
        }
    }
}

UnitOfWork - according to Martin Fowler, the Unit of Work pattern "maintains a list of objects affected by a business transaction and coordinates the writing out of changes and the resolution of concurrency problems." (The Unit Of Work Pattern And Persistence Ignorance).

namespace Dieg.Framework.DataAccess
{
    public interface IUnitOfWork
    {
        IObjectSet CreateObjectSet() where T : class;

        void SaveChanges();

        void ApplyChanges(string entityName, object entity);
    }
}

ApplyChanges method will contain the STE implementation of updating our entities state, since ApplyChanges is per entity, we can control in our BL which entities should be influenced by the specific BL method and avoid saving irrelevant changes.

Notice the implementation will have to be in the dataAcess layer project & not part of the 'framework/core/lib', since we want it to use the STE specific context extensions.

namespace Dieg.MusicLibrary.DataAccess
{
    public class UnitOfWork:IUnitOfWork, IDisposable
    {
        private readonly DiegMusicLibraryContainer _context;

        public UnitOfWork()
        {
            _context = new DiegMusicLibraryContainer();
        }
        
        public void SaveChanges()
        {
            _context.SaveChanges();
        }

        public void Dispose()
        {
            _context.Dispose();
        }

        public IObjectSet CreateObjectSet() where E : class
        {
            return _context.CreateObjectSet();
        }

        public void ApplyChanges(string entityName, object entity)
        {
            if (entity is IObjectWithChangeTracker)
            {
                _context.ApplyChanges(entityName, (IObjectWithChangeTracker)entity);
            }
            else
            {
                throw new ArgumentException("entity must implement IObjectWithChangeTracker to use applyChanges");
            }
        }
    }
}

As I mentioned earlier, when using STE the client must have a reference to the entities, so we'll have to separate the entities from the EF context & edmx code, this can easily done using T4 templates (see :How to Separate Self-Tracking Entities to Their Own Class Library (by Gil Fink)).

Let's code a simple test to see the basic concept:

static void Main(string[] args)
        {
            Artist artist;
            
            using (UnitOfWork uow = new UnitOfWork())
            {
                ArtistRepository Artists = new ArtistRepository(uow);

                foreach (var Artist in Artists.GetAll())
                {
                    Console.WriteLine(Artist.Name);
                }
                
                artist = Artists.GetById(1);

            }

            /*unitOfWork (which holds the objectContext) is disposed here
            this will happen also when sending objects through a
              WCF (or similar) service
            */

            //change something...
            artist.ChangeTracker.ChangeTrackingEnabled = true;
            artist.Name = string.Concat(artist.Name,"AAA");


            using (UnitOfWork uow2 = new UnitOfWork())
            {
                ArtistRepository Artists = new ArtistRepository(uow2);

                //calling ApplyChanges will update the state of the artist
                //using behind the scense the STE Changetracker
                //without this the save won't recognize any changes -
                // comment the following line & try it out!!
                Artists.ApplyChanges(artist);

                uow2.SaveChanges();

                Artist b = Artists.GetById(1);
                Console.WriteLine(b.Name);
            }

            Console.ReadLine();
        }

Good luck!
Diego

Grid view in MVC using mvcContrib

2011-01-29T11:59:00.001+02:00

Last post I showed a simple way to display images in your html using an MVC controller Displaying images from a database - MVC style.

But then it got me thinking...what if I have a lot of records ? what if I want to allow the users to search for a specific record?
So the answer is of course - adding some sort of page/sort/filter control/s
in ASP.NET we just needed to pick one from millions..what about MVC?

Googling through this topic I found all sort of HTML helpers some free & some commercial, the 1st one to catch my eye and later on proven to be a good catch was mvcContrib.

I will show you a small sample I built based on Raj Kaimal blog using mvcContrib.

Lets start from a few semi-new concepts when developing in MVC:

1. ViewModels:
The architectural hype these days is definitely ORM tools like EntityFramework, NHibernate etc - These frameworks and others were invented to decouple the database structure from the logical entities defined in our application and map between them to allow transferring data back and forward.
The thing is that before these tools came we were working with smaller sets of data, retrieving only the data we needed for display and sometimes modeling it for smoother work in a specific view.
For this type of work we will use ViewModel, view-model is a portion of our model built specific for a single view (or more if same that is display in more than one way), it decouple the view from the model, allows us to work with a smaller set of data in a structure adjusted to the view but still keep it strongly typed.

2. Controllers
Controllers as their name suggests - control, they are the main MVC concept, Controllers are the gateway of the view to the application & data - building a wall between them - a real wall - not an imaginary one as in webform ASP.NET - this "wall" will allow us to serve the same services for different types of views (web applications, webservices, smart phones etc).

Let's see an implementation of these concepts.

In every grid view there are 3 different views:
1. The filter view.
2. The main view.
3. The paging view.

Every view has its own viewModel.
The filter will need a viewModel which describes the filter, a list of items for a combo filter, a datetime for a datepicker etc
The main view will probably need columns from all sorts of entities, some of them could be "translated" using some sort of lookup table, some maybe formatted differently for this view etc.
The paging view will need a viewModel that describes the paging ruler, in the case of mvcContrib - implementation of IPagination.
The main view will also need to implement GridSortOptions to allow sorting.

Sounds kinda complicated, but actually it couldn't be easier...

Filter's view model will contain a list that will be represented as a dropdown in the view:

public class AlbumFilterViewModel
    {
        public AlbumFilterViewModel()
        {
            SelectedArtistId = -1;
        }

        public int SelectedArtistId { get; set; }

        public List Artists { get; set; }
    }

The main view view-model, this will contain the columns we want to display in the gridview, notice that we can change the way the columns are displayed using simple attributes, this could be implemented for a specific view or at the entity level exploiting the fact that EF entities are define as partial classes (using DataAnnotations: [MetadataType(typeof(AlbumMetaData))]):

public class AlbumListViewModel
    {
        [ScaffoldColumn(false)]  //this columns won't be shown in view
        public int ArtistId { get; set; }

        [ScaffoldColumn(false)]
        public int AlbumId { get; set; }

        [DisplayName("Artist Name")] //change the display name of the column
        public string ArtistName { get; set; }

        [DisplayName("Album Name")]
        public string Name { get; set; }

        [DisplayName("Cover")]
        public byte[] Picture { get; set; }

        [DisplayName("Last Updated Date")]
        [DisplayFormat(DataFormatString = "{0:g}")] //format the date
        public DateTime LastUpdatedDate { get; set; }

    }

As I mentioned before the main view implements the acutal grid and also the paging and sorting, the viewModel will contain the previous one plus the paging and sort definition. it will look like this:

public class AlbumListContainerViewModel
    {
        //paging
        public IPagination AlbumPageList { get; set; }
        //main view
        public AlbumFilterViewModel AlbumFilterViewModel { get; set; }
        //sort
        public GridSortOptions GridSortOptions { get; set; }
    }

Now lets go to the view.
We'll have 3 different partial views (ascx):
1. The filter.
2. The main view.
3. The paging ruler that we'll had before and after the main view.

The container will look something like this:

<% Html.RenderPartial("SearchFilter", Model.AlbumFilterViewModel); %>
    <% Html.RenderPartial("Pager", Model.AlbumPageList); %>
    <% Html.RenderPartial("SearchResult", Model); %>
    <% Html.RenderPartial("Pager", Model.AlbumPageList); %>

The filter view html is quite simple, notice that the view is strongly typed:

<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl" %>
.
.
            

<%:Html.DropDownList("ArtistId", Model.Artists, "-- All --", htmlAttributes)%>


.
.

The search result view code, you will see the mvcContrib's html helpers does everything, the only two place I wrote my own code was to specify a different behavior for two columns, one is the "More.." column which is linked to a detail view of the chosen row and a the image column which is linked to the controller that retrieve the image from the database:

<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl" %>
<%@ Import Namespace="MvcContrib.UI.Grid" %>
<%@ Import Namespace="DiegJukeboxRemote.Models" %>
<%@ Import Namespace="DiegJukeboxRemote.HtmlHelpers" %>
<%= Html.Grid(Model.AlbumPageList).AutoGenerateColumns()
    .Columns(column => {
        column.For(a => Html.ActionLink("More..", "ThumbChooserEdit", new { id = a.AlbumId })).InsertAt(4).Encode(false);
    })
    .Columns(column=> {
        column.For(a => Html.Image(a.AlbumId, "RetrieveImage", "MusicInfo", 
            new Dictionary() { 
                                                { "Height", "100" }, { "Width", "100" },
                                                {"onerror", "onImgErrorSmall(this)"}
            })).InsertAt(3).Encode(false);
    })
    .Sort(Model.GridSortOptions)
    .Attributes(@class => "table-list")
%>

The pager couldn't be simpler:

<%@ Control Language="C#" Inherits="System.Web.Mvc.ViewUserControl" %>
<%@ Import Namespace="MvcContrib.UI.Pager" %>
<%= Html.Pager(Model)
        .First("First")
        .Last("Last")
        .Next("Next")
        .Previous("Previous") %>

Finally the controller, using LINQ we retrieve the data we need from artist/album repositories, you will see here two special points:
1. Using System.Web.Mvc.SelectListItem for the dropdownlist.
2. Using MvcContrib.UI.Grid.GridSortOptions & MvcContrib.Pagination.PaginationHelper so mvcContrib helpers can do the rest :-)

public ActionResult ThumbChooser(int? artistId, GridSortOptions gridSortOptions, int? page, bool? MissingCoverOnly)
        {
            IQueryable albumList = null;

            if (!MissingCoverOnly.HasValue || !MissingCoverOnly.Value)
            {
                albumList = _BL.GetAlbumListView();
            }
            else
            {
                albumList = _BL.GetAlbumListViewMissingCover();
            }

            if (string.IsNullOrWhiteSpace(gridSortOptions.Column))
            {
                gridSortOptions.Column = "ArtistId";
            }

            if (artistId.HasValue)
            {
                albumList = albumList.Where(a => a.ArtistId == artistId.Value);
            }

            var albumFilter = new AlbumFilterViewModel();
            albumFilter.SelectedArtistId = artistId ?? -1;

            albumFilter.Artists = _BL.GetArtistList().OrderBy(art=>art.Name)
                                    .Select(a => new { a.ArtistId, a.Name })
                                    .ToList().Select(a =>
                                        new SelectListItem
                                        {
                                            Text = a.Name.Length > 20 ? string.Concat(a.Name.Substring(0,20),"...") : a.Name,
                                            Value = a.ArtistId.ToString(),
                                            Selected = a.ArtistId == albumFilter.SelectedArtistId
                                        }).ToList();

            var albumPageList = albumList
                                .OrderBy(gridSortOptions.Column, gridSortOptions.Direction)
                                .AsPagination(page ?? 1, 10);

            var albumListContainer = new AlbumListContainerViewModel
            {
                AlbumFilterViewModel = albumFilter,
                AlbumPageList = albumPageList,
                GridSortOptions = gridSortOptions
            };

            return View(albumListContainer);
        }

That's it!

Till next time...
Diego

Building N-Tier Applications with Entity Framework 4

2011-01-08T10:56:00.064+02:00

Introduction

This post describes using of EF4 for building N-Tier applications. I've decided to write about this topic, after reading an excellent series of articles about N-Tier applications by Daniel Simmons:

1. N-Tier Application Patterns
2. Anti-Patterns to Avoid in N-Tier Applications
3. Building N-Tier Apps with EF 4

Those articles provide in-depth explanation about different issues and considerations we need to take into account, while building N-Tier applications.

Self-Tracking Entities

Self-tracking entities are smart objects with an ability to keep track on their own changes. The key difference between them and regular datasets is that self-tracking entities are plain-old CLR objects (POCO) and consequently are not tied to any specific persistance technology. They are relatively simple objects that represent the entities and information about changes that they went through.

T4 Templates

T4 templates are text templates that contain text blocks and control logic mixed together and can generate a text file, similar to Velocity Template Engine used in Java.

In our example we will use T4-based code generator to create our self-tracking entities.
You need to download and install it before you start.

Building N-Tier Application

The application we're going to build is based on the example from Daniel Simmons third article and uses Northwind database as a back-end.

Let's start with building our data access layer project:

New Project--->Class Library Project, name it Northwind.DAL
Add--->New Item--->ADO.NET Entity Data Model

We will generate our model from the database:

Our model will look like this:

Right-click on the model designer--->Add Code Generation Item--->ADO Self-Tracking Entity Generator

This action will add T4 template to our project. You can also notice that it will disable the default code generation for our EF model

Now we're going to move our newly generated self-tracking entities to separate project in order to decouple the DAL project from the entities.

Right-click on the solution item--->Add New Project--->Class Library project, name it Northwind.Entities.
Afterwards, you simply cut the Northwind.tt item and paste it into Northwind.Entities project.
Don't forget to reference System.Runtime.Serialization assembly in the Northwind.Entities project and add a reference to Northwind.Entities project in Northwind.DAL project.

We're ready to add WCF service layer to our application.
Right-click on the solution item--->Add New Project--->WCF Service Application project, name it Northwind.Service.
Rename the created service definition interface to INorthwindService and move it to the entities project.

Add following methods to the INorthwindService:

[ServiceContract]
public interface INorthwindService
{
[OperationContract]
IEnumerable<products> GetProducts();

[OperationContract]
Customers GetCustomer(string id);

[OperationContract]
bool SubmitOrder(Orders order);    
}

I know that in a real application we would add additional layers like Business Logic layer and Service Interface layer, but for the sake of simplicity we will leave it out.

Let's add an implementation to our service:

public class NorthwindService : INorthwindService
{
public IEnumerable<products> GetProducts()
{
using (var ctx = new NorthwindEntities())
{
return ctx.Products.ToList();
}
}

public Customers GetCustomer(string id)
{
using (var ctx = new NorthwindEntities())
{
return ctx.Customers.Include("Orders")
.Where(c => c.CustomerID == id)
.SingleOrDefault();
}
}

public bool SubmitOrder(Orders newOrder)
{
using (var ctx = new NorthwindEntities())
{
ctx.Orders.ApplyChanges(newOrder);
return ctx.SaveChanges() > 0;
}
}
}

Please note the Include method used in GetCustomer method. This method allows so-called eager loading of Orders table to reduce the number of round-trips to the database.
The detailed information about different loading data options in EF may be found here.

Instead of building some "state of art" client to test our service, let's add a real test project to our solution:

Right-click on the solution item--->Add New Project--->Test Project, name it Northwind.Test

You have to add a reference to Northwind.Service and Northwind.Entities. Don't forget to clean up all the proxies automatically generated for the entities.

Add following test methods to the test class:

[TestClass]
public class NorthwindTest
{
public NorthwindTest()
{
}

private TestContext testContextInstance;

/// 
///Gets or sets the test context which provides
///information about and functionality for the current test run.
///

public TestContext TestContext
{
get
{
return testContextInstance;
}
set
{
testContextInstance = value;
}
}



[TestMethod]
public void TestGetProducts()
{
using (NorthwindServiceClient client = new NorthwindServiceClient())
{
List<Products> products = client.GetProducts();

Assert.IsTrue(products.Count > 0);
}
}

[TestMethod]
public void TestGetCustomer()
{
using (NorthwindServiceClient client = new NorthwindServiceClient())
{
Customers customer = client.GetCustomer("ALFKI");
Assert.IsNotNull(customer);
}
}

[TestMethod]
public void TestSubmitOrder()
{
using (NorthwindServiceClient client = new NorthwindServiceClient())
{
var products = new List<Products>(client.GetProducts());
Assert.IsTrue(products.Count > 0);

var customer = client.GetCustomer("ALFKI");
Assert.IsNotNull(customer);               

try
{
// add a new order
var newOrder = new Orders();
newOrder.OrderDate = DateTime.Now;
newOrder.RequiredDate = DateTime.Now;

var orderedProduct = 
products.Where(p => p.ProductName ==  "Chang")
.Single();
Order_Details orderDetails = new Order_Details()
{
ProductID = orderedProduct.ProductID,
Quantity = 1

};
newOrder.Order_Details.Add(orderDetails);                                       
customer.Orders.Add(newOrder);

var submitSuccess = client.SubmitOrder(newOrder);
Assert.IsTrue(submitSuccess);
}
catch (Exception ex)
{
TestContext.WriteLine(ex.StackTrace);
Assert.Fail();
}
}
}
}

I think we're done.

To see your application in action just run all test methods from the "Test" menu.

The complete code could be downloaded here.

This is it,

Mark.

ComboBox SelectedValueChanging Event

2010-12-23T17:41:00.008+02:00

Introduction

For some reason, a standard WinForm ComboBox does not contain SelectedValueChanging event, which may be useful if you're requested to intercept a change or cancel it.

After searching for a possible solution on the web, I've found a nice example and adopted it with slight modifications.

SelectedValueChanging Event

Generally, subclassing common WinForm controls is a good idea, since it allows you to customize their appearance and behavior in a single place and affect all instances in the entire application.

In this case, we will subclass a ComboBox class and add the SelectedValueChanging event:


public partial class nessComboBox : ComboBox
{
  public event CancelEventHandler SelectedValueChanging;

  private object m_LastAcceptedSelectedValue;
  private bool m_IgnoreNullPreviousValueChanging = false;

  public nessComboBox()
  {
      InitializeComponent();
  }
    
  [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
  [Browsable(false)]
  public object LastAcceptedSelectedValue
  {
      get { return m_LastAcceptedSelectedValue; }
      private set { m_LastAcceptedSelectedValue = value; }
  }

  [DesignerSerializationVisibility(DesignerSerializationVisibility.Hidden)]
  [Browsable(false)]
  public bool IgnoreNullPreviousValueChanging
  {
      get { return m_IgnoreNullPreviousValueChanging; }
      set { m_IgnoreNullPreviousValueChanging = value; }
  }

  protected void OnSelectedValueChanging(CancelEventArgs e)
  {
      if (SelectedValueChanging != null)
          SelectedValueChanging(this, e);
  }

  protected override void OnSelectedValueChanged(EventArgs e)
  {
      if (SelectedValueChanging != null)
      {
          if ((!m_IgnoreNullPreviousValueChanging ||
               LastAcceptedSelectedValue != null) &&
               (LastAcceptedSelectedValue ?? string.Empty).ToString()
               !=  (SelectedValue ?? string.Empty).ToString())
          {
              CancelEventArgs cancelEventArgs = new CancelEventArgs();
              OnSelectedValueChanging(cancelEventArgs);

              if (!cancelEventArgs.Cancel)
              {
                  LastAcceptedSelectedValue = SelectedValue;
                  base.OnSelectedValueChanged(e);
              }
              else
                  SelectedValue = LastAcceptedSelectedValue;
          }
          else if (m_IgnoreNullPreviousValueChanging &&
                      LastAcceptedSelectedValue == null
                      && SelectedValue != null)
          {
              LastAcceptedSelectedValue = SelectedValue;
          }
      }
      else
      {
          base.OnSelectedValueChanged(e);
      }
  }
}

As you can see, the overrided OnSelectedValueChanged method performs all required logic: checks if there are subscribers to the new event and raises it before calling of the OnSelectedValueChanged method.

The variable m_IgnoreNullPreviousValueChanging may be used to ignore the initial selection change from null to some specific value in case of data-binded combo.

That's it,
Mark.

WCF Data Services

2010-12-20T15:31:00.031+02:00

Introduction

WCF technology provides several types of services sharing the same undelying infrastructure and we can choose the appropriate service type based on our needs:

In this post, I would like to make a quick overview of WCF Data Services.

According to MSDN, WCF Data Services (formerly known as "ADO.NET Data Services") is a component of the .NET Framework that enables you to create services that use the Open Data Protocol (OData) to expose and consume data over the Web or intranet by using the semantics of representational state transfer (REST).

The main advantage of using WCF Data Services is that it allows easy access to data from any client that supports OData.

Visual Studio makes it easy to create OData service by utilizing an ADO.NET Entity Framework data model.

The following example is based on WCF Data Service Quickstart and built with Visual Studio 2010.

WCF Data Services Quick Start

The example is divided into four steps:

Creating a simple ASP.NET application
Defining a data model based on the well-known Northwind database by using the Entity Framework 4
Adding the data service to to the web application
Creating a WPF client that consumes the service

Let's start with the ASP.NET application:

File--->Project--->ASP.NET Web Application

Name it NorthwindService.

Next step is creating the corresponding data model:

Right-click the name of the ASP.NET project--->Add New Item--->ADO.NET Entity Data Model

For the name of the data model, type Northwind.edmx

Third step is creating the data service:

Right-click the name of the ASP.NET project--->Add New Item--->WCF Data Service

For the name of the service, type Northwind

After completing of these three steps, your project might look like this:

In order to enable access to our data service, we need to grant rights to the particular enities within the model:


public static void InitializeService(DataServiceConfiguration config)
{
    config.SetEntitySetAccessRule("Orders", EntitySetRights.AllRead
      | EntitySetRights.WriteMerge
      | EntitySetRights.WriteReplace);

    config.SetEntitySetAccessRule("Order_Details", EntitySetRights.AllRead
      | EntitySetRights.AllWrite);

    config.SetEntitySetAccessRule("Customers", EntitySetRights.AllRead);

}

Our final step will be creating a simple WPF client for consuming and modifying the model data:

In Solution Explorer, right-click the solution,
click Add--->New Project--->WPF Application.

Enter NorthwindClient for the project name.
Replace the existing code in MainWindow.xaml with this code:


<Window x:Class="MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="Northwind Orders" Height="335" Width="425"
Name="OrdersWindow" Loaded="Window1_Loaded">
<Grid Name="orderItemsGrid">
<ComboBox DisplayMemberPath="OrderID" ItemsSource="{Binding}"
         IsSynchronizedWithCurrentItem="true"
         Height="23" Margin="92,12,198,0" Name="comboBoxOrder" VerticalAlignment="Top"/>
<DataGrid ItemsSource="{Binding Path=Order_Details}"
         CanUserAddRows="False" CanUserDeleteRows="False"
         Name="orderItemsDataGrid" Margin="34,46,34,50"
         AutoGenerateColumns="False">
   <DataGrid.Columns>
       <DataGridTextColumn  Header="Product" Binding="{Binding ProductID, Mode=OneWay}" />
       <DataGridTextColumn  Header="Quantity" Binding="{Binding Quantity, Mode=TwoWay}" />
       <DataGridTextColumn  Header="Price" Binding="{Binding UnitPrice, Mode=TwoWay}" />
       <DataGridTextColumn  Header="Discount" Binding="{Binding Discount, Mode=TwoWay}" />       
   </DataGrid.Columns>
</DataGrid>
<Label Height="28" Margin="34,12,0,0" Name="orderLabel" VerticalAlignment="Top"
      HorizontalAlignment="Left" Width="65">Order:</Label>
<StackPanel Name="Buttons" Orientation="Horizontal" HorizontalAlignment="Right"
           Height="40" Margin="0,257,22,0">
   <Button Height="23" HorizontalAlignment="Right" Margin="0,0,12,12"
       Name="buttonSave" VerticalAlignment="Bottom" Width="75"
           Click="buttonSaveChanges_Click">Save Changes
   </Button>
   <Button Height="23" Margin="0,0,12,12"
       Name="buttonClose" VerticalAlignment="Bottom" Width="75"
           Click="buttonClose_Click">Close</Button>
</StackPanel>
</Grid>
</Window>

It will give the following look to our client:

We need to add a data service reference to the client project:

Right-Click the project--->Add Reference--->Discover
In the Namespace text box, type Northwind

The only thing left is to to access the service data and we're done.
Copy this code into MainWindow.xaml.cs:



private NorthwindEntities context;
private string customerId = "ALFKI";

// Replace the host server and port number with the values
// for the test server hosting your Northwind data service instance.
private Uri svcUri = new Uri("http://localhost:12345/Northwind.svc");

private void Window1_Loaded(object sender, RoutedEventArgs e)
{
 try
 {
     // Instantiate the DataServiceContext.
     context = new NorthwindEntities(svcUri);

     // Define a LINQ query that returns Orders and
     // Order_Details for a specific customer.
     var ordersQuery = from o in context.Orders.Expand("Order_Details")
                       where o.Customer.CustomerID == customerId
                       select o;

     // Create an DataServiceCollection<t> based on
     // execution of the LINQ query for Orders.
     DataServiceCollection&lorder> customerOrders = new
         DataServiceCollection&lorder>(ordersQuery);

     // Make the DataServiceCollection<> the binding source for the Grid.
     this.orderItemsGrid.DataContext = customerOrders;
 }
 catch (Exception ex)
 {
     MessageBox.Show(ex.ToString());
 }
}

private void buttonSaveChanges_Click(object sender, RoutedEventArgs e)
{
 try
 {
     // Save changes made to objects tracked by the context.
     context.SaveChanges();
 }
 catch (DataServiceRequestException ex)
 {
     MessageBox.Show(ex.ToString());

 }
}
private void buttonClose_Click(object sender, RoutedEventArgs e)
{
 this.Close();
}

Now we can build and run the application.

As you can see, exposing and consuming a model data with WCF data services is a straightforward process, but that's not the issue, the more important thing that our WPF client can be easily replaced by other clients located in a totally different environment with no change of the service itself.

The runnable code for this tutorial could be downloaded here.

This is it,

Mark..

Custom Binding with INotifyPropertyChanged Interface

2010-12-07T00:02:00.021+02:00

Introduction

In this post I would like to show a simple example of using INotifyPropertyChanged interface for binding a custom object properties to WinForm controls.

According to Microsoft's documentation INotifyPropertyChanged interface is used to notify clients about properties value changes.

The underneath idea is very simple: implementing this interface, forces raising PropertyChange event, which in-turn notifies client that binded property has changed.

The Example

Let's start with building a simple BankAccount class :


public class BankAccount : INotifyPropertyChanged
    {
        public event PropertyChangedEventHandler PropertyChanged;

        private decimal m_Balance;
        private decimal m_CreditLimit;

        public BankAccount(decimal initialBalance)
        {
            this.m_Balance = initialBalance;
            this.m_CreditLimit = initialBalance * 2;
        }

        public decimal Balance
        {
            get { return m_Balance; }
        }

        public decimal CreditLimit
        {
            get { return m_CreditLimit; }
        }

        public void Withdrawal(decimal sum)
        {
            if (sum <= m_Balance) 
            { 
                m_Balance -= sum; 
                if (PropertyChanged != null) 
                { 
                    PropertyChanged(this, 
                       new PropertyChangedEventArgs("Balance")); 
                } 
            }
        }

        public void Deposit(decimal sum)
        {
            if (sum > 0)
            {
                m_Balance += sum;
                if (PropertyChanged != null)
                {
                    PropertyChanged(this, 
                       new PropertyChangedEventArgs("Balance"));
                }

                if(m_Balance >= m_CreditLimit)
                {
                    m_CreditLimit = m_Balance * 2;
                    if (PropertyChanged != null)
                    {
                        PropertyChanged(this, 
                          new PropertyChangedEventArgs("CreditLimit"));
                    }
                }
            }
        }

    }

The class contains two properties "Balance" and "CreditLimit" which are going to be binded to our simple UI elements:

Here is the code demonstrating how we actually bind it:


public partial class MainForm : Form
{
    private BankAccount m_Account;

    public MainForm()
    {
       InitializeComponent();
       m_Account = new BankAccount(1000);

       BindControls();
    }

    private void BindControls()
    {
        txtBalance.DataBindings.Add("Text",
                               m_Account,
                               "Balance",
                               true,
                               DataSourceUpdateMode.OnPropertyChanged);

       txtCreditLimit.DataBindings.Add("Text",
                                  m_Account,
                                  "CreditLimit",
                                  true,
                                  DataSourceUpdateMode.OnPropertyChanged);
    } 

    private void btnWithdrawal_Click(object sender, EventArgs e)
    {
        m_Account.Withdrawal(decimal.Parse(txtSum.Text));
    }

    private void btnDeposit_Click(object sender, EventArgs e)
    {
        m_Account.Deposit(decimal.Parse(txtSum.Text));
    }
 }

Now, the binded textboxes will automatically reflect changes of "Balance" and "CreditLimit" properties after calling Withdrawal/Deposit methods:

The working example could be downloaded here.

This is it,

Mark

Parallel Loops with .NET Framework 4

2010-12-04T22:06:00.042+02:00

Introduction

Parallel computing draws a lot of attention at these days, because of amazing pace in which multi-core computers become widely available for industrial and personal use.

Developing applications that can take advantage of this computitional power, requires revision of the existing practices and design patterns, since those practices were born in the sequential world and do not solve similar

We all know that writting paraller applications is difficult and painful mission, not only because of well-known issues such as deadlocks and race-conditions, but simply because human beings have been always struggling to think in a parallel way.

That's why, existing of well-designed patterns for parallel programming is so crucial.

As a C# developer, I've started looking for the parallel programming resources by using .NET framework 4 , and found an excellent paper by Stephen Toub "Patterns of Parallel Programming".

The paper contains in-depth tour in .NET framework 4 for parallel programming.

In this post I would like to show one of the examples from the mentioned above paper :"Parallel Loops".

Parallel Loops

As you can guess, parallel loops allow running independent actions in parallel.

Loops are the most common control stuctures that enable the application to repeatedly execute some set of instructions.

We can use such loop when the statements within loop body have a few or no dependencies.

Creating Manual Parallel For

Let's start with the example of performing parallel "For" manually:



public static void MyParallelFor(int inclusiveLowerBound, int exclusiveUpperBound, Action body)
{
// Determine the number of iterations to be processed, the number of
// cores to use, and the approximate number of iterations to process
// in each thread.
int size = exclusiveUpperBound - inclusiveLowerBound;
int numProcs = Environment.ProcessorCount;
int range = size / numProcs;
// Use a thread for each partition. Create them all,
// start them all, wait on them all.
var threads = new List(numProcs);
for (int p = 0; p <>
{
int start = p * range + inclusiveLowerBound;
int end = (p == numProcs - 1) ?
exclusiveUpperBound : start + range;
threads.Add(new Thread(() =>
{
for (int i = start; i <>
 body(i);
}));
}
foreach (var thread in threads) thread.Start();
foreach (var thread in threads) thread.Join();
}

The main drawback of this solution is relative high cost paid for creating and destroying each thread.
We can improve it by utilizing pools of threads:



public static void MyParallelFor(
int inclusiveLowerBound, int exclusiveUpperBound, Action body)
{
// Determine the number of iterations to be processed, the number of
// cores to use, and the approximate number of iterations to process in
// each thread.
int size = exclusiveUpperBound - inclusiveLowerBound;
int numProcs = Environment.ProcessorCount;
int range = size / numProcs;
// Keep track of the number of threads remaining to complete.
int remaining = numProcs;
using (ManualResetEvent mre = new ManualResetEvent(false))
{
// Create each of the threads.
for (int p = 0; p <>
{
int start = p * range + inclusiveLowerBound;
int end = (p == numProcs - 1) ?
exclusiveUpperBound : start + range;
ThreadPool.QueueUserWorkItem(delegate
{
 for (int i = start; i <>
  body(i);
 if (Interlocked.Decrement(ref remaining) == 0)
  mre.Set();
});
}
// Wait for all threads to complete.
mre.WaitOne();
}
}

PARALLEL.FOR

The new "Parallel" class has been added to the .NET Framework 4 library. The class provides methods for performing parallel loops and regions, one of them is "For". In his paper Stephen gives a very good example of using Parallel.For in order to trace rays of light. Following code snippets demonstrate the sequential and parallel variations of this problem:



void RenderSequential(Scene scene, Int32[] rgb)
{
Camera camera = scene.Camera;
for (int y = 0; y <>
{
int stride = y * screenWidth;
for (int x = 0; x <>
{
Color color = TraceRay(
new Ray(camera.Pos, GetPoint(x, y, camera)), scene, 0);
rgb[x + stride] = color.ToInt32();
}
}
}

void RenderParallel(Scene scene, Int32[] rgb)
{
Camera camera = scene.Camera;
Parallel.For(0, screenHeight, y =>
{
int stride = y * screenWidth;
for (int x = 0; x <>
{
Color color = TraceRay(
new Ray(camera.Pos, GetPoint(x, y, camera)), scene, 0);
rgb[x + stride] = color.ToInt32();
}
});
}

We can notice that only difference between the implementations is replacing of regular for by

Parallel.For

That's it for now.

I'll continue extracting and adding such small articles to give a "gustations" of the wonderful stuff from Stephen's book.

Mark.

Dependency Injection

2010-11-15T16:53:00.009+02:00

Introduction

Dependency Injection (DI) is a design pattern which allows us to "inject" the concrete object into a class instead of having this initialization within the class itself.

One common way to achive similar result is using "Factory Method" design pattern.

"Factory Method" is a method, responsible for creating and returning of an instance of a class.

Usually a variable is passed to the "Factory" method to signalize which specific subclass should be returned.

The major drawbacks of the "Factory Method" are:

1. The method implementation is too specific and therefore cannot be used across other applications.

2. The creation options are hardcoded into "Factory" implementation, which means that all dependencies are known at compile time and cannot be dynamically extented without re-compiling.

3. The class which calls "Factory" method should know which subclass to create (sounds like dependency itself).

Just to make it clear: I'm not saying that using factories is bad. There are many applications in which using factories is valuable and sufficient, but if you need more flexible solution, that's when DI enters the picture.

Dependency Injection

DI is implemented by using so-called containers - configurable components that host the abstraction, create the concrete instance variables and inject it into appropriate classes.

There are many DI providers available on the market, here is the partial list:

1. Castle Windsor

2. Spring.NET

3. Unity

In the following example we will use the Unity 2.0 components which is part of Enterprise Library 5.0.

The Example

Lets start with creating of a simple interface IDatabase containing a single method "Save"

 interface IDataBase
 {
    void Save();
 }

Now, we'll add two classes that implement IDatabase:


 class OracleDatabase : IDataBase
    {
   public OracleDatabase()
   {
       Console.WriteLine("OracleDatabase Constructor");
   }

   public void Save()
   {
       Console.WriteLine("Save with Oracle");
   }
}

And


 public class SqlDatabase :  IDataBase
    {
     public SqlDatabase()
     {
         Console.WriteLine("SqlDatabase Constructor");
     }

     public void Save()
     {
         Console.WriteLine("Save with SQL Server");
     }
}

The "Customer" class will be the main class which uses the services of IDatabase component:

 class Customer
  {
      private IDataBase m_Database = null;
 
     [Dependency]
     public IDataBase Database
     {
        get { return m_Database; }
        set { m_Database = value; }
     }

    public void Save()
    {
       m_Database.Save();
   }
}

The "Dependency" attribute is part of the Unity application block and it's simply a gateway used by the Unity container for the concrete type injection.

The mapping between the abstract variable and the concrete type may be performed through the code or by adding a special configuration section to App.config file.

The final usage is quite simple:

IUnityContainer container = new UnityContainer();

UnityConfigurationSection configSection = (UnityConfigurationSection)ConfigurationManager.GetSection("unity");

configSection.Configure(container);

Customer customer = container.Resolve();

customer.Save();

The complete example could be downloaded here

That's it,

Mark.

Propagator Design Pattern

2010-10-18T12:41:00.014+02:00

Introduction

I've encountered this design pattern while I was looking for an existing solution to the problem of propagating an object property to the dependant objects.
Actually the "Propagator" is based on well-known "Observer" design pattern
in which an object known as the Subject holds a list of Observers and notifies them automatically of any state changes.

The difference is that "Propagator" makes it possible to construct each object to be "Subject" and "Observer" interchangeably.

The Propagator

The idea behind the pattern is to establish a network of dependent objects and when a change occurs, push it through propagators to all descendants.

Let's give a brief explanation of each actor in the diagram above:

IPropagator interface contains methods for adding/removing dependent objects and processing state changes.
Propagator class, implements the IPropagator interface and contains
the "AddHandler" method used for managing a list of delegates to be invoked when a change takes place.
StateChange class represents the change itself.
StateChangeOptions enum allows specifying a change propagation options.

The final usage is quite simple - we just need to call "Process" method of corresponding propagator to have all objects synchronized with the recent change.

The detailed description of Propagator design pattern along with a nice code example could be found in this post by Martijn Boeker.

That's it,

Mark.

How to Compare Two Spreadsheets in Excel

2010-10-08T17:02:00.006+02:00

This time a contribution from Yoav Ezer the CEO of Cogniview.

Enjoy,
Diego

Being Microsoft Excel experts, the Cogniview team are often being emailed with tricky spreadsheet questions. A particularly interesting puzzle came through recently that we thought we would share with you here:

"Each month I have to compare two spreadsheets of products, each with the same header rows and same number of column. The number of rows are different each time.

Is it possible using Excel to compare these two lists and show only the differences? I would love it if we could create a macro to solve this??"

Seems like a cool challenge, and I would love to say I had a hand in coming up with the solution but I am afraid it was the programming boffins at Cogniview who solved it!

The Solution

Several ideas were suggested and considered, including going through each sheet line by line using VB code. The problem always came when either or both sheets were really long and contained tons of data. Even on the fastest machine it could be seen to grind to a halt.

The answer was to use Excel's built-in function RemoveDuplicates to handle this. Unfortunately that makes it a solution not available to users of older versions.

We need two source spreadsheets to compare, then a target spreadsheet for the results, as shown below.

Basically, this process requires our macro to perform two steps.

Step one, we copy the first sheet's data to a new sheet and put the second's data after it, then we use the RemoveDuplicates function which removes the items from the second sheet that appear in the first.

In the second step we do the same again in reverse order - copy the second sheet's data and then the first, and use RemoveDuplicates again. The left over data in each case is the difference we need to display.

All we need to do then is present the results.

For each row we state where the data was found.

The Macro

Most of the first routine sets everything up, copying the range of data as described above. The last two lines call our custom function LeaveOnlyDifferent.

Sub CompareSheets()
' Merge into the current sheet
Dim sheetResult As Worksheet, Sheet1 As Worksheet, Sheet2 As Worksheet
Set sheetResult = ActiveSheet

' Clean merge sheet so we can work with it
If (MsgBox("This will erase all the data on the current sheet." & vbCrLf & "Do you wish to continue?", vbYesNo Or vbQuestion) <> vbYes) Then
Exit Sub
End If
sheetResult.UsedRange.Delete

' Ask for two sheets to compare
SelectSheet.SheetNames.Clear
For i = 1 To Worksheets.Count
SelectSheet.SheetNames.AddItem Worksheets(i).Name
Next
sFirstSheet = AskSheet
If (sFirstSheet = "") Then
Exit Sub
End If
Set Sheet1 = Sheets(sFirstSheet)
SelectSheetStart:
sSecondSheet = AskSheet
If (sSecondSheet = "") Then
Exit Sub
End If
If (sSecondSheet = sFirstSheet) Then
MsgBox "Please select different first and second sheets"
GoTo SelectSheetStart
End If
Set Sheet2 = Sheets(sSecondSheet)

' Find the column to use for marking
Dim sFromColumn As String
Dim nLastColumn As Integer
sTemp = Sheet1.UsedRange.Offset(1, 1).Address(True, True, 1)
sTemp = Mid(sTemp, InStr(sTemp, ":") + 1)
sFromColumn = Mid(sTemp, 2, InStrRev(sTemp, "$") - 2)
nLastColumn = Sheet1.UsedRange.Columns.Count

' Copy header
Sheet1.Range("A1:" & sFromColumn & "1").Copy sheetResult.Range("A1")
sheetResult.Range(sFromColumn & "1").Formula = "From Sheet"

' Compare stuff
LeaveOnlyDifferent Sheet2, Sheet1, sheetResult, sFromColumn, nLastColumn, 2
LeaveOnlyDifferent Sheet1, Sheet2, sheetResult, sFromColumn, nLastColumn, sheetResult.UsedRange.Rows.Count
End Sub

LeaveOnlyDifferent Function

This function is where the real solution lies, comparing and presenting the result. It accepts the sheets to compare and the destination, the columns and the first row. After copying the cells and removing duplicates it copies the result to the top of the destination sheet.

Function LeaveOnlyDifferent(Sheet1 As Worksheet, Sheet2 As Worksheet, sheetResult As Worksheet, sFromColumn As String, nLastColumn As Integer, nFirstCompareRow As Integer)
' Copy first sheet data
nFirstDataRowCount = Sheet1.UsedRange.Rows.Count - 1
Sheet1.Range("A2:" & sFromColumn & (nFirstDataRowCount + 1)).Copy sheetResult.Range("A" & nFirstCompareRow)

' Copy second sheet data below the first
nStartOfSecondData = nFirstCompareRow + nFirstDataRowCount
Sheet2.Range("A2:" & sFromColumn & Sheet2.UsedRange.Rows.Count).Copy sheetResult.Range("A" & nStartOfSecondData)

' Remove duplicates
Dim arColumns() As Variant
ReDim arColumns(0 To nLastColumn - 1)
For i = 0 To nLastColumn - 1
arColumns(i) = CVar(i + 1)
Next
sheetResult.Range("A" & nFirstCompareRow & ":" & sFromColumn & sheetResult.UsedRange.Rows.Count).RemoveDuplicates arColumns, xlYes

' Mark the different data as coming from the proper sheet
nDiffRowCount = sheetResult.UsedRange.Rows.Count - nStartOfSecondData + 1
If (nDiffRowCount > 0) Then
sheetResult.Range(sFromColumn & nStartOfSecondData & ":" & sFromColumn & sheetResult.UsedRange.Rows.Count).Formula = "Found only on " & Sheet2.Name
' Copy it to the top
sheetResult.Range("A" & nStartOfSecondData & ":" & sFromColumn & sheetResult.UsedRange.Rows.Count).Copy sheetResult.Range("A" & nFirstCompareRow)
End If

' Delete all the rest
sheetResult.Range("A" & (nFirstCompareRow + nDiffRowCount) & ":" & sFromColumn & sheetResult.UsedRange.Rows.Count).Delete
End Function

Over to You

As mentioned earlier, there are many ways to solve this particular Excel challenge - how would you solve it? Could you see this solution as being useful? Please share your thoughts and experiences with us on Facebook or Twitter.

About the author

This article was written by Yoav Ezer, the CEO of a company that creates PDF to XLS conversion software, called Cogniview.

Prior to that, cwas the CEO of Nocturnus, a technology-centered software solution company.

Displaying images from a database - MVC style

2010-09-20T23:46:00.006+02:00

Exploring MVC 2 I started a small project that included displaying images from my database, I know this is quite the basics, but I was impressed of the simplicity & elegant style this simple task gets when using MVC.

In the database I have a table that includes images contained in varbinary(max) column.

CREATE TABLE [dbo].[Album](
[AlbumId] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
[Picture] [varbinary](max) NULL,
...
...
CONSTRAINT [PK_Album] PRIMARY KEY CLUSTERED 
(
[AlbumId] ASC
)

Next in my data access layer I've added a linq query to retrieve an image by albumId.

public byte[] GetAlbumCover(int albumId)
{
var q = from album in _db.Album
where album.AlbumId == albumId
select album.Picture;

byte[] cover = q.First();

return cover;
}

As for the actual MVC...

The controller:

public ActionResult RetrieveImage(int id)
{
byte[] cover = _BL.GetAlbumCover(id);

if (cover != null)
return File(cover, "image/jpg");

return null;
}

The view:

<% foreach (var item in Model) { %>

<tr>
<td>
<%: item.ArtistName %>
</td>
<td>
<%: item.AlbumName %>
</td>
<td>
<img src="/MusicInfo/RetrieveImage/<%:item.AlbumId%>"  alt="" height=100 width=100/>
</td>
<td>
<%: Html.ActionLink("Edit", "Edit", new {id=item.AlbumId}) %>
</td>
</tr>

<% } %>

The link of the picture points to the controller (MusicInfo), the method inside the controller (RetrieveImage) & the specific image id to be able to retrieve it.

That's it...simple yet elegant.

Till next time
Diego

Import/Export DataSet to Excel file with ADO.NET

2010-09-14T15:45:00.010+02:00

Often it is necessary to Import/Export DataSet to external databases or external file. Here I will present how Import/Export DataSet to excel file (*.xls or *.xlsx) with ADO.NET.

The most significant advantage of this method is that excel installation is not necessary on running station.

You will first need a connection string to connect to the Excel Workbook, which would be the following:

Excel 8 (Excel 1997):

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\MyExcel.xls;Extended Properties="Excel 8.0;HDR=YES";

Excel 12 (Excel 2007):

Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\MyExcel.xlsx;Extended Properties="Excel 12.0;HDR=YES";

Implementation of Import:

public static DataSet ImportFromExcel(string connectionString, string fileName)
{
DataSet returnDS = new DataSet();

string excelConnectionString = string.Format(connectionString, fileName);
string[] excelSheets = getExcelSheetNames(excelConnectionString);

DbProviderFactory factory = DbProviderFactories.GetFactory("System.Data.OleDb");
DbDataAdapter adapter = factory.CreateDataAdapter();

using (DbConnection connection = factory.CreateConnection())
{
connection.ConnectionString = excelConnectionString;

foreach (string excelSheet in excelSheets)
{
DbCommand selectCommand = factory.CreateCommand();
selectCommand.CommandText = string.Format("SELECT * FROM [{0}]", excelSheet);

selectCommand.Connection = connection;
adapter.SelectCommand = selectCommand;

DataTable dt = new DataTable(excelSheet.Remove(excelSheet.Length - 1, 1));
adapter.Fill(dt);
returnDS.Tables.Add(dt);
}
}
return returnDS;
}

Implementation of Export:

public static void ExportToExcel(DataSet dataSet, string connectionString, string fileName)
{
if (dataSet != null && dataSet.Tables.Count > 0)
{
using (OleDbConnection connection = new OleDbConnection(string.Format(connectionString, fileName)))
{
OleDbCommand command = null;
connection.Open();

foreach (DataTable dt in dataSet.Tables)
{
command = new OleDbCommand(getCreateTableCommand(dt), connection);
command.ExecuteNonQuery();

for (int rowIndex = 0; rowIndex <>
{
command = new OleDbCommand(getInsertCommand(dt, rowIndex), connection);
command.ExecuteNonQuery();
}
}
}
}
}

How to use:

Lets define 2 variables:

string connectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=""Excel 8.0;HDR=YES""";
string fileName = @"C:\MyExcel.xls";

Import:

DataSet ds = ImportFromExcel(connectionString, fileName);

Export:

ExportToExcel(ds, connectionString, fileName);

Source Code: ExcelReaderWriter.cs

SQL Server performance - querying by date

2010-09-06T23:00:00.012+03:00

In every application that includes a database we will probably need to query some table by some date column -

SELECT Id, MyDate
FROM #CompareDates
WHERE MyDate = GETDATE()

Although this is a very simple syntax, I will show you how it can be a performance hit.

We'll start by filling a table with some random dates(and one row of a specific date just to make sure it exists) and add a clustered index on this column.

The data we are looking for is all the rows with today's date.

DECLARE @myNow DATETIME
SET @myNow = '2010-09-06 22:47'

SELECT *
FROM #CompareDates
WHERE MyDate = @myNow

This query will work very fast, but, because the column 'MyDate' contains time and not just date - the result won't be as expected, only rows with the exact date & time will appear.

We can see in the execution plan that it uses 'clustered index seek' and results with one row which is the row we added manually with exact time.

We can try using DATEDIFF to eliminate the time in the column:

SELECT *
FROM #CompareDates
WHERE DATEDIFF(day,MyDate,@myNow)=0

This will get the exact result, but, using a function over the 'MyDate' column causes the sql server to scan the entire index!!

If the column does not contain time, we can eliminate the time in the '@myNow' parameter:

DECLARE @myNowNoTime DATETIME

SET @myNowNoTime = dateadd(dd,datediff(dd,0,GETDATE()),0)

SELECT *
FROM #CompareDates
WHERE MyDate = @myNowNoTime

This will also perform well as the 1st query, but again...what if 'myDate' column contains time??

The solution is to avoid doing functions (like DATEDIFF, DATEADD or any user defined function) over 'MyDate' column.

The solution is very simple - date range.
First I've created two functions that one returns the begining of the day (time will be '00:00:00') and the other the end of the day (time will be 23:59:59:997)

CREATE FUNCTION [dbo].[fnGetBeginOfDay]
(@myDate DATETIME)
RETURNS DATETIME
BEGIN
RETURN CAST(FLOOR(CAST(@myDate AS DECIMAL(12, 5))) AS DATETIME)
END
GO

CREATE FUNCTION [dbo].[fnGetEndOfDay]
(@myDate DATETIME )
RETURNS DATETIME
BEGIN
RETURN DATEADD(ms, -3, DATEADD(day, 1, (dbo.fnGetBeginOfDay(@myDate))))
END
GO

Than the query will look like this:

DECLARE @beginOfDay DATETIME, @endOfDay DATETIME
SET @beginOfDay = dbo.fnGetBeginOfDay(GETDATE())
SET @endOfDay = dbo.fnGetEndOfDay(GETDATE())

SELECT *
FROM #CompareDates with (index(idx_MyDate))
WHERE MyDate BETWEEN @beginOfDay AND @endOfDay

And the execution plan looks like this, back to the wonderfull 'index seek' and returns the expected amount of rows:

Additional facts:
1. Testing the same queries with NONCLUSTERED index shows that the date range solution is as bad as DATEDIFF - both result with table scan

2. Adding to (1) a table hint (with(index(idx_MyDate))) to both queries shows that while datediff uses 'index scan', date range uses 'index seek' (which is obviously better).

Conclusions:

1. When querying using a date column which is included in an index - use 'date range' solution.

2. If the index is nonclustered - check if a table hint makes the difference.

Till next time....
Diego

Full sample test source code:

--fill table with random dates
CREATE TABLE #CompareDates (Id int identity(1,1), MyDate DATETIME)

DECLARE @myNow DATETIME
SET @myNow = '2010-09-06 22:47'
DECLARE @i int, @myDate DATETIME
SET @i = 0

WHILE @i < 10000
BEGIN
--random date (10x to Ben Nadel for the useful & short function :-)
SET @myDate = CAST(CAST( @myNow AS INT ) -5 * RAND( CAST( CAST( NEWID() AS BINARY(8) ) AS INT ) )AS DATETIME)

INSERT INTO #CompareDates (MyDate)
SELECT @myDate
SET @i=@i+1

IF @i=500
INSERT INTO #CompareDates (MyDate) VALUES(@myNow)
END

CREATE CLUSTERED INDEX idx_MyDate ON #CompareDates(MyDate)

SELECT * FROM #CompareDates

-----TESTING:

--fast, looks for specific date including time, not quite we are looking for..
SELECT *
FROM #CompareDates
WHERE MyDate = @myNow

--exact results, scans entire index
SELECT *
FROM #CompareDates
WHERE DATEDIFF(day,MyDate,@myNow)=0

--if the column does not contain time, you can eliminate time in the parameter
DECLARE @myNowNoTime DATETIME
SET @myNowNoTime = dateadd(dd,datediff(dd,0,@myNow),0)

SELECT *
FROM #CompareDates
WHERE MyDate = @myNowNoTime

--if column contains time, use range
DECLARE @beginOfDay DATETIME, @endOfDay DATETIME
SET @beginOfDay = dbo.fnGetBeginOfDay(@myNow)
SET @endOfDay = dbo.fnGetEndOfDay(@myNow)

SELECT *
FROM #CompareDates with (index(idx_MyDate))
WHERE MyDate BETWEEN @beginOfDay AND @endOfDay

Scanning images using WIA and TWAIN

2010-07-29T15:52:00.010+03:00

Introduction

The development of windows imaging applications requires choosing an API to communicate with scanner s or cameras. The most used APIs are WIA and TWAIN.

The WIA (Windows Image Acquisition) platform enables imaging/graphics applications to interact with imaging hardware and standardizes the interaction between different applications and scanners. This allows those different applications to talk to and interact with those different scanners without requiring the application writers and scanner manufactures to customize their application or drivers for each application-device combination. (http://msdn.microsoft.com/en-us/library/ms630368(VS.85).aspx)

TWAIN is a standard software protocol and applications programming interface (API) that regulates communication between software applications and imaging devices such as scanners and digital cameras. (http://www.twain.org/)

Differences:

1. WIA uses a common dialog for all devices while TWAIN uses a dialog created by the device manufacturer. Practically speaking, this almost always means that the TWAIN dialog will provide more options and advanced control over the device.

2. TWAIN allows you to use custom capabilities that the device manufacturer has created even though they don't exist in the TWAIN specifications.

3. In general, when a device supports both Twain and WIA, TWAIN is better for scanners and WIA is better for acquiring images from cameras and video devices.

4. TWAIN has three transfer modes (Native, Memory, File) and WIA only has two (Memory, File).

5. Most TWAIN sources save the settings of the previous scan while WIA does not.

6. TWAIN supports options for each page when scanning in duplex mode but WIA uses the same settings for both sides.

Problem

Usually, older scanners (drivers) are build for TWAIN and does not supported by WIA platform, moreover newer devices which created under Microsoft standard does not supported by TWAIN API.

Solution (Full Source Download)

In order to build scanning application that will communicate with both types of devices I combined those APIs under a uniform interface which will provide the easy access to both type of devices.

ScannerAdapterBase

public abstract class ScannerAdapterBase
{
protected List<Image> m_ImagesList = null;
protected ScannerDeviceData m_ScannerDeviceData = null;

// Initializes the Adapter
public abstract void InitAdapter(nessScanning control, IMessageFilter messageFilter, IntPtr handle);

// Selects scanning device, and returns the indicator that the device selected
// Returns the indicator that the device selected
public abstract bool SelectDevice();

// Acquires images from scanning device and fills ImagesLis
public abstract void AcquireImages(bool showUI);

…
}

Factory Method

public static ScannerAdapterBase GetScannerAdapter(nessScanning control, IMessageFilter messageFilter, IntPtr handle)
{
lock (locker)
{
bool isTwainDeviceSelected = false;

if (m_ScannerAdapterBase != null)
{
return m_ScannerAdapterBase;
}

try
{
//Checks WIA Devices
m_ScannerAdapterBase = new WiaScannerAdapter();
m_ScannerAdapterBase.InitAdapter(control, messageFilter, handle);
isWiaDeviceSelected = m_ScannerAdapterBase.SelectDevice();
if (isWiaDeviceSelected)
{
return m_ScannerAdapterBase;
}

//Checks TWAIN Devices
m_ScannerAdapterBase = new TwainScannerAdapter();
m_ScannerAdapterBase.InitAdapter(control, messageFilter, handle);
isTwainDeviceSelected = m_ScannerAdapterBase.SelectDevice();
if (isTwainDeviceSelected)
{
return m_ScannerAdapterBase;
}
}
catch (ScannerException ex)
{
throw ex;
}

return null;
}
}

Wia Adapter AcquireImages method

public override void AcquireImages(bool showUI)
{
CommonDialogClass wiaCommonDialog = new CommonDialogClass();

//Select Device
if (m_DeviceID == null)
{
Device device = null;
try
{
device = wiaCommonDialog.ShowSelectDevice(WiaDeviceType.ScannerDeviceType, false, false);
if (device != null)
{
m_DeviceID = device.DeviceID;
FillDeviceData(device);
}
else
{
return;
}
}
catch (COMException ex)
{
if ((WiaScannerError)ex.ErrorCode == WiaScannerError.ScannerNotAvailable)
{
return;
}
else
{
WiaScannerException se = BuildScannerException(device, ex);
throw se;
}
}
}

//Create DeviceManager
DeviceManager manager = new DeviceManagerClass();
Device WiaDev = null;
foreach (DeviceInfo info in manager.DeviceInfos)
{
if (info.DeviceID == m_DeviceID)
{
WIA.Properties infoprop = null;
infoprop = info.Properties;

//connect to scanner
WiaDev = info.Connect();

break;
}
}

//Get Scanning Properties
WIA.Item scanningItem = null;
WIA.Items items = null;
if (showUI)
{
items = wiaCommonDialog.ShowSelectItems(WiaDev, WiaImageIntent.TextIntent, WiaImageBias.MinimizeSize, false, true, false);
}
else
{
items = WiaDev.Items;
}

if (items != null && items.Count > 0)
{
scanningItem = items[1] as WIA.Item;
}

WIA.ImageFile imgFile = null;
WIA.Item item = null;

//Prepare ImagesList
if (m_ImagesList == null)
{
m_ImagesList = new List<Image>;
}

//Start Scan
while (HasMorePages(WiaDev))
{
item = scanningItem;

try
{
//Scan Image
imgFile = (ImageFile)wiaCommonDialog.ShowTransfer(item, ImageFormat.Jpeg.Guid.ToString("B")/* wiaFormatJPEG*/, false);
byte[] buffer = (byte[])imgFile.FileData.get_BinaryData();
MemoryStream ms = new MemoryStream(buffer);
m_ImagesList.Add(Image.FromStream(ms));
imgFile = null;
}
catch (COMException ex)
{
if ((WiaScannerError)ex.ErrorCode == WiaScannerError.PaperEmpty)
{
break;
}
else
{
WiaScannerException se = BuildScannerException(WiaDev, ex);
throw se;
}
}
catch (Exception ex)
{
WiaScannerException se = BuildScannerException(WiaDev, ex);
throw se;
}
finally
{
item = null;
}
}
}

Wia Adapter HasMorePages method

private bool HasMorePages(Device WiaDev)
{
try
{
bool hasMorePages = false;
//determine if there are any more pages waiting
Property documentHandlingSelect = null;
Property documentHandlingStatus = null;
foreach (Property prop in WiaDev.Properties)
{
if (prop.PropertyID == WIA_PROPERTIES.WIA_DPS_DOCUMENT_HANDLING_SELECT)
documentHandlingSelect = prop;
if (prop.PropertyID == WIA_PROPERTIES.WIA_DPS_DOCUMENT_HANDLING_STATUS)
documentHandlingStatus = prop;
}

if (documentHandlingSelect != null) //may not exist on flatbed scanner but required for feeder
{
//check for document feeder
if ((Convert.ToUInt32(documentHandlingSelect.get_Value()) & WIA_DPS_DOCUMENT_HANDLING_SELECT.FEEDER) != 0)
{
hasMorePages = ((Convert.ToUInt32(documentHandlingStatus.get_Value()) & WIA_DPS_DOCUMENT_HANDLING_STATUS.FEED_READY) != 0);
}
}
return hasMorePages;
}
catch (COMException ex)
{
WiaScannerException se = BuildScannerException(WiaDev, ex);
throw se;
}
}

TWAIN Adapter AcquireImages method

public ArrayList TransferPictures()
{
ArrayList pics = new ArrayList();
if (srcds.Id == IntPtr.Zero)
{
return pics;
}

TwRC rc;
IntPtr hbitmap = IntPtr.Zero;
TwPendingXfers pxfr = new TwPendingXfers();

do
{
pxfr.Count = 0;
hbitmap = IntPtr.Zero;

TwImageInfo iinf = new TwImageInfo();
rc = DSiinf(appid, srcds, TwDG.Image, TwDAT.ImageInfo, TwMSG.Get, iinf);
if (rc != TwRC.Success)
{
CloseSrc();
return pics;
}

rc = DSixfer(appid, srcds, TwDG.Image, TwDAT.ImageNativeXfer, TwMSG.Get, ref hbitmap);
if (rc != TwRC.XferDone)
{
CloseSrc();
return pics;
}

rc = DSpxfer(appid, srcds, TwDG.Control, TwDAT.PendingXfers, TwMSG.EndXfer, pxfr);
if (rc != TwRC.Success)
{
CloseSrc();
return pics;
}

pics.Add(hbitmap);
}
while (pxfr.Count != 0);

rc = DSpxfer(appid, srcds, TwDG.Control, TwDAT.PendingXfers, TwMSG.Reset, pxfr);
return pics;
}

Compare two object's values

2010-06-28T16:22:00.008+03:00

Recently we encounter an event that receives two objects and we wanted to compare their values (before and after of a column 'changing' event on a datatable). Using '==' doesn't do the work because the implementation of '==' in object compares the actual reference and not the values.

This simple method does the work.

static bool CompareObjectsValues(object a, object b)
{
if (a.GetType()!=b.GetType() 
|| !(a is IComparable) || !(b is IComparable))
return false;

IComparable valA = a as IComparable;
IComparable valB = b as IComparable;

return (valA.CompareTo(valB) == 0);

}

Full sample to show the problem and test:


static bool Compare(object a, object b)
{
return (a == b);
}

static void Main(string[] args)
{
object a = new object();
object b = new object();

DateTime d1 = new DateTime(2010, 6, 27);
DateTime d2 = new DateTime(2010, 6, 27);

a = d1;
b = d2;

object c = a;


//returns 'true' -> same reference
Console.WriteLine(Compare(a , c)); 
//returns 'false'-> same value but not same reference -> problem
Console.WriteLine(Compare(a , b)); 
//returns 'true' -> compares values and not references
Console.WriteLine(CompareObjectsValues(a, b));

}

That's it...
Diego

Working with LayoutEngine

2010-06-17T18:32:00.004+03:00

When you developing your custom controls sometime it requires a certain behavior or graphical representation of it child controls .

In this case you Control have a property called LayoutEngine

Example below shows you how to implement your LayoutEngine

public class DemoPanel : Panel

{

private bool m_CustomLayout = false;

private DemoPanelLayout layoutEngine = new DemoPanelLayout();

public DemoPanel() { }

public bool CustomLayout

{

get { return m_CustomLayout; }

set { m_CustomLayout = value; }

}

public override LayoutEngine LayoutEngine

{

get

{

return layoutEngine;

}

Now for the custom LayoutEngine implementation

public class DemoPanelLayout : LayoutEngine

{

public override bool Layout(

object container,

LayoutEventArgs layoutEventArgs)

{

Control parent = container as Control;

if (((parent as DemoPanel).CustomLayout))

{

Rectangle parentDisplayRectangle = parent.DisplayRectangle;

Point nextControlLocation = parentDisplayRectangle.Location;

foreach (Control c in parent.Controls)

{

if (!c.Visible)

{

continue;

}

if (nextControlLocation == parentDisplayRectangle.Location)

{

nextControlLocation.Y += c.Margin.Top;

nextControlLocation.X += c.Margin.Left;

}

c.Location = nextControlLocation;

if (!((c.Location.X + c.Width + c.Margin.Left)> (parent.Width - c.Width-c.Margin.Left)))

{

nextControlLocation.Offset(c.Width+c.Margin.Left, 0 );

}

else

{

nextControlLocation.X = parent.Left+c.Margin.Left;

nextControlLocation.Offset(0,c.Height+c.Margin.Top);

}

return false;

}

In example above DemoPanel's LayoutEngine will check the CustomLayout property
and order child controls as tabled view .

Paging in SQL 2005

2010-06-15T11:13:00.010+03:00

Suppose that you need to reduce the load of traffic between the Microsoft SQL 2005 DB
and application in case of client \ server application also reduce the load of traffic between
client and application server. One of the known methods is Paging .
In a simple example below, I'll show you how to implement Paging method at SQL 2005
server side.
Create and fill temporary table #someTable for example :

   create table #someTable(RecordKey int,FieldA int, FieldB nvarchar(50))
   insert into #someTable(RecordKey,FieldA , FieldB)values(1,5,'a')
   insert into #someTable(RecordKey,FieldA , FieldB)values(2,4,'ab')
   insert into #someTable(RecordKey,FieldA , FieldB)values(3,3,'abc')
   insert into #someTable(RecordKey,FieldA , FieldB)values(4,2,'abcd')

We'll use page size=2 ,FieldA as sort field and page number = 1

     DECLARE @PageSize INT
   DECLARE @RequestPageNumber INT

   set @RequestPageNumber = 1
   set @PageSize = 2

   DECLARE  @FromKey INT
   DECLARE @ToKey INT

   set @ToKey = (@PageSize * @RequestPageNumber)
   set @FromKey = @ToKey - @PageSize + 1;

;WITH SortedTable As
(
  SELECT
   ROW_NUMBER() OVER (ORDER BY FieldA) as RowNumber,
   RecordKey,
   FieldA,
   FieldB
  FROM #someTable
   )
   SELECT
   RecordKey,
FieldA,
FieldB
   FROM SortedTable
  WHERE RowNumber BETWEEN @FromKey AND @ToKey

The result as you can see two rows sorted by FieldA :

The example showed us the MS SQL 2005 server side implementation of paging technique
the application server/client paging techniques is another post issue ,but the common idea
identical to this one that implemented here.

Simple example of how to expose .net controls to VB6

2010-06-13T12:02:00.005+03:00

There's already a lot of discussions on iNet on this theme i'll just show you on simple
example how to expose your .Net Controls as COM to other development
environment (in our case VB6 as example).

Create your control library and make it com visible as in picture below

Because we do not have setup for now and for test we should register our library as COM
check the register for COM option to register it

Create some control and make it com visible also give it a ProgId to expose it to out world

Build the project and let test our control in VB6
Create new vb6 exe project . To add our control dynamically onto the form we will use
the VBControlExtender and code looks like that :

   Option Explicit
   Dim WithEvents objExt As VBControlExtender

   Private Sub Form_Load()
   'adding our control licens to licences collection
   Licenses.Add "InteropControlLib.DotNetControl", "ewrinvcmcoe"
     'adding the control to Form
   Set objExt = Me.Controls.Add("InteropControlLib.DotNetControl", "DotNetControl1", Me)
   objExt.Visible = True
   End Sub

Run the test project and wuala, you got .net control on vb6 form :

Those of you who found this article interesting and want to find more
check out links below :
COM Interoperability in .NET Framework Applications
COM Interop

Executing dtsx on Microsoft SQL 2005 Express with schedule

2010-06-10T13:26:00.019+03:00

Let say you need to run scheduled DTS package on Microsoft SQL 2005 Express and
as you probably know there is no SSIS and Agent Services in Microsoft SQL 2005 Express
edition but you can do this job actually very easy :

1.
   Assure that you do have the DTExec util, it should be in
   C:\Program Files\Microsoft SQL Server\90\DTS\Binn folder ,
   if you do not have one means you should install SQL Toolkit .

2.
   Next step is to make a batch file to run the DTS package.
   Create batch file with DTExec formatted command for example

setlocal
Set upPath=[Some folder path where you have your package or whatever ]
Set logFileName=DTS_Batch_Log

cd "C:\Program Files\Microsoft SQL Server\90\DTS\Binn"

     set startDate=%date%
     set startTime=%time%

     set sdy=%startDate:~10,4%
     set sdm=%startDate:~4,2%
     set sdd=%startDate:~7,2%
     set sth=%startTime:~0,2%
     set stm=%startTime:~3,2%
     set sts=%startTime:~6,2% 0

DTExec.exe /F "%upPath%\DTS\[MyDTSName].dtsx"
/MAXCONCURRENT " -1 " /CHECKPOINTING OFF
/SET "\Package.Variables[My DTS Variable name].Value";
"[Here is comes the My DTS Variable value]"
>> "%upPath%\%logFileName%_%sdy%.%sdm%.%sdd%-%sth%.%stm%.log"
IF %ERRORLEVEL% == 0 GOTO [Some label ] ELSE GOTO End
: Some label
   your code comes here
: End

   The example above shows how to run DTSX Package from File and Log
   the results of execution to some log file.

3.
   Last step is schedule and Yes there is one in Windows it called " Scheduled
   Tasks" you could find it in System Tools – just create one with call of the batch
   that you created earlier to run dtsx packages.

And that's all you need for the job to be done ;)...

SqlBroker clean up

2010-06-07T16:00:00.003+03:00

Testing SqlBroker for the 1st time outside the development environment I realized I did not define the Authorization attribute when creating the objects (messagetype, contract & services) see Asynchronous triggers using SQL Broker for more details.

This mistake filled the queue with over 300,000 messages that were not going anywhere even after I fixed the Authorization.

Good thing I found this on test environment which allowed me to run a short script that cleans up those messages without worrying about the consequences.

BTW, if you were thinking of dropping the objects and rebuild them...tried that - got a weird out of memory message from SQL Server.

Here is the script:

declare @conversation uniqueidentifier

while exists (select 1 from sys.transmission_queue )

begin

set @conversation = (select top 1 conversation_handle from sys.transmission_queue )

end conversation @conversation with cleanup

end

Till next time...
Diego