Data Set update using script


How to update the Data Set using the script?


The following is an example set up to update the Vendors Data Set.

  1. Create a string environmental variable for the connection.
  2. Navigate to Project > Document Definitions to select the required Document Definition and click the Edit button. Then open the Document Definition > Document Definition Properties > Data Sets and select the Data Set that should be updated via the script. Click the Set Up button and select the option for Source > Script.
  3. Click Edit.
  4. Make sure that the table exists in the source database. If not, it is possible to create one by using a query similar to the following:
    CREATE TABLE Vendors(
    BusinessUnitId INT,
    VatId VARCHAR(20),
    CountryCode VARCHAR(2),
    Name NVARCHAR(255),
    Street NVARCHAR(255),
    ZIP VARCHAR(10),
    City NVARCHAR(255),
    BankAccount VARCHAR(20),
    BankCode VARCHAR(10),
    GLCode VARCHAR(10)
  5. Make sure that the columns in the Data Set have the same case as the columns in the database.
  6. Use the following example script:
    using System;
    using System.Data.SqlClient;

    string connectionString = FCTools.ScriptContext.Project.EnvironmentVariables.Get("VendorsDB").Trim();

    // Specify your SQL query to select all columns from the table
    string sqlQuery = "SELECT * FROM dbo.Vendors";

    using (SqlConnection connection = new SqlConnection(connectionString))

    using (SqlCommand command = new SqlCommand(sqlQuery, connection))
    using (SqlDataReader reader = command.ExecuteReader())
    while (reader.Read())
    // Create a new record in your custom system
    IDataSetRecord record = DataSet.CreateRecord();

    // Iterate through each column in the result set and add it to the record
    for (int i = 0; i < reader.FieldCount; i++)
    string columnName = reader.GetName(i);
    object columnValue = reader.GetValue(i);

    // Adjust the logic to handle specific data types or conversions if needed
    record.AddValue(columnName, columnValue.ToString());

    // Add the record to the custom system dataset
  7. Test the solution by selecting the Update and View buttons.

Have more questions? Submit a request



Please sign in to leave a comment.