When executing a command, parameters must be exclusively database parameters or values

c# entity-framework entity-framework-6 entity-framework-6.1 stored-procedures

Question

I am not able to retrieve the ouput value back. I get the following exception:

Execption Type: InvalidOperationException: When executing a command, parameters must be exclusively database parameters or values.

C# Code Method

public Student InsertNewStudentApplication(Student student)
{
    Student newStudent = null;
    SqlParameter returnParam = new SqlParameter()
    {
        ParameterName = "@newStudentID",
        SqlDbType = SqlDbType.Int,
        Value = 0,
        Direction = System.Data.ParameterDirection.Output
    };

    var result = m_context.Database.ExecuteSqlCommand("exec usp_MyProcedureName @newStudentID, @name, @schoolID, @localAdd, @localCity, @localState, @localZip, @summerAdd, @summerCity, @summerState, @summerZip, @phoneLocal, @phoneHome, @phoneCell, @email, @gradYear, @gradMajorMinor, @gradSchoolCollege, @TShirtSize, @transferStud, @prevExperience, @leadTransferStud, @fk_interviewID, @resumePath, @picturePath ",
    returnParam,
    new SqlParameter("@name", student.Name),
    new SqlParameter("@schoolID", student.schoolID),
    new SqlParameter("@localAdd", student.LocalAdd),
    new SqlParameter("@localCity", student.LocalCity),
    new SqlParameter("@localState", student.LocalState),
    new SqlParameter("@localZip", student.LocalZip),
    new SqlParameter("@summerAdd", student.SummerAdd),
    new SqlParameter("@summerCity", student.SummerCity),
    new SqlParameter("@summerState", student.SummerState),
    new SqlParameter("@summerZip", student.SummerZip),
    new SqlParameter("@phoneLocal", student.PhoneLocal),
    new SqlParameter("@phoneHome", student.PhoneHome),
    new SqlParameter("@phoneCell", student.PhoneCell),
    new SqlParameter("@email", student.Email),
    new SqlParameter("@gradYear", student.GradYear),
    new SqlParameter("@gradMajorMinor", student.GradMajorMinor),
    new SqlParameter("@gradSchoolCollege", student.GradSchoolCollege),
    new SqlParameter("@TShirtSize", student.TShirtSize),
    new SqlParameter("@transferStud", student.TransferStud),
    new SqlParameter("@prevExperience", student.PrevExperience),
    new SqlParameter("@leadTransferStud", student.LeadTransferStud),
    new SqlParameter("@fk_interviewID", SqlDbType.Int).Value =     student.InterviewID,
    new SqlParameter("@resumePath", student.ResumePath),
    new SqlParameter("@picturePath", student.PicturePath));

    var newStudentID = (int)returnParam.Value;
    if (newStudentID <= 0)
    {
        var tempStudent = new Student { ID = newStudentID };
        newStudent = this.FindByID(tempStudent);
    }

    return newStudent;
}

SQL Stored Procedure:

ALTER PROCEDURE [dbo].[usp_MyProcedureName]
    @newStudentID       int out,
    @name               varchar(255),
    @schoolID               varchar(255),
    @localAdd           varchar(255),
    @localCity          varchar(50),
    @localState         varchar(50),
    @localZip           varchar(50),
    @summerAdd          varchar(255),
    @summerCity         varchar(50),
    @summerState        varchar(50),
    @summerZip          varchar(50),
    @phoneLocal         varchar(50),
    @phoneHome          varchar(50),
    @phoneCell          varchar(50),
    @email              varchar(255),
    @gradYear           varchar(50),
    @gradMajorMinor     varchar(255),
    @gradSchoolCollege  varchar(255),
    @TShirtSize         varchar(50),
    @transferStud       varchar(50),
    @prevExperience     text,
    @leadTransferStud   varchar(50),
    @fk_interviewID     int,
    @resumePath         varchar(255),
    @picturePath        varchar(255)
AS

SET NOCOUNT OFF;

BEGIN
    INSERT INTO OApp_students
    (
        name,
        schoolID,
        localAdd,
        localCity,
        localState,
        localZip,
        summerAdd,
        summerCity,
        summerState,
        summerZip,
        phoneLocal,
        phoneHome,
        phoneCell,
        email,
        gradYear,
        gradMajorMinor,
        gradSchoolCollege,
        TShirtSize,
        transferStud,
        prevExperience,
        leadTransferStud,
        fk_interviewID,
        resumePath,
        picturePath 
    )
    VALUES
    (
        @name,
        @schoolID,
        @localAdd,
        @localCity,
        @localState,
        @localZip,
        @summerAdd,
        @summerCity,
        @summerState,
        @summerZip,
        @phoneLocal,
        @phoneHome,
        @phoneCell,
        @email,
        @gradYear,
        @gradMajorMinor,
        @gradSchoolCollege,
        @TShirtSize,
        @transferStud,
        @prevExperience,
        @leadTransferStud,
        @fk_interviewID,
        @resumePath,
        @picturePath
    )

    set @newStudentID = @@IDENTITY

END
1
1
7/3/2014 11:44:45 AM

Accepted Answer

The parameter @newStudentID should be an output parameter (Direction = ParameterDirection.Output). But a better pattern here is to conclude the sproc by

SELECT SCOPE_IDENTITY() as NewStudentID;

in stead of set @newStudentID = @@IDENTITY. And remove the @newStudentID parameter. The part as NewStudentID is optional. Now you can call the sproc by

var newStudentId = m_context.Database
                            .SqlQuery<int>("exec usp_MyProcedureName @name, ...)
                            .First();

By the way, are you aware of the possibility to map stored procedures directly to the insert, update, and delete actions of an entity?

1
7/1/2014 10:05:29 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow