MVC3 call SQL Server Stored Procedure

Question

This is something that I would expect to be simple but it is not working. I simply want to be able to create stored procedures in my SQL Server DB and call them from within my MVC3 web app. I have managed to get my SPs imported into a generated function but when I call this I get an error:

The data reader is incompatible with the specified 'dbname.school_accreditation'. A member of the type, 'school_accreditation_id', does not have a corresponding column in the data reader with the same name.

I have created a stored procedure within SQL Server.

USE [mydb]
GO
/****** Object:  StoredProcedure [dbo].[DeleteSchoolAccreditations]    Script Date: 02/07/2014 20:56:21 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DeleteSchoolAccreditations] 
@school_id int
AS

DELETE FROM [dbo].[school_accreditation]
 WHERE school_id = @school_id

The generated c# function to call the SP.

public virtual ObjectResult<school_accreditation> DeleteSchoolAccreditations(Nullable<int> school_id)
    {
        ((IObjectContextAdapter)this).ObjectContext.MetadataWorkspace.LoadFromAssembly(typeof(school_accreditation).Assembly);

        var school_idParameter = school_id.HasValue ?
            new ObjectParameter("school_id", school_id) :
            new ObjectParameter("school_id", typeof(int));

        return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<school_accreditation>("DeleteSchoolAccreditations", school_idParameter);
    }

Should I need to do anything to this generated method in order to get it to work. I am passing in an int to the method and it all looks okay when I debug.

Please any help would be greatly received.

Joe

Well, it would help if you understood which technology you were using. MVC doesn't have any functionality whatsoever for data access. You can use any data access technology you want with it. You appear to be using Entity Framework, which is NOT MVC. — Erik Funkenbusch
– Erik Funkenbusch, Commented Feb 7, 2014 at 21:15
That's a fair point. I should have been more explicit. Thanks — Yos
– Yos, Commented Feb 7, 2014 at 23:25

Erik Funkenbusch · Accepted Answer · 2014-02-07 21:21:41Z

1

Your stored preocedure does not return a school_accreditation entity. Since it's a delete, it only returns the number of rows affected. However, your stored preocedure ExecuteFucntion seems to want a return type of school_accreditation.

Obviously you did something wrong when you generated your sproc method. Did you, by any chance, alter the sproc after you generated it? If so, delete the method from your edmx and re-add it.

For something like this, it may just be simpler to do the call directly. Something like MyContext.Database.ExecuteSqlCommand("DeleteSchoolAccreditations", school_id);

answered Feb 7, 2014 at 21:21

Erik Funkenbusch

93.6k29 gold badges201 silver badges292 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Yos Over a year ago

That makes perfect sense. So it's either my stored procedure which is wrong (I assume you would have noticed this, therefore I can assume it's okay) or, like you said I made a mistake. Either way thank you soo much. I will work it out!

Collectives™ on Stack Overflow

MVC3 call SQL Server Stored Procedure

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related