C#. Windows Forms. An example of creating a report in an application created using the Windows Forms Application template for a database of the Microsoft SQL Server type, which is located in the local *.mdf file

An example of creating a report in an application created using the Windows Forms Application template for a database of the Microsoft SQL Server type, which is located in the local *.mdf file

This example demonstrates the generation of a report based on data that are located in a local database such as Microsoft SQL Server. The database is implemented in the Education.mdf file.

In this example, you can learn how to create your own reports, which are formed on the basis of data that are intended for use under the control of Microsoft SQL Server DBMS.

Search other websites:

Task

Given a database, which is designed for use under the control of Microsoft SQL Server DBMS. Two tables with the names Student and Session are realized in database. The tables are related between themselves by a certain field.

The structure of the Student table.

Field name	Data type	Comment
ID_Book	Integer	Unique field identifier, key field, counter
Num_Book	Text (10 characters)	Number of student record book
Name	Text (30 characters)	Surname and name of the student
Year	Integer	Year of birth

The structure of the Session table.

Field name	Data type	Comment
ID_Session	Integer	Unique field identifier, key field, counter
ID_Book	Integer	Number of student record book
Mathematics	Integer	Score from Math
Informatics	Integer	Score from Informatics
Philosophy	Integer	Score from Philosophy

The tables are related to each other by the ID_Book field (the record book number).

Develop an application that demonstrates the use of reports. Generate a report that contains the following information:

number of student record book;
Surname and name of the student;
Score from Math;
Score from Informatics;
Score from Philosophy;
average score (evaluated field).

The report should be displayed in a separate form.

⇑

Considerations

To create a report, you need to create Views. A view is a simple query to a database in the SQL language. In the view, a resulting table is created with the selected data. Based on this View, a report will be created.

⇑

Instructions

1. Run Microsoft Visual Studio. Create a project using the Windows Forms Application template

After starting Microsoft Visual Studio, you need to create a new project. A detailed example of creating and saving a new project using the Windows Forms Application template is described in the topic:

An example of creating an application of ‘Windows Forms Application’ type

Select the folder for the project. For example

C:\Programs\C_Sharp\TrainReports01

⇑

2. Create a database “Education.mdf” or download the finished database file

In this case, download the archive with the ready (previously prepared) database file using this link.

The archive contains Education.mdf and Education.ldf files. It is recommended that you unpack these files into the folder where the application you are developing will be located.

⇑

3. Connecting the Database File to Project

Before connecting the database file to the project, it is recommended that you save the database files in the folder where the project files are located. In our case this is a folder

C:\Programs\C_Sharp\TrainReports01

To use the database “Education.mdf” you need to connect it to the project in the standard way. To connect the database file to the project, use the “Add Connection …” command from the context menu, which is called in the Data Connections list (Figure 1).

Another way – select the command “Connect to Database” (see Figure 1).

Figure 1. Ways of connection of project to a database file

As a result, the wizard window opens, in which you need to perform the following actions:

In the Add Connection window (Figure 2), select the “Change …” button for the Data Source field;
In the “Choose Data Source” window select the Data Source – ‘Microsoft SQL Server Database File’ (Figure 3);
In the next window “Add Connection” in the “Database file name” field, specify the path to the database file as shown in Figure 4 (the ‘Browse…’ button).

Figure 2. The “Add Connection” window. Selecting the data source

Figure 3. The “Change Data Source” window. “Microsoft SQL Server Database File” is selected

If done correctly, the “Add Connection” window can take the form shown in Figure 4. Using the “Test Connection” button, you can test the connection to the database. To fix the selection of the database, select OK.

Figure 4. The “Add Connection” window with the selected data source type and the Education.mdf database file

⇑

4. Window of the Server Explorer utility

After completing the steps, tools Server Explorer window will look as shown in Figure 5. The window displays a detailed view of the Education.mdf database.

Figure 5. Window of the Server Explorer utility with the Education.mdf database

The database contains the following components:

Student and Session tables;
the diagram of relationships between tables. The tables are linked to each other by the ID_Book field;
“View1” view, which contains the display of some table fields as well as the calculated field “Average”.

⇑

5. Development of the main form of application

In accordance with the condition of the task, the report should be displayed in a separate form. Therefore, the main form of the application has the form as shown in Figure 6.

Figure 6. Main form of application

As you can see from the figure, the form contains only one Button control named button1. After clicking on the “Show Report” button, a report in another form will be displayed.

The following properties of controls are set:

in the button1 control, the property Text = “Show Button”;
in the Form1 control (main form), the property Text = “Report Viewer”.

⇑

6. Connecting the report file to the project

In the Microsoft Visual Studio system, each report corresponds to a file that has the extension “* .rdlc”. This file contains the data that is generated in the report.

To create a file in Microsoft Visual Studio, you need to call the Add New Item command from the Project menu (see Figure 7).

Figure 7. The “Add New Item …” command from the Project menu

