FINALLY! After too many hours of work I've finally got it working. Let me just summarize what I've done.

First of all I downloaded and installed the SQL Server Management Studio Express so I won't need to use a command line tool on my local computer. A very good tool for configuring your SQL server. What I did was to detach the database from my Solution in Visual Studio and saved it somewhere else (D:\CodeMetricsDB.mdf & D:\CodeMetricsDB_log.ldf) - this was because I couldn't add files located in my user account directory into the Management studio (I wonder why?).

Anyway. After opening the application and connecting to your SQL serve, right-clikc Databases --> Attach... --> Add... --> Select the D:\CodeMetricsDB.mdf file and then press OK. Congrats, you have your CodeMetricsDB in your SQL Server now. (I renamed it to CMDB in this case afterwards).

In Visual Studio, open the Server Explorer and right-click on the Data Connections symbol --> Add new connection... --> choose your server name and then choose the DB from "Select or enter a database name:" (in this case CMDB). Press OK.

Now you're set to develop against a database in your SQL server instead of a separate file located in the App_Data subfolder (which is troublesome when moving it to the VPC).

You can from inside SQL Server Management Studio Express generate a script which creates the database with all constraints, stored procedure etc. Do this and run the script on the VPC, or copy over the files (.mdf and .ldf) and then run this from a command line (found the tip here):

sqlcmd -E -Q "exec sp_attach_db @dbname=N'CMDB',
@filename1=N'C:\path\CodeMetricsDB.mdf',
@filename2=N'C:\path\CodeMetricsDB_log.ldf'"

Now you should have the database in you VPC SQL Server (check it by either download and install SQL Server Management Studio Express on the VPC (yes, it works fine for "regular" SQL Server too), or use sqlcmd like this):

C:\> sqlcmd
1> use CMDB
2> go
Changed database context to 'CMDB'
1>

If it works, you have the database. Or, you could use a stored procedure to list the databases:

C:\>sqlcmd
1> sp_databases
2> go
DATABASE_NAME
---------------------------------------
---------------------------------------
CMDB

master

model

msdb

ReportServer

ReportServerTempDB

tempdb

TfsActivityLogging

TfsBuild

TfsIntegration

TfsWarehouse

TfsVersionControl

TfsWorkItemTracking

TfsWorkItemTrackingAttachments

WSS_AdminContent

WSS_Config

WSS_Content

1>

As you can see the CMDB database is on top.

But it isn't all set and done by just adding the table. Secondly you'll have to alter the connection string to:

1 string connstring = "Data Source=ORCASBETA2_TFSV;" +
2                     "Database=CMDB;" +
3                     "Integrated Security=true;";

As you can see we've changed the "Data source" from .\\SQLEXPRESS --> ORCASBETA2_TFSV (line 1) and the database name must match (line 2). Another thing to think about is if it is preferred to use SQL Server Authentication or Windows NT Authentication. We don't know yet, but probably Mr. König can give us some guidance of what method to prefer (as of now, I've just used Windows NT Authentication).

So everything should work fine now, right? No. When trying to access the database via the webservice by running the client on the VPC, we get an exception:

---------------------------

---------------------------
System.Data.SqlClient.SqlException: A connection was successfully established with the server, but then an error occurred during the login process.
(provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadSniError(TdsParserStateObject stateObj, UInt32 error)
at System.Data.SqlClient.TdsParserStateObject.ReadSni(DbAsyncResult asyncResult, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParserStateObject.ReadPacket(Int32 bytesExpected)
at System.Data.SqlClient.TdsParserStateObject.ReadBuffer()
at System.Data.SqlClient.TdsParserStateObject.ReadByte()
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject)
at System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart)
at System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance)
at System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options)
at System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at CodeMetricsService.registerMetrics(Int32[] theMetrics, String projName) in c:\WCFService1\App_Code\CodeMetricsService.cs:line 32
---------------------------
OK  
--------------------------- 

After some googling and research, I finally found a solution to the problem. Here's how to get around:

1. Start SQL Server Management Studio Express (in the VPC)
2. Go to [your server] --> Security --> Logins in the left pane
3. find NT AUTHORITY\NETWORK SERVICE and double click it to open the properties dialog
4. Go to User Mappings in the left pane of the properties pane
5. Identify your database in the table to the right (in this case: CMDB) and make sure the checkbox is marked under the column "Map" for you database
6. Under "Database role memebership for: CMDB [you databasename] check "db_owner".
7. Now it should work fine!

I don't know if this is a smart thing to do when havin security in mind, and there are possibly other combinations of role memberships that you could use to make it work (instead of db_owner). But it worked and I'm satisfied with that as for now. Improving things will be a later problem - now we can continue our work - and that would be to create an adapter for the TFS warehouse (finally!).

