Could not find stored procedure when calling from C# web site

I have a database NewYekAye in SQL Server with some tables and stored procedures. One of the tables is TbSoore and one of the stored procedures is spGetSoore.

I created spGetSoore with this code:

USE [NewYekAye]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE Procedure [dbo].[spGetSoore]  
    @IdSoore INT
AS  
BEGIN
    SELECT 
        IdSoore, NameSoore, 
        MakiMadani, TextMoghadame, TextHadis, TextMohtava, TextMoarefi 
    FROM 
        TbSoore 
    WHERE 
        (StutusSoore = 1) AND (IdSoore = @IdSoore)
END

And in C#, I use it with this code:

string strsql = $@"EXEC spGetSoore @IdSoore = @IdSoore;";

DataTable dt = new DataTable();

using (SqlConnection con = new SqlConnection(strcon))
{
    using (SqlCommand cmdSQL = new SqlCommand(strsql, con))
    {
        cmdSQL.CommandType = CommandType.StoredProcedure;

        cmdSQL.Parameters.Add(new SqlParameter ("@IdSoore", forwardedIdSoore));

        con.Open();
        dt.Load(cmdSQL.ExecuteReader());
    }
}

But when I run the site, I get this error:

System.Data.SqlClient.SqlException: could not find stored procedure ‘EXEC spGetSoore @IdSoore = @IdSoore’.

I checked my SQL connection in C# by using

SELECT IdSoore, NameSoore, MakiMadani, TextMoghadame, TextHadis, TextMohtava, TextMoarefi 
FROM TbSoore 
WHERE (StutusSoore = 1) AND (IdSoore = @IdSoore) 

query instead of Exec spGetSoore and it worked.

Then I run Exec spGetSoore in SQL Server, and that worked, too.

Of course in SQL Server, when I wrote EXEC spGetSoore @IdSoore = 1;, showed red underline for spGetSoore with this tooltip:

Could not find stored procedure ‘spGetSoore’.

but it worked!

Like this:

but I don’t know why I got this error and what I can fix it.

Please help me.

>Solution :

When you use CommandType.StoredProcedure the client library assumes that the CommandText provided is the name of the stored procedure to call, not a full TSQL instruction. Look at your error and you’ll see that it’s searching for a proc called "EXEC spGetSoore @IdSoore = @IdSoore" instead of "spGetSoore".

Either remove the CommandType assignment or fix the CommandText to "spGetSoore".

In SSMS it works because it submits batches of SQL, not sproc calls. The batch is the one that calls the procedure instead.

Of course in sql server when I wrote EXEC spGetSoore @IdSoore = 1; ,showed red underline for spGetSoore with this tooltip:

The red underline comes from an outdated cache in SSMS, it happens when you just created the procedure, but Intelisense didn’t got it yet. You can force a cache refresh with ctrl + shift+ R.