C# - Passing DataTable vs. SqlDataReader

Question

I have created a method that can be called using a stored procedure name and list of SqlParameters, lets call it GetData(). GetData() then manages talking to the SQL Server and getting the data. What I then need to do is hand the data back to the caller for them to read, they have no need to manipulate it. What I am trying to figure out is if it is best to hand the caller back a DataTable or SqlDataReader?

Right now, I am thinking that going with a DataTable is the best route. My decision points on this are:

• The sets of data I am getting back are small, under 100 rows and 20 columns, so memory shouldn't be an issue.
• From what I understand, a DataTable grabs all of the data and plugs it into the DataTable, then disconnects itself so GetData() would manage it's own connection.
• With a SqlDataReader, I would have to manage the connection in the calling function after I am done with the data. I would not be able to close it after I have made the call in GetData() since I am only reading one row at a time from the database.

Does this seem like the best route to go for what I need?

Answer 1

Yes, your idea is good.

Pass a datatable back, not the sqldatareader. Connections should only be maintained for the minimum amount of time necessary to transfer data to/from the database server.

Answer 2

Use a DataTable. The data bound controls are built to use that abstraction.