Stored Procedure done!
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).
http://weblogs.asp.net/fbouma/archive/2003/11/18/38178.aspx