Issues with filling PDF forms. Should I use C# with IText AcroFields to fill forms programmatically from a database? Or is this solution outdated?

Question

TLDR; Having below issues with IText solution, and have not found a "better" option.

Project currently is finding the best way to fill forms programatically from a database. After researching heavily I found a few solutions based on the following criteria and have created a (somewhat working) solution:

• We are currently using Access but are moving to SQL in coming months.
• Filling multiple unique forms that are created by state agencies based on user entered. (so we can't just create our own forms.)
• Forms are regularly updated by the agencies, but the data required remains the same, and will be stored in a database.

To solve this I researched things like IText which has a ton of tools for PDF manipulation, one small part of that is it can fill a form using AcroField technology.

I also read through a number of resources here including:

• This description on using AcroFields and IText to fill forms
• This older solution using Docotic

• This even older solution with Java

  Among many other resources. I found that IText seemed to be the best supported with some examples and API that helped me create the following:

  //Creates form
  public virtual void ManipulatePdf(string src, string dst, DataGridViewRow dataRow)
  {
      //Initialize PDF document
      PdfDocument pdf = new PdfDocument(new PdfReader(src), new PdfWriter(dst));
      PdfAcroForm form = PdfAcroForm.GetAcroForm(pdf, true);
      //form.RemoveXfaForm();
      //form.SetNeedAppearances(true);
      IDictionary fields = form.GetFormFields();
      PdfFormField toSet;
      //visits each field, fills dependant on whether dgv column exists
      foreach (string field in fields.Keys)
      {
          switch (dataGridView1.Columns.Contains(field))
          {
              case false:
                  switch (field)
                  {
                      case "Date":
                          fields.TryGetValue(field, out toSet);
                          toSet.SetValue(dateTime.ToString("MM/dd/yy"));
                          break;
                      case "Period_YY":
                          fields.TryGetValue(field, out toSet);
                          toSet.SetValue(dateTime.ToString("yy"));
                          break;
                      case "Period_YY2":
                          fields.TryGetValue(field, out toSet);
                          toSet.SetValue(dateTime.ToString("yy"));
                          break;
                      case "Period_Month_Start":
                          fields.TryGetValue(field, out toSet);
                          toSet.SetValue(dateTime.AddMonths(-1).ToString("MMMM"));
                          break;
                      case "Client_Name2":
                          fields.TryGetValue(field, out toSet);
                          toSet.SetValue(dataRow.Cells["Client_Name"].Value.ToString());
                          break;
                      default:
                          fields.TryGetValue(field, out toSet);
                          toSet.SetValue("0");
                          //this setting does not commit, issue with hybrid XFA/AcroForm
                          //.SetBackgroundColor(ColorConstants.YELLOW);
                          break;
                  }
                  break;
  
          case true:
              if (dataRow.Cells[field].Value != null && !DBNull.Value.Equals(dataRow.Cells[field].Value))
              {
                  string value = dataRow.Cells[field].Value.ToString();
                  fields.TryGetValue(field, out toSet);
                  //this value is sometimes not visable on finished form
                  toSet.SetValue(value);
              }
              break;
  
          default:
              break;
      }
  }
  //form.RemoveXfaForm();
  //pdf.GetCatalog().Remove(PdfName.Perms);
  //form.FlattenFields();
  pdf.Close();
  }
  

This solution does fill forms, but there are some very big issues with it I have not been able to solve:

1. Changes do not always commit to form (for instance .backgroundcolor)/will show empty until user clicks into form box.
2. Value will print but so will form box changes such as .backgroundcolor.
3. These forms have redundant fields and AcroForm seems to have issues with parent/child field names from Adobe. (For instance it will rename any like fields)
4. There is not an API for C# for Itext7, and I have not been able to solve these problems.

Any help, articles or insight would be appreciated, I know "open ended" questions don't get much help on here but had to try. Thank you.