How do you call a stored procedure in MVC3 C#

asp.net-mvc-3 c# entity-framework sql

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.

1
1
9/18/2012 7:23:43 PM

Accepted Answer

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.

1
9/20/2012 8:09:55 PM

Popular Answer

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.



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