none
Calling Stored Procedure with C# and Entity Framework RRS feed

  • Question

  • I am trying to run a simple stored procedure using a c# application I am writing that is using Entity Framework. Here is my stored procedure:

    ALTER PROCEDURE [dbo].[spupdTblAlertBySiteAlert]
    @sitealert_fk int,
    @op nvarchar(50) OUTPUT
    AS

    SET NOCOUNT ON

    BEGIN TRY

    UPDATE tblALERT SET STATUS = '2'
    WHERE SITEALERT_FK = @sitealert_fk
     AND STATUS = '1'
    SELECT @op = 'Test'
    END TRY

    BEGIN CATCH
    SELECT ERROR_MESSAGE() AS ErrorMessage;
    SELECT @op = 'TEST2'
    END CATCH

    Here is my code that is getting rejected:

              

    static void Main(string[] args)
            {

               var _db = new ConsumptionEntities();

       ObjectParameter stat = new ObjectParameter("stat", typeof(string));

      _db.spupdTblAlertBySiteAlert(1,stat).First();

            }

    Whenever I run this I get an error on the following generated EF code:

    public virtual ObjectResult<string> spupdTblAlertBySiteAlert(Nullable<int> sitealert_fk, ObjectParameter op)
            {
                var sitealert_fkParameter = sitealert_fk.HasValue ?
                    new ObjectParameter("sitealert_fk", sitealert_fk) :
                    new ObjectParameter("sitealert_fk", typeof(int));
        
                return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<string>("spupdTblAlertBySiteAlert", sitealert_fkParameter, op);
            }


    System.Data.EntityCommandExecutionException was unhandled
      HResult=-2146232004
      Message=An error occurred while executing the command definition. See the inner exception for details.
      Source=System.Data.Entity
      StackTrace:
           at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
           at System.Data.Objects.ObjectContext.CreateFunctionObjectResult[TElement](EntityCommand entityCommand, ReadOnlyMetadataCollection`1 entitySets, EdmType[] edmTypes, MergeOption mergeOption)
           at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, MergeOption mergeOption, ObjectParameter[] parameters)
           at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, ObjectParameter[] parameters)
           at Test_Code.ConsumptionEntities.spupdTblAlertBySiteAlert(Nullable`1 sitealert_fk, ObjectParameter op) in c:\VS_Testproject\Windows_Services\Test_Code\Test_Code\Model1.Context.cs:line 60
           at Test_Code.Program.Main(String[] args) in c:\VS_Testproject\Windows_Services\Test_Code\Test_Code\Program.cs:line 60
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException: System.Data.SqlClient.SqlException
           HResult=-2146232060
           Message=Procedure or function 'spupdTblAlertBySiteAlert' expects parameter '@op', which was not supplied.
           Source=.Net SqlClient Data Provider
           ErrorCode=-2146232060
           Class=16
           LineNumber=0
           Number=201
           Procedure=spupdTblAlertBySiteAlert
           Server=NMILLER-LT\nmiller
           State=4
           StackTrace:
                at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
                at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
                at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
                at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
                at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
                at System.Data.SqlClient.SqlDataReader.get_MetaData()
                at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
                at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite)
                at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite)
                at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
                at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
                at System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior)
                at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
                at System.Data.EntityClient.EntityCommandDefinition.ExecuteStoreCommands(EntityCommand entityCommand, CommandBehavior behavior)
           InnerException: 

    I have no idea what is going on with this. Any help would be appreciated. 

    • Moved by CoolDadTx Thursday, December 26, 2013 4:18 PM EF related
    Wednesday, December 18, 2013 7:36 PM

Answers

  • Hi Natron,

    My fault, sir. What you should do is to mark sure the name matches the ObjectParameter name and store procedure parameter name. If you have any problem to fix it, please share a project and I will fix for you.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, December 25, 2013 2:45 AM
    Moderator
  • I believe I found the answer. You have to import the function in visual studio and make the output parameter none even though it returns a nvarchar. After I made that change it seems to work now. Thanks for all your help everyone. 
    Thursday, December 26, 2013 3:06 PM

