Creating PDF Reports with Database Data (SQL)

Thu, July 11 2019, 11:04 AM (US Eastern Time)

DynamicPDF ReportWriter make’s it easy to create reports based on your business objects, but sometimes it may be preferred to access data directly from a database using an SQL query or stored procedure. This can easily be done by utilizing one of these report data classes:

Let’s take a look at the steps involved with doing this. First, attach an event to the DocumentLayout’s ReportDataRequired event. This event will be called anytime a report or sub report requires data.

// Create the document's layout from a DLEX template
DocumentLayout documentLayout = new DocumentLayout(Util.GetResourcePath("SimpleReport.dlex"));

// Attach to the ReportDataRequired event
documentLayout.ReportDataRequired += DocumentLayout_ReportDataRequired;

Now in that event, first check the elementId of the report or sub report that is requesting the data. This is necessary in case there are multiple reports or sub reports in your document layout. Next, create a DataReader or DataTable and use it to create a DataReaderReportData or DataTableReportData class. Then set the event’s RepartData event argument to it.

private static void DocumentLayout_ReportDataRequired(object sender, ReportDataRequiredEventArgs args)
{
    if (args.ElementId == "ProductsReport")
    {
        string sqlString =
            "SELECT ProductName, QuantityPerUnit, UnitPrice " +
            "FROM   Products ";

        SqlConnection connection = new SqlConnection(connectionString);
        SqlCommand command = new SqlCommand(sqlString, connection);
        connection.Open();
        SqlDataReader reader = command.ExecuteReader();
        args.ReportData = new DataReaderReportData(connection, reader);
    }
}

Lastly, layout your DocuementLayout to a Document and output the PDF.

// Specify the data
NameValueLayoutData layoutData = new NameValueLayoutData();
layoutData.Add("ReportCreatedFor", "Alex Smith");

// Layout the document and save the PDF
Document document = documentLayout.Layout(layoutData);
document.Draw(outputFilePath);
Wrap Up

A C# Visual Studio project demonstrating this is available on GitHub:

https://github.com/DynamicPDF/dotnet-pdf-report-from-database

If you have any questions or comments, I would love to hear from you.

Tags: , , , , , ,

Month List