Convert Datatable to Ienumerable<T>

Convert DataTable to IEnumerableT

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>

Convert DataTable to IEnumerableT in ASP.NET Core 2.0

One of the most efficient things you can do is write the code for the iteration yourself, using a for loop instead of LINQ, just iterate over the DataTable rows and build/hydrate the IEnumerable<UserAssignmentDto> method return value "by hand".

Since there DataTable does not implement an Enumerator in .NET Core 2.0 you will need to use a "regular" for loop to iterate over the rows. You cannot use foreach because DataTable has not implemented IEnumerable in .NET Core 2.0.

public static IEnumerable<UserAssignmentDto> StaffAssignmentsUsingStoredProcedure(System.Data.DataTable dataTable)
{
    var retList = new List<UserAssignmentDto>();

    for(int i = 0; i < dataTable.Rows.Count; i++)
    {
          var row = dataTable.Rows[i];

          var temp = new UserAssignmentDto(){
              Id = row["AssignmentNumber"],
              Position = row["EsrPositionTitle"]
          };

          retList.Add(temp);     
    }

    return retList;
}

Convert Datatable to generic IEnumerableT

You could create an extension method that convert it for you. Given you have properties on the query that matches the properties on your generic T type, you could use reflection to perform it! For sample (see the comments on the code):

public static class DataTableExtensions
{
    public static IEnumerable<T> ToGenericList<T>(this DataTable dataTable)
    {
        var properties = typeof(T).GetProperties().Where(x => x.CanWrite).ToList();

        var result = new List<T>();

        // loop on rows
        foreach (DataRow row in dataTable.Rows)
        {
            // create an instance of T generic type.
            var item = Activator.CreateInstance<T>();

            // loop on properties and columns that matches properties
            foreach (var prop in properties)
                foreach (DataColumn column in dataTable.Columns)                    
                    if (prop.Name == column.ColumnName)
                    {
                        // Get the value from the datatable cell
                        object value = row[column.ColumnName];

                        // Set the value into the object
                        prop.SetValue(item, value);
                        break;
                    }

            result.Add(item);
        }

        return result;
    }
}

And given you have a model like this:

public class Student 
{
    public int Id { get; set; } 
    public string Code { get; set; } 
    public string FirstName { get; set; } 
    public string LastName { get; set; } 
    // other possible properties
}

You can use the extension method like this:

protected async Task<IEnumerable<T>> QuerySqlCmdReadRows<T>(string sqlCommand)
{
    using (var con = new SqlConnection(Connection.ToString()))
    {
        con.Open();
        using (var cmd = con.CreateCommand())
        {
            cmd.CommandText = sqlCommand;

            DataTable dtResult = new DataTable();

            using (var reader = await cmd.ExecuteReaderAsync())
               dtResult.Load(reader);

            return dtResult.ToGenericList<T>();
        }
    }
}

// just and sample
var students = QuerySqlCmdReadRows<Students>("select Id, Code, FirstName, LastName from Students");

Converting Datatable to ienumerableanonymous?

var results = from row in dataTable.AsEnumerable()
              select new {
                  First = row.Field<string>("First"),
                  Last = row.Field<string>("Second")
              };

You'll need System.Data.DataSetExtensions.

Convert datatable to IEnumerableT to pass to ResultT(IEnumerableT data);

I ended up using Linq to convert the datatable into an array of anonymous objects.

Dim objs = From row In dt Select New With {
    .Id = row.Field(Of Integer)("Id"),
    .Count = row.Field(Of Integer)("Count")
}

Then I created a generic function to use type inference to get the anonymous object array into the Result object's constructor

Private Function GetResult(Of T)(ByVal data As IEnumerable(Of T)) As Result(Of T)
    Return New Result(Of T)(Nothing, data)
End Function

Called using Dim result = GetResult(objs)

Error Converting Datatable to IEnumerable

You are getting that error because dtRepSummary.AsEnumerable() returns IEnumerable<DataRow> but you have the return type as IEnumerable<Landing>, you can use Select to project your type like this:-

return dtRepSummary.AsEnumerable().Select(x => new Landing
                                        {
                                           Reportid = x.Field<int>("Reportid"), 
                                           Reportdate = x.Field<string>("Reportdate"),
                                           ..and so on
                                        };

An efficient way to convert from IEnumerableT to DataTable

Instead of going through a DataTable, I would implement an IDataReader for your collection and feed that to SqlBulkCopy. If done correctly, and using a lazy IEnumerable, it would be much faster, and use much less memory than the datatable route. Mark Gravell has already written such a library for converting IEnumerables to an IDataReader, and I would recommend you check that out before rolling your own.

FastMember can be found on NuGet here: https://www.nuget.org/packages/FastMember/
with the original source found here: https://code.google.com/p/fast-member/ with an example in this thread here: SqlBulkCopy from a List<>

UPDATE:
You may also need to change your command timeout, and set the batch size on the sqlbulkcopy, like this:

using (SqlCommand cmd = new SqlCommand(modelManager.GetInsertSproc(), sqlconn) { 
  CommandType = CommandType.StoredProcedure, CommandTimeout=300 })

and

bcp.BatchSize = 100000;

---

Related Topics