All replies

  • Hi Natron,

    The error message shows that you forgot to add parameter “@OP”. “Procedure or function 'spupdTblAlertBySiteAlert' expects parameter '@op', which was not supplied.”

    Please try to add it to fix this issue.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, December 19, 2013 7:12 AM
    Moderator
  • You can run sql profiler and see if your stored procedure is getting called and check what parm is being passed, you can copy it from the profile trace and execute the same in new sql query window. Check if you are getting result as expected.

    And also check in your code why there are two parm it is showing

     new ObjectParameter("sitealert_fk", sitealert_fk) :
                    new ObjectParameter("sitealert_fk", typeof(int));

    You can try restarting yur visual studio and then run it again.



    Mark ANSWER if this reply resolves your query, If helpful then VOTE HELPFUL
    INSQLSERVER.COM Mohammad Nizamuddin

    Thursday, December 19, 2013 7:23 AM
  • I am running SQL Express 2008 R2 so I do not have SQL Profiler installed. I just cannot figure this out. I have the OP parameter setup in my code above. It is an output parameter to pass and error message if something does not work. If you see something in my code above that is not write could you write what exactly is wrong? 
    Friday, December 20, 2013 8:33 PM
  • Hi Natron,

    You should create a parameter named “op”. Please change you code as following.

    ObjectParameter stat = new ObjectParameter("op", typeof(string));

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, December 24, 2013 5:27 AM
    Moderator
  • I added ObjectParameter stat = new ObjectParameter("op"typeof(string)); but I still get an error. I don't understand I have one input parameter and one output parameter but I get an error saying I do not have enough columns for the query. 

    System.Data.EntityCommandExecutionException was unhandled
      HResult=-2146232004
      Message=The data reader returned by the store data provider does not have enough columns for the query requested.
      Source=System.Data.Entity
      StackTrace:
           at System.Data.EntityClient.EntityCommandDefinition.ConstantColumnMapGenerator.System.Data.EntityClient.EntityCommandDefinition.IColumnMapGenerator.CreateColumnMap(DbDataReader reader)
           at System.Data.Objects.ObjectContext.MaterializedDataRecord[TElement](EntityCommand entityCommand, DbDataReader storeReader, Int32 resultSetIndex, ReadOnlyMetadataCollection`1 entitySets, EdmType[] edmTypes, MergeOption mergeOption)
           at System.Data.Objects.ObjectContext.CreateFunctionObjectResult[TElement](EntityCommand entityCommand, ReadOnlyMetadataCollection`1 entitySets, EdmType[] edmTypes, MergeOption mergeOption)
           at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, MergeOption mergeOption, ObjectParameter[] parameters)
           at System.Data.Objects.ObjectContext.ExecuteFunction[TElement](String functionName, ObjectParameter[] parameters)
           at Test_Code.ConsumptionEntities.spupdTblAlertBySiteAlert(Nullable`1 sitealert_fk, ObjectParameter op) in c:\VS_Testproject\Windows_Services\Test_Code\Test_Code\Model1.Context.cs:line 60
           at Test_Code.Program.Main(String[] args) in c:\VS_Testproject\Windows_Services\Test_Code\Test_Code\Program.cs:line 63
           at System.AppDomain._nExecuteAssembly(RuntimeAssembly assembly, String[] args)
           at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
           at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
           at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
           at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state, Boolean preserveSyncCtx)
           at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
           at System.Threading.ThreadHelper.ThreadStart()
      InnerException: 


    Wednesday, December 25, 2013 2:36 AM
  • Hi Natron,

    My fault, sir. What you should do is to mark sure the name matches the ObjectParameter name and store procedure parameter name. If you have any problem to fix it, please share a project and I will fix for you.

    Regards,


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Wednesday, December 25, 2013 2:45 AM
    Moderator
  • I believe I found the answer. You have to import the function in visual studio and make the output parameter none even though it returns a nvarchar. After I made that change it seems to work now. Thanks for all your help everyone. 
    Thursday, December 26, 2013 3:06 PM