How to generate a Report using Database in C# and .NET


This code shows how to generate a Mail Merge Report using a DOCX template and Database as a data source.

In our example we will take a ready database «Northwind.mdb». We will export data from tables: Orders, Customers, Shippers, Employees, [Order Details] and Products to generate our Report.

Next, we create a template in MS Word with Merge Fields or use a ready template: «OrdersDbTemplate.docx».

So, we have to take data from Northwind database and merge it with our template.

Step 1: Create a new DataTable with name 'orders' and fill it by all necessary data from DB.
We'll use this DataTable (orders) to create a table caption (as shown on the picture below) for the each order.

// Execute query for retrieving data of Orders table.
            DataTable orders = ExecuteSQL(string.Join(" ",
                "SELECT DISTINCT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,",
                "Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Orders.CustomerID,",
                "Customers.CompanyName AS Customers_CompanyName, Customers.Address, Customers.City,",
                "Customers.Region, Customers.PostalCode, Customers.Country,",
                @"[FirstName] & "" "" & [LastName] AS SalesPerson, Orders.OrderID, Orders.OrderDate,",
                "Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName AS Shippers_CompanyName",
                "FROM Shippers INNER JOIN (Employees INNER JOIN (Customers INNER JOIN Orders ON",
                "Customers.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID)",
                "ON Shippers.ShipperID = Orders.ShipVia"));
            orders.TableName = "Orders";
            dataSet.Tables.Add(orders);
            

As a result we'll get:

Step 2: Create another DataTable with name 'orderDetails'.
We'll use this DataTable (orderDetails) to create a table body (as shown on the picture below) for the each order.

 // Execute query for retrieving data of OrderDetails table.
            DataTable orderDetails = ExecuteSQL(string.Join(" ",
                "SELECT DISTINCTROW Orders.OrderID, [Order Details].ProductID, Products.ProductName,",
                "[Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount,",
                "([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100 AS TotalPrice",
                "FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON",
                "Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID"));
            orderDetails.TableName = "OrderDetails";
            dataSet.Tables.Add(orderDetails);
            

As a result we'll get:

Calculate Subtotal for the each order.

 document.MailMerge.FieldMerging += (sender, e) =>
            {
                if (e.RangeName == "Order" && e.FieldName == "Subtotal")
                {
                    e.Inline = new Run(e.Document, ((double)dataSet.Tables["Orders"].Rows[e.RecordNumber - 1].
                        GetChildRows("OrderDetails").Sum(item => (double)item["TotalPrice"])).
                        ToString("$#,##0.00", CultureInfo.InvariantCulture));
                    e.Cancel = false;
                }
            };
            

Execute the Mail Merge process and save the produced Report as «Orders.docx».

Complete code

using System.Linq;
using System.Globalization;
using System.Data;
using System.Data.OleDb;

using SautinSoft.Document;