Oh yeah, and another thing. It isn't really working well to develop in the VPC using VS 2008. Everytim I try to build my solution for the website, it never finishes the build - just keeps on chewing on forever (even though it says "Validation Complete"). I don't know what's wrong and I don't have time to find out - so I will continue to develop on my XP machine and transfer files as necessary to the VPC via FTP.

Phew! That's a long post. Hope it could be helpful for someone out there.

I decided we're better off making a stored procedure instead of managing the SQL queries in C#. First of all it's more efficient since stored procedures are are pre-compiled such that they execute optimally. Second, the abstraction is good. Now you just have to call "InsertCodeMetrics" from the webservice instead of creating complex SQL queries at that level. The positive parts are many, the negative parts are non-existing. So, how did I do the stored procedure? See here:

 1 ALTER PROCEDURE dbo.InsertCodeMetrics
 2     (
 3         @Metric1 int,
 4         @Metric2 int,
 5         @Metric3 int,
 6         @Metric4 int,
 7         @Metric5 int,
 8         @ProjectName varchar(50)
 9     )
10    
11 AS
12     DECLARE @LatestEntry int
13    
14    
15     INSERT INTO SavedEntries (ProjectName) VALUES (@ProjectName)
16     SET @LatestEntry = (SELECT @@IDENTITY)
17    
18     INSERT INTO MetricsData (EntryID, Metrics1, Metrics2, Metrics3, Metrics4, Metrics5)
19     VALUES (@LatestEntry, @Metric1, @Metric2, @Metric3, @Metric4, @Metric5)

Simple and easy and it works. This is how you would use it in the webservice (C#):

 1 SqlConnection conn = new SqlConnection(connstring);
 2 
 3 try
 4 {
 5     SqlCommand command = new SqlCommand("InsertCodeMetrics", conn);
 6     command.CommandType = CommandType.StoredProcedure;
 7     command.Parameters.Add("@Metric1", SqlDbType.Int).Value = theMetrics[0];
 8     command.Parameters.Add("@Metric2", SqlDbType.Int).Value = theMetrics[1];
 9     command.Parameters.Add("@Metric3", SqlDbType.Int).Value = theMetrics[2];
10     command.Parameters.Add("@Metric4", SqlDbType.Int).Value = theMetrics[3];
11     command.Parameters.Add("@Metric5", SqlDbType.Int).Value = theMetrics[4];
12     command.Parameters.Add("@ProjectName", SqlDbType.VarChar).Value = projName;
13 
14     command.ExecuteNonQuery();
15     return "OK! Metrics added.";
16 } catch (Exception e)
17     return e.ToString();
18 }

Easy to both use and understand. (You can see how connstring is constructed in a previous post on my blog, if you're interested).

I've spent some time figuring out how to keep data integrity intact when working with several tables in my SQL Database. Several solutions are possible, and one of them are to create a stored procedure which takes care of updating several tables at once. I think that would be the wisest thing to do. However, I've postponed that solution for a while and made it work directly in my web service.

I discovered the @@IDENTITY attribute you can use in SQL. The @@IDENTITY attribute (or directive or whatever it is called) gives you the latest identifier for the table. So I've added it to my query when I insert an entry into the SavedEntries table:

 myCommand.CommandText = "INSERT INTO SavedEntries (ProjectName) " +
                        "VALUES ('" + projName +"'); SELECT @@IDENTITY;";

Then I save the identity just selected:

string latestIdentity = myCommand.ExecuteScalar().ToString();

Finally, I alter the MetricsData table using the identity as a foreign key to the column EntryID:

myCommand.CommandText = "INSERT INTO MetricsData (EntryID, Metrics1, Metrics2, Metrics3, Metrics4, Metrics5) " +
                                       "VALUES (" + Convert.ToInt32(latestIdentity) + "," +
                                       theMetrics[0] + "," +
                                       theMetrics[1] + "," +
                                       theMetrics[2] + "," +
                                       theMetrics[3] + "," +
                                       theMetrics[4] + ");";

I don't know if the conversion to Int32 is necessary (second row above), but it feels safer because latestIdentity is declared as a string.

Anyway, it works. Now I just have to make it work on the VPC. Furthermore, I'll give it a thought about implementing this in a stored procedure instead. We'll see what happens :-). Feedback and thoughts on this matter are much appreciated!

After some search I've found the way to do it:

1. Open the table in Server Explorer and select the column in which you want to store the automatic timestamp.
2. Under Column Properties, modify "Default Value or Binding" to hold: (getdate()).
3. Save, and you're done!

