April 27, 2013

C# - Building RDLC report template and displaying it in ReportViewer Control

1. Introduction to RDLC Reports

In this RDLC tutorial, I will walk you through generating a report using the Microsoft supplied report template (.rdlc file). We will get the data from the “Titles” table of the Microsoft supplied “Pubs” database.

To generate the report on the database table, we are going to use following things:

  1. A “DataSource” that supplies the data from the database.
  2. A “Report Template” that defines the visible elements and its location. [.rdlc file]
  3. A “ReportViewer Control" supplied by the dot net framework.
  4. A “C-Sharp Form” that houses the ReportViewer control in it.

The interaction between the different aspects in generating the report is shown below:

Figure 1

In the "Report Template (.Rdlc file)" we define the report elements. This includes database fields, text labels, Images etc. The Report Template defines what you want to display and in the format you want to display. Once the report template is ready, it can be assigned to a "ReportViewer Control" that can be hosted by any container say for example a Dot Net Form.

2. About the RDLC Report Example

The finished report in the design view looks like the below screen:

Figure 2
From the designer you can see that we are going to display the data for the report in a “Table Control”. In the Page Header of the report we are going to display an image. Similarly in the page footer we are going to display the report title as well as the page number of the report. I will explain these in details when we move to specific section of the report.

The below screen shot shows how the report will look like when we run it:

Figure 3

Note that in the data part (number 4) of the report the rows are highlighted when the price is 20 or more. Let us start building this report and as you guess there is no piece of code that you need to write here. The above screen shot is actually a "C-sharp Form" with a report viewer control in it. When you want to display the report, you can call this from different dialog. Ok, Let us Begin. Start a new Visual C# Windows application.

3. Preparing the DataSource for RDLC Report

Once you started the project click on the Menu Option “Add New Data Source…” as shown in the below screen. This will open a wizard and use this wizard you can contact the database and bring the data to your project by picking a table, views, stored proc etc.

Figure 4

In this example, SQL server database on the local machine is connected and Titles table from the Pubs database is retrieved. The first option in the Data menu brings the Data Sources window that you can dock to any side of your development environment. The video shown is setting the Data Source to the Titles table of the Pubs database.

Video 1: Get Data Source for the Titles Table

4. Report Design – Display data in Table Control

Ok. The previous shown you setting up the data source. The next step is adding the report template to the project. You can add the report template to the project using the “Add new item” context menu option and then select the “Report” from the listed templates. Once the report design view is displayed, add the table control from the toolbox to the report. The report design view look like what is shown below:

Figure 5

The report control shows three sections in it. Header is useful to display the column titles. In the details section of table control the data retrieved from data base flows from start to end. Once the data portion is displayed in the detail part of the table control, in the footer you can usually place some summary information or totals etc. In our example we are going to place a static text called “End of Report”.

The table control by default displays three columns. However, you increase the number columns by clicking the “Insert Column to Left” or “Insert Column to Right” options based on the decision of how many column of data the report is willing to display. You can drag a table field from the Data Source Window and drop it to the Detail cell of the table control and doing so will automatically display the column title in the corresponding header cell. In the below video, I placed 4 columns from the Titles table to the Table control which is in the report template.

Video 2: Position the required database fields in the report

5. Show the initial report in the Report viewer

The report template is ready. The ReportViewer control will be assigned with the report that we designed (Initial Design) in the previous step. The below screen shows a ReportViewer control in the form:

Figure 6

After assigning the “.rdlc” file to the ReportViewer control, dock it to the entire form so that the user will get a better look at the report. That is all we need to show a report using the stuffs that comes with Visual studio installer itself. Assigning the report to the report view control and running the test run is shown in the below video with explanation:

Video 3: Assigning the Report Template to ReportViewer Control & Running the report

6. Report Design – Page Header and Page Footer

Section four of this article talked about Report Header and report footer. Now we will add the Page header and Page Footer for the report. Before we add the page header and footer we should understand how it would differ from the report header and footer. Look at the depiction that treats report spawns for three pages below:

