Unlike the Recordset, which could execute statements (SELECTs), represent a cursor, a snapshot, multidimensional data, etc., making it an extremely heavy object, the DataSet is a lightweight (in comparison), in-memory store. That means that all the cursor-style logic is no longer there. Neither is there any capability for loading data and querying databases. This is a good thing, known as separation of concerns.
In ADO.NET, another object fills the DataSet, the DataAdapter, another one represents the forward-only, read-only cursos, the DataReader. And yet another is the only one responsible for executing SQL statements and stored procedures, the Command. They are represented by the common interfaces IdataAdapter, IDataReader and IDbCommand. Of course, there's the IDbConnection too.
The adapter is a sort of facade object that uses all the other ones to perform it's tasks, namely, Fill and Update. Using it, you have a single object and a simple interface for handling data. Here's the way to load a DataSet with a select statement:
SqlDataAdapter ad = new SqlDataAdapter( "SELECT * FROM titles", "data source=.;initial catalog=pubs;user id=sa;password="); DataSet ds = new DataSet(); ad.Fill(ds);
Note that even if it seems that the adapter is doing all the work, internally it's creating a SqlConnection with the second ctor. Parameter, and a SqlCommand with the query, that uses the connection to execute. Finally, it then uses a reader to load the data.
As for navigation, you need to keep your current index stored somewhere, most probably in a private variable at the form level. You can then access the particular row using it. Remember the DataSet is just like any regular object hierarchy, with collections and items:
DataRow currentrow = ds.Tables.Rows[myidx]; //Can also use: ds.Tables["titles"].Rows
Now you could retrieve values for your form using either of the following calls:
txtTitle.Text = currentrow["title"].ToString(); txtTitle.Text = currentrow.ToString(); txtTitle.Text = currentrow["title", DataRowVersion.Current].ToString();
Note that using the overload that receives the DataRowVersion, you can have greater control over what's displayed.
This was first published in August 2003