Calling Team Foundation Server(TFS) APIs VIA SQL Store-procedure RRS feed

  • Question

  • I am creating my first MVC project. I have started with connecting TFS and adding bugs in to TFS via C#.

    var tfsURI = new Uri("http://test:8080/tfs"); var networkCredential1 = new NetworkCredential( "test", "test!"); ICredentials credential = (ICredentials)networkCredential1; Microsoft.VisualStudio.Services.Common.WindowsCredential winCred = new Microsoft.VisualStudio.Services.Common.WindowsCredential(credential); VssCredentials vssCredentials = new VssCredentials(winCred); using (TfsTeamProjectCollection collection = new TfsTeamProjectCollection(tfsURI, vssCredentials)) { collection.EnsureAuthenticated(); WorkItemStore workItemStore = collection.GetService<WorkItemStore>(); Project teamProject = workItemStore.Projects["Test"]; WorkItemType workItemType = teamProject.WorkItemTypes["Bug"]; WorkItem Defect = new WorkItem(workItemType); FileInfo fi = new FileInfo(@"C:\\Document.docx"); Attachment tfsAttachment = new Attachment(fi.FullName); Defect.Attachments.Add(tfsAttachment); Defect.Title = "Testing from VS to TFS Bug"; Defect.Description = "Testing from VS to entered Bug in to TFS."; Defect.Fields["Assigned To"].Value = "Test"; Defect.Save(); }

    above code works fine.

    But is it possible to achieve same result using SQL Stored procedure? is there any way to connect to TFS and add bug in to TFS using SQL Stored Procedure?

    I have my database and from sql stored procedure i wanted to connect to TFS and create WorkItem. via C# i have done as seen above example. but i need any example if same thing can be achieve from sql - Stored Procedure.

    Friday, July 26, 2019 6:17 AM

All replies

  • Yes, it's doable, but it is not as easy as it should be.

    First you have to enable CLR integration in your database, if it isn't already enabled. Then, you compile your C# code into a DLL and load it into the database with a "create assembly" statement. Then you use some SQL Statements to declare the content of the dll so that SQL Server recognizes it.

    After you have done that, you can call the methods in the dll from SQL statements in the same way as if they were stored procedures.

    However: By default, the code in the assemblies loaded in this way runs with limited permissions, and it is not allowed to make external calls. If you have sufficient permissions in SQL Server, you can load the assembly with the "...with external_access" clause, which gives it permission to make calls to TFS (or any other server). But: once you've done this, when you try to execute it you will start getting lots of errors about wrong versions of assemblies because it tries to load versions of the .net libraries that are not the same that you compiled. With some effort, these can eventually be resolved, but it is not as straightforward as we would like it to be.

    Saturday, July 27, 2019 12:13 PM
  • Wise words from Alberto.

    You'll be stretching your security and auditing policies too, if you give SQL server such powers.

    Are we talking WIQL here, or have I got my wires crossed?

    I have dug this out of my links that may help show some of the structure you need:


    And a quick scan dug this up, which also shows some direction:


    But most importantly, if it's not common practice and you can't find reams of info on it, there's probably a reason (like security, delegation, management, auditing, maintenance, etc)

    If you're new and skilling up on something, you can sometimes try to apply your existing knowledge to the new landscape. Thus spend twice as much time trying to shoehorn in a solution that will later become clear why was not the best approach. I'm sure you know all this. Just helps to be reminded from time to time.

    Keep service layers in service classes, in shared libraries. Let the database just worry about storing and serving data. Compile a library of useful functions around the infrastructure in well managed code, through shared repositories. Anything specialist will become a chain around your neck, I'm sure you understand where I'm coming from.

    Good luck!

    Got any nice code? If you invest time in coding an elegant, novel or impressive answer on MSDN forums, why not copy it over to TechNet Wiki, for future generations to benefit from! You'll never get archived again, and you could win weekly awards!

    Have you got what it takes o become this month's TechNet Technical Guru? Join a long list of well known community big hitters, show your knowledge and prowess in your favoured technologies!

    Saturday, July 27, 2019 4:38 PM