DataTable or List of strongly typed objects for cleaner codebase?

Question

Public Function GetUserList() As DataTable
    Dim resultTable As New DataTable
    Dim queryString As String = "SELECT NAME, ID FROM user" 
    ...

    myDataAdapter.Fill(resultTable)

    ...

    Return resultTable
End Function

vs

Public Function GetUserList() As List(Of User)
    Dim resultList As New List(Of User)
    Dim queryString As String = "SELECT NAME, ID FROM user" 

    ...

    Do While(myDataReader.Read())
        resultList.add( _
        new User(myDataReader.GetString(0),myDataReader.GetInt32(1)))
    Loop                         

    ...

    Return resultList
End Function

The second method tells the function caller what return types to expect so it should be better. The problem is it requires different classes to hold the return values for different queries!

I cannot imagine it would be very "clean" when you have 50 struct-like classes that are only used in one or two functions?? Am I missing something?

Edit: To clarify my question: Should I create 1 class for 1 query or is there other better practice?

Answer 1

If you are working on a simple little project with a couple tables and a few more queries then hand writing a class for each query might be the way to go. But I work with about 25 different (active) applications using about 20 different databases with tables that range from 10 to 200 tables and hand writing every class for every query would not only take 50% of my time but it would take 50% of my time for maintenance, over what it does now.

What I have is a simple program that reads the schema of a database and (based on tables/views/SPs I select) create a class for each object. This is used for holding the data, not querying it. Event though you may not return all rows every time, you can still put the results in the same class. For example, you got the ID and NAME from the USER table. Your class/table may have more fields but you just fill in the ID and NAME. If you need more info in another query, like EMAIL or something, then you fill it in too.

Now, the one issue with generating your own classes is if you need to create some custom property based on some calculation or something, you want to do that in your class, but next time you regenerate your classes from the schema, say you add a field or table, your custom property you wrote in the generated class would be overwritten. That's why you should create a BASE class for your generated code and then another class that inherits from that class, for your custom code.

This may all sound confusing but it really is very simple once you start doing it. The first step is creating the application/tool to generate your classes. This simply reads the schema of your database (you can find all kinds of posts for how to do that for what ever database you're using) and using something like IO.StreamWritter, create CLS files in the way you want them, as you showed above.

Then have it create a second class (this will be your custom code class) also for each table, but only if it does not exists already. In this class, you want to inherit from the other class.

So now when you use your data (queries, iterating, etc), you use your custom class. To make things easy, so you don't get confused, you can give a good naming conventions (like MyTable_BASE) to your base classes and then your custom classes would just be the name of your table.

The last step you would want to do is create a class/dll you can use in your application for running queries and filling in the data for you. You don't want to have to create a new class for each query and result, so doing something generic for filling in the classes is the best way to go. This creates one more level difficulty because to fill it in generically, all your base classes should inherit from a single generic class. This class would know it is data and how to store it but not know anything about the specific data. Then your methods for getting data will fill these base-base classes in.

There is a little more to all this but you will figure it out as you go, if you decide to go this way. Below I included some simple classes to give you an idea of the process. They don't really work as-is because I took some liberties to keep the code short.

Here is a simple class for holding data. This is the class all your generated classes should inherit from:

Public Class MyBaseEntity
  Private _DR as DataRow
  Public sub New(byval DR as dataRow)
    _DR = DR
  End Sub
End Class

Next is your generated class, based on the schema of your object (Table,View,Etc) All this code would be generated by your app/tool:

Public Class MyTable_BASE
  Inherits MyBaseEntity
  'Your code for NEW that calls the base class here
  Public property ID as int32
    Get
      return _DR("ID")
    End Get
    'Code for setter
  End Property
  'More code for each property/field in your table  

Last would be your custom class. You may not always use it but it is very useful once you really get into doing it this way. This class is only generated if it does not exist (to prevent overwriting your custom code):

Public Class MyTable
  Inherits MyTable_BASE
  'Your code for NEW that calls the base class here
End Class

Then if you had some calculation of something you wanted to do, you can put it in that class. For example, if you had an ORDERS table, you would store the price and quantity but not the total. In your GUI though, you need to show the total so you would create a property in that last class that would calculate the total based on the price and quantity.

Public Readonly Property Total as Decimal
  Get
    Return Price * Quantity
  End Get
End Property

Last thing I want to share is some sample code for instantiating these classes because if you create a generic method for holding your results from a query, you don't know the type (your custom class) that should be returned.

Here are a couple ways I do this but there are many more.

Public Function GetMyData(Byval SQL as string,Byval ReturnType as type) as MyBaseEntity()
  '...Your code to connect to database and get data into datarows here
  Dim aRet as new arraylist
  For i As Int32 = 0 To DT.Rows.Count - 1
    aRet.Add(Activator.CreateInstance(ReturnType, DT.Rows(i))
  Next
  Return aRet.ToArray(ReturnType)
End Function

Or, you could do it using Generics:

Public Function GetMyData(of T)(Byval SQL as string) as List(oF T)
  '...Your code to connect to database and get data into datarows here
  '...Similar type of code here, you get the idea.
End Function