How do you call a stored procedure in MVC3 C#

Question

I am looking for a way to call my stored procedure in my C# code in the controller. I already have everything that I need in the controller. I have the ID on the post of the edit, and what I need to do when submitted is call the stored procedure with the parameter id. In SQL you do this:

 EXEC [dbo].[AddGlassCutting] @AuditScheduleID = 1192

to test. This does not work in my code for the site. The database is done with code first approach.

Updated 1:33 Sept 14 2012

Added code from edit Post - this is where i need it too execute:

        [HttpPost]
    public ActionResult Edit(int id, AuditScheduleEdit viewModel)
    {            
        if (ModelState.IsValid)
        {
            viewModel.PopulateCheckBoxsToSpacerType();
            _db.Entry(viewModel.AuditScheduleInstance).State = System.Data.EntityState.Modified;
            _db.SaveChanges();
            return RedirectToAction("Audit", new {id});
        }
        else
        {
            return View(viewModel);
        }

    }

Updated 09/18/2012

        [HttpPost]
    public ActionResult Edit(int id, AuditScheduleEdit viewModel)
    {            
        if (ModelState.IsValid)
        {
            var addGlassCutting = new SqlParameter("@AuditScheduleID", id);
            _db.Database.SqlQuery<QQAForm.ViewModels.AuditScheduleEdit.AddGlassCutting>
                ("AddGlassCutting @AuditScheduleID", addGlassCutting);
            viewModel.PopulateCheckBoxsToSpacerType();
            _db.Entry(viewModel.AuditScheduleInstance).State = System.Data.EntityState.Modified;
            _db.SaveChanges();
            return RedirectToAction("Audit", new {id});
        }
        else
        {
            return View(viewModel);
        }

    }

Here is my Stored Procedure:

ALTER PROCEDURE [dbo].[AddGlassCutting] 
-- Add the parameters for the stored procedure here
@AuditScheduleID int = null
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
/****** Script for adding Glass Cutting  ******/
INSERT INTO QQAEntities.dbo.MainAnswers (AuditScheduleID, MainQuestionID)
SELECT @AuditScheduleID, MainQuestionID
FROM QQAEntities.dbo.MainQuestions
WHERE ReferenceNo > 0

END

At this point there are no errors in the code, it compiles, but when it is executed it does not run the stored procedure.

Any Help would be appreciated.

possible duplicate of [Does Entity Framework Code First support stored procedures?](http://stackoverflow.com/questions/4845246/does-entity-framework-code-first-support-stored-procedures) — Erre Efe, Sep 14 '12 at 17:26
Sorry have only used what i showed in the post. the same that i used to execute it in SQL. @Xander i have not done this before so same as webforms doesnt help. do you have an example or a link i could look at. Do i need anything in the model? — Scott Purtan, Sep 14 '12 at 17:28

score 1 · Accepted Answer · answered Sep 20 '12 at 20:09

Below is what i have found and works. This is for MVC3 C# Code first approach.

        [HttpPost]
    public ActionResult Edit(int id, AuditScheduleEdit viewModel)
    {            
        if (ModelState.IsValid)
        {
            var addGlassCutting = new SqlParameter("@AuditScheduleID", id);
            _db.Database.SqlCommand
            ("EXEC AddGlassCutting @AuditScheduleID", addGlassCutting);
            viewModel.PopulateCheckBoxsToSpacerType();
            _db.Entry(viewModel.AuditScheduleInstance).State = System.Data.EntityState.Modified;
            _db.SaveChanges();
            return RedirectToAction("Audit", new {id});
        }
        else
        {
            return View(viewModel);
        }

    }

The final take away is using SqlCommand instead of SqlQuery. At the top you may add other Parameters if you want. I only needed the one. The stored procedure was written in SQL Server 2008 Express and the only thing that i am doing in the webpage is executing it.

score 0 · Answer 2 · edited May 23 '17 at 12:31

0

So you say code first, I would assume you're using Entity Framework:

Does Entity Framework Code First support stored procedures?

edited May 23 '17 at 12:31

Community

1
1

answered Sep 14 '12 at 17:25

Mark W

3,879
2
37
53

Don't you think this could be better as a comment? – Erre Efe Sep 14 '12 at 17:26
So i have created a stored procedure in SQL server 2012 for the database. What i see leads me to beleave i need to add this to my view model for it to work. Is that correct? – Scott Purtan Sep 14 '12 at 17:39

score 0 · Answer 3 · answered Sep 14 '12 at 20:04

You should try using Entity Framework, but if you are struggling with it, then at least create some SOC (separation of concerns) and put your database ADO.NET calls outside of your controller. You want to have SKINNY controllers that are NOT wired up to your database.

Ideally, layers ... In your solution create more projects and have YourProject.Data etc.. Try creating a repository pattern... try and use an IOC container.

Some of the concepts may just be things you are not ready yet. For now you certainly could use web.config connection string and wire up your controller to query /pull data, but please don't leave that database code bleeding into your controller methods.

I am not sure i understand what you mean. I am using entity framework and pulling, editing, and querying data. I used the code first approach. I only need the stored procedure because it makes sense. When a Audit is created I need this to store a full list of questions in the table for that ID. All i need to do is call the stored procedure from the webpage at POST. I hope this explains my situation better. — Scott Purtan, Sep 17 '12 at 14:34

How do you call a stored procedure in MVC3 C#

3 Answers3