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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With