06 May

Automating the compare of 2 rows in a trigger

I just answered this question on StackOverflow about how to generically compare an inserted row to a deleted row within a trigger. I started by just commenting that it would be a good place to do a little code generation, but something about the problem wouldn’t let me put it down. Here’s what I came up with.

First I created a function that would return the query that would actually do the comparison. Notice that I’m comparing #inserted and #deleted rather than inserted and deleted.  This is because we don’t have access to the inserted and deleted tables when we’re running the comparison query in an exec()

create function GetChangedRowsQuery(
	@TableName				varchar(50), 
	@PrimaryKeyColumnName	varchar(50),
	@RowVersionColumnName	varchar(50) = ''
)
returns varchar(max)
as
begin
	
    declare 
	@ColumnName varchar(50),
	@GetChangedRowsQuery varchar(max)

    select @GetChangedRowsQuery = 
           'select isnull(a.' + @PrimaryKeyColumnName + ', b.' 
           + @PrimaryKeyColumnName + ') 
      from #inserted a
      full join #deleted b 
        on a.' + @PrimaryKeyColumnName + ' 
           = b.' + @PrimaryKeyColumnName + '
     where '

    declare ColumnCursor cursor Read_Only
    for select Name
          from Sys.columns
         where object_id = Object_Id('Member')

    open ColumnCursor
    fetch next from ColumnCursor into @ColumnName
    while @@FETCH_STATUS = 0
      begin
	if (@ColumnName != @PrimaryKeyColumnName 
            and @ColumnName != @RowVersionColumnName)
	  begin
            select @GetChangedRowsQuery = @GetChangedRowsQuery 
                + '((a.' + @ColumnName + ' != b.' + @ColumnName 
                + ' or a.' + @ColumnName + ' is null 
                    or b.' + @ColumnName + ' is null) 
                and (a.' + @ColumnName + ' is not null 
                     or b.' + @ColumnName + ' is not null))' 
                + char(13) + '      or ' 
          end
        fetch next from ColumnCursor into @ColumnName
      end
    close ColumnCursor
    deallocate ColumnCursor

    select @GetChangedRowsQuery 
           = substring(@GetChangedRowsQuery, 0, len(@GetChangedRowsQuery) -7)

    return @GetChangedRowsQuery
end

Next, I created a trigger.  It creates the #inserted and #deleted temp tables, get’s the query from the function, creates a temp table to hold the results.  Then it inserts the result into the temp table.  I’m just selecting the top 10 changed rows, but you could do whatever you needed to do with the changed rows at this point.

create trigger TestTrigger on Member for Insert, Update, Delete
as
begin
	
    select *
      into #Inserted
      from Inserted

    select *
      into #Deleted
      from Deleted

    declare @GetChangedRowsQuery varchar(max)

    select @GetChangedRowsQuery 
            = dbo.GetChangedRowsQuery('MemberTrash', 'MemberId', '')

    create table #Temp (PrimaryKey int)

    insert into #Temp (PrimaryKey)
    exec (@GetChangedRowsQuery) 

    select top 10 *
      from #Temp

    drop table #Temp
    drop table #Inserted
    drop table #Deleted
end
02 May

Unsubscribe from everything

I recently moved my email from Gmail to Outlook.com. It’s not a small deal. I needed to get all my friends and family to update their contact info for me, and I’ve had to update my contact info on countless sites. As part this move I also started unsubscribing to pretty much anything that came into my Gmail account, but not signing up to receive those emails in my Outlook.com account.

Unsubscribe All the Things

The results have been wonderful. After about 2 weeks, I now only receive 1-2 emails a day, mostly from people I want to get emails from. I don’t really know how many emails I was receiving before, but I can tell you it’s really refreshing to be receiving so few.

I’ve added a reminder to my calendar for next year to spend a week or two unsubscribing from all the junk that builds up as I try out various new services. You should do the same.

14 Mar

AngularJS URLs missing trailing slash

