Raj Kaimal : Data

Rendering an RDLC directly to the Response stream in ASP.NET MVC

rajbk — Wed, 25 Nov 2009 07:21:59 GMT

The following post shows you how to render an RDLC (Client Report Definition File) in a MVC project. For this tutorial, I am using VS 2008 with MVC 2 Beta. I will also be using the priceless Northwind database and the report will contain a list of customers in the Northwind database.

A sample project zip file is provided at the bottom of this post.

We start off by creating an ASP.NET MVC 2 Empty Web Application.

Add a new ADO.NET entity model and choose the option to “Generate from the database”.

Choose the connection string to use

Choose the database objects (Customers for our scenario) and hit finish. We see that a new entity data model has been created in the Models directory.

Create a folder called Content and a subfolder called Reports and add an RDLC into this folder. I hate working with Datasets and hence will not be using the “Report Wizard”. We choose the “Report” template instead.

Create a method that returns all the customers in the database through a Customer partial class and add this in the Model folder.

using System;
using System.Linq;
using System.Collections.Generic;

namespace RDLCRendering.Models
{
    public partial class Customers
    {
        public static List<Customers> GetAllCustomers() {
            var entities = new NorthwindEntities();
            var x = from c in entities.Customers
                    select c;
            return x.ToList();
        }
    }
}

Open the RDLC. Your UI should look like so (If you don’t see “Website Data Sources”, click on Data –> Show Data Sources):

Design your report by adding a table from the toolbox and dragging and dropping fields from the Website Data Sources (reference).

We will now construct our basic MVC web application. The app will have a home screen and a link to view the PDF report of Northwind customers.
Lets start by adding a Home controller

We add the following code in our HomeController class

namespace RDLCRendering.Controllers
{
    public class HomeController : Controller
    {
        public ActionResult Index()
        {
            string welcomeMessage = "Welcome to Northwind Traders. Enjoy the view!";
            return View((object)welcomeMessage);
        }
    }
}

Right click on the "Index()” method and select “Add View”.

Open the Index view and add code to display the string returned and also add a link for the report in the view like so:

<%@ Page Language="C#" Inherits="System.Web.Mvc.ViewPage<string>" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" 
"http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>Index</title>
</head>
<body>
    <div>
        <h2>
            <%=Html.Encode(Model) %></h2>
            
            <%=Html.ActionLink("Customers Report (PDF)", "DetailsReport", "Customers") %>
    </div>
</body>
</html>

Run the application. You should see a UI similar to the one below:

If you click on the link you will get a “The resource cannot be found.” error. This is because our Html.Action link refers to a method called “DetailsReport” in a CustomersController which does not exist yet.
Note that we are using the ActionLink overload(linkText, actionName, controllerName).

First add a reference to Microsoft.ReportViewer.WebForms in your project.

Add a CustomerController class and add a DetailsReport method like so:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.Mvc;
using System.Web.Mvc.Ajax;
using Microsoft.Reporting.WebForms;
using RDLCRendering.Models;

namespace RDLCRendering.Controllers
{
    public class CustomersController : Controller
    {
        public ActionResult Index()
        {
            return View();
        }

        public ActionResult DetailsReport()
        {
            LocalReport localReport = new LocalReport();
            localReport.ReportPath = Server.MapPath("~/Content/Reports/CustomerReport.rdlc");
            ReportDataSource reportDataSource = new ReportDataSource("Customers", Customers.GetAllCustomers());

            localReport.DataSources.Add(reportDataSource);
            string reportType = "PDF";
            string mimeType;
            string encoding;
            string fileNameExtension;

            //The DeviceInfo settings should be changed based on the reportType
            //http://msdn2.microsoft.com/en-us/library/ms155397.aspx
            string deviceInfo =
            "<DeviceInfo>" +
            "  <OutputFormat>PDF</OutputFormat>" +
            "  <PageWidth>8.5in</PageWidth>" +
            "  <PageHeight>11in</PageHeight>" +
            "  <MarginTop>0.5in</MarginTop>" +
            "  <MarginLeft>1in</MarginLeft>" +
            "  <MarginRight>1in</MarginRight>" +
            "  <MarginBottom>0.5in</MarginBottom>" +
            "</DeviceInfo>";

            Warning[] warnings;
            string[] streams;
            byte[] renderedBytes;

            //Render the report
            renderedBytes = localReport.Render(
                reportType,
                deviceInfo,
                out mimeType,
                out encoding,
                out fileNameExtension,
                out streams,
                out warnings);
            //Response.AddHeader("content-disposition", "attachment; filename=NorthWindCustomers." + fileNameExtension);
            return File(renderedBytes, mimeType);
        }
    }
}

This method will create an instance of the LocalReport class, set the Report path property, add a ReportDataSource which points to the GetAllCustomers method we defined earlier, set the report output type and page dimensions, calls the render method and invokes the File method on the Controller class. This method return a FileContentResult which sends the contents of a binary file to the response stream.

Run the page and click on the link. You should see the PDF report like so:

If you wish to prompt the user to download the PDF file, uncomment the “content-disposition” line and recompile.

Download Sample MVC Beta 2 project

SQL 2005 - Maximum number of characters displayed in each column

rajbk — Thu, 27 Sep 2007 17:52:00 GMT

This setting in Microsoft SQL server management studio, which has a default setting of 256 characters, caused me to waste almost half an hour yesterday. I was looking in the wrong places trying to troubleshoot my stored procedure and wondering why my data was being truncated. There is no indication in management studio that the text was being truncated either.

