2

I want to be able to enter SQL into a textbox and display the results in a WPF Datagrid. I thought to start with an SqlDataReader, and set the datagrid's ItemsSource to the data reader:

using (var cmd = conn.CreateCommand()) {
    cmd.CommandText = sql.Text;
    sqlResults.ItemsSource = cmd.ExecuteReader();
}

but this fails with the following error: Invalid attempt to call FieldCount when reader is closed, which I understand to mean that by the time WPF gets around to reading the FieldCount property of the row object, the using block has already been exited.

So I tried using LINQ and ToList, to get something that would persist in memory:

sqlResults.ItemsSource = cmd.ExecuteReader().Cast<DbDataRecord>().ToList();

but this only displays the 'FieldCount' for each row, which is apparently the only property which DbDataRecord has.

Some solutions I have considered:

  • Bind to a DataTable instead of a DataReader? But I don't need editing capabilities.
  • Select each row into an in-memory data structure? What data structure could I use? I can't use anonymous types, because the names and types of the columns change based on the SQL statement. If I use List<object>, how will the datagrid know to generate columns for each object in the list?
  • Create a custom type descriptor? It seems like overkill.

But I feel the solution should be very simple and easy. Am I missing something basic here?

Zev Spitz
  • 13,950
  • 6
  • 64
  • 136
  • Maybe a DataTable is overkill but it does what you need it to do. If you don't need edit then DataGrid is also overkill. – paparazzo Dec 11 '13 at 22:30
  • @Blam Can I use a ListView, or something simpler? – Zev Spitz Dec 11 '13 at 22:34
  • For multiple columns then ListView GridView. It does not have AutoGenerateColumns but it is much more lightweight. My opinion is DataGrid is more overhead than DataTable. What I do is a struct with column collection and then manually build the columns in code behind. – paparazzo Dec 11 '13 at 22:55
  • http://stackoverflow.com/questions/6959600/wpf-display-grid-of-results-with-dynamic-columns-rows/6961874#6961874 – paparazzo Dec 11 '13 at 23:00
  • @Blam Since I ended up using a DataTable, could you rewrite your comments as an answer so I can accept it? – Zev Spitz Dec 23 '13 at 22:23

2 Answers2

2

Maybe a DataTable is overkill but it does what you need it to do.

paparazzo
  • 44,497
  • 23
  • 105
  • 176
-1

Well, yep, from what you've said, I would probably go with a struct containing properties that match the DB columns, and just fill that with the linq, so you can easily bind to it.

Working more with JavaScript, and wondering if you can find an equivalent to the Eval (which is suggested never to be used by the creator, btw :), and if that would help ...

Noctis
  • 11,507
  • 3
  • 43
  • 82
  • 1
    1) As I mentioned in the question, I am taking an arbitrary SQL statement, so I don't know the fields and datatypes of the incoming data. What sort of struct would I map it to? 2) How could I possibly use `eval` here? – Zev Spitz Dec 11 '13 at 22:25
  • 2) Eval is from JavaScript. It will not be useful here. 1) Must have missed that... that would leave you with reflection and lots of dirty work ... Probably should remove my answer, and wait for Skeet to pop up (you should be in bed ... it's late in israel :) ) – Noctis Dec 11 '13 at 22:36
  • Brendan Eich recommends avoiding `eval`? Do you have a source for that? – Zev Spitz Dec 11 '13 at 22:36
  • Seems it was **Douglas Crockford**, around here: – Noctis Dec 11 '13 at 22:40