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.

In Yahoo YUI 2.5.2 the Yahoo DataTable editorSave Event’sData Cell Editor has changed.  To get to the data record for the editor it is no-longer oArgs.editor.record, but rather oArgs.editor.getRecord();

DataTable.subscribe("editorSaveEvent", EditorChanged);

function EditorChanged(oArgs)
{
    var record = oArgs.editor.getRecord();
}

Rails 2.0.2 now defaults your app’s database to SQLite3 (I believe because OSX comes pre-shipped with it).  In order to generate a rails application and having it setup to MySQL is pretty easy.

In your when you create your app add (-d mysql) in the rails command:  rails -d mysql myapp

There were a series of announcements and reviews that came out lately, here’s a sample:

Google Gears has gone mobile – what a great idea.  When your mobile device can’t get a signal (such as on an airplane) but you need to do some work, you can now access your mobile apps though google gears.  Zoho runs on Google Gears, hopefully Zoho Planner is supported.  Unfortunately, Google Gears only runs on IE Mobile.

Yahoo has announced onePlace which is a mobile service designed to take your Yahoo settings and make them available on your mobile device.  Your stock portfolio is available, your favorites, your Flickr account, your My Yahoo feeds – everything is available.  Look for this to come out shortly.

Read Write Web has a good feature comparison between Google Docs and Microsoft Office Workspaces.  Google has everything between email, documents, calendars, etc.  For the life of me I can’t figure out why they don’t have a task tracking system.  Is it too easy?  Too basic for them?  It would be a hot commodity.

Flotzam, a life streaming screensaver based all on Microsoft’s new Windows Presentation Foundation (WPF) put out a contest for redesigned UI.  Here’s a screencast of the winner.

Flotzam Design Contest Winner

Here are feeds to track the action going on at Sharepoint Conference 2008:

• Blogstream (via Technorati): http://feeds.technorati.com/search/SPC2008?authority=a4&language=en
• Photostream (via Flickr): http://api.flickr.com/services/feeds/photos_public.gne?tags=SPC2008&lang=en-us&format=atom
• Videostream (via MSN Soapbox): http://soapbox.msn.com/rss.aspx?searchTerm=SPC2008

You can also monitor:  http://mysharepointcommunity.com/SPC2008

ReadWriteWeb is trying to predict what app will be the big hit at SXSW, and they seem to have concluded that whatever it is, it will involve social networking.  RWW seem impressed with SocialThing, and predict that if they get their iPhone interface working by SXSW then thy’ve got a real chance at being a huge hit this year.  The idea behind the app is to allow you and your friends to keep tabs on what one-another is doing via different services available for life streaming.  From their website:

Get your digital life in order

socialthing! is a digital life manager that puts what you do online into one place. See everything that’s going on with your friends in all the sites you use, post stuff to multiple places at once and more!

Here are some photos from their flickr account:

And their iPhone interface:

I’ve place a badge on my home page that displays my two latest Pownce posts, and dedicated an entire page Pownce Feeds that lists my 30 most recent Pownce posts.  Things that ramble off my brain that don’t deserve a full blog post will get Pownced.