calling a stored procedure

Hi, i've had this query method:

34 public void AddDagVerslagCategorie(int logID, HistoriekDetail historiekDetail)
35 {
36 SqlConnection oConn =new SqlConnection(_connectionString);
37 string strSql ="Insert into LogDetail (LogID, CategorieID, Inhoud)";
38 strSql +="values(@.logID, @.categorieID, @.inhoud)";
39 SqlCommand oCmd =new SqlCommand(strSql, oConn);
40 oCmd.Parameters.Add(new SqlParameter("@.logID", SqlDbType.Int)).Value = logID;
41 oCmd.Parameters.Add(new SqlParameter("@.categorieID", SqlDbType.Int)).Value = historiekDetail.CategorieID;
42 oCmd.Parameters.Add(new SqlParameter("@.inhoud", SqlDbType.VarChar, 100)).Value = historiekDetail.Inhoud;
43
44 try
45 {
46 oConn.Open();
47 int rowsAffected = oCmd.ExecuteNonQuery();
48 if (rowsAffected == 0)throw new ApplicationException("Fout toevoegen historiek detail");
49 oCmd.CommandText ="select @.@.IDENTITY";
50 oCmd.Parameters.Clear();
51 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar();
52 }
53 catch (Exception ex)
54 {
55 throw new ApplicationException("Fout toevoegen historiek detail: " + ex.Message);
56 }
57 finally
58 {
59 if (oConn.State == ConnectionState.Open) oConn.Close();
60 }
61 }

which i've converted to a stored procedure:

 
1ALTER PROCEDURE [dbo].[insert_DagVerslagDetail]2-- Add the parametersfor the stored procedure here3@.dagverslagdetailIDint,4 @.logIDint,5@.categorieIDint,6 @.inhoud varchar(100)7AS8BEGIN9-- SET NOCOUNT ON added to prevent extra result sets from10-- interfering with SELECT statements.11SET NOCOUNT ON;12 SET @.dagverslagdetailID = SCOPE_IDENTITY()1314 -- Insert statementsfor procedure here15BEGIN TRANSACTION16 INSERT LogDetail (LogID, CategorieID, Inhoud)17 VALUES(@.logID, @.categorieID, @.inhoud)18 COMMIT TRANSACTION19END
 

Now i would like to call that stored procedure in my previous method, so i've changed it to this:

1public void AddDagVerslagCategorie(int logID, HistoriekDetail historiekDetail)2 {3 SqlConnection oConn =new SqlConnection(_connectionString);4string strSql ="insert_DagVerslagDetail";5 strSql +="values(@.logID, @.categorieID, @.inhoud)";6 SqlCommand oCmd =new SqlCommand(strSql, oConn);7 oCmd.CommandType = CommandType.StoredProcedure;8 oCmd.Parameters.Add(new SqlParameter("@.logID", SqlDbType.Int)).Value = logID;9 oCmd.Parameters.Add(new SqlParameter("@.categorieID", SqlDbType.Int)).Value = historiekDetail.CategorieID;10 oCmd.Parameters.Add(new SqlParameter("@.inhoud", SqlDbType.VarChar, 100)).Value = historiekDetail.Inhoud;1112try13 {14 oConn.Open();15int rowsAffected = oCmd.ExecuteNonQuery();16if (rowsAffected == 0)throw new ApplicationException("Fout toevoegen historiek detail");17 oCmd.CommandText ="select @.@.IDENTITY";18 oCmd.Parameters.Clear();19 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar();20 }21catch (Exception ex)22 {23throw new ApplicationException("Fout toevoegen historiek detail: " + ex.Message);24 }25finally26 {27if (oConn.State == ConnectionState.Open) oConn.Close();28 }29 }

Do i still need the lines17 oCmd.CommandText ="select @.@.IDENTITY";

19 historiekDetail.HistoriekDetailID = (int)(decimal)oCmd.ExecuteScalar();

Because i've declared the identity in my stored procedure

Modify your procedure to return you identity column as output parameter, and by the way you do not have to use transaction around single insert statement

or do:

ALTER PROCEDURE [dbo].[insert_DagVerslagDetail]
2 -- Add the parametersfor the stored procedure here
3 @.dagverslagdetailIDint,
4 @.logIDint,
5 @.categorieIDint,
6 @.inhoud varchar(100)
7 AS
8 BEGIN
9 -- SET NOCOUNT ON added to prevent extra result sets from
10 -- interfering with SELECT statements.
11 SET NOCOUNT ON;
12 SET @.dagverslagdetailID = SCOPE_IDENTITY()
13
14 -- Insert statementsfor procedure here

16 INSERT LogDetail (LogID, CategorieID, Inhoud)
17 VALUES(@.logID, @.categorieID, @.inhoud)
select SCOPE_IDENTITY()
19 END

and get your identity at one call to stored procedure