Tuesday, June 28, 2011

Query files in the TFS VersionControl database

TFS provides an API that C# could programmatically query source control. However, even with Linq, that may become tedious coding. TFS also provides a TfsVersionControl database that you can query directly with SQL. This has power.
Why use the undocumented TfsVersionControl database when you're "encouraged" to use TfsWareHouse?
  1. The Transaction databases (TfsBuild, TfsVersionControl, TfsIntegration) are realtime, so you don't need to wait 30 minutes - 2 hours for it to refresh.
  2. Not all the info is migrated to the TfsWareHouse (or at least, I can't find it in any documentation). For example, the warehouse has a File table, but it doesn't contain all versioned files (such as binaries, images, etc...)
  3. The TFS warehouse may be corrupted (the process to sync it may be down)
Here's a simple (TFS 2008) query to get you started. It contains version, the full path, file name, and the CreateDate (when it was checked in). It's based on versioned items, so you can query history (you may also get duplicated, so you'd need to query that).
select
V.VersionFrom, V.FullPath, L.CreationDate,
Replace(V.ChildItem, '\', '') as [FileName],
V.*, L.*
from tbl_Version V (nolock)
inner join tbl_File L (nolock) on V.FileId = L.FileId
where V.ParentPath = '$\MyTeamProject\Folder\SubFolder\'
order by V.VersionFrom desc
Note that TFS by default stores paths in a different format, so you may need to convert:
·         '/' becomes \
·          '_' becomes >
·         '-' becomes " (double quote)

Monday, June 27, 2011

Linq: Creating new objects from selects and joins

I like Linq more every time I use is. I've posted about XLinq and using linq to sort and filter lists. You can also use Linq to join two objects and select properties from each to create a new collection of objects (somewhat like SQL), run ForEach clauses, and do simple functions like Distinct, Sum, and Count.
Here's a code sample (I prefer to do minimalist samples with a unit test syntax for easy demos):

using System;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using Microsoft.VisualStudio.TestTools.UnitTesting;

namespace LinqDemo
{
      /// <summary>
      /// Summary description for UnitTest1
      /// </summary>
      [TestClass]
      public class UnitTest1
      {
            private Employee[] GetEmployees()
            {
                  return new Employee[]
                  {
                    new Employee(){ FirstName = "Homer", LastName ="Simpson", FavoriteNumber=7, DeptId=1},
                    new Employee(){ FirstName = "Marge", LastName ="Simpson", FavoriteNumber=18, DeptId=0},
                    new Employee(){ FirstName = "Bart", LastName ="Simpson", FavoriteNumber=99, DeptId=0},
                    new Employee(){ FirstName = "Monty", LastName ="Burns", FavoriteNumber=23, DeptId=9},
                    new Employee(){ FirstName = "Ned", LastName ="Flanders", FavoriteNumber=5, DeptId=0}
                  };
            }

            private Department[] GetDepartments()
            {
                  return new Department[]
                  {
                        new Department(){ DeptId=1, DeptName="Safety Operator"},
                        new Department(){ DeptId=2, DeptName="Customer Service"},
                        new Department(){ DeptId=9, DeptName="Executive"},
                  };
            }



            [TestMethod]
            public void SelectProperties()
            {
                  //create data
                  Employee[] emps = GetEmployees();

                  //Use linq to get a distinct list from some property
                  List<string> lastNames = emps
                        .Where(n => n.FavoriteNumber > 10) //Some filter
                        .OrderBy(n => n.LastName)
                        .Select(n => n.LastName) //Select specific fields
                        .Distinct() //Get only distict elements
                        .ToList();

                  Assert.AreEqual("Burns", lastNames[0]);
                  Assert.AreEqual("Simpson", lastNames[1]);
            }

            [TestMethod]
            public void JoinAndCreateAnotherObject()
            {
                  //Join "Employee" and Department to create "Worker"
                  Employee[] emps = GetEmployees();
                  Department[] depts = GetDepartments();

                  //This could be useful is emps and depts came from
                  //    different sources, or depts was cached
                  Worker[] workers =
                        (
                              from emp in emps
                              from dept in depts
                              where emp.DeptId == dept.DeptId
                                    && emp.DeptId > 0
                              select new Worker()
                              {
                                    FirstName = emp.FirstName,
                                    DeptName = dept.DeptName
                              }
                        ).ToArray();

                  Assert.AreEqual("Homer", workers[0].FirstName);
                  Assert.AreEqual("Safety Operator", workers[0].DeptName);
            }

            [TestMethod]
            public void ForEach()
            {
                  //Use a single line to update a property
                  List<Employee> emps = GetEmployees().ToList();
                  Assert.AreEqual(7, emps[0].FavoriteNumber);

                  //Double everyone's favorte number
                  //Easier than writing a for-each loop
                  emps.ForEach(n => n.FavoriteNumber = n.FavoriteNumber * 2);

                  Assert.AreEqual(14, emps[0].FavoriteNumber);
            }

            [TestMethod]
            public void Do_Aggregates()
            {
                  //Get the sum of all numbers where the number is already > 10.
                  List<Employee> emps = GetEmployees().ToList();
                  int intSum = emps
                        .Where(n => n.FavoriteNumber > 10)
                        .Sum(n => n.FavoriteNumber);
                  Assert.AreEqual(140, intSum);
            }


      }

      public class Employee
      {
            public int FavoriteNumber { get; set; }
            public int DeptId { get; set; }
            public string FirstName { get; set; }
            public string LastName { get; set; }

            public override string ToString()
            {
                  return string.Format("{0} {1} - {2}", this.FirstName, this.LastName, this.FavoriteNumber);
            }
      }

      public class Department
      {
            public int DeptId { get; set; }
            public string DeptName { get; set; }
      }

      public class Worker
      {
            public string FirstName { get; set; }
            public string DeptName { get; set; }
      }


}