2. VSTO Bug Tracker : The Web Service
Within Microsoft, there is a web service already implemented that I can use in my solution to call the bug tracking system and get back the data described in the previous article. For my demo, I’m going to implement a demo web service that will stand in for that internal Microsoft web service. It will return the same basic data structures as the internal web service does, so when I run my own copy of VSTO bug tracker, I can swap out this demo web service for the real one.
The demo web service will have three web methods. The first method is called BulkDataExport. This method takes a start date and an end date and returns back a dataset with all the bug data for that time period. This is a simplified version of the Microsoft provided web service that I use internally. That web method has more parameters to specify milestone, release, team, etc. But I will omit those for simplicity since my data is specific to my team.
<WebMethod()> _
Public Function BulkDataExport(ByVal dateFrom As String, _
ByVal dateTo As String) As System.Data.DataSet
[WebMethod()]
public System.Data.DataSet BulkDataExport(string dateFrom, string dateTo)
The second method is called to get one particular bug stat value for one particular date. This will be used by the UDF I will write later. This takes a particular team (milestone and release I omit) as well as the name of the column I want to retrieve (e.g. “Active”) and the date for which I want to get the value. So I can effectively ask the question through this web method, “How many active bugs did the VBA team have on May 1st?”
<WebMethod()> _
Public Function GetColumnValue(ByVal team As String, _
ByVal column As String, ByVal dateGathered As String) As String
[WebMethod()]
public string GetColumnValue(string team, string column, string dateGathered)
The third method will be used by my Word solution. It takes the date and returns a dataset with twenty columns representing the 4 key metrics for my 5 teams:
<WebMethod()> _
Public Function GetDailyValues(ByVal dateGathered As String) As DataSet.csharpcode, .csharpcode pre
{
font-size: small;
color: black;
font-family: consolas, "Courier New", courier, monospace;
background-color: #ffffff;
/*white-space: pre;*/
}
.csharpcode pre { margin: 0em; }
.csharpcode .rem { color: #008000; }
.csharpcode .kwrd { color: #0000ff; }
.csharpcode .str { color: #006080; }
.csharpcode .op { color: #0000c0; }
.csharpcode .preproc { color: #cc6633; }
.csharpcode .asp { background-color: #ffff00; }
.csharpcode .html { color: #800000; }
.csharpcode .attr { color: #ff0000; }
.csharpcode .alt
{
background-color: #f4f4f4;
width: 100%;
margin: 0em;
}
.csharpcode .lnum { color: #606060; }
[WebMethod()]
public DataSet GetDailyValues(string dateGathered)
Let’s walk through creating this web service.
Fire up Visual Studio 2008, select File > New > Project…, then expand out the Visual Basic or C# node, click the Web category, then pick ASP.NET Web Service Application. Name the project BugService, then click OK.
Now, let’s implement these two methods. First, let’s get a web service created. Delete the auto-created Service1.asmx file from your project. Right click on the project node, choose Add > New Item… and from the Add New Item dialog, click on the Web category, then click Web Service. Name the Web Service BugService.asmx. Then click Add.
Delete the “HelloWorld” web method that is created for you in the BugService.asmx file. Add a “using System.Data; ” statement to the top of the file. Then add a DataSet property to the BugService class that will contain our data.
Shared ds As New DataSet
Public Shared ReadOnly Property BugData() As DataSet
Get
If ds.Tables.Count = 0 Then
ds.ReadXml("C:\Users\ecarter\Documents\bugdata.xml")
End If
Return ds
End Get
End Property
static DataSet ds = new DataSet();
public static DataSet BugData
{
get
{
if (ds.Tables.Count == 0)
{
ds.ReadXml("C:\\Users\\ecarter\\Documents\\bugdata.xml");
}
return ds;
}
}
As you can see, this code kind of cheats—it loads a pre-serialized data set from disk. The data in this file has data that I originally obtained from Microsoft’s internal bug system. For my demo, I won’t live connect to this system, so instead I’ve saved off some XML. I also keep this around in a class level property so that I only have to load it once and subsequent web service calls that access this data won’t have to load all the bug data again and will therefore be quicker.
I’ve provided a copy of this data so you can play along here: https://ericca.members.winisp.net/bugdata.xml
Now, my implementation of the BulkDataExport web method. This method is pretty simple. It just grabs the data set with all the bug data and then does a restrictive query to only return the bug data between the dateFrom and dateTo. There’s some wierdness going on here with converting dates to strings and so forth—this is because I need to match the original Microsoft internal web service which has everything typed as string. For C#, be sure you add a using System.Data to the top of the class file.
<WebMethod()> _
Public Function BulkDataExport(ByVal dateFrom As String, ByVal dateTo As String) As System.Data.DataSet
Dim rows As DataTable = BugData.Tables("Table")
Dim startDate As DateTime
If (String.IsNullOrEmpty(dateFrom)) Then
startDate = Date.MinValue
Else
startDate = System.Convert.ToDateTime(dateFrom)
End If
Dim endDate As DateTime
If (String.IsNullOrEmpty(dateTo)) Then
endDate = Date.MaxValue
Else
endDate = System.Convert.ToDateTime(dateTo)
End If
Dim query = From row In rows.AsEnumerable() _
Where System.Convert.ToDateTime(row.Field(Of String)("Date")).Date >= startDate.Date And _
System.Convert.ToDateTime(row.Field(Of String)("Date")).Date <= endDate.Date _
Select row
Dim newrows As DataTable = query.CopyToDataTable()
newrows.TableName = "Table"
Dim returnDataSet As New DataSet()
returnDataSet.Tables.Add(newrows)
Return returnDataSet
End Function
[WebMethod()]
public System.Data.DataSet BulkDataExport(string dateFrom, string dateTo)
{
DataTable rows = BugData.Tables["Table"];
DateTime startDate;
if ((string.IsNullOrEmpty(dateFrom)))
{
startDate = System.DateTime.MinValue;
}
else
{
startDate = System.Convert.ToDateTime(dateFrom);
}
DateTime endDate;
if ((string.IsNullOrEmpty(dateTo)))
{
endDate = System.DateTime.MaxValue;
}
else
{
endDate = System.Convert.ToDateTime(dateTo);
}
var query = from row in rows.AsEnumerable()
where System.Convert.ToDateTime(row.Field<string>("Date")).Date >= startDate.Date &&
System.Convert.ToDateTime(row.Field<string>("Date")).Date <= endDate.Date
select row;
DataTable newrows = query.CopyToDataTable();
newrows.TableName = "Table";
DataSet returnDataSet = new DataSet();
returnDataSet.Tables.Add(newrows);
return returnDataSet;
}
Next, the implementation of GetColumnValue. This also does a basic query over the data to get back the row in the table that corresponds to the team, column, and date specified.
<WebMethod()> _
Public Function GetColumnValue(ByVal team As String, ByVal column As String, ByVal dateGathered As String) As String
Dim rows As DataTable = BugData.Tables("Table")
Dim startDate As DateTime = System.Convert.ToDateTime(dateGathered)
Dim query = From row In rows.AsEnumerable() _
Where row.Field(Of String)("Team") = team And _
row.Field(Of String)("Column") = column And _
System.DateTime.Compare(System.Convert.ToDateTime(row.Field(Of String)("Date")).Date, startDate.Date) = 0 _
Select row
For Each row In query
Return row.Field(Of String)("Value")
Next
Return "ERROR"
End Function
[WebMethod()]
public string GetColumnValue(string team, string column, string dateGathered)
{
DataTable rows = BugData.Tables["Table"];
DateTime startDate = System.Convert.ToDateTime(dateGathered);
var query = from row in rows.AsEnumerable()
where row.Field<string>("Team") == team &&
row.Field<string>("Column") == column &&
System.DateTime.Compare(System.Convert.ToDateTime(row.Field<string>("Date")).Date, startDate.Date) == 0
select row;
foreach (var row in query)
{
return row.Field<string>("Value");
}
return "ERROR";
}
Finally, the implementation of GetDailyValues:
<WebMethod()> _
Public Function GetDailyValues(ByVal dateGathered As String) As DataSet
Dim rows As DataTable = BugData.Tables("Table")
Dim startDate As DateTime = System.Convert.ToDateTime(dateGathered)
Dim query = From row In rows.AsEnumerable() _
Where System.DateTime.Compare(System.Convert.ToDateTime(row.Field(Of String)("Date")).Date, startDate.Date) = 0 And _
(row.Field(Of String)("Column") = "Active" Or _
row.Field(Of String)("Column") = "Incoming Rate (7 Day)" Or _
row.Field(Of String)("Column") = "Resolved Fixed Rate (7 Day)" Or _
row.Field(Of String)("Column") = "Resolved Rate (7 Day)") _
Order By row.Field(Of String)("Team"), row.Field(Of String)("Column") _
Select row
Dim newrows As DataTable = New DataTable("Table")
newrows.TableName = "Table"
Dim i As Integer
For i = 1 To 20
newrows.Columns.Add(i.ToString())
Next
Dim objectArray(19) As Object
i = 0
For Each row In query
objectArray(i) = row.Field(Of String)("Value")
i = i + 1
Next
newrows.Rows.Add(objectArray)
Dim returnDataSet As New DataSet()
returnDataSet.Tables.Add(newrows)
Return returnDataSet
End Function
[WebMethod()]
public DataSet GetDailyValues(string dateGathered)
{
DataTable rows = BugData.Tables["Table"];
DateTime startDate = System.Convert.ToDateTime(dateGathered);
var query = from row in rows.AsEnumerable()
where System.DateTime.Compare(System.Convert.ToDateTime(row.Field<String>("Date")).Date, startDate.Date) == 0 &&
(row.Field<string>("Column") == "Active" ||
row.Field<string>("Column") == "Incoming Rate (7 Day)" ||
row.Field<string>("Column") == "Resolved Fixed Rate (7 Day)" ||
row.Field<string>("Column") == "Resolved Rate (7 Day)")
orderby row.Field<string>("Team"), row.Field<string>("Column")
select row;
DataTable newrows = new DataTable("Table");
newrows.TableName = "Table";
int i;
for (i = 1; i <= 20; i++)
{
newrows.Columns.Add(i.ToString());
}
object[] objectArray = new object[20];
i = 0;
foreach (var row in query)
{
objectArray[i] = row.Field<string>("Value");
i = i + 1;
}
newrows.Rows.Add(objectArray);
DataSet returnDataSet = new DataSet();
returnDataSet.Tables.Add(newrows);
return returnDataSet;
}
You should be able to run the web service project and test it.
Now that we have a working web service, lets create a UDF that uses it.