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.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.
Dig Deeper on Win Development Resources
Related Q&A from Daniel Cazzulino
Here Daniel Cazzulino explains how to load a DSL (domain specific language) domain model instance file programmatically. This requires the .NET type ...continue reading
Here we offer a glimpse at 12 of .NET development expert Danny Cazzulino's top ASP.NET questions and answers.continue reading
C# developers should NOT be modifying InitializeComponent method in the code-behind (or any of the variable definitions) by hand.continue reading
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.