Figure 7

From above picture you can note that Report Header starts before the report details section starts. Report Footer starts after the detail section ends. Also be aware that the details section takes responsibility of displaying the database records and computed fields. Page Header and Page Footer will be displayed on every page. OK! Now if I ask you where should I keep the page numbers when I am designing the report, you will promptly reply Page Footer.

To add Page header and page footer to the report template, click the square-shaped area in right corner of the report (focused in the below pic) and then right click to bring the short-cut menu. From this menu, you can choose the page header and page footer.

Figure 8

Once page header and page footer are added to the report template, add an image control to the report template’s page header section. This image will be populated using the embedded image. You can add embedded image from report menu as shown below:

Figure 9

Once the image is embedded in the project, the following properties should be set to the image control added in the Page header section of the report:

Figure 10

Similarly, Two text box controls added to the Page Footer section of the report. These text boxes are used to display report name and page numbers. To get report name and page numbers, the controls value property is set with an expression that computes Page Number and displays report name. You can see setting the Page Header and Footer from the below-shown video:

Video 4: Setting the Page Header and Page Footer

7. Report Design - Highlight Costly Books

To highlight the costly books, select the details row and set the background property and color property through an expression. These expressions are shown below:

= iif(Fields!price.Value >= 20, "Black", "White"): For Back ground color
= iif(Fields!price.Value >= 20, "White", "Black"): For Color property

Figure 3. shows you the highlighted rows. You can build the above-shown expression  using the expression builder. Highlighting the costlier books by building the expression is shown in the below-given video.

Video 5: Building the expression from DB fields to highlight a row

Source Code : Download

April 16, 2013

ASP.Net - Binding the GridView Control to Database Using ObjectDataSource Control

1. Introduction

In this article, we are going to see the usage of the Object Data Source control. We will create the web page with a GridView control and link that control to the ObjectDataSource control.

First, we will see the advantage of the ObjectDataSource control over the SqlDataSource control. Let us say an organisation has the employee payroll processing application as an Internet application. For example, consider like this, there are three pages that access the employee data from the database and displays that in the page. And, each page uses the SqlDataSource control to populate controls in it. And we know SqlDataSource control talks to the database directly. Now have a look at the picture below:

Looking at the picture you understand that the SqlDataSource control sticks on each page & directly contacts the database. This leads to a maintenance pain of changing all three pages when the employee table schema in the database is changed. But when we use the ObjectDataSource control, the control talks to a class object and that class object talks to the database. Now, the schema change for the employee table affects only the class that acts as a middle tier and we call this class as business layer class.  This shown in the below picture:

OK. Let us start moving through the example of asp.net page accessing the authors table of the pubs database. The sample attached with this article has the script file that you can run on your SQL server database to create the Pubs database.

2. About the Sample Application

As the page is going to have only one GridView the sample application is easily imaginable. The screenshot of the sample application is shown below:

The Data is retrieved from the Authors table of the Pubs database.  The user can navigate through the pages of the GridView or they can modify or update a single record. This can be easily done using the SqlDataSource control. But we will use the ObjectDataSource Source in this example and from the previous section you are aware of the advantage of using the ObjectDataSource control.

3. The DataClass - Author

As you see from the sample application screenshot, we are taking only three columns from the Authors table. The picture below shows the picked columns from the Authors table:

The data class represents the table column data. From the above picture, we know that we are going to take only three columns namely Au_id, au_fname and city from the Authors table. So our data class will have three data members with the matching data type and data name. Add a class called Author to your project under the App_Code folder and provide the properties for all three picked columns. If App_code folder is not available by default, add it to the project using the helper screen shot below:

The data class for the Authors table is shown below:

