Lou Franco's Software Business Blog

Once you get TFS and Fogbugz synched, you can use FB to get to logs for a specific file. FB let's you set any URL and will put the full path you stored in the CVS table into your URL.

Adam and I decided to write an ASP.NET website with a page to get the log. Just using a GridView and the SQL connection wizards is enough to just get a log.

Given a full path, this SQL line will give you records for a log:

SELECT
    tbl_ChangeSet.ChangeSetId, tbl_Identity.DisplayName, tbl_ChangeSet.Comment, tbl_ChangeSet.CreationDate

FROM
    tbl_ChangeSet INNER JOIN tbl_Identity ON tbl_ChangeSet.OwnerId = tbl_Identity.IdentityId
    INNER JOIN tbl_Version ON tbl_ChangeSet.ChangeSetId = tbl_Version.VersionFrom

WHERE tbl_Version.FullPath = @path

ORDER BY tbl_ChangeSet.ChangeSetId DESC

Here's how you set up a SqlDataSource in an aspx file:

<asp:SqlDataSource ID="TfsDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:TfsFogbugzSync.Properties.Settings.TfsVersionControlConnectionString %>"
            SelectCommand="SELECT tbl_ChangeSet.ChangeSetId, tbl_Identity.DisplayName, tbl_ChangeSet.Comment, tbl_ChangeSet.CreationDate FROM tbl_ChangeSet INNER JOIN tbl_Identity ON tbl_ChangeSet.OwnerId = tbl_Identity.IdentityId INNER JOIN tbl_Version ON tbl_ChangeSet.ChangeSetId = tbl_Version.VersionFrom WHERE tbl_Version.FullPath = @path ORDER BY tbl_ChangeSet.ChangeSetId DESC">
            <SelectParameters>
                <asp:QueryStringParameter Name="path" QueryStringField="path" />
            </SelectParameters>
        </asp:SqlDataSource>

I get the fullpath from a path QueryString variable. So the URL I put into the FogBugz site setting (Source Control URL for logs) has "?path=^FILE" at the end.

I'm also grabbing a connection string from my web.config under the name TfsVersionControlConnectionString. It's set like this:

<connectionStrings>
        <add name="TfsFogbugzSync.Properties.Settings.TfsVersionControlConnectionString" connectionString="Data Source=TFS_SERVER;Initial Catalog=TfsVersionControl;Integrated Security=True" providerName="System.Data.SqlClient"/>
    </connectionStrings>

Then you can show this SQL in a GridView or whatever you want.