Convert DataTable to IEnumerable<T>
Solution 1
Nothing wrong with that implementation. You might give the yield
keyword a shot, see how you like it:
private IEnumerable<TankReading> ConvertToTankReadings(DataTable dataTable)
{
foreach (DataRow row in dataTable.Rows)
{
yield return new TankReading
{
TankReadingsID = Convert.ToInt32(row["TRReadingsID"]),
TankID = Convert.ToInt32(row["TankID"]),
ReadingDateTime = Convert.ToDateTime(row["ReadingDateTime"]),
ReadingFeet = Convert.ToInt32(row["ReadingFeet"]),
ReadingInches = Convert.ToInt32(row["ReadingInches"]),
MaterialNumber = row["MaterialNumber"].ToString(),
EnteredBy = row["EnteredBy"].ToString(),
ReadingPounds = Convert.ToDecimal(row["ReadingPounds"]),
MaterialID = Convert.ToInt32(row["MaterialID"]),
Submitted = Convert.ToBoolean(row["Submitted"]),
};
}
}
Also the AsEnumerable
isn't necessary, as List<T>
is already an IEnumerable<T>
Solution 2
There's also a DataSetExtension method called "AsEnumerable()" (in System.Data) that takes a DataTable and returns an Enumerable. See the MSDN doc for more details, but it's basically as easy as:
dataTable.AsEnumerable()
The downside is that it's enumerating DataRow, not your custom class. A "Select()" LINQ call could convert the row data, however:
private IEnumerable<TankReading> ConvertToTankReadings(DataTable dataTable)
{
return dataTable.AsEnumerable().Select(row => new TankReading
{
TankReadingsID = Convert.ToInt32(row["TRReadingsID"]),
TankID = Convert.ToInt32(row["TankID"]),
ReadingDateTime = Convert.ToDateTime(row["ReadingDateTime"]),
ReadingFeet = Convert.ToInt32(row["ReadingFeet"]),
ReadingInches = Convert.ToInt32(row["ReadingInches"]),
MaterialNumber = row["MaterialNumber"].ToString(),
EnteredBy = row["EnteredBy"].ToString(),
ReadingPounds = Convert.ToDecimal(row["ReadingPounds"]),
MaterialID = Convert.ToInt32(row["MaterialID"]),
Submitted = Convert.ToBoolean(row["Submitted"]),
});
}
Solution 3
If you are producing the DataTable
from an SQL query, have you considered simply using Dapper instead?
Then, instead of making a SqlCommand
with SqlParameters
and a DataTable
and a DataAdapter
and on and on, which you then have to laboriously convert to a class, you just define the class, make the query column names match the field names, and the parameters are bound easily by name. You already have the TankReading
class defined, so it will be really simple!
using Dapper;
// Below can be SqlConnection cast to DatabaseConnection, too.
DatabaseConnection connection = // whatever
IEnumerable<TankReading> tankReadings = connection.Query<TankReading>(
"SELECT * from TankReading WHERE Value = @value",
new { value = "tank1" } // note how `value` maps to `@value`
);
return tankReadings;
Now isn't that awesome? Dapper is very optimized and will give you darn near equivalent performance as reading directly with a DataAdapter
.
If your class has any logic in it at all or is immutable or has no parameterless constructor, then you probably do need to have a DbTankReading
class (as a pure POCO/Plain Old Class Object):
// internal because it should only be used in the data source project and not elsewhere
internal sealed class DbTankReading {
int TankReadingsID { get; set; }
DateTime? ReadingDateTime { get; set; }
int ReadingFeet { get; set; }
int ReadingInches { get; set; }
string MaterialNumber { get; set; }
string EnteredBy { get; set; }
decimal ReadingPounds { get; set; }
int MaterialID { get; set; }
bool Submitted { get; set; }
}
You'd use that like this:
IEnumerable<TankReading> tankReadings = connection
.Query<DbTankReading>(
"SELECT * from TankReading WHERE Value = @value",
new { value = "tank1" } // note how `value` maps to `@value`
)
.Select(tr => new TankReading(
tr.TankReadingsID,
tr.ReadingDateTime,
tr.ReadingFeet,
tr.ReadingInches,
tr.MaterialNumber,
tr.EnteredBy,
tr.ReadingPounds,
tr.MaterialID,
tr.Submitted
});
Despite the mapping work, this is still less painful than the data table method. This also lets you perform some kind of logic, though if the logic is any more than very simple straight-across mapping, I'd put the logic into a separate TankReadingMapper
class.
Solution 4
If you want to convert any DataTable to a equivalent IEnumerable vector function.
Please take a look at the following generic function, this may help your needs (you may need to include write cases for different datatypes based on your needs).
/// <summary>
/// Get entities from DataTable
/// </summary>
/// <typeparam name="T">Type of entity</typeparam>
/// <param name="dt">DataTable</param>
/// <returns></returns>
public IEnumerable<T> GetEntities<T>(DataTable dt)
{
if (dt == null)
{
return null;
}
List<T> returnValue = new List<T>();
List<string> typeProperties = new List<string>();
T typeInstance = Activator.CreateInstance<T>();
foreach (DataColumn column in dt.Columns)
{
var prop = typeInstance.GetType().GetProperty(column.ColumnName, BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public);
if (prop != null)
{
typeProperties.Add(column.ColumnName);
}
}
foreach (DataRow row in dt.Rows)
{
T entity = Activator.CreateInstance<T>();
foreach (var propertyName in typeProperties)
{
if (row[propertyName] != DBNull.Value)
{
string str = row[propertyName].GetType().FullName;
if (entity.GetType().GetProperty(propertyName).PropertyType == typeof(System.String))
{
object Val = row[propertyName].ToString();
entity.GetType().GetProperty(propertyName, BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public).SetValue(entity, Val, BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public, null, null, null);
}
else if (entity.GetType().GetProperty(propertyName).PropertyType == typeof(System.Guid))
{
object Val = Guid.Parse(row[propertyName].ToString());
entity.GetType().GetProperty(propertyName, BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public).SetValue(entity, Val, BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public, null, null, null);
}
else
{
entity.GetType().GetProperty(propertyName, BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public).SetValue(entity, row[propertyName], BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public, null, null, null);
}
}
else
{
entity.GetType().GetProperty(propertyName, BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public).SetValue(entity, null, BindingFlags.NonPublic | BindingFlags.Instance | BindingFlags.Public, null, null, null);
}
}
returnValue.Add(entity);
}
return returnValue.AsEnumerable();
}
Solution 5
Simple method using System.Data.DataSetExtensions
:
table.AsEnumerable().Select(row => new TankReading{
TankReadingsID = Convert.ToInt32(row["TRReadingsID"]),
TankID = Convert.ToInt32(row["TankID"]),
ReadingDateTime = Convert.ToDateTime(row["ReadingDateTime"]),
ReadingFeet = Convert.ToInt32(row["ReadingFeet"]),
ReadingInches = Convert.ToInt32(row["ReadingInches"]),
MaterialNumber = row["MaterialNumber"].ToString(),
EnteredBy = row["EnteredBy"].ToString(),
ReadingPounds = Convert.ToDecimal(row["ReadingPounds"]),
MaterialID = Convert.ToInt32(row["MaterialID"]),
Submitted = Convert.ToBoolean(row["Submitted"]),
});
Or:
TankReading TankReadingFromDataRow(DataRow row){
return new TankReading{
TankReadingsID = Convert.ToInt32(row["TRReadingsID"]),
TankID = Convert.ToInt32(row["TankID"]),
ReadingDateTime = Convert.ToDateTime(row["ReadingDateTime"]),
ReadingFeet = Convert.ToInt32(row["ReadingFeet"]),
ReadingInches = Convert.ToInt32(row["ReadingInches"]),
MaterialNumber = row["MaterialNumber"].ToString(),
EnteredBy = row["EnteredBy"].ToString(),
ReadingPounds = Convert.ToDecimal(row["ReadingPounds"]),
MaterialID = Convert.ToInt32(row["MaterialID"]),
Submitted = Convert.ToBoolean(row["Submitted"]),
};
}
// Now you can do this
table.AsEnumerable().Select(row => return TankReadingFromDataRow(row));
Or, better yet, create a TankReading(DataRow r)
constructor, then this becomes:
table.AsEnumerable().Select(row => return new TankReading(row));
mpenrow
Professional software developer. .NET code as a day job. Very interested in Ruby and Rails in my spare time.
Updated on July 09, 2022Comments
-
mpenrow almost 2 years
I am trying to convert a DataTable to an IEnumerable. Where T is a custom type I created. I know I can do it by creating a
List<T>
but I was thinking if there is a slicker way to do it using IEnumerable. Here is what I have now:private IEnumerable<TankReading> ConvertToTankReadings(DataTable dataTable) { var tankReadings = new List<TankReading>(); foreach (DataRow row in dataTable.Rows) { var tankReading = new TankReading { TankReadingsID = Convert.ToInt32(row["TRReadingsID"]), TankID = Convert.ToInt32(row["TankID"]), ReadingDateTime = Convert.ToDateTime(row["ReadingDateTime"]), ReadingFeet = Convert.ToInt32(row["ReadingFeet"]), ReadingInches = Convert.ToInt32(row["ReadingInches"]), MaterialNumber = row["MaterialNumber"].ToString(), EnteredBy = row["EnteredBy"].ToString(), ReadingPounds = Convert.ToDecimal(row["ReadingPounds"]), MaterialID = Convert.ToInt32(row["MaterialID"]), Submitted = Convert.ToBoolean(row["Submitted"]), }; tankReadings.Add(tankReading); } return tankReadings.AsEnumerable(); }
The key part being I am creating a
List<T>
then returning it usingAsEnumerable()
. -
ChocoMartin about 13 yearsOooh, nice. I hadn't noticed
dataTable.AsEnumerable()
, and was always doing the longer, uglier:dataTable.Rows.Cast<DataSetName.SomeLongDataTableRowName>()
-
jhappoldt over 11 yearsThe
AsEnumerable
extension method is found in theSystem.Data
namespace but be sure to reference theSystem.Data.DataSetExtensions
assembly to use it. -
Der_Meister over 8 yearsUse .Cast<DataRowView>() extension for data views.
-
codeMonkey over 5 yearsIf you're using an old version of .NET, you can also do
dataTable.Cast<DataRow>()
to get an IEnumerable<DataRow>.