Now everytime you insert a record into the database, the timestamp will be generated for you.

Now, I've managed to create my own database in my Solution in VS, created some (preliminary) tables, and I got it all working on my XP machine. But then we have that thing about transferring it to the VPC to get it to work in Windows Server 2003. Not so easy as I thought.

First of all, let's talk about my tables. This is just a preliminary design, but I think we will keep it somehow intact:

This table is called "SavedEntries". Everytime someone evaluates code metrics and sends the result to TFS, a new row will be added to this table in the database. ID is the primary key, ProjectName (or whatever ww in the future will choose as an artifact) will hold artifact-information. TimeStamp stores time and date for when the metrics were performed. We will perhaps need to store more attributes, so it is possible to extend the table with more columns.

This table is called "MetricsData". It speaks for itself. ID is the primary key for the table, EntryID is a foregin key mapped to SavedEntries.ID (see SavedEntries table above). The meric reults are stored in his table.

That was the tables. Now to my problems. I've created a new SQL Database called CodeMetricsDB.mdf and it is stored in the App_Data folder (together with its CodeMetricsDB_log.LDF file). It works fine on XP with the following connection string:

        string connstring = "Data Source=.\\SQLEXPRESS;" +
                           "AttachDbFilename=|DataDirectory|CodeMetricsDB.mdf;" +
                           "Integrated Security=True;" +
                           "User Instance=True;";

However, when I transfer the webservice files to the VPC for TFS, I get a severe exception. After some search on the net I found that it is probably the attaching of the CodeMetricsDB.mdf file that makes it all go to hell.

Mr. König helped me out a bit by telling me that I shouldn't use SQLEXPRESS but instead a "real" SQL server, and attaching the database by using some database tool. The problem is that I can't find any tool (or other way to do it) on the VPC. And even if I use the Server Explorer inside Visual Studio 2008, when I try to add a new connection, all I can choose as server is ORCASBETA2_TFSV\SQLEXPRESS. Why? Shouldn't I be able to choose some "non-Express-SQL-server"? I'm really confused.

