locked
Execute a Parameterized Stored Procedure Using EntityCommand RRS feed

  • Question

  • using (EntityConnection ef_connect = new EntityConnection("myConnectionString"))
    {
       ef_connect.Open();

       try
       {

             using (EnityCommand myCmd = ef_cconect.CreateCommand())
            {
                    myCmd.CommandText = "MyEntities.ImportedStoredProcFunc";
                    myCmd.CommandType = CommandType.StoredProcedure;
     
                   // have automated packaging of objects
                   // Map<FieldName, FieldValue>
                   Dictionary<string. string> map = UtilityFunc(userObject);

                   foreach (KeyValuePair<string, string> kitu in map)
                  {
                         EntityParameter param = new EntityParameter();
                         param.ParameterName = kitu.Key.ToString();
                         param.Value = kitu.Value.ToString();
                         myCmd.Parameters.Add(param);
                  }
     
                 using (EntityReader lodja = myCmd.ExecuteReader(CommandBehavior.SequentialAccess))
                 {
                         // lodja crunched.-
                 }
           }

       }
       catch (EntityException eex)
       {
              // InnerException = "System.Data.SqlClient.SqlException"
              // InnerException.Message = "Procedure or function 'StoredProc' expects parameter   '@theID', which was not supplied."
       }
    }

    My stored procedure is defined as:

    ALTER PROCEDURE dbo.StoredProc
     @theID varchar(32) OUTPUT,
     @mySecondParam varchar(64) OUTPUT,
     @myThirdParam int,
     @myFourthParam int
    AS
     --blah, blah
    RETURN

     

    I suspect to have missed something; advise.

    Iz

    Wednesday, December 14, 2011 8:25 AM

Answers

  • You are using an output parameter and as such need to tell it to be so.  param.Direction = ParameterDirection.Output

    EntityParameter.Direction Property

    • Marked as answer by Alan_chen Monday, December 26, 2011 2:45 AM
    Wednesday, December 21, 2011 1:35 PM

All replies

  • Hi Iz,

    What's the error information, would you please share with us?

    Have a nice day.


    Alan Chen[MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Monday, December 19, 2011 9:06 AM
  • Hi Alan,

    the error is the exception caught as described below:

     

     catch (EntityException eex)
    {
          // InnerException = "System.Data.SqlClient.SqlException"
          // InnerException.Message = "Procedure or function 'StoredProc' expects parameter '@theID', which was not supplied."
    }

     

     

    Iz

     

    • Proposed as answer by buthfa Wednesday, December 21, 2011 1:27 PM
    • Unproposed as answer by buthfa Wednesday, December 21, 2011 1:27 PM
    Wednesday, December 21, 2011 9:51 AM
  • You are using an output parameter and as such need to tell it to be so.  param.Direction = ParameterDirection.Output

    EntityParameter.Direction Property

    • Marked as answer by Alan_chen Monday, December 26, 2011 2:45 AM
    Wednesday, December 21, 2011 1:35 PM