Linq in C#

[Matthijs de Z]

Hi,

hope you can help me out with a little problem I have.

In C# I pull some data from mySQL and fill a DataTable with the
results, using a method similar to something like:

DataTable myDataTable = new Datatable();
myDataTable.Columns.Add("myDate", typeof(int));
myDataTable.Columns.Add("data1", typeof(double));
myDataTable.Columns.Add("data2", typeof(double));
myDataTable.Columns.Add("data3", typeof(double));

while (reader.read())
{
try
{
DataRow myNewRow = myDataTable.newRow();
myNewRow["myDate"] = (int)reader[0];
myNewRow["data1"] = (double)reader[1];
myNewRow["data2"] = (double)reader[2];
myNewRow["data3"] = (double)reader[3];
myDataTable.Rows.Add(myNewRow);
}
catch(Exception ex)
{
}
}


I would like to query the datatable using linq and I can if I do
something like:

int myLastDate = (from myRows in myDataTable.AsEnumerable()
select myrows.fields<int.("myDate")).Max();

but I would like to query the table with some more queries without
having to specify the field type everytime and be able to select just
some columns.

therefore I created this class:

class myInfo
{
public int myDate { get; set;}
public double data1 { get; set;}
public double data2 { get; set;}
public double data3 { get; set;}
}

and then tried to get the simplest query of them all to run:

int myLastDate = (from myInfo myRows in myDataTable.AsEnumerable()
select myrows.myDate).Max();

but that doesn't work. I'll get this message:

"Unable to cast object of type 'System.Data.Datarow' to type
'CalcStats.myInfo'.

I've googled around, but I cannot seems to get my fingers on it.
Any suggestions or even a fix would be very much appreciated.
Kind regards,

Matthijs

 

[Martin Honnen]

Hi,

hope you can help me out with a little problem I have.

In C# I pull some data from mySQL and fill a DataTable with the
results, using a method similar to something like:

DataTable myDataTable = new Datatable();
myDataTable.Columns.Add("myDate", typeof(int));
myDataTable.Columns.Add("data1", typeof(double));
myDataTable.Columns.Add("data2", typeof(double));
myDataTable.Columns.Add("data3", typeof(double));

while (reader.read())
{
try
{
DataRow myNewRow = myDataTable.newRow();
myNewRow["myDate"] = (int)reader[0];
myNewRow["data1"] = (double)reader[1];
myNewRow["data2"] = (double)reader[2];
myNewRow["data3"] = (double)reader[3];
myDataTable.Rows.Add(myNewRow);
}
catch(Exception ex)
{
}
}


I would like to query the datatable using linq and I can if I do
something like:

int myLastDate = (from myRows in myDataTable.AsEnumerable()
select myrows.fields<int.("myDate")).Max();

but I would like to query the table with some more queries without
having to specify the field type everytime and be able to select just
some columns.

therefore I created this class:

class myInfo
{
public int myDate { get; set;}
public double data1 { get; set;}
public double data2 { get; set;}
public double data3 { get; set;}
}

and then tried to get the simplest query of them all to run:

int myLastDate = (from myInfo myRows in myDataTable.AsEnumerable()
select myrows.myDate).Max();

but that doesn't work. I'll get this message:

"Unable to cast object of type 'System.Data.Datarow' to type
'CalcStats.myInfo'.

You would first need to build objects of myInfo:

IEnumerable<myInfo> infos = from row in myDataTable.AsEnumerable()
select new myInfo()
{
myDate = row.Field<int>("myDate"),
data1 = row.Field<double>("data1"),
data2 = row.Field<double>("data2"),
data3 = row.Field<double>("data3")
};
int myLastDate = (from info in infos select info.myDate).Max();

 

[Alberto Poblacion]

[...]
I would like to query the datatable using linq and I can if I do
something like:

int myLastDate = (from myRows in myDataTable.AsEnumerable()
select myrows.fields<int.("myDate")).Max();

but I would like to query the table with some more queries without
having to specify the field type everytime and be able to select just
some columns.

You can do that if you use a Typed DataSet instead of the Untyped
Dataset that you are currently using. Just define the dataset in the
designer in visual studio to contain the same columns that you are currently
defining in your code. Then fill the dataset in the same way that you are
currently using (or use a TableAdapter if you want to save code). You can
then query the typed dataset without using the field types:

http://msdn.microsoft.com/en-us/library/bb399351.aspx

 

[Matthijs de Z]

hi Martin and Alberto,

thank you both for your quick respond.

I've used Martin's suggestion (I was not able to get Alberto's
suggestion working together with MySQL).
The code mentioned seems to be working fine. Thanks!
kind regards,

Matthijs