Manipulate an object in the DB by calling a stored procedure

Question

0.00/5 (No votes)

See more:

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 10-Nov-20 11:10am

xhon

Updated 10-Nov-20 20:30pm

v5

Add a Solution

Comments

PIEBALDconsult 10-Nov-20 22:20pm

Never delete.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2020-11-10T11:28:00

Solution 1

It's going to depend on what the SP actually looks like: it needs to be something like this:

SQL

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

Posted 10-Nov-20 11:28am

OriginalGriff

Updated 10-Nov-20 11:29am

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!