MVC 4 using Stored Procedure to display view

newbie in MVC 4.

would like to have a sample MVC 4 in c# displaying records from stored procedure. Thanks

if you have other resources kindly post links. thanks

If you've ever done it in MVC without stored procedure than you already should know the answer. Using stored procedure in opposed to dynamic sql statement (or ORM if you are using one) doesn't make MVC work any different. You retrieve data into your OBJECT and pass it to your view. What do you use to access data? LINQ to SQL? Entity Framework, NHibernate, other ORM? Straight ADO.NET?

thanks for replying to my post. I've read that stored proc is somehow replaced by ORM, but I would like to use my stored procedures and not rewrite again usingEF. would still be possible?

I did try using ADO.NET EF and create edmx file. But I can't find any sample to guide me on how call the function import (storedproc) in controller and displaying the results in view.

your help is much appreciated. TIA

When you generate a Data Source in VS you get to select what tables, views and stored procedures to generate DataTables and table adapters from. You can also edit the table adapters in the DataSet designer after running the wizard to specify what SQL code or stored procedure is to be used to retrieve or save the data.

would this work i have a 2 classes


Employee.cs

Code:

---

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

using System.Data;
using System.Data.SqlClient;
using System.Web.Configuration;


namespace MYPROJ.Models
{
    public class Employee
    {
       

        public static List<EmployeeInfo> GetEmployee(int empID)
        {
            List<EmployeeInfo> empList = new List<EmployeeInfo>();
            SqlConnection conn = new SqlConnection(Config.HRISConnection);
            try
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand("stp_GetEmployees", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@EmpID", empID);
                cmd.Parameters.AddWithValue("@access", "0");
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    empList.Add(new EmployeeInfo()
                    {
                        Id = Convert.ToInt32(dr["ID"]),
                        EmployeeID = dr["EmployeeId"].ToString(),
                        FullName = dr["FullName"].ToString(),
                        GroupName= dr["GroupName"].ToString()
                    });
                }
            }
            catch (Exception)
            {
                return null;
            }
            finally
            {
                conn.Close();
            }
            return empList;
        }

        public static List<EmployeeInfo> GetEmployee()
        {
            //get all artists
            List<EmployeeInfo> empList = GetEmployee(10);
            return empList;
        }
 
     
 
        private class Config
        {
            static public String DBConnection { get { return WebConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString; } }
        }
 
   
    }
}

---

employeemodels.cs

Code:

---

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;

namespace MYPROJ.Models
{
    public class EmployeeInfo
    {
       
        public int Id { get; set; }
        public string EmployeeID { get; set; }
        public string FullName { get; set; }
        public string GroupName { get; set; }
       
    }
}

---

how can i get this to display to my view?

using VS 2012 MVC 4

As Serge said, how the data is obtained in irrelevant to MVC. Once you have a list of data items, displaying them in the view is the same regardless. If you have any examples of displaying a list of data using MVC then it's relevant.

The code you have should work. All you have to do is "return View(empList )"; at the end of your ActionResult. Just make sure you define your model (in the view) as List<EmployeeInfo>.

In your view you will just loop through your List of items and display them.