namespace Sample
{
    class Sample
    {
        static void Main(string[] args)
        {
            MailMergeUsingDatabase();
        }
        /// <summary>
        /// How to generate a Report (Mail Merge) using a DOCX template and Database as a data source.
        /// </summary>
        /// <remarks>
        /// See details at: https://www.sautinsoft.com/products/document/help/net/developer-guide/mail-merge-using-database-net-csharp-vb.php
        /// </remarks>
       static void MailMergeUsingDatabase()
        {
            // Load the DOCX-template document. 
            DocumentCore document = DocumentCore.Load(@"..\..\OrdersDbTemplate.docx");
            
            // Create a data source.
            DataSet dataSet = new DataSet();

            // Execute query for retrieving data of Orders table.
            DataTable orders = ExecuteSQL(string.Join(" ",
                "SELECT DISTINCT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,",
                "Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Orders.CustomerID,",
                "Customers.CompanyName AS Customers_CompanyName, Customers.Address, Customers.City,",
                "Customers.Region, Customers.PostalCode, Customers.Country,",
                @"[FirstName] & "" "" & [LastName] AS SalesPerson, Orders.OrderID, Orders.OrderDate,",
                "Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName AS Shippers_CompanyName",
                "FROM Shippers INNER JOIN (Employees INNER JOIN (Customers INNER JOIN Orders ON",
                "Customers.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID)",
                "ON Shippers.ShipperID = Orders.ShipVia"));
            orders.TableName = "Orders";
            dataSet.Tables.Add(orders);

            // Execute query for retrieving data of OrderDetails table.
            DataTable orderDetails = ExecuteSQL(string.Join(" ",
                "SELECT DISTINCTROW Orders.OrderID, [Order Details].ProductID, Products.ProductName,",
                "[Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount,",
                "([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100 AS TotalPrice",
                "FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON",
                "Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID"));
            orderDetails.TableName = "OrderDetails";
            dataSet.Tables.Add(orderDetails);

            // Add parent-child relation.
            orders.ChildRelations.Add("OrderDetails", orders.Columns["OrderID"], orderDetails.Columns["OrderID"]);

            // Calculate and fill Total.
            document.MailMerge.Execute(
                new
                {
                    Total = ((double)orderDetails.Rows.Cast<DataRow>().Sum(item => (double)item["TotalPrice"])),
                });

            // Calculate Subtotal for the each order.
            document.MailMerge.FieldMerging += (sender, e) =>
            {
                if (e.RangeName == "Order" && e.FieldName == "Subtotal")
                {
                    e.Inline = new Run(e.Document, ((double)dataSet.Tables["Orders"].Rows[e.RecordNumber - 1].
                        GetChildRows("OrderDetails").Sum(item => (double)item["TotalPrice"])).
                        ToString("$#,##0.00", CultureInfo.InvariantCulture));
                    e.Cancel = false;
                }
            };

            // Execute the Mail Merge.
            // Note: As the name of the region in the template (Order) is different from the name of the table (Orders), we explicitly specify the name of the region.
            document.MailMerge.Execute(orders, "Order");

            string resultPath = "Orders.docx";

            // Save the output to file.
            document.Save(resultPath);

            // Open the result for demonstration purposes.
            System.Diagnostics.Process.Start(new System.Diagnostics.ProcessStartInfo(resultPath) { UseShellExecute = true }); 
        }

        /// <summary>
        /// Utility function that creates a connection, executes the sql-query and 
        /// return the result in a DataTable.
        /// </summary>
        static DataTable ExecuteSQL(string sqlText)
        {
            // Open the database connection.
            string str = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + @"..\..\Northwind.mdb";
            OleDbConnection conn = new OleDbConnection(str);
            conn.Open();

            // Create and execute a command.
            OleDbCommand cmd = new OleDbCommand(sqlText, conn);
            OleDbDataAdapter da = new OleDbDataAdapter(cmd);
            DataTable table = new DataTable();
            da.Fill(table);

            // Close the database.
            conn.Close();

            return table;
        }
    }
}

Download

Imports System.Linq
Imports System.Globalization
Imports System.Data
Imports System.Data.OleDb

Imports SautinSoft.Document

