View X Dapper Reports

Question

I download ReportDataSource using Dapper. However, I have an empty report even with IEnumerable data loaded. When you do Datatable work.

How to transfer data from a query using Dapper for ReportViewer ?

 this.reportViewer.LocalReport.DataSources.Clear(); DataTable dt = new DataTable(); dt = CN.Query(Sql, param); Microsoft.Reporting.WinForms.ReportDataSource rprtDTSource = new Microsoft.Reporting.WinForms.ReportDataSource(dt.TableName, dt); this.reportViewer.LocalReport.DataSources.Add(rprtDTSource); this.reportViewer.RefreshReport(); – 

Answer 1

Since I could not find another way to submit my ReportViewer to a Dapper.Query query, then I downloaded the source code and added the code below.

  #region CODTEC SISTEMAS /// <summary> /// Return a typed list of objects, reader is closed after the call /// </summary> public static DataTable Query(this IDbConnection cnn, string sql, object param, IDbTransaction transaction, int? commandTimeout, CommandType? commandType) { var identity = new Identity(sql, commandType, cnn, typeof(DapperRow), param == null ? null : param.GetType(), null); var info = GetCacheInfo(identity); IDbCommand cmd = null; IDataReader reader = null; bool wasClosed = cnn.State == ConnectionState.Closed; try { cmd = SetupCommand(cnn, transaction, sql, info.ParamReader, param, commandTimeout, commandType); if (wasClosed) cnn.Open(); reader = cmd.ExecuteReader(wasClosed ? CommandBehavior.CloseConnection : CommandBehavior.Default); wasClosed = false; // *if* the connection was closed and we got this far, then we now have a reader // with the CloseConnection flag, so the reader will deal with the connection; we // still need something in the "finally" to ensure that broken SQL still results // in the connection closing itself DataTable dt = new DataTable(); dt.Load(reader); // happy path; close the reader cleanly - no // need for "Cancel" etc reader.Dispose(); reader = null; return dt; } finally { if (reader != null) { if (!reader.IsClosed) try { cmd.Cancel(); } catch { /* don't spoil the existing exception */ } reader.Dispose(); } if (wasClosed) cnn.Close(); if (cmd != null) cmd.Dispose(); } } #endregion 

Answer 2

It looks like Dapper now supports DataTable ...

From test :

 public void ExecuteReader() { var dt = new DataTable(); dt.Load(connection.ExecuteReader("select 3 as [three], 4 as [four]")); dt.Columns.Count.IsEqualTo(2); dt.Columns[0].ColumnName.IsEqualTo("three"); dt.Columns[1].ColumnName.IsEqualTo("four"); dt.Rows.Count.IsEqualTo(1); ((int)dt.Rows[0][0]).IsEqualTo(3); ((int)dt.Rows[0][1]).IsEqualTo(4); } 

Also now supported uses a DataTable as a TableValueParameter:

 public void DataTableParameters() { try { connection.Execute("drop proc #DataTableParameters"); } catch { } try { connection.Execute("drop table #DataTableParameters"); } catch { } try { connection.Execute("drop type MyTVPType"); } catch { } connection.Execute("create type MyTVPType as table (id int)"); connection.Execute("create proc #DataTableParameters @ids MyTVPType readonly as select count(1) from @ids"); var table = new DataTable { Columns = { { "id", typeof(int) } }, Rows = { { 1 }, { 2 }, { 3 } } }; int count = connection.Query<int>("#DataTableParameters", new { ids = table.AsTableValuedParameter() }, commandType: CommandType.StoredProcedure).First(); count.IsEqualTo(3); count = connection.Query<int>("select count(1) from @ids", new { ids = table.AsTableValuedParameter("MyTVPType") }).First(); count.IsEqualTo(3); try { connection.Query<int>("select count(1) from @ids", new { ids = table.AsTableValuedParameter() }).First(); throw new InvalidOperationException(); } catch (Exception ex) { ex.Message.Equals("The table type parameter 'ids' must have a valid type name."); } } 

Answer 3

You don’t know how Dapper works, but the data type bound to it is bound as follows:

 DataTable dt = new DataTable(); DataColumn dc = dt.Columns.Add(); dc.ColumnName = "DataColumn1"; dc = dt.Columns.Add(); dc.ColumnName = "DataColumn2"; dt.Rows.Add(new object[] { "Frank", 32 }); this.reportViewer1.LocalReport.DataSources.Clear(); this.reportViewer1.LocalReport.DataSources.Add(new ReportDataSource("DataSet1_DataTable1", dt)); this.reportViewer1.RefreshReport(); 

and since I am using C #, I am linking data sources as follows:

 this.bindingSource1.DataSource = getListMethod(); // this method/property returns a list of objects this.reportViewer1.LocalReport.DataSources.Add(new ReportDataSource("Point", (this.bindingSource1))); //Point is the datatable name in my Dataset.xsd file