I ran across this problem trying to deploy an Asp.Net MVC website with an AngularJS front end.  Everything worked fine as long as the site was deployed as it’s own website within IIS, but when we deployed to an Application folder within an existing site things started going wrong.

The problem was that the URLs were not getting their trailing slashes properly.  IIS adds a trailing slash to URLs when the last segment of the address refers to a folder rather than a file.

http://somesite.com/AngularApp should have been converted to http://somesite.com/AngularApp/

Since it wasn’t getting converted I was getting http://somesite.com/AngularApp#/ rather than http://somesite.com/AngularApp/#/

The fix I settled on was to check the URL of the request when it came in and if it matched the root url, but didn’t have the trailing slash, add the trailing slash.  I just added the following code to the Global.asax

protected void Application_BeginRequest()
{
    if (Request.ApplicationPath != "/" 
            && Request.ApplicationPath.Equals(Request.Path, StringComparison.CurrentCultureIgnoreCase))
    {
        var redirectUrl = VirtualPathUtility.AppendTrailingSlash(Request.ApplicationPath);
        Response.RedirectPermanent(redirectUrl);
    }
}

21 Jan

Show File Extension in Sharepoint Document Library

I was recently tasked with changing the view in our TFS Project Portal’s document library to show the file extension so you knew whether you were opening a PDF, Word Document, PowerPoint or something else for which you might not have the software installed.  There is an easy way and a hard way, and the guidance I received from a few Google searches skipped a few steps on the hard way.

Easy Way

  1. Open the Document Library
  2. Click on the Library Tools -> Library tab at the top
  3. Scroll to the bottom and click the view you wish to change
  4. Check “Name used in forms” column

This adds a new column with the full file name and extension, but it’s not clickable to view the document, so you end up with two columns for document name, the name and the name with extension.  This is why I ended up going down the hard way.

Hard Way

  1. Open the site in SharePoint Designer
  2. Click Lists and Libraries and open the Document Library you want to modify
  3. Open the View you want to modify
  4. Right click on the first row in the Name column and select Insert Formula
  5. In the Edit XPath expression box enter something that is easily searchable ie. xxxxxxxx
  6. Click OK
  7. Switch to the Code View
  8. Search for your xxxxxxx and delete the element it created
  9. Right above or below that should be an element like so <xsl:value-of select=”$thisNode/@FileLeafRef.Name” />
  10. Replace that with <xsl:value-of select=”$thisNode/@FileLeafRef.Name” /><xsl:if test=”$thisNode/@FileLeafRef.Suffix!=””>.<xsl:value-of select=”$thisNode/@FileLeafRef.Suffix” /></xsl:if>
  11. Save, exit and you’re done

The examples I found online left out steps 4-8, and until I did those the <xsl:value-of select=”$thisNode/@FileLeafRef.Name” /> element didn’t exist.

06 Jan

New Year’s Resolutions 2014

Here’s my list of New Year’s Resolutions for 2014:

Blog More

Historically, I only wrote technical posts whenever I came across something really novel and wasn’t so specific that I didn’t think anyone else can use it.  This year I’m going to loosen my standards for a technical blog posts.  That means there should be more posts, but some of them might just be a different spin on things that others have covered, or things that are really specific and may not be as useful to a broad audience.

Find more ways to share knowledge

For most of last year I shared an office with a very green developer.  Helping him learn the ropes has also been a great learning experience for myself.  Having to explain some concepts I take for granted to a beginner stretched me in ways I didn’t know I needed to be stretched.  I’d like to explore other ways to share what I have learned.  I have already volunteered to do a short talk on T4 at my local users group (after the main talk), hopefully I’ll be invited back after that.  I’m also going to keep a lookout local volunteer opportunities for things like Hour of Code

Networking

I’ve worked with and met a lot of great people, but many of them I haven’t spoken with in years.  My goal is to reach out to someone twice  a week either through email or phone.

Personal Stuff

I also have a few resolutions on a more personal level:

Go to bed earlier – I’m an early riser by nature, and every thing in life is easier when I’m well rested