On a personal note, I love MVC, I've used webforms for years but ever since MVC came out, I switched and never looked back. Webforms is bloated, heavy and non-optimized framework. MS basically wanted to convert VB developers to the web when they introduced WebForms, trying to mimic classic VB events (Button click etc) adding a bloat of ViewState. With MVC you control your output, it's slimmer and your pages load a lot faster. :) Plus you get a de-coupling of data and presentation layer (WebForms are file based, usually having a code-behind cs or vb file for each page file). With MVC you decide what view to render (no more if something.....Response.Redirect() and most of all you get Test Driven Development (TDD) environment. You can test your code, you can fine tune it before launching your application. I can go on and on but it's been already off-topic :)

@serge very well said. very much appreciated.

I did try to put this on my controller

Code:

---

public ActionResult Employee()
        {

            List<EmployeeInfo> empList = new List<EmployeeInfo>();
            return View(empList);
           
           
        }

---

but every time the empList is initialize it does not contain records and return 0 value. Could it be the Employee.cs class? I have tested the stored procedure and it is returning records given the parameters.

How can i test class and call this method GetEmployee()?

Thanks again for your help. greatly appreciated.

It's because if you look at your code, your empList is never being populated with records. I'm not sure what you are using for your data access layer (LinqToSQL, Entity Framework, pure ADO.NET or others), let's get a basic example of LinqToSQL to get you going:

PHP Code:

---

        public ActionResult Employee()
        {

            List<EmployeeInfo> empList = new List<EmployeeInfo>();
            //here you need to get your data from the database
            empList = (from e in dbContext.Employees select e).ToList();
            //above code is translated to: select * from employees
            return View(empList);


        } 


---

thanks serge, after reading more about EF I've decided to go database first approach since I'm dealing with a lot of complex stored procedures.

I was able to call my stored procs after generating edmx file:

in my controller index

Code:

---

private ProjEntities db = new ProjEntities();
// call stored procedure
var viewModel = db.stp_getEmployees(0, 1, null, "", "[Name]");
ViewBag.Employees = viewModel.ToList();
return View();

---

since this is dynamic(I don't know yet but i believe there is a proper way to do this.) to create a model that will contain sp's return other related fields)
I was able to iterate through it using ViewBag to display in my view

Code:

---

@{ var model = ViewBag.Employees }
@foreach (var item in model)
{
  <tr><td>Name</td><td>@item.FullName</td></tr>
}

---

this somehow resolved my issue. thanks for all your help to those who have shared their knowledge. very much appreciated..

I may have more questions to ask and will post another soon.

You should be using the Model and not the ViewBag. The primary data for the view is the Model. The ViewBag is for extraneous data. Some people advocate not using ViewBag at all and passing all data via the Model.

The reason it worked with a ViewBag is because you explicitly assigned value to it.
This code:

PHP Code:

---

var viewModel = db.stp_getEmployees(0, 1, null, "", "[Name]");
ViewBag.Employees = viewModel.ToList();
return View(); 


---

should be this:

PHP Code:

---

var viewModel = db.stp_getEmployees(0, 1, null, "", "[Name]");
return View(viewModel.ToList()); 


---

On a side note: some people don't use ViewBag to pass extra data to the view, I personally use it. The reason I don't like passing extra data with the model is because I would have to define my model first. The view has to know what the object type is. It's one less model/class to maintain.

Yes you are right it was supposed to be pass to the model. but my stored procedure returns query from join tables that have many fields that my employee class model can't accomodate. That is why I used ViewBag because it can somehow handle dynamic contents.

Quote:

---

Originally Posted by VBKNIGHT

Yes you are right it was supposed to be pass to the model. but my stored procedure returns query from join tables that have many fields that my employee class model can't accomodate. That is why I used ViewBag because it can somehow handle dynamic contents.

---

So use a different model for your view. The view doesn't control you. You control it. If you want to pass it a specific model then that's what you tell it to expect.

Quote:

---

Originally Posted by jmcilhinney

So use a different model for your view. The view doesn't control you. You control it. If you want to pass it a specific model then that's what you tell it to expect.

---

+1 for this.

I would like to add DON'T call your data source right from the controller!

In a typical scenario here's what my ActionResult in my EmployeeController would look like.

c# Code:

public ActionResult Employment(int id)
        {
            EmployeeModel employeeModel = new EmployeeModel(id);
 
            return View(employeeModel);
        }

My model is going to take that id and go to my BusinessLogic class with a request to get my entity. Once I've got my entity I assign it to my Employee property on my Model.

C# Code:

public EmployeeModel(int id)
            : this(new EmployeeLogic().Get(id))
        {
        }
 
        public EmployeeModel(Employee employee)
        {
            Employee = employee;
        }

My BusinessLogic class is going to do some checking to make sure that this is allowed etc and then get my data.

C# Code:

public Employee Get(int employeeId)
        {
            return new EmployeeRepository().Get(employeeId);
        }

My repository in this case will return the data as a strongly typed Employee object in this case.

C# Code:

public Employee Get(int employeeId)
        {
        }

Now suppose that you have a scenario where you need to display Employee information and Training for that employee. You could just as easily create a EmployeeTrainingModel that will provide training along with all of the basic information about the employee.

C# Code:

private List<Training> _traininngList;
        public List<Training> TrainingList
        {
            get
            {
                return _traininngList ?? (_traininngList = new TrainingLogic().GetList(Employee.EmployeeId));
            }
        }

Then you can have another ActionResult that will provide you EmployeeTrainingModel to the view.

C# Code:

public ActionResult EmployeeTraining(int id)
        {
            EmployeeTrainingModel employeeTrainingModel = new EmployeeTrainingModel(id);
 
            return View(employeeTrainingModel);
        }