How to parse a SQL query result and modify it, using C# ?

Question

I'm trying to do something very simple, yet I can't seem to find the proper way to do it.

I have a MySqlDataAdapter object which contains the result of a query. I want to parse the results of this query before rendering them in an ASP.NET Repeater. (convert date from Unix Epoch to human readable, that sort of things).

So far, I'm unable to simply find how to create a loop that will allow me to move row by row in my SQL result, parse a couple of fields and change the content of some fields.

I'm also uncertain if I can do this directly from the MySqlDataAdapter, or from a DataSet, or from a DataTable. Most of the examples I have seen on the web don't cover that specific topic.

Answer 1

You can not loop your result set over DataAdapter object. You should fill your results into a DataTable, then it will be very simple to loop the rows :

DataSet myDataSet = new DataSet();
myDataAdapter.Fill(myDataSet);

if (myDataSet.Tables.Count > 0)
{
    foreach (DataRow drResult in myDataSet[0].Rows)
    {
         // Do some stuff here over your row...
    }
}

Answer 2

First you can fill a DataTable with the results:

DataTable table = new DataTable();
adapter.Fill(table);

After that you can loop through the DataTable and do your modfications:

foreach (DataRow row in table.Rows) {
   row["MyDate"] = DoMagic(row["MyDate"]);
}

Answer 3

Do it inside a dataset.

populate the dataset from the dataadapter, then loop through the records in the dataset and modify those before binding it to the repeater.

Something like:

DataSet ds = new DataSet();
da.Fill(ds);
foreach(DataRow row in ds.Tables[0].Rows)
{
row[0] = ParseThisField(row[0]);
}
// then do your databind

Is a bit of a hacky way to do it though, but should work well enough for your purposes.