SqlDataAdapter vs SqlDataReader

by

DataReader:

  • Needs the connection held open until you are finished (don’t forget to close it!).
  • Can typically only be iterated over once
  • Is not as useful for updating back to the database

On the other hand, it:

  • Only has one record in memory at a time rather than an entire result set (this can be HUGE)
  • Is about as fast as you can get for that one iteration
  • Allows you start processing results sooner (once the first record is available). For some query types this can also be a very big deal.

DataAdapter/DataSet

  • Lets you close the connection as soon it’s done loading data, and may even close it for you automatically
  • All of the results are available in memory
  • You can iterate over it as many times as you need, or even look up a specific record by index
  • Has some built-in faculties for updating back to the database

At the cost of:

  • Much higher memory use
  • You wait until all the data is loaded before using any of it

So really it depends on what you’re doing, but I tend to prefer a DataReader until I need something that’s only supported by a dataset. SqlDataReader is perfect for the common data access case of binding to a read-only grid.

For more info, see the official Microsoft documentation.

More Related Contents:

  1. List box Object reference not set to an instance of an object
  2. How to get ALL child controls of a Windows Forms form of a specific type (Button/Textbox)?
  3. Calling the base constructor in C#
  4. Calculate the execution time of a method
  5. Why isn’t there generic variance for classes in C# 4.0?
  6. LINQ to Entities does not recognize the method ‘Double Parse(System.String)’ method, and this method cannot be translated into a store expression
  7. Remove items from one list in another
  8. How to make two transparent layer with c#?
  9. WPF Application that only has a tray icon
  10. How the StringBuilder class is implemented? Does it internally create new string objects each time we append?
  11. No console output when using AllocConsole and target architecture x86
  12. Get value of a public static field via reflection
  13. In .NET/C# test if process has administrative privileges
  14. Record Video of Screen using .NET technologies [closed]
  15. Intersect LINQ query
  16. String.Format – how it works and how to implement custom formatstrings
  17. cannot implicitly convert type ‘bool?’ to ‘bool’. An explicit conversion exists (are you missing a cast?)
  18. Convert file path to a file URI?
  19. Best C# API to create PDF [closed]
  20. Check if an executable exists in the Windows path
  21. What does “DisplayClass” name mean when calling lambda?
  22. What value should I pass into TimeZoneInfo.FindSystemTimeZoneById(String)?
  23. Checking if an object is a number
  24. LINQ query to return a Dictionary
  25. VS2010 – How to automatically stop compile on first compile error
  26. Is it better to execute many sql commands with one connection, or reconnect every time?
  27. Why does “\n” give a new line on Windows?
  28. How to lock on an integer in C#?
  29. How do I determine if a given date is the Nth weekday of the month?
  30. PostMessage WM_KEYDOWN send multiply keys?

Leave a Comment