Linq Convert DataContext to DataTable

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

Code:

---


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.

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.

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

This is my current code:

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.

Quote:

---

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.

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%