Namespace Sample
	Friend Class Sample
		Shared Sub Main(ByVal args() As String)
			MailMergeUsingDatabase()
		End Sub
        ''' <summary>
        ''' How to generate a Report (Mail Merge) using a DOCX template and Database as a data source.
        ''' </summary>
        ''' <remarks>
        ''' See details at: https://www.sautinsoft.com/products/document/help/net/developer-guide/mail-merge-using-database-net-csharp-vb.php
        ''' </remarks>
        Private Shared Sub MailMergeUsingDatabase()
            ' Load the DOCX-template document. 
            Dim document As DocumentCore = DocumentCore.Load("..\OrdersDbTemplate.docx")

            ' Create a data source.
            Dim dataSet As New DataSet()

			' Execute query for retrieving data of Orders table.
			Dim orders As DataTable = ExecuteSQL(String.Join(" ", "SELECT DISTINCT Orders.ShipName, Orders.ShipAddress, Orders.ShipCity,", "Orders.ShipRegion, Orders.ShipPostalCode, Orders.ShipCountry, Orders.CustomerID,", "Customers.CompanyName AS Customers_CompanyName, Customers.Address, Customers.City,", "Customers.Region, Customers.PostalCode, Customers.Country,", "[FirstName] & "" "" & [LastName] AS SalesPerson, Orders.OrderID, Orders.OrderDate,", "Orders.RequiredDate, Orders.ShippedDate, Shippers.CompanyName AS Shippers_CompanyName", "FROM Shippers INNER JOIN (Employees INNER JOIN (Customers INNER JOIN Orders ON", "Customers.CustomerID = Orders.CustomerID) ON Employees.EmployeeID = Orders.EmployeeID)", "ON Shippers.ShipperID = Orders.ShipVia"))
			orders.TableName = "Orders"
			dataSet.Tables.Add(orders)

			' Execute query for retrieving data of OrderDetails table.
			Dim orderDetails As DataTable = ExecuteSQL(String.Join(" ", "SELECT DISTINCTROW Orders.OrderID, [Order Details].ProductID, Products.ProductName,", "[Order Details].UnitPrice, [Order Details].Quantity, [Order Details].Discount,", "([Order Details].[UnitPrice]*[Quantity]*(1-[Discount])/100)*100 AS TotalPrice", "FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON", "Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order Details].ProductID"))
			orderDetails.TableName = "OrderDetails"
			dataSet.Tables.Add(orderDetails)

			' Add parent-child relation.
			orders.ChildRelations.Add("OrderDetails", orders.Columns("OrderID"), orderDetails.Columns("OrderID"))

			' Calculate and fill Total.
			document.MailMerge.Execute(New With {Key .Total = (CDbl(orderDetails.Rows.Cast(Of DataRow)().Sum(Function(item) CDbl(item("TotalPrice")))))})

			' Calculate Subtotal for the each order.
			AddHandler document.MailMerge.FieldMerging, Sub(sender, e)
				If e.RangeName = "Order" AndAlso e.FieldName = "Subtotal" Then
					e.Inline = New Run(e.Document, CDbl(dataSet.Tables("Orders").Rows(e.RecordNumber - 1).GetChildRows("OrderDetails").Sum(Function(item) CDbl(item("TotalPrice")))).ToString("$#,##0.00", CultureInfo.InvariantCulture))
					e.Cancel = False
				End If
			End Sub

			' Execute the Mail Merge.
			' Note: As the name of the region in the template (Order) is different from the name of the table (Orders), we explicitly specify the name of the region.
			document.MailMerge.Execute(orders, "Order")

			Dim resultPath As String = "Orders.docx"

			' Save the output to file.
			document.Save(resultPath)

			' Open the result for demonstration purposes.
			System.Diagnostics.Process.Start(New System.Diagnostics.ProcessStartInfo(resultPath) With {.UseShellExecute = True})
		End Sub

		''' <summary>
		''' Utility function that creates a connection, executes the sql-query and 
		''' return the result in a DataTable.
		''' </summary>
		Private Shared Function ExecuteSQL(ByVal sqlText As String) As DataTable
            ' Open the database connection.
            Dim str As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\Northwind.mdb"
            Dim conn As New OleDbConnection(str)
			conn.Open()

			' Create and execute a command.
			Dim cmd As New OleDbCommand(sqlText, conn)
			Dim da As New OleDbDataAdapter(cmd)
			Dim table As New DataTable()
			da.Fill(table)

			' Close the database.
			conn.Close()

			Return table
		End Function
	End Class
End Namespace

Download


If you need a new code example or have a question: email us at support@sautinsoft.com or ask at Online Chat (right-bottom corner of this page) or use the Form below:



Questions and suggestions from you are always welcome!

We are developing .Net components since 2002. We know PDF, DOCX, RTF, HTML, XLSX and Images formats. If you need any assistance with creating, modifying or converting documents in various formats, we can help you. We will write any code example for you absolutely free.