To increase the maximum value, open SQL Server management studio and go to

Tools » Options » Query Results » Results to Text » Maximum number of characters displayed in each column

Note also that there is a "Maximum Characters Retrieved" section under "Results to Grid" section too.

Changing the connectionstring of a wizard generated TableAdapter at runtime from an ObjectDataSource

rajbk — Sat, 26 May 2007 16:41:00 GMT

The Visual Studio 2005 Dataset designer allows you to create a DAL using a typed dataset and easily bind this to a GridView with the help of an ObjectDataSource. By default, in the TableAdapter generated, the visibility of the encapsulated Connection object is set to private.

For the calling code to specify its own Connection object, the visibility of the Connection property has to be made public. This can be done by setting the ConnectionModifier property of the TableAdapter to public.

Once that is done, changing the Connection at runtime can easily be done as shown below:

DataSet1TableAdapters.CustomersTableAdapter adapter = new DataSet1TableAdapters.CustomersTableAdapter();
SqlConnection conn = new SqlConnection();
conn.ConnectionString = MyConnectionManager.ConnectionString;
adapter.Connection = conn;
DataTable table = adapter.GetData();

What if an ObjectDataSource were using this TableAdapter and we wanted to change the ConnectionString at runtime?

The ObjectDataSource, thankfully, exposes an ObjectCreated event. This event gets raised after the object specified by the TypeName property is created. We can obtain a reference to this object by the ObjectInstance property exposed by the ObjectDataSourceEventArgs object. The Connection property can then be changed with a little sprinkle of reflection as shown below:

protected void ObjectDataSource1_ObjectCreated(object sender, ObjectDataSourceEventArgs e)
{
    if (e.ObjectInstance != null)
    {
        SqlConnection conn = new SqlConnection();
        conn.ConnectionString = MyConnectionManager.ConnectionString;
        e.ObjectInstance.GetType().GetProperty("Connection").SetValue(e.ObjectInstance, conn, null);
    }
}

This is useful in places where you wanted to set the ConnectionString to use based on the role of the web user. As always, if you know of a better way to do this, please post a comment. Thanks.

GridView DropDownList Pager

rajbk — Mon, 14 Aug 2006 15:18:00 GMT

This post shows you how to add a custom DropDownlist pager and pager buttons to the GridView as shown below:

Unlike the behavior of the default pager buttons, these buttons get disabled instead of being hidden (depending on the state of the GridView).

IMHO, buttons that remain at the same position while paging through a resultset makes a better UI (spatial memory).

The code is pretty straightforward.

Add the following pager template to your GridView.

<PagerTemplate>Goto Page 
    <asp:DropDownList ID="ddlPageSelector" runat="server" AutoPostBack="true">
    </asp:DropDownList>
    <asp:Button Text="First" CommandName="Page" CommandArgument="First" runat="server"
        ID="btnFirst" />
    <asp:Button Text="Previous" CommandName="Page" CommandArgument="Prev" runat="server"
        ID="btnPrevious" />
    <asp:Button Text="Next" CommandName="Page" CommandArgument="Next" runat="server"
        ID="btnNext" />
    <asp:Button Text="Last" CommandName="Page" CommandArgument="Last" runat="server"
        ID="btnLast" />
</PagerTemplate> 

Add a RowCreated event handler to your GridView and add the following code:

protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e) {
    if (e.Row.RowType == DataControlRowType.Pager) {
        PresentationUtils.SetPagerButtonStates(GridView1, e.Row, this);
    }
}

Finally, add the following code to your common class (PresentationUtils in my case)

public static void SetPagerButtonStates(GridView gridView, GridViewRow gvPagerRow, Page page) {
 
    int pageIndex = gridView.PageIndex;
    int pageCount = gridView.PageCount;
 
    Button btnFirst = (Button)gvPagerRow.FindControl("btnFirst");
    Button btnPrevious = (Button)gvPagerRow.FindControl("btnPrevious");
    Button btnNext = (Button)gvPagerRow.FindControl("btnNext");
    Button btnLast = (Button)gvPagerRow.FindControl("btnLast");
 
    btnFirst.Enabled = btnPrevious.Enabled = (pageIndex != 0);
    btnNext.Enabled = btnLast.Enabled = (pageIndex < (pageCount - 1));
 
    DropDownList ddlPageSelector = (DropDownList)gvPagerRow.FindControl("ddlPageSelector");
    ddlPageSelector.Items.Clear();
    for (int i = 1; i <= gridView.PageCount; i++) {
        ddlPageSelector.Items.Add(i.ToString());
    }
 
    ddlPageSelector.SelectedIndex = pageIndex;
 
    //Anonymous method (see another way to do this at the bottom)
    ddlPageSelector.SelectedIndexChanged += delegate {
        gridView.PageIndex = ddlPageSelector.SelectedIndex;
        gridView.DataBind();
    };
 
} 

AFAIK, VB.net 2.0 does not support anonymous methods. Therefore, for those of you converting this code to VB.net, you will need to add the handler manually. Go to the GridView Tasks and select "Edit templates", select the PagerTemplate and double click on the DropDownList to add a index changed event handler. In the event handler, write the VB.net equivalent of this:

protected void ddlPageSelector_SelectedIndexChanged(object sender, EventArgs e) {

GridView1.PageIndex = ((DropDownList)sender).SelectedIndex;

GridView1.DataBind();

}

Related : Clickable GridView Headers .