• Fausto
• DJ Ghost
• Brennan Heart
• Showtek ft. DV8
• Headhunterz
• Zany & The Prophet
• Technoboy
• Neophyte

• MC: Ruffian

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?)

Minns en tjej jag slagga med, hon hade satt mobilen på väckning, men utan ljud, så den vibrerade bara, jag drömde att ett bi surra i örat på mig, så jag försökta slå bort det, men råkade slå tjejen i ansiktet.

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)