How to associate a Dapper query result with a WPF DataGrid

Question

Here is my code. It creates a linked grid with the correct number of rows, however the cells are empty .

Xaml

<DataGrid Name="grid" ItemsSource="{Binding}" AutoGenerateColumns="True" /> 

Code for

 grid.DataContext = cn.Query("select * from SomeTable"); 

Answer 1

In docs, your syntax there - cn.Query("sql") - returns a list of objects with dynamic typing ( IEnumerable<dynamic> ). This will not work for automatic DataGrid columns that are looking for specific elements to generate their columns. I would suggest creating a simple entity class for SomeTable to map properties, and then using cn.Query<SomeTableEntity>("select * from SomeTable"); .

Answer 2

So, I think the answer is this: this is not possible. Here is the hacker solution.

  ... var items = cn.Query("select * from SomeTable"); grid.DataContext = ConvertToDataTable(items); } public DataTable ConvertToDataTable(IEnumerable<dynamic> items) { var t = new DataTable(); var first = (IDictionary<string, object>)items.First(); foreach (var k in first.Keys) { var c = t.Columns.Add(k); var val = first[k]; if (val != null) c.DataType = val.GetType(); } foreach (var item in items) { var r = t.NewRow(); var i = (IDictionary<string, object>)item; foreach (var k in i.Keys) { var val = i[k]; if (val == null) val = DBNull.Value; r[k] = val; } t.Rows.Add(r); } return t; } 

Answer 3

If you are using a non-generic version of Query, it returns a dynamic representation of the data. The dynamic API is not suitable for most user interface data bindings. It would be preferable to use the generic Query<T> API to load data into types with specific properties.

With a full push, it is also theoretically possible to implement ITypedList for data and display properties accordingly. But this is quite a bit of work for not so much gain.

Answer 4

This is not possible with pure Linq! It is possible to reuse an OLD dataset and convert back to linq using System.Data.DataSetExtensions. (Not elegant, but it works)

 // Steal concrete connection from Linq ModelDEmoWPFContainer l_ctx = new ModelDEmoWPFContainer(); var l_connection = (System.Data.EntityClient.EntityConnection)l_ctx.Connection) .StoreConnection; System.Data.SqlClient.SqlCommand l_cmd = new System.Data.SqlClient.SqlCommand(query_arg); l_cmd.Connection = (System.Data.SqlClient.SqlConnection) l_connection; System.Data.SqlClient.SqlDataAdapter l_da = new System.Data.SqlClient.SqlDataAdapter(l_cmd); System.Data.DataSet l_ds = new System.Data.DataSet(); l_da.Fill(l_ds); 

CLONE Metadata

 System.Data.DataTable l_dt = l_ds.Tables[0].Clone(); 

return to linq through System.Data.DataSetExtensions

 var dt = (from data in l_ds.Tables[0].AsEnumerable() select data).ToList(); foreach (DataColumn column in l_dt.Columns) { var binding = new Binding(string.Format("[{0}]", column.Ordinal)); datagrid.Columns.Add(new DataGridTextColumn() { Header = column.ColumnName, Binding = binding }); } datagrid.ItemsSource = dt; 

Answer 5

If you are using the MVVM pattern, I think this is the best solution:

Create an ObservableCollecion in your ViewModel, which will be bound to the ItemSource in the DataGrid :

 public ObservableCollection<T> bindableCollection; 

Then retrieve data from your database, for example:

 public void RefreshDataGrid(){ using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings[ "RecipeManager.Properties.Settings.RecipeManagerConnectionString"].ConnectionString)){ var fetchedData = conn.Query<Flavour>("select * from [Flavour]"); ConvertToDataTable(fetchedData); } } 

The final step and the most important create function is to add IEnumerable to your ObservableCollection:

 private void ConvertToObservableCollection(IEnumerable<Flavour> items){ ObservableCollection<Flavour> flavours = new ObservableCollection<Flavour>(); foreach (var item in items){ Flavour flavour = item as Flavour; flavours.Add(new Flavour(flavour.Name,flavour.Company,flavour.Shop,flavour.Amount)); } Flavours = flavours; } 

I think this is a good approach for mvvm.