SqlDataReader vs. SqlDataAdapter

Dewald

Would anyone care to point out to me what the main differences between SqlDataReader and SqlDataAdapter are, when to use one and when to use the other? I've been using SqlDataReader exclusively so far and have not yet even bothered to read what SqlDataAdapter really is about.

OriginalGriff

The big difference is time. When you use a SqlDataReader, you open a connection, start reading, then loop though each record, processing it. When you have finished, you close the database connection. When you use a SqlDataAdapter, it opens the connection, takes a snapshot of the database, and closes the connection. You can then process the records. You can use a DataAdapter to bind a data source to a control, such as a DataGridView, rather than iterating though each record. Have a look on Google: "SqlDataReader vs. SqlDataAdapter" will give you a lot of hits, most of which are pretty useful. (And yes, I do know that a SqlDataAdapter leaves the connection open if it was already - that's the advanced course, ok?)

Real men don't use instructions. They are only the manufacturers opinion on how to put the thing together. Digital man: "You are, in short, an idiot with the IQ of an ant and the intellectual capacity of a hose pipe."

Wayne Gaylard

I personally like to stick to DataReaders and convert the data into proper CLR objects, rather than binding directly to a DataAdapter. Especially when it comes to WPF applications, as then you can have all the proper interfaces such as IDataErrorInfo and INotifyPropertyChanged which makes Databinding and Validation much easier. I have heard arguments for DataAdapters before but never used them. Just my 2c. :)

Dewald

OK thanks, that helps. As regards databinding, I've always used SqlDataReader, even for that, like so:

using (SqlDataReader mySqlDataReader = mySqlCommand.ExecuteReader())
{
DataSet myDataSet = new DataSet();
DataTable myDataTable = new DataTable();
myDataSet.Tables.Add(myDataTable);
myDataSet.Load(mySqlDataReader, LoadOption.PreserveChanges, myDataSource.Tables[0]);
myDataGridView.DataSource = myDataSource.Tables[0];
}

Do you know of any advantage over this by rather using a SqlDataAdapter? How would you do that?

PIEBALDconsult

I always use a DataReader, never a DataAdapter or DataSet. In my opinion, DataAdapters, DataSets, and DataGrids were developed to allow Microsoft presenters to very quickly query, display, and update simple data and get an "oooh" from the crowd. They are fine for prototyping, but are not suitable for production enterprise applications. DataAdapters are very limited in what they can do and actually try to do too much -- e.g. optimistic concurrency[^]. The few times I've been lazy and used a DataAdapter in a real scenario, it bit me and I had to rework the code to use a DataReader instead. Also, bear in mind that in ADO.net, all data access goes through a DataReader -- ExecuteScalar, ExecuteNonQuery, DataAdapter.Fill, and DataAdapter.Update all use ExecuteReader to perform the tasks. ExecuteReader can be used to execute any SQL statement your database accepts -- queries, DML, and DDL. And, in some cases (SQL Server) you can pass a semi-colon delimited list of SQL Statements (though it doesn't report the results of non-queries the way I'd like X| ). By using a DataReader you can reduce your application's memory footprint when you don't need to have all the records in memory at the same time. Or you can fill a collection of custom objects rather than a DataTable. Or you can fill a DataTable your own way and avoid the DataAdapter's overhead. DataGrids and DataGridViews are rarely an appropriate control for displaying and editing data in real situations.

PIEBALDconsult

I wouldn't even use a DataSet like that, I define and fill DataTables myself. This[^] article shows how.

Dewald

Thanks, it looks interesting, but jeeeez, it looks complicated. I'll go through it more attentively when I have a little more time but, briefly, could you explain why that method is better than using a DataSet like in my earlier example?