2

I am having to return several record sets from SQL Server to constructs a C# object. While EF doesn't currently support (possibly the beta version) returning complex objects like this I am having to resort to returning a DataSet using ADO.NET to retrieve the data before transforming it into a pleasant C# representation, see below.

SELECT * FROM ...
exec dbo.usp_SP1 @ProductID,@CatalogName
exec dbo.usp_SP2 @ProductID,@CatalogName

Its always better to make fewer database calls however due to how this query is being executed would making several requests for each DataSet be that much worst?

2 Answers2

0

One database roundtrip is always better that three (or two).

Your code could be clean even with this set of queries. You just have to call DataTable.Load three times, in succession, to load all three results sets from the DbDataReader.

So yes, I recommend one bigger query and a single DataSet for all result sets.

Marcel N.
  • 13,726
  • 5
  • 47
  • 72
  • All roads lead to the same destination. Less calls to the database are always better. While I value clean code in cases like these performance out-weighs using EF or another ORM that doesn't supports complex return types. –  Jul 06 '12 at 14:20
  • @DaTribe: What I meant was that your code can be clean even without an ORM. Have a look at the second function in this question to see what I mean: http://stackoverflow.com/questions/11362821/dbdatareader-nextresult-and-filling-more-than-one-table/11362847#11362847 – Marcel N. Jul 06 '12 at 14:29
  • I understood what you mean but one of the advantages ORMs provide is compile time type checking while extracting data from DataTables use a string literal. –  Jul 06 '12 at 14:33
  • @DaTribe: I know, I know. I always prefer one ORM or another to hand coded data access, but I was referring strictly to the issue you exposed in your question. – Marcel N. Jul 06 '12 at 14:39
-1

You can reference each table result inside of a dataset. ds.Tables[index].Rows.... So I would make one call, get all the data and return it via whether you call another sproc or just a simple SELECT.

JonH
  • 32,732
  • 12
  • 87
  • 145