Using stored procedure output parameters in C#

Question

I am having a problem returning an output parameter from a Sql Server stored procedure into a C# variable. I have read the other posts concerning this, not only here but on other sites, and I cannot get it to work. Here is what I currently have. Currently I am just trying to print the value that comes back. The following code returns a null value. What I an trying to return is the primary key. I have tried using @@IDENTITY and SCOPE_INDENTITY() (i.e. SET @NewId = SCOPE_IDENTITY()).

Stored Procedure:

CREATE PROCEDURE usp_InsertContract     @ContractNumber varchar(7),      @NewId int OUTPUT AS BEGIN      INSERT into [dbo].[Contracts] (ContractNumber)         VALUES (@ContractNumber)      Select @NewId = Id From [dbo].[Contracts] where ContractNumber = @ContractNumber END 

Opening the database:

pvConnectionString = "Server = Desktop-PC\\SQLEXPRESS; Database = PVDatabase; User ID = sa;     PASSWORD = *******; Trusted_Connection = True;";  try {     pvConnection = new SqlConnection(pvConnectionString);     pvConnection.Open(); } catch (Exception e) {     databaseError = true; } 

Executing the command:

pvCommand = new SqlCommand("usp_InsertContract", pvConnection);  pvCommand.Transaction = pvTransaction; pvCommand.CommandType = CommandType.StoredProcedure;      pvCommand.Parameters.Clear(); pvCommand.Parameters.Add(new SqlParameter("@ContractNumber", contractNumber));  SqlParameter pvNewId = new SqlParameter(); pvNewId.ParameterName = "@NewId"; pvNewId.DbType = DbType.Int32; pvNewId.Direction = ParameterDirection.Output; pvCommand.Parameters.Add(pvNewId);  try {     sqlRows = pvCommand.ExecuteNonQuery();      if (sqlRows > 0)         Debug.Print("New Id Inserted =  ",              pvCommand.Parameters["@NewId"].Value.ToString());      }     catch (Exception e)     {         Debug.Print("Insert Exception Type: {0}", e.GetType());         Debug.Print("  Message: {0}", e.Message);     } } 

Answer 1

I slightly modified your stored procedure (to use SCOPE_IDENTITY) and it looks like this:

CREATE PROCEDURE usp_InsertContract     @ContractNumber varchar(7),     @NewId int OUTPUT AS BEGIN     INSERT INTO [dbo].[Contracts] (ContractNumber)     VALUES (@ContractNumber)      SELECT @NewId = SCOPE_IDENTITY() END 

I tried this and it works just fine (with that modified stored procedure):

// define connection and command, in using blocks to ensure disposal using(SqlConnection conn = new SqlConnection(pvConnectionString )) using(SqlCommand cmd = new SqlCommand("dbo.usp_InsertContract", conn)) {     cmd.CommandType = CommandType.StoredProcedure;              // set up the parameters     cmd.Parameters.Add("@ContractNumber", SqlDbType.VarChar, 7);     cmd.Parameters.Add("@NewId", SqlDbType.Int).Direction = ParameterDirection.Output;      // set parameter values     cmd.Parameters["@ContractNumber"].Value = contractNumber;      // open connection and execute stored procedure     conn.Open();     cmd.ExecuteNonQuery();      // read output value from @NewId     int contractID = Convert.ToInt32(cmd.Parameters["@NewId"].Value);     conn.Close(); } 

Does this work in your environment, too? I can't say why your original code won't work - but when I do this here, VS2010 and SQL Server 2008 R2, it just works flawlessly....

If you don't get back a value - then I suspect your table Contracts might not really have a column with the IDENTITY property on it.