/// <summary>
/// Summary description for Author
/// </summary>
public class Author

    //Sample 01: Defaut Ctor
    public Author() { }

    //Sample 02: Private Members
    private string m_Au_id;
    private string m_fname;
    private string m_city;

    //Sample 03: Properties
    public string Au_id
        get { return m_Au_id; }
        set { m_Au_id = value; }

    public string au_Fname
        get { return m_fname; }
        set { m_fname = value; }

    public string City
        get { return m_city; }
        set { m_city = value; }

Note that when you create the property, the property should match the database column name. In the above class, I have three properties and each one is for one column that we are planning to display in the GridView.

4. The Business Layer Class - AuthorDataSet

Well. We are done with the data class. Let us go ahead and define the Business class that operates on the data supplied by the data class.

Add a class called AuthorDataSet to the project and this class tells the ObjectDataSource control how the Select, Insert, Update and Delete operation will be performed on the database. Whereas the data class will supply the data required for the operation. We will see about that when we develop this sample on the Go.

1) In the AuthorDataSet class get the required namespace for this example

//Sample 04: Name Space
using System.Data.SqlClient;

2) Next, add the web.config file if it is not already available as part of the project. To add, select the project, right-click and choose the context menu item “Add new item…” and then from the displayed dialog select the Web Configuration file. OK, in the web configuration file define the connection string setting. This is shown in the below screenshot:

1: Your connection string name. This name will be used later, to get the connection string
2: Specifies the provider name. In our case, it is SqlClient
3: Here, you can specify the network machine name or IP address in which the database in running. I specified localhost as the database is running on the same machine in which I am developing this sample.
4: Specifies which database you want to point on the database server.
5: Username
6: Password

3) In the class file AutherDataSet.cs; retrieve the Connection string settings by the name Pubs (Tag 1 in the previous picture). Once we have the ConnectionStringSettings objects, we can easily retrieve the connection string wherever we want.

//Sample 05b: Connection string from web.config
private ConnectionStringSettings settings = ConfigurationManager.ConnectionStrings["Pubs"];

4) A default constructor must present in the business layer class as the “DataObjectSource” accesses this class and the default constructor will provide the ability to create the object on the fly.

//Sample 06: Default Ctor
public AuthorDataSet()

5) In the previous step, I specified that the Business Layer Class must provide a default constructor. It should also provide at least one method that returns an Array, List or DataTable etc., (Simply a collection of data). The below-given code retrieves the data from the database and returns that as a data table.

//Sample 07: For Select Command
public DataTable ListOfAuthors()
    //Sample 7.1: Create connection and Data Table
    DataTable authors = new DataTable();
    authors.TableName = "Authors";
    SqlConnection Con = new SqlConnection(settings.ConnectionString);

    //Sample 7.2: Create a bridge DB and Table of Data
    SqlDataAdapter adapter = new SqlDataAdapter("Select Au_id, Au_fname, city from Authors", Con);

    //Sample 7.3: Fill the Data in DataTable
    return authors;


First, a SqlConnection object is created using the connection string retrieved from the config file. Then, Select statement to retrieve the data from the database is defined in the SqlDataAdapter. And, finally, we asked the adapter to fill the data table with the result of executing the select statement. This can be depicted as follows:

OK. Now the business layer object implemented a method, that returns the collection of rows and columns retrieved from the database as DataTable. We need to design the web page to utilise this method and display the data returned through the DataTable. In the video given below, I explained designing the form and displaying the data in the DataGrid by making use of ObjectDataSource Control.

Video 1: Configure Object Data Source & Bind with DataGrid

5. Support Update and Delete operations

In the previous section, we saw retrieving the data from the database and displaying it in the DataGrid through the ObjectDataSource Control. In this section, we will perform the update and delete operation on the DataGrid view so that it will be reflected in the on the database.

In the AuthorDataSet class define two new methods, One for the Update and other One for the Delete operation. The Update Method is given below:

