Using ASP.Net MVC with Classic ADO.Net

Question

I am looking out for a way to access stored procedures using Classic ADO.Net, since I am new to ASP.Net MVC I do not know how to go about it.

Majority of the examples show CRUD operations using ADO.Net Entity framework.

Answer 1

You could have a repository:

public interface IUsersRepository
{
    public User GetUser(int id);
}

then implement it:

public class UsersRepository: IUsersRepository
{
    private readonly string _connectionString;
    public UsersRepository(string connectionString)
    {
        _connectionString = connectionString;
    }

    public User GetUser(int id)
    {
        // Here you are free to do whatever data access code you like
        // You can invoke direct SQL queries, stored procedures, whatever 

        using (var conn = new SqlConnection(_connectionString))
        using (var cmd = conn.CreateCommand())
        {
            conn.Open();
            cmd.CommandText = "SELECT id, name FROM users WHERE id = @id";
            cmd.Parameters.AddWithValue("@id", id);
            using (var reader = cmd.ExecuteReader())
            {
                if (!reader.Read())
                {
                    return null;
                }
                return new User
                {
                    Id = reader.GetInt32(reader.GetOrdinal("id")),
                    Name = reader.GetString(reader.GetOrdinal("name")),
                }
            }
        }
    }
}

and then your controller could use this repository:

public class UsersController: Controller
{
    private readonly IUsersRepository _repository;
    public UsersController(IUsersRepository repository)
    {
        _repository = repository;
    }

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

This way the controller is no longer depend on the implementation of your data access layer: whether you are using plain ADO.NET, NHibernate, EF, some other ORM, calling an external web service, XML, you name it.

Now all that's left is to configure your favorite DI framework to inject the proper implementation of the repository into the controller. If tomorrow you decide to change your data access technology, no problem, simply write a different implementation of the IUsersRepository interface and reconfigure your DI framework to use it. No need to touch your controller logic.

Your MVC application is no longer tied to the way data is stored. This makes it also easier to unit test your controllers in isolation as they are no longer tightly coupled to a particular data source.

Answer 2

Check out Dapper-dot-net - it's what drives this site - excellent, light-weight, based on pure ADO.NET, supports stored procedures very nicely - can't say enough good things about it!

Answer 3

ASP.NET MVC works with any database framework you want to use. You can retrieve your data any way you prefer (such as classic ADO.NET) and pass the resulting data model to the view. You just have to specify the type of model in the View to match the object you are passing to it.

Answer 4

If you know how to do it with ADO.NET you can do that in ASP.NET MVC (be aware, this are absolutely different frameworks have no dependency on each other).

You can encapsulate you DataAccess code into Repostitories and use those Repositories, in Controllers;