Question

How to update the Data Set using the script?

Answer

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,
   Id INT PRIMARY KEY,
   VatId VARCHAR(20),
   NationalVATIDVARCHAR(20),
   IBAN VARCHAR(30),
   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))
   {
   connection.Open();
   
   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
   DataSet.AddRecord(record);
   }
   }
   }
   }

7. Test the solution by selecting the Update and View buttons.