//Sample 08: For Update Command
public void UpdateAuthor(Author aut)
    //Sample 8.1: Create Connection and Data Table
    DataTable author_updt = new DataTable();
    SqlConnection Con = new SqlConnection(settings.ConnectionString);

    //Sample 8.2: Create SQL Adaptor to get a single row based on primary key
    SqlDataAdapter adapter = new SqlDataAdapter("Select Au_id, Au_fname, city from Authors " +
                       "where au_id = @Author_id", Con);
    adapter.SelectCommand.Parameters.AddWithValue("@Author_id", aut.Au_id);

    //Sample 8.3: Suppy the adapter the Update command
    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
    adapter.UpdateCommand = builder.GetUpdateCommand();

    //Sample 8.4: Perform the change in the data row and perform the update on Data Adaptor
    DataRow Row_tobe_updated = author_updt.Rows[0];
    Row_tobe_updated["Au_fname"] = aut.au_Fname;
    Row_tobe_updated["City"] = aut.City;


The first thing you note here is that the Author data class instance but is passed as the parameter to this function. Who will supply this parameter? Well, when you click the Update link in the GridView control (After Modifying the Data), the GridView control notifies the ObjectDataSource control that a particular row is modified. Now ObjectDataSource is aware of the method that should be informed of the update Operation. The modified row is filled in the instance of the data class Author and after that, it is passed as the parameter to the function UpdateAuthor function.

The function retrieves single row by forming the Select Query based on the primary key au_id. Note that the parameter field for the select query filled by taking data carried by the at instance passed in a parameter. Once Select Query is ready we fill the DataTable author_updt by calling the Fill method in the DataAdapter.

Once we have the DataTable ready for the modification, we educate the DataAdapter with the Update command. The SqlCommandBuilder instance builder is used to build the UpdateCommand and assign it to the DataAdapter back.

Finally, Once Adapter is Aware of the UpdateCommand; we take the single row from the DataTable and update the columns with the changed in data coming from the DataGridView as a parameter to this function. Once the DataRow taken from the DataTable is updated with the modified data, we call the Update method on the DataAdapter. This method applies the changes made to the DataTable to the underlying SQL Server database. The Entire operation can be depicted as shown in the below picture:

Now, I no need to explain much about the function that used for the deleting a record from the database. The function for the delete operation is listed below:

//Sample 09: For Delete Command
public void DeleteAuthor(Author autD)
    //Sample 1.1: Create Connection and Data Table
    DataTable author_delete = new DataTable();
    SqlConnection Con = new SqlConnection(settings.ConnectionString);

    //Sample 9.2: Create SQL Adaptor to get a single row based on primary key
    SqlDataAdapter adapter = new SqlDataAdapter("Select Au_id, Au_fname, city from Authors " +
                              "where au_id = @Author_id", Con);
    adapter.SelectCommand.Parameters.AddWithValue("@Author_id", autD.Au_id);

    //Sample 8.3: Perform the delete operation (On a single row in the data table)
    SqlCommandBuilder builder = new SqlCommandBuilder(adapter);
    adapter.DeleteCommand = builder.GetDeleteCommand();

    //Sa,ple 8.4: Delete the data row and asks the adaptor to update the db

The function is almost similar to what we wrote for the Update Operation. The only difference is, we take a DataRow from the DataTable, and then we delete that before calling the update method on the DataAdapter.

Remember, From the Previous video you are aware that our ObjectDataSource Controls only deals with the Select Operation Only. Now we should update the ObjectDataSource Control with the Update and Delete Operation. After doing so, we should also modify the GridView control to support Modification and Deletion. In the below video explained these steps:

Video 2: Providing the Support for Update and Delete Operation

6. Summary

Hope this article helped you in understanding how do we use the ObjectDataSource control with the GridView control. The attachment contents are given below:

When you extract ASP.net.zip, in the root folder you will get an SQL script file that you can run in your SQL Server to create the Microsoft’s Pubs sample database. You can open the project using the Open Website Option in Vs2005 IDE (Or Later with the conversion wizard).

Source Code: Download

Like this site? Tell it to your Friend :)