ASP .NET MVC 3 Models + stored procedures

Question

Im, new in ASP MVC and I don't know how to create Models which base on stored procedures from my db. I have already database which works with another application, and my web page must use mentioned db.

I would be gratefull if someone can show me some piece of code describing the proper way how to do that. (if I wasnt clear: I need to create ASP .NET Models which use stored procedures from my database and nothing more)

txh in advance

Answer 1

@fgeorgiew are you just needing to know how to populate a model (class) from a stored procedure? You could use an ORM like NHibernate or Entity Framework to handle the plumbing for you, or just use raw ADO.NET code, like in the example below. Note, this is just rough code, but you get the idea.

public class MyModel
{
    public int ModelId { get; set; }
    public string FirstName { get; set; }
}

public class SqlMyModelRespoitory : IMyModelRepository // optional for DI/IoC, assume interface with GetSingleModel method
{
    public MyModel GetSingleModel()
    {
        MyModel model;
        string connString = "server=10.1.1.1;database=MyDb;uid=me;pwd=hidden";
        using (SqlConnection conn = new SqlConnection(connString))
        {
            conn.Open();
            using (SqlCommand cmd = new SqlCommand())
            {
                cmd.Connection = conn;
                cmd.CommandType = System.Data.CommandType.StoredProcedure;
                cmd.CommandText = "p_GetMyModelFromDb";

                using (SqlDataReader reader = cmd.ExecuteReader())
                {
                    while (reader.Read())
                    {
                        model = new MyModel 
                        {
                           ModelId = Convert.ToInt32(reader[0]),
                           FirstName = reader[1].ToString()
                        };
                    }
                }
            }
        }
        return model;
    }
}

Answer 2

You could start with some abstraction indicating your intent:

public interface IMyRepository
{
    SomeModel Get(int id);
}

then you could write an implementation which will use your stored procedure:

public class MyRepositorySql: IMyRepository
{
    public SomeModel Get(int id)
    {
        ... call your stored procedure
    }
}

then design your controller so that it takes this abstraction as argument:

public class MyController: Controller
{
    private readonly IMyRepository _repository;
    public MyController(IMyRepository repository)
    {
        _repository = repository;
    }

    public ActionResult Index(int id)
    {
        var model = _repository.Get(id);
        return View(model);
    }
}

Now all that's left is to configure your DI framework to pass the proper implementation into the controller's constructor. As you can see now the controller is completely decoupled from the way data is fetched. It doesn't really matter whether you are using StoredProcs, some ORM or whatever in your data access layer.