logo
small logo
  • Produits
  • Acheter
  • Assistance
  • About
  • Panneau du client Assistance
    • en
    • de
    • JP
    • ZH
  • Page d'accueil
  • /
  • Articles
  • /
  • How to use stored procedures with multiple sets of data as a result
  • Toilet paper printing

    30 mars 2020

    Gentlemen jokes aside! Today we are talking about toilet paper. This essential hygiene product was

    read more
  • How to use Online Designer in ASP .NET Core

    17 janvier 2018

    One of the novelties by FastReport .NET 2018 was the adaptation of OnlineDesigner to the

    read more
  • PreviewControl.OnPrint and PreviewControl.OnExport Events

    1 octobre 2020

    In FastReport 2019.4 added the ability to subscribe to PreviewControl.OnPrint and PreviewControl.OnExport events, which are

    read more
  • FastReport .NET packages and .NET 5.0

    17 décembre 2020

    UPD: Applies to the versions of FastReport .NET before 2022.2. License packages are now available

    read more
  • Connection to NosDB (NoSQL)

    11 novembre 2019

    NosDB is a prominent representative of the NoSQL databases. It is designed for use on

    read more

How to use stored procedures with multiple sets of data as a result

8 mai 2020

Often, when creating reports, we have to deal with databases that are far from ideal. The various "features", sometimes get us stuck. For example, how to build a report based on multiple datasets, which will result in MS SQL stored procedures? When you create a data source in the report, we can use a stored procedure in SQL query, but only for a specific table. This means that it will take only a first set of data returned by the stored procedure. To generate multiple tables of the results of a stored procedure, we need to create a data source software in the code of a custom application.

You need to create a database connection, execute the stored procedure, and enter its results into the dataset. Then we transfer dataset to the report and record the data source in the report. In this way, in the designer we will receive the prepared data source when making the report. We can go further and programmatically connect to the report source, so it's already in the data tree. But the firstly, we consider the first option with simple adding a data source to the report.

Let's say you have some WinForms applications where you generate reports. Add a button to call the report editor:

1
2
3
4
5
6
7
8
private void button1_Click(object sender, EventArgs e)
 {
 Report FReport = new Report();
 SqlConnection conn = new SqlConnection("Data Source=localhost;AttachDbFilename=;Initial Catalog=salesdb;Integrated Security=True;Persist Security Info=False;User ID=;Password=");
 DataSet data = GetDataSet(conn, "Test");
 FReport.RegisterData(data, "NorthWind");
 FReport.Design();
 }

 Here, we created a report object, announced the MS SQL database connection line, created a report data source, and registered it in the report. Get the GetDataSet data source as follows:

1
2
3
4
5
6
7
8
9
10
11
public DataSet GetDataSet(SqlConnection connection, string storedProcName, params SqlParameter[] params)
 {
 var command = new SqlCommand(storedProcName, connection) { CommandType = CommandType.StoredProcedure };
 command.Parameters.AddRange(params);
 
 var result = new DataSet();
 var dataAdapter = new SqlDataAdapter(command);
 dataAdapter.Fill(result);
 
 return result;
 }

 This method accepts a connection string, the name of the stored procedure and the possible parameters for the stored procedure. In our case, we did not pass anything. After you create a connection to the database stored procedure is executed. The results of her work are placed in dataAdapter.

On this stage we could have finished the article. The above code is enough to create and register a data source in a report, which will be available to you in the designer, when you edit created at the beginning of the report object.

When the Report Designer is open, you will see a blank report template. Sources of data will need to choose the settings:

 

However, we can improve the code and avoid further design actions in selecting data sources. That is, the report will create an additional data. To do this, we will complete the push button handler:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
private void button1_Click(object sender, EventArgs e)
 {
 Report FReport = new Report();
 Config.DesignerSettings.DesignerLoaded += DesignerSettings_DesignerLoaded;
 SqlConnection conn = new SqlConnection("Data Source=localhost;AttachDbFilename=;Initial Catalog=salesdb;Integrated Security=True;Persist Security Info=False;User ID=;Password=");
 DataSet data = GetDataSet(conn, "Test");
 
 FReport.RegisterData(data, "NorthWind");
 // activate all data sources by default
 foreach (DataSourceBase source in FReport.Dictionary.DataSources)
 {
 source.Enabled = true;
 }
 FReport.Design();
 }

 As you've noticed, we signed the handler to the designer's download event. Next, we include all the data sources in the report in the loop. We implement the handler for the designer's download event:

1
2
3
4
 private void DesignerSettings_DesignerLoaded(object sender, EventArgs e)
 {
 (sender as Designer).cmdNew.CustomAction += new EventHandler(cmdNew_CustomAction);
 }

 We are forwarding the pointer to an event report designer. the event handler:

1
2
3
4
5
6
7
8
9
10
 void cmdNew_CustomAction(object sender, EventArgs e)
 {
 Designer designer = sender as Designer;
 
 //StandardReportWizard wizard = new StandardReportWizard(); // you can use any wizard form package
 BlankReportWizard wizard = new BlankReportWizard();
 wizard.Run(designer);
 
 designer.SetModified(this, "EditData");
 }

 Create a wizard of creating an empty report.

As a result, when editing an empty report, we get the added data:

 

A propos de notre produit Acheter
avatar
Dmitriy Fedyashov
Technical Writer
Fast Reports Team: Dmitriy Fedyashov - Technical Writer at Fast Reports
.NET FastReport Data Source WinForms

Ajouter un commentaire
logo
  • 800-985-8986 (English, US)
  • +4930568373928 (German)
  • +55 19 98147-8148 (Portuguese)
  • info@fast-report.com
  • 901 N Pitt Str #325 Alexandria VA 22314
  • Acheter
  • Télécharger
  • Documentation
  • Avis
  • Comment désinstaller nos produits
  • FAQ
  • Tutoriel vidéo
  • Forum
  • Articles
  • Nos actualités
  • Dossier Presse
  • Partenaires
  • Extended licensing
  • Nous contacter

© 1998-2023 by Fast Reports Inc.

  • Politique de confidentialité
  • Pas une offre publique