Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Stored Procedure Not working from c# code

I'm trying to call a stored procedure within C#.

using (SqlConnection connection = new SqlConnection(connectionString))
   using (SqlCommand command = connection.CreateCommand())
    {
      command.CommandType = CommandType.StoredProcedure;
      command.CommandText = "updateData";
      command.Parameters.Add(new SqlParameter("@inrego", rego));
      command.Parameters.Add(new SqlParameter("@inOprt", oprt));
      command.Parameters.Add(new SqlParameter("@inService", service));

      connection.Open();

      int update = command.ExecuteNonQuery();

      Console.WriteLine(update);

      connection.Close();
    }

update shows 1 on console, but the database still isn't updated.

This is the stored procedure

CREATE PROCEDURE [dbo].updateData
@inrego varchar(5),
@inOprt char(3),
@inService as varchar(50)

AS
delete from buses where rego = @inrego;
insert into buses (rego, operator,service) values(@inrego, @inOprt, @inService);
RETURN 0

Running the stored procedure manually works, aka

USE [C:\USERS\---\DOCUMENTS\VISUAL STUDIO 2013\PROJECTS\---\TEST.DB.MDF]
GO

DECLARE @return_value Int

EXEC    @return_value = [dbo].[updateData]
        @inrego = N'1',
        @inOprt = N'2',
        @inService = N'3'

SELECT  @return_value as 'Return Value'

GO

works, and successfully updates the database, but the code form C# doesn't.

like image 864
matthew5025 Avatar asked Sep 30 '22 23:09

matthew5025


1 Answers

I have been unable to reproduce the issue on clean local database(MS SQL EXPRESS 2013, Win 8.1, .NET 4.5):

CREATE TABLE [dbo].buses
(
    [rego] varchar(5) NOT NULL PRIMARY KEY,
    [operator] char(3),
    [service] varchar(50)
)
static void UpdateOrInsertBuses(String rego, String oprt, String service)
{
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        using (SqlCommand command = connection.CreateCommand())
        {
            command.CommandType = CommandType.StoredProcedure;
            command.CommandText = "updateData";
            command.Parameters.Add(new SqlParameter("@inrego", rego));
            command.Parameters.Add(new SqlParameter("@inOprt", oprt));
            command.Parameters.Add(new SqlParameter("@inService", service));

            connection.Open();
            try
            {
                int update = command.ExecuteNonQuery();

                Console.WriteLine(update);
            }
            catch (Exception exc)
            {
                Console.WriteLine(exc.Message);
            }
            finally
            {
                connection.Close();
            }
        }
    }
}

// ...
// Add data
UpdateOrInsertBuses("11", "12", "13");
UpdateOrInsertBuses("21", "22", "23");

// Update added
UpdateOrInsertBuses("21", "22", "Changed for sure");

So, it is some issue that does not relate to your current code. As it has been suggested by @ Gordon Linoff it is either a permission issue, or some trigger that meddles into the updates, or the database for some reasons reverts or ignores any changes.

like image 184
Eugene Podskal Avatar answered Oct 03 '22 08:10

Eugene Podskal