Click here to Skip to main content
15,312,535 members
Please Sign up or sign in to vote.
4.25/5 (3 votes)
See more:
Can we use stored procedures in a ASP.NET MVC 3 application? Help with sample programs.
Posted
Updated 16-Feb-12 22:20pm
v2
Comments
Eduard Keilholz 17-Feb-12 4:22am
   
We're not here to write code for you, we're here to help you write your own code. What kind of DBMS are you using, SQL Sever? Are you using some kind of ORM (Entity Framework) or something? You need to update your question in order to help us give you the right answer.

1 solution

Of course you can, MVC and your data access strategy are two completely different things.

It depends if you're using Entity Framework or not. If you are, then you can map a procedure to an entity model within the designer, have a look here.

http://msdn.microsoft.com/en-us/data/gg699321[^]

If you're not using EF, then it's quite simple - it's exactly the same as it's always been. You can just use SqlConnection objects (or whatever datasource you are using) and populate data

Here's an example of a controller creating a repository object which has a method that returns a DataTable. Obviously, you could have something here that returns a Domain\Model object or collection, whatever you like.

GetOutstandingInvoices would be your stored procedur name.

C#
public class MyController : Controller
{
    private ISomeRepository _repository;
    
    public MyController()
    {
        // You'd want to use dependency injection here!
        _repository = new SomeRepository();
    }
    
    public ActionResult Index()
    {
        var data = _repository.GetOutstandingInvoices();
        return View(data);
    }

}

public interface ISomeRepository
{
    DataTable GetOutstandingInvoices();    
}

public class SomeRepository : ISomeRepository
{
    public DataTable GetOutstandingInvoices()
    {
        var results = new DataSet();

        var connectionString = ConfigurationManager
            .ConnectionStrings["MyDatabase"].ConnectionString;

        using (var connection = new SqlConnection(connectionString))
        {
            connection.Open();
            using (var command = new SqlCommand("GetOutstandingInvoices", connection))
            {
                command.CommandType = CommandType.StoredProcedure;
                
                // Add any params? 
                
                var adapter = new SqlDataAdapter(command);
                adapter.Fill(results);
            }
        }   
        
        if (results.Tables.Count > 0)
        {
            return results.Tables[0];
        }
        else
        {
            throw new ApplicationException("No data generated for invoicing");
        }         
    }
}
   
Comments
RoshInd 17-Feb-12 5:57am
   
how can we bind data to a gridview and also how can we call it?
Dylan Morley 17-Feb-12 6:47am
   
There is no 'GridView' as such in MVC. You return the data to the View, in your View you determine how you want to display that information.

If you want something that does it all 'out of the box' you could look at Telerik MVC grid, which is free & is pretty good

http://demos.telerik.com/aspnet-mvc/

Source code + samples available

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900