Calling Team Foundation Server(TFS) APIs via SQL Server stored procedure

Question

I am creating my first ASP.NET 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();
} 

This code shown above works fine.

But is it possible to achieve same result using a SQL Server stored procedure? Is there any way to connect to TFS and add bug in to TFS using a 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 achieved from a stored procedure.

Answer 1

Update:

You could call a web service from SQL CLR. Some tutorials maybe helpful:

• How to create a SQL CLR stored procedure to get data from a web service and insert results into a SQL Server table
• Generate JSON Data Using Web Service And SQL Server Stored Procedure

---

No, the only database that's designed for user querying is the warehouse database. The others are explicitly not supported and should not be directly queried.

Do not make any changes to the TFS Databases directly, otherwise you may lose the support from Microsoft.

Please take a look at this similar question here: Add a stored procedure to TFS database

In addition to using client API, you could also use Rest API in TFS to do the something.

Work Items - Create

POST https://{instance}/{collection}/{project}/_apis/wit/workitems/${type}?api-version=5.0