# DataSets and DataTables

> Merge a DataTable or a DataSet with relations in Windows Forms, load it into the Reporting tab and connect a database with the DatabaseConnectionDialog.

- **Product:** TX Text Control .NET for Windows Forms
- **Section:** Documentation
- **Kind:** Topic
- **Path:** TX Text Control .NET for Windows Forms > Mail Merge and Reporting > Data Sources > DataSets and DataTables
- **3438 words**
- **Web URL:** https://docs.textcontrol.com/tx-text-control-dotnet-winforms/mail-merge/data-sources/datasets/
- **LLMs URL:** https://docs.textcontrol.com/tx-text-control-dotnet-winforms/mail-merge/data-sources/datasets/llms.txt
- **LLMs-Full URL:** https://docs.textcontrol.com/tx-text-control-dotnet-winforms/mail-merge/data-sources/datasets/llms-full.txt

---

Data that comes from a database usually arrives in ADO.NET form: a `DataTable` per query, collected in a `DataSet`. `MailMerge` merges such tables directly, and the relations of the `DataSet` connect the master records with the rows of their merge blocks. Here you merge a single table, merge related tables for merge blocks and prefixed fields, make the tables available in the **Reporting** tab and let users connect a database there.

The examples use the invoice of the [Mail Merge](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/getting-started/mail-merge/llms-full.txt) tutorials in Getting Started, as tables: an `Invoice` table with the columns `Company`, `Name` and `InvoiceNumber`, and an `Items` table for the merge block `Items`.

### Merge a DataTable

[Merge](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol-documentserver/mailmerge/merge-method/llms-full.txt) takes a `DataTable`. The column names are the field names, and the template is merged once for each row: a table with 50 rows creates 50 letters. The second parameter, `append`, decides whether they form one document (`true`) or separate documents (`false`).

### Merge Related Tables

An invoice has one row in `Invoice` and several rows in `Items`. Put both tables in a `DataSet`, connect them with a `DataRelation` and pass the master table to `Merge`. `Merge` follows the relations: a merge block receives the rows of the related table with its name, and a merge field addresses a related table with the table name as prefix, such as `Customer.City`.

The following methods of *Form1.cs* build the data in code and merge it into the template in `textControl1`, which contains the fields `Company`, `Name` and `InvoiceNumber` and a merge block `Items`:

```csharp
using System;
using System.Data;
using TXTextControl.DocumentServer;
```

```csharp
private DataSet CreateInvoiceData()
{
    DataSet dataSet = new DataSet();

    DataTable invoice = dataSet.Tables.Add("Invoice");
    invoice.Columns.Add("InvoiceId", typeof(int));
    invoice.Columns.Add("Company");
    invoice.Columns.Add("Name");
    invoice.Columns.Add("InvoiceNumber");
    invoice.Rows.Add(1, "Contoso Ltd.", "Jane Miller", "INV-1001");

    DataTable items = dataSet.Tables.Add("Items");
    items.Columns.Add("InvoiceId", typeof(int));
    items.Columns.Add("Description");
    items.Columns.Add("Quantity", typeof(int));
    items.Columns.Add("Price", typeof(decimal));
    items.Rows.Add(1, "Consulting", 2, 150.00m);
    items.Rows.Add(1, "Training", 1, 480.00m);

    // The relation connects each invoice with its items
    dataSet.Relations.Add("InvoiceItems", invoice.Columns["InvoiceId"], items.Columns["InvoiceId"]);

    return dataSet;
}

private void mergeDataSetButton_Click(object sender, EventArgs e)
{
    DataSet dataSet = CreateInvoiceData();

    using (MailMerge mailMerge = new MailMerge { TextComponent = textControl1 })
    {
        // Pass the master table; the related Items table fills the Items block
        mailMerge.Merge(dataSet.Tables["Invoice"], true);
    }
}
```

The code works as follows:

- `InvoiceId` is the key that connects the tables. It isn't a field of the template, but a template could show it like any other column.
- `dataSet.Relations.Add` creates a `DataRelation` from the parent column to the child column. Without it, `Merge` finds no data for the `Items` block, whose content is then removed, because [RemoveEmptyBlocks](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol-documentserver/mailmerge/removeemptyblocks-property/llms-full.txt) is `true` by default.
- `Merge(dataSet.Tables["Invoice"], true)` merges one invoice per row of the master table, with the items that belong to it, and loads the result into the editor.