Lose weight – It’s the cliche New Year’s resolution I know, but I lost a bunch of weight before my youngest son was born last April and have packed on the pounds since then.  I make bad food related decision when I’m tired and a new baby isn’t anything if it isn’t tiring.  I’d like to lose 40 pounds and keep it off for good.  No crazy diet here, more of a lifestyle change.  Just eating a little less and working out a little more.  I should be able to lose all of that weight by the end of 2014.

 

30 Nov

Error: The Key is Already Attached from Breeze.js

First let me say if you’re reading this and you haven’t checked out Breeze.js, you should.  It’s a nice little library.  I’m using it in an Angular.js based application and it has greatly simplified data access.  Now on to the show.

I’m using Breeze.js for data access and as part of that I am registering some constructors like so.

var AddressCtor = function () {
    this.CompanyId = document.getElementById('CompanyId').value;
};

var NoteCtor = function () {
    this.CompanyId = document.getElementById('CompanyId').value;
};

metadataStore.registerEntityTypeCtor('Address', AddressCtor);
metadataStore.registerEntityTypeCtor('Note', NoteCtor);

Everything was working fine then I tried to do a little code cleanup.  The constructors for Note and Address were identical.   So, of course I, re-factored them into a single generically named function.

var CompanyEntity = function () {
    this.CompanyId = document.getElementById('CompanyId').value;
};

metadataStore.registerEntityTypeCtor('Address', CompanyEntity);
metadataStore.registerEntityTypeCtor('Note', CompanyEntity);

Well, it appears that you cannot register 2 constructors that call the same function like that.  If you do you the error “The Key is Already Attached: Note:Webapplication1″, and no data loaded in the Angular app.

When I finally found the cause, I couldn’t believe it.  The fix that I came up with was to pass null as the constructor parameter and move that CompanyId assignment into a function that is passed as the init parameter.  This has the added bonus that entities that need to do a series of things AND set the CompanyId can just call the companyEntityInit function from inside their initializers.

function companyEntityInit(entity) {
    entity.CompanyId = document.getElementById('CompanyId').value;
};

metadataStore.registerEntityTypeCtor('Address', null, companyEntityInit);
metadataStore.registerEntityTypeCtor('Note', null, companyEntityInit);

 

16 Sep

Burned by Ternary Operators

First let me say that I’m not a fan of ternary operators.  I try to avoid them because I feel that an explicit if/else is much more readable.  I do use them on occasion and this seemed like the perfect place for them.

I ran into a interesting problem while trying to get a Linq where clause to work with ternary operators.  In hindsight the problem is pretty obvious, but it stumped me and one of my coworkers for a few minutes.  It wasn’t until we solved the problem in a different way that I realized what I had initially missed.

First the setup:

var books = new List&lt;Book&gt;();
books.Add(new Book { Title = "To Kill a Mockingbird", Description = null });
books.Add(new Book { Title = "Catcher in the Rye", Description = null });

There where clause I was building was part of some basic search functionality.  It would take a passed in value and check to see if the Title or the Description contained that value:

var qry = books.Where(u =&gt;
            u.Description == null ? false : u.Description.ToUpper().Contains(searchStr)
            || u.Title == null ? false : u.Title.ToUpper().Contains(searchStr)
            );

Attempts to search for the letter T were returning no results.  Like I said, I struggled with it, called in a second set of eyes and they couldn’t see the problem.  We then re-wrote where clause like this:

var qry = books.Where(u =&gt;
            (u.Description != null &amp;&amp; u.Description.ToUpper().Contains(searchStr))
            || (u.Title != null &amp;&amp; u.Title.ToUpper().Contains(searchStr))
            );

This returned 2 results as expected.  After a bit of rumination I realized the problem was my lack of parentheses.  I thought I had written this:

var qry = books.Where(u =&gt;
            (u.Description == null ? false : u.Description.ToUpper().Contains(searchStr))
            || (u.Title == null ? false : u.Title.ToUpper().Contains(searchStr))
            );

When I had essentially written this:

