3 Replies Latest reply on Mar 16, 2011 6:59 PM by philco

    Manipulating AF Tables programmatically

    AlistairFrith

      I am trying to implement programmatic access to a Table in the AF library.

       

      I have a table called [User Settings] with columns called [User ID], [KVA Upper] and [KVA Lower]. User ID is a string that is supposed to be the ID of the connecting user and the other 2 are numeric fields.

       

      I have been trying to update this using:

       
          AFTable t = myDatabase.Tables[tableName];
      
          t.Table.BeginLoadData();
          t.Table.LoadDataRow(values, true);
          t.Table.EndLoadData();
          t.ApplyChanges();
          t.CheckIn();
      

       The  problem is that, because there are no key fields, if I try to change any of the numeric values, it creates a new row instead of updating the existing row. To use LoadDataRow, I think there are 2 options:

      • I need the [User ID] field to be the primary key but I can't see anywhere in AF System Explorer to set that.
      • Otherwise I will need to go through the table row by row to find the one with the matching User ID and then change that row but I can't see anything in the DataTable class to enable me to do that either!

      I realise that dataTables are not OSI specific and I have been looking at http://msdn.microsoft.com/en-us/library/9186hy08 which is not helping! Can anyone give me some pointers?

       

      Also, to get the current connection, I have been using

       
          PISystem mySystem;
          ...
          mySystem = af[ServerName];
          ...
          string username = mySystem.ConnectionInfo.AccountName
      

      but AccountName is blank. How should I be getting the user credentials?

       

      --- Alistair.

       

       

       

       

        • Re: Manipulating AF Tables programmatically
          AlistairFrith

          I have just found the answer to my 2nd question:

           

           string username = mySystem.CurrentUserName

           

          but I am still struggling with the first. I am thinking of programmatically setting the [User ID] columns Unique property to True...

           

          ... And that fails. Instead of creating another row, I get an exception: "Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints." so it is still trying to create a new row.

           

          --- alistair.

            • Re: Manipulating AF Tables programmatically
              cmanhard

              You should be able to find plenty of examples for working with DataTables on Microsoft's site.  For example: How to: Edit Rows in a DataTable at msdn.microsoft.com/.../tat996zc(v=vs.80).aspx.

               

              Code below is an example of how to modify a row:

               
              myTable.CheckOut();
              DataTable myTable = myAFTable.Table;
              Data.DataRow[] rows = myTable.Select("User ID='User1');
              rows[0]["MyValueColumn1"] = 123;
              myTable.AcceptChanges();
              myAFTable.CheckIn();
              

               

                • Re: Manipulating AF Tables programmatically
                  philco

                  Hi Alistair,

                   

                  I had the same issue at some point. You just need to set a primary key programmatically prior to make your changes. Here's an example: (note that in this example the key is composed of a single field).

                   
                  Dim myPISystems As New PISystems
                  Dim myPISystem As PISystem = myPISystems.DefaultPISystem
                  Dim myDB As AFDatabase = myPISystems.Item(yourAFServerName).Databases.Item(yourAFDatabase)
                  Dim arPrimaryKey(0) As System.Data.DataColumn
                  arPrimaryKey(0) = myDB.Tables(yourTablename).Table.Columns(yourColumnname)
                  myDB.Tables(yourTablename).Table.PrimaryKey = arPrimaryKey
                  myDB.CheckIn()