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