Using Dapper with SharpShooter

 
Dapper is a “micro ORM” created for stackoverflow.com and they heavily use it in places where performance matters. It’s not a full blown mapper and doesn’t support relationsips and wouldn’t manage your connection life cycle etc – but it can map SQL queries to business objects.
 
And you know what, it does mapping better than anyone else. Dapper is much faster and more flexible than Linq2Sql and Entity Framework – and in this post we’ll see how to use it as a data provider for SharpShooter reports.

How to use Dapper.

Dapper unobtrusively extends IDbConnection and provides strongly typed result sets for database queries, somewhat like this:

   string sql = "SELECT * FROM Customers WHERE Id = @id";
   List<Customer> customers = connection.Query<Customer>(sql, new {id});

It’s just a single file of about 2K lines of code – just drop it into your project and move on.

DapperDemo project.

For your amusement, I created a small DapperDemo project which uses Dapper to generate a SharpShooter report with a list of tables from msdb database.
 
(This straightforward task doesn’t need any particular setup and yet, however, describes the use case reasonably well.)
 

 
The section below lists the steps you would probably go through creating the above report using Dapper as your data access layer.

SharpShooter report with Dapper, in 5 steps.

As with any report which uses something other than DataTable or DataSet as a data source, the main SharpShooter issue is that data schema would not be available at design time.
 
And so we’re left with two choices: either design the report from Visual Studio but remember all the column names – or, alternatively, run the application and design the template at runtime.
 
The second solution seems easier, and it becomes even more appealing if we use a FileReportSlot. Why? Because we then can design the template and save it under the same name as we provided at design time, and once we’ve saved it we have a fully working report!

1. So here we go: having created a WinForms application, we drag ReportManager (which would control everything related to report processing) and ReportViewer (which would render the report) and drop them on the main from. Then we create a FileReportSlot for the manager:
    
   

    
   Then we specify the report slot we just created, as Source of our report viewer:
    

   

    

2. Then we go and copy Dapper from its GitHub repository and save the file in our project. (It’s also possible to setup that via Nuget, too!)
    
3. Then we define the “business object” class (assuming we want to retrieve table name and identifier)

      public class Table
      {
         public int Id { get; set; }
         public string Name { get; set; }
      }
   

   ..and write the following code for Form_Load:

      private void Form1_Load(object sender, EventArgs e)
      {
         var tables = Select<Table>(@"SELECT 
                                          name AS Name, 
                                          object_id AS Id 
                                      FROM sys.tables 
                                      ORDER BY name");
         reportManager1.DataSources.Add("tables", tables);
                           
         fileReportSlot1.DesignTemplate();
         reportViewer1.RefreshReport();
      }
   
      private static IEnumerable<T> Select<T>(string query)
      {
         var connectionString = ConfigurationManager.AppSettings["ConnectionString"];
         using (var connection = new SqlConnection(connectionString)) {
            connection.Open();
            return connection.Query<T>(query);   //Dapper call here!
         }
      }
   

   As you can see, in Form_Load we make sure we have a chance to properly design the report before Report Viewer renders it.
    

4. Now we simply run the application with Ctrl+F5 and, no wonder, find ourselves in Report Designer. We drag-n-drop DataBand and select data source for it – notice that we have our “tables” data source there, and now Designer knows column names!
    
   

    

5. Now we drag-n-drop TextBox on the data band, and select tables.Name as the formula for its value:
    
   

    

The above steps are complete. After we hit Ctrl+S and close Report Designer, we’d see the main form with Report Viewer displaying the correct report:
 

Summary.

There are two key takeaways for this story.
 
Firstly, we have seen that it is pretty easy to use Dapper as DAL. It gives you more control over the generated SQL, comparing to Linq2Sql or Entity Framework, – which is especially important for performance-critical queries.
 
Secondly, we have seen that “alternative” data sources in SharpShooter (ie., anything not IListSource, I guess?) make it nearly impossible to design reports from Visual Studio because data schema is available at runtime only. While this is extremely inconvenient, there’s a workaround where you use FileReportSlot, call DesignTemplate before the report gets rendered, and save the properly designed (at runtime) template under the name you provided at design time.
 
As usual, you can flick though source code for the above project – give it a try, play with it and let us know what you think!