As a result, the “Add New Item …” window opens, in which you select the Report template (Figure 8) and specify a name Report1.rdlc for the report file (by default).

Figure 8. Selecting the Report template and setting the name Report1.rdlc for the report file

After confirming the selection (the Add button), the Microsoft Visual Studio window will appear as shown in Figure 9.

Figure 9. The tab window that corresponds to the Report1.rdlc file

⇑

7. Designing the report

Before designing a report, you need to activate the “Report1.rdlc [Design]” file window.

7.1. The ToolBox toolbar

To design the elements of report, use the ToolBox panel (Figure 10).

Figure 10. The ToolBox panel with tools that are necessary to design a report

⇑

7.2. The controls used to generate the report

The Microsoft Visual Studio system offers a number of controls that you can use to develop a report. With these controls, you can design the appearance (view) of a variety of reports.

The following controls are proposed:

the control of type TextBox. Displays labels, fields, or values that result from calculations;
the control of type Line. Draws a line that has a thickness of 1 point or more;
the control of type Table. This control displays data in a grid that has a fixed number of columns and a variable number of rows;
the control of type Matrix. Displays aggregated data in a grid that has a variable number of columns and a variable number of rows;
the control of type Rectangle. Draws a rectangle as a container for other report elements;
the control of type List. Displays a set of report items that are repeated for each group or row of data;
the control of type Image. Displays an image in the form of a bitmap (for example, a logo, a photo);
the control of type Subreport. Displays the associated subreport within the scope of this report;
the control of type Chart. Displays data in the form of diagrams of different types;
the control of type Gauge. Displays a value, field, or expression as a linear or radial relationship.

Without going into the features of the use of each control, we use the Table control to solve our problem. This control displays data in the grid, which have a fixed number of columns and a variable number of rows.

⇑

7.3. Setting the “Table” control

7.3.1. Configuring the Data Source for the ‘Table’ Control

To place a Table type control on a report layout, you need to select it (using mouse) in the Toolbox and click in the report area. This will load the Data Source Configuration Wizard. In the first window of the Data Source Configuration Wizard, select the Database data source (Figure 11).

Figure 11. Selecting the data source from which the application will receive data for the report

After selecting “Next>” the following window will open (Figure 12). In this window you need to select the Dataset database model. The selected database model determines the types of data objects that the application uses. This model will be added to our project.

Figure 12. Selecting the Data Model

After selecting ‘Next>’ the following window will open (Figure 13). In this window, you must select the connection to the database to which the Education.mdf file corresponds. This connection corresponds to the string Connection String, which is automatically generated by the system.

Figure 13. Configuring the connection to the database. The ‘Connection String’ string

After selecting “Next>”, you must confirm copying the database file to the current project (Figure 14). As a result, the connection string is modified. Since the Education.mdf database is already attached to the project (see step 3), it is recommended to select “No”.

Figure 14. Window for clarifying of copying the database file into the current project

The next window (Figure 15) proposes store the Connection String in the application’s configuration file. It is necessary to leave everything unchanged and select “Next>”.

Figure 15. Window with a suggestion to save the Connection String in the application configuration file

Next, there is a window for selecting database objects that will be included in the data set (Figure 16). In our case, we need to select all elements of the view View1, which was generated by the Microsoft SQL Server DBMS.

Figure 16. Selecting objects to display

After selecting the “Finish” button, the previously opened Dataset Properties window with the created data set will be displayed (Figure 17). This window displays the View1 view fields that will be displayed in the report. It should be recalled that View1 was designed by Microsoft SQL Server DBMS.

Figure 17. Dataset Properties window with View1 fields displayed

If desired, you can create one more (or more) data source using the “New …” button (see figure 17).

After confirming to OK, the Report1.rdlc report file window will have a view, as shown in Figure 18.

Figure 18. The window of Report1.rdlc file after placing and configuring the Table table

⇑

7.3.2. Setting the table columns to display in the report

There are two areas in the Table1 control:

the Header area, which displays the title of each column of the table
the Data area, that displays a set of data, which are displayed in each row of the table. A dataset is generated based on the View1 view.

⇑

7.3.2.1. Setting titles

To customize the name of each column, double-click in the corresponding cell in the Header area, as shown in Figure 19.

Figure 19. Setting the header for the field “Num_book”

As you can see from the figure, the system has generated a table that contains 3 columns. In our case, the view View1 contains 6 columns. To add a column, you need:

make a click with the right mouse button on the border of the table (gray background) in the corresponding column;
execute the ‘Left’ or ‘Right’ command of the ‘Insert Column’ menu (Figure 20).

Figure 20. Calling the command to add a new column

To place the title of the table header in the center, use the TextAlign property (see Figure 21) of the TextBox13 object that was created for the first column of the table named “Book number”.

Figure 21. Setting the centering of the table header

Also, you can adjust the width of each column of the table. After setting all the headers, the window of the report file will have the form, as shown in Figure 22.

Figure 22. The report window after setting the table headings

⇑

7.3.2.2. Configuring Data

