Reputation: 250
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.
Upvotes: 5
Views: 4040
Reputation: 10401
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.
Upvotes: 4