Why Does this C# code run the Stored Procedure twice

[Perry Pierce]

I have this stored procedure on my SQL Server

USE [PSD]
GO
/****** Object: StoredProcedure [dbo].[ocso_GetNextDTEventNumber] Script Date: 1/24/2017 7:46:12 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[ocso_GetNextDTEventNumber]
@EventNumber varchar(15) OUTPUT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--increment last number by 1
UPDATE LU_EVENT_NUMBERS SET evn_last_number = evn_last_number + 1 where evn_year = Year(GetDate())
--return event number to caller

--Get the next event number for all incidents
SELECT @EventNumber = evn_comp_formatted_next_num from LU_EVENT_NUMBERS where evn_year = Year(GetDate())

END

If I execute the Stored Procedure on the SQL Server it works as expected.

Now I call this stored procedure for a C# WCF application from the Service side using this C# code:

SqlConnection _con = new SqlConnection();
_con.ConnectionString =
"Data Source=ops-devsql;" +
"Initial Catalog=PSD;" +
"User id=PSD_Dev;" +
"Password=PSD_Dev;";
_con.Open();

string results;
DateTime date = Convert.ToDateTime("2017-10-30");

using (SqlCommand _cmd = new SqlCommand("ocso_GetNextDTEventNumber", _con))
{
_cmd.CommandType = CommandType.StoredProcedure;
_cmd.Parameters.Add("@EventNumber", SqlDbType.VarChar, 15)
.Direction = ParameterDirection.Output;
_cmd.ExecuteNonQuery();
results = _cmd.Parameters["@EventNumber"].Value.ToString();

Console.WriteLine("I called a stored procedure from the client and got an event number and it is : {0}", results);
Console.ReadLine();
channel.Close();

The code compiles and runs but the stored procedure is executed twice. I know it is running twice because when I check the number returned it is 1 higher then expected. For example I expected to see an out put of number 20 but I get number 21.

I have set a break point and step through the code and do not see any problems. Any ideas what me be happening here?

Continue reading...