Keith Yanachik

SQL Reports is a wonderful tool for creating and publishing enterprise reports, yet it’s Web Forms viewer has it’s drawbacks such as requiring an Active-X control for printing and it’s not available in ASP.Net MVC.

A good solution to utilizing SQL Reports in an ASP.Net MVC application is to use the SQL Reports Web Services API to render the report as a PDF, Excel, or CSV file and to have your action method return the rendered report as a Filestream to the browser.

The source code for this project is available on a Subversion server here:  http://code.yanachik.org/public/MVCSQLReportDemo/

The steps involved are:

1. Create a Web Reference to the SQL Reports Report Execution Web Service.
2. Create an Action Method that returns a FileStreamResult.
3. Create a proxy to the Web Service and set the credentials.
4. Render the report.
5. Return the report as a file stream.

Create a Web Reference to the SQL Reports Report Execution Web Service

In your ASP.Net MVC project add a Web-Reference to the Report Execution Web Reference to the following URL:  http://REPORTSERVER/ReportServer/ReportExecution2005.asmx and name it rsExecService.  Even if you’re using SQL Reports 2008, the Web Reference is still set for 2005.

To do this, right-click on the project in Visual Studio and select Add Web Reference…  The “Add Web Reference” dialog will appear.  Fill in the URL to your SQL Reports Report Execution Web Service and hit enter or click on the Go button.  Once the Web Service is found you fill out the Web Reference Name with rsExecService and click the Add Reference button.

Create an Action Method that returns a FileStreamResult

[System.Web.Mvc.OutputCache(NoStore = true, Duration = 0, VaryByParam = "*")]
public FileStreamResult PrintReport(string id)
{

}

The System.Web.Mvc.OutputCache attribute tells your browser to not cache the report.  If you have static data you can remove this attribute.

Create a proxy to the Web Service and set the credentials

// Create a new proxy to the web service
rsExecService.ReportExecutionService rsExec = new rsExecService.ReportExecutionService();

// Authenticate to the Web service using Windows credentials
rsExec.Credentials = new System.Net.NetworkCredential("USERNAME", "Password", "DOMAIN");

Render the report

When loading the report, the file name is the full path for the report.  In this case “Test Report” is at the root of the Report Server.  If it was in a folder then the path would be “/Folder 1/Folder 2/Report Name”.

// Load the selected report.
rsExec.LoadReport("/Test Report", null);

// Prepare Render arguments
string encoding = String.Empty;
string mimeType = String.Empty;
string extension = String.Empty;
rsExecService.Warning[] warnings = null;
string[] streamIDs = null;

// Prepare report parameters.
rsExecService.ParameterValue[] parameters = new rsExecService.ParameterValue[1];
parameters[0] = new rsExecService.ParameterValue();
parameters[0].Value = id;
parameters[0].Name = "id";

// Set the parameters for the report needed.
rsExec.SetExecutionParameters(parameters, "en-us");

//Render the report to a byte[]
Byte[] byteInfo = rsExec.Render("PDF", null, out extension, out encoding, out mimeType, out warnings, out streamIDs);

Return the report as a file stream.

System.IO.MemoryStream ms = new System.IO.MemoryStream();
ms.Write(byteInfo, 0, byteInfo.Length);
ms.Position = 0;

HttpContext.Response.AddHeader("content-disposition", "attachment; filename=FILENAME.pdf");

return new FileStreamResult(ms, "application/pdf");

The final Action Method looks like this:

[System.Web.Mvc.OutputCache(NoStore = true, Duration = 0, VaryByParam = "*")]
public FileStreamResult PrintReport(string id)
{
    // Create a new proxy to the web service
    rsExecService.ReportExecutionService rsExec = new rsExecService.ReportExecutionService();

    // Authenticate to the Web service using Windows credentials
    rsExec.Credentials = new System.Net.NetworkCredential("USERNAME", "Password", "DOMAIN");

    // Load the selected report.
    rsExec.LoadReport("/Test Report", null);

    // Prepare Render arguments
    string encoding = String.Empty;
    string mimeType = String.Empty;
    string extension = String.Empty;
    rsExecService.Warning[] warnings = null;
    string[] streamIDs = null;

    // Prepare report parameters.
    rsExecService.ParameterValue[] parameters = new rsExecService.ParameterValue[1];
    parameters[0] = new rsExecService.ParameterValue();
    parameters[0].Value = id;
    parameters[0].Name = "id";

    // Set the parameters for the report needed.
    rsExec.SetExecutionParameters(parameters, "en-us");

    //Render the report to a byte[]
    Byte[] byteInfo = rsExec.Render("PDF", null, out extension, out encoding, out mimeType, out warnings, out streamIDs);

    System.IO.MemoryStream ms = new System.IO.MemoryStream();
    ms.Write(byteInfo, 0, byteInfo.Length);
    ms.Position = 0;

    HttpContext.Response.AddHeader("content-disposition", "attachment; filename=FILENAME.pdf");

    return new FileStreamResult(ms, "application/pdf");
}

After writing Unit tests lately I’ve been trying to test my business logic (which is all done in the Entity Manager) without actually submitting data to the database.  I’ll create my Items and Customers and do an InsertOnSubmit to get them into the Entity Manager context, but never call SubmitChanges().

This gets the data into the database, however, it does not get the objects into the EntityManager’s data context.  If I were to create a Customer Order and then create a Customer Order Item I can then Add the Customer Order Item to the Customer Order and the order item is then available within the Customer Order context, but the Customer Order is still not available within the Entity Manager context.  To do that, you need to Attach the Customer Order to the CustomerOrders table.

With all of the goodies included with Service Pack 1 for Visual Studio 2008, the one thing that Microsoft did not do right is Javascript formatting.  First of all, it’s really slow.  Second of all, it doesn’t seem to be consistent with it’s indenting.

For me, I’d like to do my own formatting.  So, to turn off the Javascript formatting in Visual Studio 2008 go to Tools – Options – Text Editor – JScript – Formatting.  And uncheck all of the checkboxes in the Automatic Formatting group.