var qry = books.Where(u =&gt;
            u.Description == null ? false : 
                    (
                    u.Description.ToUpper().Contains(searchStr)
                    || u.Title == null ? false : u.Title.ToUpper().Contains(searchStr)
                    )
            );

This was clearly just my misunderstanding of how ternary operators are interpreted, and I’m unlikely to ever forget this, but I’m also going to continue to avoid ternary operators wherever I can and encourage others to do the same.

10 Sep

Repository Pattern with Entity Framework

I’m currently working on a little side project using ASP.Net MVC and Entity Framework, so of course my urls looks something like http://mysite.com/orders/14 to pull up the order that has OrderId 14.  The problem of course is security, I need to ensure that users can only view their own orders. 

My first pass at managing this was to just add the UserId to the where clause of a LINQ query. 

dbContext.Orders.FirstOrDefault(x =&gt; x.Id == OrderId &amp; x.UserId == userId);

This obviously works, but leaves me open making mistakes down the road by either forgetting to add that to the where clause, or on a more complex where clause having the UserId portion not working in one of the Or branches.  My first thought was of course to add some unit tests to the controller actions to account for this, but when I thought about doing that I realized I would essentially be writing the same test for every action.

I decided I needed to move the responsibility for adding the UserId to the where clause to a central location that handled that for all calls to user specific entities.  I could then write unit tests that code once and rest assured that all my controller actions were covered for that case.

My solution involved putting a generic repository layer on top of Entity Framework that was able to both.

First I defined the repository interface:

public interface IRepository&lt;T&gt;
{
    T GetById(int id);
    IQueryable&lt;T&gt; GetAll();
    void Add(T entity);
    void Update(T entity);
    void Delete(int id);
    void Delete(T entity);
}

And an interface that all user specific entities must implement

public interface IUserEntity
{
    int Id { get; set; } 
    int UserId { get; set; }
}

Then the repository itself with methods for all CRUD operations.  You’ll notice that each operation checks to ensure that the UserId of the entity affected matches the UserId that was used to create the repository:

public class UserRepository&lt;T&gt; : IRepository&lt;T&gt; where T : class, IUserEntity
{
    private readonly int _userId;

    public UserRepository(DbContext dbContext, int userId)
    {
        _userId = userId;
        if (dbContext == null)
        {
            throw new ArgumentNullException("dbContext");
        }
        DbContext = dbContext;
        DbSet = DbContext.Set&lt;T&gt;();
    }

    protected DbContext DbContext { get; set; }

    protected DbSet&lt;T&gt; DbSet { get; set; }

    public virtual IQueryable&lt;T&gt; GetAll()
    {
        return DbSet.Where(x =&gt; x.UserId == _userId);
    }

    public virtual T GetById(int id)
    {
        var entity = DbSet.FirstOrDefault(x =&gt; x.Id == id &amp; x.UserId == _userId);
        return entity;
    }

    public virtual void Add(T entity)
    {
        entity.UserId = _userId;
        DbEntityEntry dbEntityEntry = DbContext.Entry(entity);
        if (dbEntityEntry.State != EntityState.Detached)
        {
            dbEntityEntry.State = EntityState.Added;
        }
        else
        {
            DbSet.Add(entity);
        }
    }

    public virtual void Update(T entity)
    {
        if (entity.UserId != _userId)
        {
            return;
        }

        DbEntityEntry dbEntityEntry = DbContext.Entry(entity);
        if (dbEntityEntry.State == EntityState.Detached)
        {
            DbSet.Attach(entity);
        }
        dbEntityEntry.State = EntityState.Modified;
    }

    public virtual void Delete(T entity)
    {
        if (entity.UserId != _userId)
        {
            return;
        }

        DbEntityEntry dbEntityEntry = DbContext.Entry(entity);
        if (dbEntityEntry.State != EntityState.Deleted)
        {
            dbEntityEntry.State = EntityState.Deleted;
        }
        else
        {
            DbSet.Attach(entity);
            DbSet.Remove(entity);
        }
    }

