Wednesday, April 16, 2008

Differences between SqlDataAdapter and SqlDataReader

When I went for an interview last few weeks, the interviewers threw me some technical questions about .NET. Luckily I still can get some correct ideas on those questions. Here I will share with you one of the interview questions "What is the differences between SqlDataAdapter and SqlDataReader?"

For SqlDataReader, you need to call the ExecuteReader method of the SqlCommand object.

You can use SqlDataReader when your data access operations is mainly for fetching and displaying all the records in the database, which does not involve insert, update or delete actions, and other manipulations actions e.g. forward-only and read-only.

If the data from database is mainly for displaying in data grid, labels or other web controls, you can go for SqlDataReader.

However, when the SqlDataReader is in use, it requires the connection with the database remains open until its operation is completed.

In short, SqlDataReader can be used when:
- To deal with large volumes of data
- To optimize data access
- To display data in web controls (no update/insert/delete action)

SqlDataAdapter acts as a bridge between a dataset and database server for fetching and saving data. If your data access operations involve actions other than only displaying data, you can use SqlDataAdapter to generate a dataset or datatable.

By using SqlDataAdapter, you don’t need to explicitly open the connection. It will open and close the connection automatically when required.

One of the advantages is that the dataset works on a disconnected architecture. The connection does not need to be remained for it to perform manipulations or other actions. The connection is only required when there is an update records to the database server.

Hope you can get some ideas about SqlDataReader and SqlDataAdapter.


rajujohnmca2008 on October 22, 2011 at 3:35 AM said...


This notes SqlDataReader and SqlDataAdapter that is very helpful for my code.Previous i used only DataSet even if i dont want to perform edit the grid,Now i am wondering my fault.thanks a lot...Give like this kind of article that is very very useful for the beginner developer

muthu karunarathna on August 5, 2012 at 9:42 PM said...

yeah..this is great..thank you sir...keep it up with ur experiences....

Anonymous said...

yeah this is great...its very useful to knw d difference..thank you sir..kepp it up...


Get paid for your opinions! Click on the banner above to join Planet Pulse. Its totally free to sign up, and you can earn UNLIMITED. Find out more by visiting PLANET PULSE.
July Code Blog Copyright © 2010 Blogger Template Designed by Bie Blogger Template