0

I am trying to do return dataTable value from IEnumerable Type Anyone can help me For if i will pass any type for IEnumerable it will return that value 

IEnumerable<T> enrichment = null;
var dataTable = new DataTable();

using (ReconContext context = new ReconContext())
{
    string sql = "SELECT * FROM " + DestinationTable + "  WHERE LoadId =" + ExternalLoadId;

    using (SqlDataAdapter adapater = new SqlDataAdapter(sql, context.Database.Connection.ConnectionString))
    {
        adapater.Fill(dataTable);
    }
}
return enrichment;
Cihan Uygun
  • 2,128
  • 1
  • 16
  • 26
user3062169
  • 1
  • 4
  • It's not clear at all what `enrichment` is for or how it relates to the `DataTable`. – D Stanley Sep 29 '16 at 13:46
  • It's also not clear what this has to do with `Linq-To-Entities` or what type `T` is. Do you want a generic method that converts a `DataTable` to `IEnumerable` where `T` is a custom class? Then there are already available approaches using reflection like [this](http://stackoverflow.com/questions/12662318/how-to-convert-datatable-to-listt-using-reflections). – Tim Schmelter Sep 29 '16 at 13:55
  • That's a SQL Injection attack waiting to happen. I wonder, what will happen if I pass ` 2 OR 1=1--` as a value to `ExternalLoadId` – Panagiotis Kanavos Sep 29 '16 at 14:06
  • Why do you use a DataTable and SqlDataAdapter when you already have an EF Context? Why don't you use [SqlQuery](https://msdn.microsoft.com/en-us/data/jj592907.aspx) method? – Panagiotis Kanavos Sep 29 '16 at 14:12

3 Answers3

1

First of all you do not need to use generic definition unless you really need to. For general circumstances you can easily use DataRow like below;

IEnumerable<DataRow> enrichment = null;
var dataTable = new DataTable();
using (ReconContext context = new ReconContext())
{
    string sql = "SELECT * FROM " + DestinationTable + "  WHERE LoadId =" + ExternalLoadId;

    using (SqlDataAdapter adapater = new SqlDataAdapter(sql, context.Database.Connection.ConnectionString))
    {
        adapater.Fill(dataTable);
    }
}

enrichment = dataTable.AsEnumerable();
return enrichment;

Hope this helps.

Cihan Uygun
  • 2,128
  • 1
  • 16
  • 26
  • Ya this is right but if my function like this the public IEnumerable GeEnrichmendataByTableNameAndLoadId(string DestinationTable, int ExternalLoadId) { } – user3062169 Sep 29 '16 at 13:55
  • So, for this circumstance your SqlDataAdapter should fill datatable with type of T if not you should create a method which converts your data table to strongly type which type of T. – Cihan Uygun Sep 29 '16 at 13:59
0

Entity Framework already allows you to execute raw SQL queries and map their results to entities with SqlQuery. You don't need to use raw ADO.NET. You could just write:

public IEnumerable<T> LoadSomeLoads<T>(string table,int loadId)
{
    using (ReconContext context = new ReconContext())
    {
       var sq;="SELECT * FROM "+ table+ "  WHERE LoadId = @p0";
       var results= context.SqlQuery<T>(sql,loadId)
                           .ToArray();
    }
}

You should validate the table parameter to ensure it's an allowed table name, otherwise someone could query tables he has no authorization to, or simply browse for data by forcing the table name.

You avoid catastrophic injection attacks by using parameters but that doesn't prevent unauthorized disclosure if someone can force a query to an inappropriate table.

I suspect though that you should be looking to table-per-type inheritance. It sounds like you want to load different classes from different tables, where at least one property (LoadId) is common.

With Table-per-Type EF maps different types of an inheritance hierarchy to different tables and selects the correct table based on the mapping configuration.

Assuming, eg that you have the following classes:

public abstract class LoadTypeBase
{
    public int LoadId { get; set; }
    ...
}

[Table("LoadTableA")]
public class LoadTypeA : LoadTypeBase
{
    ...
}


[Table("LoadTableB")]
public class LoadTypeA : LoadTypeBase
{
    ...
}

and this context 

public class ReconContext: DbContext
{
    ...
    public DbSet<LoadTypeBase> Loads { get; set; }
}

You could query for all load types with a specific LoadId with :

var query = from someLoad in context.Loads
            where someLoad.LoadId = loadId
            select someLoad;

You can restrict results to a specific type with OfType<T>()

var query = from someLoad in context.Loads.OfType<LoadTypeB>()
            where someLoad.LoadId = loadId
            select someLoad;
Panagiotis Kanavos
  • 120,703
  • 13
  • 188
  • 236
  • I triyed this one i am getting list of blank datatable not getting rows do you have solution for how willl get the rows – user3062169 Sep 29 '16 at 14:35
  • My code doesn't have any data tables or rows. What did you actually try? Why are you trying to extract data rows when you are already using an ORM ? – Panagiotis Kanavos Sep 29 '16 at 14:41
  • Hey can you see your first answer context.SqlQuery ? that i said you – user3062169 Sep 29 '16 at 14:43
  • Please post what you actually tried and what was the actual result in the question. My answer doesn't use DataRow or DataTable objects anywhere, it doesn't need them. The first query will map to anything that has the same *property* names as the query results. – Panagiotis Kanavos Sep 29 '16 at 14:47
  • Ok leave it using this LoadSomeLoads function how i will return the rows from that table ? what i need to pass LoadSomeLoads function ? – user3062169 Sep 29 '16 at 14:54
  • I'm still waiting for details and explanations. What rows are you talking about? Where is the *actual code* that you tried? Did you use a proper class or `DataRow`? Why `DataRow` when EF already returns the actual class? Why do you want to use `DataRow` when you already use Entity Framework? – Panagiotis Kanavos Sep 29 '16 at 15:24
-1

Ya this is right but if my function will like this the so how i can use datarow i want to out put which i will pass Type that will be retun 

 public IEnumerable<T> GeEnrichmendataByTableNameAndLoadId<T>(stringDestinationTable, int ExternalLoadId)
user3062169
  • 1
  • 4