Reading DataSet

Question

How do I read data from a DataSet in WPF? I have a train schedule table with just 2 columns and I want to be able to read the departure times and calculate when the next train is leaving. For example, the time now is 12:29 and my application should tell me that next train will depart at 12:33.

I already googled left and right. I'm on .NET 3.5.

Answer 1

DataSet resembles database. DataTable resembles database table, and DataRow resembles a record in a table. If you want to add filtering or sorting options, you then do so with a DataView object, and convert it back to a separate DataTable object.

If you're using database to store your data, then you first load a database table to a DataSet object in memory. You can load multiple database tables to one DataSet, and select specific table to read from the DataSet through DataTable object. Subsequently, you read a specific row of data from your DataTable through DataRow. Following codes demonstrate the steps:

SqlCeDataAdapter da = new SqlCeDataAdapter();
DataSet ds = new DataSet();
DataTable dt = new DataTable();

da.SelectCommand = new SqlCommand(@"SELECT * FROM FooTable", connString);
da.Fill(ds, "FooTable");
dt = ds.Tables["FooTable"];

foreach (DataRow dr in dt.Rows)
{
    MessageBox.Show(dr["Column1"].ToString());
}

To read a specific cell in a row:

int rowNum // row number
string columnName = "DepartureTime";  // database table column name
dt.Rows[rowNum][columnName].ToString();

Answer 2

If ds is the DataSet, you can access the CustomerID column of the first row in the first table with something like:

DataRow dr = ds.Tables[0].Rows[0];
Console.WriteLine(dr["CustomerID"]);

Answer 3

If this is from a SQL Server datebase you could issue this kind of query...

Select Top 1 DepartureTime From TrainSchedule where DepartureTime > 
GetUTCDate()
Order By DepartureTime ASC

GetDate() could also be used, not sure how dates are being stored.

I am not sure how the data is being stored and/or read.

Answer 4

TL;DR: - grab the datatable from the dataset and read from the rows property.

            DataSet ds = new DataSet();
            DataTable dt = new DataTable();
            DataColumn col = new DataColumn("Id", typeof(int));
            dt.Columns.Add(col);
            dt.Rows.Add(new object[] { 1 });
            ds.Tables.Add(dt);

            var row = ds.Tables[0].Rows[0];
            //access the ID column.  
            var id = (int) row.ItemArray[0];

A DataSet is a copy of data accessed from a database, but doesn't even require a database to use at all. It is preferred, though.

Note that if you are creating a new application, consider using an ORM, such as the Entity Framework or NHibernate, since DataSets are no longer preferred; however, they are still supported and as far as I can tell, are not going away any time soon.

If you are reading from standard dataset, then @KMC's answer is what you're looking for. The proper way to do this, though, is to create a Strongly-Typed DataSet and use that so you can take advantage of Intellisense. Assuming you are not using the Entity Framework, proceed.

If you don't already have a dedicated space for your data access layer, such as a project or an App_Data folder, I suggest you create one now. Otherwise, proceed as follows under your data project folder: Add > Add New Item > DataSet. The file created will have an .xsd extension.

You'll then need to create a DataTable. Create a DataTable (click on the file, then right click on the design window - the file has an .xsd extension - and click Add > DataTable). Create some columns (Right click on the datatable you just created > Add > Column). Finally, you'll need a table adapter to access the data. You'll need to setup a connection to your database to access data referenced in the dataset.

After you are done, after successfully referencing the DataSet in your project (using statement), you can access the DataSet with intellisense. This makes it so much easier than untyped datasets.

When possible, use Strongly-Typed DataSets instead of untyped ones. Although it is more work to create, it ends up saving you lots of time later with intellisense. You could do something like:

MyStronglyTypedDataSet trainDataSet = new MyStronglyTypedDataSet();
DataAdapterForThisDataSet dataAdapter = new DataAdapterForThisDataSet();
//code to fill the dataset 
//omitted - you'll have to either use the wizard to create data fill/retrieval
//methods or you'll use your own custom classes to fill the dataset.
if(trainDataSet.NextTrainDepartureTime > CurrentTime){
   trainDataSet.QueueNextTrain = true; //assumes QueueNextTrain is in your Strongly-Typed dataset
}
else
    //do some other work

The above example assumes that your Strongly-Typed DataSet has a column of type DateTime named NextTrainDepartureTime. Hope that helps!