    public virtual void Delete(int id)
    {
        var entity = GetById(id);
        if (entity == null) return; // not found; assume already deleted.

        if (entity.UserId != _userId)
        {
            return;
        }

        Delete(entity);
    }
}

Next we wrap all of those repositories up in a Unit of Work pattern, (sample code only shows one repository, my production code has all my repositories in one UOW class).

public class UoW : IDisposable
{
    private readonly int _userId;
    private MyDbContext DbContext { get; set; }

    public UoW(int userId)
    {
        _userId = userId;
        DbContext = new MyDbContext();
    }

    private IRepository&lt;Order&gt; _orders;
    public IRepository&lt;Order&gt; Orders
    {
        get
        {
            if (_orders == null)
            {
                _orders = new UserRepository&lt;Order&gt;(DbContext, _userId);
            }
            return _orders;
        }
    }

    public void Dispose()
    {
        if (DbContext != null)
        {
            DbContext.Dispose();
        }
    }
}

Now when we want all of the Order 14, we create an instance of the Unit Of Work class for the current user and request Order 14.

var uow = new UoW(123);
uow.Orders.GetById(14);

And if someone types http://mysite.com/orders/14 into the address bar, they won’t get back any data if their UserId doesn’t match the UserId for Order 14.

It’s still not very testable, but all it takes is adding another constructor to allow us to pass in the DBContext:

    public UoW(int userId, MyDbContext dbContext)
    {
        _userId = userId;
        DbContext = dbContext;
    }

Now we can use any DBContext we like for testing.  I’m using Effort – Entity Framework Unit Testing Tool to create an in memory database context for testing purposes.  It’s fast and simple to use. 

20 Aug

I’m Back

After a long hiatus I’m going to start blogging again.

“Where have you been?” you might ask.  Well, I’ve had a few big changes in my life this past year.  

First, I took a new job at Winxnet, a software consulting and IT management company based in Portland, Maine.  It’s been a big switch.  The first contract I was assigned to was an enormous SharePoint project. Being a WPF developer, the switch to web development really stretched me, and the Sharepoint part was just an extra wrench thrown in the works.  I was learning a lot of things that most web and/or SharePoint developers would probably consider pretty basic.  It’s always fun getting stretched like that, but it left no time or energy for blogging.

Second, in April my second child was born.  Anyone with kids knows that I get a free pass for a few months for that, no questions asked.

Third, I’ve started another side project (no public details yet).  This has eaten up a bunch of time the last couple of months, but it has also got my gears turning with ideas for blog posts.

Oh, and one more thing.  My install of WordPress died.  I can no longer log into the admin account.  Rather than spend the time trying to solve the underlying issue I thought I’d check some other CMSs.  I’ll be migrating my old posts over the next week, then I’ll actually create a few new techy posts.

18 Oct

New Keyboard Layout Progress Update

Back in June I decided to change my keyboard layout with the hopes of increasing my typing speed. I have touch typed for 20 years on a QWERTY keyboard and was getting about 54 words per minute. First I tries Dvorak layout, but had a tough time even learning the keyboard never mind typing with any speed. Then I switched to the Colemak layout which I was able to get some speed on very quickly.

I’m currently typing at 60 words a minute. That’s a 10% improvement, and I’m sure there is still some speed left to be gained, but I’m not sure I’ll ever make it to my original goal of doubling my typing speed. That would be 108 WPM, and at this point I’d be thrilled with 80 WPM.

If I could go back, I’m not sure if I would switch again. More speed was my original goal, and I think I could have increased my speed faster if I had done as much practice with QWERTY as I have done with Colemak. But here I am, I have switched and I don’t think I’ll be going back.

The Colemak layout is noticeably easier to type on. Much less reaching for common letters (I’m looking at you E, R and T). My typing hasn’t suffered too terribly on QWERTY keyboards, but I can no longer touch type, I have to watch the keyboard.

Up next on my experiments with typing, mechanical keyboards. I have noticed in my practicing that there are times when I’m not sure whether I have typed a letter or not because of the mushy keys. I’ll monitor my WPM before and after that switch when I finally fork over the money for a >$100 keyboard.