Ask the Expert

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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: