Comparing two objects with reflection

About 6 weeks ago I was tasked with implementing the most requested feature in one of our products at PragmaticWorks named BI Documenter. BI Documenter will take a snapshot of selected databases, ssis packages, analysis services databases and reporting services databases and allow you to create CHM (help docs) that show everything in your selected objects. We are trying to make BI Documenter a more everyday use tool by adding features like impact analysis, lineage and the most requested feature, snapshot compare.


When BI Documenter takes a snapshot it stores the snapshot data in database tables. It's stored in a very granular fashion. Some of the table names are:

Database
Tables
Views
Procs
Packages
DataFlowTasks
ConnectionManagers

We basically document almost everything about the db, package, etc that is selected. When the help document is created that data is retrieved from the tables via hibernator objects.

After thinking about how I would implement the compare I really had two choices; Run a bunch of sql statements with inner and outer joins or compare the objects. I decided to compare the objects. The code is already there to retrieve the database and hydrate the objects and I could take advantage of LINQ so it was a logical choice for a developer.

After deciding on this strategy I found that it would be a little tougher than I anticipated. Not because it was difficult to compare the objects but rather it was going to be time consuming to write compare code for each object (database, table, view, stored proc, etc). So I decided to use reflection to help my cause. The following code will look at two objects properties and compare the data and return a list of the properties that are not equal and their values.


private List<PropertyComparison> ComparePropertiesAndFindNonMatches<T>(T object1, T object1)

        {

            PropertyInfo[] properties = object1.GetType().GetProperties();

            List<PropertyComparison> nonMatchedProperties = new List<PropertyComparison>();

           

            foreach (PropertyInfo info in properties)

            {

           


                if (info.PropertyType.BaseType.Name == "Object" && info.PropertyType.Name != "String")

                    continue;

 

                object[] atts = info.GetCustomAttributes(true);

 

 

                bool hasExcludeAttribute = false;

                foreach (object att in atts)

                {

                    ExcludeFromCompare excludeAtt = att as ExcludeFromCompare;

                    if (excludeAtt != null)

                    {

                        hasExcludeAttribute = true;

                        break;

                    }

                }

 

                if (hasExcludeAttribute) continue;

 

                object value1 = info.GetValue(object1, null);

                object value2 = info.GetValue(object2, null);

 

                if (value1 == null && value2 == null)

                {

                    continue;

                }

 

                if (value1 == null || value2 == null)

                {

                    nonMatchedProperties.Add(new PropertyComparison() { PropertyName = info.Name, Value1 = (value1 == null ? string.Empty : value1), Value2 = (value2 == null ? string.Empty : value2) });

                    continue;

                }

 

                if (!value1.Equals(value2))

                {

                    nonMatchedProperties.Add(new PropertyComparison() { PropertyName = info.Name, Value1 = value1, Value1 = value2 });

                }

 

            }

 

            return nonMatchedProperties;

        }

 

I use a generic (<T>) method to make sure that I don't make the mistake of trying to compare two different types of objects. For instance if I tried use the following code, it wouldn't work because I'm trying to compare a database object to a table object. 

SsDatabase db1 = null;

SsTable table1 = null;

 

List<PropertyComparison> nonMatchedProperties = ComparePropertiesAndFindNonMatches<SsDatabase>(db1, table1);


The line of code below was used to not compare object types unless it was a string. I supposed I could have just checked for Name == "Value" but I digress.

if (info.PropertyType.BaseType.Name == "Object" && info.PropertyType.Name != "String")

The PropertyComparison class is a really simple class to store the values of the property being compared.

public class PropertyComparison

{

    public string PropertyName

    {

        get;

        set;

    }

 

    public object Value1

    {

        get;

        set;

    }

 

    public object Value2

   {

        get;

        set;

    }

}

The ExcludeFromCompare attribute was used to decorate a property if I didn't want the property compared. This is useful for properties that will change with each snapshot like the property DatabaseId which stores an identity column when hydrated.

[AttributeUsage(AttributeTargets.Property)]

    public class ExcludeFromCompare : Attribute

    {

 

    }

And that's that. A pretty easy to use method to compare to objects properties of the same type.

Shifting focus

When I first started this blog my goal was to blog all the time. That started off really great. I got a BUNCH of traffic from my LINQ TO SQL blog posts. Then the shine of the new blog started to wear off. The only thing this blog has been about lately has been inconsistency. Go figure...

In any case, one of the biggest issues that I have with a blog just about software / web development is that I generally don't have enough to discuss in that arena or I simply don't have the will to write about something I found that was cool. With that said, I've decided to shift away from trying (or not trying) to blog purely about development. This is now going to be a more personal blog about software development, real estate investing, SEO work, making money, music, books, etc. I have a bunch of interests outside of software development and I didn't really have an outlet for those things.

So with renewed interest I'll be blogging more consistently now. In fact, look for the first new post to be posted soon.

Quick Update

After a few months as a contractor for PragmaticWorks, Inc I have accepted an offer to become a full time employee with them. I'm a product manager for TaskFactory, a collection of SSIS components. I built the product from the ground up. The first release went out last week and we'll be adding 15-20 more components over the next few months. It's raw right now and we have some beta testers on it now. I'm sure there will be a lot of changes to it over the next few months.

One of the requirements of the new position is that I blog every week. Over the next few months I'll probably be blogging about SSIS, PowerShell (I'm learning it now), SQLShare updates (I'm doing videos for them), debugging  and other various stuff.

Looking forward to blogging again!

Until next time...

Implicit conversion from data type sql_variant to uniqueidentifer is not allowed.

If you use guids (uniqueidentifiers) in your database in conjunction with ASP.NET SqlDataSource databinding, chances are you'll eventually run into this error "Implicit conversion from data type sql_variant to uniqueidentifer is not allowed. User the CONVERT function to run this query".

Here is a screenshot of the whole error:


The problem is Visual Studio sucks at picking up the fact that this error will occur EVERY SINGLE TIME and continues to let the error happen.

It's easy to fix though. Find your parameters (selectparameters, insertparameters, etc) for the particular statement you're running (select, update, delete, insert). You should see something like this:



The fix is just to remove the Type = "Object" from the parameter that is linked to the uniqueidentifer column in your table. In this case, the controlparameter "CompanyID"  is linked to the companyid column in my companies table. The resulting code should look like this:


Until Next Time...

Ok, So I neglected my blog

For the past couple of months I've neglected my blog. Although, I have been getting a lot of positive feedback for my previous post so at least I'm getting a decent amount of traffic. To be quite honest, I lost the drive to write for a while. I'm hoping that changes and I can get back into giving some decent content to everyone.

I've been mostly working on jumpstarttv.com which is in constant motion. I'm always doing something with it. The development is coming to a close by the end of the month with just minor updates here and there but it's mostly just going to be content development.

Until Next Time...

Relaunch of JumpStartTV.com

A few months ago my company acquired jumpstarttv.com. One of our partners Brian Knight had started the project a few years ago but got busy and it was left in limbo. We decided that we could take it over and make it a really great site for tasked based videos. Our idea is to have very short (2-5 minute) videos that teach you how to do a specific task or explain a single subject. A couple of examples are "What is the index seek operator" and "Creating a database with SMO".

My part of the project was to spiff it up a little bit. So I've done a few things to the site to make it more user friendly. All the work has been done in visual studio 2008. There's even a little ajax thrown in as well (Part of the "Watch It Later" functionality). With that said there is still a lot of work we're doing to make the site even better.

Look for more updates from me as we goes through various iterations on the site. If you have any suggestions for the site, let me know. We are always looking for feedback!

I've pretty much given up on IE7

IE7 has been giving me a BUNCH of problems lately. Not just on one machine but ALL THREE OF MY MACHINES! It consistently freezes, crashes and continues to really irritate me. I'm using Firefox until they either fix the problem(s). I'll be loading up chrome this week as well.

I used to like IE6 on XP because I could login as a limited access user and use IE as a "shell" to open administrative windows using MakeMeAdmin. They took that away with IE7 and now I'm stuck learning all of the control panel command names. (compmgmt.msc = Computer Management). I'm not real sure what they were thinking with that unless they had only had Vista on the brain.

All in all I'm pretty disappointed with IE. I still *have* to use it while testing features on jumpstarttv.com but other than that, It's firefox for me!

Datatable to List of objects mapper.

In my previous blog post about mapping data rows to objects you saw how easy it was to use reflection to map a data row to an object. With a little extra effort you can map data tables to a list of those mapped objects. The code below builds on my previous blog and will not work unless you have the code from that blog entry.

Public Shared Function MapDataTableToList(Of T)(ByVal dt As DataTable) As IList(Of T)
        If dt Is Nothing Then
            Return Nothing
        End If
 
        Dim list As IList(Of T) = New List(Of T)
       
        For Each dr As DataRow In dt.Rows
            list.Add(MapDatarowToObject(Of T)(dr))
        Next
 
        Return list
    End Function
To explain how this works we’ll go line by line

Public Shared Function MapDataTableToList(Of T)(ByVal dt As DataTable) As IList(Of T)

I’m creating a method that needs to be called by defining a type for T. The only parameter is a data table. An example of calling this method looks like this:

Dim dt As DataTable = GetDataTableFromDb()
Dim usersList As IList(Of User) = MapDataTableToList(Of User)(dt)
 
This would convert a data table to a generic list (IList) of user objects.

If dt Is Nothing Then
Return Nothing
End If

These lines of code make sure that the data table parameter has a value.

Dim list As IList(Of T) = New List(Of T)
 
Create a generic list of objects based on T. T is the generic type specified when calling the method. In the example usage above the T parameter would be of type User.
 
 
For Each dr As DataRow In dt.Rows
list.Add(MapDatarowToObject(Of T)(dr))
Next

Iterate through each data row in the data table and call MapDatarowToObject (from the previous blog entry) and add the mapped object to the list.


Return list
 
Return the list of objects from the method.

So there you go, An easy to use data table to object mapper.

Until next time.

So I can add trainer to my resume...

It's been about a month since I last posted. I have a good excuse although I could have taken 5 minutes over the last month to at least say hi. In any case over the last month I've been busy with two big projects.

The first is the relaunch of JumpStartTV.com. It's was a site developed by Brian Knight with the intentions of a different kind of video site. Along the way it was forgotten. Andy decided to revive the project and I was the one tasked with the revival. I haven't done too much work to it yet as we just wanted to get it up and running with a few minor changes. I added a tag cloud and added some admin tools for automatically processing videos but that's about it. Look for some very nice updates over the next few weeks with a major push of content. We're currently planning 1 new video per day 4 days a week for the SQL channel. The .NET channel will start adding content in October. We have a lot of different authors contributing content which should make for a very cool technical video site. By the way, all of the content is free.

The second project is the one with which I can add trainer to my resume. I successfully put together a Beginning ASP.NET course and delivered it to 7 students last week. It went well in most places but needs a little work to get it totally right. I would say that the students were pretty happy with the content and enjoyed their time training with me. I even had one student buy a $50 gift card as a thank you.

Andy and I are going to be developing two more courses that will be available next year. Advanced ASP.NET and .NET for DBA's. There will be more information on those courses to come.

I'm really excited about the work that I'm doing and this next year should be a really good for me both professionally and personally.

My next blog should be the one I promised a few months ago about mapping a data table to list objects.

Until next time.

Recap of the Central Florida .NET Users Group

Last tuesday I went to speak at the Central Florida .NET Users group. My session covered some of the new features in C# and VB.NET using Visual Studio 2008. I want to thank Roy and Charlie for inviting me to speak. It was my first users group session.

I had a good time with this group. There were a lot of really good questions and suggestions. I've never had such a huge response. Where were these attendees at my last SQL Saturday session?

After the session was over I got to hang out with a couple of the attendees at Chili's. Had a beer, some food and some really interesting conversations. One in particular about Project Euler. I had never heard about it before. It's a really cool site for nerds!

One of the most surprising things I heard that night was the number of employees that attended the users group from a local company. Out of approximately 1000 developers, 2 from the company showed up. 2 out of 1000.... That's just staggering to me. However it's not that surprising. A good number developers do not want to do anything outside of work. I think the reasoning I've heard behind that is if they aren't being paid for it, then why should they do it? The 2 people that showed up were really smart and I enjoyed talking with them.

All in all it was a good night. I got some great feedback, had a good time and enjoyed everything about it. Ok, maybe the two hour drive home at 10:30 P.M. wasn't all that fun but it was worth it...

Sorry for my lack of posts recently. I've been really busy with a few projects that you'll be hearing about shortly.

«March»
SunMonTueWedThuFriSat
28123456
78910111213
14151617181920
21222324252627
28293031123
45678910