In an application, the tables come from the database, for example filled by a `DataAdapter`; the relations are then added in the same way. Relations can reach further levels, such as the products of an item, for nested blocks and for prefixed fields like `Production_Product.Name`. The blog article [Prefixed Merge Field Names using Related Tables](https://www.textcontrol.com/blog/2014/07/03/prefixed-merge-field-names-using-related-tables/) shows such a template.

### Offer the Tables in the Reporting Tab

[LoadDataSet](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol-documentserver-datasources/datasourcemanager/loaddataset-method/llms-full.txt) of the tab's [DataSourceManager](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol-windows-forms-ribbon/ribbonreportingtab/datasourcemanager-property/llms-full.txt) loads a `DataSet` with its relations for template design, and [LoadDataTable](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol-documentserver-datasources/datasourcemanager/loaddatatable-method/llms-full.txt) a single table:

```csharp
private void Form1_Load(object sender, EventArgs e)
{
    ribbonReportingTab1.DataSourceManager.LoadDataSet(CreateInvoiceData());
}
```

Users then choose the master table with **Select Master Table**. **Insert Merge Field** lists its columns, and **Insert Merge Block** the tables related to it, which [PossibleMergeBlockTables](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol-documentserver-datasources/datasourcemanager/possiblemergeblocktables-property/llms-full.txt) returns in code.

### Edit Relations in the Designer

When the loaded tables lack a relation, users add it with **Edit Data Relations** in the **Data Source** group of the Reporting tab. In the **Create Data Relation** dialog box, they choose a main table and a child table and check the column that connects them in each:

![The Create Data Relation dialog box with Sales_SalesOrderHeader as main table and Sales_SalesOrderDetail as child table, both connected by the column SalesOrderID](https://docs.textcontrol.com/assets/img/tx-text-control-dotnet-winforms/create-data-relation-dialog.5622f5d1.webp)

Your code opens the same functionality with [EditDataRelationsDialog](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol-documentserver-windows-forms/editdatarelationsdialog/llms-full.txt), and [DataRelations](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol-documentserver-datasources/datasourcemanager/datarelations-property/llms-full.txt) lists the relations afterward:

```csharp
using System.Diagnostics;
using TXTextControl.DocumentServer.DataSources;
using TXTextControl.DocumentServer.Windows.Forms;
```

```csharp
private void relationsButton_Click(object sender, EventArgs e)
{
    DataSourceManager dataSourceManager = ribbonReportingTab1.DataSourceManager;

    // Let the user add or remove relations between the loaded tables
    using (EditDataRelationsDialog dialog = new EditDataRelationsDialog(dataSourceManager))
    {
        dialog.ShowDialog(this);
    }

    foreach (DataRelationInfo relation in dataSourceManager.DataRelations)
    {
        Debug.WriteLine($"{relation.ParentTableName} -> {relation.ChildTableName}");
    }
}
```

These relations belong to the `DataSourceManager`, the data of the designer. To merge with them, save them together with a database connection in a [data source configuration file](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/mail-merge/data-sources/configuration-files/llms-full.txt), or add the same relations to the `DataSet` your code merges.

### Connect a Database in the Designer

In Windows Forms, users can connect the Reporting tab to a database themselves. **Data Source** in the **Select Data Source** drop-down opens the **Choose Data Source** dialog box, where they pick the kind of database and the data provider and then build the connection:

![The Reporting tab of TX Text Control Words with the Choose Data Source dialog box listing Microsoft Access, ODBC and SQL Server data sources and a data provider](https://docs.textcontrol.com/assets/img/tx-text-control-dotnet-winforms/choose-data-source-dialog.7f4159b6.webp)

The dialog is [DatabaseConnectionDialog](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol-documentserver-windows-forms/databaseconnectiondialog/llms-full.txt), which your code can open with the `DataSourceManager` it should connect. After the connection, [ConnectionString](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol-documentserver-datasources/datasourcemanager/connectionstring-property/llms-full.txt) and [DataProviderName](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol-documentserver-datasources/datasourcemanager/dataprovidername-property/llms-full.txt) return the selected connection:

```csharp
using System;
using System.Windows.Forms;
using TXTextControl.DocumentServer.DataSources;
using TXTextControl.DocumentServer.Windows.Forms;
```

```csharp
private void connectButton_Click(object sender, EventArgs e)
{
    DataSourceManager dataSourceManager = ribbonReportingTab1.DataSourceManager;

    using (DatabaseConnectionDialog dialog = new DatabaseConnectionDialog(dataSourceManager))
    {
        dialog.ShowDialog(this);
    }

    MessageBox.Show(this, dataSourceManager.DataProviderName + Environment.NewLine + dataSourceManager.ConnectionString);
}
```

A connected database is the one kind of data source that can be saved as a configuration file and merged later without code that queries it; [Data Source Configuration Files](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/mail-merge/data-sources/configuration-files/llms-full.txt) describes the steps.

### Replace MergeBlocks in Older Code

Code written for older versions may call [MergeBlocks](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/reference/txtextcontrol-documentserver/mailmerge/mergeblocks-method/llms-full.txt), which merges the blocks of a template with the tables of a `DataSet` whose names match the block names. The method is obsolete: since version 20.0, `Merge` merges blocks from the related tables of the master table, as shown above. [Deprecated APIs](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/whats-new/deprecated-apis/llms-full.txt) lists it with its replacement.

### Next Steps

- [JSON and XML](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/mail-merge/data-sources/json-xml/llms-full.txt): XML files read into a `DataSet`, and JSON data.
- [Data Source Configuration Files](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/mail-merge/data-sources/configuration-files/llms-full.txt): merge a database with the relations defined in the designer.
- [Nested Blocks and Master-Detail Reports](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/mail-merge/nested-blocks/llms-full.txt): relations over several levels.
- [Merge Blocks](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/mail-merge/blocks/llms-full.txt): design the blocks that related tables fill.

---

## Nearby

- **Previous:** [Objects and Collections](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/mail-merge/data-sources/objects/llms-full.txt)
- **Next:** [JSON and XML](https://docs.textcontrol.com/tx-text-control-dotnet-winforms/mail-merge/data-sources/json-xml/llms-full.txt)