FieldDatabase

FieldDatabase class

Implements the DATABASE field.

To learn more, visit the Working with Fields documentation article.

public class FieldDatabase : Field

Constructors

Name	Description
FieldDatabase()	The default constructor.

Properties

Name	Description
Connection { get; set; }	Gets or sets a connection to the data.
DisplayResult { get; }	Gets the text that represents the displayed field result.
End { get; }	Gets the node that represents the field end.
FileName { get; set; }	Gets or sets the complete path and file name of the database
FirstRecord { get; set; }	Gets or sets the integral record number of the first data record to insert.
Format { get; }	Gets a `FieldFormat` object that provides typed access to field’s formatting.
FormatAttributes { get; set; }	Gets or sets which attributes of the format are to be applied to the table.
InsertHeadings { get; set; }	Gets or sets whether to insert the field names from the database as column headings in the resulting table.
InsertOnceOnMailMerge { get; set; }	Gets or sets whether to insert data at the beginning of a merge.
IsDirty { get; set; }	Gets or sets whether the current result of the field is no longer correct (stale) due to other modifications made to the document.
IsLocked { get; set; }	Gets or sets whether the field is locked (should not recalculate its result).
LastRecord { get; set; }	Gets or sets the integral record number of the last data record to insert.
LocaleId { get; set; }	Gets or sets the LCID of the field.
Query { get; set; }	Gets or sets a set of SQL instructions that query the database.
Result { get; set; }	Gets or sets text that is between the field separator and field end.
Separator { get; }	Gets the node that represents the field separator. Can be `null`.
Start { get; }	Gets the node that represents the start of the field.
TableFormat { get; set; }	Gets or sets the format that is to be applied to the result of the database query.
virtual Type { get; }	Gets the Microsoft Word field type.

Methods

Name	Description
GetFieldCode()	Returns text between field start and field separator (or field end if there is no separator). Both field code and field result of child fields are included.
GetFieldCode(bool)	Returns text between field start and field separator (or field end if there is no separator).
Remove()	Removes the field from the document. Returns a node right after the field. If the field’s end is the last child of its parent node, returns its parent paragraph. If the field is already removed, returns `null`.
Unlink()	Performs the field unlink.
Update()	Performs the field update. Throws if the field is being updated already.
Update(bool)	Performs a field update. Throws if the field is being updated already.

Remarks

Inserts the results of a database query into a WordprocessingML table.

Examples

Shows how to extract data from a database and insert it as a field into a document.

Document doc = new Document();
DocumentBuilder builder = new DocumentBuilder(doc);

// This DATABASE field will run a query on a database, and display the result in a table.
FieldDatabase field = (FieldDatabase)builder.InsertField(FieldType.FieldDatabase, true);
field.FileName = DatabaseDir + "Northwind.accdb";
field.Connection = "Provider=Microsoft.ACE.OLEDB.12.0";
field.Query = "SELECT * FROM [Products]";

Assert.AreEqual($" DATABASE  \\d {DatabaseDir.Replace("\\", "\\\\") + "Northwind.accdb"} \\c Provider=Microsoft.ACE.OLEDB.12.0 \\s \"SELECT * FROM [Products]\"", field.GetFieldCode());

// Insert another DATABASE field with a more complex query that sorts all products in descending order by gross sales.
field = (FieldDatabase)builder.InsertField(FieldType.FieldDatabase, true);
field.FileName = DatabaseDir + "Northwind.accdb";
field.Connection = "Provider=Microsoft.ACE.OLEDB.12.0";
field.Query =
    "SELECT [Products].ProductName, FORMAT(SUM([Order Details].UnitPrice * (1 - [Order Details].Discount) * [Order Details].Quantity), 'Currency') AS GrossSales " +
    "FROM([Products] " +
    "LEFT JOIN[Order Details] ON[Products].[ProductID] = [Order Details].[ProductID]) " +
    "GROUP BY[Products].ProductName " +
    "ORDER BY SUM([Order Details].UnitPrice* (1 - [Order Details].Discount) * [Order Details].Quantity) DESC";

// These properties have the same function as LIMIT and TOP clauses.
// Configure them to display only rows 1 to 10 of the query result in the field's table.
field.FirstRecord = "1";
field.LastRecord = "10";

// This property is the index of the format we want to use for our table. The list of table formats is in the "Table AutoFormat..." menu
// that shows up when we create a DATABASE field in Microsoft Word. Index #10 corresponds to the "Colorful 3" format.
field.TableFormat = "10";

// The FormatAttribute property is a string representation of an integer which stores multiple flags.
// We can patrially apply the format which the TableFormat property points to by setting different flags in this property.
// The number we use is the sum of a combination of values corresponding to different aspects of the table style.
// 63 represents 1 (borders) + 2 (shading) + 4 (font) + 8 (color) + 16 (autofit) + 32 (heading rows).
field.FormatAttributes = "63";
field.InsertHeadings = true;
field.InsertOnceOnMailMerge = true;

doc.FieldOptions.FieldDatabaseProvider = new OleDbFieldDatabaseProvider();
doc.UpdateFields();

doc.Save(ArtifactsDir + "Field.DATABASE.docx");