vsts extension making database calls

Question

I'm investigating building a VSTS Extension that can call an external SQL database passing a task ID, display the results in a task, and pass back an update if needed back to the SQL database. I have been looking for a while for a microsoft help doc that will answer this question but have not had any luck. Is it possible to interact with an external SQL database using a VSTS Extension? If so, is there any documentation out there that I missed?

UI extension? No. You'd need to host it on an external Azure website or self-hosted IIS instance and then can into it from the extension over a REST API. — jessehouwing, Apr 11 '18 at 21:02

score 0 · Accepted Answer · answered Apr 12 '18 at 04:29

There isn’t the built-in api to do with database in VSTS extension, the VSTS extension uses Data storage to store data.

Anyway, you just need to do it directly with some libraries, for example:

var connection = new ActiveXObject("ADODB.Connection") ;

var connectionstring="Data Source=<server>;Initial Catalog=<catalog>;User ID=<user>;Password=<password>;Provider=SQLOLEDB";

connection.Open(connectionstring);
var rs = new ActiveXObject("ADODB.Recordset");

rs.Open("SELECT * FROM table", connection);
rs.MoveFirst
while(!rs.eof)
{
   document.write(rs.fields(1));
   rs.movenext;
}

rs.close;
connection.close;

Related thread: How to connect to SQL Server database from JavaScript in the browser?

The better way is that you can build a API project (e.g. web api) to do with database (need to be accessible from internet), then you can call that API with necessary data in you VSTS extension.

Thank you, this is the answer I needed. – Kyle Sykora Apr 12 '18 at 12:13 — Kyle Sykora, Apr 12 '18 at 12:13

vsts extension making database calls

1 Answers1