Linq Convert DataContext to DataTable

[Tealc]

I have been searching for several hours and cannot find a way to convert my result to a data table without major hacking. There has to be an easier way. This code fails no matter how many variations I try.


Load from stored procedure

public DataTable Associates_SelectAll()
{
DataTable dTable = new DataTable();
using(dbContext context = new dbContext())
{
var assocQuery = context.Associates_SelectAll();
dTable = (DataTable)assocQuery;
}
return dTable;
}

Please tell me what I need to do to convert this result to a DataTable. A DataSet would also work.

[nelo]

Hi there,

Is there a specific reason you need a DataTable or DataSet? The result of that method call will give a collection that you can iterate over and do whatever you want.

[Tealc]

Because I would like my interface for data providers to return an object I know is compatible with DataGridView.

This is my current code:

public object Associates_SelectAll()
{
using(dbContext context = new dbContext())
{
var associates = dbContext.Associates_SelectAll();
return associates;
}
}

it doesn't work because associates falls out of scope before the return value can be read by the calling code and I can't fix it. I can make it work if I leave out the using() block but then it doesn't get disposed correctly.

Also the returned object gets bound directly into a DataGridView. Because I can't convert the return object to a DataSet I have no idea what object I am binding to the grid and no way to pevent it from failing.

This method is implementing an interface which means my interface has a return type of object also and in the future when someone creates a new data provider using the interface I have no control over what damage they can do to the program because the interface uses an object instead of something a DataSet or DataGrid.

I need to make sure that the type being returned is a type that a DataGridView can bind to.

[nelo]

it doesn't work because associates falls out of scope before the return value can be read by the calling code and I can't fix it. I can make it work if I leave out the using() block but then it doesn't get disposed correctly.

You don't have to have the using block. You can simply execute any clean up operations necessary yourself (i.e. dbContext.Dispose()).

I don't really understand the kind of constraints you are under. Perhaps you can post the interfaces and classes that make up your framework. Anyway maybe you can have a look at the CopyToDataTable method. This is an extension method that can be applied to anything the derives from IEnumerable (dbContext.Associates_SelectAll() should result in an instance of IEnumerable...). So that way you get a DataTable that you can bind to your data grid.

Perhaps an even better solution would be to use a LinqDataSource and bind the DataGridView to the LinqDataSource.

[vahidcompy]

DataSet dsInc = new DataSet();
DataClassesDataContext db = new DataClassesDataContext();
var r = from e in db.Enquiries
where e.CityID == Convert.ToInt32(id)
select new { e.FirstName, e.LastName, e.EnquiryDate };
SqlCommand cmd = db.GetCommand(r) as SqlCommand;
DataTable dataTable = new DataTable();
SqlDataAdapter adapter = new SqlDataAdapter(cmd);
adapter.Fill(dsInc);
return dsInc;

This is working 100%