At this stage, you need to configure the data that will be displayed in the rows of the table. The number of rows that will be displayed can be anything. The data is displayed in the Data area of the table.

Each column of the table is individually configured. The first column in the View1 view is a column named Num_Book. This column should be selected in the context menu of the table as shown in Figure 23.

Figure 23. Setting the data displaying in the column “Book number”

The corresponding View1 view fields are selected in all subsequent columns. After the formation of all the columns, the table will has view as shown in Figure 24.

Figure 24. The report window after configuring the table

⇑

7.4. Setting the report header

To set the report header, use the TextBox tool (see Figure 25), which you need to place above the table. After placement, the corresponding object will be created. To specify a name, you need to click in the rectangular area of the TextBox tool.

Figure 25. Specifying the title of the report “View1”

If desired, you can improve (change) the type of the report with the help of other tools.

⇑

8. Designing an additional form and linking the report to a form

In accordance with the condition of the task, when clicking on the “Show Report” button, a new form of application can be opened with the created report. Therefore, in the program need to develop a new form.

8.1. Adding a form to the project

The development of an additional form is implemented in a standard way for MS Visual Studio. A more detailed example of creating a new form is described in the topic:

Example of creating and calling a new form in C#

First, you need to add a form (a form file, a resource) to the project using the command

Project -> Add Windows Form...

As a result, the “Add New Item” window opens, in which you select the Windows Form template and leave the name of the Form2.cs file offered by the system.

Figure 26. The window for specifying the template of the created form

As a result, Microsoft Visual Studio will create a new form, which, so far, does not have any controls. Class, named Form2 corresponds to the form.

⇑

8.2. The Report Viewer control

In order for the report to be displayed in the application, the “Report Viewer” control is provided in the Microsoft Visual Studio (see Figure 27). This control connects the application (program) with the report file Report1.rdlc. First, go to the form design mode “Form2.cs [Design]”. Next, from the ToolBox toolbox on the Reporting tab, select the ReportViewer control and place it on the Form2 form with the mouse, as shown in Figure 27. As a result, the system will automatically prompt you to select a report file in the ReportViewer Tasks window (see Figure 27). In addition, an instance (object) named reportViewer1 will be generated for the control.

Figure 27. Placement of the “Report Viewer” on the form

After selecting the drop-down button, the Report Viewer Tasks window displays a list of the connected (created) report files. In our case, you need to select one file named TrainReports1.Report1.rdlc (Figure 28). Here the prefix TrainReports1 stands for the name of our project.

Figure 28. Connecting the project file to the ReportViewer control

After selecting the Report1.rdlc file, adjusting the size of the Form2 form, and adjusting the size of the reportViewer1, the form window will appear as shown in Figure 29.

Figure 29. The additional form window after you place and configure the ReportViewer control

The system automatically generates three objects named EducationDataSet, Query1BindingSource and Query1TableAdapter. These objects connect the application to the database. They can be used to programmatically manage the report. But this is a completely different topic.

To verify that the Report1.rdlc file associated with reportViewer1, it is needed to click on the arrow in the right corner.

Figure 30. Checking the reportViewer1 connection with the Report1.rdlc report file

⇑

9. Program code of the Form2.cs form file

After setting up reportViewer1, the system will automatically generate the report display code on the form in the Form2 event’s Load event handler. The Load event of the Form2 form is called at the moment if you want to display the form on the screen. Therefore, calling Form2 from the main form (Form1) will automatically display the report.

The general listing of the Form2.cs module is as follows:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace TrainReports01
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }

        private void Form2_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'EducationDataSet.View1' table. You can move, or remove it, as needed.
            this.View1TableAdapter.Fill(this.EducationDataSet.View1);
            this.reportViewer1.RefreshReport();
        }
    }
}

⇑

10. Programming the click on the “Show Report” button to display a report from the main form

The last step is to program a click on the “Show Report” button of the main form. Calling the Click event handler on the Show Report button is performed in a standard way for the MS Visual Studio. A detailed description of event programming is described in the topic:

Example of event programming of button click in C#. Development of a program to determine the surface area of the sphere

Without going into the details of event programming, here is the code of the event handler button1_Click(), from which the report is called

// click on the "Show Report" button
private void button1_Click(object sender, EventArgs e)
{
    //
    Form2 f = new Form2(); // create an instance of form 2
    f.ShowDialog(); // display the form, the report will be automatically displayed
}

As you can see from the listing, an instance of Form2 is created in a standard way and a form is called on which the report will be displayed.

⇑

11. Program code of the file Form1.cs

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace TrainReports01
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        // click on the "Show Report" button
        private void button1_Click(object sender, EventArgs e)
        {
            //
            Form2 f = new Form2(); // create the instance of form 2
            f.ShowDialog(); // display the form, the report will be automatically displayed
        }
    }
}

⇑

12. Running the application for execution (testing)

After running the application for execution, the program window, where the report is displayed, will appear (Figure 31).

Figure 31. Displaying the report in program