Using ASP.NET MVC without an ORM

Question

In my ASP MVC application I'm using standard SQL (rather that Linq to SQL or other ORM) to query my database.

I would like to pass the database results to my view and iterate over the results in my view. But I'm not sure how to do this. Every example I've seen passes some string or uses L2S. I would like to pass something like nested Hashtables, but the only thing I can think of is to pass an SqlDataReader object to the view, but this sounds like a really bad idea.

How would I go about displaying my database results from a standard SQL query to my view? I would really like use Linq or other ORM, but requirements dictate we don't (don't ask me why, I don't understand). I'm doing this in VB. I'll try by best to convert any C# examples provided.

Answer 1

Try using DataTables - DataTable can load data from IDataReader... (I think the method's called Load)

Answer 2

You could create your own Data Transfer Object classes and populate instances of them using ADO.Net code. These DTO classes would be simple POCO-style classes that just contained property get/set accessors, no methods. Using POCO objects is arguably preferable to DataSets/DataTables as they are lightweight (no superfluous state) and are more intuitive to work with from an object-oriented perspective.

Answer 3

MVC is about separation of concerns. Passing SqlDataReaders, DataTables, or whatever class that resides in the System.Data namespace to a view is not a good idea. You need to define a model which might talk to the database, and a controller which will pass this model to the view. If your company policy says don't use an ORM then maybe classic WebForms are better suited to your scenario than the MVC pattern.

Answer 4

I agree with Rashack. This article explains it in some detail.link text

In a nutshell, here's how to do it using DataTable and DataReader:

private DataTable GetData()
{
    DataTable dt = new DataTable();

    using (SqlConnection connection
             = new SqlConnection("ConnectionString"))
    using (SqlCommand command = new SqlCommand())
    {
        command.Connection = connection;
        command.CommandText = "SELECT * FROM Customers";

        connection.Open();
        using (SqlDataReader reader =
            command.ExecuteReader
                (CommandBehavior.CloseConnection))
        {
            dt.Load(reader);
        }
    }

    return dt;
}

Then, you can read that DataTable into an entity object that you pass around.

I think you'll find this can yield much better performance than using Linq or an ORM.

Answer 5

You could create simple classes for the data you want to transfer and then populate a List of objects in your controller from a data reader manually, and then pass this to your View - e.g. (C# but this should be easy to convert)

// open your connection / datareader etc.

List<Customer> customers = new List<Customer>();

while(dataReader.Read())
{
 Customer c = new Customer();
 c.Id = dataReader.GetInt32(0);
 c.Name = dataReader.GetString(1);
 // etc (you might want to use string indexers instead of ints for the get methods)

 customers.Add(c);
}

// close and dispose your datareader / connection etc as usual

return View("List", customers);