How does ADO.NET Dataset work?

I'm facing a problem regarding Dataset. As in VB6, without binding the controls I used to display data in text boxes, I want to allow the user to click on next and previous buttons and call the Recordset's next and previous property, respectively. But in ADO.NET I have not seen such kind of properties. Can you kindly help me how to use this kind of functionality without binding the controls and explain how to create the dataset object?...

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();

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[0].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[0].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

Dig deeper on Win Development Resources

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.



Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: