Creating a Visual Studio 2010 ASP.Net Reports Web Site with a LINQ DBML data source

by Heathesh 8. June 2010 02:48

With Visual Studio 2008, it appears the only way to connect to a LINQ DBML and use it as a data source was to create your own Data Processing Extensions implementation. With Visual Studio 2010 it's a little bit simpler, but can still cause some pain.

To begin with, create a ASP.Net Reporting Web Site. I first tried this by creating a web application and after much hair pulling decided to use the preconfigured Reporting website project type to save myself the configuration issues.

Once you've creating the website, the Data Source configuration wizard should start automatically. I simply cancelled this. As part of the website you'll see that it adds a "Report1.rdlc" file, I deleted this and added a new report, and called it "PeopleDetails".

If you do delete the "Report1.rdlc", be sure to open the HTML of the Default.aspx and change the report name accordingly:

        <rsweb:ReportViewer ID="ReportViewer1" runat="server" Font-Names="Verdana" Font-Size="8pt">
            <LocalReport ReportPath="PeopleDetails.rdlc">

Next add a new "Class Library" project to your Solution. This class library project will be used to store your LINQ DBML in, and it needs to be separate in order for you to make it accessible on the machine so the report can read it.

You can delete the sample "Class1.cs" file created by the IDE, and add a new "LINQ To SQL Classes" item. Next add the relevant tables etc. to the DBML as required. Then add the newly created project as a reference to your website. Make sure to add the connection string as required to the Web.Config of your website.

        <add name="PeopleDbml.Properties.Settings.SampleConnectionString"
            connectionString="Data Source=.\server;Initial Catalog=Sample;Integrated Security=True"
            providerName="System.Data.SqlClient" />

Next add a class to your "App_Code" folder, I called mine DataReader simply because it seemed to be the most relevant name. In your DataReader class, add a method to retrieve a List of the data you wish to diplay on your report. In my case I wanted a list of People from the Person table in the database. So I implemented a method called GetPeople which accepts the data context as a parameter:

    /// <summary>
    /// Gets a list of people from the database
    /// </summary>
    /// <returns></returns>
    public List<Person> GetPeople(PeopleDataClassesDataContext dx)
        return (from people in dx.Persons
                select people).ToList();

Now comes the weird part. For some reason the report will not be able to find the PeopleDbml dll. If you tried to add a table for example, and tried to add a data source to that table you will get an error that it can't find the dll. To solve this problem, compile your PeopleDbml class library, find the dll and copy it to the "C:\Program Files\Microsoft Visual Studio 10.0\Common7\IDE\PrivateAssemblies" folder.

Once you've done that, close Visual Studio 2010 (because I haven't found another way of doing this), reopen it and reopen your solution. Then try and add a table to your report and it should work now without any errors. Simply set the name of the DataSet as required, I called mine PeopleDataSet, set the DataSource to "global" and you should be able to select "DataReader (GetPeople)" in the "Available DataSets" drop down.

Once the DataSet has been added, design your table (i.e. setup your fields as required, I simply clicked and dragged three different fields into the three columns available).

The last thing you need to do is set the ReportDataSource for the report. I did this by adding the following code to my Default.aspx page:

using Microsoft.Reporting.WebForms; //using for the ReportDataSource
using PeopleDbml;

public partial class _Default : System.Web.UI.Page
    /// <summary>
    /// Gets the connection string from the web.config
    /// </summary>
    private string _connectionString
        get { return ConfigurationManager.ConnectionStrings["PeopleDbml.Properties.Settings.SampleConnectionString"].ConnectionString; }

    /// <summary>
    /// Gets or sets the data context
    /// </summary>
    private PeopleDataClassesDataContext _dataContext

    /// <summary>
    /// Handles the page load event
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void Page_Load(object sender, EventArgs e)
        if (!IsPostBack)
            if (_dataContext == null)
                _dataContext = new PeopleDataClassesDataContext(_connectionString);

            //create an instance of my data reader class
            DataReader dataReader = new DataReader();
            //create the report data source, specifying that it's for the PeopleDataSet
            ReportDataSource reportDataSource = new ReportDataSource("PeopleDataSet", dataReader.GetPeople(_dataContext));
            //add the data source to the report viewer's local report data sources

    /// <summary>
    /// Handle the dispose to clean up the data context, which overriden to clean up the data context
    /// </summary>
    public override void Dispose()

        if (_dataContext != null)

That was it. Happy Reporting!


Tags: , , , , ,

Development | .Net | Visual Studio 2010 | VS2010 | LINQ

Powered by BlogEngine.NET (with enhancements by Heathesh)
Theme by Mads Kristensen (with tweeks by Heathesh)


Microsoft Certified Professional

Microsoft Certified Technology Specialist

Answer Questions


Tag cloud


<<  September 2017  >>

View posts in large calendar