Click here to Skip to main content
14,732,909 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
I created a stored procedure to Delete an object from the table Movie in my db and I called it from a DataAccessLayer method of my application (C#).

I passed the stored procedure as a parameter of the SqlCommand object together with the SqlConnection object, then I added the parameters of the stored procedure by using the method Parameters , which is called by the SqlCommand object.
The method doesn't work (the item has not been deleted); for it didn't throw any exception I suppose the issue is caused by a non-correspondence between the parameters in the stored procedure and those that I'm trying to add them here...
The stored procedure works fine.

What I have tried:

public bool Delete(Movie movie)  
        {
            try
            {
                bool result = false;
                string DeleteProcedure = "[dbo].nameOfTheProcedure";
                using (SqlConnection conn = DB.GetSqlConnection())
                {
                    using (SqlCommand cmd = new SqlCommand(DeleteProcedure, conn))
                    {
                        cmd.CommandType = CommandType.StoredProcedure;
                        cmd.Parameters.Add("@MovieId", SqlDbType.NVarChar).Value = movie.Id;
                        cmd.Parameters.Add("@Title", SqlDbType.NVarChar).Value = movie.Title;
                        
                        cmd.Parameters.Add("@Director", SqlDbType.NVarChar).Value = movie.Director;                       
                        conn.Open();
                        result = cmd.ExecuteNonQuery() == 1;
                    }
                }
                return result;
            }
            catch (SqlException ex)
            {
                Console.WriteLine(ex.Message);
                return false;
            }
        }
Posted
Updated 10-Nov-20 21:30pm
v5
Comments
PIEBALDconsult 10-Nov-20 22:20pm
   
Never delete.

1 solution

It's going to depend on what the SP actually looks like: it needs to be something like this:
CREATE PROCEDURE [dbo].[nameOfTheProcedure]
@MOVIEID NVARCHAR(10),
@TITLE NVARCHAR(MAX)  -- This should be irrelevent - the ID should be unique.
AS
   DELETE FROM [dbo].[YourDBTableName]
   WHERE Id = @MOVIEID
GO
   
v2
Comments
xhon 11-Nov-20 3:04am
   
my stored procedure works, it's just that the C# method returns false instead than true because the item has not been deleted (I can delete it by calling my stored procedure on my db manager application though)
OriginalGriff 11-Nov-20 3:33am
   
How do you know it works? How did you test it?
What is different about the way you tested it, compared to how you are calling it now?
xhon 11-Nov-20 4:15am
   
the stored procedure works when I call it from the Sql Server Manager, whereas the method in Visual Studio where I call the stored procedure doesn't return the expected result
OriginalGriff 11-Nov-20 4:21am
   
But when you call it from SSMS, you pass the parameters manually, and possibly differently.
So use the debugger and look at two things: Exactly what is in the parameters you pass, and exactly what does the ExecuteNonQuery call return?
How does that differ from the SSMS way?
xhon 11-Nov-20 5:34am
   
no, I don't pass the parameters manually, I pass them directly from the C# method. I checked them by storing the results of the "cmd.Parameters.Add" statements on variables (one for each parameter added) and they are correct. I assigned the result of "cmd.ExecuteNonQuery == 1" to the variable "result" and the method returns it and it's false (so the result of ExecuteNonQuery==1 is false) instead than true
OriginalGriff 11-Nov-20 5:59am
   
So when you did test it with SSMS, how did you pass the parameters, if not manually? When you right click an SP and select "Execute", a dialog pops up to enter parameter values. What - exactly - did you enter there?

And what - exactly - did ExecuteNonQuery return? Not "what did it return that probably wasn't 1?" but the actual value. Rule one of debugging: "gather information, not guesses". :laugh:
xhon 11-Nov-20 6:32am
   
ExecuteNonQuery returns -1.
UPDATE: I inserted new items to the db from C# and I succeeded in deleted them by using this method (ExecuteNonQuery stills returns -1 and ExecuteNonQuery() == 1 still returns false) BUT the deleting operation fails when I tried to delete the items already in the db (i.e. those rows created within Sql Management Studio, not added from my application)... I guess the problem could be in a non-correspondence between the properties of the columns in the DB and the attributes of the corresponding entities, but at this point I wonder why ExecuteNonQuery return -1 (shouldn't it be 1?) even when the deleting operation is successful.
OriginalGriff 11-Nov-20 7:01am
   
So you need to look at the SP and see exactly what it does.
I can't do that for you!
OriginalGriff 11-Nov-20 7:14am
   
Oh - and a silly thought.
You have checked that SSMS and your C# app are definitely using the same DB, haven't you?
xhon 11-Nov-20 7:12am
   
I solved the problem!:) some items could not be deleted because of referential integrity, I edited my db and now everything works fine (but I still don't understand why ExecuteNonQuery returns -1)
OriginalGriff 11-Nov-20 7:15am
   
As I said: look to your SP - we can't see it or how it works!
xhon 11-Nov-20 7:50am
   
I solved it! It depended on having set NOCOUNT ON in my SP. Now I commented that statement and the ExecuteNonQuery returns the expected number. Thanks for the tips
OriginalGriff 11-Nov-20 8:21am
   
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900