So I'm stuck again, with something working on XP but not on the VPC. And I don't know if the Integrated Security + User Instance in the connection string is a wise thing to do. Perhaps I should use SQL authentication instead. But then again, I need to find some way to add users to the SQL server databases (which I don't know how to do in the VPC).

One positive thing is that I at least know how I should read information from the database usin C# (same connection string [conn] as above - it works on my local computer but not on the VPC):

 1     SqlConnection conn = new SqlConnection(connstring);
 2 
 3         try
 4         {
 5             conn.Open();
 6         }
 7         catch (Exception e)
 8         {
 9             return e.ToString();
10         }
11 
12         // try to read data from the DB
13         try
14         {
15             SqlDataReader myReader = null;
16             SqlCommand myCommand = new SqlCommand("select * from SavedEntries", conn);
17             myReader = myCommand.ExecuteReader();
18             string result = "Result: ";
19             while (myReader.Read())
20             {
21                 result = result + myReader["ProjectName"].ToString() + "....";
22             }
23             return result;
24         }
25         catch (Exception e)
26         {
27             return e.ToString();
28         }
29 
30         conn.Close();

Lines 15-17 performs the reading operation. Everytime myReader.Read() is called, it consumes one row of the results returned from the SQL database. (In the above examples, it returns a string containing all ProjectNames stord in the SavedEntries table, separated by four dots).

I'll have to figure out how to solve this XP <--> VPC problem. As I've tried so far, developing on the VPC instead doesn't seem to help me at all. I'm pretty sure that it all will work fine if I could figure out a way to (1) attach the database to the SQL Server in the VPC and (2) create a correct connection string for the scenarioa in (1).

Finally I've managed to get the webservice work on the TFS VPC. What did I do? There are some further things you have to have in mind when deploying on the VPC (when developed on another machine):

• Make sure that the right version of ASP.NET is used (should be 2.0.50727). This is easily changed through the IIS Manager by right-clicking on your website --> properties --> ASP.NET tab.
• You have to change some parts of the XML schema for your client. This is because it uses Windows authentication, and the <identity> entry must be adjusted to the user logged in on the VPC (in this case TFSSETUP). So make sure your <client> ... </client> part in [clientfilename].exe.config looks like this:

1         <client>
2             <endpoint address="http://localhost:38970/Service.svc"
3                 binding="wsHttpBinding" bindingConfiguration="WSHttpBinding_IService"
4                 contract="ServiceReference.IService" name="WSHttpBinding_IService">
5               <identity>
6                     <userPrincipalName value="ORCASBETA2_TFSV\TFSSETUP" />
7                 </identity>
8             </endpoint>
9         </client>

The thing you need to change is on line 6 (userPrincipalName). Also make sure that the endpoint (line 2) is linked correctly (see previous post).

Phew! Now I just have to do some fine-tuning to adjust it to our code metric model. And figure out how to make TFS recognize this webservice exists...(anyone?)

Now, there are obviously more things to do. First of all I need a client which sends the code metrics data to the web service (I did implement a simple client, but Magnus is taking a deeper look into making a client via a package for VS 2008). Secondly, the web service needs a method that receives these code metric data and finally forwards them into the Data Tier on TFS. I know how to construct a method that receives the data. Now I need to concentrate on two primary things regarding the web service:

• How does TFS register that my web service exists? (I think I’ve read about that somewhere but forgot it by now… typical!)
• How does the web service push the data into our operational store?
• Furthermore, after these two things have been dealt with, I need to find out:
• Where and how do we implement the operational store? (Yeah I know, in the SQL server in TFS – but I need more details! Preferably a guide or walkthrough)
• …and then all the things concerning the adapter, the XML schema and those things in that area.

• Learn WCF
• Build a web service
• Make XML schema for the adapter
• Create Adapter
• Create operational store
• Write some parts of the master thesis (theoretical)

It's been a while since I wrote something here, so I think it's time I'll give you all an update on what's going on in the project.

On monday this week we had a Live Meeting with Mr. König, where we showed this presentation with our findings and questions. The black hole as we saw it then was how to implement the operational store where we should store our data in the TFS server.

The answer to that question were pretty straightforward. We have to create a new database in the same SQL-Server. Then we have to build our own Web service layer on TFS. Finally, we have to implement a client which communicates with the web service. This client should send the code metrics down to our operational store (database). The webservice we implement should then be added to the IIS on the TFS server. As an endpoint to which the client can communicate, we need to provide an URL for the service.

The Team Foundation Object Model that I've been wondering about will apparently be created when you supply the URL to the client. I didn't really understand how that works, but I think It'll be clear as soon I start experimenting with it.

We should have our emphasis on the Adapter + operational store + the report, and not spend too much time constructing the web service.

One thing to think about is how to map our dimensions we'll create to those that already exist in the data warehouse in TFS.

We still need to put our emphasis on the server part of the project, not the client. Next week we will meet with an American gentleman who has been responsible for the (now existing) Code Metrics in Visual Studio 2008. Depending on the outcome of that meeting, we'll see what twist & turns the project will take on the client side. Some suggestions were uttered during the meeting; you could for example see if it is possible to extend the Code Analysis engine to get our code metrics. Or, we could use some kind of external open source tools that performs the code analysis in Visual Studio.

Regarding the reports, we should use MDX and the Report Designer to create them.

So Magnus and I have splitted up the work between us. I will take care of:

• Figuring out how to create the Operational Store
• Create a Web Service
• Create an Adapter
• Create a XML schema for the Adapter

Magnus will take care of:

• Creating the report (MDX + Report Designer)
• Take a look at the Dashboard-part; how to implement that in the project portal
• Figuring out how to use the TFC API to create our client

As for the master thesis part, work is not going so well. We still haven't got a project context to work with (delimitations on project type, role, size of project etc) so we can't do any empirical research. We're at a complete halt here, and it is very concerning! Time is running out and I'm really worried if we'll make it on time. To add up to all this we still haven't got the books we need to do a good job. None of these things are at our table, so I really really do hope that it gets sorted out soon, preferrably already yesterday.

Anyway, my TODO-list:

• Learn WCF
• Build a web service
• Make XML schema for the adapter
• Create Adapter
• Create operational store
• Write some parts of the master thesis (theoretical)

• Common Structures
• Work Item Tracking
• Source Control
• Team Foundation Build
• Team Test

• void Initialize(IDataStore ds)
  Initializes the adapter, and holds a reference to the IDataStore object (keeps the adress of the TFS server and the tools the adapter will work with).
  
• SchemaChangeResult MakeSchemaChanges()
  Provides means by which an adapter can indicate whether it will change a schema on this run.
  
• DataChangesResult MakeDataChanges()
  Does the primary work of the adapter. Pulls data from the operational store and writes to the warehouse using the object model.
  
• void RequestStop()
  Stops the adapter in an orderly fashion in the event the service is taken down.

• Correctability is quantified by using the metrics Fault counts and Effort
• Testability is quantified by using the metrics Degree of testing and Effort
• Expandability is quantified by using the metrics Change counts and Effort

• functionality
• reliability
• efficiency
• usability
• maintainability
• portability