Returning data from database in .net: Return a DataTable or LIst<T>?

I'm struggling with bridging the concepts of good database design with good object orientated design.

Traditionally if I wanted to display a list of news stories in a repeater, I would use something like:

<script runat="server">

    void ShowNews()
    {
        rptNewsStories.DataSource = News.GetAllNews(); // Returns a DataTable
        rptNewsStories.DataBind();
    }

</script>

<asp:Repeater id="rptNewsStories" runat="server">
    <ItemTemplate>
        <div>
            <span class="Title"><%# Eval("Title")"%> (<%# Eval("Location")"%>)</span>
            <p>
                <%# Eval("Summary")"%>
            </p>
            <ul>
                <li>Added by: <%# Eval("AddedByFullName")%></li>
                <li>Added on: <%# Eval("AddedOn")%></li>
            </ul>
        </div>
    </ItemTemplate>
</asp:Repeater>

Here News.GetAllNews() returns a DataTable, which is just a dump of what the stored procedure returns. The stored procedure is written to return data by using joins, so it's开发者_如何学Go more than one tables worth of data.

The advantage of this in that in the database the stored procedure can look up who added the news story from the AddedByID that exists in the News table and return the persons full name as the AddedByFullName value returned.

However if I try and drop the use of a DataTable and instead return a List of the News objects, I get the following:

<script runat="server">

    void ShowNews()
    {
        rptNewsStories.DataSource = News.GetAllNews(); // Returns a List<News>
        rptNewsStories.DataBind();
    }

</script>

<asp:Repeater id="rptNewsStories" runat="server">
    <ItemTemplate>
        <div>
            <span class="Title"><%# Eval("Title")"%> (<%# Eval("Location")"%>)</span>
            <p>
                <%# Eval("Summary")"%>
            </p>
            <ul>
                <li>Added by: <!-- Here there is only a AddedByUserID, not an AddedByFullName value --></li>
                <li>Added on: <%# Eval("AddedOn")%></li>
            </ul>
        </div>
    </ItemTemplate>
</asp:Repeater>

But now I'm left with the problem that certain values that I want to display (Like AddedByFullName) don't exist within the News object, because they're not something that's explicitly set, but instead of retrieved from a lookup ID in the object.

I'd like to return objects rather than DataTables, but I don't know the best way to bridge this gap.

Do I:

* Create additional properties in the News class for every additional value that can be returned from the database in relation to this data?

* Stick with DataTables for specific cases where that are lots of additional values?

Or am I just totally on the wrong track!

---

Your choices are restricted by the underlying technology you're willing to use as data access. At the moment there are several alternatives that the VS toolset and .Net framework supports:

• Classic ADO.Net 2.0. Design in Typed datasets and DataTable objects in code.
• LINQ to SQL. Design in LINQ O/R Design modeler, use IQueryable<T> and LINQ syntax in code.
• Entity Framework. Design in the Entity Data modeler, use entities in your code.
• 3rd party ORM tools like NHibernate. Each one has it own specific designer and code object type.

From these, all but ADO.Net typed data sets allow you to specify navigation relations like you ask, either eagerly or lazily loaded. In a case like you describe the natural hing to do with these technologies would be to model the relationship between News article and Author explicitly in the designers and let the framework deal with the problem of loading the appropriate data and into the appropriate types, ultimately meaning that the join issue is handled implicitly by the application data access layer (the framework) rather than by an explicitly created stored procedure.

The choice of technology will ripple everywhere in your code, from how you fetch your data to how you display it and how you update, none of these technologies are easily interchangeable. Personally, I find that the right balance of power and complexity is with LINQ to SQL.

---

You can try this, perhaps not the better solution:

void ShowNews()
{   
    User[] usersConcerned = News.GetAllUsersLinkedWithNews(); //only return the users concerned by the news
    List<News> news = News.GetAllNews();
    foreach(News item in news)
    {
       item.AddedByUser = usersConcerned.FirstOrDefault(u=>u.Id == item.AddedByUserID);
    }
    rptNewsStories.DataSource = news ; 
    rptNewsStories.DataBind();
}

---

Good question:

There are a couple of approaches you can take:

1. You can retrieve the additional data that you need on the binding event of the repeater and populate it into a "placeholder" for each news record returned. This has performance issues because of the additional querying but it will work.

2. If possible I'd modify the news object to contain the additional information, or create an object that inherits from the news object that had the additional information or properties contained within it.

Hope this helps :-) +1

Edit/Comment: I think the friction you are seeing in your design is that you aren't acknowledging the "has-a" relationship between your news object and what I'd call its author/contributor. The join you are doing in your query is just disguising the lack of the relationship in the object model.